Migrate SQL 2012 Clustered Instance to SQL 2016 Standalone Instance

Question

I'm about to migrate a SQL 2012 failover clustered instance to SQL 2016 standalone instance. At the end of migration I will have to reuse the SQL virtual network name and IP address. I'm planning to perform the below:

1. Setup a SQL 2016 standalone instance
2. Perform full backup of databases from 2012 FCI
3. Restore full backup to 2016 instance with NORECOVERY
4. Script out SQL logins and import to 2016 SQL instance
5. Script out any Jobs and import to 2016 SQL
6. On cutover date, perform last differential database backup and restore to 2016 SQL with RECOVERY
7. Swap the server hostname and SQL virtual network name between two SQL instance
8. Swap IP address

Is the above steps good to go? Or is there anything that I have missed out?

Appreciate for any advise.

Answer 1

A few comments, and I'm sure there will be more.

Logins and jobs are only two things to consider when you move a database. You have more things. Linked servers, potential sp_configures are a couple of examples. I wrote an article on the subject: https://karaszi.com/moving-a-database-between-two-sql-server-instances

Doing the final cut with a diff backup is one option. Another is to have log backups running periodically (possibly using log shipping) and do the final cutover using a log backup instead of a diff backup. I can't say which is best for you, but something to consider.

Make sure your 2016 instance is patched. Then consider tuning on Query Store. And then run with the old db compat level for a while. And then consider upping the db compat level (if your app supports it) and see where you get better vs worse perf.

Answer 2

May I know if MonitorDB and Report Server DB need to be migrated? After I setup the new 2016 instance and installed the reporting services,

YES you need to if you are using it, not quite sure about monitor DB but it seems like it would keep monitoring data which is also pretty important. Without reportserver database backup your reporting services would not work in terms that you would not see reports. You need to restore the database and reconfigure the reporting services using reporting services GUI.

As far as your migration step goes, I do not see any issue. You do not need to swap IP, hardly anything relies on IP just a swap of hostname would be enough. Rest things are covered by Tibor in his answer.

Answer 3

I can't say whether you should migrate the reporting database. This is used by Reporting Services (RS). I frequently see this being installed, even when it isn't used. I can't say whether your installation uses RS. You need to ask around...

Answer 4

Hi Shashank,

Thanks for your reply. After reviewing Tibor and your replies, I would like to confirm the below as my final migration plan so that I could proceed with the migration by following these:

1. Setup SQL 2016 standalone instance with the same collation with existing 2012 FCI
2. Perform full backup of databases from 2012 FCI and restore to 2016 SQL with NORECOVERY
3. Script out and import SQL logins & passwords to 2016 SQL instance
4. Script out Jobs & Alerts, import to 2016 instance
5. Script out Linked servers and import to 2016 instance
6. Reconfigure reporting services & monitoring
7. Perform last log/differential backup and restore to 2016 SQL instance with RECOVERY
8. Swap hostname between clustered instance & standalone instance server
9. Swap the IP address (a requirement of the project)

Appreciate your expertise to confirm whether the above steps are good to go.