Azure: SQL VM vs SQL Database

t-sqltuesday

“The Cloud” edition

 

Azure SQL Database vs. Azure SQL Virtual Machine

 

In keeping with my “this blog is for the beginner DBAs” style, I’m going to keep this short and simple.   I will also confess that I have only played with the cloud, and only Microsoft Azure so you won’t see anything about AWS or any other cloud provider.

Some basic terminology:

  • Cloud: No such thing.  It is just your stuff on someone else’s machines that they maintain for you.
  • Virtual Machine (VM): A Virtual Server on some physical servers…yours, or someone else’s.
  • Azure: Fancy name for Microsoft’s cloud. As a noun or an adverb it means “blue”.  Or a small butterfly.
  • Azure SQL database: Just a database in Azure on some storage
  • Azure Virtual Machine: A VM on Microsoft’s Azure servers, that you do not have to maintain the underlying physical infrastructure.

When the boss comes to you and wants to look at “moving SQL to the cloud”, please make sure you are comfortable with all of the above terms so you are on the same page, or know where he is misusing some buzzwords.

Let’s assume a very basic scenario:  You have one database that your company website uses to sell its widgets.   It needs to be up pretty much 24×7, and your building has occasional internet connectivity issues that are out of your control.  Enough so that you are losing sales and customer confidence.  Your traffic is low, and your business is small so every sale is important.

You basically have three choices:

  • Move to a colocation provider and manage it all yourself
  • Move to Managed Hosting solution such as Rackspace
  • Move to the cloud

For this conversation, let’s assume the first 2 choices are ruled out due to cost.  For Azure pricing, there is a handy calculator here.

Virtual machines are basically priced based on resource quantity (CPU/Ram/Storage) combined with number of hours per month it is turned on (on/off is via the Azure portal, not shutdown from the VM desktop).  You can get an 8 core, 56GB Ram, 605GB storage VM for $1.32/hr. or $982.08/month.

Azure SQL Databases are priced based on DTUs x number of hours/mo.  A single database with 250 DTUs and 500GB of storage runs $1.25/hr. or $930/mo.

Note: Pricing can and does change, so this is based on prices in the calculator as of 9/10/2016.  Also, you can change service tiers as necessary at any time.  Ramp up for seasonality, down for off-season, etc.

Pros and cons:

Virtual Machine:

  • Pretty much the same as a virtual or physical machine in your network.
  • You worry about the O/S in the VM, Microsoft takes care of the underlying infrastructure
  • Spin one up in minutes with SQL Server already on it
  • SSMS, database management, etc. is all the same as your local (on premises) servers. Use the SSMS GUI as you always have, or T-SQL
  • Backups are your responsibility and under your control (can be automated via the portal)
  • You patch O/S and SQL as you see fit (Automated is available through the portal)

azure_vm_screenshot

SSMS and Services from my Azure SQL 2016 VM on Windows 2012 R2

Azure SQL database:

  • Connectivity is through connection strings for SSMS and other applications. Strings are available through the Azure portal
  • Patching is automated under the covers by Microsoft…existing connections may be reset when they happen. You have no say in this.
  • Backups are completely automated
  • Can be created in just minutes (assuming storage accounts, etc. already configured)
  • Some commands only available through T-SQL
  • Geo-replication for DR in a few clicks (DR server needs to be created of course)
  • TDE in two clicks

azure_sqldb_screenshot

SQLDB from SSMS on my laptop, with Properties and Create User template…

This is not a comprehensive list of every feature available, nor is it intended to be.  Microsoft is adding more and more to the Azure SQL products all the time, so no list will really be able to keep up.

Rather, the goal here is to give the junior DBA a reference point to the major differences between SQL Server in an Azure VM vs. am Azure SQLDB.

The VM is most likely the more familiar choice for those new to cloud computing, but each workload and company will have to make the decision based on their requirements as to which of the two is the optimal choice.

Hope this helps clarify some things for you!  Please ask questions in the comments and I will answer what I can, as long as you remember the disclaimer at the top J

Kevin3NF

The OnPurpose DBA

 

Leave a Comment

Sign up for our Newsletter

%d bloggers like this: