SQL Deploy - Developer's Guide

Home > Products > SQL Deploy > SQL Deploy - Developer's Guide

SQL Deploy Developer's Guide - Database Deployment Software for SQL Server and MSDE

How many times have you deployed an initial version of your SQL Server or MSDE Database application to your clients or departments, then wondered how you are going to make updates to the Database schema in the future without hassle?

Introduction

Before you start

Integrating SQL Deploy into your application

1) Using SQL Deploy Check for Version Checks
2) Using SQL Deploy Compare to upgrade database changes
3) Using SQL Deploy project files (.sdproj)
4) Using the Options Control (Recommended) - New!
5) Use SQL Deploy on your existing database

Introduction

Before you start

1) Optimise your database for maximum performance. Read our Rules To Better SQL Server Databases . Instead of manually implementing these rules, use SSW SQL Auditor to automatically implement many of them for you. Download it and give it a go.

2) Setup your script directories and files

Create a new script directory under your application folder. This directory is for the scripts which will create/manipulate the database structure, and insert/manipulate important static data. Place all appropriate scripts in this directory.
Your first script should drop (take this out if the database is of high importance) and create the database, your second should create tables, stored procs, views, etc and so on.
If you have not got any scripts of your database yet then you can use the program "scptxfr.exe" (supplied with SQL Server under C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade ) in order to script out your entire database.

An example command line to use is:
SCPTXFR.exe /s <ServerName> /d <DatabaseName> /P <SA_Password> /f ver0001.sql /H /A
Eg., "SCPTXFR.exe" /s (local) /d Northwind /f ver0001.sql /P mypassword /H /A
If you have sample data, create another script directory also under your application folder. Place all your insert scripts in there.
Once all your files are in this directory make sure that they are named correctly. SSW SQL Deploy will determine the order of the scripts from there name. Make sure they are in alphabetical order. If you are unsure, have a look at the Samples\DatabaseSQLScripts folder under your SSW SQL Deploy installation.

It's important that you apply a flexible and useful naming standard to your SQL scripts.
SSW SQL Deploy will store the names of the run scripts in a _zsDataVersion table. If SQL Deploy runs and this table does not exist already, the user will be asked if they want SQL Deploy to install it for them. To avoid complication, we recommend that you include the SQL that makes the table in your first script. You can find the SQL in the _zsDataVersion.sql file. CHECK FISRT

Integrating SQL Deploy into your application

1) Using SQL Deploy Check for Version Checks

The SQL Deploy Check is a com compliant assembly which helps find what version of the database the client currently has. The application may then alert the user that he needs to update the database of version X.XX because the scripts are version X.XX.

Connection String	Gets or sets the connection string to the database.
CurrentDatabaseVersion	Gets the version of the current database according to the connection string. This is the last run script against the database, stored in the _zsDataVersion table.
NewDatabaseVersion	Gets the latest version of the scripts.
SQLScriptPath	Gets or sets the path to the SQL scripts.

This code illustrates how SSW SQL Deploy Check maybe used in your application.

Using SSW.SQLDeploy.Check;

                        ...

                        ...

                        string strConnection = "Data Source=(local);Initial Catalog=Northwind;";

                        string strSQLDatabaseScripts = "c:\temp\Scripts"

                        ClientUtil cu = new ClientUtil();

                        if(cu.IsNewVersion(strConnection,steSQLDatabaseScripts)){

                             MessageBox.Show("Please upgrade your database to "+
                        cu.NewDatabaseVersion +", it is currently "+cu.CurrentDatabaseVersion);

                        

                        }

                        ...

2) Using SQL Deploy Compare to upgrade database changes

The SQL Deploy Compare feature provides the ability to compare all the scripts against the current database and update the new changes.

Connection String	Gets or sets the connection string to the database.
SQLScriptPath	Gets or sets the path to the SQL scripts.

This code illustrates how SSW SQL Deploy Compare maybe used in your application.

		using SSW.Framework.Data;

		using SSW.Framework.Data.SqlServer;

		...

		...

		string strDatabaseName = "SQLDeploySampleDatabase"; 

		string TempDatabaseName = strDatabaseName + "_TEMP";

string strDatabaseScriptPath = "../DatabaseSQLScripts/";



Settings settings = new Settings();

settings.FolderPath = Server.MapPath(strDatabaseScriptPath);



SQLFileFinder finder = new SQLFileFinder();

finder.FillAdvanced(settings);

finder.SetEnabledOnAllFiles(true);

SQLFileCollection files = finder.EnabledFiles();



settings.Connection = "Server=localhost;Database=SQLDeploySampleDatabase;";



if (DBUtils.DoesDatabaseExist(TempDatabaseName, settings.Connection))

