Configuring SQL Server Database Mirroring across two Windows Azure datacenters using certificates

Artigo
02/05/2014

In this article, we will explore a SQL Server high availability and disaster recovery scenario with SQL Server Database Mirroring configured across two different Windows Azure datacenters, one in Western Europe and one in West US. Currently, a limitation in Windows Azure virtual networks does not allow an Active Directory domain to span across two different regions because the internal communication between virtual networks is not possible. Fortunately, SQL Server provides an out of the box option to setup a Database Mirroring session using certificates, without the requirement of an Active Directory domain. There are a couple of important considerations on using Database Mirroring for this purpose:

Database Mirroring is a technology that will not be supported in future SQL Server versions. To read about deprecated and not supported features of SQL Server you can visit this page: https://technet.microsoft.com/en-us/library/ms143729.aspx
Database Mirroring is limited to two replicas, a principal and a mirror database. Therefore, in case of a geo-replicated scenario in asynchronous mode, you can only have one database on the primary datacenter, exposing your database to the risk of potential data loss in case of a disaster. To mitigate the impact of such a solution, you could combine a Log Shipping configuration on top of Database Mirroring on the first site.

An alternative to work around the limitations shown above is to implement a custom solution, but we won’t talk about that in this post. Let’s go back to the post’s topic:

The image below describes the scenario that we are going to configure in this tutorial. In our example, we will place a Virtual Machine in a Western Europe datacenter (called FRANCEDWEVM, with an instance that hosts a database called MIRRORINGDB acting as a principal) and one Virtual Machine in West US datacenter (called FRANCEDUSVM, with an instance that hosts the mirror database). We will utilize Database Mirroring in High Performance mode, which replicates data asynchronously, to avoid the performance impact that a synchronous replica will generate. Please note that High Performance mode is only available in the Enterprise Edition of SQL Server. Visit this page for details about Database Mirroring operating modes.

What do you need to test this scenario?

A Windows Azure subscription is sufficient. If you don’t have one, you can activate a 30 days trial here. There are also free Windows Azure resources available for Partners and owners of MSDN contracts: https://www.windowsazure.com/en-us/pricing/member-offers/

STEP ONE – Virtual Networks configuration

First thing to do is creating two Windows Azure Virtual Networks, one placed in Western Europe and one in West US. Those networks will host our SQL Server virtual machines.

The management portal has a quick create wizard that will guide us in the virtual networks configuration.

We have the Virtual Network called francedwe, placed in Western Europe

and the Virtual Network called francedus, placed in West US

This step will take a couple of minutes to complete.

STEP TWO – Virtual Machines provisioning

Windows Azure has a very rich Gallery that includes also SQL Server images. In our example, we will utilize the image with SQL Server 2014 CTP2 installed on Windows Server 2012 R2.

We just need to choose the name of the VM, the size in terms of CPU and memory, user name and password

Then we need to specify the network name (francedwe and francedus, correspondingly to francedwevm and francedvmus) and the cloud service name. The FQDN of each VM has to be unique, and in this scenario this is particularly crucial as we will use it to setup database mirroring. Windows Azure adds the cloudapp.net suffix to cloud services, so the result for our VMs will be:

francedwevm.cloudapp.net – VM hosted in a Western Europe datacenter

francedusvm.cloudapp.net – VM hosted in a West US datacenter

The provisioning of each VM will take approximately 5/10 minutes.

For each VM we need to expose two TCP ports, one for the SQL Server service (we will use default 1433 TCP port), and one for the mirroring endpoints (we will use TCP 5022). In Windows Azure, we have the concept of endpoints, which give us the possibility to open TCP or UDP ports on the WEB. As we mentioned before, we don’t have a direct communication between Windows Azure virtual network, so we need to use public endpoints to allow this.

The images below show two endpoints:

MSSQL – TCP 1433 used for SQL Server service
MIRRORING – TCP 5022 used for Database Mirroring

