search

GFlisch / Arc4u.Guidance.Doc

Other
5 stars 1 forks source link

Automate the upgrade of Hangfire SQL Database Schema in MigrationTool #154

Open vvdb-architecture opened 1 year ago

vvdb-architecture commented 1 year ago

Is your feature request related to a problem? Please describe. When you are adding a Hangfire job with SQL Server as a backing store, you can't configure the Hangfire library to automatically create its database schema at service startup, since service accounts typically do not have DDL rights on the SQL Database.

For deploying in DEV and TEST environments, you can execute the script yourself (or just give the required rights to your service account). For deploying in ACC and PROD, you need to give Server Team the script to execute on the database server.

We need an automated solution for all environments which doesn't require adding rights to service accounts.

Describe the solution you'd like Since most applications using Hangfire with SQL Server as a backing store will also use SQL Server for the database layer, the application will have a MigrationTool. It would be nice to have a function in the Guidance that allow us to right-click on the MigrationTool project and choose a "Add Hangfire SQL Script Support", which would ask for the Hangfire Version (or deduce it automatically from the version used in the project) and automatically add the required support for adding the Hangfire database schema or updating it to the required version.

Describe alternatives you've considered The alternative is to do the "solution i'd like" manually. I will outline the steps here: these steps are exactly the ones that need to be automated by the Guidance:

Step 1

Create a folder named Hangfire in the MigrationTool project.

Step 2

Locate the install.sql script for Hangfire. This can be found in the tools directory that was created when adding the hangfire.sqlserver NuGet Package. For example, if the directory of your NuGet packages is C:\PRJ\Nuget\packages and the version of the hangfire.sqlserver package is 1.7.33, then the directory will be C:\PRJ\Nuget\packages\hangfire.sqlserver\1.7.33\tools: image

Step 3

Copy the install.sql script you've located in step 2 to the Hangfire folder you've created in step 1 and change the file properties to be an embedded resource. This is a choice: treating the file as an embedded resource avoids to have you copy the file to the output directory when deploying. But other options are possible, of course.

This install.sql script is very smart: it either creates the database schema for Hangfire if it does not exist, or updates it to the required schema version. Please note that version downgrades are not supported. The script also PRINTs messages about the various steps it is executing.

Step 4

Add a C# source file called HangfireScriptRunner.cs to the Hangfire directory you created in step 1 with the following content:

using Microsoft.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Reflection;

namespace $(ApplicationName).$(ServiceName).DataAccess.MigrationTool.Hangfire
{
    class HangfireScriptRunner
    {
        private const string ScriptName = "install.sql";
        private readonly string _connectionString;

        public HangfireScriptRunner(string connectionString)
        {
            _connectionString = connectionString;
        }

        private static string GetScriptContent()
        {
            using var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(typeof(HangfireScriptRunner), ScriptName);
            if (stream == null)
                throw new Exception($"The Hangfire script {ScriptName} was not found as an embedded resource");
            using var reader = new StreamReader(stream);
            return reader.ReadToEnd();
        }

        public void Run()
        {
            var script = GetScriptContent();
            using var connection = new SqlConnection(_connectionString);
            var server = new Server(new ServerConnection(connection));
            server.ConnectionContext.InfoMessage += ConnectionContext_InfoMessage;
            server.ConnectionContext.ExecuteNonQuery(script);
        }

        private void ConnectionContext_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            Console.WriteLine(e.ToString());
        }
    }
}

This file loads the install.sql script and executes it, making sure that any messages are output to the console (just like the MigrationTool is doing during any other of its migration steps).

Step 5

You need to add the following dependency to the MigrationTool .csproj file to make the above code compile:

    <PackageReference Include="Microsoft.SqlServer.SqlManagementObjects" Version="161.46367.54" />

Note that the version number of the package was chosen to minimize the upgrade impact on the existing components for the current .NET Core version.

Step 6

You need to update the code in Program.cs to make the HangfireScriptRunner callable.

The Container method is adjusted as follows:

    public static IServiceProvider Container()
    {
        if (null != _container) return _container;

        IConfiguration configuration = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json", true, true)
#if DEBUG
            .AddJsonFile($"appsettings.{Environment.MachineName}.json", true, true)
#endif
            .Build();

        IServiceCollection services = new ServiceCollection();

        var connectionString = configuration.GetConnectionString("$(ApplicationName)_$(ServiceName)");
        services.AddSingleton(new HangfireScriptRunner(connectionString));

        services.AddDbContext<DatabaseContext>(optionsBuilder =>
        {
            optionsBuilder.UseSqlServer(connectionString,
                                        (options) =>
                                        {
                                            options.MigrationsHistoryTable("DataAccess__MigrationsHistory", "app");
                                            options.MigrationsAssembly("$(ApplicationName),$(ServiceName).DataAccess.MigrationTool");
                                            options.CommandTimeout(30);
                                        })
                            .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
        });

        _container = services.BuildServiceProvider();

        return _container;
    }

Note that we read the connectionString once, since we need it in 2 places now: the HangfireScriptRunner and the DatabaseContext.

