Introduction
In Part 1 of this series I discussed the principles of test-driven database development (TD3) along with Try/Catch and SqlCmd functionality. In Part 2, I discussed Solutions as defined in Microsoft Integrated Development Environments (IDEs), deployment, and different types of tests. In Part 3, I demonstrated refactoring and automating existence tests. In this article, I cover database deployment and deployment artifacts.
The WeatherData database is real – I built it to hold data collected by my weather station.
Open the Existing Solution
Our existing Database Solution is named WeatherData and it's a Database Project we created in SQL Server Management Studio. Once open, click View | Solution Explorer to display the solution artifacts:
Deployment
In Solution Explorer we see the scripts we've created so far. Let's dig into the deployment scripts. We have two deployment scripts in our project: Deploy_V1.ver.sql and Deploy_V1.1.ver.sql.
Deploy_V1.ver.sql reads: Print '' Print 'Deploying WeatherData v1.' Print '' Print 'Calling Create_WeatherData.test.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_WeatherData.test.sql" Print 'Create_WeatherData.test.sql called.' Print '' Print 'Calling Create_WeatherData.db.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_WeatherData.db.sql" Print 'Create_WeatherData.db.sql called.' Print '' Print 'Calling Create_WeatherData.test.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_WeatherData.test.sql" Print 'Create_WeatherData.test.sql called.' Print''
Print 'WeatherData v1 deployed.' Deploy_V1.1.ver.sql reads: Print '' Print 'Deploying WeatherData v1.1.' Print '' Print 'Calling Create_Raw.test.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_Raw.test.sql" Print 'Create_Raw.test.sql called.' Print '' Print 'Calling CreateRaw.schema.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_Raw.schema.sql" Print 'CreateRaw.schema.sql called.' Print '' Print 'Calling Create_Raw.test.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_Raw.test.sql" Print 'Create_Raw.test.sql called.' Print '' Print 'Calling Create_StageTemperature.test.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_StageTemperature.test.sql" Print 'Create_StageTemperature.test.sql called.' Print '' Print 'Calling CreateStageTemperature.table.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_StageTemperature.table.sql" Print 'CreateStageTemperature.table.sql called.' Print '' Print 'Calling Create_StageTemperature.test.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_StageTemperature.test.sql" Print 'Create_StageTemperature.test.sql called.' Print '' Print 'Calling Create_GetLatestAvgTemp.test.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_GetLatestAvgTemp.test.sql" Print 'Create_GetLatestAvgTemp.test.sql called.' Print '' Print 'Calling CreateGetLatestAvgTemp.proc.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_GetLatestAvgTemp.proc.sql" Print 'CreateGetLatestAvgTemp.proc.sql called.' Print '' Print 'Calling Create_GetLatestAvgTemp.test.sql...' :r "C:\Projects\WeatherData\WeatherData\Create_GetLatestAvgTemp.test.sql" Print 'Create_GetLatestAvgTemp.test.sql called.' Print '' Print 'WeatherData v1.1 deployed.'
Let's build a master deployment script for version 1 of our database that calls these scripts in the desired order. Our first version of the script will look something like this:
/* Notes: 1. Turn on SQLCMD mode. 2. If you receive an error like "Incorrect syntax near ':'", see Note 1. 3. Correct the file paths, replacing the default with the full path to your files. */Print '' Print 'Building WeatherData Version 1.' Print '' Print 'Calling DeployV1.ver.sql...' :r "C:\Projects\WeatherData\WeatherData\Deploy_V1.ver.sql" Print 'DeployV1.ver.sql called.' Print '' Print 'Calling Deploy_V1.1.ver.sql...' :r "C:\Projects\WeatherData\WeatherData\Deploy_V1.1.ver.sql" Print 'Deploy_V1.1.ver.sql called.' Print ''
Print 'WeatherData Version 1 built.'
Let's save it as MasterDeploy_V1.ver.sql.
Note it contains our standard comments on SqlCmd mode at the top, followed by some documentation in the form of Print statements. The first Print statement documents our intention in this script – we are building version 1 of the WeatherData database. Next we call our two version deployment scripts, Deploy_V1.ver.sql and Deploy_V1.1.ver.sql.
Executing this script returns results that look like:
Building WeatherData Version 1. Calling DeployV1.ver.sql... Deploying WeatherData v1. Calling Create_WeatherData.test.sql... Success: WeatherData exists. Create_WeatherData.test.sql called. Calling Create_WeatherData.db.sql... Create WeatherData database > WeatherData database exists. Create_WeatherData.db.sql called. Calling Create_WeatherData.test.sql... Success: WeatherData exists. Create_WeatherData.test.sql called. WeatherData v1 deployed. DeployV1.ver.sql called. Calling Deploy_V1.1.ver.sql... Deploying WeatherData v1.1. Calling Create_Raw.test.sql... Success: The Raw schema exists. Create_Raw.test.sql called. Calling CreateRaw.schema.sql... > Raw schema exists. CreateRaw.schema.sql called. Calling Create_Raw.test.sql... Success: The Raw schema exists. Create_Raw.test.sql called. Calling Create_StageTemperature.test.sql... Success: Raw.StageTemperature exists. Create_StageTemperature.test.sql called. Calling CreateStageTemperature.table.sql... > Raw.StageTemperature exists. CreateStageTemperature.table.sql called. Calling Create_StageTemperature.test.sql... Success: Raw.StageTemperature exists. Create_StageTemperature.test.sql called. Calling Create_GetLatestAvgTemp.test.sql... Success: GetLatestAvgTemp exists. Create_GetLatestAvgTemp.test.sql called. Calling CreateGetLatestAvgTemp.proc.sql... > Dropping Procedure raw.GetLatestAvgTemp. > Creating Procedure raw.GetLatestAvgTemp. > raw.GetLatestAvgTemp Procedure created. CreateGetLatestAvgTemp.proc.sql called. Calling Create_GetLatestAvgTemp.test.sql... Success: GetLatestAvgTemp exists. Create_GetLatestAvgTemp.test.sql called. WeatherData v1.1 deployed. Deploy_V1.1.ver.sql called. WeatherData Version 1 built.
I believe these results should be stored because it constitutes a deployment artifact.
“What do you mean by a deployment artifact, Andy?” I'm glad you asked! It's a deployment log, tracing the activities of the deployment script. Sometimes in database work, I find myself in the position of having to back up some statement I made. This probably doesn't happen to you, but it happens to me. Someone will question whether I deployed all of the objects, for example. Having a deployment artifact – the results of the deployment script, along with the script itself – can provide convincing evidence to back up my claim that I did, in fact, accomplish the installation.
It can also prove I missed something, so it cuts both ways.
I'd argue the only wrong answer to the question “What happened?” is “I don't know and I have no way of finding out.” Deployment artifacts help answer these questions.
Where's the Beef?
Let's beef up this deployment report some. It has some useful information already, but I bet we can improve the formatting and add some missing information that will make this even more useful.
First, I add a deployment header to all three deployment scripts. For MasterDeploy_V1.ver.sql, it looks like this:
Print '------------------------------------------------' Print ' Deployment Log: WeatherData' Print ' Script: MasterDeploy_V1.ver.sql (M1)' Print ' Version: 1.0, 1.1' Print ' Start DateTime: '+ convert(varchar,GetDate(), 101) + ' '+ convert(varchar,GetDate(), 108) Print ' Server: '+ @@ServerName Print ' Executed By: ' + Original_Login() Print '------------------------------------------------'
This provides a nice summary of my intent when executing this script. I also add a prefix to each Print statement executed from MasterDeploy_V1.ver.sql to identify feedback sent to the deployment log. In the MasterDeploy_V1 script I use the [M1] identifier and the lines look like this:
Print ' [M1] Calling DeployV1.ver.sql...'
I add similar identifiers to the Deploy_V1 and Deploy_V1.1 scripts ([V1] and [V1.1]). Re-executing MasterDeploy_V1 produces the following deployment log:
------------------------------------------------
Deployment Log: WeatherData
Script: MasterDeploy_V1.ver.sql (M1)
Version: 1.0, 1.1
Start DateTime: 06/28/2009 16:54:18
Server: ANDYREDXPS64
Executed By: AndyRedXPS64\A. Ray Leonard
------------------------------------------------
[M1] Calling DeployV1.ver.sql...
------------------------------------------------
Deployment Log: WeatherData
Script: Deploy_V1.ver.sql (V1)
Version: 1.0
Start DateTime: 06/28/2009 16:54:18
Server: ANDYREDXPS64
Executed By: AndyRedXPS64\A. Ray Leonard
------------------------------------------------
[V1] Deploying WeatherData v1.
[V1] Calling Create_WeatherData.test.sql...
Success: WeatherData exists.
[V1] Create_WeatherData.test.sql called.
[V1] Calling Create_WeatherData.db.sql...
Create WeatherData database
> WeatherData database exists.
[V1] Create_WeatherData.db.sql called.
[V1] Calling Create_WeatherData.test.sql...
Success: WeatherData exists.
[V1] Create_WeatherData.test.sql called.
------------------------------------------------
[V1] WeatherData v1 deployed.
------------------------------------------------
[M1] DeployV1.ver.sql called.
[M1] Calling Deploy_V1.1.ver.sql...
------------------------------------------------
Deployment Log: WeatherData
Script: Deploy_V1.1.ver.sql (V1.1)
Version: 1.1
Start DateTime: 06/28/2009 16:54:18
Server: ANDYREDXPS64
Executed By: AndyRedXPS64\A. Ray Leonard
------------------------------------------------
[V1.1] Calling Create_Raw.test.sql...
Success: The Raw schema exists.
[V1.1] Create_Raw.test.sql called.
[V1.1] Calling CreateRaw.schema.sql...
> Raw schema exists.
[V1.1] CreateRaw.schema.sql called.
[V1.1] Calling Create_Raw.test.sql...
Success: The Raw schema exists.
[V1.1] Create_Raw.test.sql called.
[V1.1] Calling Create_StageTemperature.test.sql...
Success: Raw.StageTemperature exists.
[V1.1] Create_StageTemperature.test.sql called.
[V1.1] Calling CreateStageTemperature.table.sql...
> Raw.StageTemperature exists.
[V1.1] CreateStageTemperature.table.sql called.
[V1.1] Calling Create_StageTemperature.test.sql...
Success: Raw.StageTemperature exists.
[V1.1] Create_StageTemperature.test.sql called.
[V1.1] Calling Create_GetLatestAvgTemp.test.sql...
Success: GetLatestAvgTemp exists.
[V1.1] Create_GetLatestAvgTemp.test.sql called.
[V1.1] Calling CreateGetLatestAvgTemp.proc.sql...
> Dropping Procedure raw.GetLatestAvgTemp.
> Creating Procedure raw.GetLatestAvgTemp.
> raw.GetLatestAvgTemp Procedure created.
[V1.1] CreateGetLatestAvgTemp.proc.sql called.
[V1.1] Calling Create_GetLatestAvgTemp.test.sql...
Success: GetLatestAvgTemp exists.
[V1.1] Create_GetLatestAvgTemp.test.sql called.
------------------------------------------------
[V1.1] WeatherData v1.1 deployed.
------------------------------------------------
[M1] Deploy_V1.1.ver.sql called.
------------------------------------------------
[M1] WeatherData Version 1 built.
------------------------------------------------
This looks a lot busier (and is longer), but it also provides a better description of what happened during the deployment. This isn't nearly as important when all goes as planned; it's vital information when something fails.
One Last Thing Here
The hard-coded paths might troublesome – especially as I drop the scripts from my laptop to a Development server, then a Test server, and finally a Production server. Some folks just don't like anything on the C: drive of a server except the OS. I understand why.
To facilitate this, I use a trick my friend Dan Lewis taught me about variables in SqlCmd. Using the setvar command, I can create and initialize a variable:
:setvar ScriptPath "C:\Projects\WeatherData\WeatherData\"
Then, I can dynamically set the path to the sql files called from the script by removing the double-quotes and replacing the string C:\Projects\WeatherData\WeatherData\ with $(ScriptPath):
:r $(ScriptPath)Deploy_V1.ver.sql
But wait there's more! Edit the Deploy_V1 and Deploy_V1.1 scripts in the same manor, and the variable value is passed through to them as well. Now, the deployments are drive- and folder-agnostic. How cool!
Conclusion
In this article, we've examined one approach to scripting database deployments. We covered a couple cool concepts; deployment artifacts and using the setvar SqlCmd command to allow us to configure and use dynamic paths to SqlCmd scripts.
:{> Andy
The Series
The articles in this series:
