AlwaysOn- PreferredBackup

Which one is a preferred backup replica?

If sys.fn_hadr_backup_is_preferred_replica( @dbname ) <> 1
BEGIN
-- If this is not the preferred replica, exit (probably without error).
END
-- If this is the preferred replica, continue to do the backup.

AlwaysOn Monitoring

Secondary Replica Health

0-NOT SYNCHRONIZING,1-partially healthy,2-healthy

select  synchronization_health secondaryreplicahealth
from  sys.dm_hadr_availability_replica_states
where  role_desc = 'secondary'

Primary Replica Health

--preferred secondary backup

declare @DBName as varchar(150)
declare @preferredReplica as int
SET  @DBName = (select top 1 name from sys.databases where database_id > 4)
SET  @PreferredReplica =(Select master.sys.fn_hadr_backup_is_preferred_replica(@DBName))

 --print @preferredReplica

IF
(@preferredReplica = 0 )
BEGIN
select
synchronization_health Primaryreplicahealth from sys.dm_hadr_availability_replica_states
where role_desc = 'primary'
END

 IF (@preferredReplica = 1 )
BEGIN
select
 99
END

Replica Custernode status
0 = Not joined
1 = Joined, standalone instance
2 = Joined, failover cluster instance

select
distinct  min(join_State) from
sys.dm_hadr_availability_replica_cluster_states
where  join_state_desc ='JOINED_STANDALONE'

--Listener Status

--IP resource ONLINE/OFFLINE state from the WSFC cluster, one of:

-- 0= Offline. IP resource is offline.

-- 1 = Failed. IP resource was being brought online but failed.

-- 2 = Online Pending. IP resource is offline but is being brought online.

-- 3 = Online. IP resource is online.

　

select  'state' = case 
when  state_desc = 'OFFLINE' then 0
when  state_desc = 'FAILED' then 1
when  state_desc = 'ONLINE PENDING' then 2
when  state_desc = 'ONLINE' then 3
end  from
sys.availability_group_listener_ip_addresses

 

Data Loss Time (RPO)

 

AlwaysOn CheckList

Windows Server 2012 and SQL Server 2012 AlwaysOn availability group

SQL Server 2012 AlwaysOn availability group is HOT!!!!
-Use Windows Server 2012
-DO NOT Use Windows Server 2008 R2 ( BAD BAD BAD Idea!)

 Before we start on availability groups, we need to prepare for the following checklist.

Ø  Windows Server Operating system version & edition

Ø  Processor

Ø  Memory

Ø  Data/Log/Temp/Backup drives Configuration for SQL Server

Ø  Central backup share for both primary and secondary replicas

Ø  Windows recommended hot fixes  For Windows 2012: http://support.microsoft.com/kb/2784261

Ø  Public static ip addresses for private network in primary and secondary replica

Ø  Private ip address for public network in primary and secondary replica

Ø  Virtual cluster name and virtual cluster ip address

Ø  Number of nodes on a cluster

Ø  Quorum model and CIF share configuration

Ø  SQL server Enterprise Edition

Ø  SQL Server Cu updates

Ø  Availability groups Names

Ø  Reserved availability group Listener names and listener static IP Addresses

Ø  Number of availability groups on primary replica

Ø  Number of databases on each availability group

Please read the following links before we start AlwaysOn.
Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQLServer)
http://msdn.microsoft.com/en-us/library/ff878487.aspx#RestrictionsAG

Note : AlwaysOn availability groups do not work with distributed transactions. (MSDTC)

Inside AlwaysOn

Monitor Performance for AlwaysOn Availability Groups

I am referring to http://technet.microsoft.com/en-us/library/dn135338.aspx
Availability groups in SQL server 2012 is a hot topic. Recently, I have set up and configured availability groups at my work. I have SQL server 2012 enterprise version on Windows 2012 Data Center Edition.

We will need to know how primary and secondary servers are communicating to each other in order to monitor performance of availability groups.


 Data Synchronization Process







1 . Log Flush

Log data is flushed to disk. This log must be replicated to the secondary replicas. The log records enter the send queue.


select * from sys.dm_os_performance_counters
where counter_name like '%log bytes Flushed/sec%' 

2. Log Capture

Logs for each database is captured and sent to the corresponding partner queue (one per database-replica pair). This capture process runs continuously as long as the availability replica is connected and data movement is not suspended for any reason, and the database-replica pair is shown to be either Synchronizing or Synchronized. If the capture process is not able to scan and enqueue the messages fast enough, the log send queue builds up.

select * from sys.dm_os_performance_counters where counter_name like '%bytes sent to Replica/sec%' 

3. Send

The messages in each database-replica queue is dequeued and sent across the wire to the respective secondary replica.

select  * from sys.dm_os_performance_counters where
counter_name like '%bytes sent to transport/sec%'


4. Received and Cached

Each secondary replica receives and caches the message.
select  * from sys.dm_os_performance_counters where
counter_name like '%log bytes received/sec%' 

5. Harden
Log is flushed on the secondary replica for hardening. After the log flush, an acknowledgement is sent back to the primary replica. Once the log is hardened, data loss is avoided. 


select  *  from sys.dm_os_wait_stats
where  wait_type = 'HADR_LOGCAPTURE_SYNC'
select  * from sys.dm_os_performance_counters
where  counter_name like '%log bytes Flushed/sec%'


6. Redo

Redo the flushed pages on the secondary replica. Pages are kept in the redo queue as they wait to be redone.


select  * from sys.dm_os_performance_counters
where  counter_name like '%redone bytes/sec%'

select  * from sys.dm_os_wait_stats  where  wait_type = 'REDO_THREAD_SYNC'


Flow Control Gates

AlwaysOn Availability Groups is designed with flow control gates on the primary replica to avoid excessive resource consumption, such as network and memory resources, on all availability replicas. These flow control gates do not affect the synchronization health state of the availability replicas, but they can affect the overall performance of your availability databases, including RPO.

how many times flow control was activated and how much time was spent waiting on flow control. Higher wait time on the flow control translate to higher RPO.


select  * from sys.dm_os_performance_counters  where  counter_name like '%Flow control%'