A bit over a year ago, I blogged about my experience migrating a test SQL Server instance from a VM to a physical machine with a little help from my friends. That migration went well and the instance has been running trouble-free ever since. But it’s small potatoes. A modest instance, it’s only about 5% the size of production. With SQL Server 2008R2’s EOL looming, it was time to migrate production to SQL Server 2016.
It’s a pretty beefy setup:
- 2-node Failover Clustered Instance
- 16 cores
- 768GB RAM
- 4 TB of storage
- Over 8000 databases
How do you move eight thousand databases in a reasonable amount of time? I spent about an hour and a half one morning hashing ideas out w/ folks in the dbatools Slack channel, plus several conversations in the office and with our hosting provider.
Start-DbaMigration? I love that function but it’s single-threaded and we just don’t have time to backup & restore that many databases that way.
- Backup & restore? Multi-threaded, our daily full backups take over 3 hours to run. Double that to do a backup & restore.
- Detach & reattach? We’ll need either double the storage and eat the time copying the data, or risk the time required to restore from backup if we have to revert.
- Log shipping, mirroring, replication? Again, double the storage, and we have so many databases that it’s just not feasible.
- In-place upgrade? Not supported by our hosting provider, and there’s not much of a safety net.
Ultimately the team settled on a variation of the detach & reattach.
- Install SQL Server 2016 on new physical servers with “dummy” drives (so that paths could be set in the installation process)
- Shut down SQL Server 2008R2
- Take a SAN snapshot
- Move the LUN to the new server
- Attach the databases
This is the fastest way for us to move the data, and the snapshot provides a way back if we have to revert. We have backups as well, but those are the reserve parachute - the snapshot is the primary.
This process is easiest if the paths for the data and log files remain the same. But that didn’t happen here. On the old instance, both data and logs were dumped in the same directory. The new instance has separate data and log directories. And it’s a new drive letter to boot.
OK, so how do you attach eight thousand databases and relocate their files in a reasonable amount of time?
It’s dbatools to the rescue, with
Mount-DbaDatabase being the star of the show. Not only does it attach databases for you, you can use it to relocate and rename the files as well. But that’s really one of the last steps. We have setup to do first.
Once the servers were turned over to us, the DBA basics had to get set up. I configured a few trace flags for the instance with
Set-DbaStartupParameter -Traceflags 3226,4199,7412,460.
String or Binary Data would be truncated error message in a future Cumulative Update
|Suppress successful backup messages in the error log
|Enable query optimizer fixes in CUs and hotfixes
|Enable lightweight execution statistics profiling
Update 2019-01-16: I’ve received a couple questions about the use of TF4199. With the
QUERY_OPTIMIZER_HOTFIXES this isn’t absolutely necessary to get post-RTM hotfixes for the optimizer. Pedro Lopes (blog | twitter) recommended in his Summit 2018 to enable it globally, and he confirmed that in an email exchange with Andy Galbraith (blog|twitter) that Andy wrote up on his blog_. Pam Lahoud (twitter) wrote about the topic in a recent post as well.
I did use
Start-DbaMigration but excluded
Databases, Logins, AgentServer, ExtendedEvents (the last because we don’t use XE on the old instance anyway; this avoided any warnings or errors related to it). Excluding databases makes sense given what I wrote above, but why logins and Agent jobs? The source instance is several years old and has built up a lot of cruft; this migration was a good chance to clear that out. All disabled logins and jobs were scripted out and saved, and only the active items migrated. But that also meant I couldn’t use
Copy-DbaAgentServer because it doesn’t filter jobs out; a few extra steps were necessary.
For reasons I don’t understand,
Start-DbaMigration copied our database mail and Linked Server setups faithfully, with one exception - the passwords. We were able to fix that up easily enough but I found it strange that of all things, the passwords weren’t copied properly. Especially since I’ve done this successfully with dbatools in the past.
Although I only wanted to migrate the currently-active logins, I wanted the ability to re-create any disabled logins just in case, so I needed to extract the create scripts for them. I achieved this via
Moving Agent jobs
I had the same need for Agent jobs, and achieved it similarly. However, because I excluded the
Start-DbaMigration, I had to peek into that function to find out all the other things it copies before copying the jobs. I also wanted to leave the jobs disabled on the new server so they didn’t run before we were ready to test & monitor them in a more controlled way.
Maintenance & Monitoring
When that was complete, we updated the community tools that are installed in system databases
- Brent Ozar’s First Responder Kit -
-database master -force
- Adam Machanic’s
- Ola Hallengren’s Maintenance Solution -
By default, this function doesn’t install Agent jobs, which is fine here. I already copied the Agent jobs over, but I wanted the latest & greatest versions of the backup and index maintenance stored procedures that they call.
We use MinionWare’s Minion CheckDB but didn’t need do a separate installation or migration. With the exception of the Agent jobs, everything is self-contained in a single database. The Agent jobs were copied above, and the database came over with all the others.
Ready to Go
With the above complete, there wasn’t much left to do aside from doing some small-scale testing of the database attachment process and validating system operations (database mail, backups, CheckDB, etc.).
My next post will cover migration day.