Downloaded 22 times
Uploaded byNicholas Vossburg
Azure Database Services for MySQL PostgreSQL and MariaDB
This document summarizes the Azure Database platform for relational databases. It discusses the different service tiers for databases including Basic, General Purpose, and Memory Optimized. It covers security features, high availability, scaling capabilities, backups and monitoring. Methods for migrating databases to Azure like native commands, migration wizards, and replication are also summarized. Best practices for achieving performance are outlined related to network latency, storage, and CPU.
Azure Database Services for MySQL PostgreSQL and MariaDB
- 1.
- 2. > 60%WHY OPEN SOURCE? >40% 90%
- 4. “Judge us bythe actions we have taken in the recent past, our actions today and in the future.” — Satya Nadella, CEO Microsoft
- 5.
- 6.
- 7.
- 8. AZURE RELATIONAL DATABASEPLATFORM PowerBI,AppServices,DataFactory, Analytics,ML,Cognitive,Bot… Global Azure with 43 Regions Azure Compute SQL Data Warehouse Azure Storage SQL Database MariaDB PREVIEWPostgreSQL Flexible: On-demand scaling, Resource governance Trusted: HA/DR, Backup/Restore, Security, Audit, Isolation Intelligent: Advisors, Tuning, Monitoring Database Services Platform MySQL
- 10. SERVICE TIERS Service TierBasic General Purpose Balanced IO and Compute Performance Optimized Memory Optimized Intended Use Case Built for workloads with light compute needs and variable IO performance Ideal for most business workloads offering balanced and scalable compute and storage options Cache more data for faster transaction processing and higher concurrency vCore 1 2 2 4 8 16 32 2 4 8 16 Compute Generation Gen 4, Gen 5 Gen 4, Gen 5 Gen 5 only Storage 5 GB – 1 TB Magnetic Media 5GB – 2TB Remote SSD 5GB – 2TB Remote SSD IOPS Variable 100-6000 IOPS 100-6000 IOPS
- 11. SERVICE TIERS Service TierBasic General Purpose Balanced IO and Compute Performance Optimized Memory Optimized Intended Use Case Built for workloads with light compute needs and variable IO performance Ideal for most business workloads offering balanced and scalable compute and storage options Cache more data for faster transaction processing and higher concurrency vCore 1 2 2 4 8 16 32 2 4 8 16 Compute Generation Gen 4, Gen 5 Gen 4, Gen 5 Gen 5 only Storage 5 GB – 1 TB Magnetic Media 5 GB – 4 TB Remote SSD 5GB – 2TB Remote SSD IOPS Variable 100-6000 IOPS 100-6000 IOPS
- 12. SERVICE TIERS Service TierBasic General Purpose Balanced IO and Compute Performance Optimized Memory Optimized Intended Use Case Built for workloads with light compute needs and variable IO performance Ideal for most business workloads offering balanced and scalable compute and storage options Cache more data for faster transaction processing and higher concurrency vCore 1 2 2 4 8 16 32 2 4 8 16 Compute Generation Gen 4, Gen 5 Gen 4, Gen 5 Gen 5 only Storage 5 GB – 1 TB Magnetic Media 5 GB – 4 TB Remote SSD 5 GB – 4 TB Remote SSD IOPS Variable 100-6000 IOPS 100-6000 IOPS
- 13.
- 14. Control access • SecureSSL connectivity • Server firewall rules • Virtual Network (SE) Protect data • Built-in encryption at-rest for data and backups SECURITY BUILT IN Identity • Native authentication • Threat detection
- 15. VNET SERVICE ENDPOINT Microsoft Azure VirtualNetwork Customer VNET FrontEnd Subnet Not allowed BackEnd Subnet HDI Subnet Gateway HDInsight Not allowed IP ACL V N E T A C L VNET Service EndPoint (Azure MySQL Database) IP ACL V N E T A C L VNET Service EndPoint (Azure MySQL Database) User User On-Premises Express Route Public Peering or Internet (using ACLed NAT IPs) in development Azure MySQL Database Customer Servers Customer Servers Azure PostreSQL Database Internet • Extend VNET private address space to Azure Database for MySQL and PostgreSQL • Easy to configure (uses Microsoft.SQL service tag) • Each VNET service endpoint applies to only one Azure region
- 16. S E CU R E A N D C O M P L I A N T Protect your data with up-to-date security and compliance features with the Azure IP Advantage. SOC1 – Compliant SOC2 – Compliant SOC3 – Compliant ISO 27001:2013 – Compliant ISO 27018:2014 – Compliant CSA STAR Certification – Compliant HIPAA / HITECH Act – Compliant PCI DSS Level 1 – Compliant ISO 27017:2015 – Compliant ISO 27018:2014 – Compliant ISO 9001:2015 – Compliant ISO/IEC 20000-1:2011 – Compliant ISO 22301:2012 – Pending SOC 2 Type 2 CSA STAR Certification Level 1
- 17. BUILT-IN HIGH AVAILABILITY Serverprovisioning and management server=server.mysql.database.azure.com Retry Elastically scale your compute up or down Independently scale up storage as needed seamlessly Use replicas only if you need to! MySQL IP:3306 PGSQL IP:5432 US West Azure Storage MySQL or PostgreSQL Server MySQL or PostgreSQL Server
- 18. = $285 vs$132 == $285 vs $262 = HIGH AVAILABILITY AND SCALE High Availability High Availability
- 19. HIGH AVAILABILITY INAWS RDS VS. ADS AWS RDS with a 99.95% SLA is 2x more expensive* than Azure Database for MySQL/PostgreSQL High Availability High Availability * as of June 2018
- 20. SCALE PERFORMANCE ONTHE FLY Server provisioning and management server=server.mysql.database.azure.com Scale your server compute up or down in seconds! Independently scale up storage as needed MySQL IP:3306 PGSQL IP:5432 US West Azure Storage MySQL or PostgreSQL Server MySQL or PostgreSQL Server
- 21. BACKUP AND RESTORE •Built-in backups • Choose LRS or GRS • Restore from geo-redundant backups for disaster recovery (RPO <= 1 hr) • 1x Backup storage included • PITR up to 35 days (min. 7 days)
- 22. MONITORING AND ALERTING •Built-in monitoring • Configurable alerts • Auto notifications • Enabled for database engine monitoring by default
- 23. MYSQL DATA-IN REPLICATION •Data-in Replication (for migration scenarios) o o Read Read
- 24. MYSQL AND POSTGRESQLREPLICATION • Same region replica (for read scale-out) o • Each replica is a new MySQL/PostgreSQL server • MySQL: Native replication • PostgreSQL: In preview Replica WriteRead Read
- 25. 1. MySQL dumpand PG dump Native commands, no additional setup 2. Migration wizard Native or 3rd party tools 3. Replication Attunity, near-zero down time migration Azure Database Migration Service MIGRATION METHODS - Native way, no additional setup - Use when application can tolerate downtime - Third-party solution, needs setup - Use when application cannot tolerate downtime - Fully-orchestrated migration service - General Availability (H1 2018) here
- 26. KEY LEARNINGS FORACHIEVING BEST PERFORMANCE • Network latency o o • Best practices o o
- 27. KEY LEARNINGS FORACHIEVING BEST PERFORMANCE • Storage o o postgres (pg_stat_statements) • Best practices o o o o o o
- 28. KEY LEARNINGS FORACHIEVING BEST PERFORMANCE • CPU o o postgres (pg_stat_statements) o • Best practices o o o o o
Editor's Notes
- #4 Hiring – each and every new technical hire needs to have OSS skills… Get comfortable, get fluent Please communicate this to your teams
- #8 https://blog.openai.com/scaling-kubernetes-to-2500-nodes/ https://mspoweruser.com/elon-musk-just-made-microsoft-azure-100-cooler-association/ Elon Musk ✔ @elonmusk OpenAI first ever to defeat world's best players in competitive eSports. Vastly more complex than traditional board games like chess & Go. Elon Musk ✔ @elonmusk Would like to express our appreciation to Microsoft for use of their Azure cloud computing platform. This required massive processing power.
- #9 This approach starts with offering choice and flexibility but also a business-driven agile approach to bringing technology to market. Contributing to 3rd party projects such as Linux or Hadoop, or integrating open source technologies in our products, like fluentd, DC/OS or Docker. And, of course, also releasing projects as open source. You’ve heard about .NET Core, PowerShell, Roslyn or VS Code. It’s been quite the transformation, but there’s more to be done. There are hundreds of repos and over 3K people just in the Azure GitHub organization, hosting projects like the Azure SDKs and the Azure CLI but we know the ecosystem expects more “Day 1” open source projects, and we are proactively looking into opportunities to do more with open source. Of course, the ecosystem is a critical part of that too, whether commercial or community partners. And nowhere is this more visible than in our Linux investments. Microsoft employs developers supporting projects such as: Linux kernel, Docker, Apache Software Foundation, Hadoop & YARN, Python Software Foundation.
- #10 Natural for us to extend our PaaS offerings and meeting customers where they are by bringing PostgreSQL as a fully managed service in Azure. At the same time, we want to make sure that Postgres is a first class product within Azure – and we started thinking very early on how best we can bring some of our learnings working on database products to the new Azure Database for Postgres offering. Let’s dive into what we exactly did. “Sunil – you should romance a bit more of this”
- #11 Tedious Set-Up and Maintenance Implementing an on-prem or IaaS deployment of MySQL, PostgreSQL or MariaDB means you need to maintain the underlying OS supporting the database engine. Version upgrades, patching and OS optimizations are all up to the customer to ensure their environment is optimized and protected. With Azure Database Services, OS host updates are maintained by Azure as well as the database engine itself. Optimized for Performance With an on-prem or IaaS installation of MySQL, PostgreSQL, or MariaDB, a knowledgeable DBA is required to tune the installation specifically for the expected usage of the application. Azure Database Service manages both the host OS and database configuration based on the size of the SKU chosen by the user. This ensures that whatever SKU you choose, defaults (such as maximum connections) are optimized for the amount of memory available to your database server. Expensive and complex HA solutions Achieving high-availability with IaaS or on-prem installations require replicas as well as orchestration logic to handle the failure of a master (or the addition of expensive 3rd party solutions). With Azure Database Services, HA is built-in and orchestrated by the Azure Database Management Service which means there is no configuration by the customer necessary. In addition, there are no replicas to maintain with Azure Database Services meaning it is free! Complex to achieve end-to-end security and compliance Azure’s secure platform (encryption at rest, encryption in motion (SSL)) combined with certifications for SOC2, PCI/PCI-DSS, HIPPA and others means that out of the box security and compliance is taken care-of for the customer. Challenging and costly to achieve scale Leveraging our HA infrastructure, scaling your Azure Database Service from one performance tier to another is as simple as moving a slider (or single CLI command) and within 30 seconds, your server is running at the performance necessary for your ever-growing workload. With IaaS or on-prem installations, increasing performance means provisioning new, larger VMs or worse – new HW. Unpredictable costs Azure Database Services billing model provides transparent visibility into the costs to run your server and even gives an estimate monthly cost based on resources provisioned.
- #18 - Virtual Network Service Endpoints
- #19 For Azure Database for MySQL and PostgreSQL, the virtual network rules feature has the following limitations: At present, an Azure Web App in a subnet that has Service Endpoints turned on does not yet function as expected. We are working on enabling this functionality. Until this feature is fully implemented, we recommend that you move your Web App to a different subnet that does not have service endpoints turned on for Azure Database for MySQL and PostgreSQL. In the firewall for your Azure Database for MySQL and PostgreSQL, each virtual network rule references a subnet. All these referenced subnets must be hosted in the same geographic region that hosts the Azure Database for MySQL and PostgreSQL. Each Azure Database for MySQL and PostgreSQL server can have up to 128 ACL entries for any given virtual network. Virtual network rules apply only to Azure Resource Manager virtual networks; and not to classic deployment model networks. Turning ON virtual network service endpoints to Azure Database for MySQL and PostgreSQL also enables the Azure SQL Database services. On the firewall, IP address ranges do apply to the following networking items, but virtual network rules do not: Site-to-Site (S2S) virtual private network (VPN) On-premises via ExpressRoute
- #20 SOC2 - Service Organization Controls standards for operational security ISO 27001 - Information Security Management Standards ISO 27018 - Code of Practice for Protecting Personal Data in the Cloud CSA STAR - Cloud Security Alliance: Security, Trust & Assurance Registry (STAR) PCI DSS Level 1 - Payment Card Industry (PCI) Data Security Standard (DSS) Level 1 Service Provider HIPAA / HITECH Act - Health Insurance Portability and Accountability Act / Health Information Technology for Economic and Clinical Health Act ISO 27017:2015 - Code of Practice for Information Security Controls ISO 27018:2014 – assesses control environment to protect of personal data in the cloud. ISO 9001:2015 Quality Management Systems Standards ISO 22301:2012 Business Continuity Management Standard ISO/IEC 20000-1:2011 Information Technology Service Management
- #21 Setting-up high availability for database servers is hard, requiring either custom code to manage detection/failover, or expensive 3rd party solutions to make it a bit easier. Worse yet, it’s expensive as typically a second database server (replica) is necessary in order to fail-over in a matter of seconds. A second server = 2x the cost for HA. For AWS customers, you cannot receive a SLA from Amazon on your database server unless you have deployed in a multi-AZ, again meaning 2x the cost. Azure Database Services is built upon the SQL Database platform which is a Service Fabric-based PaaS solution. As such, rather than having to boot-up an entire OS stack to bring up a new server (such as in IaaS), Azure Database Services run the database engine in a custom container technology which you can think of as a secure “pico process”. The time it takes to bring-up a new server in this custom container is a matter of seconds. This means that in the event that your database server has hung, or “gone away”, the Azure Database Management Service” detects the failure, brings-up a new server in this lightweight container, maps the new IP address to the DNS name of your instance and maps to your storage. This entire process takes between 30-45 seconds. This is built-in to all performance tiers of Azure Database Services and since a replica instance isn’t needed, there is no additional cost to the customers. In contrast, an AWS RDS server that is deployed in a single AZ would take minutes to start – and that does not account for how you would detect the failure and switch-over
- #22 Customers who are IaaS customers in Azure today to understand that the specs of a VM do not equate directly to the specs of Azure Database Services. The reason is two-fold: Customers do not size a VM based on their typical workload, rather they size wisely to handle workload spikes so as not to impact performance of the application. With Azure Database Services, the ability to scale performance on the fly means they SHOULD size their instance based on typical workload needs and then elastically scale when necessary. This lowers costs. A VM has to support the performance requirements for both the database engine as well as the host OS. With Azure Database Services, the SQLPAL isolated pico-process (mini-OS) significantly lowers the HW needs compared to a VM. So in this example, if I have a D4S_V3 VM with 4 vCPUs and 32GB of SSD, when I choose an Azure Database for MySQL the customer can likely choose a smaller size of 2 vCores with the same storage (and in fact, they would get more storage as the storage for Azure Database Services is dedicated to the database, logs, etc. – no host OS footprint here). The customer can then profile their workload and determine if it meets their performance requirements, and if it does not, they can easily scale-up to the next tier. More importantly, in an IaaS VM implementation, if you want to achieve HA you need a second server (replica). This will double their costs, in this case from $143/mo. to $286/mo. With Azure Database Services with built-in HA, there are no additional replicas needed and as such – there is no cost impact. So to sum up this example, a HA IaaS MySQL VM costs $286/mo., whereas Azure Database for MySQL would cost $132/mo. That’s a saving of $154/mo.
- #23 We have made an implicit decision to price-match AWS RDS in order to be competitive in the market. This means that the price disparity between AWS RDS vs. Azure Database Services is minimal, if at all. In this case, an AWS RDS db.m4.large MySQL instance (2vCPU) with 32GB of SSD would match Azure Database Services (spec-wise) at a 2 vCore General Purpose SKU. However, like the IaaS VM scenario, there is a big difference between AWS RDS and Azure Database Services. AWS RDS only offers a 99.95% uptime SLA if the database servers is deployed in a Multi-Availability Zone. If they deploy in a single-AZ, there is NO SLA from AWS. With Azure Database Services, we provide a 99.99% SLA on all instances without the need to have additional replicas. So in this case, AWS would cost you $264/mo. whereas Azure Database for MySQL would cost only $132/mo. A $132/mo. savings!
- #24 Leveraging the same technology that powers our High-Availability, Azure Database Services provides the ability to scale performance on the fly with little/no application downtime. Scaling is as simple as literally changing a slider to increase or decrease vCores, and clicking OK. Scaling effectively brings-up a new server at the new performance tier, and switches over to the storage on the previous server and maps to the existing DNS name, all in about 15-20 seconds. This means that customers do not have to worry so much about the size of the server they need coming into Azure. They can choose a SKU to start and then monitor the performance of their database server to determine if they should either scale-up or scale-down their server based on their workload. Customers can then set alerts on metrics such as CPU or Memory usage to notify them in the event they need to scale-up their server for workload spikes.
- #27 https://docs.microsoft.com/en-us/azure/mysql/concepts-data-in-replication MySQL Read Replicas are used by customers to achieve read scale-out, which helps mitigate performance impact on the primary server for workloads that are extremely read-heavy. For Azure Database Services, we are introducing Read Replicas for MySQL in Preview at the end of March. We are using native engine replication which uses replication of the binlog to keep the replicas up to date with the Master server. We provide 2 replication scenarios for Azure Database Services for MySQL: Data-In Replication: This is the ability to set-up a replica server in Azure linked to a master server in another location. This could be an Azure IaaS MySQL VM, on-premises MySQL server or a MySQL server in another public cloud. This is primarily targeted at enabling customers to migrate their MySQL server from another location/service into Azure Database for MySQL. By using replication for this migration scenario, there is little/no downtime for the customer’s application. Once the replica is seeded from the master, it is then kept asynchronously up to date, meaning the customer can move their application over by simply changing their connection string. Same-Region Replica: This is standard MySQL read replication enabling customers to set up to 5 read-replicas in the same Azure Region as their Master server. This is limited to 5 replicas to minimize performance impact on the Master, as each replica server has some performance impact on the Master server, which is typically between 10-15%. (Note that AWS also only supports up to 5 replicas in RDS today) Over the next 6 months we will be bringing PostgreSQL native engine replication to Azure Database Services as well. In addition, at this time we will be introducing the ability to setup your replica servers in Azure regions outside of the Region your Master server is located, enabling world-wide read scale-out.
- #28 https://docs.microsoft.com/en-us/azure/mysql/concepts-read-replicas https://dev.mysql.com/doc/refman/5.7/en/replication-features.html MySQL Read Replicas are used by customers to achieve read scale-out, which helps mitigate performance impact on the primary server for workloads that are extremely read-heavy. For Azure Database Services, we are introducing Read Replicas for MySQL in Preview at the end of March. We are using native engine replication which uses replication of the binlog to keep the replicas up to date with the Master server. We provide 2 replication scenarios for Azure Database Services for MySQL: Data-In Replication: This is the ability to set-up a replica server in Azure linked to a master server in another location. This could be an Azure IaaS MySQL VM, on-premises MySQL server or a MySQL server in another public cloud. This is primarily targeted at enabling customers to migrate their MySQL server from another location/service into Azure Database for MySQL. By using replication for this migration scenario, there is little/no downtime for the customer’s application. Once the replica is seeded from the master, it is then kept asynchronously up to date, meaning the customer can move their application over by simply changing their connection string. Same-Region Replica: This is standard MySQL read replication enabling customers to set up to 5 read-replicas in the same Azure Region as their Master server. This is limited to 5 replicas to minimize performance impact on the Master, as each replica server has some performance impact on the Master server, which is typically between 10-15%. (Note that AWS also only supports up to 5 replicas in RDS today) Over the next 6 months we will be bringing PostgreSQL native engine replication to Azure Database Services as well. In addition, at this time we will be introducing the ability to setup your replica servers in Azure regions outside of the Region your Master server is located, enabling world-wide read scale-out.
- #29 Here are other migration options in addition to Data-In replication for MySQL as we just talked about. Of course customers can use the familiar tools that exist today for dumping your database and importing into Azure, but of course this implies downtime for your application as once your dump your database, changes cannot be made to the original server until you import your data into the new server in Azure. This isn’t acceptable for most workloads – but is an option. Attunity Replicate for Microsoft Migrations is a free-offering from Microsoft and Attunity that helps orchestrate the migration of data only from sources outside of Azure into Azure Database Services. This is similar to Data-In replication for MySQL, but provides a simple interface to set-up that is literally a “click and drag” migration operation. Additionally, this is extremely useful for PostgreSQL customers as there is currently not a Data-In migration solution for them today. However there are a few limitations, primarily that sources from other public clouds such as AWS or GCP are not supported. Later this year, before the 2nd have specifically, we’re excited to bring forth the Public Preview of the Azure Database Migration service for MySQL and PostgreSQL. Unlike the Attunity solution above, DMS will provide additional source options such as AWS RDS.