How to setup an hybrid scenario with SQL Server and Windows Azure Point-to-Site VPN

  • Article

Windows Azure allows you to connect your on-premises network to Virtual Network using a Site-to-Site VPN.
Now you can also connect a single client machine , without configuration of your company firewall.

This new feature is called Point-to-Site VPN: remote client machines can be connected to Virtual Network in Azure as shown in the next image:

 

In this post, I show you an example of Point-to-Site VPN configuration: we setup Log Shipping connecting an on-premises SQL Server's instance (configured on my laptop) and a SQL Server hosted on Windows Azure.

 

To complete this task, we need the following:

  • - A Windows Azure virtual network, used for connecting virtual machines hosted on the cloud;
    - A virtual gateway on Windows Azure: this will manage the clients' connections;
    - A client-type certificate

1-Virtual Network and Virtual Gateway Creation on Windows Azure

This step is very easy to complete using Management Portal:

image

 

1

In the wizard, enter the address space you want to use for connecting your client machines to servers on Azure by VPN

image

 

image

After creating your Windows Azure Virtual Network, configure the Gateway:

image.

this task takes about 10 minutes.

image

2-Client Certificate creation

From the Virtual Network Dashboard, we need to create a self-signed client certificate

image

that will be used by your client machines for VPN connection.
To do this, we can use "makecert" utility, available in Windows SDK .

First, we create a root certificate (you will upload it to the Windows Azure Management Portal) executing the following command:

makecert -sky exchange -r -n "CN=<RootName>" –pe -a sha1 -len 2048 -ss My

After the root certificate, we create the client certificate (remember to upload it to your machines) executing the command:

makecert.exe -n "CN=<CertName>" -pe -sky exchange -m 96 -ss My -in "<RootName>" -is my -a sha1

3-On-Premises SQL Server configuration

I have installed a new server: this server, named FRANCEDLOCAL, hosts a SQL Server 2012 instance.

First, we upload the client certificate, image generated in the previous step, and download from the Virtual Network Dashboard the VPN package (available for 32-bit and 64-bit clients).

imageimage

imageimage

At this time, on-premises SQL Server is ready to communicate with Windows Azure via VPN. Now we have to add it to a Windows domain so go on with the next step.

4-Windows Azure configuration - Domain Controller and SQL Server installation

Windows Azure allows you to create custom virtual machines and provides some built-in templates: SQL Server 2012 is one of them.

For more details about pricing, please refer to this page and to the Windows Azure calculator.

 

image

During the virtual machine creation wizard, you can connect the VMs to the virtual network we have created before.

image

image

 

imageimage

I have created a Windows domain named "francedsqlconf.it" with one domain controller named "FRANCEDPTSDC" and a SQL Server named "FRANCEDPTOSITE" hosted on the cloud.

image

image

image(FRANCEDLOCAL)

 

5-LOG Shipping Configuration

Now the hybrid network configuration is operational: it is time to configure Log Shipping in SQL Server.
The primary database is "AdventureWorks" (you know it, right?! Sorriso ) and it is hosted on FRANCEDLOCAL and the secondary database will be based on FRANCEDPTOSITE.
On FRANCEDPTSDC, we create a shared folder named "LogBackup" for storing files related to T-Log backups.
On FRANCEDPTOSITE, we create another shared folder named "RestoreLog" as repository for files related to "copy\restore" of T-Log.

image

image

For this scenario, I suggest you to set Backup Compression to "compress backup" (this feature is availabe also in SQL 2012 Standard edition).

image

 

image

image

image

 

Francesco