In this post, I will discuss a new SQL Server option that recently launched a preview in Azure called SQL Managed Instance, enabling you to run a private, managed version of SQL that is almost 100 percent compatible with on-premises SQL Server.
PaaS Versus IaaS
In a post, I wrote a few months ago, I compared and contrasted the (then) two options for deploying SQL Server in Azure:
- IaaS: You install SQL Server in a virtual machine and live with the cost and pain of managing SQL Server, patching it, upgrading it, backing it up, and so on.
- PaaS: You get an Azure SQL database and use it, letting Microsoft keep the code up to date, secure, fault tolerant, manage your backups, and more.
I believe that Azure SQL is a better offering. So why doesn’t everyone use it? As one comment on the post indicated, sometimes we have old code that expects to find the features of SQL Server and it is simply not there. Azure SQL gives you connection string access to SQL databases but it’s not the full-blown lots-of-services SQL Server that you know from the past.
Azure SQL is great for new projects but it’s no good for lift-and-shift. It turns out that customers have a lot of lifting-and-shifting that they’d like to do. Those customers have had no choice but to bring SQL Server virtual machines to the cloud, which only extends the old problem. It isn’t exactly the most affordable! Microsoft wants more people in the cloud, their cloud to be precise, so they are will to engineer solutions to make it easier.
SQL Managed Instance
Microsoft has provided us with a middle ground. SQL Managed Instance is a solution that will offer near 100 percent compatibility with SQL Server, the same SQL Server that you run on-premises and can deploy with an Azure virtual machine. However, Managed Instances will be a PaaS service where Microsoft gives us the latest version of the code, handles updates, looks after our backups, and all the other things that we expect from a platform.
Note that Microsoft never says 99.9 percent compatibility or 99.99 percent compatibility. They always use the phrase “near 100 percent” compatibility and that’s quite a statement. They’re not there yet with the recently launched Preview of SQL Managed Instance but they plan to be for general availability.
The primary goal of SQL Managed Instance is to give you a path to migrate to Azure. Let’s say that you wish to move an application to Azure and that the database runs on SQL Server. Historically, you had no choice but to move the database machine to Azure too or to redeploy it as another database virtual machine. All that accomplished was that you dropped the tin but most of the operational challenges remained.
Instead, you can use the Azure Data Migration Service to migrate the database to a managed instance running in Azure and immediately take advantage the reduction of operational workload that PaaS can bring to the table. Perhaps, the database will remain there forever, or maybe you will upgrade the application later to take advantage of the lower cost Azure SQL offers.
Note that a managed instance can host up to 100 databases on 8TB of Premium storage with between 5000 and 7500IOPS per data file (database).
A secondary reason to consider SQL Managed Instances is that it provides a private connection point to the service via a virtual network.
SQL Managed Instance is deployed to a virtual network (ARM only) and can be connected to by other PaaS services and virtual machines across that virtual network. Customers can also have private connections to the database across site-to-site VPN or ExpressRoute connections.
Some customers might choose to deploy SQL Managed Instances because of this privacy, much like how we can deploy App Service Environment (ASE) for the private hosting of app services or web apps.
Microsoft highlights a number of features of Azure SQL Database Managed Instances:
- Data Encryption in Motion: Transport Layer Security (TLS) is used to encrypt data in motion. Always Encrypted is also used to protect sensitive data in flight, at rest, or during query processing.
- Dynamic Data Masking: Have you ever noticed how your credit card is shown to you as a payment option on a shopping site? The last four numbers are shown but everything is masked (X) out? That’s the result of Dynamic Data Masking, which also works behind the scenes.
- Row-Level Security: You can secure access to sensitive rows based on the characteristics of a user attempting to access the data.
- Threat Detection: An automated system that attempts to detect and alert you to suspicious query behavior.
- Azure AD Integration: You can use Azure AD for authentication instead of Windows authentication. Note that using Azure AD Connect will bring your Windows (Active Directory Domain Services) accounts to Azure AD. The Premium version of Azure AD adds multi-factor authentication. You can also use SQL authentication.
Microsoft mentions two paths for migrating a database to managed instances:
- You can use the Azure Data Migration Service. This managed service provides near-zero downtime and will be best for busy environments or large-scale migrations.
- You can also restore from a backup. First, you would configure on-premises SQL Server to backup to an Azure storage account (blob storage) and then you would restore from that backup. This would be better suited for migrations, where extended downtime would be required (to avoid data loss after the backup).
The pricing for managed instances is not nearly as cheap as that of Azure SQL but you are getting a full database instance to yourself! It’s probably also more affordable than a mid-large virtual machine. Keep in mind that the instance can host up to 100 databases.
You can deploy Azure SQL Database Managed Instance in a General Purpose tier today and soon there will be a higher cost Business Critical Tier. The general purpose tier is available with:
- 8 cores: $736.29/month
- 16 cores: $1,472.58/month
- 32 cores: $2,208.87/month
The above costs are for US Dollar over 730 hours in the East US region using the RRP pricelist.
If we compare that with a virtual machine alternative, the D8_v3 (8 cores) with SQL Server Standard, not even Enterprise, which is what you get with Azure PaaS, will cost $1,097.92/month.
Also keep in mind that Hybrid Use Benefit, if you have Software assurance on your SQL Server licensing can bring down the managed instance prices by 40 percent – $444.39/month for the 8-core General Purpose option.
A deployment comes with 32GiB of storage. Additional costs will include storage:
- Capacity: Each 32GiB block, up to 8TiB, will cost $0.0575/GB
- IOPS: Every 1 million requests will cost $0.10. Charging will start after Dec 30th
- Backup storage: Will cost $0.05 per GiB. Charging will start after June 30th
The managed instance option for Azure SQL could prove to be very popular as a path to cloud transformation for mid-large businesses. I would have liked to have seen a 4-core option for small-mid businesses who also have smaller applications to migrate.