SQL Server 2012: Always On FAQs
As Always On is a new feature and many DBAs and Infrastructure teams have several questions regrading this solution, I thought of writing a blog post with one line answers for some of the most common FAQs on Always On solution. I will be adding more questions as we go. I have collected this information from MSDN articles, internal discussions etc.
Supportability:
1. Can we have AG created across multiple domains?
No
2. Can we have AG created against multiple clusters in the same domain?
No
3. Can we have 2 SQL Instances (Replicas) running on the same node, being part of the same AG?
No
4. Can we have AG created on 3rd party cluster solutions?
No
5. Is there a particular quorum model to create the cluster for AG?
No, Use the best quorum to keep your cluster online. https://msdn.microsoft.com/en-us/library/hh270280.aspx.
6. Can we create an AG with local machines and VM machines in Windows Azure?
No, It is currently not supported on Windows Azure VMs(slated for future releases)
7. Can we replicate system databases in AGs?
No
8. Can we have AGs created across VMs on same physical host?
Yes you can
9. Do we support Cross database transactions in an AG?
No, we do not
10. Can we have Availability Groups for SQL 2012 32 bit running Windows 64 bit platforms?
No, it is not supported
11. Do we have any Windows hotfixes to be installed on Server 2008 to better support Always On?
https://msdn.microsoft.com/en-us/library/ff878487.aspx#WinHotfixes
Setup\Installs:
1. Do we require some kind of shared storage to create an AG?
Shared Storage is optional for AG.
2. Can we use same service accounts for all replicas?
Yes if you want Kerberos Authentication to the listener
3. Do we need to use the same disk structure across all the availability replicas?
Not needed but recommended because of Add file operations.
Failover Scenarios:
1. What happens when your primary replica is offline? Which replica does your AG failover to?
AG fails over to the replica which has been configured for Automatic failover partner. If there are no automatic failover partners AG doesn’t failover automatically
2. What happens when the original primary replica comes back again?
When the original primary comes back online, it will assume the role of a secondary and starts syncing pages with the current Primary.
3. What happens when you do a forced failover because primary is not available and the old primary comes back online after an hour in Async AG replicas? Do we have to re sync databases?
No, you need not resync db's, the secondary(old primary) will sync the pages for transactions which were inflight with the current primary and then starts synchronizing . so no more resync
4. In a scenario, where your automatic failover secondary replica fails, will it automatically configure another sync replica to become Automatic.
No, It will not you have to manually failover to the sync replica. However you can convert a sync replica to automatic anytime.
5. Can we control which secondary replica it will failover if it is not configured for Automatic failover?
No, we do not . https://sqlcat.com/sqlcat/b/msdnmirror/archive/2012/03/30/do-not-use-windows-failover-cluster-manager-to-perform-availability-group-failover.aspx
Integration with other Technologies:
1. Can we combine AG and DB Mirroring?
No, They do not interoperate. https://msdn.microsoft.com/en-us/library/hh710077.aspx
2. Can we combine AG and Replication?
Yes but Distributor is not supported
3. Can we combine AG and Log Shipping?
Yes it shouldn’t stop
4. Can we configure AG for Sharepoint Server?
Yes, https://technet.microsoft.com/en-us/library/hh913923.aspx
5. Can we create a DB snapshot on a secondary replica?
Yes
6. Can we use SSRS and AG together to query data from secondary replicas?
Yes, https://msdn.microsoft.com/en-us/library/hh882437.aspx
Connectivity\Listener:
1. Can you configure connection to AG listener as Kerberos?
Yes See above
2. How many network names\listener names can be associated to an AG?
1 network name, more than 1 will be ignored even if present
3. Will SQL browser detect, if AG listener name is listening on a different port other than 1433
SQL Browser is not aware of the listener and cannot provide clients the information on which port the listener is running, you need to hard code the port number from the client side.
DNS\AD\Kerberos:
1. Can we create an SPN for the AG listerner?
Yes, Use same service accounts across all of them
2. Can we create an SPN if the listener is listening on a different port number?
Yes, as long as the service accounts are same across all replicas
3. Can we create a Cname\Alias for the AG listener?
Yes and also you can manually create an SPN for it to force using Kerberos
Active Secondaries\ Manageability:
1. What happens when 2 different secondary replicas backup the transaction log at the same time?
Only 1 replica can backup the log at a time, the other replica will be blocked waiting for backup lock resource
2. What happens when 2 different replicas run CHECKDB at the same time?
No issues
3. Is all my backup information located in a centralized location when we are using AG to do backup across all replicas?
No, it decentralized to each msdb on every replica
4. Do we need to have same file structures on all the replicas
Yes, it is recommended to have the same storage structure as it can impact a Add file operation. https://msdn.microsoft.com/en-us/library/hh510190.aspx
5. Can we configure our own policies in the Always on Dashboard?
Yes we can configure
6. What is a rolling upgrade?
Rolling upgrade is a process where we upgrade the secondary replicas first, failover and upgrade the primary replicas. Reduces Downtime
7. Can we backup logs on any secondary replica in the AG architecture?
Yes, we can backup logs on any secondary replica as long as the secondary replica is in Synchronizing or Synchronized states. We can also configure backup priority for these replicas.
Read Only Routing:
1. Will Read Only Routing use Kerberos?
Yes, as long as you have the same service accounts across and there is an SPN created for the secondary replica
2. What is the response when you connect to a AG listener to a db configured for ReadOnly Routing?
TDS token containing the secondary replica connection information.
3. What is the connection string item which distinguishes an application that it is intended for read only operations?
ApplicationIntent=ReadOnly;
4. Does ReadOnly routing do load balancing?
No, It doesn’t. it only does routing . You need to configure your own solution for load balancing.
If there is more than one read only replica in the routing list, when the first replica in the routing list is offline, the connection will be routed to the next replica in the routing list.
Performance:
1. How does AG improve failover times ?
As there is no database recovery during failovers, however if there is a long REDO queue there will be delay in online time following a failover.
2. Can we create indexes on the secondary replicas?
No
3. Can we create statistics on the secondary replicas?
No, However you can take advantage of the temporary statistics which are created on the secondary replica stored in the tempdb database.
4. What are the Impacts of Snapshot Isolation levels on secondary replicas?
All isolation levels are defaulted to snapshot isolation level, it impacts tempdb and also the local db as it will add a 14 byte pointer to every row on the primary and secondary
5. How many worker threads will be taken for every AG database?
Uses a request queue and worker thread pool. Minimum of 2, Now we implement a pool design
6. How many REDO threads can we have for an AG db?
One
7. Do secondary replicas talk to each other in case the primary is offline?
No, All the secondary replicas communicate with the Primary replica only. Incase the primary replica is offline and there is no automatic failover, all the secondary replicas go to RESOLVING state.
8. How do we avoid Split Brain mechanisms in Always On?
Quorum model in the cluster and lease expiration mechanism
9. How do we know which secondary replica has the least latency for a AG database?
Run sys.dm_hadr_database_ replica_states on all the secondaries and check for end_of_log_lsn for each secondary. The one with the highest has the least latency.
AG + FCI:
1. What is a race condition? How are we changing it in design
Race condition means where windows cluster failover and AG failover race against each other which can lead to unpredictable results. Hence in Always on FCI, we are disabling Automatic failover for AG
2. Do we have Automatic failover for AGs when they are clustered SQL Instance?
No, there is no automatic failover when your replicas are clustered SQL Instances.
Multi Site\Multi Subnet:
1. How do we configure AG to detect multisite subnets?
It automatically detects multiple subnets.
2. Can we use AG as inter data center DR solution?
Yes, however you need to make sure the network latencies
3. How do we configure SQL FCI instance across multiple data centers using AG as DR mechanism
See SQL CAT whitepapers explaining these solutions
4. Can we deploy AG in more than 2 subnets?
Yes
5. Is it possible to add a replica from a different subnet after the AG is created?
Yes we can, you need to manually add the IP address resource from the new subnet as a dependency to the AG listener name
6. What is the connection string which makes connections to all the registered IP address in parallel reducing the client connectivity time to the AG listener?
Multisubnetfailover='TRUE'
7. Is there a distance limitation for datacenters which can configure Always Ags as the DR solution?
No, there is no distance limitation. However network latencies between subnets shouldn’t exceed 4000ms
8. What are the other considerations to keep in mind while configuring multisubnet failover solutions?
CrossSubnetDelay and DNS latency are very important while setting up these solutions. Please refer to https://technet.microsoft.com/en-us/library/dd197562(WS.10).aspx for more information