Jovan Popovic Microsoft Development Center Serbia Managed Instance – Dark Secrets - PowerPoint Presentation

Jovan Popovic Microsoft Development Center Serbia Managed Instance – Dark Secrets Session

Introducing Azure SQL Managed Instance ArchitectureMigration to Managed Instance Agenda

We take care of your database chores Your work so far How SQL Database helps Hardware purchasing and management Built-in scale 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-failoverDisaster recovery implementationBuilt-in geo-redundancy and geo-replicationEnsure compliance with standards on your ownBuilt-in easy to use featuresSecure your data from malicious users and mistakesBuilt-in easy to use featuresRole out updates and upgradesBuilt-in updates and upgradesMonitor, troubleshoot, and manage at scaleBuilt-in easy to use featuresTune and maintain for predictable performanceBuilt-in easy to use features PaaS benefits

Managed by Microsoft Managed by customer On-premises Datacenter Management Hardware O/S provision /patching Database Provision/ Patch/Scaling Virtualization Data Applications High availability /DR/Backups SQL Server 2017/2019Infrastructure(as a Service)Datacenter ManagementHardwareVirtualizationO/SDatabase Provision/ Patch/ScalingDataApplicationsHigh availability /DR/BackupsAzure SQL VMsPlatform(as a Service)Data Applications Datacenter Management Hardware Virtualization O/S Database Provision/ Patch/Scaling High Availability/ DR/Backups Azure SQL Database (Managed Instance, Single DB) On-premise costs tends to be driven by hardware and data center management costs Infrastructure-as-a-Service reduces cost categories related to data center and compute Platform-as-a-Service off-loads customers’ most administrative tasks to Azure, further improving efficiency with machine-learning capabilities for performance and security Managed Instance : instance-level deployment for lift-shift existing apps to Azure, fully backward compatible Single database : database-level deployment for new apps Modernization opportunities Intelligent performance/security Machine-learning capability

Azure SQL PaaS options Azure SQL Database Database-scoped deployment option with predictable workload performance Shared resource model optimized for greater efficiency of multi-tenant applications Best for apps that require resource guarantee at database level 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 friction and effort Elastic Pool SingleManaged InstanceInstance-scoped deployment option with high compatibility with SQL Server and full PaaS benefits

Why Managed Instance? Cloud migration requires some changes. There are dependencies on the existing features. SQL Agent? RESTORE? CLR? Fully-fledged SQL instance with nearly 100% compatibility with on-prem SQL Server High availability Backups All PaaS features Native VNET implementation Private IP addresses Competitive Transparent Frictionless Lift & shift Fully managed Isolation Business model

App subnet Isolation and connectivity of Managed Instance SQL instance #1 SQL instance #2 SQL instance #3 VNet -to- VNet Web app (public IP) On-premises VPN/Express route gateway MI Subnet VNet VNet 2 IaaS hosted app Network isolation (customer VNET) Tenant isolation (virtual private cluster) Management access

Virtual cluster Virtual cluster VNet Azure VM TDS endpoint  (Private IP) SQL Server DB Engine Management Agent LB ILB GW GW GW mymi .< clusterid >.database.windows.net TLS TLS TLS Windows Firewall SQL Management (Public IP) VM VM SQL Management (public IP) TDS endpoint (private IP) SQL

Service tiers

Redundant node Active compute node Azure storage account Scaled node Cloud Database Architecture Separation of compute and storage Stateless compute VM TempDB on local Azure Premium Storage Replicated remote files Disk size 128 GiB 256 GiB 512 GB 1 TiB 2 TB IOPS per blob 500 1100 2300 5000 7500 Throughput per blob 100 MiB /sec 125 MiB /sec 150 MiB /sec 200 MiB /sec 250 MiB /sec General Purpose Architecture

Short transactions Batch updates Table/index partitioning Potentially higher RTO (Preview) Accelerated database recovery General Guidelines

Secondary endpoint (read-only) Primary endpoint (read-write) Always On AG Super-fast SSD Secondary replica Primary replica Secondary replica Secondary replica All databases on local SSD Failed node is replaced with the spare - Seeding populated state on local SSD Transaction processing - Primary replica is updated - Log record sent to all secondaries - 2 of 3 nodes must acknowledge - Update is confirmed - All secondaries apply log record - Avg. latency < 3ms Spare node Secondary replica Business Critical Architecture

Secondary endpoint (read-only) Primary endpoint (read-write) Always On AG Super-fast SSD Primary replica Secondary replica Secondary replica Secondary replica Always On AG Super-fast SSD Primary replica Secondary replica Secondary replica Secondary replica Business Critical – Upgrade SLO

Data Pages Covering RBPEX Data Cache Compute Read Only Data Pages ….. File Snapshots File Snapshots File Snapshots Azure Storage Log Cache Log Service   1 TB data file Covering RBPEX Data Cache ….. Data Pages 1 TB data file Covering RBPEX Data Cache Data Pages 1 TB data file Page Servers RBPEX Data Cache Secondary Compute sqlservr.exe RBPEX Data Cache Secondary Compute sqlservr.exe RBPEX Data Cache Primary Compute sqlservr.exe RBPEX Data Cache Secondary Compute sqlservr.exe SQL SQL SQL 100 TB storage Snapshot backups HyperScale (H2 2019)

Assessment Environment check Data Migration Assistant Benchmark tools/ HammerDB Migration Native restore Data Migration Assistant Data Migration Service Transactional Replication MigrationAssessmentFull Recovery ModelTransparent Data EncryptionMemory/CPU ratioSmall log filesCompatibility levelLegacy cardinality estimatorWindows authentication

SQL Instance storage account RESTORE 1 2 BACKUP TO AZURE SQL Server on VM/on- prem Management Service 3 MANAGED RESTORENative RESTORE – Offline Migration

Migrate Server objects - @ psdbatools Install-Module dbatoolsCopy-DbaSysDbUserObject -Source $ src -Destination $ dest Copy- DbaDbMail -Source $src -Destination $destCopy-DbaAgentOperator -Source $src -Destination $destCopy-DbaAgentJobCategory -Source $src -Destination $destCopy-DbaAgentSchedule -Source $src -Destination $destCopy-DbaAgentJob -Source $src -Destination $destGet-DbaLogin -SqlInstance $src -SqlCredential $srcLogin | Where-Object { $_.LoginType -eq "SqlLogin" -and $_.IsDisabled -ne $true } | Copy-DbaLogin -Destination $dest -DestinationSqlCredential $destLogin -ExcludeSystemLogins

Subnet used for MI Virtual Network ( VNET ) 1 2 3 4 Subnet used for DMS On-Premises Azure SQL DB Managed InstanceAzure Blob Storage SMB Network Share 1 Provide existing backups in network share 2 DMS upload backup files to Azure storage 3 4 Provide Tail-Log backup, initiate cutover in DMS and change the application connection strings DMS initiate the migration to Azure SQL MI Full backup restored and Transaction log backups continuously applied until cutover DMS – Online Migration

Transactional Replication – Online Migration

Tran. Repl. – Migration from instance

Maintenance tools Database engine Query store, DMV, XeventAutomatic tuning/plan correctionManagement toolsAzure CLI, AzureRm.Sql PowerShell PsDbaToolsMaintenance scripts (community)Index maintenance solution (Ola Hallengren)Adaptive Index defrag (Sql Tiger tools)First Responder Kit BpCheck (Sql Tiger Tools)

Questions? jovanpop@microsoft.com Twitter: @jovanpop_msftsqlcommunity.slack.com #azure-sql-managedinstance SQL Managed Instance FeedbackAzure SQL Database Documentation