0

I am looking at migrating sql server with 25 databases of total size 7TB to a new machine (better hardware).

All settings (logins/dbmail/proxy/credential/lonked servers), and backup/agent job history needs to be retained.

Due to the above, scripting is not an option as it doesn't preserve history.

Maintenance window is of 3 hours.

I have tried setting up new sql server with same version/Patching, followed by restoring the system dbs and user dbs. But this process (including backup/copy/restore takes 7 hours). Without backups (mdf/ldf copy) takes 15 hours.

Alternate approach using full/diff backups:

  1. Restore user db with norecovery onto new instance
  2. During maintenance window backup/restore Differential backup with recovery
  3. Stop original instance
  4. Stop new instance and reorganize db files as per the source instance data/log paths
  5. Restore master, msdb, model

This approach takes about 1 hour since differentials are small sized. I'm unable to find any msdn documentation for moving sql to new hardware. Is there any risk with this approach? Or any alternate solution?

For example- at step 1 I can restore master db rather than at step 5.

4
  • Is your SQL Server running in a VM? Sep 5, 2022 at 16:28
  • Yes it is running in a VM
    – variable
    Sep 5, 2022 at 16:32
  • Then why can't you just migrate the VM to the better hardware? Sep 5, 2022 at 16:39
  • The OS needs fresh install so that isn't an option
    – variable
    Sep 5, 2022 at 17:10

1 Answer 1

2

I would suggest to use dbatools.io cmsdlet Start-DbaMigration (https://docs.dbatools.io/Start-DbaMigration) to migrate sever objects like logins, linked servers, etc. It will create a copy of your prod instance without user databases. To migrate databases I would configure db mirroring (yes, it's obsolete but requires no special reconfigurations and works fine) and at the maintenance window would just do the switch of primary and secondary roles. I did the same migrating 800 dbs (40TB+ in size) between continents (Europe-USA) recently. The switch took about 2hrs due to the high amount of online mirrors and weak network channel. To preserve backup/restore history I see two ways:

  1. extract/load content of appropriate tables (msdb)
  2. restore msdb from the source server (and get Sql agent jobs as a bonus as well as others - dbmail, operators, etc).
3
  • Why not restore master db to get the logins/linked servers/etc?
    – variable
    Sep 6, 2022 at 12:41
  • Well, it's the fastest way (replace existing system files on the new server with the ones from original server). However, it requires some efforts and knowledge. Usage of DBATools is easy ("fire and forget") and let's you not to worry about technical details. On the moment of the switch there could be new logins, jobs, etc. So, seamless migration from one server to another needs a solution which copies system information and keep user databases in sync with the new server as much as possible. Sep 6, 2022 at 13:01
  • Can't find any documentation from Microsoft on migration.
    – variable
    Sep 6, 2022 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.