AlwaysOn Availability Groups - FAQ (4225142)

Posted by David Gugick Aug 23, 2012 to the LiteSpeed Community

We recently held a webcast with Microsoft’s Luis Vargas, program manager in the SQL Server group for AlwaysOn. Interest in AlwaysOn is extremely high as it’s one of the really compelling new features in SQL Server 2012. New features often come with a lot of questions and the webcast was no different. I spent the entire hour answering questions in the Q & A window (which prevented me from watching and enjoying Luis’ presentation). We probably could have extended it another 30 minutes and still had questions left unanswered.

I reviewed the questions from the 350+ webcast attendees, added a few of my own, and answer them below. If there are any mistakes, and there are bound to be some (like when I misspoke during Q & A about indexes on read-only secondaries), please let me know in the comments and I'll update as needed. If you have a question that is not answered below, please post it to the comments and I will get you an answer.

If you're looking for a recording of the AlwaysOn webcast, you can find it here: http://www.quest.com/webcast-ondemand/-experts-perspective-webcast-achieving-high-availability-with-sql-serv818161.aspx

1. Which SQL Server Editions include AlwaysOn Availability Group functionality?

SQL Server Enterprise Edition http://www.microsoft.com/sqlserver/en/us/editions.aspx

2. Do I need to use a specific Windows Server edition with AlwaysOn Availability Groups?

You need to use Windows Server Enterprise or above http://www.microsoft.com/sqlserver/en/us/editions.aspx

3. What are the prerequisites for AlwaysOn Availability Groups?

More information can be found here http://msdn.microsoft.com/en-us/library/ff878487.aspx

4. How many replicas can I have in an AlwaysOn Availability Group?

5 total – 1 primary and up to 4 secondaries

5. How many databases can participate in an AlwaysOn Availability Group?

Up to 100 is the recommendation, but it’s not enforced

6. How many AlwaysOn Availability Groups can I have on an instance?

Up to 10 availability groups is the recommendation, but it’s not enforced

7. What's the difference between asynchronous and synchronous availability modes?

Asynchronous-commit mode is best for instances are in different data centers. Once a transaction is written to the log, the primary sends confirmation to the client. Transaction latency is low http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson.aspx. Synchronous-commit ensures transactions are committed to all synchronous secondaries before committing to the primary replica. Transaction latency is increased http://msdn.microsoft.com/en-us/library/ff877931

8. How many synchronous secondary replicas can I have?

You can have up to 2 synchronous replicas, but you are not required to use any. You could run all secondaries in async mode if desired http://msdn.microsoft.com/en-us/library/ff877931

9. Can I use a secondary for read-only queries?

An active secondary can be used to offload read-only queries from the primary to a secondary instance in the availability group http://msdn.microsoft.com/en-us/library/ff878253 

10. Can I use a secondary for backup?

An active secondary can be used for some types of backups http://msdn.microsoft.com/en-us/library/hh245119 

11. What types of backups are supported on active secondaries?

You can run Copy Only Full backups and regular (non-copy only) transaction log backups on active secondaries. Differential backups are not supported on active secondaries http://msdn.microsoft.com/en-us/library/hh710053

12. Do I have to license my secondary replicas?

You are allowed to use a single passive secondary replica at no additional SQL Server license cost. However, a second passive secondary must be licensed. All active secondaries (used for read-only queries or backup) must be licensed

http://download.microsoft.com/download/7/3/C/73CAD4E0-D0B5-4BE5-AB49-D5B886A5AE00/SQL_Server_2012_Licensing_Reference_Guide.pdf

13. Can I create additional indexes or statistics on read-only secondaries to improve query performance?

If you require additional indexing to improve performance on a read-only secondary, then tou should weigh the cost of creating and maintaining the index on the primary replica http://msdn.microsoft.com/en-us/library/ff878253.aspx#Indexing 

14. Can I create additional statistics on read-only secondaries to improve query performance?

However, you can allow SQL Server to automatically create statistics on read-only secondaries. Auto-created statistics are placed in tempdb. A failover or restart removes that information http://msdn.microsoft.com/en-us/library/ff878253.aspx#Read_OnlyStats 

15. What is the impact of running read-only workloads on active secondaries?

Can take resources from the redo thread and slow down synchronization and add latency in synchronous availability mode.

