sql server migration enhancements

A while back, I added some new features to our migration commands but I forgot to blog about them. Then, I used one of the new features for a fast and successful migration, got so pumped and had to share.

Multiple destinations

Now, you can migrate from one server to many. This applies to both Start-DbaMigration and all of the Copy-Dba* commands, including Copy-DbaDatabase and Copy-DbaLogin.

As you may be able to see in the title bar of this Out-GridView, I am migrating from workstation, which is a SQL Server 2008 instance, to localhost\sql2016 and localhost\sql2017. My entire command is as follows:

If you examine the results, you’ll see it migrated sp_configure, then moved on to credentials – first for localhost\sql2016 then localhost\sql2017. And continued on from there, migrating central management server, database mail, server triggers and databases.

It migrates a bunch of other things too, of course. Haven’t seen or performed a migration before? Check out this 50-second video of a migration.

-UseLastBackups

This one is awesome. Now, you can use your existing backups to perform a migration! Such a huge time saver. Imagine the following scenario:

Dear DBA,
Your mission, should you choose to accept it, is to migrate the SQL Server instance for a small SharePoint farm.
You’ll work with the SharePoint team and will have four hours to accomplish your portion of the migration.
All combined, the databases are about 250 GB in size. Good luck, colleague!

A four hour outage window seems reasonable to me! Here is one way I could accomplish this task:

  • Update the SQL Client Alias for the SharePoint Servers
  • Shut down the SharePoint servers
  • Execute my scheduled log backup job one final time
  • Perform the migration using -UseLastBackups switch
  • Boot up the SharePoint Servers
  • Check that the sites work
  • Party 🎈

I’ve actually done this a number of times, and even made a video about it a couple years ago.

The outdated output is drastically different from today’s output but the overall approach still applies. Note that back then, -UseLastBackups did not yet exist so in the video, it performs a backup and restore, not just a restore. I should make a new video.. one day!

So basically, this is the code I’d execute to migrate from spcluster to newcluster:

This would migrate all of my SQL Logins, with their passwords & SIDs, etc, jobs, linked servers, all that, then it’d build my last full, diff and log backups chain, and perform the restore to newcluster! Down. To. My. Last. Log. So awesome πŸ˜πŸ‘

Thanks so much for that functionality Stuart, Oleg and Simone!

What about VLDBs?

For very large database migrations, we currently offer log shipping. Sander Stad made some cool enhancements to Invoke-DbaLogShipping and Invoke-DbaLogShippingRecovery recently, too.

Other migration options such as Classic Mirroring and Availability Groups are still on the agenda.

Happy migrating!

- Chrissy

6 thoughts on “sql server migration enhancements

  1. julius Reply

    This is great! just wondering if the destination has a sqlserver authentication?
    thank you for this.

  2. Tomy Reply

    Hi! We plan on using this to migrate from 2008 to 2016 but We currently having some errors. Were just staring to learn this great dbatools and Were stuck on “WinRM cannot process the request with errorcode 0x8009030e occurred while using Kerberos authentication.

    Big Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.