Azure SQL Serverless Database: A Comprehensive Guide

Azure SQL Serverless Database is a game-changer for businesses, allowing you to only pay for what you use. This means you can scale up or down as needed, without being locked into a fixed pricing plan.

With Azure SQL Serverless, you can enjoy the benefits of a fully managed database, including automatic backups and high availability, without the need for manual maintenance.

The database automatically scales to meet demand, so you don't have to worry about provisioning or managing resources. This makes it perfect for applications with variable workloads or unpredictable traffic patterns.

As a result, you can focus on developing and delivering your application, without the hassle and expense of managing a traditional database infrastructure.

The cost of an Azure SQL Serverless database is the summation of the compute cost and storage cost.

Compute cost is calculated at $0.0001479 per vCore-second. Premium storage costs $0.1265 GB/month, and backups cost $0.22 and $0.055 GB/month for PITR and LTR respectively.

Azure SQL Serverless can save about half of the budget expenses compared to a vCore-based SQL Server model. This is because you're not paying for Azure SQL Server TempDB system database compute and storage space.

You can estimate active vCores using the app_cpu_percent metric, where 100% is equal to maximum vCore allocation. For example, a 15% metric value for a maximum of four vCores means only one vCore is in use.

The autopause feature can help save money, but be aware of its limitations. Autopause may not happen if there are number sessions > 0, CPU > 0, active geo-replication is enabled, long-term backup is executing, Azure infrastructure update, or sync database used in SQL data sync.

Here's a breakdown of the compute cost:

Scalability and Performance are key features of Azure SQL Serverless. The minimum and maximum vCores are configurable parameters that define the range of compute capacity available for the database.

These parameters determine the proportionality of memory and IO limits. The auto-pause delay is another configurable parameter that defines the period of time the database must be inactive before it is automatically paused.

Load balancing automatically happens if the machine can’t fulfill resources inside a couple of moments. For instance, if the resource request is 4 vCores, however just 2 vCores are accessible, then, at that point, it might take up to a couple of moments to stack balance before 4 vCores are given.

Here are some key metrics for monitoring the resource usage of the app package and user resource pool of a serverless database:

Autoscaling is a crucial aspect of scalability and performance. It ensures that your database can handle changes in workload without manual intervention.

The minimum and maximum vCores are configurable parameters that define the range of compute capacity available for the database. This means you can adjust the amount of resources allocated to your database based on your needs.

Load balancing automatically happens if the machine can’t fulfill resources inside a couple of moments. For instance, if the resource request is 4 vCores, however just 2 vCores are accessible, then, at that point, it might take up to a couple of moments to stack balance before 4 vCores are given.

The database stays online during load balancing except for a brief period at the end of the operation when connections are dropped. This ensures minimal downtime and maximum availability for your users.

Here are some key metrics to monitor for autoscaling:

These metrics will help you understand how your database is performing and make data-driven decisions for autoscaling.

Auto-pause and auto-resume are features that allow your serverless database to automatically pause and resume based on usage. Currently, this feature is only supported in the General Purpose tier.

Auto-pause is triggered if all sessions are closed and the CPU usage is 0 for the user workload running in the user resource pool. An option is provided to disable auto-pausing if desired.

Some features don't support auto-pausing, but do support auto-scaling. If you're using any of these features, you'll need to disable auto-pausing and the database will remain online regardless of inactivity: Geo-replication, Long-term backup retention, sync database used in SQL Data Sync, DNS alias, and Elastic Jobs.

Here are some features that temporarily prevent auto-pausing during deployment: service updates that require the database to be online. Once the service update completes, auto-pausing becomes allowed again.

To modify the auto-pause delay, you can use the Set-AzSqlDatabase command with the MaxVcore, MinVcore, and AutoPauseDelayInMinutes arguments. This is only applicable to the General Purpose tier, as serverless auto-pausing is not currently supported in the Hyperscale tier.

Serverless computing is available in many regions around the world.

You can use Serverless for General Purpose and Hyperscale tiers with support up to 40 maximum vCores in most places, but there are some exceptions.

Serverless computing is not available in China East, China North, Germany Central, Germany Northeast, or US Gov Central (Iowa).

If you're planning to use Serverless in one of these regions, you might need to consider alternative solutions or wait for the service to become available.

Here are the regions where Serverless is currently not available:

Azure SQL Serverless offers robust security and encryption features to protect your data.

Data is automatically encrypted at rest and in transit, ensuring your sensitive information remains secure.

With Azure SQL Serverless, you can enable Transparent Data Encryption (TDE) to protect data at rest, without any additional setup or maintenance.

TDE uses a certificate to encrypt and decrypt data, providing an additional layer of security.

Azure SQL Serverless also supports row-level encryption, allowing you to encrypt specific data columns or rows.

This feature is particularly useful for storing sensitive information, such as credit card numbers or personal identifiable information.

Azure SQL Serverless integrates seamlessly with Azure Active Directory (Azure AD), enabling you to manage access and authentication for your database.

This integration provides an additional layer of security and helps you meet regulatory requirements.

