Worked example: Deploying with migration scripts - SQL Source Control

This example shows you how to create a migration script for a database, so you can deploy automatically without fear of data loss.

This example uses:

• SQL Compare Professional Edition, version 10
• The Subversion source control system
• The Tortoise SVN client for Subversion

In the example, the Magic Widget Company has a SQL Server database running on a live web server. This database contains a number of tables, views, stored procedures, and other database objects. The Magic Widget Company's development is working on an update to this database. They have already created a copy of the production database, as a baseline to develop against.

They have linked their development copy of the database to source control, and will now set up the ability to create and manage migration scripts.

To follow this example, you should download and install:

• the latest version of TortoiseSVN
• SQL Source Control 3.0
• SQL Compare 10

The example has four stages:

1. Set up the databases
2. Specify a migration scripts location
3. Create a migration script
4. Deploy the migration script

1. Set up the databases

This worked example uses the WidgetDev and WidgetStaging databases.

To create the databases on your SQL Server:

1. Click here to view the SQL creation script for the databases
2. Copy the script, paste it into a query window in SQL Server Management Studio, and run it.

The databases and their schema are created.

2. Specify a migration scripts location

This example assumes WidgetDev is already linked to source control, but no migration scripts folder has been set up.

Linking associates the database with a location in source control. For detailed instruction on linking a database to source control, see: Linking a database to source control

Migration scripts are customizable change scripts that SQL Compare uses in deployment. They can be used to avoid data loss and to avoid the need to repeatedly make manual configuration changes.

To specify a location to store migration scripts:

1. On the SQL Source Control Setup tab for the WidgetDev, click Setup link:

   

   The Link Migrations Repository dialog box is displayed:

   

   No migration scripts folder is set up, so we will create one.

2. Next to Migrations scripts repository URL, click Browse

   The Select Migration Scripts Repository dialog box is displayed:

   

   The best place to store your migration scripts depends on your development environment. In this example, we will create a sibling folder at the same level as the database schema.

   For more information, see Working with Migrations

3. Click Create Folder

   The Create Folder dialog box is displayed.

4. In Folder Name, type a name for the folder.

   In this example, call the folder WidgetMigrations

5. Click Create

   The new folder is displayed on the Select Migration Scripts Repository dialog box.

6. Ensure the Widget Migrations folder is selected, and click Select

   The new migration scripts location is selected

7. On the Link Migrations Repository dialog box, click Link

   Migration scripts are now set up.

3. Create a migration script

In this example, we want to add a NOT NULL constraint to a column in the WidgetDescriptions table in WidgetDev.

In SQL Server Management Studio, open a new query window, and run the following SQL queries:

UPDATE [WidgetDescriptions] SET WidgetName = '<Unset>' WHERE WidgetName IS NULL

This updates all rows in the table with the value <Unset> for the WidgetName column.

ALTER TABLE [WidgetDescriptions] ADD CONSTRAINT CK_WidgetName_NOTNULL CHECK (WidgetName IS NOT NULL)

This adds a NOT NULL constraint on the WidgetName column.

If we commit this change and then try to deploy it to WidgetTest using SQL Compare, the deployment will fail; a default value is required when adding a NOT NULL constraint to a column with existing data.

We can specify a default value if we commit the change as a migration script:

1. In SQL Source Control, on the Commit Changes tab, right-click the change to WidgetDescriptions, and click Add New Migration Script:

   

   The Create Migration Script dialog box is displayed:

   

2. Ensure the table WidgetDescriptions is selected, and click Create and Edit Script

   A new query window opens, populated with the script to deploy the change:

   

   Here you can edit the script to prevent errors in deployment.

3. Before the final ALTER TABLE statement in the script, add the following SQL:

   UPDATE [WidgetDescriptions] SET WidgetName = '<Unset>' WHERE WidgetName IS NULL

   GO

4. Click Proceed to commit.

   SQL Source Control displays a confirmation dialog box:

   

5. Click Save.

   The migration script is now listed on the Commit Changes tab:

   

6. Type a commit comment, and click Commit.

   The migration script is committed to source control. SQL Compare can now use the script during deployment.

4. Deploy the migration script

Now we have created a migration script for the change, we can deploy it to WidgetStaging using SQL Compare:

1. In SQL Compare, on the Project Configuration dialog box, select the latest version of WidgetDev as the source, and WidgetStaging as the target:

   

2. Click Compare Now.
3. The comparison results are displayed:

   

   You can see changes to WidgetDev that we will deploy to WidgetStaging.

   The change to the table WidgetDescriptions is covered by the migration script we created.

   When SQL Compare is using a migration script to augment its own generated change script, this is indicated with the icon

4. In the comparison results pane, ensure the check boxes for all objects with differences are selected:

   

5. Click Deployment Wizard.
6. On the first page of the Synchronization wizard, select a synchronization method.

   In this example we will synchronize using SQL Compare.

7. Click Next.

   The Review migration scripts page of the wizard is displayed:

   

   The upper pane of the page lists the migration scripts you can select to include in the deployment.

   The lower pane shows where in deployment SQL Compare will use the selected scripts.

   In this example, the migration script we committed is listed, and included by default.

8. Click Next.
9. The Review script page of the wizard is displayed, showing the generated deployment script.

   The migration script we selected to include forms part of the synchronization script.

10. Click Deploy Now.
11. A confirmation dialog box is displayed. Click Deploy Now to continue.

    SQL Compare performs the deployment.

    The comparison results are displayed, showing that WidgetDev and WidgetStaging are now the same. The column WidgetName now has a default value, and no data was lost in the deployment.

The migration script we created will be re-used in future deployments, so there is no need to manually add the default values each time you deploy.