Sql-server – Migrating a SQL Server 2014 AG to a new 2017 AG

availability-groupssql serversql server 2014sql-server-2017

Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn.

Due to the configuration issues with the present cluster, we can not use the existing cluster.

The production server has 52 databases with a total size of 2 TB. The databases servers an online system, so minimum downtime is our core requirement.

enter image description here

Our initial plan is to go for a side by side approach.

  1. Provision 3 SQL Server 2017

  2. Create new cluster

  3. Logs from the existing 2014 AG Primary server to all the servers in the new 2017 group.

On the switchover day,

  1. Disconnect all applications from the existing server.
  2. Do a final logshipping.
  3. Apply logs to all servers in the new group WITH NORECOVERY, except WITH RECOVERY on Primary_new
  4. Create AG and add all database to AG
  5. Change all applications to point to the new cluster.
  6. Shutdown old cluster.

Is this a good approach? Are there any other approaches?
If I am missing anything, please guide me.

Thanks.

Best Answer

The plan sounds great. You just need to include a deep UAT (User Acceptance Test) as it is possible that the way that the new version interprets the sentences is a bit different. For example, every version is going to be more strict with ending each sentence with ; We made similar migration almost one year ago from 2008 to 2014 and the sentences like:

USE ProductionDB;
INSERT INTO Archive_DB.dbo.Archive_Tbl1 (Field1, Field2)
SELECT Field1, Field2 
FROM ProductionDB.dbo.Tbl1;

We had detected several issues because sometimes the DB was ommited and the new version made a different assumption than the old version. Fortunately we could fix all during the UAT. Good luck !!!

Related Question