1 / 23

Azure SQL Managed Instance: Architecture, Migration, and Benefits

Learn about the architecture of Azure SQL Managed Instance, the migration process, and the benefits it offers. Experience a fully managed SQL instance with high availability, backups, and all PaaS features. Achieve isolation, connectivity, and cost efficiency.

christopherkim
Download Presentation

Azure SQL Managed Instance: Architecture, Migration, and Benefits

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. Agenda Introducing Azure SQL Managed Instance Architecture Migration to Managed Instance

  3. PaaS benefits We take care of your database chores

  4. Modernization opportunities • Platform • (as a Service) • Infrastructure • (as a Service) Intelligent performance/security • On-premises • On-premise coststends 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 Applications Applications Applications Data Data Data High availability /DR/Backups High availability /DR/Backups High Availability/ DR/Backups Database Provision/ Patch/Scaling Database Provision/ Patch/Scaling Database Provision/ Patch/Scaling O/S O/S provision /patching O/S Virtualization Virtualization Virtualization Hardware Hardware Hardware Datacenter Management Datacenter Management Datacenter Management • Azure SQL Database • (Managed Instance, Single DB) • Azure SQL VMs • SQL Server 2017/2019 Managed by customer Managed by Microsoft Machine-learning capability

  5. Azure SQL PaaS options Azure SQL Database Single Elastic Pool Managed Instance Database-scoped deployment option with predictable workload performance Shared resource model optimized for greater efficiency of multi-tenant applications Instance-scoped deployment option with high compatibility with SQL Server and full PaaS benefits 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

  6. Why Managed Instance? CLR? RESTORE? • Cloud migration requires some changes. • There are dependencies on the existing features. SQL Agent? Lift & shift Fully managed Isolation Business model • 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

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

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

  9. Service tiers

  10. General Purpose Architecture Active compute node • Cloud Database Architecture • Separation of compute and storage • Stateless compute VM • TempDB on local • Azure Premium Storage • Replicated remote files Redundant node Scaled node Azure storage account

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

  12. Business Critical Architecture Primary endpoint (read-write) • 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 Always On AG Super-fast SSD Secondary replica Primary replica Secondary replica Secondary replica Secondary replica Spare node Secondary endpoint (read-only)

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

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

  15. Migration • Assessment • Full Recovery Model • Transparent Data Encryption • Memory/CPU ratio • Small log files • Compatibility level • Legacy cardinality estimator • Windows authentication • Assessment • Environment check • Data Migration Assistant • Benchmark tools/HammerDB • Migration • Native restore • Data Migration Assistant • Data Migration Service • Transactional Replication

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

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

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

  19. Transactional Replication – Online Migration

  20. Tran. Repl. – Migration from instance

  21. Maintenance tools • Database engine • Query store, DMV, Xevent • Automatic tuning/plan correction • Management tools • Azure CLI, AzureRm.Sql PowerShell • PsDbaTools • Maintenance scripts (community) • Index maintenance solution (Ola Hallengren) • Adaptive Index defrag (Sql Tiger tools) • First Responder Kit • BpCheck (Sql Tiger Tools)

  22. Questions? • jovanpop@microsoft.com • Twitter: @jovanpop_msft • sqlcommunity.slack.com #azure-sql-managedinstance • SQL Managed Instance Feedback • Azure SQL Database Documentation

More Related