Sql-server – AlwaysON Migrate from 2014 to 2016

availability-groupsmigrationsql serversql server 2014sql-server-2016

I have like 20 Listners on 2014 with approx 500 DBS,

what is the best way to migrate these with least downtime, effort

My idea is:
Stop access
Backup dbs
Start restore dbs
While restore is running:
Drop AOG on 2014
Create it on 2016
Done

This seems simple, but with TB data this will take some time.

Have found this ->
Recommended ways to upgrade AlwaysOn AG from 2014 to 2016
But it niether does explain

Hope someone can help

Best Answer

I'd recommend configuring log shipping to migrate your databases. Hours/days/weeks before your planned migration, configure log shipping from your current (2014) production Availability Group. You should perform the log shipping restores on all nodes of the new (2016) Availability Group.

Also in advance, pre-configure the Availability Groups on the new servers, but do not create the Listener.

This will minimize downtime during your cutover maintenance by restoring the majority of your data ahead of the maintenance window.

During the cutover, the steps to complete the migration would be:

  • Shut down applications/kill existing sessions.
  • Perform tail log backups for the (2014) databases you are migrating. (BACKUP LOG... WITH NORECOVERY)
  • Allow the log shipping restore jobs to run a final time, or manually restore the final logs to all new (2016) servers WITH NORECOVERY.
  • On one of the new servers, recovery the databases (RESTORE DATABASE... WITH RECOVERY).
  • Add the databases to the Availability Groups, and join secondary servers.
  • Drop the Listeners on the old (2014) AGs.
  • Create the Listeners on the new (2016) AGs.
  • Turn on applications.
Related Question