Always On availability groups: HA-DR

Availability group   --> A container for a set of databases, availability databases, that fail over together. 

Availability database -->A database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to eight read-only copies (secondary databases) 

Primary database --> Read-write copy of an availability database. 

Secondary database --> Read-only copy of an availability database.

Availability replica --> Maintains a local copy of each availability database that belongs to the availability group 

Primary replica -->  Availability replica that makes the Primary databases available for read-write connections from clients and,                                           also, sends transaction log records for each primary database to every secondary replica.

Secondary replica --> Availability replica that makes the Secondary copy of each availability database, and serves as a potential                                              failover targets for the availability gropy .Optionally, a secondary replica can support read-only access to                                                secondary databases can support  creating backups on secondary databases. 

Availability group listener --> A server name to which clients can connect in order to access a database in a primary or secondary replica of                                                     an Always On availability group. Availability group listeners direct incoming connections to the primary replica                                                      or to a read-only secondary replica. 

You are administrating a SQL Server Always on Failover Cluster instance and you are planning to configure when a failover or restart is performed for the FCI. Which property should you set to achieve that?

 The FailureConditionLevel property can be used to set the condition to perform a failover or restart for the FCI instance.

 Which you need to resolve an issue with the queries that become very slow after upgrading the SQL Server instance from SQL Server 2012 to SQL Server 2017. Which database level option should you configure to fix that issue?

 The LEGACY_CARDINALITY_ESTIMATION database level option should be disabled, to set the cardinality estimation model of the database back to the SQL Server 2012.

Benefit of Contained Database Users with Always On

Creating contained users enables the user to connect directly to the contained database. This is a very significant feature in high availability and disaster recovery scenarios such as in an Always On solution. If the users are contained users, in case of failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. 

Configuration 

HOW TO TROUBLESHOOT ALWAYS ON SYNCHRONIZATION ISSUE

Always On synchronization issue 

Always On availability group for Synchronization it may be one of below status for the Availability Database 

1.      Database is Restoring

2.      Database is Recovering

3.      Database is in Recovery pending

4.      Database is in Suspect

5.      Database is in Emergency

6.      Database is in Offline

Huge transaction no of transactions, not ample space left on Secondary  

secondary server not much space left for database file to grow and the Always On database synchronization 

weekly maintenance job 

Most Common Issues

Configuration 

SQL Server

Operation

35250 erros

Not Synchronizing

OS

Avg delay(ms/TRN) =TransactionDelay(ms/sec)/MirrorWriterTxns/sec

Monitoring Availability Replicas DMV's Always on


To view availability group, availability replica, and database states 



sys.dm_hadr_cluster_members

current Windows Server Failover Clustering (WSFC) cluster.

 

sys.dm_hadr_availability_replica_cluster_nodess

 It gives us information about the availability replica for AG in WSFC. You get the information about availability replica irrespective of the replica states.

sys.dm_hadr_availability_replica_states

 

sys.dm_hadr_availability_replica_cluster_states

It is a useful DMV to get the details of local replica, remote replica and their synchronization states. Here are the important columns for this DMV.


sys.dm_hadr_cluster_networks

query retrieves the network configuration of the current WSFC cluster.


sys.availability_replicas

 Get information about the existing availability replicas for each SQL Server Always On Availability Groups. For example, in my lab environment, I have two availability groups – [demoag] and [SQLAGDemo]. Each availability group has two replicas therefore, we get four rows in the output. 


sys.dm_hadr_availability_replica_states

It is a useful DMV to get the details of local replica, remote replica and their synchronization states. Here are the important columns for this DMV. 


sys.availability_databases_cluster  

sys.dm_hadr_database_replica_cluster_states

This DMV returns a row containing information intended to provide you with insight into the health of the availability databases in the AlwaysOn availability groups in each AlwaysOn availability group on the Windows Server Failover Clustering (WSFC) cluster. 


sys.dm_hadr_auto_page_repair 

This DMV returns a row for every automatic page-repair attempt on any availability database on an availability replica that is hosted for any availability group by the server instance.

 

sys.dm_hadr_availability_group_states 

This DMV returns a row for each AlwaysOn availability group that possesses an availability replica on the local instance of SQL Server. Each row displays the states that define the health of a given availability group.

 

sys.dm_hadr_availability_replica_cluster_nodes 

This DMV returns a row for every availability replica (regardless of join state) of the AlwaysOn availability groups in the Windows Server Failover Clustering (WSFC) cluster.


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

Windows Failover Clusters can have up to 64 nodes with Windows Server® 2012 R2.