{

	    DropDatabase(TempDatabaseName, false);

}



settings.ConnectionString.InitialCatalog = TempDatabaseName;

settings.IsNewDatabase = !DBUtils.DoesDatabaseExist(strDatabaseName, settings.Connection);

settings.NewDatabaseName = TempDatabaseName;

settings.PlaceHolder = "[DatabaseNamePlaceHolder]";



foreach(SQLFile file in files)

{

	    file.SQLFileStepsReplace("'"+databaseName+"'","'"+TempDatabaseName+"'");

	    file.SQLFileStepsReplace("'"+databaseName+".","'"+TempDatabaseName+".");

	    file.SQLFileStepsReplace(" "+databaseName+" " ," "+TempDatabaseName+" ");

	    file.SQLFileStepsReplace("USE "+databaseName+"" ,"USE "+TempDatabaseName+"");

	    file.SQLFileStepsReplace("DATABASE "+databaseName+"" ,"DATABASE "+TempDatabaseName+"");

}	



settings.ConnectionString.InitialCatalog = "";

du.UpgradeDatabase(files, settings, settings.NewDatabaseName, true);

3) Using SQL Deploy Project Files (.sdproj)

To automate the upgrade process for later use, you can make a sdproj file which stores all the information about your database. SQL Deploy will make this file for you when you run SQL Deploy for the first time.

After the file has been made, package the file with your application. We recommend that you put the file with the rest of your script files in the Script folder we created in the last step.

Now you can just add new script files to the Scripts folder and the next time the user double clicks on the sdproj file, SQL Deploy will automatically select the new scripts. The user should only have to click next through all the screens.

4)Using the Options Control

The options control provides means for the application user to select, create, update and compare the application database. The options control is powered by SSW SQL Deploy for all the database creation, upgrade and compare.

The developer can easily implement this control using the following steps:

Create a new Windows Application project (preferably in C# as the code listings)
Right-click on the toolbox, and select "Add/Remove Items..."
Browse for SSW.Framework.WindowsUI.Options.dll under your SSW SQL Deploy folder
Your toolbox should now have the DatabaseSetupControl:

Figure: DatabaseSetupControl added to the VS.Net toolbox
- Add the control onto your form by dragging the control onto your form.
- Add references to SSW.Framework.Data.dll in order to access the ConnectionBuilder Class
If you intend to show the compare button, add ExamDiff.exe from your SQL Deploy run directory to your primary output directory.

Set the configuration of the control using the Configuration property in your load event handler (or constructor).

	ConnectionBuilder	Gets or sets the connection string builder for the connection to the database.
	CreateScriptsPath	Gets or sets the path to the create scripts folder.
	NewDatabaseName	Gets or sets the database name (set as default when creating a new database).
	DatabaseNamePlaceholder	Gets or sets the database name place holder in the scripts which will be replaced with the database name.
	IsSampleDatabaseNameEnforced	Gets or sets whether the sample database name is enforced on the database creation form. This will set the database name textbox to be disabled if insert sample checkbox ticked.
	IsDatabaseNameEnforced	Gets or sets whether the database name is enforced on the database creation form. This will set the database name textbox to be disabled.
	PostCreateScriptsPath	Gets or sets the path to the post create scripts folder.
	PostUpgradeScriptsPath	Gets or sets the path to the post upgrade scripts folder.
	PreUpgradeScriptsPath	Gets or sets the path to the pre upgrade scripts folder.
	ProductName	Gets or sets the product name which will mainly be displayed in the status messages.
	SampleDatabaseName	Gets or sets the sample database name (set as default when creating a new database with insert sample checkbox ticked).
	SampleScriptsPath	Gets or sets the path to the sample scripts folder.
	UpgradeScriptsPath	Gets or sets the path to the upgrade scripts folder.

Figure: Properties to set to configure the control

Below is some sample code of setting the control's configuration & handling the ConnectionBuilder.ConnectionStringChanged event.

Replace the MyConfiguration properties with the paths on your computer. A Sample MyConfiguration Class is shown in the code listing at the bottom.

private void Form1_Load(object sender, System.EventArgs e)

                                        {

                                              databaseSetupControl1.Configuration.ConnectionBuilder.ConnectionString
                                        = MyConfiguration.ConnectionString;

                                              databaseSetupControl1.Configuration.CreateScriptsPath
                                        = MyConfiguration.CreateScriptsPath;

                                              databaseSetupControl1.Configuration.UpgradeScriptsPath
                                        = MyConfiguration.UpgradeScriptsPath;

                                              databaseSetupControl1.Configuration.ProductName =
                                        Application.ProductName;

                                              databaseSetupControl1.Configuration.SampleScriptsPath=
                                        MyConfiguration.SampleScriptsPath;

                                              databaseSetupControl1.Configuration.NewDatabaseName
                                        = "SSWSQLDeployNorthwindSample";

                                              databaseSetupControl1.Configuration.IsDatabaseNameEnforced
                                        = true;

                                              databaseSetupControl1.Configuration.ConnectionBuilder.ConnectionStringChanged+=
                                        new EventHandler(Configuration_ConnectionStringChanged);

                                        

                                        }

                                        

                                        private void Configuration_ConnectionStringChanged(object sender, EventArgs e)

                                        {

                                           // MessageBox.Show("The connection string has changed, please
                                        handle here.",Application.ProductName);

                                        }

Figure: Code listing sample of how to set the configuration of the control

Handle the ConnectionStringChangedevent. In the event handler the Configuration.ConnectionBuilder.ConnectionStringChanged would return the selected connection string. Typically you would then store this connection string.

Note: If you are using SQL Deploy's sample scripts (typically in C:\Program Files\SSW SQL Deploy\Samples\WebForm\DatabaseSQLScripts), it is a good idea to copy them into 3 separate folders:

CreateScripts - ver0001.sql
UpgradeScripts - ver0002.sql,ver0003.sql,ver0004.sql,ver0005.sql
SampleScripts - ver0006.sql

Then set the paths for the configuration accordingly. The listing below shows the MyConfiguration class encapsulating those script paths.

public class MyConfiguration

                                        {

                                          public MyConfiguration() {}

                                        

                                          public static string ConnectionString

                                          {

                                               get { return ""; }

                                          }

                                        

                                          public static string CreateScriptsPath

                                          {

                                             get { return @"c:\Program Files\SSW SQL Deploy\Samples\WebForm\DatabaseSQLScripts\Create";
                                        }

                                          }

                                        

                                         public static string UpgradeScriptsPath

                                          {

                                             get { return @"c:\Program Files\SSW SQL Deploy\Samples\WebForm\DatabaseSQLScripts\Upgrade";
                                        }

                                         }

                                        

                                          public static string SampleScriptsPath

                                          {

                                             get { return @"c:\Program Files\SSW SQL Deploy\Samples\WebForm\DatabaseSQLScripts\Sample";
                                        }

                                          }

                                        }

Figure: Code for a sample MyConfiguration class with typical script paths

5)Using SQL Deploy On Existing DataBase

