Modernize your data on Azure Database for Postgres & MySQL | Data Platform Summit 2020 | Sunil Agarwal & Parikshit Savjani

Are you looking to migrate it to the cloud, gain operational efficiency, and yet more importantly, not give up control of your database? Look no further. In this session you will learn how the new Flexible Server for Azure Postgres or Azure MySQL gives you full control for choosing your configuration, optimize cost, manage automatic maintenance, with flexible high availability options for your databases. For each of these capabilities, we will cover the ‘why’ and how using with simple demos to make your learning real.

Transcript

1. Modernize Your Data on Azure Database for Postgres And MySQL Flexible Server Parikshit Savjani, Principal PM Manager Azure Database for MySQL & MariaDB Sunil Agarwal, Principal PM Manager Azure Database for PostgreSQL @talktosavjani @S_u_n_e_e_l
2. Agenda Why Flexible Server? Flexible Server offering for PostgreSQL and MySQL Choosing the right deployment option
3. Deployment options for MySQL & Postgres Hyperscale (Citus) Ideal for multi-tenant applications and real-time analytical workloads that need sub-second response. Supports both transactional/operational workloads and hybrid transactional analytics workloads. Single Server Enterprise ready, fully managed community PostgreSQL server with up to 64 vCores, optional geospatial support, full-text search and more Flexible Server (preview) Maximum control with a simplified developer experience. Supports custom maintenance windows, zone redundant high availability, and simple cost optimization. Arc Enabled Hyperscale (Citus) Deployed on the infrastructure of your choice(on-premises/edge/multi-cloud), it is ideal for multi-tenant applications, transactional/operational workloads and real-time analytical workloads that need sub-second response.
4. Why Flexible Server? Some common questions – Why introduce Flexible Server? – Is Flexible Server @feature parity with Single Server? – How is the workload performance on Flexible Server – When will Flexible Server GA? – Is Flexible Server more expensive? – Why should I deploy my workload on Flexible Server – What is the difference between MySQL and PostgreSQL Flexible Server? – How will I migrate from Single Server to Flexible Server?
5. Single Server: Architecture and Value Proposition Notable Facts – GA’d 2018 – Significant growth YOY – Many Tier-1 workloads (e.g. HSBC, Adobe) – Intelligent Performance – Integration with Azure Infra (e.g. App Service, Kubernetes) – Key Engineering Investments since GA • Integration with AAD • Security: vNET,BYOK and Automatic Threat Protection (ATP) • Large storage 16 TB and 20k IOPS • Read Replicas • Logical Encoding (WAL2JSON) Region container container PG GP SSD gateway Built for Tradeoffs • High Availability • Elasticity • Security • Integration in ecosystem • Industry leading TCO • Performance (latency) • Familiarity
6. Single Server Challenge: Higher Latency Key Challenges – Connection Indirection through GW – No AZ (Availability Zones) locality Key Points Ø Limited control on resource placement Ø Once deployed, the placement can change due to SLO upgrade/downgrade, Failures Learnings Ø Eliminate GW Ø Provide AZ colocation
7. Single Server Challenge: Monthly Maintenance Window System Controlled • Multiple changes are batched together to minimize interruptions • Follows Azure Safe Deployment Policy (SDP) • Advanced Notification 72 hours. Customer can’t pause • Total span time 15 hours (5 pm to 8 am) but actual maintenance for a server (minutes) • For zero-day vulnerability, we reserve the right to initiate maintenance without 72 hours advance notification. Challenge: Customer has limited control to align maintenance to their workload patterns Planned Improvements • We are in the process of reducing maintenance window to 10 hours in public rings • Premium rings to selectively onboard customers with 4 hours maintenance window Learnings Ø Provide Customer Controlled Maintenance Window
8. Single Server Challenge: Connections Key Challenges – Connection requires <username>@servername – Establishing a new connection expensive – Hard limit on max number of Connections per SKU Learnings Ø Remove @<servername> Ø Reduce time to establish new connection Ø Decouple max connections from SKU Ø Integrate PGBouncer in Server Best Practice Recommendations • Use connection pooling – PGBouncer (Azure Kubernetes offers PGBouncer sidecar) • Connection retry logic should have back off logic
9. Single Server Challenge: AZ Resilient HA HA and current challenge – Resiliency with compute failure – Resiliency with storage failure – Failure of AZ can lead to downtime Azure Storage Server Secure container Secure container Secure container … X Server Secure container X Learnings Ø AZ resilient HA with Automatic failover with no data loss
10. DEMO: SINGLE SERVER
11. Flexible Server Customer Pain Points: Addressed ü Lower Latency – AZ colocation, no GW ü Performance – Good out-of-box performance. ü Customer controlled maintenance schedule ü Availability Zone resiliency ü Telemetry Accuracy/Richness: Example – IOPS ü Connections: Configure up to 5000 connections ü Developer Challenges ü Connection string does not require @server as part of username ü Simplified Provisioning workflow with good defaults ü Stop/Start the service when not in-use ü Competitive starter SKU (MySQL $12, PG $25) ü Scale up/down across all SKUs ü Integrated PGBouncer ü Guided E2E developer experience with AKS and App Service
12. Data Platform Virtual Summit 2020 is a community initiative by DataPlatformGeeks FLEXIBLE SERVER OFFERING FOR POSTGRESQL AND MYSQL
13. North Europe Region – East US West US 2 AZ1 AZ2 AZ3 Flexible Server Architecture Flexible Server Availability Zone 1 Availability Zone 2 Availability Zone 3 MySQL or PostgreSQL Linux VM Azure VM AKS App Service Premium Storage Data, Logs Backup Storage
14. Out of Box Performance & Latency Note: 1 Test configuration: US East region, 4 vCore General Purpose servers Select 1 Test Read Only Test Read Write Test Latency (ms) Transactions Per Second (TPS) 2.76 ms 0.54 12k 59k Single Server Flexible Server 2.77 ms 0.86 12k 37k Single Server Flexible Server 26.13 ms 8.83 1.2k 3.6k Single Server Flexible Server 80% reduction 70% reduction 66% reduction +390% +220% +200 % Measures connection latency Indicative of perf for read-only workloads Indicative of perf for read + write workloads "It's two times as fast" "Nothing needs to be changed in our product side almost the same experience as RDS " “I did very simple benchmark, the latency was 4-5x better and tps 3-4x better” Early Adopter Feedback Flexible Server Flexible Server Flexible Server Single Server Single Server Single Server
15. Out of Box Performance & Latency Note: 1 Test configuration: US East region, 4 vCore General Purpose servers Select 1 Test Read Only Test Read Write Test Latency (ms) Transactions Per Second (TPS) 2.76 ms 0.54 12k 59k Single Server Flexible Server 2.77 ms 0.86 12k 37k Single Server Flexible Server 26.13 ms 8.83 1.2k 3.6k Single Server Flexible Server 80% reduction 70% reduction 66% reduction +390% +220% +200 % Measures connection latency Indicative of perf for read-only workloads Indicative of perf for read + write workloads Flexible Server Flexible Server Flexible Server Single Server Single Server Single Server
16. Early Adopter Feedback for Flexible Server "It's two times as fast" "Nothing needs to be changed in our product side almost the same experience as RDS " “I did very simple benchmark, the latency was 4-5x better and tps 3-4x better”
17. SIMPLIFIED DEVELOPER EXPERIENCE
18. Easy Create – Smart defaults
19. Easy CLI Experience Setup Environment (Once) 1. az account set --subscription “Your Azure subscription name” 2. az group create --name resource-group-name --location southeastasia 3. curl ifconfig.me (What is my IP address) Provision a new Flexible Server 4. az mysql flexible-server create --resource-group resource-group-name --name servername - -admin-username user --admin-password “password” --version 5.7 --sku-name Standard_B1ms --public-access <your external client IP address> OR az mysql flexible-server create –l <Azure region>
20. NATIVE CONNECTION EXPERIENCE (IT JUST WORKS !!!) mysql -h mydemoserver.mysql.database.azure.com -u myadmin –p password psql -h mydemoserver.postgres.database.azure.com -u mydemouser –p password
21. Simple Cost Optimization for development needs • Use low-cost Burstable SKU starting $13 per month for all development needs.
22. Simple Cost Optimization for development needs • Use low-cost Burstable SKU starting $13 per month for all development needs. • Stop the Server when not in use. Pay for storage only while the server is stopped
23. Demo DEMO: Simplified deployment of Flexible Server
24. MAXIMUM CONTROL FOR YOUR DATABASES
25. Control your planned maintenance • System defined schedule or Custom schedule for patching • Schedule your maintenance per server • 1-hour maintenance window • Downtime less than 60 seconds • Next planned maintenance schedule only after 30 days • 5 days advance notification
26. Network Isolation
27. More Server Parameter Control
28. ZONE RESILIENCY
29. Region – East US Zone Redundant HA Architecture in Flexible Server Backup storage Availability Zone 1 Flexible Server (Primary) MySQL or PostgreSQL Linux VM Premium Storage Data, Logs Azure VM AKS App Service v Availability Zone 2 Flexible Server (Standby) Linux VM Premium Storage Data, Logs Azure VM AKS App Service Backup Storage Synchronous Replication Backup Storage MySQL or PostgreSQL
30. Demo DEMO: Maximum Control and Zone Resiliency with Flexible Server
31. Flexible Server: Key Takeaway Build resilient apps across availability zones Maximum control for your databases Simplified developer experience
32. When to use Single Server—vs. Flexible Server?
33. When to use Single Server vs. Flexible Server (Preview) Single Server Flexible Server (Preview) Database Version § MySQL 5.7, 8 § Postgres 9.5, 9.6, 10, 11 § MySQL 5.7 § MySQL 8.0 (planned) § Postgres 11, 12 § Postgres 13 (planned)
34. When to use Single Server vs. Flexible Server (Preview) Single Server Flexible Server (Preview) Database Version § MySQL 5.7, 8 § Postgres 9.5, 9.6, 10, 11 § MySQL 5.7 § MySQL 8.0 (planned) § Postgres 11, 12 § Postgres 13 (planned) High Availability Built-in HA (Single-AZ) Zone Redundant HA
35. When to use Single Server vs. Flexible Server (Preview) Single Server Flexible Server (Preview) Database Version § MySQL 5.7, 8 § Postgres 9.5, 9.6, 10, 11 § MySQL 5.7 § MySQL 8.0 (planned) § Postgres 11, 12 § Postgres 13 (planned) High Availability Built-in HA (Single-AZ) Zone Redundant HA Scheduled Maintenance System scheduled System or custom scheduled
36. When to use Single Server vs. Flexible Server (Preview) Single Server Flexible Server (Preview) Database Version § MySQL 5.7, 8 § Postgres 9.5, 9.6, 10, 11 § MySQL 5.7 § MySQL 8.0 (planned) § Postgres 11, 12 § Postgres 13 (planned) High Availability Built-in HA (Single-AZ) Zone Redundant HA Scheduled Maintenance System scheduled System or custom scheduled Stop/Start O P
37. When to use Single Server vs. Flexible Server (Preview) Single Server Flexible Server (Preview) Database Version § MySQL 5.7, 8 § Postgres 9.5, 9.6, 10, 11 § MySQL 5.7 § MySQL 8.0 (planned) § Postgres 11, 12 § Postgres 13 (planned) High Availability Built-in HA (Single-AZ) Zone Redundant HA Scheduled Maintenance System scheduled System or custom scheduled Stop/Start O P PITR P P
38. When to use Single Server vs. Flexible Server (Preview) Single Server Flexible Server (Preview) Database Version § MySQL 5.7, 8 § Postgres 9.5, 9.6, 10, 11 § MySQL 5.7 § MySQL 8.0 (planned) § Postgres 11, 12 § Postgres 13 (planned) High Availability Built-in HA (Single-AZ) Zone Redundant HA Scheduled Maintenance System scheduled System or custom scheduled Stop/Start O P PITR P P Network Isolation P Private Link P VNET Integration
39. When to use Single Server vs. Flexible Server (Preview) Single Server Flexible Server (Preview) Database Version § MySQL 5.7, 8 § Postgres 9.5, 9.6, 10, 11 § MySQL 5.7 § MySQL 8.0 (planned) § Postgres 11, 12 § Postgres 13 (planned) High Availability Built-in HA (Single-AZ) Zone Redundant HA Scheduled Maintenance System scheduled System or custom scheduled Stop/Start O P PITR P P Network Isolation P Private Link P VNET Integration Customer managed keys for Encryption P O (planned) Reserved Instances (RI) P O (planned) Cross region replicas P O (planned)
40. When to use Single Server vs. Flexible Server (Preview) Single Server Flexible Server (Preview) Database Version § MySQL 5.7, 8 § Postgres 9.5, 9.6, 10, 11 § MySQL 5.7 § MySQL 8.0 (planned) § Postgres 11, 12 § Postgres 13 (planned) High Availability Built-in HA (Single-AZ) Zone Redundant HA Scheduled Maintenance System scheduled System or custom scheduled Stop/Start O P PITR P P Network Isolation P Private Link P VNET Integration Customer managed keys for Encryption P O (planned) Reserved Instances (RI) P O (planned) Cross region replicas P O (planned) Uptime SLA 99.99% O (planned)
41. When to use Single Server vs. Flexible Server (Preview) Single Server Flexible Server (Preview) Database Version § MySQL 5.7, 8 § Postgres 9.5, 9.6, 10, 11 § MySQL 5.7 § MySQL 8.0 (planned) § Postgres 11, 12 § Postgres 13 (planned) High Availability Built-in HA (Single-AZ) Zone Redundant HA Scheduled Maintenance System scheduled System or custom scheduled Stop/Start O P PITR P P Network Isolation P Private Link P VNET Integration Customer managed keys for Encryption P O (planned) Reserved Instances (RI) P O (planned) Cross region replicas P O (planned) Uptime SLA 99.99% O (planned)
42. Frequently asked questions • When will Flexible Server GA?
43. Frequently asked questions • When will Flexible Server GA? • Is Flexible Server available at same cost as Single Server?
44. Frequently asked questions • When will Flexible Server GA? • Is Flexible Server available at same cost as Single Server? • Is Flexible Server available in all Azure regions today
45. Frequently asked questions • When will Flexible Server GA? • Is Flexible Server available at same cost as Single Server? • Is Flexible Server available in all Azure regions today • How will I migrate from Single Server to Flexible Server?
46. Frequently asked questions • When will Flexible Server GA? • Is Flexible Server available at same cost as Single Server? • Is Flexible Server available in all Azure regions today • How will I migrate from Single Server to Flexible Server? • Where can I learn more information and news about Flexible Server?
47. aka.ms/postgres-flexible-server-docs MySQL Postgres aka.ms/mysql-flexible-server-docs What is Flexible Server for Postgres What is Flexible Server for MySQL aka.ms/postgres-cloud9 aka.ms/mysql-tenthline AskAzureDBforMySQL@service.microsoft.com AskAzureDBforPostgreSQL@service.microsoft.com
48. OSS Database Sessions in DPS 2020 • Best Practices for Migrating your workloads to Azure Database for MySQL and PostgreSQL Arun Kumar Thiagarajan and Sunil Agarwal • Build Modern Cloud Native applications with Kubernetes and OSS Databases on Azure Sunitha Muthukrishna • Turbocharge your MySQL engine to make it fast, resilient and scalable Amol Bhatnagar • What is Azure Arc enabled PostgreSQL Hyperscale Jean-Yves Devant (JY)
49. Data Platform Virtual Summit 2020 is a community initiative by DataPlatformGeeks Sunil Agarwal @S_u_n_e_e_l @AzureDBPostgreSQL @talktosavjani @AzureDBMySQL Parikshit Savjani thank you merci grazie ध"यवाद ευχαριστώ gracias ధన#$ా&'ల) 谢谢
50. Any Questions?