SQL Server Denali AlwaysOn–Q&A
Since the release of SQL Server Denali CTP3 I have been talking more and more to customers and partners about this release and one of its great new capability called AlwaysOn. The AlwaysOn feature basically provides an easy way for DBAs to enable High Availability and Disaster Recovery solutions for their application and custom database(s) – yes multiple databases.
For the below Q&A I will assume some basic understanding of AlwaysOn, if you are not aware of it at all, then you can read an overview at - https://msdn.microsoft.com/en-us/library/ms190202(v=SQL.110).aspx
How many replicas supported in SQL Server Denali CTP3?
The out of the box install of Denali CTP 3 will support one secondary either in Synchronous/Asynchronous mode. It also supports automatic failover and also the secondary can be readable.
Should you wish to test multiple replicas you can enable Trace Flag 9532. For more information see:
https://msdn.microsoft.com/en-us/library/cc645581(SQL.110).aspx#TF9532
Note: This is a CTP 3 only restriction and by the time Denali releases we will support 4 replicas with 2 being synchronous and one pair of primary/secondary being automatic failover.
Database Features supported in CTP3?
The next common question I get is, are the other features of SQL Server such as Filestream, Fulltext, etc supported. Below is a list of other database engine features AlwaysON supports:
AlwaysOn Availability Groups can be used with the following features or components of SQL Server:
Change data capture
Change tracking
Contained databases
Database encryption
FileStream and FileTable
Full-text search
Remote Blob Storage (RBS)
Replication
Service Broker
SQL Server Agent
Source: https://msdn.microsoft.com/en-us/library/ff877884%28SQL.110%29.aspx
Number of Databases in a Availability Group (AG)?
There is no hard limitations on the number of databases in each AG, and the answer to this question is going to be the classic IT one “it depends on workload”. One may think back to the limitations stated with Database Mirroring (DBM) especially with 32bit environments stating it was 10 databases per instance of SQL Server, this was essentially due to the dedicated thread(s) it would required. You will be glad to know that this doesn't apply to AG as there has been enhancements made so that the process is built into the core database engine which enables it use thread pooling and provide better performance.
The main bottleneck in AlwaysOn AG is now going to be the network, and so a lot is going to depend on the latency and bandwidth of the network. Having said the above, the product team is currently testing with a customer 4000 databases in a single AG.
When would you not use AlwaysON availability groups?
Use log shipping /DBM when Disaster Recovery is across multiple domains as Always On uses Windows clustering which requires it to be in the same domain.
Also AlwaysON doesn't have capability of delayed restore to protect from logical errors (accidental deletion of data from table, update of incorrect table, etc) so Log shipping is still a good solution to help protect against these issues.
If you have multiple databases in a Availability group and one of the databases has a failure, will it automatically failover?
The short answer to this question is No. The AG uses Windows Clustering for detection of failure events, and with Denali this process has been enhanced to query more then just select @@version. However the failure detection is at the instance level. The stored procedure used to diagnose state of the AG (both Failover Clustering & AG) is The sp_server_diagnostics (https://msdn.microsoft.com/en-us/library/ff878233(SQL.110).aspx).
If you require to have individual database level detection this can be achieved one of the following methods:
- Monitoring through System Center Operations Manager to see if a database has gone offline and then automating a response to cause the AG to failover
- As AG can be managed via policies in SQL Server, a response to a change in policy can be created to cause a failover to take place.
- Manual intervention//scripting
What recovery mode do the databases need to be in to participate in AlwaysOn?
Full Recovery Mode is required for databases to participate in AlwaysOn.
How is a fast failover supported between multi subnet sites?
Connectivity providers such as the .NET 4.0, ODBC and latest SQL Native client are able to cache the names of the secondary site(s) and don't have to wait for DNS to be updated to reroute the connection. This is feature only in new connectivity providers, the older providers will just continue trying to connect to each replica with its default TCP timeouts between each try.
What happens in the situation of the transaction log running out of space?
Add additional log file, it is sequentially written to, unlike the data file for which writes are striped across all files. This is the same as for Database Mirroring.
Can you have different indexes or statistics in the Readable secondary database?
You cannot manually as a DBA create different indexes or statistics on the Readable secondary database. However the database engine automatically creates temporary statistics on the secondary readable to cater for the different usage of the database. These temporary statistics get created in TempDB and requires auto stats/update stats to be enabled. The creation of statistics create a log record and thus whatever new statistics are created on the primary get moved to the secondary.
The sample statistics take about 8KB of space so it doesn't have a major impact on your TempDB database.
How is the data moved in AlwaysOn to the secondary?
The mechanism to move data is similar to DBM which also uses the Service Broker mechanism, however in Denali due to the integration into the DB engine it is more efficient then DBM.
What are the limitations of Availability groups?
- Distributed transactions support – AG is not able to support cross database distributed transactions. This is a issue with MSDTC not being able to be registered at a sub instance level. This is a known issue and is being worked on for either final or future releases.
- Cannot have 2 replicas on same physical machines - in a clustered environment you will need to ensure that there are no overlapping possible owners if you want to have AGs between the nodes.
What is the recommendations with file paths for databases?
Just like in DBM the recommendation is to keep exact same file paths at both Primary and Replicas, this ensures that any file based operations such adding of a file, etc works as expected on all replicas.
Is replication supported with AlwaysOn?
Yes it is and it is similar to DBM support, so if a publisher fails over it is able to fail across to the secondary. The distributor cannot be part of AlwaysOn.
Can the network traffic for AlwaysOn data movement be dedicated to certain network interfaces?
As it uses the Services Broker infrastructure, you can bind different network and use NUMA Network configurations to isolate network traffic.