How V2 migration scripts are used in deployment
Published 18 March 2014
We're going to remove the migrations V2 beta from SQL Source Control in version 5. We're replacing it with an improved version of the original migrations feature that supports more things (including Git, branching, and merging).
We'll be publishing more information about this soon.
This page refers to the Migrations V2 beta. For information about how to use V1 migration scripts, see Working with migration scripts.
Imagine a database developer, Lucy, is working on a database. We'll call Lucy's current version of the database revision A.
Lucy renames a table and adds a migration script to prevent data loss (just like this example). Then she commits the change, including the migration script, to source control. This creates revision B.
Another developer on Lucy's team, Sam, has his own database. This is still at revision A. He updates it to revision B and gets Lucy's change.
To do this, SQL Source Control runs a series of steps:
1SQL Source Control stores migration scripts in table-valued functions on the database. To check if there are migration scripts to run in this deployment, SQL Source Control compares the table-valued functions in revision A (in Sam's database) and B (in source control). |  |
2SQL Source Control finds Lucy's migration script in revision B, and finds that it doesn't exist in Sam's database. This means it hasn't been run on Sam's database yet. |  |
3Before SQL Source Control runs the migration script on Sam's database, it creates a temporary database and copies Sam's schema to it. This is so Sam's database is unaffected if any of the following steps fail. You can set if the database is created on the server or on LocalDB. For more information, see Setting the location of the temporary database. |  |
4SQL Source Control runs the migration script on the temporary database. This creates a temporary new revision, A1. Revision A1 consists of revision A plus the changes made by Lucy's migration script. |  |
5To determine the remaining differences, SQL Source Control compares revision A1 and revision B. Sam's database is still unchanged at this point. |  |
6To update the temporary database to revision B, SQL Source Control writes a new script with the remaining changes. We'll call this the remainder script. SQL Source Control will only run the remainder script script on the temporary database, which is at revision A1. The remainder script won't work on Sam's database, which is still at revision A. |  |
7SQL Source Control prepends Lucy's migration script from step 2 to the remainder script from step 6. This creates the final deployment script. |  |
8Finally, the deployment script is run on Sam's database to update it to revision B. The temporary database is deleted. |  |
