I was in need of migrating a key DB (located on a fragile, never updated SQL 2008 R2) into a SQL 2016 Server hosting a “Fail over/Availability Groups Environment”). There could be no downtime given how essential this DB was to the business.
- Mirroring was not option given the difference in SQL versions between the two servers.
- Log Shipping was not an option given that I’m the “backup/restore” of the DB would have obstructed the “Availability Group Configuration.”
- CDC – was tempting; but don’t trust SQL 2008 R2.
So what to do? “Merge Statement” to the rescue.
I could write a “Merge Statement” on every table and provide my own process of “DB Replication.” Schedule the process to run via SQL Schedule, and keep both DBs the same. This will allow the “Operation” team to change pointers without risk of customer downtime. And, it also minimizes the impact of human error when changing those pointers.
But, what about the time-cost in writing a “merge” statement for every table in a DB? Depending on the number of tables, that might take forever.
What if we had a “Merge Script Generator?” Below is the code…
dba_GenerateAndExecuite_MergeSQL
It would also be helpful if you had a PROC that could generate a list of ordered table names based on their foreign keys…please the following PROC for that: dba_udf_ListTablesByForeignKeyOrder
Once you have the “Merge Script Generator,” a “sorted list of tables based on their Primary key,” the next thing would be to loop through all the DBs and apply run the “Merge Script Generator” on the each table.
Please see the following PROC for that:
Loop through DBs and appy PROC – dba_GenerateAndExecute_MergeSQL.sql
Be the first to comment