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:
Start-DbaMigration -Source workstation -Destination localhost\sql2016, localhost\sql2017 -BackupRestore -UseLastBackup | Out-GridView
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.
-UseLastBackup
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 -UseLastBackup 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, -UseLastBackup 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:
# first, modify the alias then shut down the SharePoint servers $spservers = "spweb1", "spweb2", "spapp1", "spapp2" Remove-DbaClientAlias -ComputerName $spservers -Alias spsql1 New-DbaClientAlias -ComputerName $spservers -ServerName newcluster -Alias spsql1 Stop-Computer -ComputerName $spservers # Once each of the servers were shut down, I'd begin my SQL Server migration. Start-DbaMigration -Source spcluster -Destination newcluster -BackupRestore -UseLastBackup | Out-GridView # Mission accomplished 🕵️♀️🕵️♂️
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-DbaDbLogShipping and Invoke-DbaDbLogShipRecovery recently, too.
# Also supports multiple destinations!
# Oh, and has a ton of params, so use a PowerShell splat
$params = @{
Source = 'sql2008'
Destination = 'sql2016', 'sql2017'
Database = 'shipped'
BackupNetworkPath= '\\backups\sql'
PrimaryMonitorServer = 'sql2012'
SecondaryMonitorServer = 'sql2012'
BackupScheduleFrequencyType = 'Daily'
BackupScheduleFrequencyInterval = 1
CompressBackup = $true
CopyScheduleFrequencyType = 'Daily'
CopyScheduleFrequencyInterval = 1
GenerateFullBackup = $true
Force = $true
}
# pass the splat
Invoke-DbaDbLogShipping @params
# And now, failover to secondary
Invoke-DbaDbLogShipRecovery -SqlInstance localhost\sql2017 -Database shipped
Other migration options such as Classic Mirroring and Availability Groups are still on the agenda.
Happy migrating!
- Chrissy
This is beautiful!!!!
So glad you like it, John 🤗
This is great! just wondering if the destination has a sqlserver authentication?
thank you for this.
Yep! Most commands support `-SqlCredential` but this one is slightly different. Use `-DestinationSqlCredential`.
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!
hey tomy,
sorry, kerberos issues are out of scope. that’s a network/config issue unrelated to dbatools.
Planning on using this for the first time in the coming days to migrate a failing 2-node multi-instance WFC from a datacenter in NY to a new 3-node WFC in PA. These are migrating from one old domain to a new one so there will be some challenges I’m sure, but given there is a two-way trust so things should go pretty smoothly. This is definitely a move the crystal event so I’m hoping Start-DbaMigration will be the time saver I have learned about.
Thank you for all you folks do for the SQL community!
Hi Chrissy,
How’s everything going?
I tested Copy-DbaPolicyManagement on my Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) – 14.0.3048.4 (X64) . It worked well with conditions migration, but not the policies.
Any thought for that?
Thanks,
Is there a way to mention data file and log file location while restoring it. When I try to run the command I get error related to permission. (not sure where its trying to store data and log files). Thanks
This is so sweet @ christy , am wondering if i have to check any compatibility on this before migrating ? Am just a Newbie and this would be my first migration in prod in few weeks from now, i wanted to know .
Great progress. I’ve migrated one instance already. I’d like to migrate again but only with a few selected databases. Is there a way to “include” instead of “exclude” databases being migrated?