Murilo Miranda

SQL Server + Azure: Introduction to Hybrid scenarios

July 1, 2015 by

We hear a lot about hybrid SQL Server environments, but how to integrate my datacenter with Azure? Check this article and you will have a simple and effective explanation about the connectivity option!

It is being very common to hear about cloud in the last months (or even years). We can see huge companies like Amazon, Google and Microsoft heavily investing in infrastructure and new cloud-based services.

In fact, the cloud is being more and more part of our lives. Probably you didn’t even noticed that! But just look back, maybe two or three years ago… Almost everyone used to have a “pendrive” in a corner of their pockets. I am including myself into this account, by the way!

Maybe the story has changed. Looking for nowadays, you can still find your good old pendrive, collecting dust somewhere in your house, and not being used as former times.

But what happened? Just look at some services as Dropbox, Google Drive or OneDrive:

  • You can store your files.
  • You can access it from everywhere where you have internet connection.
  • If you delete your files you will be able to recover it easily.
  • The space constraint is a matter of upgrade your plan.
  • … and more!

We can also refer that in a world where a single person may have different devices as a cellphone, a tablet, one work PC and a personal PC, those services are also useful in order to keep your files up to date and accessible without effort. And in some case you also have a recoverable list of versions!!

Well, the pendrive example is the one that I most identify myself, because I started to use it without even know that I was entering in the Cloud era. And this would be useful even to SQL Server! You can easily find articles explaining how to take advantage of one of those services to store… database backups!

In a broader look, we can see that the cloud brought benefits and options to also improve high availability and disaster recovery strategies, making those architectures more economic, scalable, and elastic.

As we will be talking about SQL Server, nothing better than talk about Azure, where I already lost track of the tremendous number of new services and improvements that Microsoft is constantly announcing. Yes, they are heavily investing on Azure, this came to stay!

The hybrid cloud

The objective of this article is start a series of technical guides on how to take advantage of the Azure integration with SQL Server, and in order to reach this objective, we will be focusing in two specific service stacks of Azure:

  • Data Services

  • Network & Automation Services

By playing with both of those together, we will be able to build a complete solution and integrate on-premises with the cloud. So let’s start talking about network…

How to integrate on-premises and cloud?

In order to integrate your datacenter (on-premises) and Azure, Microsoft offers some options, which have different levels of performance, complexity and may or not fit with your needs. The following picture shows a summary of this:

Connectivity over the internet

This is targeted to consumers. For example, if you have a service, which you need to connect from everywhere, and want to connect by using a workstation (using a browser, for example) or some tool that will provide you access if you have all the credentials to. Typically, services that are using a public IP or more specific cases where you need to explicitly open a port on an Azure Portal, like connect to an Azure Virtual Machine, by using the Remote Desktop Protocol (RDP).

Point-to-Site (P2S) – IPSec VPN

Here we are in another level of connectivity. The Point-to-site VPN, or just P2S requires a more complex deployment. The P2S VPN is used to connect a single workstation to a range of service in Azure, that are based in a specific subnet.

This is a one way of connection, based in an IPSec VPN that you need to configure in the computer that is going to be connected to an Azure vNet. This way a point (the computer) connects to a site (Azure). This strategy also allows the user to connect to a service in Azure, like a Virtual Machine or a Database.

I wrote an article on how to configure a P2S VPN, step-by-step. Just follow this link if you are interested.

Site-to-Site (S2S) – IPSec VPN

The site-to-site (S2S) IPSec VPN, is a kind of P2S VPN, but with a difference that changes the game: You are now connecting your network to Azure. We can call this a many-many connectivity (I don’t know if this exists, but the idea is make it simple for the DB professionals :)), as we are connecting the on-premises network to an Azure virtual network.

This VPN is configured in the network layer, differently from the P2S, where you need to setup a soft VPN in a computer. However, there is a way to do this from the application layer, using the Routing and Remote Access Service (RRAS), from Windows.

The S2S VPN allows us to create more complex strategies, as we will be permanently connecting our servers with Azure infrastructure and services. So we can use it to an Active Directory replica, extend the AlwaysOn Availability Groups strategy, etc…

If you want to check on how to create a S2S VPN using RRAS, I have an article published here that explains all the steps.


This is an option to the site-to-site VPN. But what is the difference?

The main difference here is that using ExpressRoute you will be using a private network and, of course, you will need to pay for this 🙂

What are the advantages? Why would I pay for a service that I already have for free?

Basically by using ExpressRoute, you will be using a private network, avoid the data transfer in the internet. This means, that you will have a more secure, controlled, and a better connectivity quality if you use ExpressRoute.

As this is the best option, you will need to pay for the exclusivity and quality of this service. But not for Microsoft! You need to find a service provider in your region.

Microsoft already has some partners, around the globe. So, if you have this need (I think everyone has, but the budget is the constraint here…) check the current service providers in order to get a quote.

With the ExpressRoute, we can follow different topologies, as you can see:

In terms of connectivity, this is the “icing on the cake”, but you will need to pay the price…


In this article we saw how we can integrate our environment with Azure. We have different options, with different levels of complexity, depending of our needs.
Now that we understood the basics, we are ready to go beyond! Keep tuned for the next articles, where I will cover all the possible options that we have to integrate a SQL Server instance with Azure!

Murilo Miranda
SQL Azure

About Murilo Miranda

Murilo Miranda is a Luso-Brazilian blogger and speaker. SQL Server MVP, living in the UK. Nowadays he's Database Consultant at Pythian, company based in Ottawa - Canada. With experience working in Portugal, Holland, Germany and United Kingdom, he's always available to learn and share his knowledge, in order to contribute to SQL Server community, View all posts by Murilo Miranda