Azure SQL Managed InstanceA fully managed SQL Server in the cloud

Mladen Andzic

Program Manager, Azure SQL

mlandzic@microsoft.com

Agenda When to choose SQL Managed Instance

What to expect with SQL MI

What’s new and what’s coming next

Azure SQLThe family of SQL database services on Azure

Azure SQL Database

Best for supporting

modern cloud apps

database

Pre-provisioned or serverless compute and Hyperscale storage to meet specific workload

requirements

instance

Offers high compatibility

with SQL Server and

native VNET support

Best for lift and shift migrations and apps requiring

OS-level access and control

virtual machine

Automated manageability features

and OS-level access

SQL Server on Azure Virtual Machines

Azure SQL Managed Instance

Best for modernizing

existing apps

Service

Resource

model

Platform-as-a-Service (PaaS)Infrastructure-as-a-Service (IaaS)

Familiarity Tools Flexibility

Which Azure SQL offering is right for you?

Full control and capabilities of SQL Server instance

• OS level access

• SQL Server versions 2008 - 2019

• Patching/Upgrades

• App or other services colocation

Fully managed SQL Server engine as a service

• Native restore and log shipping

• Cross-DB transactions and Linked Server

• Service Broker and Event Notifications

• SQL Agent and Transactional Replication

• .NET CLR integration and ML Services

Fully managed SQL database as a service

• A database scoped programming model

No

Migration, App Modernization, App Innovation

Azure SQL

Database Yes

SQL Server

on Azure VMsYes

Azure SQL

Managed InstanceYes

Customer Managed

Microsoft Managed

Control Capabilities

No?

What to expect with SQL MI

What is under your

control

What service is

providing for you

Tier selection,

instance sizing,

operation scheduling

Hardware allocation,

deployment of service

binaries

Service

deployment

Updates of database

schema, compat level

change

Service updates and

patches (OS, SQL)

Keeping

system

up-to-dateWhat is under your

control

What service is

providing for you

Filtering & routing

data traffic, Express

Route configuration

Service-aided subnet

configuration

Network security &

connectivityWhat is under your

control

What service is

providing for you

BYOK for TDE

TDE, TLS, FoG, AAD

Certificate Management

What is under your

control

What service is

providing for you

Cross-region

connectivity, Sizing of

geo-secondary

instance, Manual

failover

Built-in HA

configuration, Geo-

replicated databases

(FoG), Auto-failover

HA and DR

What is under your

control

What service is

providing for you

Manual backups,

recovery using PITR

and geo-restore

Geo-replicated

automatic backups

(full, diff. log), LTR

Database backups

What is under your

control

What service is

providing for you

AD → AAD account

sync (AD Connect)

ADFS, Client drivers

SSO AAD

authentication

Integrated Authentication

What is under your

control

What service is

providing for you

Azure alerts and

monitoring tool

configuration

Collection and

streaming of

performance and

health metrics

Monitoring

What is under your

control

What service is

providing for you

SQL MI VNet

SQL MI VNet

How to connect to Managed Instance

Peering / VPN Channel

Application VNet

What’s new and what’s coming next

SQL MI New Features and Improvements

AAD LoginsGA

ML ServicesPreview

Windows user migrationGA

Hosting SSRS catalog databasesGA

Distributed transactionsPreview

Cross-instance service brokerGA

Log replay APIPreview

Available

Announcing

Coming soon

SQL Server compatibility and seamless migration

DB3, coordinator

Managed instances can reside in the

same or different regions

Cross-instance collaboration secured

with Server Trust Group entity

Enabler for app architectures with

vertically or horizontally partitioned

data

Unlocks migrations and modern app

development scenarios

Preview limitations:

• Homogeneous participants (SQL MI only)

• .NET as the only app layer supported (in

addition to Transact-SQL)

On-prem / Azure Application

Instance 1 Instance 2 Instance 3

Server Trust Group

DB1

DB2DB3

DB4

DB5

Transact-SQL:

BEGIN DISTRIBUTED TRANSACTION

.NET:

new TransactionScope()

Distributed transactions across SQL MIs

SQL MI New Features and ImprovementsBusiness continuity and disaster recovery

Geo-restoreGA

Accelerated Database RecoveryGA

PITR for deleted databasesGA

Cross-instance PITRGA

Global VNet peeringGA

Available

Announcing

Global VNet Peering support

Simplicity of setup Low latency Enterprise-grade throughput

SubnetNSG

Virtual Network

SubnetNSG

Virtual Network

