Azure SQL Elastic Pools: a way for saving costs with your Microsoft Dynamics ERP databases in the cloud

I’ve written a lot in the past about how you can move your existing on-premise Microsoft Dynamics NAV / Dynamics 365 Business Central databases to the cloud, how you can use SQL Server on an Azure VM or how you can start using Azure SQL (links here and here). I’ve also talked about how you can have a very performant database in the cloud by using the Hyperscale tier of Azure SQL (link here).

Today I want to talk about how you could save costs when using “SQL as a service”, alias Azure SQL database. When you create an Azure SQL database, you can choose between three service tiers (Basic, Standard and Premium) and between a single database, a managed instance or an elastic pool.

Elastic pools provide a simple and cost effective solution for managing the performance of multiple databases within a fixed budget. An elastic pool provides compute (eDTUs) and storage resources that are shared between all the databases it contains. Databases within a pool only use the resources they need, when they need them, within configurable limits. The price of a pool is based only on the amount of resources configured and is independent of the number of databases it contains.

Dynamics 365 Business Central SaaS actually runs on Azure SQL Elastic pools. Why you cannot run your existing Dynamics ERP database in the cloud with this type of service too?

There are two main benefits on using this technology:

• Reduced costs: database share resources allocated for an entire pool, not for a single database
• Each database can use more resources than what is planned at the creation time.

Normally I see that quite all partners that are using Dynamics NAV /D365BC databases in the cloud with “SQL as a service” are actually using a single database (Azure SQL database created with an amount of allocated DTU). This is a good choice for many scenarios, but there are many cases where an elastic pool could help you save money, for example:

• When you have many small customers and you (as a partner) provide an ERP as a service
• When you have customers that uses many database instances with a variant load during the day.

To explain how you can use elastic pools with your Dynamics NAV/D365BC database instance in the cloud (Azure SQL), I start from the following situation:

Here I have 3 Dynamics 365 Business Central databases based on Azure SQL (single database). All the 3 databases are under the same Azure SQL server instance and they’re all configured as Standard Tier (S1), 20 DTUs, 250 GB storage (very estimated price, about 35$/40$ months for each database instance).

To move these database on a pool, we need to create a SQL elastic pool instance from the Azure Portal (remember that all databases you want to add to the pool should be in the same Server than the pool):

Click on Configure elastic pool to add resources to your pool:

Here I’ve created a pool with Standard Tier, 200 DTUs and 250 GB storage. This is a very big pool, able to run about 500 databases (see here for details). What about costs for this elastic pool? That’s it (please always remember that Azure costs could change in the future):

What I always put in evidence here is:

An Elastic Pool with 200 eDTUs (standard) and 250 GB storage costs about the same as 10/15 single databases with 20 DTUs each (standard) and 250 GB storage each, but it’s able to run (maybe not 500 as the official numbers) but >= 50 Dynamics 365 Business Central / Dynamics NAV databases.

To add your existing databases to the pool, select the SQL elastic pool instance, click on Configure, select Databases and click on Add Database:

Here you can select the databases that you want to add to the SQL elastic pool:

In the Per database settings section, you can specify the minimum and maximum DTU to allow for each database:

Here I have specified 10 DTUs as minimum, 200 DTUs as maximum. Note that if the maximum pool DTU is 200, you can still set the maximum to 200 DTU per database without depriving other databases from functioning. That is, if only one database is active, it can use the maximum DTUs of the pool, but when another database becomes active, the server adjusts to provide DTU to that rather than having it not work at all.

When all setup is finished, click on Save and the elastic pool is provisioned.

If you now check your Azure SQL databases on the Azure Portal, you can see this:

Your databases now runs on Azure SQL elastic pool tier. The database connection string doesn’t change (so your external aplications are not affected by this change).

You can monitor your elastic pool resources directly from the Azure Portal (by sleecting the pool and going to the Overview pane) and also you can change the resources allocated to a pool when you need (on the fly, without downtime).

You can also use Powershell to move databases between different pools if you need more resources (this is what occours under the hood with Dynamics 365 Business Central SaaS).

You can’t change the service tier (Basic, Standard, Premium) after you’ve created an elastic pool. If you need that (for example in order to move databases from a Standard pool to a Premium pool) you need to move databases from the Standard pool to the new Premium pool.

I think that Azure SQL elastic pool is a cost effective way to evaluate when you want to move your Dynamics ERP databases to the cloud. Obviously, you need to evaluate your scenarios and evaluate is a standard database confioguration is more suitable for you that using a pool. A pool is useful when you need to deploy different databases to the cloud (a pool with a single database is not a good choice).