The Evolution of SQL Server as a Service - SQL Azure Managed Instance

Introduction to SQL Azure Database Managed Instance

1. The Evolution of SQL Server as
a Service:
Azure SQL Database
Managed Instance
Javier Villegas

2. Technical Speaker
NetConf , SQL PASS, 24 HOP, SQL Saturdays and PASS Virtual Groups
GroupBy and DataPlatformGeeks
@sqlargentina
Javier Villegas
DBA Manager at Mediterranean Shipping Company
Involved with the Microsoft SQL Server since SQL 6.5 to 2019 (Azure SQL DB & MI)
Specialization in SQL Server Administration, Performance Tuning , High Availability
and Disaster Recovery
Microsoft MVP Data Platform
MCP and MCTS
Blogger and MSDN Forums contributor
@javier_vill
/javiervillegas
sql-javier-villegas.blogspot.com.ar
[email protected]

3. Why modernize by moving to the cloud?
•Want to increase productivity and decrease costs
•Data center is too costly and complex to manage
•Hosting solution is high maintenance
•Want to accelerate your growth
•Easily get new features to get that competitive edge
•Expand your reach globally

4. If you:
•Need control over / access to the operating system
•Have to run the app or agents side-by-side with the DB
…then IaaSis the right solution for you
Otherwise, recommendation is PaaS
•Better total cost of ownership
•Focus on your business, and put your DBs on autopilot
How to choose between PaaS and IaaS?

5. IAAS vs PAAS
Relational Database Engines
It’s a VM! Migrate Fleet of Databases Fully managed Database
Management of OS and SQL Server Security Isolation with Azure VNet Active Learning and Optimization
Select Version, OS, Edition Application Surface Compatibility
SQL Agent, Profiler
Cross DB querying, CLR, Replication,
CDC, Service Broker
End to End Integration
Intelligent Data Protection & Security
Database sizes up to 35TB Application & Data Modernization
Minimize Migration Downtime
Up to 4 TB single Databases
> using Elastic Scale
Single VM availability SLA: 99.9%
(<43 min downtime p/month)
Multi-VM availability SLA: 99.95%
(<21 min downtime p/month)
Database availability SLA: 99.99%
SQL Server in Azure VM Azure SQL Database Managed Instance Azure SQL Database
Geo-DR
NEW*
All Azure SQL Database features

6. Managed instances
Azure SQL
SQL virtual machines Databases
•SQL Server surface
area (vast majority)
•Native virtual
network support
•Fully managed
service
•SQL Server and OS
server access
•Expansive SQL and OS
version support
•Automated
manageability features
for SQL Server
•Resource sharing
between multiple
instances to price
optimize
•Enables migration of
many small instances
at scale
•Fully managed service
•Resource sharing
between multiple
databases to price
optimize
•Simplified performance
management for
multiple databases
•Fully managed service
•Hyperscale storage
(up to 100TB)
•Serverless compute
•Fully managed
service

7. What is a Managed Instance
Azure SQL Database Managed Instance is a new capability of Azure SQL
Database, providing near 100% compatibility with SQL Server on-
premises, providing a nativevirtual network (VNet)implementation
that addresses common security concerns, and abusiness
modelfavorable for on-premises SQL Server customers. Managed
Instance allows existing SQL Server customers to lift and shift their on-
premises applications to the cloud with minimal application and
database changes. At the same time, Managed Instance preserves all
PaaS capabilities (automatic patching and version updates, backup,
high-availability), that drastically reduces management overhead and
TCO.

8. Your work so far HowPaaS helps?
Hardware purchasing and management Built-in
Scales on-demand
Protect data with backups (with health checks and retention) Built-in
Point-In-Time-Restore
High availability implementation Built-in
99.99% SLA and auto-failover
Disaster recovery implementation Built-in
Geo-redundancy and geo-replication
Ensure compliance with standards on your own Built-in / easyto use features
Secure your data from malicious users and mistakes Built-in / easyto use features
Role out updates and upgrades Built-in
Monitor, troubleshoot and manage at scale Built-in / easyto use features
Tune and maintain for predictable performance Built-in/ easyto use features
Focus on your business…

