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

Developing a Backup Strategy

There’s a balance that needs to be struck between how often the backups occur and how long it takes to recover. These can be summarized by two different objectives that need to be carefully considered when developing your backup strategy.  The first is the recovery point objective, or R-P-O. This defines what amount of data... » read more

Database Recovery Models

In the simple model the transaction log automatically truncates itself when the data file has reached certain checkpoints. The benefit is that the log is kept small, but it does so at a cost. Database restores can only occur to the exact point that the last full backup was taken. With a simple recovery model,... » read more

Database Engine Tuning Advisor Tool

The tuning advisor will analyze the database and make recommendations for indexesthat would be beneficial to add or remove. It even provides the TSQL commands that will create the suggested indexes.  The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the... » read more