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
- General Purpose
- 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.
Setup your on premise SQL server environment with SQL Server 2014
Step by Step Installation of the Azure SQL Managed Instance
SQL Server 2016 Enterprise Always on installation and configuration in Windows Server 2016
About Author
