Choosing a cloud relational database when you know SQL Server

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

If you have some control over the code used in the application layer, administrative and operational costs can be reduced overall by migrating your data tier towards platform services (PaaS) instead of SQL Server in a virtual machine (Infrastructure as a Service, otherwise known as IaaS).

 

For a relational database, Microsoft’s SQL Server engine also comes in PaaS, and provides the following deployment options for an Azure SQL database:

  • Single database represents a fully managed, isolated database. You might use this option if you have modern cloud applications and microservices that need a single reliable data source. A single database is similar to a contained database in Microsoft SQL Server Database Engine.
  • Managed instance is a fully managed instance of the Microsoft SQL Server Database Engine. It contains a set of databases that can be used together. Use this option for easy migration of on-premises SQL Server databases to the Azure cloud, and for applications that need to use the database features that SQL Server Database Engine provides.
  • Elastic pool is a collection of single databases with a shared set of resources, such as CPU or memory. Single databases can be moved into and out of an elastic pool.

For Azure SQL Database or Managed Instance, you do not have to manage upgrades, high availability, or backups. In general, Azure SQL Database can dramatically increase the number of databases managed by a single IT or development resource. Elastic pools also support SaaS multi-tenant application architectures with features including tenant isolation and the ability to scale to reduce costs by sharing resources across databases. Managed instance provides support for instance-scoped features enabling easy migration of existing applications, as well as sharing resources amongst databases.

 

Service tiers

Azure SQL Database offers three service tiers that are designed for different types of applications:

  • General Purpose/Standard service tier is designed for common workloads. It offers budget-oriented balanced compute and storage options.
  • Business Critical/Premium service tier is designed for OLTP applications with high transaction rate and lowest-latency I/O. It offers the highest resilience to failures by using several isolated replicas.
  • Hyperscale service tier is designed for very large OLTP database and the ability to auto-scale storage and scale compute fluidly.

 

When to choose the General Purpose/Standard service tier

General Purpose service tier is a default service tier in Azure SQL Database that is designed for most of the generic workloads. If you need a fully managed database engine with 99.99% SLA with storage latency between 5 and 10 ms that match Azure SQL IaaS in most of the cases, General Purpose tier is the option for you.

Find resource characteristics (number of cores, IO, memory) of General Purpose/Standard tier in Managed Instance, Single database in vCore model or DTU model, or Elastic pool (groups of databases with aggregate throughput) in vCore model and DTU model.

 

Who should consider the Business Critical service tier

Business Critical service tier is designed for the applications that require low-latency responses from the underlying SSD storage (1-2 ms in average), fast recovery if the underlying infrastructure fails, or need to off-load reports, analytics, and read-only queries to the free of charge readable secondary replica of the primary database.

Some  key reasons why you should choose Business Critical service tier instead of General Purpose tier are:

  • Low IO latency requirements – workload that needs the fast response from the storage layer (1-2 milliseconds in average) should use Business Critical tier.
  • Frequent communication between application and database. Application that cannot leverage application-layer caching or request batching and need to send many SQL queries that must be quickly processed are good candidates for Business Critical tier.
  • Higher availability – Business Critical tier in Multi-AZ configuration guarantees 99.995% availability, compared to 99.99% of General Purpose tier.
  • Fast geo-recovery – Business Critical tier configured with geo-replication has a guaranteed Recovery point objective (RPO) of 5 sec and Recovery time objective (RTO) of 30 sec for 100% of deployed hours.

 

Who should consider the Hyperscale service tier

The Hyperscale service tier is intended for most business workloads as it provides great flexibility and high performance with independently scalable compute and storage resources. With the ability to auto-scale storage up to 100 TB, it’s a great choice for customers who:

  • Have large databases on-premises and want to modernize their applications by moving to the cloud
  • Are already in the cloud and are limited by the maximum database size restrictions of other service tiers (1-4 TB)
  • Have smaller databases, but require fast vertical and horizontal compute scaling, high performance, instant backup, and fast database restore.

The Hyperscale service tier supports a broad range of SQL Server workloads, from pure OLTP to pure analytics, but it is primarily optimized for OLTP and hybrid transaction and analytical processing (HTAP) workloads.

 

I hope this helps guide you in deciding on the Platform as a Service SQL Server options in Azure.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.