Post reply

AlwaysOn - adding secondary problem

  • June 12, 2017 at 8:39 pm

    #326673

    Hello SSC,

    I am having trouble with adding a secondary replica to my availability group.  Once I choose the server/instance to connect to, the "Next" button on the Add Replica wizard is grayed out.  The server I am trying to add is part of the same Windows Cluster, has AlwaysOn Availability Groups enabled, has the same instance name, and has the same service account as the Primary replica.  Pictures are worth a thousands words, so I've attached 4:
    step 1 - went to add replica
    step 2 - clicked the add replica button
    step 3 - connected to the soon-to-be secondary replica server
    step 4 - the "next" button is grayed out

    step 1:

    step 2:

    step 3:

    step 4:

    Strangely, however, If I were to create a new Availability Group altogether it allows me to add the second server as a secondary replica with no problems, it's just doing it after the AG is created that it fails.
    Any thoughts or suggestions on why it won't let me add this secondary replica?

    Thanks!
    Dan

  • June 13, 2017 at 2:18 am

    #1946485

    Check whether its already exists as replica.

  • June 13, 2017 at 2:34 am

    #1946488

    The replica is on a server that's in the same cluster? They're both SQL 2014 Enterprise Edition?

  • June 13, 2017 at 3:10 am

    #1946492

    Hi 

    Please tick the box under "Automatic Failover" then see if the NEXT command button will be enabled. Furthermore, you will also have to decide if you want the secondary replica to be synchronous commit mode or Aysnchronous commit mode. If you choose the synchronous commit mode, then tick the box under "Synchronous Commit" as well.

  • June 13, 2017 at 4:36 am

    #1946498

    are the instances on the same physical node in the cluster

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • June 13, 2017 at 5:13 am

    #1946510

    Ricky Valencia - Tuesday, June 13, 2017 3:10 AM

    Hi 

    Please tick the box under "Automatic Failover" then see if the NEXT command button will be enabled. Furthermore, you will also have to decide if you want the secondary replica to be synchronous commit mode or Aysnchronous commit mode. If you choose the synchronous commit mode, then tick the box under "Synchronous Commit" as well.

    It's not necessary to configure the AG for automatic failover.

  • June 13, 2017 at 7:55 am

    #1946550

    VastSQL- It does not already exist on this instance, however there is a different instance that it does already exist on, and works on.
    Beatric Kiddo- It is in the same cluster.  They both are using SQL Server Enterprise Edition with the same patch level.
    Ricky Valencia- Ticking the boxes did not make a difference.
    Perry Whittle- Under Windows Failover Cluster Manager it shows that both physical servers are nodes within the Windows cluster itself.

    Thanks for all your replies!

  • June 13, 2017 at 8:11 am

    #1946553

    Weird. Try scripting it out and see what error message you get instead of using the GUI.

  • June 13, 2017 at 8:48 am

    #1946562

    ifilter - Tuesday, June 13, 2017 7:55 AM

    Perry Whittle- Under Windows Failover Cluster Manager it shows that both physical servers are nodes within the Windows cluster itself.

    That is not what i asked.
    Are both the instances you are attempting to add installed on the same physical node?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • June 13, 2017 at 9:37 am

    #1946578

    Beatrix Kiddo- The following worked with no errors:
      ALTER AVAILABILITY GROUP AvailGroup1 ADD REPLICA ON 'Node2\instance1'
      WITH (
      ENDPOINT_URL = 'TCP://Node2.xyz.com:5022',
      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
      FAILOVER_MODE = MANUAL
      );

    Perry Whittle- Oops, misunderstood.  The instances are installed on separate nodes, and both instances are named the same.  Node1\instance1 and Node2\instance1

    Since creating the secondary replica worked with t-sql instead of GUI then I think this should work.  I'm still not sure why the GUI wouldn't allow it, possibly there might be some underlying problem with it.  The reason I say this is because I am now not able to add the database to the availability group.  Though, this would be for another post.

    Thanks all for your help!!

  • June 14, 2017 at 8:04 am

    #1946765

    I found the problem that was restricting me from adding a secondary replica.  First to mention, this was a second instance on this node, the first instance was already in an availability group with the other node and working as expected.  The reason this was preventing me from advancing through the wizard was because of the port used by the endpoint URL.  Each endpoint must have a unique port per instance and by default the wizard applies port 5022 to the Endpoint URL.  I am not aware of a way to specify a different port in the wizard, however I found a couple of workarounds that aren't the cleanest of methods.

    Method 1:

    1. Use t-sql to create the Hadr_endpoint with desired port (i.e. 7022)
    2. Grant connect permissions to the SQL Engine service account of the other node
    3. Use t-sql to create the Availability Group (which requires a database to be used)
    4. Remove the secondary replica from the Availability Replicas list (it doesn't work, at least I wasn't able to get it to)
    5. Via the wizard add the secondary replica back in (the Next button is no longer grayed out and works as originally expected)

    If a listener will be needed to do the install of the software, assuming the correct databases weren't yet present, then a dummy database needs to be created/used in order to create the Availability Group.  Once everything is set up correctly then remove the dummy database from the Availability Group and the instance.

    Method 2:

    1. User t-sql to create the Hadr_endpoint with desired port (i.e. 7022)
    2. Grant connect permissions to the SQL Engine service account of the other node
    3. Via wizard create the Availability Group.  The wizard will still force port 5022 on the primary replica but for the secondary replica added it will put port 7022
    4. Change primary to second node
    5. Remove former primary replica node (the replica that has port 5022)
    6. Add the previously removed node back to the Availability Group.  It will add as the correct port 7022

    Both methods are sloppy at best, but these are the only 2 ways I can figure out how to do this.  Does anyone know of a cleaner way?

    Microsoft's article on endpoints:
    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/specify-endpoint-url-adding-or-modifying-availability-replica

  • June 14, 2017 at 8:24 am

    #1946778

    ifilter - Wednesday, June 14, 2017 8:04 AM

    I found the problem that was restricting me from adding a secondary replica.  First to mention, this was a second instance on this node, the first instance was already in an availability group with the other node and working as expected.  The reason this was preventing me from advancing through the wizard was because of the port used by the endpoint URL.  Each endpoint must have a unique port per instance and by default the wizard applies port 5022 to the Endpoint URL.  I am not aware of a way to specify a different port in the wizard, however I found a couple of workarounds that aren't the cleanest of methods.

    Method 1:

    1. Use t-sql to create the Hadr_endpoint with desired port (i.e. 7022)
    2. Grant connect permissions to the SQL Engine service account of the other node
    3. Use t-sql to create the Availability Group (which requires a database to be used)
    4. Remove the secondary replica from the Availability Replicas list (it doesn't work, at least I wasn't able to get it to)
    5. Via the wizard add the secondary replica back in (the Next button is no longer grayed out and works as originally expected)

    If a listener will be needed to do the install of the software, assuming the correct databases weren't yet present, then a dummy database needs to be created/used in order to create the Availability Group.  Once everything is set up correctly then remove the dummy database from the Availability Group and the instance.

    Method 2:

    1. User t-sql to create the Hadr_endpoint with desired port (i.e. 7022)
    2. Grant connect permissions to the SQL Engine service account of the other node
    3. Via wizard create the Availability Group.  The wizard will still force port 5022 on the primary replica but for the secondary replica added it will put port 7022
    4. Change primary to second node
    5. Remove former primary replica node (the replica that has port 5022)
    6. Add the previously removed node back to the Availability Group.  It will add as the correct port 7022

    Both methods are sloppy at best, but these are the only 2 ways I can figure out how to do this.  Does anyone know of a cleaner way?

    Microsoft's article on endpoints:
    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/specify-endpoint-url-adding-or-modifying-availability-replica

    In the wizard you manually type the required port into the TCP port field if i remember correctly

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • June 14, 2017 at 8:46 am

    #1946785

    When adding the AG the Primary's "Endpoint URL" textbox is locked.  Any secondary I add the textbox is editable.

Viewing 13 posts - 1 through 12 (of 12 total)

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