Azure Managed Instance Your Bridge to the Cloud

1. Azure Managed InstanceYour Bridge to the Cloud Joey D’Antoni SQLBits 01 March 2019

5. Azure Relational Database Platform Power BI Azure App Services Azure Data Factory Azure Analysis Services Azure Machine Learning Azure Cognitive Services Azure Bot Service Azure SQL Database SQL Data Warehouse PostgreSQL MySQL MariaDB Database Services Platform IntelligentAdvisors, tuning, monitoring FlexibleOn-demand scaling, resource governance TrustedHA/DR, backup/restore, security, audit, isolation Azure compute Azure storage Global Azure in 54 regions

6. How to easily migrate and get benefits of cloud? Migrate* Rehost (lift and shift) Enables rehosting orlight refactoring for most apps Enables optimizationduring or post migration(fully managed service) Refactor Assess Optimize Rearchitect IaaS (virtual machines) fall short here Eliminates the need to rearchitect or rebuild your apps Rebuild

7. What is SQL Database Managed Instance? SQL Database (PaaS) Managed Instance New deployment option enabling friction-free migration of SQL Server workloads to a fully-managed service Single database SQL Server compatibility Fully-managed service Full isolation and security New purchasing options • Fully-fledged SQL instance with nearly 100% compat with on-premise • Built on the same infrastructure as SQL Database • Provides the same benefits (PaaS) • Contained within your VNet • Private IP addresses • Express Route / VPN connectivity • Transparent • Frictionless • Competitive Elastic Pool

8. Introducing Azure SQL Database Managed Instance Azure SQL Database SQL Single Elastic Pool Managed Instance (preview) SQL Standalone managed database for predictable and stable workloads Instance-scoped programming model with high compatibility to SQL Server Shared resource model for greater efficiency through multi-tenancy Best for SaaS apps with multiple databases that can share resources at database level, achieving better cost efficiency Best for modernization at scale with low cost and effort Best for apps that require resource guarantee at database level

9. Benefits of Platform as a Service We take care of your database chores

10. Save time with familiar SQL Server tools and resources Full compatibility with SQL Server 2005+ Eliminate app changes with full SQL Server programming surface Use familiar SQL Server features in Managed Instances 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, for workload orchestration improved awareness Scenario enablers: Change Data Capture, Service Broker, Transactional Replication, and CLR DMVs, XEvents, and Query Store for troubleshooting

11. Database Compatibility Based Certification • Microsoft Database Compatibility Level Protection • Full Functional protection once assessment tool runs clean. • Query Plan shape protection. • Overall process • Use Database Migration Assistant (DMA) and Database Experimentation Assistant (DEA) for assessment. • Migrate database and keep/set source Database Compatibility Level on target. • Perform minimal testing or as determined by your organization.

12. Easy migration: nearly 100% like SQL Server • Data migration • DMS (migrations at scale) • Native backup/restore • Configurable DB file Layout • Log Reply * • Security • Integrated Auth (AAD) • Encryption (TDE, AE) • Row Level Security • SQL Audit • Vulnerability Assessment* • Dynamic Data Masking • Programmability • Cross-database queries and transactions • .NET, R* • Linked servers • Global temp tables • Operational • DMVs & XEvents • Query Store • SQL Agent • DB Mail (external SMTP) • Scenario enablers • Service Broker • Change Data Capture • Transactional Replication* * - some features will be added until General Availability of Managed Instance

13. App compatibility: what’s is missing? • Features with a better alternative in Azure Always-On Availability Groups: local HA, active geo-replication Windows Authentication: Azure Active Directory is the alternative. Management Data Warehouse : OMS integration is the alternative. • Retired features Database Mirroring: built-in HA / geo-replication Extended stored procedures: customers should use CLR • Features considered post-GA Filestream, Filetable Cross-instance distributed transactions (MS DTC) Stretch Database PolyBase

14. Dedicated resources through customer isolation VNET support in SQL Database Managed Instance VNet-to-VNet Web app(public IP) Enable full isolation from other tenants without resource sharing Promote secure communication over private IP addresses with native VNET integration Enable your on-premise identities on cloud instances, through integration with Azure Active Directory and AD Connect Plan your deployment for enough IP addresses in your Vnet address space in your subnet UDR and NSG limitations VNet2 Network isolation(customer VNET) SQL instance #1 SQL instance #2 Tenant isolation(compute, storage) VNet1 SQL instance #3 “Virtual data cluster” dedicated to customer(virtual private cluster, VNET, private IPs) On-premises VPN/Express route gateway

15. App integration and network security “VNet Integrated” web app (peered VNet) 4 App subnet App Service Environment “VNet Integrated” web app 2 1 IaaS hosted app 3 Application VNet Peering / VPN channel MI subnet App subnet 5 IaaS hosted app App Service Environment 6 Managed Instance #1 Managed Instance #2 Express Route / VPN Gateway 7 On-premises app

16. certificate verification Network communications Certificate Authorities Internet TLS TLS Management & Deployment Plane Azure Azure SQL Database Managed Instance TLS Service dependenciesAzure Customer applications & tools On-Premises + Azure VNet

17. Pricing Tiers

18. AZURE HYBRID BENEFIT (AHB):Get more for your virtualized workloads SQL Server license trade-in values SQL Database Managed Instance Take an inventory of on-premises licenses to determine potential for conversion Convert on-premises cores to vCores to maximize value of investments 1 Standard license core = 1 General Purpose core 1 Enterprise license core = 1 Business Critical core 1 Enterprise license core = 4 General Purpose cores (virtualization benefit) Business Critical General Purpose General Purpose OR Highly virtualized, Enterprise Edition Enterprise Edition Standard Edition SQL Server with Software Assurance

19. Creating your first Managed Instance Portal

20. Connecting to Managed Instances • SQL Authentication • Azure AD • Token-based authentication for applications connecting to SQL Database • ADFS or native user/password authentication for a local Azure Active Directory without domain synchronization • Multi-Factor Authentication (MFA) Latest driver versions provides best connectivity experience • Do not forget that MI is available on private endpoint – test connectivity for your scenarios • Public endpoint is coming later this year Build connectivity resilience into your code to protect from transient faults Continue using well-known SQL Server tools (SSMS, SSDT) – install latest version

21. SURFACE AREA

22. USER DATABASE FILE LAYOUT CONSIDERATIONS

23. Performance Impact of Data File Size

24. Backup aND RESTORE

25. TempDB

26. High Availability

27. SCALING MANAGED INSTANCE

28. Business Intelligence Services Recommendation - move BI solutions to PaaS model PowerBI Not installed side-by-side with Managed Instance Migrate your SSIS packages to new SSIS on Azure Data Factory (PaaS service) Migrate your OLAP models to Azure Analysis Services … or run these services in Azure virtual machines For SSRS: run in a virtual machine, or switch to Power BI SSIS / SSAS PaaS SSIS / SSAS