Skip to end of metadata
Go to start of metadata

Migration scripts are a first-class artifact of ReadyRoll database projects, and are responsible for the entire deployment of your database.

Migrations use a change-script driven approach to deployment (as opposed to the model-driven approach of SSDT projects), where you script your database changes up-front and deploy the same set of scripts in a pre-defined order through each of your application environments. This results in consistent and repeatable deployments through to Production.

The trade-off is the up-front work required to get your scripts right during the development phase. ReadyRoll provides the DBSync tool to assist with the authoring and re-authoring of migrations, allowing you to work online to make your database edits, and then import your changes to generate your migrations.

Adding a migration to your database project

If you’ve already got some scripts written, or if you need to deploy scripts provided by a vendor (eg. ASP.NET Membership Provider object creation scripts), simply create a new project and then right-click the project node in Solution Explorer and select Add... Script. This will add an empty migration to the project, into which you can paste your script content.

The new script will be added to the project with the following auto-generated filename:

[nextNumber]_[date]-[time]_[username].sql

For example, 0005_20140416-2133_noland.sql.

If your script contains SQL statements that have already been executed against the target database, click Mark as Deployed to prevent the script from being executed again, otherwise click Deploy Project.

Guidelines to help you get the most out of migration scripts

  • Migration scripts are intended to be executed a single time only against a target database: if you have used a migration script to add a column to a table on your Production server, you should not remove or change the script that performed the change but instead add a new script to the project to drop the column. 
  • An exception to the previous point is in the scenario where your newly-generated migration script has not yet been checked into source control. This means you have the option to delete and re-import a migration if you are certain that it has not been deployed to any other database server other than your own.
  • The Xml fragment in the header of your migration scripts contain a unique identifier for the migration (ie. <Migration Id="{UniqueIdentifier}"/>). ReadyRoll uses this GUID to ensure that the script is executed a single time against the target database. Please be sure not to remove this comment section, as this may result in your script being executed more than once against your target database.
  • Migration scripts can be renamed at any time, even if the script has already been deployed. This is possible because the script's unique identifier is contained within the script itself and not in the filename.
  • The migration script filename must start with a number, however the rest of the name is up to you. We auto-generate a filename for you when the migration is added to your database project (see below), however you can change this after import.
  • If you are finding that the number of migrations in your project is starting to become unmanageable, try using semantic versioning with your database project to help organize your migrations by branch or release.

 

Alternatives to migrations

If you would like to have your migration executed with each modification to the script file, try using Programmable Objects (useful for deploying replaceable objects such as stored procedures, functions and views).

Alternatively, if you would like to perform an action each time you deploy the database project, try using a Pre or Post-Deployment script instead. These can be used for creating the database if it doesn't exist, altering database settings, or for static data.

  • No labels