Author

Database Waits and Deadlocks

Waits Waits occur when system resources are busy processing other tasks, and new tasks are placed into a holding queue until resources free up. Locks on data are the only reason a task will encounter a wait. Bottlenecks and hardware resources are also common contributors to a task having to wait before proceeding. A dynamic... » read more

Transaction Isolation Types

As we know that, in order to maintain consistency in a database, it follows ACID properties. Among these four properties (Atomicity, Consistency, Isolation and Durability) Isolation determines how transaction integrity is visible to other users and systems. It means that a transaction should take place in a system in such a way that it is... » read more

Implicit vs Explicit Transactions

SQL Server supports both implicit and explicit transactions. Implicit transactions are initiated when the system’s session option “Implicit Transactions” is set to ON. In this state, any of a number of commands, including Create, Insert, Update, Delete, Drop, and Select will automatically begin a new transaction. To finalize the transaction, the user must specifically issue... » read more

Database Locking and Concurrency

When two or more users access the same tables at the same time, they’re considered concurrent users. If the operations that they are performing are simple reads, then SQL Server can easily accommodate both users simultaneously. But once one or more users begin updating and inserting records into a table that someone else is reading,... » read more

SQL Server FILESTREAM and FILETABLES

If your database design calls for the immigration of files such as audio, images or other document types, then you’ll want to investigate the SQL Server feature called Filestream. In the past, immigrating a database with unstructured binary data files meant providing links to their location on the disk drive. This is not an ideal... » read more

Linked Server

Not all of the information neededfor the design of an application has to be storedwithin a single instance of of SQL Server. Through a feature called Linked Servers. SQL Server’s database engine can query tables and other remote instances of SQL Server or even other products. Such as Microsoft Access Databases, Oracle Databases, Excel Spreadsheets... » read more

Restore tail-log backups

In the middle of a crisis, where you fear the database is lost, and you need to get it back up and running as quickly as possible, it’s easy to jump right into the Recovery Phase and start restoring the backups that you’ve been carefully creating. But there is an intermediate step that you should... » read more

Database Backup Encryption

Database administrators go to great lengths to ensure that their SQL server instances remain secure. They’re usually physically located in a secured room and use appropriate user accounts than authentication protocols to verify permissions during network access. But if your database back-ups are stored off-site or in the cloud, then just as much care needs... » read more

Database snapshots

Database snapshots provide a way to view the records in a database as they were at a specific point in time. This read-only copy of the data preserves the state of the information, which can be useful for reporting or auditing since these activities can occur on the snapshot without impacting the source database. Multiple... » read more

Database Backup Types

SQL Server backup types outline how the data is handled. Here we have several options, but they all start with a full backup. A full backup will maintain all the data files and the transaction log. A full backup is required before any of the other types can be implemented. Once a full backup is... » read more