Saturday, November 20, 2010

Sql Upgrade Strategies

In my prior post I described how you can identify the different between a prior production database and a developer’s evolution of the database. You could make the developer responsible for developing the scripts but that is not always the best use of such a resource (do you make the developer responsible for writing all of the automated unit tests for their code and thus do not need testing resources? The appropriate best use of a developer issue is similar). Whether the developer’s does it, the database developer does it or a contracted resource does it – you end up with a batch of TSQL for each ‘upgrade’ (there may be dozens of developer’s upgrades in a product release cycle). This post looks at the management issue.


  1. We have the TSQL to go from Prod –> Dev
  2. We want to apply it to some other Prod system


The basic pattern is a series of update files and a console application that reads the TSQL in , configuration information and then apply the information. The files must be sequenced (manifest file or by file naming).


NOTE: For a release system, the TSQL may be in a password protected zip file that the console application has the password for. For development, the TSQL may be coming from files in the same folder.


Put a Version Table in the database

This table is essential for easy management of the database. It is also useful for well managed code, your C# code can check to see if the database supports an feature and thus provide backward compatibility. An example is shown below:

var spName = string.Empty(); if (DBVersion > 1783) { spName = "p_GetOrgaizationTree_3"; } else if (DBVersion > 234) { spName = "p_GetOrgaizationTree_2"; } else if (DBVersion > 83) { spName = "p_GetOrgaizationTree_1"; } else { spName = "p_GetOrgaizationTree"; }

The DBVersion is obtained and cached when the application starts so there is only one call to it. My preference is to use an Int and increment it by 10 each time (there’s are advantages to leaving space between them – see below).

Sequential Update

If you put each batch of TSQL into XML (instead of using a .sql file) you are able to do a better control update of databases. For example, if the root element is:


  <dbupdate fromversion=”230” toversion=”240”>


Then if the database version is not a match, the file is skipped. If it is a match, then the dbversion is incremented by 1 at the start (to 231) of executing the file content and then updated to 240 at the successful end. If any statement fails, the console utility exits – leaving a log file to be sent to support. The database is left in state 231 (so a repeat execution will not execute anything because there is no script for ‘231’). After supports figure out the problem, it sends a new file with a header of:


   <dbupdate fromversion=”231” toversion=”240”>


This gives support some 9 attempts at fixing any problems…


The use of XML allows better control of what SQL in an update actually gets implemented.  Often the TSQL is sufficient, for example:


In some cases, writing a pre-condition that is executed by the console application is more efficient or easier to understand.

<function check="Select count(1) From sys.objects where name='GetProfileTypeID'" id="function17" executed="10/30/2010 1:40:49 PM"> CREATE FUNCTION [Sync].[GetProfileTypeID] ( @ProductionID Int ) RETURNS Int AS BEGIN DECLARE @DevId Int DECLARE @ProfileTypeName nvarchar(max) SELECT @ProfileTypeName=ProfileTypeName FROM BoardDemos.dbo.ProfileType Where ProfileTypeID=@ProductionID SELECT @DevID=ProfileTypeID From BoardDemos_Dev.dbo.ProfileType WHERE @ProfileTypeName=ProfileTypeName RETURN @DevID END </function>

Remember that in some cases, items cannot be dropped and recreated because they are in use elsewhere.

Bail on first exception

The console application should bail on the first exception. In general, it should also do a backup of the database before any execution. The console application should also support doing an automatic rollback/restore if any exception occurs (this may be ON by default, and set to OFF when debugging issues).  You should not leave a customer installation in limbo.


In some cases, you may also wish to do a diagnostic backup at the point of failure before doing the restore.


Gotchas for Data

In the past I have encountered two situations where there’s been some pain in writing scripts.  When the primary key is Identity(1,1) and new data is being added that must match up with existing records; OR  when the primary key is a GUID and new data is being added that must match up with existing records AND there is no reliable alternative key (unique index) to use for match ups.  The easiest solution is often to create a SyncID column that is a GUID and explicitly set it (instead of relying on NewID()) and use this as an alternative key.  In some cases, I have had to infer the match by doing a:




to insure that there is an expected match.  In other cases, there may be rename of something, for example “Winslow” –>  “Bainbridge Island” in the production system (with the center in terms of longitude and latitude being updated too!). The development system did not have this change applied. If there was a SyncID() in the table (which used an Identity(1,1) for primary key) then life is much easier.  This actually facilitate a two way update of the Prod and Dev databases (Some data from prod can update the dev database).


Over Christmas I may make a drop of my version of the above console application available for any interested parties – so drop me a line (or leave your email here)…

No comments:

Post a Comment