What developers are doing with Postgres & MySQL on Azure | Build 2020 | Samay Sharma, Andrea Lam

Join us to learn how developers like you are architecting applications on Postgres & MySQL open source databases on Azure—for use cases like e-commerce, web content, time series data, real-time analytics, & multi-tenant SaaS

Transcript

1. What developers are doing with Postgres
   & MySQL on Azure
   Andrea Lam
   Samay Sharma
   

   View Slide

2. View Slide

3. Agenda
   Why open source
   databases on
   Azure?
   Common patterns
   for Postgres
   What devs are
   doing with
   MySQL on Azure
   Seamless cloud
   migrations
   Open
   Source
   

   View Slide

4. 1K
   800
   600
   400
   200
   100
   Score (logarithmic scale
   2013 2014 2015 2016 2017 2018 2019 2020
   Growing In Popularity
   https://db-engines.com/en/ranking_trend
   PostgreSQL
   MySQL
   

   View Slide

5. PostgreSQL & MariaDB
   are fastest growing
   relational database
   management systems
   MySQL named 2019
   “Database of the year”
   by DB-Engines.com
   PostgreSQL named
   2017 and 2018
   “Database of the year”
   by DB-Engines.com
   Postgres one of most
   loved & wanted databases
   in Stack Overflow
   Developer Survey 2019
   80%
   Growing In Popularity
   

   View Slide

6. Rich feature set
   Proven resilience
   & stability
   Open source
   

   View Slide

7. Rich feature set
   Proven resilience
   & stability
   Open source
   Fully
   managed
   Highly
   scalable
   Security &
   Compliance
   Intelligent
   performance
   

   View Slide

8. Rich feature set
   Proven resilience
   & stability
   Open source
   Fully
   managed
   Highly
   scalable
   Security &
   Compliance
   Intelligent
   performance
   

   View Slide

9. Available on Microsoft Azure
   Rich feature set
   Proven resilience
   & stability
   Open source
   Fully
   managed
   Highly
   scalable
   Security &
   Compliance
   Intelligent
   performance
   

   View Slide

10. Oracle to Postgres
    1
    

    View Slide

11. Oracle to Postgres, a
    common pattern we
    are seeing more &
    more of
    

    View Slide

12. Modernize data estate by migrating Oracle > Postgres
    Rich PostgreSQL features & data types including geospatial
    Fast and low risk PL/SQL to PL/PgSQL migration
    Minimal downtime via Ora2PG & Azure Database Migration
    Reduce TCO by over 90% & avoid vendor lock-in
    Fully-managed database service
    

    View Slide

13. Modernize data estate by migrating Oracle > Postgres
    Rich PostgreSQL features & data types including geospatial
    Fast and low risk PL/SQL to PL/PgSQL migration
    Minimal downtime via Ora2PG & Azure Database Migration
    Reduce TCO by over 90% & avoid vendor lock-in
    Fully-managed database service
    

    View Slide

14. Modernize data estate by migrating Oracle > Postgres
    Fast and low risk PL/SQL to PL/PgSQL migration
    Minimal downtime via Ora2PG & Azure Database Migration
    Reduce TCO by over 90% & avoid vendor lock-in
    Fully-managed database service
    Rich PostgreSQL features & data types including geospatial
    

    View Slide

15. Modernize data estate by migrating Oracle > Postgres
    Rich PostgreSQL features & data types including geospatial
    Minimal downtime via Ora2PG & Azure Database Migration
    Reduce TCO by over 90% & avoid vendor lock-in
    Fully-managed database service
    Fast and low risk PL/SQL to PL/PgSQL migration
    

    View Slide

16. Modernize data estate by migrating Oracle > Postgres
    Rich PostgreSQL features & data types including geospatial
    Fast and low risk PL/SQL to PL/PgSQL migration
    Reduce TCO by over 90% & avoid vendor lock-in
    Fully-managed database service
    Minimal downtime via Ora2PG & Azure Database Migration
    

    View Slide

17. Migrating from
    Oracle to Postgres |
    The Process
    Post-migration
    Migration
    Migrate
    schema,
    data, &
    objects
    Data
    sync
    Cutover
    Pre-migration
    Discover Assess Connect Remediate
    applications
    Optimize
    Run functional
    & performance
    tests
    

    View Slide

18. Operational Real-time Analytics
    2
    

    View Slide

19. Scalable real-time
    analytics with
    Hyperscale (Citus) on
    Azure Database for
    PostgreSQL
    

    View Slide

20. aka.ms/citus
    

    View Slide

21. Why build
    real-time analytics
    on Postgres?
    

    View Slide

22. Real-time ingest &
    complex analytics in
    same database
    Why build
    real-time analytics
    on Postgres?
    

    View Slide

23. Real-time ingest &
    complex analytics in
    same database
    Data types,
    operators, functions
    Why build
    real-time analytics
    on Postgres?
    

    View Slide

24. Real-time ingest &
    complex analytics in
    same database
    Data types,
    operators, functions
    Extensions:
    pg_partman,
    pg_cron, HLL, topn
    Why build
    real-time analytics
    on Postgres?
    

    View Slide

25. Real-time ingest &
    complex analytics in
    same database
    Data types,
    operators, functions
    Extensions:
    pg_partman,
    pg_cron, HLL, topn
    Pre-aggregations
    & rollups
    Why build
    real-time analytics
    on Postgres?
    

    View Slide

26. Real-time ingest &
    complex analytics in
    same database
    Data types,
    operators, functions
    Extensions:
    pg_partman,
    pg_cron, HLL, topn
    Pre-aggregations
    & rollups
    Connectivity with rest
    of data pipeline
    Why build
    real-time analytics
    on Postgres?
    

    View Slide

27. Real-time ingest &
    complex analytics in
    same database
    Data types,
    operators, functions
    Extensions:
    pg_partman,
    pg_cron, HLL, topn
    Pre-aggregations
    & rollups
    Connectivity with rest
    of data pipeline
    Scale to hundreds
    of TBs with
    Hyperscale (Citus)
    Why build
    real-time analytics
    on Postgres?
    

    View Slide

28. Real-time
    analytics
    architectures
    with
    Hyperscale
    (Citus)
    Notification Hubs
    Devices
    Raw Events
    Azure
    Databricks
    Scheduled
    process
    Browser Aggregations
    App Service/AKS
    4
    Hyperscale (Citus) in Azure
    Database for PostgreSQL
    4
    3
    5
    2
    6
    1
    Event Hubs
    

    View Slide

29. 4
    4
    5
    1
    6
    3
    2
    Notification Hubs
    Devices
    Raw Events
    Azure
    Databricks
    Scheduled
    process
    Browser Aggregations
    Hyperscale (Citus) in Azure
    Database for PostgreSQL
    Event Hubs
    App Service/AKS
    Real-time
    analytics
    architectures
    with
    Hyperscale
    (Citus)
    

    View Slide

30. 3
    5
    2
    1
    6
    4
    4
    Notification Hubs
    Devices
    Raw Events
    Azure
    Databricks
    Scheduled
    process
    Browser Aggregations
    Hyperscale (Citus) in Azure
    Database for PostgreSQL
    Event Hubs
    App Service/AKS
    Real-time
    analytics
    architectures
    with
    Hyperscale
    (Citus)
    

    View Slide

31. 4
    4
    3
    2
    1
    5
    6
    Notification Hubs
    Devices
    Raw Events
    Azure
    Databricks
    Scheduled
    process
    Browser Aggregations
    Hyperscale (Citus) in Azure
    Database for PostgreSQL
    Event Hubs
    App Service/AKS
    Real-time
    analytics
    architectures
    with
    Hyperscale
    (Citus)
    

    View Slide

32. 4
    4
    3
    5
    2
    1
    6
    Notification Hubs
    Devices
    Raw Events
    Azure
    Databricks
    Scheduled
    process
    Browser Aggregations
    Hyperscale (Citus) in Azure
    Database for PostgreSQL
    Event Hubs
    App Service/AKS
    Real-time
    analytics
    architectures
    with
    Hyperscale
    (Citus)
    

    View Slide

33. 4
    4
    3
    5
    2
    1
    6
    Notification Hubs
    Devices
    Raw Events
    Azure
    Databricks
    Scheduled
    process
    Browser Aggregations
    Hyperscale (Citus) in Azure
    Database for PostgreSQL
    Event Hubs
    App Service/AKS
    Real-time
    analytics
    architectures
    with
    Hyperscale
    (Citus)
    

    View Slide

34. Magento ecommerce on MySQL
    3
    

    View Slide

35. Build ecommerce
    apps that delight
    customers with
    Magento & MySQL
    An open source
    e-commerce solution
    developed in PHP
    

    View Slide

36. AKS Kubernetes Cluster
    NGINX 1.15.5 Ingress
    Cache service (2 pod instances)
    Varnish Pod Varnish Pod
    Web (Magento) service
    WEB Pod
    App (Magento) service
    APP Pod
    Redis-cache service
    (1 pod instance)
    Redis-session service
    (1 pod instance)
    Redis Cache (1)
    Redis Cache (1)
    Magento uses
    Azure Database for
    MySQL to store
    inventory catalog,
    order, and customer
    information
    

    View Slide

37. Built-in HA for 99.99% SLA fault
    tolerant performance
    Transactions in 500 milliseconds
    with 20K IOPS and 16 TB of
    storage
    Read-replicas to scale out read-
    only workloads for analytical
    platforms
    AKS Kubernetes Cluster
    NGINX 1.15.5 Ingress
    Cache service (2 pod instances)
    Varnish Pod Varnish Pod
    Web (Magento) service
    WEB Pod
    App (Magento) service
    APP Pod
    Redis-cache service
    (1 pod instance)
    Redis-session service
    (1 pod instance)
    Redis Cache (1)
    Redis Cache (1)
    Azure Database for
    MySQL gives Magento
    (and ecommerce
    customers)
    

    View Slide

38. Built-in HA for 99.99% SLA fault
    tolerant performance
    Transactions in 500 milliseconds
    with 20K IOPS and 16 TB of
    storage
    Read-replicas to scale out read-
    only workloads for analytical
    platforms
    AKS Kubernetes Cluster
    NGINX 1.15.5 Ingress
    Cache service (2 pod instances)
    Varnish Pod Varnish Pod
    Web (Magento) service
    WEB Pod
    App (Magento) service
    APP Pod
    Redis-cache service
    (1 pod instance)
    Redis-session service
    (1 pod instance)
    Redis Cache (1)
    Redis Cache (1)
    Azure Database for
    MySQL gives Magento
    (and ecommerce
    customers)
    

    View Slide

39. Built-in HA for 99.99% SLA fault
    tolerant performance
    Transactions in 500 milliseconds
    with 20K IOPS and 16 TB of
    storage
    Read-replicas to scale out read-
    only workloads for analytical
    platforms
    AKS Kubernetes Cluster
    NGINX 1.15.5 Ingress
    Cache service (2 pod instances)
    Varnish Pod Varnish Pod
    Web (Magento) service
    WEB Pod
    App (Magento) service
    APP Pod
    Redis-cache service
    (1 pod instance)
    Redis-session service
    (1 pod instance)
    Redis Cache (1)
    Redis Cache (1)
    Azure Database for
    MySQL gives Magento
    (and ecommerce
    customers)
    

    View Slide

40. Built-in HA for 99.99% SLA fault
    tolerant performance
    Transactions in 500 milliseconds
    with 20K IOPS and 16 TB of
    storage
    Read-replicas to scale out read-
    only workloads for analytical
    platforms
    AKS Kubernetes Cluster
    NGINX 1.15.5 Ingress
    Cache service (2 pod instances)
    Varnish Pod Varnish Pod
    Web (Magento) service
    WEB Pod
    App (Magento) service
    APP Pod
    Redis-cache service
    (1 pod instance)
    Redis-session service
    (1 pod instance)
    Redis Cache (1)
    Redis Cache (1)
    Azure Database for
    MySQL gives Magento
    (and ecommerce
    customers)
    

    View Slide

41. aka.ms/azure-magento
    

    View Slide

42. Moodle online learning
    on MySQL
    4
    

    View Slide

43. Deliver online
    educational programs
    with Moodle
    Allows educators to manage
    quizzes, assignments, and
    communicate with students
    Moodle: open source learning
    management solution
    developed in PHP
    

    View Slide

44. And yes, Moodle—like MySQL—is open source
    

    View Slide

45. Azure
    Admin (SSH) IP
    VNet
    Gluster FS
    Controller VM
    (w/ opt. NFS vol.)
    Elastic VMS
    Azure Database
    for MySQL
    Moodle deployment architecture on Azure
    

    View Slide

46. Azure
    Admin (SSH) IP
    Autoscaling VMSS
    web frontend
    VNet
    Gluster FS
    Controller VM
    (w/ opt. NFS vol.)
    Elastic VMS
    Azure Database
    for MySQL
    Moodle deployment architecture on Azure
    

    View Slide

47. Azure
    Admin (SSH) IP
    Autoscaling VMSS
    web frontend
    VNet
    Gluster FS
    Controller VM
    (w/ opt. NFS vol.)
    Elastic VMS
    Azure Database
    for MySQL
    Moodle deployment architecture on Azure
    

    View Slide

48. Azure
    VNet
    Moodle deployment architecture on Azure
    

    View Slide

49. Moodle uses Azure Database for MySQL to store
    student scores, grades, & student info
    Automated patching &
    backups
    Integrated with native
    MySQL tools
    Concurrent user access
    Moodle deployment architecture on Azure
    Azure
    VNet
    

    View Slide

50. aka.ms/azure-moodle
    

    View Slide

51. Migrating data to Azure
    5
    

    View Slide

52. aka.ms/azure-mysql-migrate aka.ms/azure-postgres-migrate
    Seamless data migrations to the cloud
    

    View Slide

53. Postgres Quickstart Docs:
    https://aka.ms/azure-postgres-quickstart
    https://aka.ms/azure-mysql-quickstart
    Postgres Resources
    

    View Slide

54. Postgres Quickstart Docs:
    https://aka.ms/azure-postgres-quickstart
    https://aka.ms/azure-mysql-quickstart
    Azure Postgres Blog:
    https://aka.ms/azure-postgres-blog
    https://aka.ms/azure-mysql-blog
    Postgres Resources
    

    View Slide

55. Postgres Quickstart Docs:
    https://aka.ms/azure-postgres-quickstart
    https://aka.ms/azure-mysql-quickstart
    Azure Postgres Blog:
    https://aka.ms/azure-postgres-blog
    https://aka.ms/azure-mysql-blog
    @AzureDBMySQL citusdata/citus
    @AzureDBPostgres
    AskAzureDBforMySQL@service.microsoft.com AskAzureDBforPostgreSQL@service.microsoft.com
    Postgres Resources
    

    View Slide

56. © Copyright Microsoft Corporation. All rights reserved.
    Andrea Lam
    @andreajlam
    @AzureDBMySQL
    thank you
    merci
    grazie
    ध"यवाद
    ευχαριστώ
    gracias
    ధన#$ా&'ల)
    谢谢
    @samay_sharma
    @AzureDBPostgres
    @citusdata
    Samay Sharma
    thank you
    merci
    grazie
    ध"यवाद
    ευχαριστώ
    gracias
    ధన#$ా&'ల)
    谢谢
    

    View Slide

57. Any Questions?
    

    View Slide

58. © Copyright Microsoft Corporation. All rights reserved.
    

    View Slide