16. How do I limit redo thread impact of running read-only workloads on active secondaries?

Use the resource governor to limit CPU. Run DDL modifications during times of low activity

17. Can I make DDL changes to the primary in an AlwaysOn Availability Group?

DDL changes are automatically migrated to secondaries automatically 

18. Do AlwaysOn Availability Groups help with database corruption?

If a corrupt page is detected, SQL Server will attempt to repair the page by getting it from another replica http://msdn.microsoft.com/en-us/library/bb677167.aspx 

19. Can I manually fail over to a secondary replica?

If the secondary is in synchronous-commit mode and is set to “SYNCHRONIZED” you can manually fail over without data loss. If the secondary is not in a synchronized state then a manual failover is allowed but with possible data loss http://msdn.microsoft.com/en-us/library/hh213151.aspx 

20. Can SQL Server automatically fail over to a secondary?

If the primary and at least one secondary are set to AUTOMATIC failover and the secondary is synchronized, then SQL Server can automatically failoverhttp://msdn.microsoft.com/en-us/library/hh213151.aspx#TermsAndDefinitions 

21. Can I run DBCC CHECKDB on secondary replicas?

You can run DBCC CHECKDB, but you should try and run DBCC CHECKDB on the primary as well as any secondary being used for backups

22. Are AlwaysOn Availability Groups a replacement for Log Shipping?

Probably, with the following caveats: With AlwaysOn, log records are applied immediately, so there is no delayed apply ability. If you require a delay for DR (e.g. someone accidentally drops a table and you want to try and have some time to repair from the subscriber in a log shipping setup), you may want to consider continuing to use log shipping. If you remove log shipping, you lose your log backups and they will now have to be scheduled http://technet.microsoft.com/en-us/library/hh758463

23. Can I use Transparent Data Encryption with AlwaysOn Availability Groups?

You would need to decrypt the database and follow these guidelines to add a database that was previously encrypted to an AlwaysOn Availability Group http://msdn.microsoft.com/en-us/library/hh510178.aspx 

24. Do I have to run my backup jobs on all replicas that can participate in backups?

You must schedule your backups to run on any replica that you want to (or can) participate in a backup. You can use to the system function sys.fn_hadr_backup_is_preferred_replica to determine if the current replica is the preferred one for backup

25. If I run a transaction log backup on an active secondary, what happens to the transaction logs on other replicas?

They are maintained and the logs are cleared on other replicas to maintain the log chain http://blogs.msdn.com/b/sqlgardner/archive/2012/07/18/sql-2012-alwayson-and-backups-part-1-offloading-the-work-to-a-replica.aspx

26. Do AlwaysOn Availability Groups require a Witness server like mirroring?

AlwaysOn Availability Groups do not require a witness SQL Server. The underlying Windows cluster can (optionally) use a witness File Share as a voting member. http://msdn.microsoft.com/en-us/library/hh270280.aspx 

27. Can I disallow read-only connections on the primary?

To enforce this, specify the read_only intent in the connection string and add only secondaries (not the primary) to the read_only_routing list. If you want to disallow direct connections to the primary from read_only connections, then set its allow_connections to read_write. http://msdn.microsoft.com/en-us/library/hh213002.aspx 

28. When failover occurs, does the application need to do anything special for an existing SQL server connection that was established via the AlwaysOn Availability Group listener or will the connection automatically reestablish and continue to work?

When a failover occurs, the application’s connection is broken. The application needs to have connection retry logic to reestablish connectivity. http://msdn.microsoft.com/en-us/library/hh205662.aspx

29. Can my failover instances be located on different subnets and, if so, do I need to do anything different with the application connection string?

Yes, instances can be located on different subnets. Either way, to make failover as fast as possible, use MultiSubnetFailover=True in connection strings so the client attempts connections in parallel. http://msdn.microsoft.com/en-us/library/hh205662.aspx

30. For synchronous commit mode, what is the maximum Latency allowed before the secondary loses its synchronized status and is this latency configurable?

The primary will wait 10 seconds without ACKs. This is configurable via SESSION_TIMEOUT option. http://technet.microsoft.com/en-us/library/hh213612.aspx

31. If a DBA grows a data file manually on the primary, will SQL Server automatically grow the same file on secondaries?

File operations are replicated automatically