Primary (West US) Secondary (East US)

Instance1 Instance2

Failover group FOG1

Instance4 Instance3

Failover group FOG2

Global VNet peering New!

* Supported in newly created subnets only

SQL MI New Features and ImprovementsSecurity and compliance

TLS 1.2 enforcementGA

Long-term backup

retentionPreview

Service-aided subnet configurationGA

Service endpointsGA

Backup redundancy options

(LRS/ZRS/GRS)GA

Private linkPreview

Service endpoint policies

for storageGA

Available

Announcing

Coming soon

Provides fixed private IP address of MI

Enables instance-level traffic control

Enables distributed network architectures

Works across regions, subscriptions and

tenants

SQL MIApp A (you)

10.0.1.0/24

10.0.1.99

App B (supplier)

10.0.1.0/24

10.0.1.55

App C (end user)

10.0.2.0/24

10.0.2.55

Private Link

MI1 MI2

SQL MI New Features and ImprovementsPerformance, scalability and cost optimization

Improved backup compression GA

Performance improvementsGA

Backup retention period < 7 days GA

Available

Announcing

SQL MI Performance Improvements (Perf v2)

Improved log rate for SQL MI General Purpose (GP)

- Per-instance limit: 3MB/s per vCore (unchanged)

- Per-instance max: 120 MB/s (up from 22 MB/s)

- Per-DB limit (new):

Improved log rate for SQL MI Business Critical (BC)

- Per-instance limit: 4MB/s per vCore (unchanged)

- Per-instance max of 96 MB/s (up from 48 MB/s)

- Increased IOPS from 2.5k to 4k IOPS

Test Results (SQL MI GP):

- TPC-C – 2x-3x transaction throughput

- TPC-H – up to 23% lower execution time

- Scans – up to 2x throughput

- Data Ingestion – 2x-3x data ingestion rate

0 10 20 30 40 50 60 70 80 VCORE

DATA INGESTION

(LOG RATE)

GP: 65 MB/s

BC: 96 MB/s

Log file size Per-DB limit

<= 128 GB 22 MB/s

<= 512 GB 30 MB/s

<= 1 TB 50 MB/s

<= 2 TB 65 MB/s

Demo

SQL MI Index Rebuild before and after

Automatic backups cost optimization

• Fine-tuning of PITR backup retention

period:

• 1-35 days for active databases

• 0-35 days for deleted databases

• Up to 30% better compression of backups

• Less expensive backup storage options

through configurable redundancy

SQL MI New Features and ImprovementsMonitoring and manageability

SCOM Management PackGA

User-initiated failoversGA

Alert management from portalGA

Querying Log Analytics

from portalGA

Resource Health integrationPreview

Available

Announcing

Coming soon

AAD Directory Readers

role assignment Preview

1. Global Admin or Privileged Role Admin (once per Azure AD tenant):

Create Azure AD group and assign “Directory Readers” role to this group

Assign group owners to the group – any Azure AD users without specific roles

2. Group owners then can add managed identity of an instance to the group

No more Global Admin’s or Privileged Role Admin’s action needed per each instance:

AAD Directory Readers role assignment (preview)

• SCOM Management Pack for MI (GA)

• Ability to monitor hybrid environments: SQL Server + SQL DB/MI

• The most comprehensive monitoring solution for MI based on SQL Server MP

• Automatic discovery of Azure SQL resources to monitor

• Updated dashboards

• Monitoring of MI specific features: Failover Groups

Monitoring hybrid environment using SCOM

SQL MI New Features and ImprovementsService deployment and patching

Instant storage scaling

(GP)GA

Instance management

operations transparencyGA

Available

Announcing

Online HW generation upgrade GA

Maintenance windowPreview

Coming soon

Enhanced instance management experience

CRUD API improved - link• New API version (2020-02-02) introduced

• MI visible from the start of provisioning

• New provisioningState property

• Full tooling support (Portal, PowerShell, Azure CLI)

OPERATIONS API introduced - link• For retrieving status of create/update operations

• Response contains operation parameters (old -> new)

• Response contains operation steps

(current step, total steps, step details)

• Provides capability for canceling operation

• Full tooling support (Portal, PowerShell, Azure CLI)

Resources

Check Azure SQL Managed Instance documentationaka.ms/azuresqldocs

Try the Azure SQL Workshopaka.ms/sqlworkshops

Subscribe to Microsoft Tech Community for Azure SQLaka.ms/azuresqltechcommunity

© Copyright Microsoft Corporation. All rights reserved.