SQL Server and Other Topics


I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver

I'm at a client that uses SQL Server Database Mirroring for their high availability solution. The principal and mirror for the databases happen to have the data file on two different drive locations (i.e. they don't match). Why is this important when you create a file? Easy, the CREATE FILE command is a transaction that is sent to the mirror.

 Run the CREATE FILE T-SQL or add a file from SSMS and you'll have an ugly situation on your hands.

 Suspended? What this suspended status about? Oh, no, you've just made a mess.

 Error log from the mirror server:

2015-04-08 12:29:18.94 spid30s     CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'E:\SQLData\StupidFileBrokeMe.ndf'.
2015-04-08 12:29:18.94 spid30s     Error: 5123, Severity: 16, State: 1.
2015-04-08 12:29:18.94 spid30s     CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'E:\SQLData\StupidFileBrokeMe.ndf'.
2015-04-08 12:29:18.94 spid30s     Error: 1454, Severity: 16, State: 1.

So lesson one when adding a data file, know where your data files are on both principal and mirror. If you've gotten to this point you're going to have to remove the mirror and re-add it from the beginning.  Not only that, but it is going to require a SQL Server service restart. You can't drop the mirror, you can't bring it online, you can put it into emergency... you're gonna have to trick the system. I actually had to shutdown the service, delete the data files. When the service started up I was able to drop the database. It turned out to be a bigger pain than I thought it would be just to demo it for you. Thank goodness I didn't have this happen to me in production.

So, the client was smart enough to already know the data files were on different drive letters and paths. Bonus! Now we can really work some magic.

 So, let's look at what really needs to happen and save us from having to go through the effort of dropping the mirror and re-adding the mirror from scratch. I certainly don't want the production environment unprotected during the massive ... across WAN ... copy of the backup to the DR site.

The process is as follows:

  1. Remove Mirror
  2. Add File on "Principal"
  3. Backup Transaction Log
  4. Restore Transaction Log to "Mirror" copy WITH MOVE to the new location of the data files.
  5. Add the mirror partner and witness (if it exists)
  6. Set safety off if asynchronous.

So easy right? Well... the hard part is making sure every step is done correctly... time to look for that old tool... you know the one you may never have used because you didn't quite understand it. Ever used SQLCMD mode in SSMS? Yes, this is the magic.

 Like many of you DBAs out there it took me years to see SQLCMD as the tool it really was. Getting others to find the magic has taken almost as long.

I recommended it as a tool to implement the data file addition, and the DBA got a look in his eyes, almost a fear that it would take too long to learn. 

I whipped out my cape and pulled out the old wooden toolbox, and started digging through the tools... and buried deep in the bottom was this bright shiny tool that was all smooth and balanced and comfortable to use. It was very simplistic in its usage.


For SQLCMD, start with the easy basic commands:


:SETVAR var "variable value"

There is enough to munch on for years in just those two SQLCMD commands. Look how simple this script is, but how much easier than writing multiple scripts connected to several servers. We can change our connections back and forth between principal and mirror server (don't forget your GOs).



:SETVAR prisrv "SERVER01" 
:SETVAR mirsrv "SERVER02" 
:SETVAR filegroup "JOB" 
:SETVAR phyfilename "TST_SWITCH_NEW_FILE.ndf" 
:SETVAR priloc "E:\SQLData" 
:SETVAR mirloc "C:\SQLServer\Data" 
:SETVAR backuploc "\\server02\Backups\TST_SWITCH.trn" 
:SETVAR filesize "204800KB" 
:SETVAR filegrowth "204800KB" 
:SETVAR mir "TCP://SERVER01.domain.com:5022" 
:SETVAR pri "TCP://SERVER02.domain.com:5022" 
:SETVAR wit "TCP://SERVER03.domain.com:5022" 
-- Remove Mirror, Add Data File, backup logfile 
:CONNECT $(prisrv) 
USE [$(db)] 
ALTER DATABASE [$(db)] ADD FILEGROUP [$(filegroup)] 
NAME = N'$(filename)',  
FILENAME = N'$(priloc)\$(phyfilename)' ,  
SIZE = $(filesize) ,  
FILEGROWTH = $(filesize) )  
TO FILEGROUP $(filegrowth) 
BACKUP LOG [$(db)] TO DISK = '$(backuploc)' WITH INIT 
:CONNECT $(mirsrv) 
FROM DISK = '$(backuploc)' 
MOVE '$(filename)' to '$(mirloc)\$(phyfilename)' 
:CONNECT $(mirsrv) 
ALTER DATABASE [$(db)] SET PARTNER = '$(mir)' 
:CONNECT $(prisrv) 
ALTER DATABASE [$(db)] SET PARTNER = '$(pri)' 
ALTER DATABASE [$(db)] SET WITNESS = '$(wit)' 


The key to this script is the ability to test the script in another environment to make sure it works. Be prepared to make mistakes, set up your test environment by creating a database and set up mirroring. It doesn't have to have data in it. Make sure the data files are on different file paths.

 If you tried to do this with the SSMS GUI it would be easy to forget a step, run a step out of order, get confused where you are. With SQLCMD, its all about the variables, the T-SQL code doesn't change.

The best part of this script, after helping the DBA write a script with SQLCMD to get comfortable with SQLCMD, I gave him my script with the SETVAR for each of the variables that would change. He was able to test it multiple times in a lower environment. When we was comfortable he modified the parameters to match production...and at 5:30AM (while I was sleeping), it ran flawlessly.It ran flawlessly for several reasons, 1) it was tested many times, 2) he was comfortable with the processes and 3) he was very careful to use the correct variables.



I covered the simplicity of SQLCMD and why it is a great tool to use for processes that need precise coordination and timing across multiple servers. It is important for you to push your comfort level to make your life easier. Most DBAs have more work than they can handle (its why I'm a consultant) so make your life easier and learn SQLCMD today.

I used the example of adding a new data file to a mirrored database where the principal and mirror used different drive paths for the data files. This script simplifies the implementation of adding a new data file to principal and mirror.