Notes on Flattening Migrations

Here are some quick notes on a method to recreate migrations from scratch.

• Use Visual Studio 2013 2015 to create scripts for the new first migration so that the database will have all the same data as one would have had if they had migrated thru all the old migrations

  • From the Visual Studio menu, choose Tools | Sql Server | New Data Comparison...

• Data Compare Notes

  • You need two databases.

    • First, create a new database and do Update-Database to get it updated with all the migrations. Let's name the database RockRMS_Clean
    • Next, using Sql Server Management Studio, right click on your clean migrated database and choose Tasks | Generate Scripts. Do all the default settings on the wizard and you'll get a script that recreates the database, but with no data. Use this to create an empty version of the Rock Database. Edit the scripts so that the new database is named RockRMS_Empty, and then execute it

  • Use Data Compare and make the Source Database RockRMS_Clean and the Target Database RockRMS_Empty.

    • Follow all the steps in the wizard and you'll see a grid that looks like this

    • You will see a list of tables to generate scripts for. The ones that are checked are ones that have data in them. We need all of those except for a few, so uncheck these

      • [_MigrationHistory]
      • [Audit]
      • [ExceptionLog]

    • At the top left of the grid you'll see a button "Generate Script..". Click it. You'll get a nice script that contains a bunch of SQL that will help repopulate the database. However, we'll need to edit it first:

      • At the top of the script, remove everything until the end of the "ALTER TABLE ... DROP CONSTRAINT .." lines. Then, at the bottom of the script, remove all of the "ALTER TABLE ... ADD CONSTRAINT..." lines, and also the "COMMIT TRANSACTION" line. The ALTER TABLE CONSTRAINTS won't work because the constraint names might be different in the new database, and we don't need to wrap this in a transaction. Entity Framework does that for us.
      • Next, go find the sql script called CodeGen_EnableDisableAllConstraints.sql. It is in our Dev Tools\Sql folder. Run that on the Rock database. Copy and Paste the "DisableAllConstraints" code and insert it at the top of the Populate script. Then, copy and paste the "EnableAllConstraints" code and insert it at the bottom of the populate script.
      • Now, paste this as the first thing in the populate script

/* Pointer used for text / image updates. */
DECLARE @pv binary(16)

• Now, back in Visual Studio, do the following
  • delete all the migrations in the Rock.Migrations project
  • make sure your web.ConnectionStrings.sql RockContext is pointing to a database that doesn't exist (we want Add-Migration to create a new database, etc
  • in PackageManagerConsole run "Add-Migration CreateDatabase"
  • Remove the guts of CreateDatabase.Down()

Then edit the first migration making sure that it includes

• the result of "Add-Migration CreateDatabase" (your new first migration)

• the storedprocs, functions, etc that are in Rock-RMS\database

• Any special Schema things that the Add-Migration did not figure out. - Computed Columns and Indexes that Add-Migration didn't figure out. There are some on [Person] and possibly others. HINT: Try running the migration and then using Schema Compare to see what needs to be put in manually. NOTE! Make sure that the computed column is deleted before trying to create it. The CreateTable probably created the computed column as a regular field.

• the results of the populate script

• To see an example of what the first migration should look like when you're done, look in GitHub history to see what the FirstMigration has looked like before