How to migrate databases from SQL 2012 to SQL 2017 failover cluster using Red Gate tools

Koenraad.Dendievel

We are getting ready to move some interconnected databases (20+) from a single SQL server 2012 standard to a SQL server 2017 enterprise failover cluster(availibility group)

I started testing with backup/restore but this is not very practical for 20+ databases and not very repeatable/scriptable
it also demands that you remove database from availibility group , restore it, re add it to availibility group manual each time
I tried the "copy database wizard" but ran into all sort of issues (timeouts/unable to copy windows logins/...) and databases getting deleted if job fails

We want exact copies so using SQL compare + SQL data compare does not look like the right path

Our databases are not(only partly) under SQL source control

Wich (Red Gate) tools can i use to copy/sync the databases to the new location, without bringing source offline, for testing if everything still works in a clustered environment/new server version?

Wich (Red Gate) tools should i use on day X to do the final migration/sync?

Are there tools for copying and syncing server config to each node:

• linked servers
• notify email operators
• database mail profiles
• SQL logins from windows accounts (+ mappings to databases)
• ...

Can you point me to a guide/checklist for this kind of migration

Dan B

Hi Koenraad.Dendievel,

You can script the restore of multiple databases inside SQL Backup, information on this can be found here https://productsupport.red-gate.com/hc/en-us/articles/360007937494.

You would need to write a script to set the AG's yourself.

SQL Compare and Data Compare could work to move the databases over they will generate scripts to create complete copies of the database.

We don't have any other tools to achieve this nor can we assist with your other requirements. I would advise posting on a more general forum for assistance with this such as http://www.sqlservercentral.com/. 

Please do let me know if you have any further questions about backup or compare.

Koenraad.Dendievel

Dan,

Thanks for the feedback. I cannot view the zendesk information on how to script out multi-db restore via SQL BACKUP. Can this article be made public?

I was hoping Red Gate which has great tools also made a migration tool or a least a better version of the copy database wizard that i didnt know of

sincerely

Dan B

Hi Koenraad,

I have updated the post with the external link. Please let me know if that still doesn't work for you.