9. Easy migration: nearly 100% like SQL Server
Supports compatibility modes (SQL Server 2005+)
Security
•TDE
•SQL Audit
•Row level security
•Always Encrypted

10. Managed Instance is available in two service tiers
General Purpose: Designed for applications with typical performance and IO latency
requirements.
Business Critical: Designed for applications with low IO latency requirements and minimal impact
of underlying maintenance operations on the workload.
Both service tiers guarantee 99.99% availability and enable you to independently select storage
size and compute capacity.
Managed Instance service tiers

11. General Purpose Business Critical
vCores 4*-80 4-80 + 4-80 (replica)
Memory (GB/vCore) 5.1 5.1 + 5.1 (replica)
Availability SLA 99.99%
Storage 8 TB 4 TB
IO latency 5-10 ms 1-2 ms
IOPS Up to 30K Up to 110K
Log throughput 22MB/s 48 MB/s
TempDBsize 24 GB/vCore No specific limit
Log file size Up to 2 TB
In-memory OLTP No Yes
Read-only replica No Yes
Price 1 ~2.5
Service Tier Comparison
* 2 vCoresis a minimal size for General Purpose in an instance pool

12. Assess and Convert

14. Migration Tools & Services

15. Offline
•Native backup/restore
•BACKUP WITH CHECKSUM
Online
•Data Migration Service
•Replication
•Log shipping
(coming soon)
Azure SQL Instance
Storage Account
RESTORE 12 BACKUP TO AZURE
SQL Server on VM/on-prem
3
MANAGED
RESTORE
Easy Database Migration
0
Migrate
server-level
objects
Restore Service

16. Will not be installedside-by-side with Managed Instance
Recommendation: move to PaaS model
SSAS / SSIS / SSRS

17. For Tabular Model
Migrate your OLAP models to Azure Analysis Services
SQL Server Analysis Service -SSAS
… or run these services in Azure virtual machines

18. Migrate your SSIS packages to new SSIS on Azure Data Factory
SQL Server Integration Service -SSIS
… or run these services in Azure virtual machines

19. Migrate your reports to Power BI
SQL Server Reporting Service -SSRS
… or run these services in Azure virtual machines

20. SQL Agent?
CLR?
ISOLATION?
Cross-DB queries
Linked servers
CLR
SQL Agent
Restore
Service Broker
Server-level objects
Database mail
Server collations
Time zone choice
Deployed in a VNet
Private IP address
Instance-level
features
PaaS ++
features
Network
isolation
High availability
Automatic backups
Automatic patching
Automatic tuning
Monitoring as scale
Advanced data security
Why Azure SQL DB Managed Instance?

21. Enables rehosting or
light refactoring for
most SQL apps
Minimizes the need
to rearchitect or
rebuild your apps
Migration to Cloud

22. Core Database Features, File layout, Server-level objects, SQL Agent jobs, Linked
server to SQL, Server/Azure SQL, CLR, Db Mail, Query Notifications
Windows logins, Windows Authentication, Maintenance jobs, Auditing, BULK
INSERT, Simple recovery model, Cross-instance SSB/Transactions
Distributed Transactions, Cross-instance Service Broker, Polybase
Extended stored procedures, Alerts
Database Migration Assessment

23. PaaS: Web Apps, SSAS (tabular) -> AAS, SSRS -> PowerBIPremium, SSIS -> ADF
IaaS: Web App, Desktop app, SSAS(MD), SSIS, SSRS
Windows AD, 100+ DB/instance
Polybase, StretchDB, File access, Linked server to non-MSSQL,
Cross-instance Service Broker, BizTalk
DTC, Windows Services,
Performance/Availability trade-offs
Solution Migration Assessment

