NMigrate

• NMigrate CodePlex site

Contents

• Problem
• Solution
• FileHandlers
• BatchFileHandler
• SqlFileHandler
• ConfigFileHandler
• CsFileHandler
• How to run the migration and get results
• Conclusion

Problem

Have you already shipped a new release and you had to manually repeat the same error prone procedures on lots of machines? If you say no, it means your deployment is automated -continuous deployment- and it's great. Or maybe your software is not yet in production so you don't have to worry yet about migration. Or maybe, someone else -an intern- is doing your deployment... but who will waste time when he will break things?

Sometimes you deploy changes by hand, sometimes you creat your own migration tool, sometimes you used something like DBMaintain to migrate your data.

DBMaintain is a great tool to help you migrate your databases. However, as a developer, you need to deploy code and database at the same time. You can't dissociate both.

Solution

NMigrate is a framework to help you to create migration projectd so you can easily migrate both code and database with the same tool. You will be able to execute your SQL scripts, C# scripts, batch scripts, config file replacement scripts, one after the other, in parallel or not, to your next version.

This tool is in its first release and already in production (for my own company's programs), so you can be sure that I'll do everything to make your (read "my") life the easier I can.

First of all, I want to thank Terence Parr (creator of Antlr and StringTemplate) for doing such a great and simple template engine. This guy wrote some books on parsers and DSL. If you want to learn more about this black art, I highly recommend it to you!

NMigrate uses StringTemplate for its simplicity against other template engines like NVelocity (after trying to use NVelocity, I was not sure if it really was a template engine). I don't plan yet to extend NMigrate to use other template engines. (Simplicity first, time will tell us if we need it.)

How to get started

A visual studio extension is provided, just install the latest release and run it.

Restart Visual Studio and now a new project type is shown.

How it works

So what did it create for us?

We have:

• NMigrate and StringTemplate binaries.
• Some script samples.
• Environment.conf is the configuration of the environment to deploy. (I'll come back to it later.)
• Environment.cs is the format of the configuration file as a WCF DataContract.
• Program.cs, well I think you know what it is.

Scripts are prefixed with a version number and executed in order. If two scripts have the same version number, then they are executed in parallel.

That's it, now let's take a look at the Program.cs file.

class Program
{
    static void Main(string[] args)
    {
        JsonConfiguration<Environment> configuration = 
          new JsonConfiguration<Environment>("../../Environment.conf");
        Migration migration = new Migration();
        migration.Configuration = configuration;
        migration.FileHandlers.Find<ConfigFileHandler>().DefaultAppExePath = 
                               "NMigrate1.exe";
        migration.FileHandlers.Find<SqlFileHandler>().DefaultConnectionString = 
          configuration.GetValue().Database.FullConnectionString;
        migration.VersionHandler = new FileVersionHandler("Version.txt");
	var result = migration.Migrate("../../Scripts");
	result.ThrowIfError();
}

The code just takes the configuration of the environment through the JsonConfiguration class, Environment is the type of the format of this config file.

Let's see Environment.cs; nothing complicated, just a classic WCF DataContract with some helper properties:

[DataContract]
public class Environment
{
    [DataContract]
    public class DatabaseEnvironment
    {
        [DataMember]
        public String FullConnectionString
        {
            get;
            set;
        }
        public string ServerConnectionString
        {
            get
            {
                var builder = new SqlConnectionStringBuilder(FullConnectionString);
                builder.InitialCatalog = "";
                return builder.ToString();
            }
        }
        public string DatabaseName
        {
            get
            {
                return new SqlConnectionStringBuilder(FullConnectionString).InitialCatalog;
            }
        }
    }

    [DataMember]
    public DatabaseEnvironment Database
    {
        get;
        set;
    }
}

As you can guess, here is the config file:

{
    "Database" :
    {
        "FullConnectionString" : 
          "Initial Catalog=Easydhome;Data Source=.\\SQLEXPRESS;Integrated Security=True;"
    }
}

This config file is supposed to be different when deployment environments are different.

You can extend NMigrate if you want an XML file, it's not really complicated, but do you really need it?

FileHandlers

The next lines need some explanation:

migration.Configuration = configuration;
migration.FileHandlers.Find<ConfigFileHandler>().DefaultAppExePath = "NMigrate1.exe";
migration.FileHandlers.Find<SqlFileHandler>().DefaultConnectionString = 
            configuration.GetValue().Database.FullConnectionString;

I set the configuration to my Migration object then configure FileHandlers, these classes will process your scripts.

There are four file handlers out of the box for .bat files, .sql files, .cs files, and .config files.

Some file handlers can be templated, it means that they pass through the template engine before being executed. BatchFileHandler returns true on the IsTemplated(FileInfo) method.

BatchFileHandler

Let's take a look at the 4.CreateMembershipUserTable.bat file in the sample.

"C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe" 
   -d "$o.Database.DatabaseName$" -C "$o.Database.ServerConnectionString$" -A m

As you can see, $placeholders$ are exposed, they have direct access to the environment configuration's file properties.

This file is handled by the BatchFileHandler.

By default, Out and Error TextWriters write on the console output (in red for errors).

SqlFileHandler

Let's take a SQL script sample:

IF EXISTS(SELECT name FROM sys.databases
WHERE name = '$o.Database.DatabaseName$')
    DROP DATABASE [$o.Database.DatabaseName$]
GO

As you can see, SQL files are also templated.

With the handler type, you can specify how to choose the connection string given the script file.

DefaultConnectionString is used in Program.cs:

migration.FileHandlers.Find<SqlFileHandler>().DefaultConnectionString = 
                configuration.GetValue().Database.FullConnectionString;

If you have multiple databases, you can provide a custom handler to select the right connection string based on the script name, with the SelectConnectionString property.

migration.FileHandlers.Find<SqlFileHandler>().SelectConnectionString = 
      (file) => file.Name.Contains("SecondaryDB") ? 
      env.SecondConnectionString : env.PrimaryConnectionString;

Internally, this handler uses the SqlCmd.exe tool, so you have to configure the path to it with SqlCmdPath, if it is different from the default one.

ConfigFileHandler

Here is an example of a templatized config file (1.App.config):

<configuration>
    <connectionStrings>
        <add name="myConnection" connectionString="$o.Database.FullConnectionString$"/>
    </connectionStrings>

ConfigFileHandler needs to know where to copy and how to rename this config file after the placeholder's replacement. For example, in the Program.cs file, we see:

migration.FileHandlers.Find<ConfigFileHandler>().DefaultAppExePath = "NMigrate1.exe";

It means that App.config is for the application NMigrate1.exe, so after being templatized, the config file will be moved to NMigrate1.exe's directory and renamed NMigrate1.exe.config.

ConfigFileHandler is not limited to the executable config file.

• *.Web.config scripts are templated and copied to DefaultWebSitePath.
• *.App.config scripts are templated and copied to DefaultAppPath.
• *.*.exe.config files are copied to their corresponding folder of ApplicationPaths.
• *.*.config files are copied to their corresponding website folder in WebsitePaths.

CsFileHandler

C# scripts are compiled on the fly during execution with CodeDOM and reference already loaded assemblies of your current AppDomain. Sometimes you will have compilation errors because of not referenced assemblies, it means that your AppDomain has not yet loaded these assemblies when compiling the script. To load them in the AppDomain, just declare a null variable of a type contained in the assembly before starting migration, and the CLR will load it.

Let's take a look at the script 5.CreateUsers.Mock.cs.

public class CreateUsers : IExecutable
{
    #region IExecutable Members

    public void Execute(Configuration args)
    {
        SqlMembershipProviderFactory factory = new SqlMembershipProviderFactory();
        factory.SetLowSecurity();
        factory.ConnectionString = 
           args.GetValue<Environment>().Database.FullConnectionString;

        var success = 
          factory.CreateMembershipProvider().CreateUser(
          "TestUser", "password");
        if(success != MembershipCreateStatus.Success)
            throw new Exception("Unexpected result when creating test user " + 
                                success.ToString());
    }

    #endregion
}

As you can see, you have full access to the environment's configuration.

SqlMembershipProviderFactory is a helper type of NMigrate to create SqlMembershipProviders without using the config file.

Errors are handled simply by throwing an exception that you will be able to see with MigrationResult.Error or by calling MigrationResult.ThrowIfError().

How to run the migration and get results

To run the migration from the current version, use the Migratoin.Migrate(string path, int currentVersion) method, this method is not expected to throw. Or you can just use built-in VersionHandler to persist the current version(FileVersionHandler, SqlDatabaseVersionHandler).

migration.VersionHandler = new FileVersionHandler("Version.txt");
var result = migration.Migrate("../../Scripts");
result.ThrowIfError();

Results are retrieved by version, and by script file:

Conclusion

That's it for now, this tool will evolve depending on your needs, my priority is to keep common things simple, I'll do my best to not over design it.