Introduction

In being with a SQLServerCentral member for around three years now, I have found many articles useful, and with my DBA career now in full flight, I felt it was about time to contribute back to the community. In this article, I'd like to provide a solution to a common problem many production DBA's face: making after-hours deployments hands off and (hopefully) less confusing.

Deploying application DB changes to multiple databases, including multiple .sql files, mainly for the distribution of DDL and DML database changes in a way that is acceptable to our business owners, developers, and other stakeholders is something that we all need to do regularly as DBA's. Trying to make that process repeatable, intuitive, and giving us DBA's some much needed rest after-hours and on weekends is something this deployment method strives to achieve.

The tools/features used for this solution are: xp_cmdshell, SQLCMD, and the SQL Server Agent.

Background Information Related to Application Releases at my Organization

I work at a large organization that has grown significantly in the past year where we are now typically pushing at least 25-50 application releases a month, ranging from critical 24x7 applications to minimally utilized applications . To adhere to uptime agreements, we do a lot of these releases after business hours or on weekends; times of the week that we value as DBA's.

We initially had our developers provide us with a single script for each database for an application deployment. The problem we encountered with this method was that we would often receive one or two updates for each file as QA testing would reveal problems or items that were supposed to be included with the release but weren't.

Politics aside, on deployment nights, the DBA performing the release would often come across the question, "Do you have the updated release script?" Even worse, the "old" (and incorrect) script would get executed, and it would be difficult to get the delta of the old script versus the correct one. This led us to have the developers provide us with incremental update scripts (01.sql, 02.sql, etc), with each file building on the previous one(s). This led to less confusion since we may have executed 01.sql, but not 02.sql. To correct the mishap, it would be easy to get the 02.sql updates in place simply by running that script. Compared to the single file method of deployment, this helped alleviate confusion.

As for who handled the deployments from the DBA team, we used to handle our releases by assigning our on-call DBA to all after-hours and weekend-related changes, but this quickly became exhausting for whoever was on-call for the given week. With many of these releases consisting of fully-tested scripts, we needed a way to automate the execution of these releases. Since many of them consisted of changes to one or more databases or multiple changes to a single DB utilizing more than one SQL script, we needed a way to execute multiple scripts through an automated style of deployment. We also had a requirement to notify the application developers or release management team that the release was complete from the database deployment script, and provide them with the output.

Our Methodology Explained

We start out by asking the personnel generating the release scripts to group them by database, put "USE" statements atop each script, and number them in the order in which they are to be executed (ie C:\releases\2010\July\release_scripts\DeploymentTest1\01_DDL_Updates.sql for the first update to run against the DB DeploymentTest1). We ask them to provide the scripts this way so that it is easier for us to intuitively know what order the scripts should be executed and against which DB. The "USE" statements help ensure they are actually executed against the correct DB's since the context of the SQLCMD call is against "master".

Once the scripts are gathered, we then build out a template file (a .sql file utilizing sqlcmd syntax) to be called by SQLCMD that lists out each individual SQL script that needs to be executed and in what order. Once this is built, we then schedule a SQL Server Agent job to call SQLCMD with the input file being the template file just built and configure the output parameter of SQLCMD to generate the scripts' output so it can be sent to the application release team. We conclude the release by emailing the output file to the release team which let's them know that the DB updates are complete.

If anything unexpected arises, we let the application team know that our on-call DBA is available to assist if necessary so that the application team has a "safety net" available to them. With the scripts generally been proven to execute successfully, this "safety net" is not often used (hence the free time we gain).

Our methodology summarized:

• All scripts for the release are gathered and put into a centralized location for execution, sectioned off by database.
• A SQL Server Agent job is scheduled to run SQLCMD that executes the scripts sequentially from the centralized location and generates an output file
• The second step of the job emails the output file as an attachment to the release team.
• With any unexpected issues, the on-call DBA can be contacted.

The Breakdown of our Solution

