Tern is a relational database migration library for .NET which generates migration scripts that:

• Are idempotent (you can run them multiple times on the same database)
• Validate the continuity of the migration (like rejecting scripts that have been changed after being deployed)

Ternkey builds on Tern and adds turnkey (eh?) integration into Web Deploy.

Install the Ternkey package onto your web application project:

PM> Install-Package Profero.Ternkey

Or if you just want to reference the library directly:

PM> Install-Package Profero.Tern

This will:

1. Add some starter scripts into Database\default\1.0.sql
2. Build obj\[Configuration]\default-migration.sql when the project is built (if the version scripts have changed, that is)
3. Include the script in when deploying or packaging your web project.
4. Define a web deploy parameter default-Migration Connection String

The default structure used by Ternkey is:

Database
  /[schema]
    /1.0.sql
    /1.1.sql
    /2.0.sql

schema is a simple grouping for related scripts. They can, but don't have to, relate to schemas in SQL server. For example, you might want to include one for your application's database and another for ASP.NET's membership tables.

Version scripts default to the format [version]-*.sql, where version is a 4 (or less) part version number. Each script contains the SQL statements that make the changes.

Scripts can contain the GO batch separator and will be executed in parts using sp_executesql.

Scripts can contain a number of metadata comments at the start:

-- This is a description of the script and will appear in the DatabaseVersion table
-- Skip: EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Person')

CREATE TABLE [User]
(
  [ID] INTEGER IDENTITY(1,1) NOT NULL
  [Name] NVARCHAR(256) NOT NULL
)

The desription, which is unprefixed, will be used recorded in the DatabaseVersion table for auditing purposes.

The skip statement allows Tern to be introduced to untracked databases by skipping version scripts that match a given query.

The scripts that Tern generates guarantee idempotency by recording which scripts have been run in a database table. The generated script only runs scripts that have not previously run.

The generated scripts attempt

1. Ensure deployed script content has not changed by comparing it to a computed hash
2. Ensure intermediate versions (eg. 1.0, 2.0, 1.5) have not been added after deployment

The Skip metadata statement (see Metadata) can be used to introduce Tern to an untracked database or as a way of using Tern without recording versions in a database table.

Using Skip statements brings the responsibility of consistancy entirely on the version scripts, since there is no way of ensuring the skip statement is representative of the changes made by the script.

We welcome bugfixes and ideas around the provider model. In order to run the automated tests you will need:

1. NuGet 2.7+
2. SpecFlow Visual Studio Extension
3. Machine.Specifications Visual Studio Extension

Just clone/fork, open the solution, build and run the tests using Test Explorer or ReSharper.

Tests in MSBuild.SystemTests fail with "DeploymentManager" error

The type initializer for 'Microsoft.Web.Deployment.DeploymentManager' threw an exception.

This is caused by a rogue provider installed by SQL 2012. Remove the "Microsoft.Data.Tools..." key from both:

HKLM\Software\Microsoft\IIS Extensions\msdeploy\3\extensibility HKLM\Software\Wow6432Node\Microsoft\IIS Extensions\msdeploy\3\extensibility

More information: http://stackoverflow.com/questions/6351289

1. /* Multiline comments */ are not supported as metadata in scripts (only -- single line comments)
2. The connection string will not appear in the Publish dialog and will need to be populated manually via the publish profile (for MSBuild based deployments) or SetParameters/Commandline (for package based deployments)
3. Properties that change options/conventions do not appear in project properties and must be edited manually in the project file
4. Transactional migrations is currently an all-or-nothing option

1. Syntax checking, integrated into the Visual Studio build process (already available on a branch)
2. Automated generation of simple change scripts
3. Automated testing of version scripts (with comparison against canonical schema)
4. Property page UI for migration options

A Tern is a family of migratory birds. The Arctic Tern, specifically, has the longest known migration path of any animal.

Migration? Geddit? Yeah...