In-Depth

Moving Your SQL Infrastructure to Microsoft Azure

Putting your databases in the public cloud might be scary, but the rewards are substantial. Here's how this fictional company does it.

• By Paul Schnackenburg
• 08/24/2015

Just Another Big Business (JABB) Systems International is a fictional, 2,400-employee business with global offices. It manufactures security equipment, such as surveillance systems, alarms, bulletproof glass and cars, safes and related technologies. Its IT team of 15 is responsible for the day-to-day running of its datacenters, as well as improving the business through innovative use of technology.

They have two datacenters: New York and Los Angeles, which is in a co-location facility. The environment is mostly Windows Server virtual machines (VMs), with about 60 percent running on a VMware cluster, another 30 percent running on a newer Hyper-V cluster, and the rest running on older, physical servers. There are quite a few Linux servers as well as two main, in-house-developed line-of-business (LOB) applications. The IT team currently finds itself in a challenging situation, with aging hardware in both datacenters and a shrinking budget.

JABB is migrating from its current platforms to a hybrid/mostly public cloud-based infrastructure, focused on Microsoft Azure. That means moving SQL to the cloud. It's not an easy job, but JABB is doing it the right way.

Grabbing the Right Tools
There are two excellent tools that'll help with assessing the current state of the JABB infrastructure, as well as giving guidance for migrating to Azure. The first is the Microsoft Assessment and Planning Toolkit (MAP), which provides insight not only for Azure migrations, but also on-premises consolidation with Hyper-V, for instance, or Office 2013/Office 365 and SQL migrations.

The Azure Virtual Machine Readiness Assessment Tool, on the other hand, is specific to Azure and provides detailed analysis of VMs, suggests VM sizes and flags potential issues with on-premises configurations. For testing hybrid cloud implementations, there's an excellent guide available here.

Moving VMs to the Cloud
A common term is "lift and shift," in which workload VMs are simply moved from on-premises hosts to Infrastructure-as-a-Service (IaaS) providers with minimal reconfiguration. For that scenario, the Azure go-to service is Azure Site Recovery (ASR), which offers both migration and disaster recovery (DR) in one toolbox, which is shown in Figure 1. ASR supports several scenarios: Hyper-V-to-Azure replication, VMware or physical servers-to-Azure replication, one VMware site-to-another VMware site orchestration, System Center Virtual Machine Manager (VMM)-to-Azure replication, and one VMM site-to-another VMM site orchestration. The first stop for JABB is VMware-to-Azure migrations.

The issue with moving VMs to any cloud is the necessary downtime when the VM is powered off on-premises and then uploaded to the cloud. Depending on the size of the data housed in the VM, this could be hours or even days.

ASR (which is based on the acquisition of InMage in mid-2014), lets you keep systems up and running on-premises, while replicating the disk writes to Azure. After the virtual disks are in sync in both places, the downtime for the switchover is minimal. ASR is currently in preview (beta), and it takes a few days for Microsoft to approve a request for a test drive.

The data can be replicated directly over the Internet, through a Site-to-Site VPN Azure link or through ExpressRoute. No inbound ports need to be opened in the JABB firewalls. The source can be VMware VMs (Windows or Linux), physical servers or even VMs running in another Azure region.

Azure Site Recovery
ASR uses a process server running Windows Server 2012 R2 (as a VM or physical install) to do the caching, encryption and compression of the data on-premises. Azure has a configuration server running as a standard A3 size, as well as a master target (MT) server, which has virtual disks attached to it, running either Windows or Linux, depending on the source servers being protected.

The replication target of the MT is either a standard A4, which offers up to 15 disks, or a D14, which provides up to 31 disks. The D14 is only used if a VM hash more than 15 disks attached to it. Each server to protect then needs the Mobility service installed, which can either be pushed out from the process server or installed manually. The beauty of ASR is that, because these are just attached virtual disks in Azure, you're not paying for dormant VMs; the cost is only incurred when you actually failover, thus creating VMs.

For each application workload, you create a protection group that logically groups VMs together, as well as let you define the order in which they should spin up in Azure. JABB will use ASR to move VMs from their existing VMware platform to Azure as business needs dictate.

For Hyper-V workloads (JABB uses both VMware and Hyper-V), ASR lets you replicate VMs to Azure using the built-in Hyper-V replica functionality, either using VMM or standalone Hyper-V hosts. Again, this can be used as a way to migrate workloads to the cloud or for DR preparation. This service (shown in Figure 2) can also orches­trate replication between two on-premises datacenters, in which case the actual VM data is never stored in Azure. Failover is managed using recovery plans where the order of VM startup can be controlled, along with any manual steps or scripts required for a successful failover.

These services will markedly improve the DR readiness of JABB after implementation, with all business-critical VMs (and, based on how cost effective it is, perhaps even not-so-critical workloads) stored in the cloud, ready for action, in case one of its own datacenters isn't available. Note that while technology is a key part of DR, there are many other elements to successful DR preparedness, involving the whole business and testing.

Once you have VMs in the cloud, don't forget that you can control their configuration and behavior using Windows PowerShell Desired State Configuration.

