We recently held an encore webcast with Microsoft’s Luis Vargas, program manager in the SQL Server group for AlwaysOn Availability Groups in SQL Server 2012. Like the first time, interest was extremely high. And with almost 400 very active participants asking questions, I have put together Part 2 of the AlwaysOn Availability Groups FAQ below.
Part 1 can be viewed here: AlwaysOn Availability Groups - FAQ Part 1
If you're looking for a recording of the AlwaysOn webcast, you can find it here:
1. What are the limitations of doing a SQL Server 2012 2-node Standard version in AlwaysOn?
AlwaysOn Availability Groups are only available with SQL Server Enterprise Edition https://www.microsoft.com/en-us/sql-server/default.aspx
2. What happens to Agent jobs that run on 2 or more databases when one of the databases is failed over?
Failover is at the Availability Group level, not at the database level. However, if you had a job that “touches” one database in an availability group and one that is not in the group, you would need to code the job in a way that you can detect that the AlwaysOn database is no longer active. With respect to backups, the Maintenance Plan Backup task is AlwaysOn aware and automatically runs backups on the preferred replica. If you are a LiteSpeed for SQL Server user, then we provide additional AlwaysOn backup options (Back up preferred, Back up primary, Back up all, Back up none). Backup jobs must be created on every replica in the AlwaysOn Availability group where backups are allowed to run since they are not replicated.
3. What kind of performance degradation for read only queries, if any, can be expected when running on an active secondary?
An active secondary in the same data center as the primary should have latency no different than the primary and performance should be just as good. New statistics automatically created on a secondary are maintained in tempdb (since the secondary is read-only). If the secondary is rebooted, those stats are lost and need to be rebuilt, so if they were helping query performance, there could be some degradation until they are rebuilt from new queries. On the other hand, if the index stats are up to date then there should be no negative effect. More information here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups
4. Can we failover from physical to virtual VM whether Hyper-V VM, VMware VM
Yes. Failover can happen from physical to physical, virtual to virtual, physical to virtual, or virtual to physical.
5. Are AlwaysOn Availability Groups certified by SAP?
Please review this document from SAP for more information: https://store.sap.com/sap/cpa/ui/resources/store/html/StoreFront.html?original_fqdn=ecohub.sap.com
6. Is it possible to add new databases to availability groups automatically or with scripting?
Yes. Please refer to this page: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database
7. Must AlwaysOn Availability Groups be installed on a cluster?
Traditional Windows – SQL Server clustering is not required for AlwaysOn. They can be used together, but it’s not necessary.
8. Do Availability Groups support databases stored on a Clustered Shared Volume (CSV) in Windows Server 2012?
CSV support extends to SQL Server data, however CSV does not support SQL Server Clustering. So it can be used with AlwaysOn as long as you don’t cluster your SQL Servers. More information here: https://technet.microsoft.com/en-us/library/jj612868.aspx
9. If I need to rebuild indexes, can I do this on the primary?
Index operations are fully logged and will be replicated to the secondaries. But because these types of operations can generate a lot of log activity, you may want to consider running them off hours
10. If a failover occurs due to server crash and only one replica is left in AG, will read-only queries read from the remaining primary?
Read-Only Application Intent connections succeed only when there is a read-only replica available. If none are available, then you may have to code the logic in the application to try a connection on a read-write replica. Please note that you can add the primary as the last replica in the read_only_routing_list, so that read-only connections are routed there if no readable secondary is available. More information here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover#ConnectToSecondary
11. Can a database in an AG be a content database of SharePoint?
Please refer to this TechNet article: https://technet.microsoft.com/en-us/library/hh913923.aspx
12. Would running a transaction log backup on an active secondary perform a checkpoint on the primary and not let the log files to grow on the primary side. In other words, are there any performance implications of running t-log backups on secondaries?
A log backup on a secondary will mark a new backup LSN on the primary and cause a log truncation up to that point. This backup LSN is replicated to the remaining secondaries, thus causing log truncation there too. There are no performance implications to the primary.
13. On an active secondary used for read-only queries, does an application holding locks on the secondary block the continuous restoration of transactions as can occur in traditional Log Shipping?
There are no locks held on active secondaries and therefore there is no blocking. All reads on active secondaries run in RCSI isolation level (Read Committed Snapshot Isolation) to reduce blocking. There can still be blocking in case of DDL operations as these require a schema update lock. There is a redo_blocked XEvents triggered in this case, to allow killing a blocking query. The recommendation is to run DDL at less busy hours.
14. Once an AG is set up, how much maintenance is required to keep it running?
DBAs need to be skilled in this feature, understand how to add and remove databases from an AG, set them up, take them offline for rebuilding, etc. So there is a cost in training, documenting procedures, etc.
15. Can we set up cross database AG where Database1 is active on InstanceA and has a secondary on InstanceB, and Database2 is active on InstanceB and has a secondary on InstanceA?
That would only work if the two databases were in different Availability Groups. Since all databases in an AG fail over together, there is no way to have some active on one instance and some active on another.
16. Could you have three different servers, each with its own unique AG all using the same server as a secondary? If so, how would overlaps in database names be handled?
That is supported as long as there is no overlap in database names. When you add the AG to the secondary during setup, SQL Server checks to see if there is an database name conflict and will report it as an issue immediately and disallow the addition of the AG. You would have to make sure all database names were unique or use separate SQL Server instances on the same physical secondary server to avoid the overlap.
17. Can two instances that participate in an AlwaysOn AG be located in different domains?
No, this configuration is not currently supported by Microsoft.
18. Do the primary and all secondaries need to have the same release version of SQL Server 2012? In other words, can each instance be on a different build number?
Yes, they can be different build numbers. When upgrading/patching, Microsoft recommends following a rolling upgrade process: Upgrading the secondaries first, failing over the primary, upgrading the ex-primary, and failing back.
19. Can the same database participate in more than one AG?
A database may only participate in a single AG.
20. If we have 2 read only secondaries for reporting, which server it used if we provide a listener in our connection string
Each active availability replica that needs to support read-only routing requires a read-only routing list. Read-intent connection requests are routed to the first available readable secondary in the list. More information here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups
21. Where can I read more about NetVault LiteSpeed for SQL Server?
We received this question many times from those attendees who were not familiar with our SQL Server backup & recovery product. LiteSpeed has a lot of great features that add considerable value to SQL Server and the DBA. You can download and read more about LiteSpeed here:https://www.quest.com/products/litespeed-for-sql-server/There is also a great Tech Brief that can be downloaded here: https://www.quest.com/techbrief/top-7-netvault-litespeed-features-dbas-should-know-about824950/