3 Ways to Restore SQL Server Databases

This is likely the last in a series of three posts about backups and restores.  My aim with this series has been to give you an idea of the options available and a basic start on using those options. I’m not trying to go in-depth on the subject of backups and restores.

Today will be about getting you going on the concept of restoring databases in SQL Server.  I’m using a local install of SQL Server 2017 on my desktop PC to demonstrate these techniques.

Obtaining the StackOverflow Database

Before I dive into demonstrating the restore options, I want to talk a little bit about the set up. I’m using the StackOverflow2010 database.  Anonymized data dumps of the StackOverflow database are available here. This particular version of the database I’m using came from a page on Brent Ozar’s site.

I then used the below PowerShell code to take backups.  Between the Full and Diff backup I added one user to the Users table.  Between the Diff and Log backup I add two more users.

backup-dbadatabase -SqlInstance MySQLServer2017InstanceName -Database 'StackOverflow2010' -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -Type Full -FilePath dbname-backuptype-timestamp.bak -ReplaceInName -CompressBackup -Verify

backup-dbadatabase -SqlInstance MySQLServer2017InstanceName -Database 'StackOverflow2010' -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\' -Type DIFF -FilePath dbname-backuptype-timestamp.bak -ReplaceInName -CompressBackup -Verify

backup-dbadatabase -SqlInstance MySQLServer2017InstanceName -Database 'StackOverflow2010' -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -Type Log -FilePath dbname-backuptype-timestamp.trn -ReplaceInName -CompressBackup -Verify

Below is the T-SQL I used to insert some rows.  That value for Age though!  Ah, to be 25 again!

 

INSERT INTO dbo.Users ([AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId])
VALUES
('I make SQL Server more stable, highly available and easier to manage', 25, GETDATE()-7, 'Lee Markum', 0, NULL, GETDATE(), 'Saint Louis, MO', 0, 1000000, 2000000, 'LeeMarkum.com', NULL)

--Took a differential backup here so I would have some changes to restore

INSERT INTO dbo.Users ([AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId])
VALUES
('I make T-SQL solutions that just work', 25, GETDATE()-7, 'T-SQLGuru', 0, NULL, GETDATE(), 'Saint Louis, MO', 0, 1000000, 2000000, NULL, NULL),
('I make SSIS solutions that just work', 25, GETDATE()-7, 'SSISGuru', 0, NULL, GETDATE(), 'Saint Louis, MO', 0, 1000000, 2000000, NULL, NULL)

--Took a log backup here

Using SSMS to Restore a Database

Just like for creating a backup, SQL Server Management Studio has menu options for restoring a database. Open your copy of SQL Server Management Studio and connect to a non-production or lab environment of some kind. You’re going to right click the database you want to restore and use the fly out menu to navigate to Tasks > Restore > Database.

 

Selecting the Database option from the fly-out menu above brings you to the below page. Notice in the following screen that SQL Server finds the applicable backup files for you. No in-depth knowledge of restore operations is needed in this case because the work is done for you. It has found the full, differential and log backups that I took earlier.  Each row represents a backup file.

If you only want to restore to the time represented by the differential backup, then uncheck the box next to the log backup.  SQL Server will then restore the full and differential backup, but not the log backup. You might want to do something like this if you know that the data in the database was corrupted or changed in an unexpected or undesired way immediately after the differential backup.  As a result, you restore the database to the point in time represented by the differential in order to restore the database to its last known good condition.

In the Destination section, SSMS fills in the name of the database that you selected earlier for restoring.  However, if you give the database a different name here, then you can restore the database and compare the restored database to the current database.  You might want to do this in order to attempt to repair data in the current database by getting data from the restore ddatabase. Please note the yellow banner that informs you that this process will take a tail log backup.  This is a preventative measure.  It captures the last portion of the transaction log called the tail of the log.  It allows for complete recovery.  Without this backup, if you restore this database, you could lose transactions.

 

One other option I want to point out on this screen is what the “Timeline” button is for. When clicked, you’ll arrive at a screen like the below.  This allows you to choose a point in time to restore to based on the available backups.  This point in time restore is only available for databases in the full and bulk-logged recovery model. For bulk-logged recovery model, certain rules apply as to when this option would be available.

If you select the radio button next to “Specific date and time” then you can either specify a certain date and time in the Date and Time boxes or you can also use the slider icon along the bottom.  There is also a Time Interval drop down that controls what you see in the colored timeline above the slider icon. Using this option is the same as using the WITH STOPAT command using T-SQL for the restore. For my demo, I have not used this option, but I want you to be aware that it is available. This Timeline feature is new in SQL Server 2012.

 

Once you have the General page looking the way you need it, click on the Files selection on the left to bring you to the page below. Here you have one option to work with and that is whether you want to relocate the database files to another folder. Checking this option allows you not only to relocate the files, but rename them as well.  You might want to do that if you’re restoring the database to a different name so that you don’t have two databases with different names, but the same file names. That might make it more difficult later to figure out which files belong to which database without running a query to figure it out.

Should You Use the WITH REPLACE Option?

Now click on the Options selection on the left.  You arrive at the below page. The option “overwrite the existing database (WITH REPLACE)” will bypass certain checks that would normally occur during the restore process.  As a result, you really would rarely use this option, if ever. This option will do what it sounds like and replace the target database with the database in the backup file.  If you mistakenly point this restore to database on a different server that isn’t the same database that is in the backup and use this WITH REPLACE option, then you will overwrite the other database on the other server. There is a large section in this page about caution using the WITH REPLACE option.

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-2017

Here is where the option for taking the Tail-log backup is located.  You should leave this checked and note the location for where that backup will be written.  If it isn’t the location you would like, change it in the Backup file box.

There is also an important option under “Server connections.” If you leave this unchecked and there are current connections to the database then the restore will fail.  A restore operation needs exclusive access to the database in order to perform a restore so other connections must be terminated first.  When this option is selected, SSMS will take care of that for you.

Once all the options you want are filled in, I would encourage you to click the Script option at the top so you can obtain the T-SQL equivalent for what SSMS is going to do. You can either click OK in the lower right of SSMS to execute the restore, or click Cancel and go to the T-SQL to review what will be done and then execute the T-SQL.

 

Now let’s turn our attention to using T-SQL for restoring a database.

Using T-SQL to Restore a Database

Below is the generated T-SQL for restoring the StackOverflow2010 database based on the available backup chain on my local SQL Server. I have added comments so you can better understand what each part is doing.  T-SQL syntax for performing a restore is, in most scenarios, not too difficult.  I am not showing things like file and filegroup restores or piecemeal restores.  These are more advanced scenarios.

