Always on interview questions

AlwaysOn Availability Groups - FAQ

  

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

    SQL Server Enterprise Edition

 

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

    Groups?

    You need to use Windows Server Enterprise above   

    

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 that 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 desiredhttp://msdn.microsoft.com/en-us/library/ff877931

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

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

 

10. Can I use a secondary for backup?

 Yes. 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?

 Maybe. 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?

 No. If you require additional indexing to improve performance on a read-only secondary, then you 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?

 No. 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?

 Read-only queries 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?

 Yes. DDL changes are automatically migrated to secondaries automatically

 18. Do AlwaysOn Availability Groups help with database corruption?

 Yes. 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?

 Yes. 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?

 Yes. 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?

 Yes. 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 scheduledhttp://technet.microsoft.com/en-us/library/hh758463

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

 No. You would need to decrypt the database and follow these guidelines to add a database that was previously encrypted to an AlwaysOn Availability Grouphttp://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?

 Yes. 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?

 No. 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?

 Yes. 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?

 

Yes. File operations are replicated automatically