wahooga.com

Example: Updating a remote customer database schema

Posted by Dave Mellors | Filed under Software Development

Overview

A common use of the Red Gate SQL Comparison SDK is to package up the latest version of a database schema and send it to customers as an executable.

This example demonstrates how easy it is to use the SDK to do this.
NOTE: SQL Comparison SDK is to be the new name for SQL Toolkit

Pre-requisites

Visual Studio 2005 or Visual Studio 2008
Red Gate SQL Comparison SDK
You can download a 14 day free trial of SQL Comparison SDK if you haven’t already got it.
WidgetProduction and WidgetStaging SQL creation script
This database creation script is used by some of the SQL Compare API toolkit samples.
SqlDbDeploy.zip
The database schema deployment example used in this article.

Building the example

When you load the solution in Visual Studio you will need to update the references to the toolkit assemblies. You can find the .dll files in the folder where you installed SQL Compare. The default location is Program Files\Red Gate\SQL Compare 6

If you want to change the splash image then you can replace the splash.jpg file in resources.

The embedded snapshot.snp file is used by default. A snapshot of the WidgetStaging database is included in the resources folder. You can delete it or replace it with your own snapshot but you need to remember to set the build action to Embedded Resource.

Using the example

The compiled example takes a number of command line parameters which are:-

/S:<servername>
- This is the server on which the database resides to synchronize.
/D:<database>
- The database name to update.
/B:<filename>
- If you want to make a backup of the original schema before an update specify a filename here.
/V:true
- Specify this if you want verbose information.
/UE:<snapshotname>
- Name of embedded snapshot to use. If /UB not specified then default is snapshot.snp /UB:<snapshotfilename>
- Name of snapshot file to use.

At the moment the example includes a snapshot for the WidgetProduction database so if you run the following parameters:-
SqlDbDeploy.exe /S:davidm\sql2005 /D:widgetproduction /B:backup.snp

This would update the widgetproduction database on davidm\sql2005, create a backup snapshot before doing an updates and call the file backup.snp and provide verbose output.

If you want to reset the database back to the original you would use:-
SqlDbDeploy.exe /S:davidm\sql2005 /D:widgetproduction /V:true /UB:backup.snp

SqlDbDeploy – The movie
Just in case you haven’t installed the SDK yet you can view a flash movie of a demonstration of SqlDbDeploy.

Thank You
I would like to thank the Red Gate customers who provided information and source code which helped me to understand how they are using the SDK. In particular a big thank you to Rawden Hoff of Nebula Systems whose VB.Net example inspired me to produce this example.

Feedback & disclaimer
This example is provided “as is” and is not supported by Red Gate Software. This is my first .net Windows application so if you find this example useful or have any suggestions for improving it then it would be great to hear from you

UPDATE FOR SQL Comparison SDK v7

The solution available for download is for the SQL Toolkit v6 if you are using the SQL Comparison SDK v7 I believe you will need to make the following changes.

Open the solution and remove the references to the Red Gate assemblies.
There have been some namespace changes so you will need to add the new assemblies from your V7 installation. The files are
RedGate.Shared.Sql.dll, RedGate.Shared.Utils.dll and RedGate.SqlCompare.Engine.dll
Replace the RedGate using statements in Form1.cs with
using RedGate.Shared.SQL;
using RedGate.Shared.SQL.ExecutionBlock;
using RedGate.Shared.Utils;
using RedGate.SQLCompare.Engine;
Because of the namespace changes remove the references toRedGate.SQL.Shared from Form1.cs line 168 so it now looks like
BlockExecutor executor = new BlockExecutor();