(Update 2021-12-07: The database "Singelton" changed name to "Single" in Azure in June 13, 2018)

Azure SQL Databases are an excellent service that allows you to run your SQL Server workloads in Azure. Azure SQL Databases can be ran as a singleton instance, which means that you pay for the compute/RAM/Log IO per database or you can pool compute resources in an elastic pool between multiple databases.

SQL Server Edition

It is important to keep in mind that the SQL Server edition for Azure SQL Databases is a custom and unique one, but it always includes Enterprise features, so you do get online index operations automatically. This is great for DBAs who are used to working with the Enterprise edition of SQL Server. You also get some of the fancy Pause and Resume index operations that came in SQL Server 2017. You can also control the compatibility level as well once the database has been restored in Azure.

Pricing

Pricing for Azure SQL Database depends on whether or not you go the singleton or elastic pool pricing model, but there is also a DTU and vCore pricing model that you can choose between. A DTU is a “Database Transactional Unit” which is essentially a formula that Microsoft developed that combines computation power, RAM, and transaction log IO. In our experience, it is not really possible to accurately convert an on premise SQL node to DTUs without migrating the workload. The good news is that you can always adjust the DTUs in the portal or programmatically via the AZ Powershell module. You can also choose between Standard and Business Critical tiers, which have different latency and connectivity SLAs.

There is also a hyperscale pricing tier, which utilizes a new architecture. This is an architectural change which allows for storage, transaction log IO, and compute to scale independently, where the storage can support up to 100 TB of data. This does slightly increase the latency between compute and storage, but its similar to the standard Azure SQL Database tier.

Note: There is a new pricing feature called serverless, which will deallocate your compute if you haven’t had an active query in 6 hours. This also include connection requests, so if your Azure SQL Database is not locked down by the firewall rules, then it will probably not be deallocated due to brute force attacks. There is an increase in latency when the first database query is executed to “wake up” the instance, but you don’t pay for it while the compute is deallocated, which is great for reducing costs.

Singleton vs Elastic Pools

When choosing between the singleton and elastic pool pricing model, it is important for you to understand your workload of your SQL databases. Do your databases have an unpredictable workload that varies on an hourly basis? If so, it may make sense to go with the Elastic Pool pricing model and let your databases share a pool of computation resources. If your databases utilize a consistent and predictable computational workload, then the singleton model may make more financial sense.

Limitations

There are some limitations to Azure SQL databases and it is important to know about these before you migrate your databases as an Azure SQL Database.

• You cannot query across databases on the Azure SQL instance. This means that you cannot query from Database1 to Database2 on the same instance. This can be a deal breaker for a lot of applications.

• There is no SQL agent. The good news is you can use Azure runbooks to call stored procedures/queries. An alternative to Runbooks is the TSQL Agent service or you can also use a Azure Data Factory to call stored procedures.

• Use the Azure migration tool to check to see if your database is compatible. There may be issues relating to how your database is designed that may not work well in Azure.

Benefits

While some of the limitations can be deal breakers, there are a lot of benefits to be found by running your database as an Azure SQL Database. One of the benefits is that it essentially forces your DBA and/or developers to follow the best practices relating to database design and maintenance. If your queries are not tuned or are lacking key indexes, then you will be consuming more DTUs or vCores than you need to. Running your workloads in Azure will force you to keep up with indexes, have strategic statistics and index maintenance, and to optimize your queries. If you do this, Azure SQL Databases can be incredibly cost effective, which is something we have seen ourselves with our clients.

Another great benefit is that you don’t have to care about hardware, since Azure SQL Databases are a Platform as a Service (PaaS) service. This means that you only need to manage the application and databases, including backups. This is great because from an operations perspective, there is far less to monitor and manage, which allows your DBAs and developers to do what they do best. The uptime for Azure SQL Databases is great as they come with a 99.9% connectivity SLA, which can get higher if you are on the Premium/Business Critical pricing tier.

Overall, Azure SQL Databases are a great service within Azure and as more and more applications are migrated to Azure, choosing to switch to Azure SQL Databases can be a wise choice depending on the needs of the application. It is important to understand these needs as this can impact performance and cost.