Why choose FluentMigrator

[jzabroski]

I recently answered this question on the SQL Server sub-reddit: https://www.reddit.com/r/SQLServer/comments/k1eg1m/how_do_you_handle_versioning_and_migrations/gewcllz/?utm_source=reddit&utm_medium=web2x&context=3

[jzabroski]

An example of the code written to alter all image, text and ntext data types in a 500GB SQL Database is below.
It is an exercise to the reader to implement EmbeddedResourceUtility.GetCsvData<T>, but the idea is that it reads a CSV file saved as an embedded resource inside the FluentMigrator Migrations project assembly.
The general approach of going column by column, row by row, was the only approach that scaled for me.

    static class AlterColumnExtensions
    {
        public static void AlterColumnDataTypes(this Migration migration, string columnChangesFilePath)
        {
            var columns = GetColumns(columnChangesFilePath);

            var columnsInBaseTypes = columns.Where(c => c.TableType == "BASE TABLE").ToList();

            var columnsInViews = columns.Where(c => c.TableType == "VIEW").ToList();
            var distinctViews = columnsInViews.Distinct(new DistinctViewComparer()).ToList();

            foreach (var column in columnsInBaseTypes)
            {
                ConvertColumnAndRefreshLargeObjectPool(
                    migration,
                    column.SchemaName,
                    column.TableName,
                    column.ColumnName,
                    column.NewDataType,
                    column.MaxFieldLength,
                    column.Nullability,
                    column.CollationName);
            }

            // Refreshing the views referencing columns that were previously TEXT and NTEXT may prevent strange bugs
            // due to stale metadata.
            foreach (var view in distinctViews)
            {
                migration.Execute.Sql($"EXECUTE sp_refreshview N'{view.SchemaName}.{view.TableName}'");
            }
        }

        private class DistinctViewComparer : IEqualityComparer<Column>
        {
            public bool Equals(Column g1, Column g2)
            {
                if (g1 == null && g2 != null) return false;
                if (g1 != null && g2 == null) return false;
                // ReSharper disable once ConditionIsAlwaysTrueOrFalse
                if (g1 == null && g2 == null) return true;
                return g1.SchemaName == g2.SchemaName && g1.TableName == g2.TableName;
            }

            public int GetHashCode(Column obj)
            {
                return $"[{obj.SchemaName}].[{obj.TableName}].[{obj.ColumnName}]".GetHashCode();
            }
        }

        private static void ConvertColumnAndRefreshLargeObjectPool(
            this Migration migration,
            string schemaName,
            string tableName,
            string columnName,
            string newDataType,
            string maxFieldLength,
            string nullability,
            string collationName)
        {
            
            migration.Execute.Sql($@"
ALTER TABLE [{schemaName}].[{tableName}] ADD [{columnName}_TEMPORARY] {newDataType}({maxFieldLength}) COLLATE {collationName} {nullability} CONSTRAINT [DF_{schemaName}_{tableName}_{columnName}_TEMP] DEFAULT '';
");
            migration.Execute.Sql($@"
ALTER TABLE [{schemaName}].[{tableName}] ALTER COLUMN [{columnName}] {newDataType}({maxFieldLength}) COLLATE {collationName} {nullability} ;
");
            migration.Execute.Sql($@"
DISABLE TRIGGER ALL ON [{schemaName}].[{tableName}]
");

            // As described in: https://web.archive.org/web/20170429104953/http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx
            // SQL Server 2005 deprecated NTEXT in favor of NVARCHAR(MAX), and convert TEXT to VARCHAR(MAX).
            // For TEXT and NTEXT, T-SQL string functions, such as SUBSTRING, do not have the same performance on 2005 as it did on 2000.
            // Ergo, it makes sense to convert NTEXT to NVARCHAR(MAX), and convert TEXT to VARCHAR(MAX).
            //
            // However, naively altering the column can actually make things worse, because, while SQL Server will alter the column,
            // it won't use the default NVARCHAR(MAX) setting of text in row, but will keep the text in the LOB structure,
            // and still use pointer lookups to get the text out of the LOB. In fact, NVARCHAR(MAX) with text not in row actually performs
            // WORSE than NTEXT when doing SUBSTRING calls.
            //
            // So, how do we tell SQL Server to move text from the LOB structure to the table (if less than 8,000 bytes)?
            // Simple: After running your ALTER COLUMN, run an UPDATE statement setting the column value to itself, like so:
            //
            // UPDATE dbo.testTable SET testText = testText
            // 
            // Basically, we have to force SQL Server to re-evaluate each row and whether that row should be stored
            // in-line or in the LOB structure.
            migration.Execute.Sql($@"
UPDATE [{schemaName}].[{tableName}]
SET [{columnName}_TEMPORARY] = CAST([{columnName}] AS {newDataType}({maxFieldLength}))
");
            migration.Execute.Sql($@"
UPDATE [{schemaName}].[{tableName}]
SET [{columnName}] = [{columnName}_TEMPORARY]
");
            migration.Execute.Sql($@"
ALTER TABLE [{schemaName}].[{tableName}] DROP CONSTRAINT [DF_{schemaName}_{tableName}_{columnName}_TEMP]
");
            migration.Execute.Sql($@"
ALTER TABLE [{schemaName}].[{tableName}] DROP COLUMN [{columnName}_TEMPORARY]");

            migration.Execute.Sql($@"
ENABLE TRIGGER ALL ON [{schemaName}].[{tableName}]
");
        }

        /*
         * GetColumns csv data source was generated via:
SELECT
	ist.TABLE_SCHEMA AS SchemaName,
	ist.TABLE_NAME AS TableName,
	ist.TABLE_TYPE AS TableType,-- we need to call sp_refreshview on any views after we're done
	isc.COLUMN_NAME AS ColumnName,
	isc.DATA_TYPE AS OldDataType,
	CASE isc.DATA_TYPE
		WHEN 'ntext' THEN 'NVARCHAR'
		WHEN 'text' THEN 'VARCHAR'
		WHEN 'image' THEN 'VARBINARY'
	END AS NewDataType,
	CASE
		WHEN isc.DATA_TYPE = 'ntext' AND isc.CHARACTER_MAXIMUM_LENGTH = 1073741823 THEN 'MAX'
		WHEN isc.DATA_TYPE = 'text' AND isc.CHARACTER_MAXIMUM_LENGTH = 2147483647 THEN 'MAX'
		WHEN isc.DATA_TYPE = 'image' AND isc.CHARACTER_MAXIMUM_LENGTH = 2147483647 THEN 'MAX'
		ELSE 'Unknown'
	END AS MaxFieldLength,
	CASE isc.IS_NULLABLE 
		WHEN 'YES' THEN 'NULL'
		ELSE 'NOT NULL'
	END AS Nullability,
    isc.COLLATION_NAME as CollationNme
FROM
	INFORMATION_SCHEMA.TABLES ist
	INNER JOIN INFORMATION_SCHEMA.COLUMNS isc
		ON ist.TABLE_CATALOG = isc.TABLE_CATALOG
		AND ist.TABLE_SCHEMA = isc.TABLE_SCHEMA
		AND ist.TABLE_NAME = isc.TABLE_NAME
WHERE
	isc.DATA_TYPE IN ('image', 'text', 'ntext')
ORDER BY ist.TABLE_TYPE ASC
         */
        private static IList<Column> GetColumns(string columnChangesFilePath)
        {
            return EmbeddedResourceUtility.GetCsvData<Column>(
                typeof(Column).Assembly,
                columnChangesFilePath);
        }

        public class Column
        {
            public string SchemaName { get; set; }

            public string TableName { get; set; }

            public string TableType { get; set; }

            public string ColumnName { get; set; }

            public string OldDataType { get; set; }

            public string NewDataType { get; set; }

            public string MaxFieldLength { get; set; }

            public string Nullability { get; set; }

            public string CollationName { get; set; }
        }
    }