To enhance security, we can set Access Control Lists (ACL) for each endpoint. In our case, endpoints for francedwevm will allow connectivity for francedusvm Public Virtual IP (VIP) and vice versa. This part is optional but, for obvious reasons, recommended. Every endpoint can have different ACLs, so we need to configure them for both MSSQL and MIRRORING endpoints, and in both Virtual Machines.

Every Cloud Service has a VIP associated, and the Cloud Service Dashboard displays it

STEP THREE – Preparing SQL Server services

On both VMs, we need to setup the Windows Firewall to listen on the ports we need for Database Mirroring (TCP 1433, 5022)

Then, we need to change the SQL Server authentication method to SQL Authentication (this requires a SQL Server restart)

STEP FOUR – Configuring Database Mirroring between two Windows Azure datacenters

Database Mirroring requires, for both instances, to configure security for outbound and inbound connections. We are using the configuration with certificates; therefore, we need to create a master key and a certificate on each server. Then we need to save both certificates on each virtual machine, as SQL Logins will use them for the authentication. In this tutorial, each server has a C:\Temp folder for this purpose.

We can query the sys.database_mirroring_endpoints to check if the endpoint has been created correctly.

Now we need to solve a small issue: we need to copy certificate files between the two VMs, but we know that there is no communication between the Virtual Networks. We could add a data disk to the first Virtual Machine, save the certificate there and then move the data disk to second storage account in order to attach it to the secondo VM. This is a very time consuming task, so why don’t just upload the certificate in the Windows Azure storage, using a private container? To accomplish this, we can utilize one of the clients available on the web, like AzureStorageExplorer. To access a Windows Storage Account you need the storage account name and its storage key, and you can get it from the Management Portal, through this button.

----------------------------------------------------------------------------------------------------------------

NOTE : For those of you that prefer playing with code, I have created a sample .NET Console Application to interact with Windows Azure storage, and you can use it instead of AzureStorageExplorer. You can download it from here. Uncomment the following part and insert your Azure Storage Account credentials

----------------------------------------------------------------------------------------------------------------

Now that both certificates reside on the C:\Temp folder of each server, we can proceed with the configuration of the inbound connections. We also need a SQL login to accomplish this part of the setup. Again, execute this step on both servers.

Great, we are now ready to complete the mirroring session setup. Next task is to backup our database in the principal server and restore it on the mirror and then set the mirroring session. We need to find a way to move backup files from the principal to the mirror. Here SQL Server can give us a great help, because it allows the backup of a database to a URL represented by a Windows Azure storage account. If our instance is running SQL Server 2012 SP1 CU2 or above we can do it using T-SQL, and for older versions of SQL Server we can install a client tool to do that. To download it, visit this page. In our example, we are using SQL 2014 CTP2, so this part can be done with TSQL.

-------------------------------------------------------------------------------------------------------------------------

NOTE : to understand more about Backup to Azure features in SQL Server, you can visit the SQL Server Team blog. I have also written an article a few months ago.

-------------------------------------------------------------------------------------------------------------------------

To interact with Windows Azure storage we need to create a credential object, specifying storage account name and storage key. Then, we will take a full backup and a log backup, using a storage container as a destination.

Let’s fly again to the US :), to connect to the mirror server instance. There, we need to create a credential object, and then issue a restore command using the windows azure storage account.

Now we have the database restored (in NORECOVERY mode) in the remote datacenter, and the final step is to set the mirroring session

To do that, we need to issue the following TSQL command in the mirror server

and the same command has to be run in the principal server. We are using the public FQDN of the Cloud Services.

Wow, our mirroring session is now operational! :)

Please note one important thing: the Database Mirroring Monitor tool won’t work correctly if you connect to the local instance as it will try to resolve the NetBIOS name (see image below)

The workaround for this is to connect to the remote instance using the FQDN ([servername].cloudapp.net) and launching the monitor from there

STEP FIVE – Simulate a Failover

Using the SET PARTNER FAILOVER option of the ALTER DATABASE statement, we can trigger a manual failover. The image below shows that now the principal is now FRANCEDUSVM

One last important thing: don’t forget to set HIGH PERFORMANCE mode, otherwise your database performance will be bad :) But remember, data loss could occur using this setting.