SQL Deploy - Users Guide

SQL Deploy Developer 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 Reconcile to upgrade database changes
3) Using SQL Deploy project files (.sdproj)
4) Using the Business Module (Recommended)
5) Using the Options Control (Recommended)- New!

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.
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 Reconcile to upgrade database changes

The SQL Deploy Reconcile 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 Reconcile 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 Business Module

Coming soon.

5) Using the Options Control

Coming soon.