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 Source Control 3
- SQL Compare Pro 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've already created a copy of the production database as a baseline to develop against. They've linked their development copy of the database to source control, and will now set up the ability to create and manage migration scripts.
The example has four stages:
- Set up the databases
- Specify a migration scripts location
- Create a migration script
- 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:
View the SQL creation script for the databases (click to expand)
- 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:
- 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.
- Next to the migration 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.
- Click Create Folder.
The Create Folder dialog box is displayed.
- In Folder Name, type a name for the folder.
In this example, call the folder WidgetMigrations
- Click Create.
The new folder is displayed on the Select Migration Scripts Repository dialog box.
- Make sure the Widget Migrations folder is selected, and click Select.
The new migration scripts location is selected
- 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:
This updates all rows in the table with the value <Unset> for the WidgetName column.
This adds a NOT NULL constraint on the WidgetName column.
If we commit this change and then try to deploy it to WidgetStaging 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:
- 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:
- Make sure 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 specify a name for the script and edit it to prevent errors in deployment.
Before the final
ALTER TABLEstatement in the script, add the following SQL:
- Click Proceed to commit.
SQL Source Control displays a confirmation dialog box:
- Click Save.
The migration script is now listed on the Commit Changes tab:
- 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:
- In SQL Compare, on the Project Configuration dialog box, select the latest version of WidgetDev as the source, andWidgetStaging as the target:
Click Compare Now.
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
- In the comparison results pane, make sure the check boxes for all objects with differences are selected:
- Click Deployment Wizard.
- On the first page of the Deployment Wizard, select a deployment method.
In this example we will deploy using SQL Compare.
- 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.
- Click Next.
- 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 deployment script.
- Click Deploy Now.
- A confirmation dialog box is displayed. Click Deploy Now to continue.
SQL Compare runs the deployment.
The comparison results are displayed, showing that WidgetDev and WidgetStaging are now the same. The columnWidgetName 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.