SQL Server 2014 CTP2 AlwaysOn Availability Groups Hybrid Cloud scenario using Windows Azure

  • Article

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.

image

On-premises

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 https://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:

  1. WPC2013DC: Windows Server 2012 R2, Domain Controller, DNS Server
  2. FRANCEDWPC2013: Windows Server 2012 R2, Domain Member, Failover Cluster Feature, SQL Server 2014 CTP2 (AlwaysOn Node1 Primary [sync mode])
  3. WPC2013ND: Windows Server 2012 R2, Domain Member, Failover Cluster Feature, SQL Server 2014 CTP2 (AlwaysOn Node2 Secondary [sync mode])

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.

image

Windows Azure

  1. A Virtual Network that I will use to place Virtual Machines in Azure. You can read a tutorial on how to configure Virtual Networks here: https://www.windowsazure.com/en-us/manage/services/networking/create-a-virtual-network/
    1. My Virtual Network is called HybridNet and it is able to communicate with my on-premises network through a Virtual Gateway. Local Network name’s OnPremNet. Network configuration, Gateway and VPN are the first things you need to take care to setup an Hybrid environment with Windows Azure
  2. HYBRIDDC. A virtual machine running in Windows Azure with the following roles: Domain Controller and DNS Server of the same domain/forest configured in the local network
  3. SQLCTP2: A virtual machine with the following services enabled: Domain Member, Failover Cluster Feature, SQL Server 2014 CTP2 (AlwaysOn Node3 Secondary [async mode, readable]), Reporting Services

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.

image

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)

image

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

image

image

image

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)

image

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

image

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.

image

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.

image

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)

image

imageimage

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.

image

SQLCTP2 is also a Readable Secondary, so we can offload some workloads, like a report server or the backup operations.

image

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)

image

image

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.

image

Also, simply opening an HTTP ENDPOINT using the VM Dashboard on Windows Azure we could decide to expose the report server to external users

image

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

image

image

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.

image

image

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.

image

Conclusions and benefits:

I see lots of benefits that this solution could provide, like:

  1. Simplicity: this solution seems quite complex but it is actually very easy to configure, for two main reasons:
    1. Windows Azure network configuration is easy, and from the Windows Azure Virtual Network dashboard you can download a ready-to-use script for your on-premises VPN gateway
    2. SQL Server 2014 adds the possibility to setup an Azure secondary AlwaysOn node in Windows Azure without the need to have an advanced knowledge of the Windows Azure Platform. This is possible thanks to the Add Azure Secondary Wizard process, which hides much of the complexity to the DBA.
  2. Local High Availability with zero data loss using a secondary synchronous AlwaysOn Server
  3. Disaster recovery site with a manual failover using a secondary asynchronous AlwaysOn Server
  4. Remote backups. I could use the remote server in Azure to take backups to data disks stored in Windows Azure Storage which has built-in high availability features. To learn more about Windows Azure storage you can visit this page: https://www.windowsazure.com/en-us/develop/net/fundamentals/cloud-storage-scenarios/
  5. Reporting: creating reporting solutions using a server in Windows Azure could give the benefit to offload some workload to a server which is not heavily utilized. Also, you could decide to expose that web server on the web for external users that need to visualize some of the corporate data.

Francesco

@francedit