Partager via

SQL Server 2012: Always On FAQs

  • Article

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

Comments

  • Anonymous
    January 11, 2013
    Hello! Nice FAQ. I'm new on this scenary and I have some questions that maybe you can help me: What happens if I lose the AG Listener? Can I have a LNB, or use some other resource, to have HA of the listener? Thanks in advance.

  • Anonymous
    January 22, 2013
    What about Operating System Version? Can I use Windows Server 2012 Standard?

  • Anonymous
    January 28, 2013
    Niels. We need Enterprise Edition of SQL Server, However, Operating system can be either Windows Server 2012 Standard or Enterprise. All the features and scalability are same on Standard and Enterprise editions of Windows Server 2012 with the exception of Virtualization.

  • Anonymous
    January 28, 2013
    Thank you very much for your answer Krishna Golla and many thanks for this site. Rgrds NR

  • Anonymous
    November 14, 2013
    This is wrong.

  1. How many network nameslistener names can be associated to an AG?    1 network name, more than 1 will be ignored even if present

  • Anonymous
    January 09, 2014
    I like your FAQ, however I couldn't figure out what an AG is.  For anyone else who is wondering, it is an Availability Group (AG).

  • Anonymous
    May 26, 2015
    Do we have same blog for SQL Server 2014

  • Anonymous
    August 14, 2015
    Hi Krishna, Thanks for nice post. I have one question sorry if I am repeating it. Assume we have AO AG configured with two sync secondary with automatic failover option. In case if I take primary SQL Instance's service offline or in stop state. In standard case what will be the result? I have tested it and I can see primary database is not available since SQL Service is stopped and other two secondary replicas with resolving status. Is that right behavior or something missing in configuration? Can you please confirm? Thanks for your time and help in advance. Thanks

  • Anonymous
    August 17, 2015
    Please ignore my query as I was able to figure it out. I've to change value of cluster fail-over attempt & time to test it. On stopping of primary SQL Server service it is triggering failover and working as per the expected.

  • Anonymous
    September 21, 2015
    I have 2 node cluster (Node1 and Node2) and one Availability Group in SQL server 2014 enterprise edition. The availability group includes 1 Databases (DB1) If DB1 is corrupted in primary server then what will be the Behavior of the failover?

  1. The DB will be automatically failover to Secondary server ?
  2. DB1 is not accessible any more? Can you give any link related to this issue?
  • Anonymous
    October 27, 2015
    Hi Jose, As per my understanding, database corruption will not trigger failover. In such case you have to act manually since your SQL Service is running and database may available partially. In case due to corruption your SQL Service is failing then there are chances for failover. In other end, if your database corruption is due to H/W issue (specifically due to storage/HDD) then you are good since your secondary replica is in good position. In worst case scenario you can break the AOAG to bring secondary replica online as primary. Prior to that you can try manual failover as well. I haven't faced such incident so not in position to provide real time exp. Share your thoughts as well. Milind