Always on AG and SQL Upgrade

  • Admingod


    Points: 5541

    Looking to upgrade from SQL Server 2014 to SQL 2016 which has always on AG on SQL 2014. Is there a better approach to do this upgrade with Always on AG. Thinking to upgrade first from SQL 2014 to SQL 2016 and then reconfigure always on AG on SQL 2016. If it's just service pack i would apply on secondary node first then later on primary node. However, for upgrade do you suggest any better solution other than what i mentioned. Please advise?

  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • as1981


    Points: 2270

  • i1888


    Points: 1799

    The best solution is migration, e.g. moving all databases to a SQL Server 2016 AlwaysOn ready environment, but unless you have enough budget, or in case you have new hardware...

    In my environment, I have done something like above, or follow the Microsoft Guide upgrading Async nodes first, I upgraded straight from SQL Server 2014 to SQL Server 2016, then apply SP, then apply CU, then sync nodes, failover, upgrade the pre-primary nodes, after all nodes are upgraded, failover back(if it is required).

    You may prefer to use Command to run upgrade, installing SP, CU, by /SkipRules=RebootRequiredCheck to save some time and reboot once at last.


  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714637

    Migration is what I'd do, as outlined above, but why 2016? If you have the 2016 license, but if you're upgrading with new licenses, why not 2017?

  • Admingod


    Points: 5541

    Thanks that's a good point. May be i would go with SQL 2017. On a side note, I am looking for prerequisite IP address needed for setting up always on Availability group for existing three SQL Server standalone instances.Two are co-located on same location(synch) and third one would be on DR side(Aysnc).

    So my understanding is I need total of 2 IP address

    One IP address for the fail over cluster hosting the Availability group

    one IP for availability group listener.

    Correct me if i am need any more IP addresses?

  • Wecks


    Points: 484

    I believe that is all you require.  As per Steve Jones's Post go 2017 if you can however it all depend on what you will be hosting on an availability group.  If it is a vendor app certain apps do not support Availability groups and some apps do not support the latest and greatest SQL so check before you commit.


    When deciding on AG, Budget is usually a key concern,  Availability groups is a Enterprise level feature and as such you will need enterprise licencing which for alot of organisations is too much of an expense. However depending on your requirements utilising SQL Standard you are able to run BAG (Basic Availability Groups) however you will be restricted to one Database per group, but you can have multiple groups.  If you have an application that has multiple databases and you are able to configure connection strings for each db then you could potentially use BAG to save you some cash where this would not suffice if you have multiple DB's per application and you can not specify individual connection strings.

    Food for thought.



  • Admingod


    Points: 5541

    Thanks to everyone. I was trying to create cluster for AG group and it fails to create cluster the sad part is without any error and it says

    error occurred while creating the the cluster

    This operation returned because the timeout period expired.

    Anyone has seen this problem? Please advise?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply