SQL Server 2014 AlwaysOn Availability Groups - Add Azure Replica wizard in action
In this blog post, I will describe one interesting feature included in SQL Server 2014: the capability of adding an availability group secondary replica in Windows Azure through a useful Wizard.
I have already published a couple of articles talking about Windows Azure and SQL Server in hybrid scenarios. I will reference them later in this document as they explain in detail some configuration step needed by this post and for which I won’t go to much in detail here.
First thing to underline is that the Add Azure Replica wizard is a client tool, so we just need to install SQL Server 2014 Client Tools to use it. It targets SQL Server 2014 (now in CTP2) but also SQL Server 2012 SP1. Supporting SQL Server 2012 is interesting for those customers whom have just finished upgrading and, at the same time, they want to take advantage of the cloud features for disaster recovery, backup or resource offloading purposes.
Prerequisites for the Add Azure Replica wizard
1. An AlwaysOn Availability Group configuration already in-place in the on-premises environment.
2. A Windows Azure subscription that will host SQL Server AlwaysOn secondary replicas
3. A VPN Site-To-Site between your on-premises network and Windows Azure.
Test environment topology
The left side of the image below shows the on-premises environment (part of the prerequisites). The Add Azure Replica wizard will configure the third node (the right side of the image); in my example, it is the 10.0.0.0 Virtual Network. In other words, SQL Server will take care of this step for you! We just need to have the Virtual Network and the VPN tunnel properly configured.
ON-PREMISES Virtual Machines and installed Features/Roles
Below you can find the configuration that I have used for testing.
- SQL2014DC (IP 192.168.0.18). Domain Controller (in my example I have created an Active Directory domain called dotnetisland.org), DNS Server (also used by the Windows Azure Virtual Network for name resolution), SQL Server Management Tools
- SQL2014N1 (IP 192.168.0.16). Member Server, Windows Server Failover Cluster (e.g. cluster1.dotnetisland.org), AlwaysOn node 1 (synchronous replica), user databases
- SQL2014N2 (IP 192.168.0.19). Member Server, Windows Server Failover Cluster (e.g. cluster1.dotnetisland.org), AlwaysOn node 2 (synchronous replica), user databases
In order to mimic a real environment, it has been created different accounts to separately execute services and to run the Wizard. It has been configured a service account for SQL Server (sqlservice) and a user account to run the wizard (sqluser). Secondarily, it has been added two databases to the AlwaysOn group, and each database has database and log files on different disks. The wizard does a great job in recognize the on-premises setup and applying the appropriate configuration to the remote node.
Windows Azure network configuration
It has been configured a site-to-site VPN and specified the on-premises DNS server. To create a site-to-site VPN you need to either configure a VPN hardware device, or use Windows Server 2012 with the RRAS service. Details on how to configure a site-to-site VPN are available at this link or you can visit my previous blog post. There is also a good tutorial available on the Windows Azure website, here. This step could be quite complex for a DBA who is not also a network engineer. Therefore, especially in configurations that require the configuration of a VPN hardware device (e.g. a Cisco or a Juniper firewall), I recommend to ask the help of a network administrator. Once the VPN will be up and running, you will see Windows Azure as an extension of your datacenter, and the rest of the configuration is straightforward.
Please note that Windows Azure Virtual Networks do not allow Static IP Addresses. When deploying a Virtual Machine in Windows Azure, a DHCP Server will assign an IP address from the subnet you specify. It will stay with the VM for its lifetime, unless you decide to stop and deallocate it.
The image below, taken from the Windows Azure portal, describes the site-to-site VPN configuration that I have used.
francedsitetosite represents the Virtual Network name
MyOnPrem is the on-premises subnet
When you see that the half-blue and half-green bar connects the two blocks (in my case francedsitetosite and MyOnPrem), it means that the VPN tunnel is working fine.
DNS Servers block contains the list of the on-premises DNS servers used for name resolution. You can set more than one DNS Server if you want. In my example the DNS Server is also the Domain Controller for the Active Directory domain called dotnetisland.org
DATA IN and DATA OUT indicate the amount of data moved in and out the VPN tunnel.
GATEWAY IP ADDRESS (hidden by the black bar in the image) is the IP Address of the Windows Azure Virtual Gateway that will establish the VPN tunnel
Adding a third Windows Azure node using the Add Azure Replica wizard
The Wizard can be started from the “Add Replica…” context menu in SSMS. We will add a third, asynchronous replica hosted by a Virtual Machine that we would call e.g. FDIAZSQLNODE3
First thing to do is to connect to your Windows Azure subscription. In order to perform this task, the wizard allows the connection to the Windows Azure portal to download a management certificate.
After the connection to the Windows Azure portal has been established, we have to specify the following parameters:
- Image type (in my example SQL Server 2014 CTP2 Evaluation on Windows Server 2012 R2). The available SQL Server images from the Image Gallery will fill the dropdown list.
- Image size (a Large VM is the minimum recommendation for a production environment)
- Virtual Network name and Virtual Network Subnet name
- Virtual Machine name and Virtual Machine local credentials
- Domain name and Domain user credentials
And this is it…
Following screenshots are the same as adding an AlwaysOn replica on-premises. Here, for example, we could decide to set the secondary as readable.
The screenshot below provides also a link to check the report log, that can help us to troubleshoot in case of errors.
What the wizard has done for us (behind the scenes)?
- It has created a Windows Azure Storage account
- It has created a cloud service and the Windows Azure VM will be assigned to it. If you will add additional AlwaysOn replicas in Windows Azure, they will share the same cloud service.
-
- The name of the cloud service will be: alwayson+[name of the Virtual Network]+[name of the Availability Group]+GUID
- It has provisioned the virtual machine, using the image of our choice, and consequently added the data disks needed by SQL Server
- The virtual machine has been added to the Active Directory domain (in my example the domain is dotnetisland.org)
- The Failover Cluster feature has been installed and the VM has been joined to the cluster
- Configured the AlwaysOn availability group and the databases.
In case of failures (e.g. connectivity issues), a detailed log is available and the steps already taken will be rolled back.
Considerations and resources
Several tests, using database of small size, took less than 40 minutes to complete, and considering that it has not been configured anything on the Azure side (except VPN), this might definitively be a feature that DBAs should explore.
I have also tried to challenge the wizard, adding more nodes to the cluster, simulating several failures and I can say that, even if I am using a CTP version, it is already stable and it works very well. Below you could find some links with additional information.
https://msdn.microsoft.com/en-us/library/dn463980(v=sql.120).aspx
https://msdn.microsoft.com/en-us/library/ff877884(v=sql.120).aspx
Francesco