Applies to
ApexSQL Source Control
Summary
This article describes how to work with the Migration scripts feature.
Migration scripts are customizable SQL scripts created by user through ApexSQL Source Control containing any specific configuration changes, or overrides that need to be executed before or after applying an object change from source control, or they can be executed instead of a change. Configuration changes are, for example, disabling replication prior to applying a change and re-enabling it after the change is being applied, temporary disabling any ongoing maintenance or administrative task that runs against the database and that prevents changes to be applied properly. Instead of making configuration changes manually, each time a change needs to be applied, those changes can be saved as migration scripts, committed to source control, and run automatically, prior to applying changes. In addition, a migration script can be bound to a specific change (e.g. column rename) and as such, in this particular case, it will prevent data loss.
Why use migration scripts?
The main purpose of using the migration scripts is to ensure that database changes, being made and committed to source control, can be properly applied without breaking up database integrity, making configuration changes manually, or to prevent data loss.
For example, renaming a column will be treated by source control system as dropping a column with an old name, and creating a new column with a different name. This will result in data loss, when that specific change is applied against a database. With a migration script assigned to this change, a column will be renamed in a way the data won’t be lost.
Description
In this article, a table rename will be used to represent the migration scripts workflow.
Let’s say we have developers working on the separate copies of the same database linked to source control, working in dedicated development model.
One developer renames a table (e.g. Sales.SalesTerritory to Sales.SaleTerritories):
After, refreshing the Action center tab, table rename will be presented as follows:
The first two changes (marked in the image above) represent creating a new table Sales.SaleTerritories, and dropping the Sales.SalesTerritory table. The rest of changes show referenced objects modification due to a table rename change.
To add a migration script for this change, right click the first item in the list (that represents a new table creation), and select the Add migration script option from the right click context menu:
This initiates the new query where the migration script can be created:
As shown in the image above, this particular migration script will execute the sp_rename stored procedure instead of applying a change that will drop a table and create a new one.
In the above example, the existence of the Sales.Territory table is checked through the guard clause, and the migration script will be executed only if the appropriate table exists. Otherwise the migration will be skipped.
When the migration script is prepared, click the Save button and refresh the Action center tab to show the migration script along with changes:
To edit a migration script, right click on a database, and select the Migration scripts option from the More source control options submenu:
From the right click context menu, a migration script can be deleted or edited:
Another way to delete the migration script before it is committed, is to change the action in the Action center tab, from Commit (green arrow), to Get (blue arrow):
In addition, the migration script can be edited from the Action center tab, by right clicking an object change related to the migration script, and selecting the Edit migration script option:
Once the migration script is committed along with the appropriate changes, it will be stored in the Migration scripts folder on the repository:
Going one level more into the MigrationScripts folder, the exact migration script will be shown:
Another developer who has his own copy of a database linked to the same repository will have changes committed by the first developer shown in the Action center tab, with the Get action set by default:
When applying changes, the migration script will be executed instead of the actual change, in this particular case.
Before applying changes against a target database, ApexSQL Source Control will create a temporary database (copy of the target one), and try to apply changes.
|
Quick tip: The temporary database will be deleted regardless of the results of applying changes |
If everything goes fine, the Get changes wizard will be initiated:
In the Script tab of the wizard, the entire script that will be executed against a database will be shown. In this example, only the sp_rename stored procedure will be executed, in order to rename a table.
|
Quick tip: Depending on the actual change, a migration script can be executed before the change, after the change, or instead of the actual change. |
As a result, a table in the target database will be renamed, without data loss:
Q: Is there a way for the migration script to be deleted after it is committed?
A: Yes, once it is added to a specific change, it can be deleted afterwards, through the Action center tab, or through the Migration scripts form.
Q: Is there a way to add another migration script to an object change if the same object has already been changed and committed with a migration script?
A: Yes, in this case, when applying changes, the migration script committed first will be executed before the newly added migration script.
Q: Is there a way to review all migration scripts for a single database?
A: Yes. Right click on a linked database, and select the Migration scripts command from the More source control options submenu. The Migration scripts form appears, showing all the migration scripts related to the selected database:
Q: Is there a way to commit two migration scripts for two different object changes in a single changeset?
A: Yes, multiple changes along with the appropriate migration scripts can be committed within a single changeset.
