1

We currently have a number of SQL 2012 server pairs set up with SQL Availability groups where we are just syncing data on the secondary for a RO copy, no listener is currently set up for HA.

We now have an opportunity to add a listener so we can utilize the secondary for read-only and HA fail-over. With HA fail-over, we started thinking of down the road scenarios like upgrading to SQL 2016.

Can we add a SQL 2016 server as a secondary node to our existing AG and when we're ready to migrate, fail-over the SQL 2012 primary to the SQL 2016 secondary and then make it the primary node going forward?

We don't like doing upgrades in place so migrating data to new servers with minimal downtime can be risky, especially if outage windows are small. Just wondering if this is even an option to experiment with. In theory, it could definitely minimize the data and server migration windows to a few minutes, if you can mix SQL versions in an AG. I haven't come across anything that states you cannot do this but haven't had the time to try.

3

Yes, you can absolutely do this. I've done this with several 2012 to 2016 migrations and not encountered any issues along the way.

Just bear in mind, you will not be able to fail back to the 2012 instance once you fail to that 2016 replica, and until you have more 2016 replicas you will not have any HA in place.

  • 1
    Additionally, the higher level replicas cannot be read and one the failover to a higher level occurs the lower level ones will stop synchronizing. – Sean says Remove Sara Chipps Mar 3 '17 at 0:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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