Moving Workloads to the Cloud
Moving VMs to public clouds using lift and shift is relatively easy, but many proponents of public cloud infrastructure advise against this. If all you do is move VMs from on-premises to a public cloud, you still have all the responsibility for protecting those VMs against malware and hackers, keeping them patched, as well as managing and monitoring the OS and the application. In other words, your only gain is not having to pay for the hardware up front; instead, you defer that to a monthly bill.

JABB takes a look at some of their LOB applications to see how they can improve this situation. Because Azure offers both IaaS and Platform-as-a-Service (PaaS) services -- unlike Amazon Web Services (AWS) -- there's a clear opportunity to transform some applications for additional advantages.

Some of the JABB applications are traditional three-tier services. Instead of deploying VMs as front-ends with IIS, an application can take advantage of Web Roles, a part of cloud services. This gives you fully functioning VMs, with IIS preconfigured, without the hassle of having to manage those VMs. Azure will periodically update and re-create these VMs, but that's all managed by the platform; as long as you have several of these for load balancing, it should be transparent to users of the application.

The back-end database in many LOB applications is SQL Server, which can be deployed in Azure in two ways. You can certainly have one or more IaaS VMs with SQL Server (templates with SQL preinstalled), but this again leaves the tasks of protection, backup and patching in in the hands of JABB. If JABB needs to do this for some SQL Servers, there's an excellent document detailing exactly how to configure VMs and SQL for maximum performance.

Alternatively, there's Azure SQL Database, which is a PaaS service that provides databases, as shown in Figure 3. Microsoft manages the underlying fabric, OS and SQL Server itself, with a 99.99 percent service-level agreement (SLA). It comes in Basic, Standard and Premium tiers; unlike the Standard and Enterprise versions, on-premises SQL includes all features at all tiers.

The difference is in the amount of resources assigned to your databases, measured in Database Throughput Units (DTU). When you set up databases, they're housed in a logical container called a server, with common firewall settings for all databases. The service offers Transparent Data Encryption (TDE) and row-level security, while integrating with the new Key Vault service for storing encryption keys securely. Depending on your tier, backup is built in for a certain number of days; your databases can also be geo-replicated.

Database Sizing
JABB database admins want to figure out exactly how to size their databases, and which tier and performance level to configure. It's similar to how you do it on-premises when sizing a new server. But with Azure SQL Database it's generally easier to deploy a test workload and measure performance, because scaling up or down is easy.

Once you have more than a handful of databases, managing the sizing up and down manually becomes a chore (not to mention cloud-contrary; remember, it's all about automation) and Elastic Database Pools becomes attractive. You can group many databases (in the current preview, the max is 100) and set a performance level for the whole pool. Each individual database in the pool can then scale up and down as the load fluctuates, and JABB will have more predictable budgeting for LOB database services. Today the maximum size of an Azure SQL Database is 500GB, which could be a limitation for some applications. SQL Server 2014 Management Studio has built-in functionality to assist in upgrading to both Azure SQL Database and moving an on-premises workload onto Azure IaaS VMs. Cumulative Update 5 for SQL Server 2014 provides parity with the newest version 12 of the Azure PaaS service. The wizard analyzes your on-premises database and flags any features that aren't compatible with the PaaS service. It's also worth mentioning that SQL Always On can store copies of your databases on-premises and a copy in Azure for failover.

So, with a bit of development work, many of the company's custom applications are now using PaaS Web Role front-ends and PaaS SQL Server databases as the back-end, leaving the logic in the middle tier to be housed in IaaS VMs.

Don't forget ancillary services; perhaps data from one or more LOB applications are stored in data warehouse VMs today for analysis, instead of moving those wholesale to the cloud. When the time comes, JABB will look to employ other services, such as Azure Stream Analytics/Power BI, which would be just as capable and more cost-effective.

Shifting IT, Shifting Roles
When JABB finishes the process, it has to decide how the IT department will change. Its role might shift to that of a services provider or services broker. This is necessary because otherwise savvy business users will simply bypass IT and order the services themselves from any of the myriad cloud providers online today (the concept of "Shadow IT").

The company will have to shift to managing services, not assets. Imagine, for instance, trying to keep a Configuration Management Database (CMDB) as required by ITIL up-to-date with servers and applications being spun up and destroyed in the cloud on a daily basis?

IT will also need to tear down the walls between the networking, database, application and development teams. A layered approach works in an on-premises world where the responsibilities can be clearly mapped to the underlying hardware and software, but not in the public cloud world. New roles will come to light, such as the Author who creates templates, defines the service catalog and manages the lifecycle of cloud assets. And, of course, automation will become a cornerstone in every aspect of day-to-day operations. An excellent guide to the operational maturity required to operate a cloud (whether private, hybrid or public) is the Cloud Services Foundation Reference Architecture.

Billing Questions
A note on billing is also prudent. JABB will need to learn how to manage sometimes unpredictable IT spending in the cloud. The usage file that you can download for each subscription has recently been changed, with added columns making detailed analysis easier.

Azure is a rapidly morphing beast, and JABB will have its work cut out for it, to figure out which Azure services to take advantage of as they expand their hybrid cloud. One thing is for certain, though: Taking the steps outlined here will provide it with a more agile IT infrastructure that's able to respond faster to changing business needs. And that agility (apart from cost effectiveness) is the true reason for any business today to move to hybrid and public cloud.