Azure SQL Server Managed Instances – It’s going to change the SQL Server world.

SQL Server Managed instances are going to be the next big thing in the SQL server world. Currently, it’s in preview and promises a lot to offer. After this offering will come into GA, I hope we can witness many migrations from SQL server VM to SQL Server Managed Instances.

Now if you are new about the Azure SQL Databases, let’s see what are the three offers we have currently on Azure for the SQL Server Databases.

Single Database Elastic Pool Managed Instances(Preview)
Standalone managed database for predictable and scalable workload Shared resource model for greater efficiency through multi-tenancy. Instance scope programming model with high compatibility to SQL Server
Best for apps that required resource guarantee at the database level. Best for SaaS apps with multiple databases that can share resources at the database level, achieving better cost efficiency. Best for modernization with scale with low cost and effort.

What is SQL Server Managed Instances?

It’s a new deployment model of Azure SQL Database, providing near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine.
You may ask the question why it’s near, you can find what are the things which are missing in the managed instance, once you go through rest of the section in this blog.

Features of the SQL Server Managed Instances.

  • Fully Managed Data Base as a Service (DBaaS)
  • Built on the same infrastructure of the SQL DB
  • Fully-fledged SQL instance with nearly 100% compatible with on-prem.
  • Full isolation and security. ( Container within your Vnet, Private IP addresses, EXPRESSROUTE/VPN Connectivity)
  • New Business Model (Transparent, Frictionless, Competitive)

It will not be very critical to understand which business will run on which SQL Server offering in Azure if you own SQL server VM’s, ultimately you own patching, backups and database high availability, which is definitely not required if you move to SQL server managed instances, and there are more.

Let’s see a detail comparison table.

SQL Server on VM SQL Server Managed Instance
Hardware Purchasing and Management Built-in Scale-on-Demand
Protect Data with Backups (with health check and retention) manual configuration required Built-in Point-In-Time-Restore
High Availability Implementation Required Built-in 99.99% SLA and Auto Fail-Over
Disaster Recovery Implementation Required Built-in Geo-Redundancy and Geo-Replication
Ensure Compliance and Standards on your own Built-In compliance (Easy to use features)
Secure your data from malicious attacks and mistakes Built-In easy to manage feature
Patching (Updates Roll Out) Updates and Upgrades will be done by MS
Monitor, troubleshoot and manage at a scale Built-In Easy to use feature
Security Isolation Isolated environment (V-Net Integration)
Tune and Maintain for predictable performance Built-In Easy to use feature

Let’s have a detail understanding of all the available features, supportability, migration, security and other details.

What are the familiar SQL server features which can be found?

  • Native Backup and Restore
  • Cross-database queries and transactions
  • Security Features including Transparent Data Encryption, SQL Audit, Always Encrypted and Dynamic Data Masking
  • SQL Agent, DBMail
  • Scenario Enablers: Change Data Capture, Service Broker, Transactional Replication and CLR
  • DMVs, XEvents, and Query Store for Troubleshooting

What is the Version Compatibility?

  • Full Compatibility with SQL Server 2005+

What is the available Authentication Process?

  • SQL Server
  • Active Directory

What is the Authorization level?

  • SQL Server 2017

How to carry the Pre Migration Assessment Process?

  • Pre Migration Assessment Process can be done by running the Database Migration Assistant (DMA) and Database Experimentation Assessment (DEA)

What is the Migration Process?

  • DMS (Data migrations at scale)
  • Native Backup and Restore
  • Log Reply
    (Coming in GA)

What are the Security Features?

  • SQL Audit
  • Encryption (TDE, AE)
  • Vulnerability Assessment (Coming in GA)
  • Row Level Security
  • Dynamic Data Masking

What are Programmability Features added?

  • Cross-Database Queries and Transactions
  • .NET
  • R Language(Coming in GA)
  • Linked Server
  • Global Temp Tables

Scenario Enables

  • Service Broker
  • Change Data Capture
  • Transactional Replication

What are the features which have the better alternative in Azure?

  • Always ON Availability Groups ->Local HA, Active Geo-Replication
  • Windows Authentication->Azure AD Authentication
  • Management Data Warehouse->OMS Integration

What are the retired features?

  • Database Mirroring
  • Extended Stored Procedures: Customers should use CLR

What are the features which will be included post GA?

  • Filestream, Filetable
  • Cross-Instance Distributed Transaction (MS – DTC)
  • Stretch Database
  • Polybase

How your data will be secure and isolated?

  • Full isolation from other tenants without resource sharing
  • Promote secure communication over private IP addresses with native VNET integration
  • Enable your on-premises identities on cloud instances integration with Azure AD and AD Connect

What is the Azure Backup Retention Period?

  • By default 7 days

Types of SQL Server Managed Instances in Azure

  1. General Purpose
  2. Business Critical

Let’s see what are the difference between the above two instances.

General Purpose Business Critical
Best For Data Applications with Common I/O and Availability Requirements Business Critical Data Applications with fast I/O and High Availability Requirements
Compute Tiers 8, 16,24,32,40,64, 80 vCores 8, 16,24,32,40,64, 80 vCores
Storage Fast Remote Storage 32 GB – 8TB Per instance Super-Fast Local SSD storage 32 GB-4TB Per Instance
Availability 1 replica, no read scale (Two node availability group, the secondary is not readable) 3 replica, 1 read scale (Three node availability group, 1 secondary is readable)
Surface Area Full (except in-memory OLTP) Full

Azure Hybrid Benefits

Please see the Azure Hybrid Benefits if you bring the on-prem license in Azure

1 SQL Server Standard License Core = 1 General Purpose Core
1 SQL Server Enterprise License Core = 1 Business Critical Core
1 SQL Server Enterprise License Core = 4 General Purpose Cores

Conclusion: Azure Managed instance is going to change the world of SQL server instance management, it has all the business features in-built and doesn’t need in-person database management, the SQL DBA’s must be thinking that their job is at risk, I personally feel they can start learning new areas in database technologies like ML, AI, Neural Networks etc. which looks very promising. That’s all for today. Thanks for your time for reading this post. You have a great day ahead.