Миграция версий БД MSSQL

Приветствую!

В данном посте я хочу поделить мыслями на тему поддержки и миграции изменений в базах данных и MSSQL в частности, а также своим решением.

Проблема

Как правило, в более-менее серьезных проектах, есть несколько независимых окружений (environment).
Разработчик использует для написания и отлаживания кода системы свою базу, потом изменения кода и схемы базы данных перемещаются на окружение тестировщика, после чего переносятся на staging и production.
Опять же, в более-менее серьезных проектах, разработчиков и тестировщиков больше чем один, также и с их окружением.
Соответственно должен быть организован процесс миграции изменений не только кода, но и базы данных, причем желательно чтобы этот процесс был удобен и надежен.
Подробности под катом.

Варианты решения проблемы

Исходя из своего опыта участия в проектах, могу выделить несколько способов организации процесса миграции изменений базы данных:
1. В хранилище кода для каждой версии продукта создается отдельная папка, в которую складываются скрипты под номерами. И так для каждой версии. В этом случае, должен быть отдельный инженер развертывания (deploy engineer), который следит за всеми базами данных, поддерживает их актуальность и соответствие текущей версии кода. Данный процесс может успешно применяться в проектами с регулярными и запланированными релизами, но в случае непрерывных релизов уже может стать проблемой.
2. Есть выделенный человек ответственный за схему базы данных, которому разработчики присылают патчи. Эти патчи добавляются в хранилище кода (source control) и разворачиваются двумя разными bat скриптами, один из них создает окружения заново, второй просто применяет патчи. При этом подходе проблема соответствия кода версии базы данных значительно уменьшается, но при этом отследить какие именно были сделаны изменения, кем и когда уже значительно сложнее. Высокая вероятность ошибки.
3. И наконец, последний вариант и наиболее близкий мне, это использование специальных framework-ов для миграции версий базы данных, такие как FluentMigrator, .Net Migrator и прочие.

Мое решение

Для одного из проектов, мной было написано приложение, которое должно значительно упростить проблему миграции версий баз данных.
Код выложен на CodePlex.com под крайне либеральной лицензией MIT, так что можете использовать данный продукт как вам будет удобно!
Система представляет собой WinForm приложение состоящее из двух проектов. В качестве framework-а для миграции используется FluentMigrator. Вдаваться в подробности о том что такое FluentMigrator и с чем его едят я не буду, желающие прочитать подробности о данном framework-е могут обратиться к этой статье FluentMigrator — система версионных миграций.
Мой проект, с нехитрым названием «C# Database Migrator» лежит тут: https://csharpdatabasemigrator.codeplex.com/
Что система умеет:
1. Работать с несколькими типами базы данных, то есть вы можете создавать миграции для баз данных с разной структурой. Как это делается в коде немного ниже.
2. Система умеет создавать бэкапы баз данных, а также их восстанавливать.
3. В качестве миграции может быть использована директория со всеми входящими в нее .sql файлами.
4. В файле миграции можно проверять название базы данных. Это может быть полезно в случае если какие-то данные должны попасть только тестовую базу или наоборот.

Как начать использовать

1. Забрать исходники проекта https://csharpdatabasemigrator.codeplex.com/SourceControl/latest
2. Открыть проект в студии
3. Зайти в папку Default (папка по умолчанию для файлов миграции)
4. Создать файл миграции. Файл миграции представляет собой класс наследованный он базового класса Migration

using FluentMigrator;
namespace Migrations.Default
{
    [Migration(1, "Author: AuthorName; Description")]
    public class Migration_1: Migration
    {
        public override void Up()
        {
           // Migration Up code here
        }
        public override void Down()
        {
           // Migration Down code here
        }
    }
}

5. В атрибутах класса прописать номер версии и его описание, а также сам код для миграции. Например создания новой таблицы

Create.Table("Users")
    .WithIdColumn() 
    .WithColumn("Name").AsString().NotNullable();

Можно выполнить SQL команды:

Execute.Sql("DELETE TABLE Users");

Подробности синтаксиса можно прочитать тут: https://github.com/schambers/fluentmigrator/wiki/Fluent-Interface
6. Скомпилировать проект и запустить приложение:

7. Подключиться к серверу баз данных
8. Выбрать в выпадающем списке нужную базу данных
9. Выбрать тип операции «Update To latest Version» и нажать кнопку «Do Job»
10. Проверить лог в правой части формы

Как работать с несколькими типами базы данных

Все файлы миграции, относящиеся к одного схеме базы данных, должны находится в одном пространстве имен (namespace). По умолчанию это «Migrations.Default» по имени папки в проекте. Чтобы добавить еще один тип миграции нужно в проекте «Migrations» создать еще одну папку, например «MySecondDatabase» и отредактировать файл конфигурации «MigrationNamespaces.config»

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <MigrationNamespaces>
    <MigrationNamespace Name="Default" Path="Migrations.Default"/>
    <MigrationNamespace Name="My Second Database" Path="Migrations.MySecondDatabase"/>
  </MigrationNamespaces>
