1

we are going to upgrade from SQL server 2012 Standard edition to SQL Server 2016 Enterprise edition. we can't have a downtime more than 30 mins .The new SQL server will use always on availably group 1 primary and 2 read-only secondaries . I am looking for the fastest way to do this upgrade.
I am thinking of creating mirroring between the old server and new primary server , then shut down the application update the connection string , fail over the DB to the new server, stop mirroring, start the application. After the application is up, start in configuring the Availability group.

IS this a correct way to use

Improve this question
3
  • How big is the database and how many dbs are involved in migration ?
    – Kin Shah
    Commented Oct 25, 2016 at 21:33
  • 1
    3 dbs , size = 200 GB
    – sebeid
    Commented Oct 25, 2016 at 22:52
  • What's your HA/DR plan once you break the mirror and before 200GB has been replicated?
    – Greg
    Commented Oct 26, 2016 at 3:25

1 Answer 1

Reset to default
2

What you've got sounds like it should work and would get you online quickly, however you will have no HA plan if something fails before you've got the 200GB database replicated to at least one of your secondaries.

Also what will you do if your upgrade fails? I don't think there will be a way back to the old server without going back and restoring backups.

When we've done similar migrations (2012 to 2014), we've done the following:

  1. Create the Availability Group
  2. Take a full backup of current production
  3. Restore on all servers in the AAG (leaving in restore mode)
  4. Take a differential backup of current production shortly before your 30 minute window and restore to all servers on the AAG (leaving in restore mode)
  5. Take regular log backups of the current production server and restore (you could automate this with log shipping)
  6. When your 30 minute window starts, restore the last of the log backups, bring the new primary online, make sure everything is attached to the AAG and you're ready to go
Improve this answer
2
  • can we create the AAG while the DB is restore mode
    – sebeid
    Commented Oct 28, 2016 at 21:16
  • Can't remember, but I don't think so. If you do the restore to all databases to the same point (full/diff/logs) then the AAG should be created in seconds
    – Greg
    Commented Oct 29, 2016 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.