24. Database migration
process overview
Assess workloads,
identify issues
.
Pre-migration
Discover Assess Convert
Quickly migrate
database using
RESTORE
Compare
performance with
baseline
Configure database
by resolving
configuration and
performance issues
Migrate schema,
data & objects
Test Configure
PoC
Migrate the source
schema, and then
migrate the source
data to the target
Configure database
by resolving
configuration and
performance issues
Migrate schema,
data & objects
Cutover
Migration

25. Full Security & Isolation

26. Features obsolete in the cloud
(or have a better alternative)
OMS

27. Features that have been retired

28. Features that are considered
https://feedback.azure.com/forums/915676-sql-managed-instance/suggestions/35659075-add-support-for-
filestream-filetable

30. How to programmatically identify a
Managed Instance
The following table shows several properties, accessible through
Transact SQL, that you can use to detect that your application is
working with Managed Instance and retrieve important properties.
Property Value Comment
@@VERSION Microsoft SQL Azure (RTM) -12.0.2000.8 Jul 3 2019
10:02:53 Copyright (C) 2019 Microsoft Corporation
This value is same as in SQL Database.
SERVERPROPERTY ('Edition') SQL Azure This value is same as in SQL Database.
SERVERPROPERTY('EngineEdition') 8 This value uniquely identifies Managed Instance.
@@SERVERNAME,SERVERPROPERTY ('ServerName') Full instance DNS name in the following
format:..database.windows.net, whereis name provided
by the customer, whileis auto-generated part of the
name guaranteeing global DNS name uniqueness
(“wcus17662feb9ce98”, for example)
Example: my-managed-
instance.wcus17662feb9ce98.database.windows.net

