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.
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
issue Error Joining Databases (35250)
Listner creation failures
Read-Only routing: Setup & Connections
SQL Server
AG Dashboards
DMVs
SQL Error Log
Resolving
Not Available
ALWAYSON
WindowsServer Failover Clustering
Avaibility group
Quoram
Connection with primary database terminated
Connection with secondarydatabase terminated
Connnection for availability group
lease
Extended Events (*.xel files)
sys.dm_xe_session
system_health_-_1.exl
Operation
Connecting to Listeners (especially Multi-subnet)
Log file growth
Unexplained Failovers
Secondary can't connect/database not syschronizing
35250 erros
Firewall rules
Mirroring Endpoint permission
Endpoint_URL typos
Endpoint port conflicts (other instance)
Host intrusion/ anti-virus system
Not Synchronizing
File operations- pathgemometriesnot identical
Forgot to "resume" afterfailover in ASYNCmode ( withallow data loss)
Missingcertificates for TDE
Extrems stree
Long Failovers/Databases Inaccessible
OS
Cluster Logs
Your AG Name Hadrag connect to SQL Server
Hadrag lease thread terminated Hardrag Stopping HealthWorker Thread
rcm:RcmApi:MoveGroup has exceededits restart limit
NetRemotedUnreachable lease renawal failed
lost quorum shutting down
log begin
Permon counters
SQLServer:Availibaility Replica
SQLServer:Database Replica
Average delay per transaction due to SYNCHROUS Secondary:
Avg delay(ms/TRN) =TransactionDelay(ms/sec)/MirrorWriterTxns/sec
Events Logs
Netmon/NETSH
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.