Database Migration

In the course of developing an application, you will change your model repeatedly. In development this is not so much of an issue as you can delete your database and recreate. After you deploy to production, this is not an option. The nHydrate framework does have a migration path for databases. This involves versioning databases. This is done by use the extended properties of SQL Server databases. Each time the installation tool is run on a database; the date and version are saved to the database as well. This lays the groundwork for versioning.

Each time you deploy to production, your model should be incremented in some way. Each model has a version property. It consists of four numbers separated by a period: major, minor, revision, and build. You can increment these numbers in any way you wish. However keep in mind that the upgrade algorithm respects the order of these numbers as well as the magnitude. For example the major number will also be used before the minor number and so forth down the series. Upgrades refresh the database from the current database version up to the current version. The order is important since the upgrades are run in order. If the database is on version 0.0.0.1 and the current model is 0.0.1.0, the upgrade might run the following versions in order 0.0.0.2, 0.0.0.3, and 0.0.1.0. In other words, all scripts are order by their respective versions and run in order. That allows you to upgrade any version of a database up to the latest version as long as you have kept a running sequence of changes.

To run the installer from the VS.Net environment set the installer project’s properties. Set the debug action to start an external program. The program to use is the .NET framework’s InstallUtll.exe. On my drive the path is “C:\Windows\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe”. Then set the command line arguments to the output DLL. By default this is the project name with “.DLL” on the end. The default would look something like this: “CompanyName.ProjectName.Install.dll”. Now when you run the project or choose debug application, an installer UI is displayed. The UI simply asks for database connection information. The installer assembly can also be run inside of a larger installation application with no UI if desired.

Now that we have covered how to run the application, we can address its makeup. The installer has three sections: create scripts, stored procedures, and upgrade scripts. The first consists of two script files “CreateSchema.sql” and “CreateData.sql”. The first file is run on every installation. It contains all tables, indexes, triggers, relationships, etc. The entire makeup of the database is in this file. The second script is the creation of all static data. This is data that has been defined in the model on specific tables.
The stored procedures are all the scripts needed for the framework to function. All inserts, updates, deletes, and selections are performed through stored procedures. These are recreated on each generation so they will be recompiled on the server for each installation.

Finally, the upgrade scripts are split into two places. There are pre-schema scripts and post-schema scripts. This is important since there are changes that need to be made before the schema and stored procedures are run, like adding a field to the database. There are also scripts that need to be run after the schema and stored procedures are run, like data insertion. In the installer project there is a scripts in the folder “Upgrade Scripts” for each model version generated. If you do not change the model version a new file is not generated. These scripts are run after the schema and stored procedures have been applied. In the “Upgrade Scripts\Generated” folder there is a scripts generated for each generation. You will notice these files are named not only with the standard four version numbers, but a fifth number has been added for generation version. This is a discriminator for the installer to sort the files before execution. You add any scripts here that need to be run before the stored procedures need to be run.

In summary, the installer is a very powerful way to create or upgrade a database. You can change your model without fear of the database getting out of sync. The order of an upgrade is as follows.
  • Scripts ordered by number in the “Upgrade Scripts\Generated” folder
  • Generated Schema script
  • Scripts ordered by number in the “Upgrade Scripts” folder
  • All Stored Procedures

Command Line

Assuming that you have a model with the company name "Acme" and a project name of "MyProject" an installer assembly will be created with the name "Acme.MyProject.Install.dll". In the examples below, we use this library to define a command line string to create and update databases.

Use the following syntax to get help text.
"C:\windows\Microsoft.NET\Framework\v2.0.50727\installutil.exe" Acme.MyProject.Install.dll -?

To create a database that does not exist, you must specify the database to create with the "-a" switch and the master database connection string with the "-m" switch. After this command completes there will be a new database on the current server with a name of "MyDatabaseName". The master database information is needed because we are creating a database and this requires access to the master database.
"C:\windows\Microsoft.NET\Framework\v2.0.50727\installutil.exe" Acme.MyProject.Install.dll -a:"Data Source=.; Initial Catalog=MyDatabaseName;User Id=sa;Pwd=MyPassword" -m:"Data Source=.;Initial Catalog=master;User Id=sa; Pwd=MyPassword"

If a database already exists and you wish to upgrade to a newly generated version, use the upgrade ("-u") switch. The command line below simply defines the connection information to the existing database and uses the "-u" switch to define an upgrade.
"C:\windows\Microsoft.NET\Framework\v2.0.50727\installutil.exe" Acme.MyProject.Install.dll -a:"Data Source=.; Initial Catalog=MyDatabaseName;User Id=sa;Pwd=MyPassword" -u

The command line functionality allows you to define an installer in a script or batch file. You can incorporate this into your build process to create databases on the fly, run unit tests, deploy application systems, etc.

Last edited May 27, 2010 at 7:46 PM by codetools, version 2

Comments

No comments yet.