I just created a program for manually managing the SQL Server database version in two projects. There would be a third time when writing almost the identical utility code twice. Hence, in order to manage the SQL Server version, I wrote a NuGet package. It checks the database version at startup and then launches any upgrade scripts as necessary. It also guarantees that the scripts only run once.
I first started with code that required to be invoked in some way from the application’s start. The call went through the application layers since the entry assembly did not know the Repository assembly. The second time, I came up with a plan to initiate the migrations with reflection by creating an entry property in the assembly. By avoiding direct references to repository assemblies from the application start point, we could handle numerous SQL Server databases.
In the initial ASPHostPortal configuration, we saw that the scripts occasionally ran concurrently on two servers. A database lock for the migrations was implemented as a solution to this.
Making a standard NuGet package without any additional references to an OR-mapper was the next step. and incorporate extended logging. You can utilize the package by taking the next actions:
1. Create a user on your database with enough rights to modify your database
2. Add the migrations NuGet package to your repository and startup project.
3. Create a Migrations directory that contains SQL scripts for resource migration. The script must be named 1.sql or 201512011655.sql in numbers. They’ll go in ascending order.
4. Add the assembly attribute to AssemblyInfo.cs
[assembly: Migrations.Config.SqlServerMigration("myconnectionstringname")]5. Add the myconnectionstringname to your application configuration
6. Call the migration manager in your application startup to run all migrations:
MigrationManager.RunMigrations();
