August 15, 2018

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

By Aavisek Choudhury SQL Server 0 Comments

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.

Post Views: 10,201

0.00 avg. rating (0% score) - 0 votes

Tags:Azure SQL Server Managed Instance