SSW SQL Deploy - Screenshots

SQL Deploy Users 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?

Prerequisites

Introduction

Getting Started

First install SQL Deploy by downloading and running the setup package. When the installation is complete, view the Samples folder that the setup package has opened.

Figure: The first thing you should do is view the sample and look at the sample code

Viewing the Samples

Please take the time to look at the samples that are installed with the application. There are samples for:

• Microsoft Access
• .NET Windows Forms
• ASP.NET Web Forms

The DatabaseSQLScript folder contains sample SQL scripts that work with the supplied sample applications.

Using SQL Deploy in Directory Mode

Overview of Directory Mode

Directory mode is designed to allow Developers to incorporate SQL Deploy into their applications to deliver Database updates quickly and easily at the clients site.

The SQL DBA or Developer saves all of the database updates to a single folder. SQL Deploy monitors this folder and checks for SQL scripts that have not been applied to the Database. It keeps track of the changes it has applied to the Database by updating a history table.

For SQL Developers

When a new version of the Database is required, simply

• email the SQL scripts and the SQL Deploy Project (sdproj) file to the client.
• Instruct them to place these files into the directory that SQL Deploy is monitoring. When the application detects the new SQL scripts the user will be notified that changes need to be made to the Database and a dialog similar to the following will be displayed.

The SQL Deploy DLL will display this dialog when it detects a SQL script that has not been applied to the database. The checking process is normally run when the application that is using SQL Deploy is started.

When the user clicks on the Yes button, SQL Deploy will be launched with the SQL Deploy Project (sdproj) file as a parameter. The user has only to click the Next button for each screen in the SQL Deploy wizard to apply the database updates. You could also start the console version of SQL Deploy with will upgrade the database with no user intervention.

Command line arguments for SSW SQL Deploy

You can start SQL Deploy with command line arguments to specify the initial values displayed in the wizard screens.

SSWSQLDeploy.exe [/XMLFile:settings.sdproj] [/TimeOut:value] [/UserMode:true|false] [/ReconcileScriptsMode:true|false] [/FolderPath:folderpath] [/ConnectionString:connectionstring] [/SelectedFile:filepath] [/UseTransactions:true|false] [/UpdateVersionTable:true|false]

OR

SSWSQLDeployConsoleUI.exe /XMLFile:settings.sdproj

Command line usage examples

Specifying the Connection string

SSWSQLDeploy.exe /ConnectionString:Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=SSWSQLDeployNorthwindSample;Data Source=(local)

Specifying the SQL Deploy Project to use

SSWSQLDeployConsoleUI.exe /XMLFile:C:\Accounting\Accting.sdproj

Using Directory mode

SSWSQLDeploy.exe /TimeOut:10 /UserMode:true
/ReconcileScriptsMode:false /FolderPath:C:\Accounting\SQLScripts
/UseTransactions:true /UpdateVersionTable:true

Using the Console EXE

SSWSQLDeploy.exe /XMLFile:C:\Accounting\Accting.sdproj

Saving SQL Deploy Project (.sdproj) and SQL script files

To enable your Database to be updated with SSW SQL Deploy you must save all the changes you make to the Database as SQL script files. These can be saved from Enterprise Manager, Query Analyzer or Visual Studio .NET or created manually.

You are required to name your scripts in alphabetical order so SQL Deploy knows the order in which to run the SQL scripts. e.g. 0001.sql, 0002.sql etc.

The _zsDataVersion Table

A history of the SQL scripts that have been run on the Database is stored in a table called _zsDataVersion. If this table does not exist in the Database then SQL Deploy will assume all scripts need to be run. This table will be created by SQL Deploy if it does not exist.

Figure: The Directory mode only works when the backend has a table called _zsDataVersion

The _zsDataVersion table is used by SQL Deploy to determine which SQL scripts are new and which have already been run.

Figure: The ticked SQL scripts as there are no entries in the _zsDataVersion table for them.

Using SQL Deploy in Single File Mode

Overview of Single File mode

In Single file mode SQL Deploy will run a single SQL script against the Database. It is a simple method for ad-hoc database updates.

When a new SQL script is emailed to a user by the Developers, the SQL Deploy Project (sdproj) file must be included. The SQL scripts and SQL Deploy Project (sdproj) file are to saved to the users local disk drive as directed by the Developer.

Figure: The two files sent by the Developer

Select the SQL Deploy Project (sdproj) file

Open SSW SQL Deploy, and select the SQL Deploy Project (sdproj) file. This file contains all the settings required to run the SQL script.

Figure: Select the SQL Deploy Project (sdproj) file sent by the Developer

Select Single File mode

We are only running a single SQL file so select the Single File mode.

Figure: Select the Single File mode

Select the SQL script file that was sent by the Developer.

Figure: Select the SQL script file to run

Choose whether to create a new Database or modify an existing Database.

NOTE: If you are creating a new Database, the SQL script MUST contain a CREATE DATABASE statement. SQL Deploy will use the CREATE DATABASE statement to find the name of the Database. If you are not sure of how to do this, look at the 'ver100.sql' file in the samples folder.

Figure: Choose New or Existing Database

If you are creating a new Database specify the Server and security settings.

If you are updating an existing Database, click on the button to the right of the connection string to build the connection string using the standard Microsoft UDL wizard.

Figure: Connection string to an existing Database

Confirm any information then click on Finish to start the upgrade process.

Figure: Confirmation finish dialog

Using SQL Deploy to Reconcile Scripts with the Database

Overview of Reconcile scripts mode

The reconcile scripts mode allows you to check that the saved SQL scripts contain all of the changes to your Database. It checks that you have not left anything out before you upgrade a clients Database.

This feature is not available with the console application.

In this mode SQL Deploy creates a temporary Database then compares the temporary Database structure to your existing Database. Any differences in the SQL scripts are then displayed.

To do this you should run SQL Deploy in the Reconcile Scripts With Database Mode.

SQL Deploy will run all the scripts and create a temporary Database, it will then script out the Database and display any differences in the schemas of the Databases.

Figure: Report generated after a reconcile. Demonstrating missing data from the script.

6.Getting Started for the Developer

See the SSW SQL Deploy Developer Guide New!