</configuration>

После этого, в главном окне приложения будет доступен тип «My Second Database».

Выполнение sql файлов из папки

1. Система может использовать в качестве миграции файлы из указанной папки. Все файлы будут отсортированы по имени и выполнены в момент применения миграции:

Как это все работает

Для выполнения миграции из c# кода надо подключить сборку «FluentMigrator.Runner» и выполнить один метод:

public Action<string> OnNewUpdateLog;

public void UpdateToLatestVersion(string databaseName, string migrationNamespace)
{
	//указывает событие, которое будет использоваться в качестве вывода всех сообщений от FluentMigrator-a
	var announcer = new TextWriterAnnouncer(OnNewUpdateLog);
	
	// получаем текущую сбору
	var assembly = Assembly.GetExecutingAssembly();
	
	// создаем новый контекст и указывает пространство имен, которое содержит классы миграции
	var migrationContext = new RunnerContext(announcer)
	{
		Namespace = migrationNamespace
	};
	
	var options = new MigrationOptions { PreviewOnly = false, Timeout = 60 };

	//создаем фабрику для SqlServer2008
	var factory = new FluentMigrator.Runner.Processors.SqlServer.SqlServer2008ProcessorFactory();
	var processor = factory.Create(GetConnectionString(databaseName), announcer, options);

	//создаем новый runner
	var runner = new MigrationRunner(assembly, migrationContext, processor);

	//выполняем миграцию к последней версии
	runner.MigrateUp(true);
	OnNewUpdateLog("Done");
}

Для создания и восстановления бэкапов баз данных есть два метода:

public void BackupDatabase(string databaseName, string destinationPath)
{
	var sqlServer = new Server(_connection);
	databaseName = databaseName.Replace("[", "").Replace("]", "");
	var sqlBackup = new Backup
		{
			Action = BackupActionType.Database,
			BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString(),
			BackupSetName = "Archive",
			Database = databaseName
		};

	var deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);

	sqlBackup.Initialize = true;
	sqlBackup.Checksum = true;
	sqlBackup.ContinueAfterError = true;

	sqlBackup.Devices.Add(deviceItem);
	sqlBackup.Incremental = false;
	sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);

	sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
	sqlBackup.PercentCompleteNotification = 10;
	sqlBackup.PercentComplete += (sender, e) => OnSqlBackupPercentComplete(e.Percent, e.Message);
	sqlBackup.Complete += (sender, e) => OnSqlBackupComplete(e.Error);
	sqlBackup.FormatMedia = false;
	sqlBackup.SqlBackup(sqlServer);
}

public void RestoreDatabase(string databaseName, string filePath)
{
	var sqlServer = new Server(_connection);

	databaseName = databaseName.Replace("[", "").Replace("]", "");
	
	var sqlRestore = new Restore();
	sqlRestore.PercentCompleteNotification = 10;
	sqlRestore.PercentComplete += (sender, e) => OnSqlRestorePercentComplete(e.Percent, e.Message);
	sqlRestore.Complete += (sender, e) => OnSqlRestoreComplete(e.Error);

	var deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
	sqlRestore.Devices.Add(deviceItem);
	sqlRestore.Database = databaseName;

	DataTable dtFileList = sqlRestore.ReadFileList(sqlServer);

	int lastIndexOf = dtFileList.Rows[1][1].ToString().LastIndexOf(@"\");
	string physicalName = dtFileList.Rows[1][1].ToString().Substring(0, lastIndexOf + 1);
	string dbLogicalName = dtFileList.Rows[0][0].ToString();
	if (!Directory.Exists(physicalName))
	{
		physicalName = sqlServer.MasterDBPath + "\\";
	}

	string dbPhysicalName = physicalName + databaseName + ".mdf";
	string logLogicalName = dtFileList.Rows[1][0].ToString();
	string logPhysicalName = physicalName + databaseName + "_log.ldf";

	sqlRestore.RelocateFiles.Add(new RelocateFile(dbLogicalName, dbPhysicalName));
	sqlRestore.RelocateFiles.Add(new RelocateFile(logLogicalName, logPhysicalName));

	sqlServer.KillAllProcesses(sqlRestore.Database);

	Database db = sqlServer.Databases[databaseName];
	if (db != null)
	{
		db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
		db.Alter(TerminationClause.RollbackTransactionsImmediately);
		sqlServer.DetachDatabase(sqlRestore.Database, false);
	}

	sqlRestore.Action = RestoreActionType.Database;
	sqlRestore.ReplaceDatabase = true;

	sqlRestore.SqlRestore(sqlServer);
	db = sqlServer.Databases[databaseName];
	db.SetOnline();
	sqlServer.Refresh();
	db.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple;
}

Подробности в классе DatabaseManager.cs

Всем спасибо!