Transactional migration for heterogeneous database.

[workgroupengineering]

I have an application that has heterogeneous database types (Access, Sqlite, Sql Server) with heterogeneous data.
I would like to understand if it is possible to do this or extend for do it:

            // Initialize the services
            var serviceProvider = new ServiceCollection()
                .AddLogging(lb => lb.AddDebug().AddFluentMigratorConsole())
                .AddFluentMigratorCore()
                .ConfigureRunner(
                    builder => builder
                        .AddJet(config=>
                            {
                                  config.WithConnectionString(AccessPath);
                                  config.WithTags("Access");                        
                            })
                        .AddSQLite(config=>
                            {
                                 config.WithConnectionString(SqLitePath);
                                 config.WithTags("Sqlite");                        
                            })
                        .AddSQLSever(config=>
                            {
                                  config.WithConnectionString(SqlServerConnectionString); 
                                  config.WithTags("SqlServer"); 
                            })
                        .ScanIn(typeof(...).Assembly).For.All())
                .BuildServiceProvider();

            // Instantiate the runner
            var runner = serviceProvider.GetRequiredService<IMigrationRunner>();

            // Run the migrations
            runner.MigrateUp();

• each database has its own VersionTable
• when runner.MigrateUp () is executed, MigrateUp is called for each registered database
• if one of the migrations fails on one db, all db will be rooled back.

[jzabroski]

I am not sure I understand your question. It sounds like you have unique databases for each environment. Why not create separate runners for each one? In terms of the differences between the two, the only difference should be the assembly you run. You can absolutely centralize the logic for the runner - it is just like the dotnet-fm global tool. So, it would seem the main thing you want to customize is the ScanIn, and you probably want to make that a parameter.

Keep in mind Access doesn't really have transactions.

Also, there is nothing baked in that will auto-discover which database runner will work.

[workgroupengineering]

Thanks for the reply, I need it when I call the migration to be performed on all three providers. If one of the migrations fails on a specific provider, it must roll back to all registered providers.

[jzabroski]

I see. Distributed transactions are tricky. If all databases are SQL Server, you can use MSDTC to override the default transaction coordination mechanism SQL Server uses. If you do so, and you use virtualization, make sure MSDTC is compatible with the write-through specification for your virtualization solution, as you might see disastrous performance consequences running effectively two transaction coordination mechanisms on top of one another.

The other caution I would give, specific to SQL Server, is you might have to deal with zombied transactions if you enable MSDTC. I also recommend disabling snapshot isolation if you have it enabled for the databases involved, for the same reasons as the virtualization solution warning. If you're trying to debug zombied transactions due to distributed rollback, you can use "Attention" events via SQL Server Profile/Extended Events to see the real reason a transaction got zombied.

I don't think you can use MSDTC with SQL Server, Jet and SQLite. You would need to manually coordinate any rollback. Probably the simplest semi-automated technique is to create your own Runner and in a catch statement, rollback any completed work. This technique is referred to in the database community as "compensation handlers".

Good luck

[workgroupengineering]

Thanks for the tips. Can you tell me if FluentMigrator supports TransactionScope?

[jzabroski]

TransactionScope is the means by which you enlist a transaction in a distributed transaction. If two or more SqlConnection(s) are nested, it will escalate the transaction to a distributed transaction.

FluentMigrator does not care about TransactionScope, nor does pretty much any system. It is an orthogonal concept. You should read up on how TransactionScope works when you have multiple databases, etc. Most people wrap TransactionScope in an Orchestration layer that sits above Services layer that has otherwise non-composable data access layer operations. In this way, it can create a new transaction within the same connection. If you're using dependency injection, you can get the current connection from your lifetime scope and call DbConnection.BeginTransaction. Ordinarily, I use proxies to automatically wrap code in transactions that either succeed or rollback if there is an exception, but that does increase the learning curve for Junior Developers c.f. Greg Young's talk 8 Lines of Code.

Also, as I already covered, Jet and Sqlite don't really support distributed transactions, so if SQL Server side fails, you will not be protected.

Good luck.