Thinking of migrating one or more of your SQL Server instances to the Cloud? If so, have you spent much time figuring out which provider, solution and options to go with? There are essentially two main Cloud providers to consider – Azure (Microsoft) and AWS (Amazon Web Services). Both providers offer two main products in this area: Virtual Machines with SQL Server and Managed SQL Server instances. From there, the combinations and permutations of options available can be overwhelming. I have assembled a AWS / Azure Cloud SQL Server overview of products and options identifying the key points to consider, my own experience with these providers and products, and I how I would go about approaching a migration.
With Virtual Machines, you have access to the Server’s Operating System (OS) as if it were hosted in your infrastructure, allowing you the freedom to install all the software that you require – provided you have sufficient storage (disk) space. Both Azure and AWS provide many, many different options in terms of Virtual Machines.
See the following links for more information:
SQL Server Licensing
Virtual Machine Configuration Types
Recommended Virtual Machine Configuration Type(s) by SQL Server Edition
Storage Optimized Pricing (AWS On-Demand using SSD Storage):
- Reserved Instance can provide 60% savings for each of these options:
- xlarge – $350.64
- 2xlarge – $701.28
- 4xlarge – $1401.84
- 8xlarge – $2803.68
- All prices are in US Dollars $
- Assumes 720 hours per month (30 day month)
- one gibibyte = 1 GiB = 230 Byte = 1,073,741,824 Byte
- one gigabyte 1 GB = 109 Byte = 1,000,000,000 Byte
*GiB / GB definitions source: http://wintelguy.com/gb2gib.html
Storage Optimized Pricing (Azure using SSD Storage):
For all Virtual Machine configuration types, specifications and prices refer to:
- AWS: https://calculator.s3.amazonaws.com/index.html
- Under Computer: Amazon EC2 Instances or Compute: Amazon EC2 Dedicated Hosts, click “+ Add New Row”
- Click the “Type” field to open the “Select Instance Type” pop up window
- Select “Windows and Std. SQL Server” in the Operating System option box
- Azure: https://azure.microsoft.com/en-ca/pricing/details/virtual-machines/sql-server-standard/
- Expand each Virtual Machine Configuration Type to see detailed options
Managed SQL Server Database(s)
With managed SQL Server databases, there are several options to consider. Here is an overview of the Managed SQL Server Database offerings from both providers.
Full list of Azure features/limitations:
SQL Server Licensing
Same as SQL Server Licensing for Virtual Machines above.
SQL Server Standard (AWS On-Demand, Single-AZ):
SQL Server Standard (Azure – Elastic Pool – Standard):
Here is a link to AWS’ pricing calculator:
Here is a link to Azure’s pricing calculator:
Here is a link to a Microsoft article to help you determine which service tier is best suited for your workload(s):
My Experience with SQL Server, Virtual Machines, AWS/Azure SQL Products
I have been working with SQL Server since 1998 (version 7) and Virtual Machines since 2005. I am a big fan of both technologies and I am impressed with how easy and quick it has become to spawn new instances. Prior to Virtual Machines, setting up a SQL Server database required a physical server which most often also required setting up (unpack, assemble, turn on…). If you were lucky, you’d have a base image of the exact same server to start with. If not, you had to slug through the entire OS installation and configuration before you could even start the SQL Server installation and configuration. Once you had the physical server on hand this would typically consume an entire day. Then Virtual Machines came along and this typically chopped the time in half since setting up the server typically only required spinning up a new Virtual Machine base image (no more hardware specific driver dependencies). Now by making use of a Virtual Machine image with the appropriate edition of SQL Server pre-installed or a managed SQL Database instance, you can have your database up and running in a matter of minutes. Incredible.
I have not tried a majority or the options that are available from both providers since that would take many weeks/months and a lot of money to accomplish. But I have made use of both AWS and Azure’s free offerings. AWS provides a free tier of products for the first 12 months while Azure offers a 30-day trial that includes a $300 USD credit.
For AWS, in EC2 I maintain a free tier Windows Server 2012 R2 Virtual Machine with SQL Server 2012 Express including SSRS (30 GB hard drive with ~5 GB free space after SQL Server installation) and in RDS a free tier SQL Server 2016 Express database (20 GB of storage). There were no free tier Windows Server images with SQL Server Express pre-installed so I had to install it manually on a free tier Windows Server image. And I ended up with Windows Server 2012 R2 and SQL Server 2012 because attempts with newer versions of both resulted in running out of disk space (limited to 30GB in free tier).
I do not use either of these databases for production critical systems, however, I do use them for development and analysis of decent sized datasets (hundreds of thousands of records). The performance to date has been aligned with the local SQL Server 2016 Developer edition I have running on my Lenovo Thinkpad P50 which is impressive. Stored procedures that involve a number operations on datasets with ~20k records typically execute in 2-5 seconds. I would not hesitate to make use of either of these for non-critical production databases. However, it is important to note that I had a heck of a time trying to get the RDS SQL Database instance to see a database backup file that I created and wanted to restore in that environment. It also appears that I am not alone with this struggle since a great deal of other people have experienced the same issues based on their posts on various Database/SQL/AWS message boards.
For Azure, I have not created any Virtual Machine instances but I did create a SQL Server 2016 Express Azure Database which I deleted when my free trial expired.
With the Azure SQL database, I was disappointed with the performance and the lack of SQL Server objects that are typically accessible from SQL Server Management Studio – I believe the latter is the case because it is not a full-blown version of SQL Server. Given it was an entry level SQL database configuration I would have expected that the performance would have been similar to the SQL Server Express databases I have in AWS since they make use of entry level configurations as well, hence, the free tier. However, this was not the case. The same stored procedures that execute in 2-5 seconds within AWS were taking >20 seconds in Azure. Unfortunately, I did not get the opportunity to try the same tests with an Azure Windows Server Virtual Machine running SQL Server Express but I am optimistic that the results would be similar to AWS given Virtual Machine specifications parameters are consistent between the two (reference Cores and RAM), whereas, Azure SQL database specification parameters make use of obscure DTU (Database Transaction Units) values which indicate the instance’s performance capability without any correlation to the number of Cores or amount of RAM that it represents.
My Recommended Approach to a SQL Server Database Migration
If I were tasked with evaluating AWS and Azure products with the intention of migrating one or more of my databases, I would take the same approach as what I have already done in the form of proof of concepts covering various scenarios that make use of each product with one or more appropriate configuration types / service tiers (Cores, RAM, Storage, etc…). As the first and potentially final stage of the migration, I would likely start with the Virtual Machine and SQL Server installation since it most closely mimics my own environment and what I am use to. After completing the migration to the Virtual Machine & SQL Server environment, I would then put focus on proving out a managed SQL database instance in a development/test environment. If I got to the point where I was satisfied with all aspects of the managed SQL database instance (performance, stability, ability to troubleshoot, etc…), I would then put a plan in place to migrate there. However, if I encountered an issue I would not hesitate to abandon this path at least until the product / technology advances enough to alleviate my concern(s) since the price points are similar and I would already have the resources and processes in place to manage the overhead involved in maintaining the environment (OS and SQL Server patches, service packs, etc…).
Taking a multi-phase approach is not a bad thing since the preparation involved would produce the required migration plan and scripts if they don’t already exist as part of your DR plan. In addition, having the confidence that you are capable of (fairly) easily moving to a different environment could come in handy when trying to negotiate with the providers for the best deal possible.
As you can see, both moving your SQL databases to the cloud and the licensing implications can be complicated. If you are moving to the Cloud, if you are already in the Cloud or have a hybrid environment (Cloud & On-premise), make sure you have a team of Licensing Experts on your side to reduce any potential gaps and ultimately drive down your licensing spend. Let Method 180 be your ace in the hole. Find out more at www.method180.com.
Here are links to the various Getting Started Guides if you want to give any of these products a test drive:
AWS EC2 Virtual Machine:
Azure Windows Virtual Machine:
AWS RDS SQL Server Database:
Azure SQL Database: