Migrate database schemas using SQL Script Generation

There are many different scenarios for migrating database schemas between two database providers. It’s not uncommon for a development team to run into issues with their database provider mid cycle, or to just be attempting to support more than one database in the same code. Sometimes you just need to get the schema to another database because you have internal tools that use that vendor for part of your testing.

Whatever the reason, moving across vendors can be a painful process that usually results in a lot of hand written SQL scripts. The CornerstoneDB SQL Script Generator makes this process very easy to get a script from one database, but targeted to another vendor. You can also use the same vendor to just get the SQL scripts, if that is all you need.

From a single API, you can migrate schema between SQL CE 3.5, SQL Server 2005/2008/2008R2, VistaDB 4, and MySql 5.

What We are Going To Do in this Article

In this article, we are going to migrate the schema from a VistaDB database to SQL Server 2008. I will be using the Script Generator API from CornerstoneDB to generate a schema creation script.

The VistaDB database migrated is a sample database that simulates the schema found in a car racing organization. We use this database as a test system due to the complex nature of the relationships and lookups.

Previously, I wrote a blog about using SQL Server Integration Services to migrate a database from VistaDB to SQL Server. The process was complicated, and error prone. It took me several hours to do the initial setup, after the build of the package it took under a minute to do the migration of the schema, but what a painful few hours for a simple schema move. I only needed to do this once, but the SSIS package system is really built for things you need to run over and over.

Example VistaDB Database Schema

The schema includes lots of FK relationships and many to many mapping tables (DriversToRaces, DriversToTeams). This type of model was great for us to show how we handle complex FK generation correctly.

Getting into the Code

We will build a simple command line application to do the work. The API is the star of the show, so we want to demonstrate some code! After creating a new console application project, I will need to create a new method to handle the migration steps.

static void Migrate(string vistaDBConnection, string sqlServerConnection)
{
}

The new Migrate methods needs to accept connection string parameters for both my current VistaDB database and my SqlServer destination. Next I will need to reference the ScriptGenerator API from CornerstoneDB and create a new ScriptGenerator object.

using CornerstoneDB.ScriptGeneratorTool;

static void Migrate(string vistaDBConnection, string sqlServerConnection)
{
    ScriptGenerator generator = new ScriptGenerator();
    generator.ConnectionString = vistaDBConnection;
    generator.SourceProviderName = "vistadb";
    generator.DestinationProviderName = "sqlserver";
}

NOTE: The API is still in BETA and may have changed between the time this was posted and the product goes into production. Always look at the current documentation for specific samples.

In order to create a new script generator object, you will need to set properties for the source database connection string which we got from our methods first parameter, the source provider name which in this case is VistaDB and the destinations provider name which is Microsoft SQL Server.

This script generator object will allow us to get a list of SQL commands we can use to build our SQL Server copy.

CornerstoneDB Script Generator API

This sample is using the beta version of the script generator API which allows for script generation to a file, or it can handle the script as a list of strings (the actual SQL commands). This version of the API also allows for script generation using MySQL and Microsoft SQL CE for an amazing amount of script output from a single tool.

We will be dynamically building a SQL server database from the output SQL script, I will be using the second option and getting the script commands back to execute immediately rather than writing them to disk. You could just as easily put them to disk for processing by external tools, check them into source control, etc.

List<string> commands = generator.GetScriptCommands();

Dependency Order Preserved

The order of dependencies in the database is preserved in the output. The SQL code is ordered to allow all the commands to be executed without regard to foreign keys, or other dependencies. The Script Generator API handles all the complexity of the dependency chain for you. The resulting SQL can be used directly on a blank database to recreate the entire schema as it is existing in the original source database.

Put this Schema into a New SQL Server Database

After creating a new list of type string and filling it with the commands returned from my SQL script, I need to build some logic to execute them in SQL server. To do this, I will need to add a reference to the SqlClient and create a new method to execute my script.

using System.Data.SqlClient;

static void ExecuteScript(List<string> commands, string connectionString)
{
}

This new method takes a parameter of the script commands and the SQL server connection string. It then opens a connection to my SQL Server database and executes each command one at a time. This logic could be altered to handle exceptions if a command does not succeed and save progress, perform logging, but for this demonstration I’m going to keep things simple.

static void ExecuteScript(List<string> commands, string connectionString)
{
    using (SqlConnection connection = new SqlConnection())
    {
    connection.ConnectionString = connectionString;
    connection.Open();

    using (SqlCommand command = new SqlCommand())
    {
        command.Connection = connection;

        foreach (string s in commands)
        {
            command.CommandText = s;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}

First we create a new SqlConnection object and wrap it in a using statement, this is important because when I’m done with the connection it will dispose it of automatically and the GC will remove the object quickly. Next we set the connection string property of the SqlConnection to the connection string from the target SQL Server database and then open the connection.

Opening the connection to the database here allows us to execute each of my commands under one connection object, and not waste opening an entirely new connection per command.

Now that the connection is open, we will create a new SqlCommand object and set its connection to the SqlConnection. All that’s left to do is iterate over the string based script commands, assign them to the commands text and execute them.

Running the SQL Migration Code

The connection strings I use in this demonstration point to a VistaDB database on my C drive that I listed the schema and an empty SQL server database I created earlier named VDB (just add a new Database in SQL Server Management Studio). I also added a Stopwatch object to write out how long all of the logic took to process, let’s take a look.

static void Main(string[] args)
{
    Console.WriteLine("Starting to migrate");
    System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
    sw.Start();

    Migrate(@"Data Source = C:\VDB.vdb4", 
        @"Data Source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog = VDB;");

    sw.Stop();
    Console.WriteLine("Done..");
    Console.WriteLine(string.Format("Elapsed time:{0}", sw.Elapsed));
    Console.ReadLine();
}

Console Output after Running the App

Looks like the migration ran in a little over one second without any exceptions. This is what the schema of the new SQL server version looks like in SQL management studio.

What Do We Get in the Scripts?

The first version of the SQL Script Generator includes the generation of the database schema including the following:

• Table Schema
• Column Schema (including type conversion across vendors)
• Foreign Keys
• Primary Keys
• Auto increment Columns
• Default Values
• Indexes

Not supported in the initial release:

• Views
• Stored Procedures
• CLR Procedures
• Triggers
• User defined types (not cross database friendly)

Summary

The SQL Scripts themselves are available for you to use however you need them. They can be written to disk, added to source control, diff’d using another tool, etc.

One common use I find is in taking a snapshot of a schema as it is existing on a certain date. I will point to a database, and use the same vendor as the output (usually SQL Server to SQL Server). Then I generate the scripts and check them into TFS with a schema version number. Then I always know I can go back to that schema version at some later date.

Additional Resources

• CornerstoneDB Tools
• VistaDB 4
• Microsoft SQL Server