31. Key features and capabilities of a
Managed Instance
PaaS benefits Business continuity
No hardware purchasing and management
No management overhead for managing underlying infrastructure
Quick provisioning and service scaling
Automated patching and version upgrade
Integration with other PaaS data services
99.99% uptime SLA
Built in high availability
Data protected with automated backups
Customer configurable backup retention period (fixed to 7 days in
Public Preview)
User-initiated backups
Point in time database restore capability
Security and compliance Management
Isolated environment (VNet integration, single-tenant service,
dedicated compute and storage
Encryption of the data in transit
Azure AD authentication, single sign-on support
Adheres to compliance standards same as Azure SQL database
SQL auditing
Threat detection
Azure Resource Manager API for automating service provisioning
and scaling
Azure portal functionality for manual service provisioning and
scaling
Data Migration Service

32. New and preview features
4 vCoreson Gen5 hardware generation
Support for subscriptions withAzure monthly credit for Visual Studio subscribers
Support forSharePoint 2016 and SharePoint 2019andDynamics 365 Business Central
Configure time zoneduring instance creation.
Create instances withserver-level collation of your choice.
Geo-restore functionality enables you torestore your database to another data center using PowerShell.
(Preview)Re-create dropped databases using PowerShell
Managed instances are protected withbuilt-in firewall.

33. New and preview features
(Preview)Bring your own encryption keyswhile migrating on-premises databases that already have
enabled Transparent Data Encryption (TDE).
(Preview) UseGeo-distributed failover groupsto keep a copy of the instance in the another region and
ensure that your data will be available even in the regional disaster scenario.
Configure backup retention up to 35 daysfor Point-in-time restore. Long-term backup retention (up to
10 years) is still not enabled so you can useCopy-only backupsas an alternative.
(Preview) CreateInstance-level Azure AD server principals (logins)usingCREATE LOGIN FROM EXTERNAL
PROVIDER.

34. PublicEndpoint

37. D E M O
Azure SQL Database
Managed Instance

38. Redundant node
Active compute node
Scaled node
General Purpose Architecture
File size
IOPS
(per file)
Throughput
(per file)
0-128 GiB 500 100 MiB/s
128-256 GiB 1,100 125 MiB/s
256-512 GiB 2,300 150 MiB/s
0.5-1 TiB 5,000 200 MiB/s
1-4 TiB 7,500 250 MiB/s
4-8 TiB 12,500 480 MiB/s
Separation of compute and storage
Stateless compute VM with TempDBon SSD
Database files on Azure Premium Storage

39. Secondary endpoint
(read-only)
Primary endpoint
(read-write)
Always On AG
Super-fast SSD
Secondary replicaPrimary replica
Secondary replicaSecondary replicaSpare node
Secondary replica
Business Critical Architecture
Collocated compute and storage
All database files on a local SSD
Four replicas in a synchronous AO AG
Connect to primary:
Server=mysrvr.8c3b350fdd39.database.windows.net
Connect to secondary:
Server=mysrvr.8c3b350fdd39.database.windows.net;
ApplicationIntent=ReadOnly

40. Secondary endpoint
(read-only)
Primary endpoint
(read-write)
Always On AG
Super-fast SSD
Primary replica
Secondary replicaSecondary replica
Secondary replica
Always On AG
Super-fast SSD
Primary replica
Secondary replicaSecondary replica
Secondary replica
Business Critical –Instance Resize

41. Isolation and connectivity

42. Disaster Recovery Architecture
Subnet
NSG
Virtual Network
West US (primary)
Instance 1Instance 2
VPN gateway
Subnet
NSG
Virtual Network
East US (secondary)
Instance 3Instance 4
VPN gateway
Failover group FOG1
Failover group FOG2
FOG1.8c3b350fdd39.database.windows.net -> Instance 1
FOG1.8c3b350fdd39.secondary.database.windows.net -> Instance 4
FOG2.8c3b350fdd39.database.windows.net -> Instance 2
FOG2.8c3b350fdd39.secondary.database.windows.net -> Instance 3

43. Instance Pools (preview)
Migrate multiplesmall SQL instances
together to a fully managed instance pool
Add instances starting from 2 vCoresup to
your pool’s limit.
Benefit from fast provisioning and scaling
operations at the instance level
Smaller total IP address footprint
SQL Server 1
(4 CPUs)
SQL Server 2
(2 CPUs)
SQL Server 3
(2 CPUs)
8 vCoreInstance Pool

44. Integration with Cloud Services

45. Virtual Network
“On-premises”
data gateway
Managed
Instance
On-premises
data gateway
SQL Server
Subnet 1
Subnet 2
NSG
NSG
Service Bus
Relay
Analysis Services
Logic Apps
Power BI Service
PowerApps
Microsoft Flow
SSIS in Azure Data Factory (ADF)
SSISDB
Integration via “on-premises” gateway

46. Jan-Jun 2019
App compat
•Custom time-zones (non-UTC)
•Rename database
Networking and connectivity
•Removing the Internet
and Azure DNS dependencies
•Optional public endpoint (easy
integration with PaaS services)
•Redirect connection type
Deployment
•4 vCoreinstances
•MSDN subscriptions, larger quotas
PaaS
•Geo-restore
•Data discovery and classification
•UK South, West US , UAE, South
Africa, Australia Central, France
South, Brazil South, South Africa,
UAE
Features
Regional Availability
App compat
•Trace flag support
•AAD logins (GA)
Network and connectivity
•Intrusion prevention: inbound limited to
Management IP ranges
•Use of ServiceEndpoints enabled
Deployment
•Instance pools (preview)
PaaS
•Failover Groups (GA)
•TDE BYOK (GA)
•PITR for deleted databases(Portal)
•Germany, Switzerland
•Sovereign clouds (US, China)
•Norway
July-Dec 2019
Regional Availability

47. Azure SQL Database
Managed Instance
Questions ?
@javier_vill
http://sql-javier-villegas.blogspot.com.ar
https://ar.linkedin.com/in/javiervillegas
[email protected]

48. Thank you!!
Gracias !!
Javier Villegas