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

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

  • Comments 2
  • Likes

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

    Your comment has been posted.   Close
    Thank you, your comment requires moderation so it may take a while to appear.   Close
    Leave a Comment
    • Hi Francesco,
      I tried to create mirroring in Point to Site set up, for that I am unable to add the local machine into domain. In the above setup you created did you set up Logshiping having both boxes from windows Azure portal or did you configure Logshiping between a remote machine out of cloud and a machine in cloud?


    • Hi, sorry for the delay, for some reason I haven't received the comment notification. I am pretty sure (it was a year ago :) ) that one of the machines was on my local laptop. Check dns and routing in local machine to be sure you can add the cloud server to the domain (e.g. try a telnet on port 53 from azure to on-prem to see if dns communication is working) HTH