SQL Server Always On (AO) Q&A:

Q. How many SQL nodes can be configured in AO CONFIGURATION?

A: Max 5, 1 for primary, 4 for secondary replica. Max 2 can be part of automatic failover, max  2 can part of synchronous replica, only primary can be read write and rest will be read only.

Q. What is the use of listener in AO?

A. By defining listener a virtual network name (VNN) in AO one has no need to use server name to connect current primary replica, using listener name connection can be automatically redirected to primary replica and there would be no changes in application connection string after failover.

Listener consists of a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses. Only the TCP protocol is supported by availability group listener

When you create a new availability group listener it becomes a resource in a cluster with an associated virtual network name (VNN), virtual IP (VIP), and availability group dependency

One can set default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

Q. What is the difference between read-intent and read only replica?

A.  Read-intent only: Only read-only connections are allowed
     Yes: All connections are allowed, but only for read-only access
So the main difference between Read-intent-only and Yes is that the later allows all connections, regardless if it read or read/write, but only read access will be granted.

Q. what are the availability modes in AO?

A.         Asynchronous-commit mode

Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

       Synchronous-commit mode

Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.

 Q. What is License policy for Always on server?

A. SQL Server 2012 has been released with a new license model. With SQL Server 2012 AlwaysOn’s ability to have multiple secondaries you need to take into account the licensing when you are going to be implementing multiple secondaries. The license model requires you to license your Active (Primary) SQL Server in your AlwaysOn Cluster. You are allowed one Passive (Secondary) server that you do not need to license. If you have more than one secondary server, you need to license that server whether it is active or passive.For example: If we were to have one Primary Server (Active), three Secondary Servers (one Active, two Passive) we would be required to license three of the four servers.

Q. what is Session timeout period.

A. The session-timeout period is an availability-replica property that determines how long connection with another availability replica can remain inactive before the connection is closed. The primary and secondary replicas ping each other to signal that they are still active. Receiving a ping from the other replica during the timeout period indicates that the connection is still open and that the server instances are communicating. On receiving a ping, an availability replica resets its session-timeout counter on that connection.

The session-timeout period prevents either replica from waiting indefinitely to receive a ping from the other replica. If no ping is received from the other replica within the session-timeout period, the replica times out. Its connection is closed, and the timed-out replica enters the DISCONNECTED state. Even if a disconnected replica is configured for synchronous-commit mode, transactions will not wait for that replica to reconnect and resynchronize.

The default session-timeout period for each availability replica is 10 seconds. This value is user-configurable, with a minimum of 5 seconds. Generally, we recommend that you keep the time-out period at 10 seconds or greater. Setting the value to less than 10 seconds creates the possibility of a heavily loaded system declaring a false failure.

Q. Number of Databases in a Availability Group (AG)?

A. There is no limit of database it all depend on workload.

Q. What if one database fails out many in one availability group?

A. No, failover depends over instance level. The AG uses Windows Clustering for detection of failure events

Q. Recovery model for AO?

A. Full recovery model.

Q. Is cluster required for AO?    

A.            Deploying AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster. To be enabled for AlwaysOn Availability Groups, an instance of SQL Server must reside on a WSFC node, and the WSFC cluster and node must be online. Each availability replica of a given availability group must reside on a different node of the same WSFC cluster,The WSFC cluster monitors this resource group to evaluate the health of the primary replica.

The quorum for AlwaysOn Availability Groups is based on all nodes in the WSFC cluster regardless of whether a given cluster node hosts any availability replicas. In contrast to database mirroring, there is no witness role in AlwaysOn Availability Groups.

The overall health of a WSFC cluster is determined by the votes of quorum of nodes in the cluster. If the WSFC cluster goes offline because of an unplanned disaster, or due to a persistent hardware or communications failure, manual administrative intervention is required. A Windows Server or WSFC cluster administrator will need to force a quorum and then bring the surviving cluster nodes back online in a non-fault-tolerant configuration

An availability replica can be hosted by either a standalone instance of SQL Server or an FCI instance. Only one FCI partner can host a replica for a given availability group. When an availability replica is running on an FCI, the possible owners list for the availability group will contain only the active FCI node.

AlwaysOn Availability Groups does not depend on any form of shared storage. However, if one use a SQL Server failover cluster instance (FCI) to host one or more availability replicas, each of those FCIs will require shared storage as per standard SQL Server failover cluster instance installation.

Q. Can we set failover to automatic for AO replica?
A. yes for standalone instance on clustered environment, SQL Server Failover Cluster Instances (FCIs) do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can only be configured for manual failover.

Q. How to change the failover mode for AO?
A. ALTER AVAILABILITY GROUP MyAG MODIFY REPLICA ON 'COMPUTER01' WITH  (FAILOVER_MODE = AUTOMATIC);

 It should always be performed on primary replica.

Q. Can AO work for nodes in different geographic location?
A. can have geographically remote nodes participating in one AG, but they must be members of the same AD domain.

Q. what are the different types of failover?
A. Failover event is when your primary replica fails. In that case, Secondary node which can be automatically promoted to Primary, will be promoted automatically. Such nodes are known as Failover Targets. You may also have secondary nodes which may not be automatically promoted to primary. The new primary replica then recovers its databases and makes them available to users of the availability group.

1. Automatic failover (without data loss). Automatic failover is only available when both the primary replica and the secondary replica are running in synchronous-commit mode, and the failover mode is set to automatic. In automatic failover mode, failure of the primary replica causes failover to the secondary replica without the need for administrator intervention. No data loss will occur on failover.
2. Planned manual failover (without data loss). Planned manual failover is only available when both the primary replica and the secondary replica are running in synchronous-commit mode. In planned manual failover mode, a database administrator must issue a failover command to initiate failover. No data loss will occur on failover.
3. Forced manual failover (with possible data loss). Forced manual failover is the only failover type that you can use for replicas that are in asynchronous-commit mode. You must initiate forced manual failover manually. Any transactions that were committed on the primary replica, but which the secondary replica has not yet written to its log, will be lost. You can also use forced manual failover for replicas that are in synchronous-commit mode when the secondary replica is not showing as synchronized with the primary replica

Q. how to perform manual failover?

A. It requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Connect to a server instance that hosts a replica whose role is in the SECONDARY or RESOLVING state in the availability group that that needs to be failed over

Forced manual failover:

Data loss is possible during the forced failover of an availability group. In addition, if the primary replica is running when you initiate a forced failover, clients might still be connected to former primary databases

ALTER AVAILABILITY GROUP AccountsAG FORCE_FAILOVER_ALLOW_DATA_LOSS;

Planned manual failover:

The target secondary replica and the primary replica must both be running in synchronous-commit availability mode.

ALTER AVAILABILITY GROUP MyAg FAILOVER;

Q. What are DMVs used for AO?

A. select * from sys.dm_hadr_cluster

select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
select * from sys.availability_replicas
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states


Q. Can we delete primary or secondary replica database?

A. This task is supported only on secondary replicas. You must be connected to the server instance that hosts the secondary replica from which the database is to be removed

ALTER DATABASE MyDb2 SET HADR OFF;