USE [master]
/*This is the first part of the T-SQL generated when the "close existing connections" option is chosen in the GUI. This gives the session doing the restore the exclusive access needed.
*/
ALTER DATABASE [StackOverflow2010] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--This is the tail-log backup
BACKUP LOG [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010_LogBackup_2020-10-04_10-23-33.bak' WITH NOFORMAT, NOINIT,  NAME = N'StackOverflow2010_LogBackup_2020-10-04_10-23-33', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

/*Here is the full backup.  Database restores need a place to start from and the full backup is that place.
*/
RESTORE DATABASE [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010-Full-202010030919.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

--Here is the differential backup
RESTORE DATABASE [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010-Differential-202010030938.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

--Her are the two log backups to restore
RESTORE LOG [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010-Log-202010030947.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010_LogBackup_2020-10-04_09-20-23.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

/*This is the second part of the T-SQL generated when the "close existing connections" option is chosen in the GUI. This puts the database back to multi_user so it is ready to be used*/
ALTER DATABASE [StackOverflow2010] SET MULTI_USER

GO

 

Now let’s take a look at doing this with PowerShell

Using PowerShell to Restore a SQL Server Database

There are commands in the PowerShell on your local PC or server that will perform restores of SQL Server backups.  However, we will be discussing the PowerShell module called DBATools, which has to be installed before it can be used.

Please go to https://dbatools.io/download/ to see how to obtain and set up the module for use with this part of the blog post. I won’t be covering that here as the material is well documented on their site.

Once you have that, open PowerShell as an Administrator.  Type in the below code and hit F5.

Help Restore-dbadatabase -Detailed

This will give you a full description of the parameters and switches available in this command.  Numerous example code snippets will also be shown. From that help information I wrote the two examples that follow.  The first example will not perform the restore but instead writes to a .sql all the T-SQL needed to perform the command.  Strictly speaking, the -DatabaseName and value aren’t needed because the only backup files in that directory are for the StackOverflow2010 database. This option is useful so you can see what the command is going to do.

In the second example, the PowerShell will actually do the restore and then write it’s actions out to a text file. This is useful for having a history of what the command did.

In the third example, this will  use the folder structure from Ola Hallengren’s world famous maintenance solution. Notice the following switch:

-MaintenanceSolutionBackup

There are also switches for maxtransfersize, buffercount, and blocksize.  These can all be experimented with for your databases to find an optimal combination that restores the database as fast as possible. Additionally, there is a -RestoreTime option that is the equivalent of the WITH STOPAT syntax in T-SQL and the TimeLine function in the SSMS GUI.

<#This will not perform the restore, but will instead create a .sql file of the necessary T-SQL to do the requested restore.
#>
Restore-DbaDatabase -SqlInstance MYPC\Kronos2017 -DatabaseName StackOverflow2010 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -WithReplace -OutputScriptOnly | Out-File 'C:\DBAToolsRestoreScripts\RestoreStackOverFlow2010.sql' 

<#This will do the restore and create a text file showing what the PowerShell command actually did
#>
Restore-DbaDatabase -SqlInstance MYPC\Kronos2017 -DatabaseName StackOverflow2010 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -WithReplace | Out-File 'C:\DBAToolsRestoreScripts\RestoreActionsStackOverFlow2010.txt' 

<# This will do the restore based on Ola Hallengren's directory structure from his world famous maintenance solution.  Notice the switch -MaintenanceSolutionBackup
#>
Restore-DbaDatabase -SqlInstance MYPC\Kronos2017 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -MaintenanceSolutionBackup -WithReplace -OutputScriptOnly | Out-File 'C:\DBAToolsRestoreScripts\RestoreStackOverFlow2010.sql' 

Next Steps to Take

  1. Download and install DBATools.  It’s useful for a lot more than handling SQL Server restore scenarios!
  2. If you’re not familiar with PowerShell, you need to learn it, if for no other reason than the options that DBATools provides you for working with SQL Server.
  3. Connect to a test server and try out each method. Get some experience with these methods so you know which one you like the most and so you’re not trying to figure out how you want to perform a restore when you actually have to do it for real.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

5 Ways to Make SQL Server Backups

Last time we discussed 5 backup types for SQL Server.  Now I want to introduce you to five ways to make backups in SQL Server. I can’t possibly explore all the options available with each of these six methods. There is a lot to cover for even a “simple” topic like backups.

Method 1: Using the SSMS GUI to Make a SQL Server Back up

 

For those readers who like the SSMS GUI, there is good news.  SQL Server Management Studio offers a fairly straightforward method for backing up a database. Once you connect to the SQL Server instance containing the database you want to back up, left click on the “+” sign next to the Databases folder.  Then right click the name of the database you want to backup, choose Tasks, then choose the Backup option in the fly out menu.

That brings you to the General Backup Menu page in SSMS.  In this menu you can access a number of settings related to the backup that will be generated.

With the “Backup type” drop down you can control whether this will take a Full, Differential or Log backup.

The “Backup component” section can be adjusted to take either a backup of files and filegroups or the default selection of “Database.”

In the Destination section you can backup to the default of “Disk” or choose “URL” from the drop down to make a backup to Azure as the target. When the destination of Disk is left you will be supplied with the destination of the backup and a filename for the backup.  This directory will be the default backup directory selected during the SQL Server installation.  If this is not where you want the backup, simply click “Remove” then click “Add” to navigate to the location you want to use.  The “Add” menu does accept share paths.

The Media Options selection on the “Select a Page” allows you to choose options for things like whether you want to append this backup to the same media set or start over with a new one.

The media options seem to me to be from an earlier day when backups were written to physical tapes.  Those tapes then had to be rotated from time to time.  It’s not a best practice to append backups to the media set because you’re adding backup files into a single media set.  If something happens to the media set and it is unusable, then the backups are not accessible.

In the Reliability section, you should check the options for “Verify backup when finished” and “Perform checksum before writing to media.” Thes options will cause your backup to take longer to complete, but they do help with validating the integrity of the backup at the time it was written.

The Backup Options are in the “Select a page” menu has what I think is one very important feature to note.

On this page there is an option related to backup compression.  Back in older versions of SQL Server, like 2005 and 2008 this was an Enterprise Edition only feature.  As of SQL Server 2008R2 it is available in Standard Edition.  To make using this compression the default for all of your backups, simply run the code below on your SQL Server.  Then, when you get to this option in the SSMS GUI, just leave it set to “Use the default server setting.”  You’ll want the space savings that compression offers.  Why use more space on your separate storage for backups than is necessary? I mean, you are storing your backups somewhere other than on the SQL Server, right?!

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Once you work through these meu options, simply click “Ok” and SQL Server will make the backup for you.  You can also click on the “Script” option at the top middle of the wizard to have SQL Server show you the T-SQL it is about to run.  You could save this as an example script to review later.

Method 2: Using T-SQL To Make a SQL Server Backup

T-SQL is a tried and true method for backing up databases.  You have more options available when backing up with T-SQL versus the GUI.  Most of those options are going to be more advanced options. A very basic example of the backup command that produces a full SQL Server backup is below. That is followed by examples for a differential backup and a log backup.

BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Full.bak'

BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Differntial.bak' WITH DIFFERENTIAL

BACKUP LOG [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_log.trn'

Buffer Count and maxtransfersize are two options worth noting. You can experiment with these T-SQL options to take backups faster. The buffer count value conrols how many I/O buffers are used for processing the backup and the maxtransfersize controls how much data is moved at any one time.

Below I’ve provided three examples from my testing on my home lab PC. The initial buffercount and maxtransfersize data was captured by turning on trace flags 3605 and 3213 then looking in the error log after the first backup was taken.  After that I simply experimented with values.  Be aware that increasing the buffer count too much can cause an out of memory error.

As you can see, the initial throughput was 219.412 mb/sec and the elapsed time for that part was 39 seconds. This was using SQL Server defaults.

Increasing the buffer count to 8 increased the throughput to 258.653 mb/sec and the elapsed time dropped about 6 seconds. Combining the second change with a maxtransfersize of 4MB increased the throughput to 270.095 and pushed the time down another 1.4 seconds. I shaved 8 seconds off the backup time.  This was for a small database of about 14 GB.  For larger databases the increased throughput could represent a significant time savings.

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
/*
buffercount = 4 and maxtransfersize = 1024kb
no options
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 39.041 seconds (219.412 MB/sec).
The backup set on file 1 is valid.
*/

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM, BUFFERCOUNT = 8
/*
Buffer Count = 8
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 33.118 seconds (258.653 MB/sec).
The backup set on file 1 is valid.
*/

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM, BUFFERCOUNT = 8, maxtransfersize = 4194304

/*
Buffercount = 8 and maxtransfersize = 4MB
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 31.715 seconds (270.095 MB/sec).
The backup set on file 1 is valid.
*/

 

Method 3: Using Powershell to Make a Backup

 

If you’re not using PowerShell with your SQL Servers, you should be.  If you’re not using the DBATools module with your SQL Servers, get it now. PowerShell can do fantastic, wonderful things and DBATools can do powerful, amazing things for you with respect to all things SQL Server. Below is a simple example of using the DBATools command Backup-DbaDatabase to make a full backup.  The command has a full range of options available, including backing up all the databases on a SQL Server when you don’t pass the -Database parameter. You should check into it right away.

Backup-DbaDatabase -SqlInstance MySQLServer -Database Stackoverflow2010 -path 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup'

 

Method 4: Using Maintenance Plans to Make a Backup

 

Because this post is already running long and there are a lot of other places that show how to use maintenance plans, I’m not going to step through how to do this here. You can go here and here to see how to use maintenance plans.  Also Jonathan Kehayias has a Pluralsight course on using them.

I am going to give you some quick thoughts on using maintenance plans though. First, Maintenance Plans are another GUI method to setting up backups.  In that respect, they are an easy point and click method to handle taking backups and handling backup retention, which we haven’t even talked about. Secondly, because of the nature of this method, which allows you to pick Backups as an option for a plan and then steps you through each part of a workflow wizard, Maintenance Plans can be a common approach for IT professionals coming from the SysAdmin side of things. No need to know or understand the options presented in the SSMS Backup wizard, for example.

Some downsides exist in backups inside maintenance plans prior to SQL Server 2016. Namely, the below options don’t exist.

– Perform checksum
– Continue on error
– Block size
– Max transfer size

Method 5: Using Ola Hallengren’s Maintenance Solution to Make A Backup

 

You can get help with Ola’s scripts here. Ola’s scripts are known and used world wide by DBAs.  They just work.  When you go to his page, you’ll download the file maintenancesolution.sql.  That file contains not only the backup routine but routines for CHECKDB and index maintenance.  You can download each components scripts individually as well. On his support page for the backup script, he explains all the available options and provides a laundry list of real world examples for how to set up the scripts for various scenarios.

Ola has options for the buffer count and maxtransfersize I demonstrated earlier.  He has options for using his scripts with AlwaysOn Availability Groups and he has options for backup retention. His scripts even work with third party vendor applications like LiteSpeed and Red Gate SQL Backup Pro.

What to Do Next:

  1. Review your current backup strategy and look for servers that aren’t being backed up at all.
  2. If you are a SysAdmin or an “Accidental DBA”, look at those links about setting up maintenance plans and get busy backing up your servers.
  3. If you didn’t know about DBATools, then stop what you’re doing and go to their web page.

5 Types of SQL Server Backups

One of the foundational responsibilities of a DBA is making sure that backups are available.  SQL Server has several foundational types of backups that can be made. Before I dive into that though, I want to ask a question that seems to have a fairly obvious answer.

Why take backups?

  1. You want to be able to recover from data corruption or mistakes made when people make changes directly to the data – like when they write and run a script in SSMS, outside of an application that’s using a set of tested stored procedures.
  2. You want to be able to refresh you non-prod environments so developers have data that looks like prod  to develop against.
  3. You want to have backups so you can test whether you can restore and how long it will take
  4. You want backups as they are often an essential part of the migration process from one SQL Server to another – usually newer SQL Server, or to Azure.

SQL Server Full Backups

What does a full backup accomplish?  A full backup, creates a backup of the entire database. It is all-inclusive, containing the entire database structure and all the data.  When a full backup starts, the Log Sequence Number is recorded and when the full backup completes, the Log Sequence Number is recorded.  This Log Sequence Number is the mechanism used by SQL Server to know what order INSERTS, UPDATES or DELETES occurred in.  As such, having the beginning and ending Log Sequence number recorded as part of the full backup allows for a transactionally consistent backup because the full backup is aware of the changes that took place while the backup was being made.  This allows for recovery of those transactions during the restore process. This full backup serves as the base backup, or starting point for all differential and log backups after it.  Each time a new full backup is taken, a new base backup point is created.

SQL Server Differential Backups

What does a differential backup do for you?  A differential backup works off the concept of a differential bitmap.  SQL Server is composed of eight kilobyte pages. These pages are collected in groups of eight called “Extents.” There is a bitmap that is used to mark any Extents that have experienced data changes since the last full backup. When a differential backup is executed, it is this bitmap that determines what data is backed up.

Based on the size and rate of change in a database, differential backups are often going to complete faster than a full backup.  Additionally, the differential backups will be smaller in size since only changed extents are backed up, rather than the entire database.

When the full recovery model is used, the power of the differential can be very important.  The differential backup reduces the number of log backups that have to be restored. For example, if there is a full backup taken on Sunday night, and transaction logs every 15 minutes after that, when a DBA needs to restore the database to Thursday at 5:53 AM, she needs to construct a restore process based on the Full backup and every transaction log since Sunday evening!  That’s a lot of transaction logs to have to include.  With a daily differential occurring at 5 AM, the DBA now only needs to restore the full backup from Sunday evening, the one differential that was created at 5 AM on Thursday, and only the transaction log backups that occurred after 5 AM Thursday. Being able to skip over using all those log backups is powerful.

This makes for much simpler script creation and readability, and can save a lot of time if the DBA is trying to write this script manually.  You should have an automated process to create the backup script, but some DBAs don’t have that set up yet. We’ll talk about automating the backups script creation  later in the series.

SQL Server File and FileGroup Backups

What are File and FileGroup backups? Well to start with, this is a more advanced topic than these other backup types. As such, it is less likely that you will need this type of backup. However, knowing what your options are is always a good idea.

File and FileGroup backups allow a more granular backup, and by extension, a more granular restore process. It does what it sounds like.  A file or filegroup backup backs up an individual file or a collection of files contained in a filegroup. This allows for restoring a small part of a database to fix a problem rather than having to restore the entire database. This option, however, does introduce complexity and, in my opinion, is really only useful for large databases, particularly in the range of 500 GB or more.

These file and filegroup backup types can be used when a larger database has been divided into multiple files.  For example, a large database containing sales information might be divided into various Sales files, perhaps by year, or even by month if there are a high volume of sales recorded in the database.  These files are then aggregated into FileGroups. See below for an example of creating files and filegroups in a database. This example was taken from the  MS docs page here.

USE master;
GO
-- Create the database with the default data
-- filegroup, filestream filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
  ( NAME='MyDB_Primary',
    FILENAME=
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',
    SIZE=4MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),

--Here is where a separate file is created and assigned to a FileGroup, which is little more than a container.
FILEGROUP MyDB_FG1
  ( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),

--Here is where another separate file is created and assigned to a FileGroup.
  ( NAME = 'MyDB_FG1_Dat2',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB)
LOG ON
  ( NAME='MyDB_log',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB 
  MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
  ( cola int PRIMARY KEY,
    colb char(8) )
ON MyDB_FG1;
GO

SQL Server Transaction Log Backups

What is a transaction log backup? A transaction log backup will take a backup of SQL Server’s transaction log so that data changes stored there can be recovered during the restore process.  Again the Log Sequence Number concept mentioned earlier is key.  Because transactions occur in a certain order, the log backups contain that order of transactions.  Transaction log backups must be restored in order.  If you try to restore a sequence of log backups in the wrong order, SQL Server will generate an error.

Having these log backups allows you to do what is called a point in time restore.  Having the option for a point in time restore can be critical for a business. Let’s suppose someone is making changes directly to one or more tables.  If a mistake is made and an unexpected data change occurs, point in time restore will allow you as the DBA to recover the data up to the moment just before the erroneous change was made.

SQL Server Copy Only Backups

What are Copy-only backups?  Copy only backups are backups that do not affect or track the Log Sequence Number information.  Why would you use this? Well, let’s suppose that you are already taking full, differential and log backups.  You are asked to provide a backup file of a database to another department or perhaps just a single developer who is working on a project. That Developer, hopefully, doesn’t have access to the backup directory for your backup processes.  Rather than you trying to copy a set of backups to a share or other location that the Developer does have access to, you could simply create a Copy Only full backup that is written to a shared location.  The Developer can then use that backup to restore locally to her machine, and you receive the benefit of not changing the base backup for your backup chain.

What to Do Next

  1. Examine your environment to see if you are leveraging the “Power of the Diff” by taking differential backups to help simplify your recovery process.
  2. Consider the size of your databases and determine if you have a situation that might benefit from having a database with multiple files and filegroups.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

Lessons Learned Updating to SQL Server 2017 AlwaysOn Availability Groups

In late 2019, a project that had been in progress for well over a year finally came to a conclusion.  I had collaborated with a number of people to migrate a stand alone SQL Server 2008 and two stand alone SQL Server 2008 R2 instances.  Each stand alone SQL Server was migrated to a three node Availability Group. Here are a few things learned along the way.

Automatic Seeding

First, consider using automatic-seeding if you are migrating to AlwaysOn Availability Groups.  I hadn’t previously worked with anything newer than SQL Server 2014 so I was unaware of this feature.  It is new as of SQL Server 2016. In this case, the old SQL Servers were migrating up to SQL Server 2017, so this option was available to us.  For a couple of months leading up to the migration event there had been various tests involving the set up of the AGs.  One of those tests involved restoring a full backup followed by one or more log backups over on the secondary nodes.  As the migration date approached, we started experimenting with auto seeding.

The auto-seeding feature streams the database from the primary to one or more secondaries behind the scenes.  This is accomplished using the endpoints created during the Availability Group creation process. This feature can eliminate the need to create scripts to handle the restores of databases over to secondary nodes in the AG.  

There are four ways to monitor the process of the streaming of the databases to the secondary nodes.

  • First, the SQL Server Error Log makes entries about the process.
  • Second, various DMVs contain information about the status and progress of automatic seeding. 
  • Third, Backup history tables in MSDB can be queried.
  • Fourth, Extended Events can be used to monitor the progress.  We chose to query DMVs during the migration.

Handling Connection Names

Second, ponder how to handle the scenario where connection names for applications change. As it turns out, a lot of meaning and importance is attached to names.  The computer and SQL Server instance names we were dealing with were deeply ingrained in the business. The names had been around for years and were used everywhere in connection strings.  If we migrate to new SQL Server instances, how do we handle that? 

One option is to create an alias in DNS that points to another location. Several months before the actual migration, we changed the computer names for each of the stand alone SQL Servers. The Infrastructure team used the previous computer names as aliases that then pointed to the newly named computers. This does require a change inside SQL Server, by the way.  SQL Server is aware of the computer it is installed on, so if you do this, know that you have to make a change in SQL Server as well. 

Let me explain the concept by way of example. Let’s suppose you have a computer called MyServerABC and it has a default instance of SQL Server on it.  This means that database connections also use the name MyServerABC to talk to the databases.  That connection name might be used in a lot of applications.  So that tens, or maybe even hundreds, of connection strings don’t need to change when the SQL Server is migrated, here is what you can do. During a maintenance window, change the name of the computer and make a change to SQL Server so it knows the new computer name it is on. Here is a link to describe how to do that in SQL Server and it has a discussion of other considerations when doing this sort of thing.

Let’s suppose the new computer name is MyServerXYZ. After the previous actions are taken, in DNS a new record is made called “MyServerABC”.  That alias is then “pointed” or directed to the computer name “MyServerXYZ”.  Now, applications that use MyServerABC as a connection name can continue using that connection name.  When the application makes a call to MyServerABC, that call is simply re-routed to MyServerXYZ and the application is none the wiser.

This exact change process worked for us early on.  Later, once we migrated the first stand alone SQL Server to an Availability Group, a person on the Infrastructure team changed the alias so that it pointed to the AG Listener instead of the old server we just migrated from.  Connections were then instantly routed to the new SQL Server 2017 AG. 

Database Migration Assistant

Third, the Database Migration Assistant is software from Microsoft that evaluates a SQL instance and the databases on that SQL Server for readiness to be migrated to a new platform. This tool can help migrate databases to Azure, but we were executing a migration involving on-prem to on-prem.  The tool provides an assessment of and information regarding scenarios in use in your environment that might block a successful migration as well as offering information on new features to leverage in the new SQL Server. 

We were able to find several things in our environment that had to be changed prior to the migration using this software.  Without the assessment, we would have migrated only to experience failures of various kinds in the new environment.  

Planning Software

planning

Fourth, use planning software of some kind so the migration steps are known and progress can be tracked.  One of the things I chose to do was plan out each step and create an electronic project board for all the things that needed to happen the day of the migration.  I had been using Office 365’s Planner at that time, so I created my steps there.  One of the Developers converted that to Trello because that is what the Dev teams were using at the time, and the Devs were at the office with us to help with testing applications post-migration.  The Trello board was made visible on a couple of large, wall mounted TV’s for everyone to see.  This allowed everyone to know what had been done, what was being worked on and what was next.

Communication

Fifth, think carefully about how the migration team will communicate during the migration.  (All in the same physical room? Company conference bridge while working remote? Conference call with a support vendor? Remote work platform like Teams or Slack? Some combination of these things?)

If you have a big room that will hold everyone, that might be a great option. Remember, this happened back in early December 2019, pre-covid. So, having a dozen or so of your favorite co-workers all in a room made sense.

Meeting together allows for easy, fast communication by simply turning to the person in the room to discuss something or ask a question. Collaboration is smooth and convenient in that scenario.  As I mentioned earlier, we leveraged a Trello board that was displayed on a couple of large TVs so everyone can see what has been worked on, what is currently being worked on and what is next.  

Be aware and mindful of the human tendency to talk when idle though.  If you get a dozen people in a room and only a few of them are busy at the moment, there could be a significant amount of chatter to block out for the people who are busy at that moment.

These days, now that the U.S. is experiencing an explosion of work from home due to the pandemic, you would probably set up something like Teams or Slack and use that as your collaboration platform so people can collaborate from home on migration day.  Platforms that allow for phone calls, screen sharing and quick collaboration among large groups of people would be essential in a large scale, high impact IT project. In those scenarios, the people collaborating still need to think carefully about communication so that people aren’t talking over each other while addressing the migration work. 

Well, that’s it for now.  I hope these points help you consider some things that maybe need to be included or discussed as part of your next IT project.  

Next Steps To Take

  1. Download and try out the Database Migration Assistant. 
  2. Check out Office 365’s Planner/Tasks app or something like Trello or Jira to help you not only with migration planning, but stayong on top of your every day workflow..
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

 

Querying An AS400 Using SQL Server 2019 Data Virtualization

 

For those who have taken an interest in the latest SQL Server release, SQL Server 2019, you may have noticed a new feature called Data Virtualization. Perhaps you’ve seen a presentation about it, or maybe you’ve read about it in Bob Ward’s book entitled “SQL Server 2019 Revealed.”

I myself was curious about this feature and so I set out to see if I could use it to connect to an AS400 iSeries machine. Tom Willwerth, my friend at StraightPathSQL, and I worked on this process together. This post is primarily about walking you through the steps we recently took to get this configured.

SQL Server Polybase and SQL Server 2019 Data Virtualization

Before I dive into that, why was I interested in this feature? What did I hope to gain? Well, first of all, there was definitely the motivation of wondering, “Can we get this to work?” Secondly, and more practically, the promise of SQL Server 2019 Data Virtualization is to make other data sources available without using a Linked Server and without the time it takes to develop an ETL process to move the data. On a related note, you can cut out the time it takes for an ETL job to actually move the data somewhere like a data warehouse or flattened tables for reporting. Third, the Polybase feature has a built in engine that can provide query performance not available via Linked Servers. Fourth, I wanted to provide a way for developers to query data in the AS400 without having to learn the different syntax required by the AS400 iSeries. Fifth, query writers can also join the external able to local SQL Server data.

How To Set Up SQL Server 2019 Data Virtualiztion

Now let’s move forward with the how of this process. I started by installing SQL Server 2019, and in that process there are some steps to select for installing the Polybase feature. If you’re not familiar with that, it came out in SQL Server 2016 and is the foundation of data virtualization. During the install process, SQL Server suggested a port range that it would use as part of configuring Polybase. I wasn’t sure if SQL Server knew that the suggested port range was open or not. As a result, I actually stopped to download the SysInternals tool called TCPView and run it on the server to confirm that the suggested port range was open.

After the SQL Server install completed, I ran the code below in SSMS to enable the Polybase feature.

exec sp_configure 'Show Advanced Options', 1
reconfigure
EXEC sp_configure 'polybase enabled', 1
reconfigure
EXEC sp_configure 'polybase enabled'

 

The next thing I did was create a database master key. This has to exist in order to encrypt the credential that will be stored in SQL Server later on in the process.

CREATE MASTER KEY WITH ENCRYPTION BY PASSWORD = 'S0meStr0ngP@55w0rd1234xYz!'

 

I then set my test database to compatibility level 150 for SQL Server 2019 and made my database scoped credential, like so.

ALTER DATABASE MyTestSQLDatabase SET COMPATIBILITY_LEVEL = 150

CREATE DATABASE SCOPED CREDENTIAL MySQLCred WITH IDENTITY = 'MYAS400CRED', SECRET = 'S0m3Str0ngP@55w0rd';

The value for IDENTITY corresponds to a user in the source database, in this case, the AS400. SECRET, of course, is the password for the user name stored in IDENTITY.

I believe after this Tom joined the process and we began together by installing version 1.8 of the 64 bit iSeries Client Access driver for Windows onto the SQL Server instance I was testing from. Going into the details of that are a bit beyond the scope of this post so I won’t describe the process here.

Creating an external data source was the next part of the journey. Syntax for that is here.

After a lot of wrangling, searching, and looking here for an AS400 connection string example, Tom and I arrived at this code sample that we ran successfully. Obviously I’ve changed some details to anonymize this, but you get the idea.

CREATE EXTERNAL DATA SOURCE MYAS400DEV WITH (LOCATION = 'odbc://MYAS400DEV.LeeMarkum.com', CONNECTION_OPTIONS = 'Driver={iSeries Access ODBC Driver}; System = MYAS400DEV'; ServerNode = MYAS400DEV.LeeMarkum.com', PUSHDOWN = ON, CREDENTIAL = MySQLCred);

Next up we took the optional step of creating a schema called EXT for holding our external tables. Afterward, we moved on to the business of actually making our first external table. This was very much a multi-step, trial and error process.

Here is the process we eventually found that worked:

  • Get SQL definition from AS400 via the iSystem Navigator, minus trigger definitions, and paste into SSMS
  • Remove any index or key definitions. Without this change SQL Server throws an error
  • Remove any defined defaults. Without this change SQL Server throws an error
  • Change the CHAR data types to NCHAR. Without this change SQL Server throws an error
  • Replace DB2 schema name with EXT, our previously created SQL Server schema.
  • Change CREATE TABLE  to CREATE EXTERNAL TABLE
  • Add this at the bottom: WITH ( LOCATION=’MYAS400DEV.SomeSchemaName.DB2TABLENAMEHERE’, DATA_SOURCE= MYDEVAS400)

This left us with code like the below:

CREATE EXTERNAL TABLE EXT.Customer 
(CUST# NUMERIC(7,0)NOT NULL,
CUSTNAME NCHAR(30) NOT NULL,
CUSTADDRS NCHAR(50) NOT NULL,
CUSTCITY NCHAR(30) NOT NULL,
CUSTSTATE NCHAR(2) NOT NULL,
CUSTZIP NCHAR(2) NOT NULL
)
WITH ( LOCATION='MYAS400DEV.SomeSchemaName.DB2TABLENAMEHERE', DATA_SOURCE= MYDEVAS400)

As part of this process, we had to keep the same column names as what existed in the AS400. Otherwise, an error was thrown, and this makes sense given that we’re looking at the AS400 table in the WITH (LOCATION = ”, data_source = ) part of the syntax.

After this step there is the optional step of creating statistics on columns of the virtual table. You might want to do this, for instance, on columns that might be involved in joins as a performance enhancement. For the purposes of this work, I skipped this step for the time being because Tom and I just wanted to see some query results from all this work! We could return later to this item to try to tweak performance.

The moment of truth had finally arrived. We loaded up a query against our EXT.Customer table in SQL Server 2019 and, to our delight, we received the expected result set back using standard T-SQL.

I’m not gonna lie, this was a fair amount of work and took some time to set up. However, I’m hoping this blog post, and others like it, will save you some pain and time.

Next Steps To Take

  1. Get a copy of SQL Server 2019 Developer Edition and install it and the Polybase feature. Then enable Polybase.
  2. Identify a data source in your environment that you would like to use data virtualization to query. It could be Oracle, Microsoft Access, PostGres, MySQL or any number of things.
  3. Look through the available documentation on the connection strings for the data source you’ve chosen and figure out what the connection string should look like.
  4. Look through the rest of the walk-through in this article and give the feature a try.
  5. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

SQL Server Install Guide Part 3

I’m in part 3 of a series on installing SQL Server.  In the previous post I began discussing steps to take after the install is complete.  Because there are quite a number of things to consider post-install, this part 3 will continue discussing the post-install configuration items.

Configure SQL Server Alerts

One of the things I always do after installing SQL Server is set up SQL Server Alerts.  This is a free, easy way to find out about problems on the SQL instance, like read retry errors or other potential nasty issues.  There are probably a number of places to find scripts useful for this task.  The one I recommend is from Glenn Berry at SQLSkills.com.  You can find his script at this link.  One of my favorite things about Glenn’s script is that it has very well defined alert names that allow the DBA to easily know what server is involved when an alert is sent.

How Should MAXDOP be configured?

Next up is setting MAXDOP.  The foundational advice from Microsoft concerning Max Degree of Parallelism is in KB2806535, found here.  The summary of this article is that if you have a single NUMA node and less than 8 logical processors, then keep MAXDOP at or below the number of logical processors.    If you have multiple NUMA nodes and less than 8 logical processors per NUMA node then keep MAXDOP at or below the number of logical processors per NUMA node.   If there are more than 8 logical processors per NUMA node, keep MAXDOP at 8.

So, how do you tell how many NUMA nodes there are on the machine?  I’m glad you asked!  There are several ways to determine this information.  I will refer the reader to a post by Denny Cherry on the various methods.

Setting Min/Max Server Memory

MIN/MAX server memory should be reviewed as part of your post install configuration items.  The MIN Server Memory setting is the minimum memory your server will allocate to SQL Server.  The trick to understanding this is that SQL Server does not automatically grab that minimum value of memory upon start up.  The memory used by SQL Server increases gradually after start up, assuming a steady workload on the instance.  Once it crosses the value set in this MIN Memory setting then it does not give that memory back to the OS.

Setting the MAX Server Memory setting will prevent SQL Server from taking so much memory that it starves the OS.  I typically leave 4-6 GB of RAM free to the OS and have not had problems with memory pressure using that guideline.

I will say though, that the machines I work with are dedicated to SQL Server, and almost always have just the database engine installed on them.  If you have other SQL Server components on this same box, or application components from one or more applications then 4-6 GB left to non database engine components probably is not enough.  Definitely monitor the Available Megabytes Perfmon counter over time so you can see how much free memory the machine has and adjust the Max Server Memory setting for SQL Server as needed.

To keep this series to a reasonable length, I’m simply going to list the next few items I have on my own personal Install Guide that I use.  The series wasn’t meant to be an exhaustive explanation of everything one would put on an install guide anyway.

The guide I use is in Excel format and I go through each item on it as I work through the process.  I mark off what I’ve done and then I save the guide.  This helps me to ensure that I’ve covered all the steps and it gives me and other people a record of what I did during the process.  Some of these steps can be entire blog posts on their own, or even an entire series written on that one step, so summarizing them here and expanding on them in later posts will be my approach.  Ok, so here’s the remaining list.

  1. Ensure Windows power plan is on high performance
  2. Configure tempdb appropriately. (Google this and do research.  Here are a couple of starting points.  See this and this.
  3. Set up a DBA management database. (This is a database on each instance that has key procedures and objects, like sp_WhoIsActive, sp_Blitz, and any other custom scripts you like to use for troubleshooting or diagnostics)
  4. Enable Query Store (SQL Server 2016 and higher)
  5. Update compatibility level of databases to newest level. (This assumes that the new cardinality estimator was tested in a Dev and test environment prior to this migration or new install)
  6. Make sure database backups are occurring as expected.
  7. Consider enabling automatic plan choice correction (SQL Server 2017)

So, what are some of your steps for installing and configuring SQL Server?

SQL Server Install Guide Part 2

In part 1 of this series, I discussed pre-installation steps.  There are many guides available online for the actual installation process so I’m not going to give a step by step installation guide here.  Rather, I’m going to focus on what to think about after the install is complete.

Recent versions of SQL Server will now go out and download additional content for you before the install actually happens so you’re not left on the base, RTM release of the product.  However, one thing to consider post installation is still, are you on the latest patch?  How do you know?  Where can you go to compare the version number of the SQL Server you installed to what is currently available?  Well, you can look back at this post to answer that question.

Once you’ve determined whether or not additional patching is needed and completed that, then what?  Well, there is a lot of post install configuration still to do.  For example, do you need to migrate Linked Servers, SQL Agent jobs, SQL Operators?  What about setting up database mail or moving Logins?  Each one of these can take up a lot of time.  What if there was a quick way to handle those things?  Enter DBATools.

DBATools is a fantastic collection of hundreds of PowerShell commands that have been developed and tested by users worldwide.  With a simple command, linked servers can be migrated, with their password information intact. The same with SQL Server logins and their passwords and permissions.  Database mail? No problem!  In fact, if you call Start-DBAMigration and pass a source and target SQL instance, then you can migrate the entire instance in a single command.  DBATools is fantastic for ongoing maintenance tasks and a bunch of other things too so be sure to check out all that it can do for you as a DBA.

The next item on my install guide is trace flags.  Trace flags control the behavior of SQL Server in a variety of ways.  As one example, trace flag 3226 controls whether successful backups are logged in the SQL Sever error log.  Setting this flag prevents those “backup successful’ messages from cluttering up your error log.

The important thing to remember if you’re doing a migration is that you most likely want the new SQL Server to behave in the same way as the old one.  To find out what trace flags are globally enabled on a SQL Server, run the following code.

DBCC TRACESTATUS(-1)

If you want a list of trace flags and what they do, those lists do exist but they are sometimes hard to find.  Here is one such list that tells you whether you should be concerned about a particular trace flag being enabled or not.

If you’re starting from scratch with the database and application you’re installing, be sure to find out if the vendor has any recommendations and why they recommend those particular trace flags.  Also, do your own research about trace flag functionality and see why you personally may or may not want to use a particular flag.

 

SQL Server Install Guide Part 1

In part 1 of a series on installing SQL Server, I’m going to discuss things to do prior to running the installation program for SQL Server.  There are a number of install guides for SQL Server on the internet and plenty of recommendations around installation best practices and how-to’s.  This will be what I’ve arrived at over the course of time.

Working with the Database Migration Assistant

This first step I’m going to introduce makes the assumption that this install is going to be for one or more databases that you’re planning to migrate from an existing instance.  My first step is to run the Database Migration Assistant from Microsoft.  This product is free and can be installed away from your SQL Servers.  I run my install from my laptop.  After opening the UI and starting an assessment, this tool will scan the source databases selected and determine if there are any breaking changes, behavior changes, or deprecated features between the source and target for your migration.

The Database Migration Assistant will also do two additional things in regards to migrations.  First, it will make suggestions on new features that may be advisable to use in the new instance.  Second, the software can also actually do the database migration for you.  That includes migration to a recent SQL Server version on premises and migration to Azure.

Naming Your SQL Server

The next item on my list is a relatively easy one to handle, but it’s still worth at least some forethought.  The database instance needs a name.  For a default instance, the computer name will be the instance name.  Which means that in some cases your name is decided for you by a System Administrator.  If you have input into the name, try to use a logical naming convention that will have some longevity.  In some environments you might have a convention like “Application” + “Purpose” + “Location” + “some number” such that a machine name might be CitrixWebSTL01 for a Citrix web server in Saint Louis.  I would recommend something similar to this so that when the machine name is viewed the person knows exactly what it is for.  Going generic, like SQL01, SQL02, SQL03, etc. just doesn’t say much about what the SQL instance is for.

Creating Service Accounts for SQL Server

The third item on my install guide is to identify or create accounts for the SQL Server modules you plan to install.  For instance, if you’re installing the database engine, utilizing SQL Agent and maybe installing SSIS on the box, then you really should use three different accounts with different passwords, one for each service that will be installed.  Doing this will help keep the machine and the environment more secure.  If you use one account for every service and that one account is used on every SQL install, you’ve now made your entire environment vulnerable if that username and password is ever compromised.

Standardize The Drive Letters

Fourth, identify and agree upon drive letters for files.  If the machine is physical and you can have some influence over what is used, be sure to separate mdf from ldf files and be sure to get TempDb on its own drive.  Doing these things will help overall performance as each of these file types has its own usage pattern.  Mixing those files types on the same drive is going to hurt performance.

If the environment is virtual and so a SAN is involved, I would still encourage you to have a scheme for where the files go because it makes administration easier. Also, having a consistent design across all servers helps when it comes time to restore databases from one place to another.  No more pesky WITH MOVE statements in your RESTORE DATABASE scrips if across the environment there is a consistent way drives and files are laid out.

Consider Enabling Instant File Initialization

Enabling instant file initialization is the fifth item on my SQL Server install guide.  Instant file initialization is a performance enhancing option that will really help with data file growth times and restore times.  It allows SQL Server to claim space on a drive without zeroing out the data that might be on that part of the drive.  This means that the data is still retrievable by a skilled hacker until SQL Server writes over the data. However, there are many layers of security to be bypassed before that would be possible and there are substantial benefits to instant file initialization. Check out a post by Kimberly Tripp on the subject.  Instant File initialization is a multi step process prior to SQL Server 2016, but now we have a handy check box.

Next Steps To Take

  1. Download and use the Database Migration Assistant to assess your SQL Server.
  2. If you don’t know what the naming conventions are for computers in your environment, talk to one or more SysAdmins and find out.
  3. Do some research on instant file initialization.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

Correlating IO Wait with Queries

Have you ever looked at sys.dm_os_virtual_file_stats and saw horrible latency for your database files? Generally speaking, you want to see latency below 20 ms on your database files.  much above that and there are some problems somewhere that need some tuning.

But, how do you know what queries are causing your issue? What’s causing those IO waits?

How to Find Queries Associated with Pending IO Requests

I responded to a question on SQLServerCentral.com about performance that is related to these types of scenarios. The poster wanted to know if there was a way to correlate pending io requests with the queries that were experiencing the waits using the DMV sys.dm_io_pending_requests.

This was a well timed question that got my attention because I was working on my own scenario with a SQL Server and IO issues.  I had set up a custom data collector set in perfmon and analyzed the data.   The data showed very high Avg. Disk Queue Length, low Page Life Expectancy and high Page Reads/sec for sustained periods of time.  By correlating all this data together, it is easy for you to see that I had one or more queries hitting the disk to retrieve data to load into memory and causing pages to be flushed out. The forum question, and my own scenario, sent me off to do additional investigation and I arrived at the query below.

Reviewing SQL Server IO Latency

SELECT    
ipir.io_type, 
ipir.io_pending,
ipir.scheduler_address, 
ipir.io_handle,
os.scheduler_id, 
os.cpu_id, 
os.pending_disk_io_count,
ER.session_id, 
ER.command, 
ER.cpu_time, 
ST.text

FROM sys.dm_io_pending_io_requests ipir
INNER JOIN sys.dm_os_schedulers os ON ipir.scheduler_address = os.scheduler_address
INNER JOIN sys.dm_exec_requests AS ER ON os.scheduler_id = ER.Scheduler_id
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST

I began by looking at sys.dm_io_pending_requests in BOL.  I noticed that this DMV had Scheduler_Address as one of its outputs.  I quickly realized I could join to sys.dm_os_schedulers on the scheduler_address and then make an additional join to get to sys.dm_exec_requests.  From there I was only a CROSS APPLY away from getting the SQL statement associated with the IO waits.

Sys.dm_os_schedulers has its own column that keeps track of pending IO called “pending_disk_io_count.”  As a result, you could eliminate sys.dm_io_pending_requests from the above query if you were truly interested only in the pending io count.  However, using both DMV’s does provide a more detailed picture.

Performance Tuning IO Waits

When doing performance tuning, keep in mind that observed issues may only be symptoms.  In my case, after reviewing execution plans and data from SET STATISTICS IO ON, I could see that the high IO wasn’t due to poor performing storage, but queries that were performing millions of physical reads.  I examined the query plan and could see that an index was missing.  I applied the index and the physical reads dropped to almost zero.  I used the results from the above query to bolster my case to the application vendor that the index was needed because extremely high physical IO was killing the server.

Try out these DMV’s and see what other interesting gems you can get from them.

Next Steps to Take

  1. If you want to track queries causing IO Latency, you could create a table to hold the output of the query in this post and schedule this query to run in a SQL Agent job on an interval. Then examine your table over time.
  2. Be sure to review execution plans related to these queries to look for other performance tuning  opportunities.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

Consolidate Multiple Email Operators

Suppose you’ve been working on cleaning up SQL Agent jobs and their email operators and find that you have more than one operator with the same email address. How would you tidy this up to ease administration?

Let’s do a little setup to demonstrate the scenario.  The T-SQL below will create three operators with different names but the same email address.  Then it updates two of my existing test jobs to use those email operators when the job fails.  I am also logging failures to the Windows Event Log.

USE [msdb];
GO

EXEC msdb.dbo.sp_add_operator @name=N'DBA', 
 @enabled=1, 
 @pager_days=0, 
 @email_address=N'SuperHumanPerson@MyDomain.com'
GO

EXEC msdb.dbo.sp_add_operator @name=N'Super Human Person', 
 @enabled=1, 
 @pager_days=0, 
 @email_address=N'SuperHumanPerson@MyDomain.com'
GO

EXEC msdb.dbo.sp_add_operator @name=N'SQL Server Team', 
 @enabled=1, 
 @pager_days=0, 
 @email_address=N'TheDBADistroGroup@MyDomain.com'

Now I will run this simple query to find out what my email operators and email addresses are.

USE msdb;
GO

SELECT id
, name
, email_address
FROM sysoperators

Here is the result set.

How does this sort of thing happen, you ask.  Well, it doesn’t just happen in contrived blog posts!  Suppose there is a fair amount of turnover in a DBA environment and the DBA’s are not always checking what the last person did.  Each one after another goes in and does her own thing.  Voila!  You end up with duplicate email addresses for email operators.

Some jobs have “DBA” as the email operator.  Some have “Super Human Person” as the email operator.  You might even have a couple of “test” operators in their with “SuperHumanPerson@Mydomain.com”.

Maybe the DBA didn’t think about the fact that he might not always be the DBA and so it might be better to set up an email distribution group that was controlled by the Exchange person.  That would mean that as DBA’s came and went the same email operators could be used in SQL Server. Who knows! But I have seen this.  So what now?

sp_delete_operator to the rescue!

Here is our example.

EXEC sp_delete_operator @name = 'DBA', @reassign_to_operator = 'SQL Server Team';
GO
EXEC sp_delete_operator @name = 'Super Human Person', @reassign_to_operator = 'SQL Server Team';

So what this does is it deletes the email operator in the @name parameter and assigns all jobs that had the @name operator to the operator assigned to the @reassign_to_operator parameter.  Now when you run the second T-SQL snippet from above against the sysoperators table in msdb, you get back just the operator name “SQL Server Team.”