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 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 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
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
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
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
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
On data tables with a large number of columns, it might be beneficial to consider a column store index. As the name would imply, column store indexes organize the columns of a data table, rather than the rows. It’s a subtle distinction, but this arrangement allows SQL Server to fit more values in memory and... » read more
You can create a partitioned table or index in SQL Server 2017 by using SQL Server Management Studio or Transact-SQL. The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. Partitioning can make large tables and indexes more manageable and scalable.... » read more
Here’s a few guidelines to get started. Indexes should almost always be built on columns used as primary keys, foreign keys, or frequently used in query join and where clauses. Tables with frequent updates should have very carefully chosen indexes, since each additional index will dramatically reduce write speeds to the table. Very large and... » read more