Creating Change Scripts

Creating change scripts is never a fun task for a DBA. That being said, nothing is more satisfying than a perfect deployment to four different environments. The only way to do this "perfect" deployment is to invest a lot of time in good script writing, like the one that will be shown shortly, or to invest in a tool.

There are more tools on the market than ever to help a DBA package and deploy the changes. For example, Red-Gate (http://www.red-gate.com) can compare two databases (test and production, for example) and package the change scripts to move production up to the same level. This same type of tool is available through many vendors such as ApexSQL, Idera, and Quest, to name a few. None of these tools eliminates human intervention entirely. You must have some interaction with the program to ensure that a change that was not meant to be deployed is not sent to production.

A last tool worth mentioning here is Visual Studio Team System for Database Professionals. This is the Microsoft product that aids in the complete change cycle. Visual Studio 2003 used to do this very well, but the deployment feature was removed in Visual Studio 2005 until the Team System version. At the time of the publication of this book, Visual Studio Team System for Database Professionals is still in very early beta. The tool should allow for change detection and simple deployment to environments. If you make a change to a column name, the tool creates a change script to change every stored procedure and view that referenced that column name.

Since this tool isn't generally available yet, let's go over the way you used to do this in Visual Studio 2003 and use SQL Server 2005 technology with it. Visual Studio 2003 had a great way to package all of your scripts and generate a command file to call the scripts. The command file was the key element that ensured a perfect deployment time after time. You can create the command file manually in Notepad or you can download the sample here, called Installv101.cmd, from www.wrox.com. We will break this script into more manageable chunks, but first here's the entire process:

 @echo off REM: Authentication type: Windows NT REM: Usage: CommandFilename [Server] [Database] if '%1' == '' goto usage if '%2' == '' goto usage if '%1' == '/?' goto usage if '%1' == '-?' goto usage if '%1' == '?' goto usage if '%1' == '/help' goto usage sqlcmd -S %1 -d %2 -E -b -i "Version100.sql" if %ERRORLEVEL% NEQ 0 goto errors sqlcmd -S %1 -d %2 -E -b -i "Version101.sql" if %ERRORLEVEL% NEQ 0 goto errors goto finish REM: How to use screen :usage echo. echo Usage: MyScript Server Database echo Server: the name of the target SQL Server echo Database: the name of the target database echo. echo Example: MyScript.cmd MainServer MainDatabase echo. echo. goto done REM: error handler :errors echo. echo WARNING! Error(s) were detected! echo - - - - - - - - - - - - - - - - echo Please evaluate the situation and, if needed, echo restart this command file. You may need to echo supply command parameters when executing echo this command file. echo. pause goto done REM: finished execution :finish echo. echo Script execution is complete! :done @echo on 

First, the lines with the following if statements ensure that the user has passed in the proper variables. If they neglected to pass in a variable, they are sent to a section of code called usage. Also, if the user passes in one of many switches such as /?, the user is sent to the same section. You can add many more checks for variables, but just keep in mind that variables are ordinal in nature. If you pass in a third variable, just refer to it as %3, and then pass it as the third option after calling the command file.

 if '%1' == '' goto usage if '%2' == '' goto usage if '%1' == '/?' goto usage 

The most important part of the script actually calls the subscripts. There would be two lines here for each script that you wished to call. In some cases, you could have 50 lines on large deployments. This is the script that has been modernized to use sqlcmd versus osql.exe. You pass in the variables for the server name and the database name. The -E switch enforces Windows Authentication. If you wished to use SQL Authentication, you could use the -U and -P switches. Using these two switches would cause you to have to create two new input parameters (%3 and %4) and write the error checks mentioned earlier.

In the following script, you can also see two input files being called. One is for Version 1.0 of the database and then the other is for 1.0.1 bug fixes. If any error is found in the script, the command file halts and calls the error routine. If the error level is not equal to 0, the error routine is executed.

 sqlcmd -S %1 -d %2 -E -b -i "Version100.sql" if %ERRORLEVEL% NEQ 0 goto errors sqlcmd -S %1 -d %2 -E -b -i "Version101.sql" if %ERRORLEVEL% NEQ 0 goto errors 

The error routine looks like the following block of code. The code will display an error message and stop the execution of code. The weakness in this code is that it's not transaction based. The DBA must code this inside each subscript called earlier. The problem there is that the transactions cannot not cross subscripts. So, if Script A successfully executed to purge the data but Script B failed to load the data, the data would remain truncated.

 :errors echo. echo WARNING! Error(s) were detected! echo - - - - - - - - - - - - - - - - echo Please evaluate the situation and, if needed, echo restart this command file. You may need to echo supply command parameters when executing echo this command file. echo. pause goto done 

This rather large script is a practical example if you must hand-code your solution. After doing this enough, you'll either become proficient at it or so tired of hand-coding that you decide to buy a tool. There is no wrong answer as long as you get your changes deployed without incident. Before executing this command file against the production database on the final deployment day, try to clone the database (if it is small enough) to ensure that you can execute the script against the clone successfully.

You can call the batch file by using the following syntax. Again, this syntax is only using two variables: the server name and database name. Typically, you will want to name your command files to match a database version number or application version number.

 InstallV101.cmd localhost adventureworks