A few months ago I wrote this article where I have described how to setup an Hybrid IT scenario using Windows Azure point-to-site VPN and SQL Server configured using Log Shipping. The concepts and the network basics remain more or less the same, but in this case I have created a little bit more sophisticated scenario that uses SQL Server 2014 CTP2 AlwaysOn Availability Groups and leverages a Site-to-Site VPN lo link a on-premises network (using a RRAS Server) and a Windows Azure Virtual Network Gateway.
The test environment is displayed in the following picture.
I have 1 Host server with the RRAS Service enabled. This host has a public IP port which is able to talk with a Windows Azure Virtual Network. You can setup a VPN with Windows Azure using a VPN device or a RRAS server. More details on requirements and supported devices can be found here http://msdn.microsoft.com/en-us/library/windowsazure/jj156075.aspx
The host has 3 Virtual Machines (subnet 192.168.0.0/24) with the following roles/services enabled:
Each guest Virtual Machine has the routing table that allows to redirect the traffic to Windows Azure using the RRAS Service. In the following image the arrow indicates the remote network in Windows Azure and the gateway (hidden by the green line) is the public IP of the local Firewall, my RRAS server.
Let’s have a look at the network configuration in Windows Azure:
As I mentioned before, our Virtual Network is called HybridNet, and it’s using an affinity group (HybridAG) mapped with a virtual network created in a data center in Western Europe.
The local networks tab lists the name of the local network subnet (192.168.0.1/24) and the on-premises VPN gateway public IP address (our RRAS Server)
The HybridNet dashboard (image below) shows the details of the configuration, including the Virtual Gateway IP Address, the list of the Virtual Machines running in Azure and that are using this network, and the inbound and outbound traffic generated so far. Inside the ellipse you can also see that there is a link to download the script for your on-premises VPN device
Let’s have a look at the on-premises network configuration
I have downloaded in the local Host Server the configuration script from the Windows Azure portal (in case of a RRAS Server, it is a Powershell script)
I have configured the RRAS Server and run the .PS1 script, and the result is the VPN connection up and running between my on-prem network and Windows Azure
Active Directory Domain configuration
Looking at Active Directory Site and Services and at the DNS Manager console, you can see that the domain is spanned across the two datacenters. I recommend to run DCDIAG command in every domain controller and to test if the Active Directory replication is working fine before proceeding with the configuration.
Inside the rectangles of the DNS console manager you can find the servers involved in the configuration. hybridclu is the IP created by the cluster configuration, that can be seen in one of the images below.
These are the pictures that describe the 3 nodes cluster configuration, where SQLCTP2 is the VM running on Windows Azure, and two networks, one on-premises (192.168.0.x) and one on Azure (10.0.1.x)
AlwaysOn Availability Groups configuration
I have configured an AlwaysOn Availability Group group called ag1 where the database replicas are available in the 3 servers of the cluster. SQLCTP2 (Azure) is configured in asynchronous while FRANCEDWPC2013 and WPC2013ND, the 2 nodes on-premises, are configured in synchronous replication. There are 2 databases inside ag1, AdventureWorks2012 and db1.
SQLCTP2 is also a Readable Secondary, so we can offload some workloads, like a report server or the backup operations.
To test the configuration we can insert a new record on-premises (FRANCEDWPC2013) in the tUsers table inside db1 database and see if the data is replicated on the Windows Azure node on Azure (SQLCTP2)
We could use also this remote server to take backups, but please consider that you have to use Copy-Only backups, to don’t impact the log chain.
Also, simply opening an HTTP ENDPOINT using the VM Dashboard on Windows Azure we could decide to expose the report server to external users
In the following image you can see that I am connecting from my laptop to the reporting server available in Azure, and a very basic report is being executed to display data in the tUsers table
AlwaysOn Add Azure Replica Wizard
SQL Server 2014 includes the possibility to add an AlwaysOn replica on Azure using a Wizard that hides much of the complexity under the hoods, which I think is a great feature especially for DBAs that are not very familiar with Windows Azure Platform / geo cluster / networking / etc.
the last step of the Wizard allows to specify all the details of the new AG node, like the image template, the size (number of cores etc.), VM name, subnet (HybridNet in our case) etc.
Conclusions and benefits:
I see lots of benefits that this solution could provide, like: