Azure Classroom Series – 24/Nov/2021

Azure SQL Databases Contd

• Azure SQL Database

  • is not part of your vnet, you need to create a private endpoint to access azure sql database privately
  • Doesnt have all the features of SQL Server Enterprise and it will be difficult for customers who are using unsupported features of Azure SQL to move to Azure SQL Database

• Azure SQL Managed Instance:

  • This is fully compatible with on-prem SQL Server,
  • The Azure SQL Managed instance is par of the virtual network
  • Creation:

• Similarities between Azure SQL Database and Azure SQL Managed Instance

  • Managment: Both will have support for
    • automated patching and version updates
    • automated backups
    • high availability
  • Back up: Both supports automatic backup, Full backups are taken every 7 days, differntial backups 12 hours & log backup every 5-10 mins. Backup retention is 7 days defualt & max 35 days
  • Availability: 99.99-99.995% availability is guaranteed for every database
  • SQL MI: 99.99% availability is guranteed for every database
  • Host Accessibility: There is no direct control over underlying comput server.
  • License: Both have built-in license model with Pay as You go

• Key Differences between Azure SQL Database and Azure Managed Instance

  • Recovery Model:
    • Azure SQL Database: From automated backup only
    • Azure SQL Managed Instance: From automated backups and full backups placed on Azure Blob Storage
  • Active Geo-replication:
    • Azure SQL Database: Supported. In all service tier apart from hyper scale
    • Azure SQL Managed Instance: Not Supported, Use Auto failover groups as an alternative solution
  • Auto Scale:
    • Azure SQL Database: Only supported in Serverless
    • Azure SQL Managed Instance: Not Supported
  • Automatic tuning:
    • Azure SQL Database: Supported
    • Azure SQL Managed Instance: Not Supported
  • Long Term Backup Retention:
    • Azure SQL Database: Supported, keep automatically taken backups for up to 10 years
    • Azure SQL Managed Instance: Use Manual backups as a work around

• Lets understand pricing: Refer Here

  • Reservations: You can give a commitment to Azure that you would be using the database for the next 1 year or 3 years and then you will be getting huge discounts upto 59%

• Choose

  • Azure SQL Database if:
    • you are building a modern cloud applications that want to use the latest stable SQL features
    • You want to reduce the cost of database management & focus of application features
    • Your application is designed for unplanned & unbound database
  • Azure SQL Managed Instance if:
    • You want the database with VNET integration
    • You want nearly fully compatibility with SQL Server on-premises
    • Your application is relying heavily SQL Agents
    • You want to reduce the cost of database management & focus of application features