Behind the scenes of a cloud service: Customer databases

To run Business Central anywhere, you need two main components:

1. A compute tier: This is responsible for executing the code, i.e., your AL code, the BC platform code, etc.
2. A data tier: This is where customer data is persisted

In the previous post, Behind the scenes of a cloud service: Data plan clusters, we covered the compute tier of Business Central in the cloud. It consists of clusters of VMs, on which we install the NST and other services. It also contains additional resources such as load balancers, virtual networks, and the App DB.

In this post, we will look at the data tier of Business Central in the cloud. As you might have already guessed, this is going to be all about Azure SQL Databases in various flavors. The post is a bit long, but hopefully worth the time to read it. If you deploy Business Central on-premises in Azure, the post also gives you insights into how you can potentially optimize your use of Azure SQL.

Azure SQL Introduction

Azure SQL is a Database-as-a-Service offering that lets you create and manage databases without having to manage the underlying infrastructure. You never see the hardware, VMs, storage, or processes that host the database engine and the databases, but you can still easily connect to the databases e.g. from SQL Server Management Studio or from an application.

In Azure, like on-premises, you have both databases and database servers, however, a database server in Azure is just a “logical server”. It doesn’t represent a running server or process anywhere. The best way to think about an Azure SQL database server is that it is a group of databases, which lets you configure things for multiple databases at once, rather than for each individual database. As an example, you configure firewall settings, auditing, and threat detection at the database server level.

Azure SQL offers three service tiers: Basic, Standard, and Premium. A main difference between them is their throughput and performance predictability. In Business Central, we generally use Standard databases for non-paying customers and Premium database for paying customers.

There are new tiering models coming in Azure SQL, including a vCore-based model, but we don’t use them (yet) in Business Central, so we won’t cover them here.

Single Databases

The simplest way to use Azure SQL is to create a so-called single database.

We cover it here mostly to explain our journey. At this time, we have almost entirely moved away from using single databases in Business Central in the cloud.

When you create a single database in Azure SQL, you choose both the service tier and the compute size. For example, you might create a database with “Standard” tier and compute size “S0”. See here for a list of tiers and compute sizes for single databases.

Once created, Azure SQL physically allocates the chosen capacity for the database. It will be always available for this database, guaranteed. If you don’t use the capacity, e.g., during the night, then the resources that are allocated for you are wasted. On the other hand, if you make expensive queries and hit the capacity limit of your database, then your queries will run slower and potentially fail. So, it’s important for the cost vs. performance target to select the right capacity. Fortunately, you can easily change the tier and compute size on your database if needed.

In the early days of Business Central in the cloud, we used mostly S0 databases, which are relatively low-powered databases. S0’s are generally insufficient for realistic usage scenarios for Business Central. On the other hand, S0’s are often plenty for trial usage, however, many trial customers use Business Central only very lightly or stop using it altogether, and then we end up not using all the allocated resources for these S0 databases.

For these reasons, and a few others, we have moved almost entirely to Elastic Pools in Business Central.

Elastic Pools

When you create an elastic pool in Azure SQL, you again choose both the service tier and the compute size. For example, you might create an elastic pool with “Standard” tier and compute size “200 DTUs”. See here for a list of tiers and compute sizes for elastic pools.

Initially, the pool will contain no databases, but we will immediately start paying the full price for it. Then we start to add databases to it (for free), either by creating new databases in the pool, or by moving existing single databases (e.g., S0's) into the pool. All the databases in the pool will share the resources of the pool.

In addition to specifying the total capacity of the pool (e.g., 200 DTUs), we also specify that each database in the pool can use between 0 and 20 DTUs, for example. The upper limit prevents a single database from consuming all the resources of the pool. The lower limit of 0 means that we will not physically reserve any capacity for each database, so let’s say a given database is not using any resources at all, other databases can use those resources.

Elastic pools provide two significant benefits:

• Costs are reduced because databases share resources. At any point in time, some databases will need resources whereas others will be idle. We might have 100 customer databases in a pool with 200 DTUS, i.e., each database has only 2 DTUs on average, and this may be plenty! Contrast that with the small S0 single database, which has 10 DTUs.
• Each database can use more resources. Due to the resource sharing model, we can allow each database to use more resources than if we physically had to allocate resources per database. For example, by specifying an upper limit of 20 DTUs per database, each database can potentially use up to 20 DTUs, which is clearly more than if the database were an S0 with only 10 DTUs. This directly leads to better performance for the end user.

When we decide how many databases to put into an elastic pool, we strive to put in as many as possible, until the point where we see 100% pool utilization for anything but very short bursts. See further down for how we continuously adjust the number of databases in each pool in order to hit the right performance vs. cost target.

Shared Databases

Elastic pools are superior to single databases for Business Central in the cloud. As explained, they allow us to give more resources to each database, while at the same time allowing for cost reductions.

Great as they are, however, elastic pools also have limitations. For example, a standard-tier pool with 200 DTUs can contain at most 500 databases (see here). The reason for this limitation is that there is an overhead associated with each physical database, so even if nobody is querying a database, it is still not free to have it around.

In some cases, we need to run with a lower cost than what elastic pools can offer. This is the case for Microsoft Invoicing, which is powered by Business Central behind the scenes.

Fortunately, the needs of Microsoft Invoicing are much more limited than those of Business Central, both wrt. resource needs and functionality needs. The simpler requirements make it possible for us to put the data for multiple customers into the same database.

In this configuration, we create a high-powered single database (e.g. Premium P1) and put the data of many customers inside it. Data for each customer is strictly isolated from each other. Each record in the database has a column stating which customer owns the record, and the NST automatically applies the necessary filtering so each customer only sees their own data.

This configuration has some functional limitations. Since multiple customers share a single database and schema, there are challenges in areas such as extensions, resource governance, and manageability (e.g. backups). The Shared Database configuration was designed for a specific scenario in the cloud, and it is not recommended – nor supported – to run in this configuration with Business Central on-premises.

Continuous Optimization

When we create each database, we make an initial choice of service tier and compute size. For example, for a trial subscription of Business Central, we will probably create the database in a Standard-tier elastic pool with 200 DTUs. The elastic pool might already contain 100 databases, and now it will contain 101 databases.

Perhaps having 101 databases in the same pool is fine at first, but usage patterns may change over time. Some of the databases may start to use more resources, and to sustain good performance for end users, we need to move some databases to other pools. Conversely, the pool might have room for even more databases, so we should add more databases to it to avoid wasting resources.

Another scenario is that a customer switches from a trial subscription to a paying subscription. In this case, we want to move the database from a Standard elastic pool to a Premium elastic pool. And vice versa, if the customer cancels the paid subscription.

To handle these scenarios, we have a couple of microservices (in the control plane – see this post), which periodically validate that all criteria are satisfied, and take automated action if it isn’t the case.

Only using automation are we able to provide the right performance level to customers, at the scale of the Business Central cloud.

We hope this blog post about the Business Central data tier gave you some interesting insights. You now know how Business Central in the cloud stores customer data. And if you operate Business Central yourself, you hopefully have a better overview of the options you have with Azure SQL.