To make our lives as DBA's easier, we developed the following solution to handle multiple-script, multiple-database updates for application releases:

*For this breakdown, we are including the files in a year\month style folder structure; it may be more pertinent to your environment to label by application and release version.

1. We gather each individual DB update script and place them into the locations listed below. These locations will be referenced in our template file.
   *Each script should have a "USE [dbname]" at the top of the script to change DB contexts since the SQLCMD context will be "master".

   a. C:\releases\2010\July\release_scripts\DeploymentTest1\01_DDL_Updates.sql
   b. C:\releases\2010\July\release_scripts\DeploymentTest1\02_DML_Updates.sql
   c. C:\releases\2010\July\release_scripts\DeploymentTest1\03_DDL_Updates.sql
   d. C:\releases\2010\July\release_scripts\DeploymentTest2\01_DDL_Updates.sql

2. We create a script file to be used as the query input script for sqlcmd. This script will be used as the template file referencing each of the individual scripts to be executed. Code sample below.
   *We name this file 01a_main_release.sql since 01a is a partner to 01_main_release.sql (below), but you can certainly use any name that makes sense for your environment.
   **This file uses commands only recognized by SQLCMD

   a. C:\releases\2010\July\01a_main_release.sql

   Code sample:
   :On Error exit

   --DeploymentTest1 updates
   :r "C:\releases\2010\July\release_scripts\DeploymentTest1\01_DDL_Updates.sql"
   :r "C:\releases\2010\July\release_scripts\DeploymentTest1\02_DML_Updates.sql"
   :r "C:\releases\2010\July\release_scripts\DeploymentTest1\03_DDL_Updates.sql"

   --DeploymentTest2 updates
   :r "C:\releases\2010\July\release_scripts\DeploymentTest2\01_DDL_Updates.sql"

   *":r" is a command recognized by sqlcmd to know a script is being passed in; DB context is changed with each script; each script should have a USE statement at the top.
   Reference: (http://msdn.microsoft.com/en-us/library/ms165702.aspx)

   b. If a last minute addition to the release comes in, it's easy to just add in a reference to the new script at the end of this file. For example, if a fourth script is needed for DeploymentTest1, it should build on the first three, then a reference to it can easily be added to the end of the template file.

3. Create a primary script file that will execute the sqlcmd call to the template file via the "-i" switch and generate an output file via the "-o" switch. The output file generated will be nearly identical to the output you'd receive within SSMS results pane, which is very useful for application release team members.
   *We name this file 01_main_release.sql since it is the first and only file that would need to be executed to kick off the solution. The contents of this file gets placed into the SQL Server Agent job "Test_Application_Deployment".
   Reference for sqlcmd switches (http://msdn.microsoft.com/en-us/library/ms162773.aspx)

   a. C:\releases\2010\July\01_main_release.sql

   Code sample:
   EXEC xp_cmdshell 'SQLCMD -S"SQLServerA" -E -d"master" -i"C:\releases\2010\July\01a_main_release.sql" -o"C:\releases\2010\July\01a_main_release_output.txt"'

4. Schedule a SQL Server Agent Job that executes the content of 01_main_release.sql to kick off the application of all scripts.

   a. Step 1: Execute the command to fire sqlcmd to begin the deployment.
   b. Step 2: Email the 01a_main_release_output.txt file to whoever needs to receive the output from the release.

An Example to Tie It All Together

The attached files contain an example that I'll explain here. The example is based on a July 2010 release, so the folder structure is labeled as such. Any folder structure could certainly be utilized.

First, you'll want to extract release_files.zip to "C:\" so that the contents extract to the pre-defined directories.

A breakdown of the files provided in the example:

• C:\releases\prep_01_create_sample_DBs_and_Job.sql
  - This will create the databases DeploymentTest1 and DeploymentTest2 and also create the SQL Server Agent job "Application_Release_Job" that contains the code from 01_main_release.sql
• C:\releases\2010\July\release_scripts\01_main_release.sql
  - This script contains the same code as the first step of the SQL Server agent job that makes the call to SQLCMD.
• C:\releases\2010\July\release_scripts\01a_main_release.sql
  - This is the template file that is used as the input to the SQLCMD in the 01_main_release.sql file.
• C:\releases\2010\July\release_scripts\DeploymentTest1\01_DDL_Updates.sql
  - This will create the Employees table and the usp_Select_Employees stored procedure in DeploymentTest1
• C:\releases\2010\July\release_scripts\DeploymentTest1\02_DML_Updates.sql
  - This will insert one record into the Employees table in DeploymentTest1
• C:\releases\2010\July\release_scripts\DeploymentTest1\03_DDL_Updates.sql
  - This will create the LogInsert table in DeploymentTest1
• C:\releases\2010\July\release_scripts\DeploymentTest2\01_DDL_Updates.sql
  - This will create the Products table in DeploymentTest2

In the files listed above, imagine that 01_DDL_Updates.sql and 02_DML_Updates.sql for DeploymentTest1 were the initial scripts provided for the release. Upon testing, QA discovers that the LogInsert table is missing, so the developer creates 03_DDL_Updates.sql to add to the release. A reference to it is added to the 01a_main_release.sql template file, and now that third update script will be included with the release without needing to overwrite either of the first two files. The provided template file includes this reference already, but I wanted to illustrate how the files listed here would tie into our release methodology.

To get the example working in your environment, perform the following:

1. Execute C:\releases\prep_01_create_sample_DBs_and_Job.sql to create the empty sample databases and the sample SQL Agent Job "Application_Release_Job" that will perform the release.
2. Update step 1 of the SQL Agent job "Application_Release_Job"; switch the "-S" SQLCMD switch to point to your SQL Server instance (Approximately half-way down the step content).
3. Enable sp_send_dbmail (http://technet.microsoft.com/en-us/library/ms191189.aspx)
4. Create a Database Mail profile (http://msdn.microsoft.com/en-us/library/ms175951.aspx)
5. Update step 2 of the SQL Agent job to include the name of your Database Mail profile and enter your email address as the recipient.
6. Execute the job "Application_Release_Job".
   a. DeploymentTest1 will have two tables created.
   b. DeploymentTest1 will have one stored procedure created.
   c. DeploymentTest1.dbo.Employees will have one record inserted (John Doe).
   d. DeploymentTest2 will have one table created.
   e. DeploymentTest2 will have one stored procedure created.
7. Review the email attachment to see the output from the execution.

Summary

To summarize the methodology and solution presented, I have explained how we have evolved in handling our releases and how we have gained some time back in our personal lives. Through the use of incremental database scripts, we have eliminated the confusion that we used to have related to a single script that required multiple updates. Thanks to automating our application releases with the help of SQLCMD and the SQL Server Agent, we have happier DBA's as a result of our on-call weeks consisting of more personal time off instead of performing application releases.

At a high level, the solution presented can be summarized into four points:

1. SQL Server Agent step 1 executes sqlcmd that calls the template file containing a list of all of the individual database scripts to execute for the release.
2. Each script runs sequentially in the order placed in the template file, changing database context thanks to the "USE" statements atop each individual file.
3. Upon completion of step 1 of the SQL Server Agent job and the SQLCMD command, the output file is generated.
4. Step 2 of the SQL Server Agent job generates an email notification using Database Mail, attaching the output of the script executions.

Closing

By using this solution, we have been able to gather up all of the scripts for a release during normal business hours, package them up, and schedule all of them for execution after hours. This has freed us from having to always be available for application releases at all hours of the night/weekend.

All files have been attached with instructions to get you up and running with this solution if you are interested in checking it out. If you experience any problems with the provided example, or have comments or questions about the post, please feel free to join the discussion and I'll be happy to reply to posts there.

I hope everyone finds this solution useful, and I would sure appreciate any feedback related to it!

Cheers!