Skip Navigation LinksHome > Products > SQL Deploy > SQL Deploy - Developer's Guide
SQL Deploy

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

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 StringConnection String Gets or sets the connection string to the database.
GET VERSIONCurrentDatabaseVersion 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.
Get latest script NewDatabaseVersion Gets the latest version of the scripts.
Get/Set paths to scriptsSQLScriptPath 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.

Gets/Sets Connection string 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:

5)Using SQL Deploy On Existing DataBase

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

Adventure Works Database
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

Generate Scripts
Figure: Use SQL Server Management Studio (SSMS) to generate scripts to create your whole database
Script All Objects
Figure:Check the "script all objects in the selected database" box

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

Single File
Figure:Make sure to create 1 single file in your project folder
Succesfull Generation
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

create database script
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

Select SQL File
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

Ready To Start

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