Node manage activity on AG setup

Category: sql server manageability

Question

SQLGalaxy on Mon, 25 Jul 2016 05:08:56


Hi..

Configured alwayson group one of the OLTP database as following setting.

> there are total two node added in WFCS and FSW file share witness. all the nodes are windows 2012 R2 and SQL 2012.

what are the pre-requisite's should we take care if these node are getting restarted manually due to windows patch updating. also

what are check list need to be check post restart all the nodes?

Pl. advice

Thanks

Replies

Enric Vives on Mon, 25 Jul 2016 09:07:35


Hi SQLGalaxy,

Take a look on this link:

https://www.brentozar.com/sql/sql-server-alwayson-availability-groups/

Teige Gao on Wed, 27 Jul 2016 05:52:48


Hi SQLGalaxy,

According to your description, you can upgrade your server by using the following steps:

  1. Before starting upgrade, we need to protect the data by using the following steps:
    1. Protect your data by performing a full database backup on every availability database
    2. Run DBCC CHECKDB on every availability database
  2. Remove automatic failover on the primary replica and secondary replica by using the following steps:
    1. In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.
    2. Expand the Always On High Availability node and the Availability Groups node.
    3. Click the availability group whose replica you want to change.
    4. Right-click the replica, and click Properties.
    5. In the Availability Replica Properties dialog box, use the Failover mode drop list to change the failover mode of this replica.
  3. Upgrade the secondary replica instance then make it online.
  4. Manually fail over the availability group to the secondary replica by using the following steps:
    1. In Object Explorer, connect to a server instance that hosts a secondary replica of the availability group that needs to be failed over, and expand the server tree.
    2. Expand the AlwaysOn High Availability node and the Availability Groups node.
    3. Right-click the availability group to be failed over, and select the Failover command.
  5. Upgrade the server instance that formerly hosted the primary replica then make it online.
  6. Configure automatic failover partners as desired.

If it is asynchronous-commit secondary replica, you can review this article: https://msdn.microsoft.com/en-us/library/dn178483(v=sql.110).aspx

Regards,

Teige


SQLGalaxy on Thu, 28 Jul 2016 07:27:25


Hi Teige.

Thank you for reply and mentioned AG setup maintenance steps on both Primary & secondary Replica

As per AG setup now, there are two node in local data center and running with automatic failover mode. later on will add the third node at remote data center for DR requirement.

1. I have doubt during upgrade patch installation in primary replica, will it stop Log queue send to secondary replica while change to manual failover from automatic failover mode in existing setup?

2. DBCC CHECKDB command, can we execute this command in secondary replica node for avoiding the server resource using during executed DBCC in Primary?

3. how can we manage SQL jobs when change became primary node from secondary node? first create all SQL jobs in secondary node and make disable it then enable once secondary change to primary role.

Thanks

Enric Vives on Thu, 28 Jul 2016 07:47:40


About the third question: I have the same problem.

At the beginning of my job I'll check that:

(I am determining Availability Group Name because we have got two Availability Groups in Always ON  AG.name = 'AlwaysOnERP')

declare @primaryreplica as varchar(7);


set @primaryreplica = 
(select InA.role_desc  from sys.dm_hadr_availability_replica_states INA  /*AG.name, RE.replica_server_name, */
inner join sys.availability_groups_cluster AG
on inA.group_id = AG.group_id
inner join [sys].[dm_hadr_availability_replica_cluster_states] RE
on re.replica_id = INA.replica_id
where Ag.name = 'AlwaysOnERP' and role_desc = 'PRIMARY')

IF (@primaryreplica = 'PRIMARY')

--do stuff are you in primary
ELSE

--do stuff are you in read-only node
 RAISERROR('We are in secondary replica.', 11, 1);