SQL Database Mirroring to the Cloud - 20 Key Scenarios with Windows Azure Infrastructure Services

“Hey Kevin… I would like to use Windows Azure as a location where I set up a SQL Server database mirror. Is this something I can do?”

Absolutely, yes!  In fact, it doesn’t even require anything fancy as far as setting up special connectors or site-to-site VPN in order to try this out (though in production you should consider a more secured connection).

As a really great tutorial and example, I point you to this TechNet resource: Tutorial: Database Mirroring for Disaster Recovery in Hybrid IT

This tutorial shows you how to implement SQL Server database mirroring for disaster recovery end-to-end in a hybrid IT environment. In this configuration, the principal database server runs on-premise and the mirror database server runs in Windows Azure. You can implement this scenario without a VPN connection between Windows Azure and your on-premise network if you use server certificates. Furthermore, it is possible for your principal database server to run behind a NAT device on-premise if you forward the appropriate ports on your NAT device to the server.

To try this out, I created a new Windows Server 2012 (using the free evaluation) server as a Hyper-V virtual machine named SQLOnPrem, and had a downloaded .ISO of the SQL Server 2012 evaluation installation connected to it (in the virtual DVD drive).  I didn’t even have to manually install SQL on that box, since the tutorial actually gives you the scripts to do all the heavy lifting.  And as long as you can redirect port 5022 through your NAT/firewall to your local VM, it’s really easy to set up.

I also used my Windows Azure subscription.
HINT: If you need one of those, you should get the FREE Trial.

The tutorial provides PowerShell code to build the Windows Azure based virtual machine, which is an evaluation SQL Server 2012 running on Windows Server 2008 R2.  Note however that, like the SQL Server AlwaysOn High Availability tutorial I discussed and used last week, the PowerShell code in the tutorial is a little out-of-date and won’t work as provided.  In particular the PowerShell command for the creation of the Azure-based “SQLinCloud” server should be:

New-AzureVMConfig `
-Name $sqlServerName `
-InstanceSize Large `
-ImageName $sqlImageName.ImageName `
-MediaLocation "$storageAccountContainer$sqlServerName.vhd" `
-DiskLabel "OS" |
Add-AzureProvisioningConfig `
-Windows `
-AdminUserName $vmAdminUser `
-Password $vmAdminPassword `
-DisableAutomaticUpdates ` |
Set-AzureSubnet `
-SubnetNames $subnetName |
Add-AzureEndpoint `
-Name "SQL" `
-Protocol "tcp" `
-PublicPort 1433 `
-LocalPort 1433 |
Add-AzureEndpoint `
-Name "SQLMirroring" `
-Protocol "tcp" `
-PublicPort 5022 `
-LocalPort 5022 |
New-AzureVM `
-ServiceName $serviceName `
–AffinityGroup $affinityGroupName `
-VNetName $virtualNetworkName

Notice how this is essentially one-line of PowerShell that creates a VM in a specific Affinity Group, creates the Azure Service, connects the Machine to your Azure Network and subnet, sets up two TCP endpoints!  Amazing!

Another fix needed to be made in the script to configure the firewall on the SQLonPrem machine (your local SQL Server).  It should read:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$wmi = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $env:COMPUTERNAME
$wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp'].IsEnabled = "True"
$wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp'].Alter()
$svc = Get-Service -Name 'MSSQLSERVER'
$timeout = New-Object System.TimeSpan -ArgumentList 0, 0, 30
$svc.Stop();
$svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
$svc.Start();
$svc.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)

(The highlight is what I had to correct.)

I’ve put my copy of the corrected PowerShell that I used to create the Windows Azure VM up on SkyDrive for you.  GET IT HERE.

Other than that, it works great, and is a very useful tutorial.  In the end you’ll have a database installed locally, and a mirror up in your server running under Windows Azure.

"Oh yeah? Prove it?”

(sigh)  Okay… here’s my local SQL Server…

…and here’s my SQL Server in the cloud.

For those of you not familiar with SQL Server Database Mirroring, I recommend you look at this page: Database Mirroring (SQL Server). 
Important note: Database Mirroring is a feature that is being phased out in favor of the newer SQL Server 2012 AlwaysOn Availability Groups for the purpose of High Availability (See my blog post HERE).  But.. if you want to mirror, you can.  And now you can mirror to the cloud.

---

What do you think? Are you considering using the cloud and Windows Azure as part of your storage and disaster recovery solutions? Give us your comments or questions!