Migrate SQL 2012 Clustered Instance to SQL 2016 Standalone Instance

Marcus Wong Theen Nam 1,111 Reputation points
2021-01-14T17:25:39.217+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,786 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
532 questions
0 comments
Accepted answer
  1. tibor_karaszi@hotmail.com 4,311 Reputation points
    2021-01-14T17:58:39.697+00:00

    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.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shashank Singh 6,251 Reputation points
    2021-01-15T07:25:30.357+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. tibor_karaszi@hotmail.com 4,311 Reputation points
    2021-01-15T07:26:50.883+00:00

    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...

  3. Marcus Wong Theen Nam 1,111 Reputation points
    2021-01-15T07:47:53.847+00:00

    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.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.