Using DbUp - The first go...

So a release was coming up with multiple SQL scripts to be executed in a sequence. The best bet was to use a tool such as DbUp to automate the process.

DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date.

I used the default example on http://dbup.github.io/ to start off. But my scripts timed out as I had the embedded SQL scripts doing some serious data uploads as well.

A solution to the timeout problem can be found here https://phejndorf.wordpress.com/2014/07/02/setting-execution-timeout-in-dbup/

So the final code looks like the following with a deviation to use a configuration file with the connection string:

static int Main(string[] args)
        {
            var connectionString =
                args.FirstOrDefault()
                ?? ConfigurationManager
                .ConnectionStrings["DefaultConnection"]
                .ConnectionString;
 
            var upgradeEngineBuilder =
                DeployChanges.To
                    .SqlDatabase(connectionString)
                    .WithScriptsEmbeddedInAssembly(
                    Assembly.GetExecutingAssembly());
 
            upgradeEngineBuilder.Configure(c =>
            {
                // 30 minutes in seconds
                c.ScriptExecutor.ExecutionTimeoutSeconds = 30 * 60; 
                Console.WriteLine("Configure ExecutionTimeoutSeconds to "
                    + c.ScriptExecutor.ExecutionTimeoutSeconds);
            });
 
            var upgrader = upgradeEngineBuilder.LogToConsole().Build();
 
            var result = upgrader.PerformUpgrade();
 
            if (!result.Successful)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(result.Error);
                Console.ResetColor();
                Console.ReadKey();
                return -1;
            }
 
            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("Success!");
            Console.ResetColor();
            Console.ReadKey();
            return 0;
        }


No comments:

Post a Comment