Is there an elegant and fluent syntax to enable Snapshot Isolation?

[Boris Modylevsky]

I need to turn ON the snapshot Isolation level as part of migration using FluentMigrator.

It can be done using plain SQL like this:

    public class EnableSnapshotIsolationLevelMigration : Migration
    {
        public override void Up()
        {
            EnableSnapshotIsolation(true);
        }
 
        public override void Down()
        {
            EnableSnapshotIsolation(false);
        }
 
        private void EnableSnapshotIsolation(bool enable)
        {
            Execute.Sql(
$@"DECLARE @enableSnapshotIsolation NVARCHAR(256)
SET @enableSnapshotIsolation = N'ALTER DATABASE ' + DB_NAME() + N' SET ALLOW_SNAPSHOT_ISOLATION {(enable ? "ON" : "OFF")}'
EXEC sp_executesql @enableSnapshotIsolation ");
        }
    }

Is there fluent API in FluentMigrator that allows the same?

[Dustin Venegas]

Boris,

I definitely don't see a direct way to set that option in the codebase. I really don't see an option to set generic options for MSSQL either. You'll need to author a method which takes in a MSSQL option and enabled/disabled.

I am no expert in extending FluentMigrator, but I think you've got two options. The codebase doesn't have a lot of activity, but I believe pull requests are welcome. Alternatively, an internal or external library you control should be sufficient.

It might be easiest to extend things by adding the following type of extension.

    public static class IExecuteExpressionRootExtensions
    {
        enum MssqlDatabaseOptions { ALLOW_SNAPSHOT_ISOLATION }

        private const string _alterDatabaseOptionSet = @"
DECLARE @enableSnapshotIsolation NVARCHAR(256)
SET @enableSnapshotIsolation = N'ALTER DATABASE ' + DB_NAME() + N' SET {0} {1}'
EXEC sp_executesql @enableSnapshotIsolation
";

        public static void WithMssqlOption(this IExecuteExpressionRoot executeExpressionRoot, MssqlDatabaseOptions option, bool enabled)
        {
            var enabledString = enabled ? "ON" : "OFF";
            executeExpressionRoot.Sql(string.Format(_alterDatbaseOptionSet, option.ToString(), enabled ? "ON" : "OFF"));
        }
    }

Best of luck!
-dev

[Gunnar Liljas]

FluentMigrator generally doesn't deal with database level modifications. If I needed something like that, I would simply add an extension method.

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fluentmigrator-google-group+unsub...@googlegroups.com.
To post to this group, send email to fluentmigrator-google-group@googlegroups.com.
Visit this group at https://groups.google.com/group/fluentmigrator-google-group.
For more options, visit https://groups.google.com/d/optout.