In this Essential Guide to SQL Server 2014 series I’ve been covering some of the most important new features in SQL Server 2014. In the last column I covered migrating to In-Memory OLTP using the Analysis Migrate Report (AMR) tool. In this column I’ll cover the new features in SQL Server 2014 AlwaysOn Availability Groups. AlwaysOn Availability Groups were first introduced with SQL Server 2012. In case you don’t know about them, an availability group provides automatic or manual failover environment for a set of user databases. All of the databases in the set will failover together as a group. The original implementation for SQL Server 2012 provided support for four secondary replicas and only one of them could be synchronous. Secondary replicas can support read-only workloads. All replicas in an AlwaysOn Availability Group must reside on a Windows Failover Cluster node. SQL Server 2014's AlwaysOn Availability Groups have been extended and enhanced in a number of ways that both improve their flexibility as well as making them a more robust high availability and disaster recovery solution. With SQL Server 2014, Microsoft added support for additional secondary replicas as well as provided Windows Azure integration.
More Synchronous and Asynchronous Replicas
SQL Server 2014 increased the maximum number of secondary replicas from four to eight. In addition, now two of those replicas can now be synchronous. Like the earlier release you can freely mix and match synchronous and asynchronous replicas to provide both high availability and disaster recovery. Secondary replicas are also now available for read-only workloads even when the primary replica is unavailable.
Windows Azure Replicas
SQL Server 2014 now provides Windows Azure AlwaysOn integration for disaster recovery in the cloud. In the event of a local database outage, you can run your SQL Server databases from Windows Azure virtual machines that host AlwaysOn replicas. Replicas in Azure must be asynchronous and you must be running SQL Server 2014. They only support manual failover.
The new Windows Azure AlwaysOn availability options are fully integrated into SQL Server Management Studio (SSMS). To create an asynchronous availability group replica in Windows Azure you need to enable AlwaysOn Availability Groups using the SQL Server Configuration Manager. Then open SSMS navigate to the AlwaysOn High Availability node and either use it to create a new Availability Group or to add a replica to an existing Availability Group. If you have an existing Availability Groups you can select it and then use the Add Replica wizard that you can see in Figure 1 to add an asynchronous replica in Azure.
The wizard will take care of creating an Azure virtual machine that will act as an asynchronous replica. You do need to have a VPN connection from your on-premise network to Azure for the wizard to work.
In closing its worth mentioning that AlwaysOn replica databases are not a substitute for performing backups. You still need to continue to back up your databases and your transaction logs to provide complete data protection.
