Nov 03, 2014

You know that virtual machine you’re using that’s hosted on Windows Server 2012 R2 or System Center 2012 R2? Well, that same virtualisation technology is now available to you through Microsoft Azure. It’s now quite easy to imagine some hybrid cloud infrastructure where you have both on-premises servers and hosted servers. It’s also easy to move your virtual machines from your private cloud to the Microsoft Azure public cloud. Awesome! So let me be a little more specific about the benefits to you. You can easily migrate existing applications. That is, you can simply duplicate your on-premises server configurations in a Microsoft Azure virtual machine and migrate the application to it. There’s built-in resilience. The virtual machines are based on VHD files that are stored in Microsoft Azure blob storage so your files are protected from hardware failure because of the replicated file system capabilities of Microsoft Azure. And there are a number of ways for you to provision new virtual machines, from pre-defined templates to uploading your own images. Okay, so what I’m telling you here is that there’s an IaaS (Infrastructue as a Service) solution in addition to the PaaS (Platform as a Service) solution you’ve already heard about. The valuable thing about it is that it’s much more flexible, with an itsy-bitsy downside… there’s a little more administrative effort on your part, both configuration and management. So if you’ve already virtualised your SQL Server databases, you’re most of the way there. You’ll need to upload the VHD to Microsoft Azure blob storage. There’s even a nifty (command line) tool to help you out: CSUpload. One little thing though… the VHDs in Microsoft Azure must be in fixed size format. Fortunately, CSUpload will convert your VHD to a fixed sized disk format for you if required. Okay, so what do you do if you haven’t virtualised your SQL Server databases? Really? It’s 2014 dude! Alright, let’s say you haven’t. Well, you can create a virtual machine based on a template. Microsoft Azure has a gallery of pre-configured images for you to use to create a virtual machine. I just checked and there’s 59 of them right now. They range from a BizTalk Server 2013 Enterprise to Microsoft Dynamics NAV 2013 R2 to SQL Server 2014 RTM Enterprise with a number of Ubuntu, Centos, SUSE and Oracle servers thrown in for good measure. Or you can create your own image by using Windows Sysprep to generalise the operating system. You can also install SQL Server by using the prepared instance feature of SQL Server setup (did you know about that?). Once you’ve created your VHD you can upload it to Microsoft Azure and use the Microsoft Azure management portal to capture it as an image. Voila! Remember the CSUpload from before? Yep, that’s it. Alrighty then… so you now have a virtual machine in Microsoft Azure, running SQL Server 2012 or 2014…how much different is it to create a database than it is now on-premises? Not much is the short answer. Long answer? Here goes. Because your SQL Server instance is in a hosted environment there are a couple of considerations. Firstly, storage. Your virtual machine will have a C: and a D: drive. Don’t put your database files on D:. This is only intended as temporary data space and is not persisted when your virtual machine is deactivated. You can place your data and log files on C: but it’s generally only suitable for small, relatively inactive databases. If your databases are larger than 10GB with moderate to high I/O you should add data disks to your virtual machine and use them for your data and log files. Now if you want the best performance for your high I/O databases, you might consider adding at least three data disks. One for the log files and the other two for data. Create data files on them and combine them in a filegroup. Once you create tables in the filegroup, SQL Server will stripe the data across the disks. This has been demonstrated to improve I/O performance. The most common scenario is to migrate existing on-premises databases to your virtual machine in Microsoft Azure. There are a number of business and technical implications you should consider. The following factors are of primary importance:

• Cost: This is often the principal consideration when migrating. It must include the cost of compute and storage as well as licensing. If you’re transferring an existing on-premises SQL Server instance, you can equally transfer your existing license through the Software Assurance License Mobility offering. If you’re creating a new instance, you’ll be charged by the hour for the image, which includes the necessary software licenses.
• Technical: Consider your requirements, including network connectivity, Active Directory design and other technical aspects of your infrastructure. Having a thorough understanding of your technical requirements makes for a smoother migration.
• Compliance and Security: This is a big one. What are the government, industry or corporate policies for data storage. In the EU, for example, customer data must not be physically stored outside the EU.
• Timeline: How long is it going to take? Can you schedule time for a trial migration and testing?

To help with some of these practical considerations you can use the Microsoft Assessment and Planning (MAP) Toolkit. So you’ve decided to migrate an on-premises database. How are you going to get it there? There are a number of options. You’ll pick your strategy based on your particular requirements. Your mileage may vary. Here’s some ideas:

• Data-Tier Application: DACPAC or BACPAC. Que? These are the forms of data-tier application that you can create and use to migrate your database. Remember that a DACPAC only contains the schema of your database while a BACPAC contains the data as well. If you use a DAPCPAC, you’re going to have to come up with some other way to move the data.
• Backup and Restore: Backup your database to Microsoft Azure storage and restore it to your virtual machine from there.
• Detach and Attach: Detach from the on-premises instance, copy the database files to Microsoft Azure and attach to the SQL Server instance in your virtual machine.
• Generate SQL Scripts: Generate scripts from your on-premises database and then run the scrips on your virtual machine instance.
• SQL Server Import and Export: Use the Import and Export Wizard to transfer data from on-premises to your virtual machine.
• SQL Server Integration Services: You can use the Transfer Database task to transfer an entire database or you can pick and choose what you want to transfer.
• Copy Database Wizard: You can always use the Copy Database Wizard from SQL Server Management Studio. Critically, the SQL Server Agent must be running on both machines.
• Deploy Database to a Microsoft Azure VM Wizard: SQL Server Management Studio for SQL Server 2014 includes this handy little wizard. It will even provision a new virtual machine for you.

Okay, so I’m hoping at this stage that you’re all fired up and ready to start migrating all of your databases to Microsoft Azure. Well… maybe you should step away from this blog post, grab a cup of coffee and have a think about it first. At least I’ve given you some ideas. Tell you what, why don’t you come along and attend 10977: Updating your SQL Server Skill to Microsoft SQL Server 2014 and you’ll get the best info as well as some practical, hands-on experience!