The start of the Main method makes sure we have access to the service provider:

    static void Main(string[] args)
    {
        var services = Container();
        var databaseCtx = services.GetService<DatabaseContext>();

        if (null == databaseCtx)
        ...

Since we can only move forward with Hangfire scripts, we add the following to the apply-all method, after the call to databaseCtx.Database.Migrate();:

                    // always run the hangfire script
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("Running the Hangfire script...");
                    Console.ForegroundColor = ConsoleColor.Yellow;
                    var hangfireScriptRunner = services.GetRequiredService<HangfireScriptRunner>();
                    hangfireScriptRunner.Run();

We can also add a separate option to apply only the Hangfire script

        app.Command("apply-hangfire", config =>
        {
            config.Description = "Run the hangfire script";
            config.OnExecute(() =>
            {
                try
                {
                    var currentColor = Console.ForegroundColor;
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("Running the Hangfire script...");
                    var hangfireScriptRunner = services.GetRequiredService<HangfireScriptRunner>();
                    hangfireScriptRunner.Run();
                    Console.ForegroundColor = ConsoleColor.Green;
                    Console.WriteLine("Finished running the Hangfire script.");
                    Console.ForegroundColor = currentColor;
                    return 0;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    return -1;
                }
            });
        });

Additional context With the above changes, executing the migration tool will show the extra statements output by the Hangfire scripts during deployment:

2023-03-23T16:51:49.1679947Z ##[section]Starting: Update Database with MigrationTool
2023-03-23T16:51:49.1820570Z ==============================================================================
2023-03-23T16:51:49.1820821Z Task         : PowerShell
2023-03-23T16:51:49.1821013Z Description  : Run a PowerShell script on Linux, macOS, or Windows
2023-03-23T16:51:49.1821206Z Version      : 2.170.1
2023-03-23T16:51:49.1821382Z Author       : Microsoft Corporation
2023-03-23T16:51:49.1821622Z Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/utility/powershell
2023-03-23T16:51:49.1821875Z ==============================================================================
2023-03-23T16:51:50.2373465Z Generating script.
2023-03-23T16:51:50.2424091Z Formatted command: . 'C:\agent\A1\Elia.Factory.Automation.AzDev.Agent.Install.1.1.0.5\_work\r2\a\CI-PRT.Scheduler\Drop\Packages\Pipeline\Steps\Ps\DatabaseUpdateMigrationTool.ps1' -MigrationToolPath 'C:\agent\A1\Elia.Factory.Automation.AzDev.Agent.Install.1.1.0.5\_work\r2\a\CI-PRT.Scheduler\Drop\Packages\MigrationTool' -MigrationToolExe 'PRT.Scheduler.DataAccess.MigrationTool.exe'
2023-03-23T16:51:50.2705506Z ========================== Starting Command Output ===========================
2023-03-23T16:51:50.2897295Z ##[command]"C:\WINDOWS\System32\WindowsPowerShell\v1.0\powershell.exe" -NoLogo -NoProfile -NonInteractive -ExecutionPolicy Unrestricted -Command ". 'C:\agent\A1\Elia.Factory.Automation.AzDev.Agent.Install.1.1.0.5\_work\_temp\8e72b95a-1dd1-44d2-8216-2657e7010b0d.ps1'"
2023-03-23T16:51:50.6235564Z Initiate Db update.
2023-03-23T16:51:50.6242142Z We found the following parameter values:
2023-03-23T16:51:50.6258822Z - MigrationToolPath: C:\agent\A1\Elia.Factory.Automation.AzDev.Agent.Install.1.1.0.5\_work\r2\a\CI-PRT.Scheduler\Drop\Packages\MigrationTool
2023-03-23T16:51:50.6264385Z - MigrationToolExe: PRT.Scheduler.DataAccess.MigrationTool.exe
2023-03-23T16:51:50.6307116Z Start MigrationTool Db update.
2023-03-23T16:51:50.6313061Z Target db update path: C:\agent\A1\Elia.Factory.Automation.AzDev.Agent.Install.1.1.0.5\_work\r2\a\CI-PRT.Scheduler\Drop\Packages\MigrationTool\
2023-03-23T16:51:50.6706876Z Execute MigrationTool ...
2023-03-23T16:51:51.5538187Z Migrate the database PRT_Scheduler_ACC.
2023-03-23T16:51:51.8406619Z Pendings migration that will be applied.
2023-03-23T16:51:51.8519183Z Running the Hangfire script...
2023-03-23T16:51:51.9428518Z Installing Hangfire SQL objects...
2023-03-23T16:51:51.9429032Z Database schema [HangFire] already exists
2023-03-23T16:51:51.9429334Z Table [HangFire].[Schema] already exists
2023-03-23T16:51:51.9429584Z Current Hangfire schema version: 7
2023-03-23T16:51:51.9429822Z Hangfire database schema installed
2023-03-23T16:51:51.9430058Z Hangfire SQL objects installed
2023-03-23T16:51:51.9430605Z Migration is finished.
2023-03-23T16:51:51.9545213Z MigrationTool execution done.
2023-03-23T16:51:51.9550863Z Db update done.
2023-03-23T16:51:52.0006115Z ##[section]Finishing: Update Database with MigrationTool