In this quick walkthrough we configure SQL Deploy for the existing "AdventureWorks" sample database for future changes..

: Figure: AdventureWorks sample database from Microsoft, with no "_zsVersion" table code

Step 1: Script your existing database to a "00000_create_database.sql" script file

: Figure: Use SQL Server Management Studio (SSMS) to generate scripts to create your whole database

: Figure:Check the "script all objects in the selected database" box

Let SSMS create the "00000_create_database.sql" for you

: Figure:Make sure to create 1 single file in your project folder

: Figure:Generation of scripts was successful

Add this 2 lines at the start of the script:

CREATE DATABASE [AdventureWorks]
GO

Note: This script is special because it creates a new database. We identify this by these lines.

Final result of step 1

: Figure:One "create database script" file in your project "SQLScripts" folder

Step 2: Tell SQL Deploy that you have run already the "create database script"

Since you have already an existing database, you have to tell SQL Deploy about that.
Copy and paste this script into SQL Server Management Studio and run it on the AdventureWorks database.
This will tell SQL Deploy that this script has been run already

    USE ADVENTUREWORKS
      GO
           INSERT INTO [dbo].[_zsDataVersion](
           [DateCreated]
           , [EmpUpdated]
           , [ScriptPath]
           , [ScriptFile]
           , [Note]
           , [SQLDeployVersion]
           )
        SELECT
           getdate() -- current time on insert
           ,suser_sname() + ' as ' + user_name() -- system user name
           , N'D:\DataPeterGfader\ProjectsTFS\AdventureWorks\SqlScripts' -- the folder where the script was located, just for tracing purposes
           , N'00000_create_database.sql' -- the name of the script file
           , N'SSWSQLDeploy: Database created manually, don''t delete this record' --
           Note
           N'12.22' -- SQL Deploy version

This step is not necessary if you start with a new database from scratch.
If you wish that SQL Deploy creates a new database for you run the wizard as described on SSW SQL Deploy-Create new database in EXE mode

Final result of step 2

1. Run through the wizard to update your database

: Figure: SQL Deploy sees that "00000_create_database.sql" has been run already

2. Run "Compare database" and see that your database matches up with the SQL scripts

Your database is ready and you can start using SQL Deploy

Remember: Make sure to script out your changes as described on the sql deploy exe mode page