What is the t key to SQL Upgrades project success? Preparation. Prep work is going to take 80-90% of the time. SQL Server install and setup is going to be an easy part.
Why is that important? Because to do this right – you only get one try! Or rollback is real nasty—a ton of downtime. The manager leading the project will not look very good.
Backstory
A few time ago, someone asked me how to test the SQL Migration with minimal downtime and the least impact on customers.
Say DB is called “InQuire_DemoA” on Source now. Here are the steps we will take:
- We will backup and restore ‘InQuire_DemoA” to the Destination server with precisely the same name: “InQuire_DemoA”. This DB is what you will test against. That way, you have less to reconfigure on your end. So you test against the same DB in the new location (NY as an example) as you have on Source (CA as an example) servers.
- At the same time, we will back up and restore “DemoA” from Source to Destination and configure log shipping from CA to NY. So ‘DemoA’ will be log shipped from Source to Destination as ‘DemoA_red9’. This DB will be a read-only copy. All data, stored procedures, table changes, etc. will be captured from Source to Destination.
- You will do testing on NY – After testing is complete and (typically) during the migration window, we will drop “DemoA” (test DB), rename “DemoA_red9” (read-only) to “DemoA” and set “DemoA” live. This now becomes your production DB.
Why Log Shipping?
- It can minimize downtime, even for large databases.
- Log shipping may not be new and shiny like availability groups, but it has very few blockers for implementation. After you get it running, it doesn’t add any additional workload to your legacy databases.
- Log shipping also supports a mix of SQL Server versions and editions like Standard or Enterprise, which other migration options lack.
- Log Shipping is easy to set up, made even easier by the Dbatools.
Conclusion
Database upgrades and migrations happen every 3-5 years. Or less. If you are using your internal staff, those people haven’t done this task a lot.
We have. We are typically running 2-5 simultaneous SQL Server Migration and Upgrade projects in tandem for various clients.
We’d love to talk to you about any questions you may have about your SQL Upgrade or Migration project specifics (even if you don’t go with us!)
