HighAvailability Groups service restart

• Joshua D Gang

  Ten Centuries

  Points: 1126

  More actions

  June 9, 2015 at 10:56 am

  #304872

  I inherited a SQL 2012 Ent server sitting on a 2008R2 server using AlwaysOn High Availability, two nodes.

  Available Mode: Synchronous commit

  Failover Mode: Manual

  Connection in Primary role: Allow all connections

  Readable secondary: No

  seesion timeout: 10

  Somebody decided to give SQL server priority boost so I need to change this ASAP. So I plan on doing the following.

  1. Manually fail over to the secondary, which does not have the priority boost set to true

  2. change the setting

  3. restart the service

  4. Manually fail over

  My question is with the service restart. How does SQL handle if the DB changes on the new primary while the secondary is having the service restarted. Where can I see if the DB are sync again or if not where are they in the sync process.

  Thanks

• Joie Andrew

  One Orange Chip

  Points: 27360

  More actions

  June 9, 2015 at 3:53 pm

  #1804161

  Availability Groups are independent instances. The primary will see the secondary go offlline and show errors until synchronization starts up again, but other than that it should be fine. You can see the status of the AG and the synchronization of each db in the AG from the AlwaysOn dashboard. In SSMS expand the instance > "AlwaysOn High Availability" > "Availability Groups", right-click and select "Show dashboard"

  Joie Andrew
  "Since 1982"

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  June 10, 2015 at 5:23 am

  #1804271

  Joshua D Gang (6/9/2015)

  ---

  I plan on doing the following.

  1. Manually fail over to the secondary, which does not have the priority boost set to true

  2. change the setting

  3. restart the service

  4. Manually fail over

  Looks reasonable

  Joshua D Gang (6/9/2015)

  ---

  My question is with the service restart. How does SQL handle if the DB changes on the new primary while the secondary is having the service restarted.

  The secondary will become the new primary and will handle read\write connections. If the new secondary goes offline then transactions will build up in the transaction log until the secondary comes back online at which point synchronisation will resume.

  Joshua D Gang (6/9/2015)

  ---

  Where can I see if the DB are sync again or if not where are they in the sync process.

  Thanks

  Either from the availability group dashboard or by querying the AlwaysOn availability group DMVs

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

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