Azure SQL Serverless also supports Always Encrypted, which allows you to protect sensitive data without having to decrypt it.

This feature is particularly useful for protecting data in use, such as when it's being processed or queried.

Connecting to an Azure SQL Serverless database can be a bit tricky if it's paused. If a serverless database is paused, the first connection attempt resumes the database and returns an error stating that the database is unavailable with error code 40613.

Once the database is resumed, you can retry the login to establish connectivity. Fortunately, database clients that follow connection retry logic recommendations won't need to be modified.

If you're wondering how long it takes for the database to auto-resume or auto-pause, the latency is generally around 1 minute to auto-resume and 1-10 minutes after the expiration of the delay period to auto-pause.

For more information on connection retry logic, you can check out the following resources:

If a serverless database is paused, the first connection attempt resumes the database and returns an error stating that the database is unavailable with error code 40613.

Once the database is resumed, you can retry the login to establish connectivity. This is because database clients following connection retry logic recommendations shouldn't need to be modified.

To implement connection retry logic, you can refer to the following resources:

Latency can be a major issue for serverless databases, with some taking up to 1 minute to auto-resume after being paused.

This may not seem like a lot, but it can add up and impact the overall performance of your application.

In some cases, auto-pausing a serverless database can take between 1-10 minutes after the expiration of the delay period, which can be a significant delay for users.

It's essential to consider these latency times when designing your application and deciding on the best approach for your serverless database.

Azure SQL Serverless has two compute tiers: serverless and provisioned. The serverless tier is ideal for single databases with intermittent, unpredictable usage patterns, while the provisioned tier is better suited for databases with more regular usage patterns.

The serverless tier is automatically scaled, whereas the provisioned tier requires manual scaling. This means that serverless databases can pause and scale down to zero, resulting in a lower minimum compute bill. However, serverless databases are more expensive than provisioned ones when running.

Here's a summary of the key differences between the two tiers:

Azure SQL Serverless is currently only supported in the General Purpose tier on Generation 5 hardware in the vCore purchasing model. This means that you can create a new serverless database or move an existing database into the serverless tier, but you'll need to choose the General Purpose tier and Generation 5 hardware.

To create a new serverless Hyperscale database, you can use PowerShell.

You can create a new serverless Hyperscale database with one high availability replica and zone redundancy using the following PowerShell example:

Create a new serverless Hyperscale database with one high availability replica and zone redundancy using the following PowerShell example:

Azure CLI is a powerful tool for managing your Azure resources. You can create a new serverless Hyperscale database with zone redundancy using the CLI.

To create a Hyperscale database, you can use the following Azure CLI example. This will give you a database with one high availability replica and zone redundancy.

The Azure CLI example for creating a Hyperscale database with zone redundancy is straightforward. Simply copy and paste the code into your terminal.

I've used the Azure CLI myself to manage my Azure resources, and it's been a game-changer. With the CLI, you can automate tasks and manage your resources more efficiently.

Zone redundancy is a key feature of Azure SQL Serverless, and it's easy to enable using the CLI. This ensures that your database is highly available and can withstand outages.

To create a new serverless Hyperscale database with zone redundancy, you'll need to use the following Azure CLI command. Make sure to replace the placeholders with your own values.

You can find the full Azure CLI example in the article, including the command to create a new serverless Hyperscale database with zone redundancy.

Azure SQL Database offers three main tiers: General Purpose, Business Critical, and Hyperscale. However, serverless support is only available in the General Purpose and Hyperscale tiers.

The General Purpose tier is a good choice for most workloads, offering a balance between performance and cost. It's supported in the vCore purchasing model and Generation 5 hardware.

One of the key benefits of the General Purpose tier is its ability to scale automatically, making it a great choice for workloads with unpredictable usage patterns. However, it's not suitable for workloads that require high performance and low latency, such as those with high average compute utilization.

The Hyperscale tier, on the other hand, offers high performance and low latency, making it a great choice for workloads that require high availability and low latency. However, it's not supported in the serverless compute tier.

Here's a summary of the supported and not supported tiers for serverless support:

It's worth noting that serverless databases can be moved between the General Purpose and Hyperscale service tiers, but this requires careful planning and consideration of the database's performance requirements.

In terms of pricing, serverless databases are charged per second of usage, while provisioned databases are charged per hour. This means that serverless databases can be more cost-effective for workloads with intermittent or unpredictable usage patterns. However, they can also be more expensive for workloads with high average usage.

Overall, the choice of tier will depend on the specific needs and requirements of your workload. It's essential to carefully consider the performance, cost, and scalability requirements of your workload before selecting a tier.

Here are some scenarios where serverless is a good choice:

And here are some scenarios where serverless is not a good choice:

The pause/resume status of a serverless database is a crucial aspect to understand, especially when working with auto-pausing enabled databases. In this case, the database status includes the following values: Online, Pausing, Paused, and Resuming.

The Azure portal displays the database status in the overview page of the database and its server. You can also view the history of pause and resume events in the Activity log. This is especially useful for tracking database activity over time.

Here's a breakdown of the possible status values:

To view the current database status, you can use the following PowerShell example.