Always on AG and SQL Upgrade

  • Admingod

    SSCertifiable

    Points: 5620

    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: 80380

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

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

  • as1981

    SSCrazy

    Points: 2380

  • i1888

    SSCommitted

    Points: 1806

    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: 715979

    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

    SSCertifiable

    Points: 5620

    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

    Say Hey Kid

    Points: 669

    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

    SSCertifiable

    Points: 5620

    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?

  • Admingod

    SSCertifiable

    Points: 5620

    Never mind. I think i found the problem.Made some progress but stuck into other issue.

    However, this time i am getting different error as below.

    Adding special permissions to the computer object failed. Trying to add 'Full-Access' permissions for security principal to computer object . Verify that the user running create cluster has permissions to update the computer object om Active Directory Domain services. Access denied.

    Seems like my account needs permission to create account based on the error. What level of permission would i need to create the cluster in AD or do you think the error is misleading?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715979

    I believe you need to be able to add objects in AD that themselves will have local permissions. I've typically installed the cluster as a part of domain admins. You might need those permissions, or need to get a domain admin to run part of this for you.

    The computer object needs to be created, and then you need to be able to add permissions to this object, some of them perhaps the equivalent of WITH GRANT.

  • as1981

    SSCrazy

    Points: 2380

    Hello,

    If domain permissions are the issue then this might help https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-2017

    It's listed as 2017 but, as far as I know, it's the same for 2016. Apologises if you've already seen that link.

     

  • Admingod

    SSCertifiable

    Points: 5620

    However, my sysadmin thinks that i have permission, then why do i get this permission error, can you think of anything else?

  • as1981

    SSCrazy

    Points: 2380

    I can't remember the details but I had something similar. The situation I had was as follows:

    I tried to create the cluster.

    The domain admin found some missing permissions and gave me the permissions

    I couldn't create the cluster because, in the first attempt, some domain objects had been created so I got failure messages on subsequent attempts. They had to remove some of the objects using the domain admin tools.

    Unfortunately I don't have access to a test or live domain system at the moment so can't be more specific. I do remember that the error messages weren't particularly accurate.

  • Admingod

    SSCertifiable

    Points: 5620

    That helps. Thanks! I can able to create cluster after granting the permission to AD. Now i have problem adding the node to this cluster since the IP range is different i don't get the option to put the ip address while adding the node. Any ideas?

  • as1981

    SSCrazy

    Points: 2380

    If I understand correctly then the two servers that you need in the cluster are on different IP ranges? If so then I'm not sure about that as the servers need to communicate with each other. I think it's possible to have multiple IP's, one for communication between the servers, and one for the cluster functionality but I've never set that up.

     

Viewing 15 posts - 1 through 15 (of 18 total)

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