Dynamics 365 Business Central on-premise performances to the max with Azure SQL Database Hyperscale

When you’re start implementing Dynamics 365 Business Central on-premise (because you’re not ready for the SaaS now), one of the good decision you can take today is to move your database to the cloud. I’ve written in the past different posts on when and why using Azure SQL Database or SQL Server installed on an Azure VM with Dynamics 365 Business Central (same is for NAV if you want). You can find some links here and here.

Normally, my personal experience is resumable as follows:

  • Use Azure SQL Database with Standard Tier only for small installations
  • SQL Server on Azure VM performs better than Azure SQL Database with Standard Tier when users > 10/15
  • Azure SQL Database is not always cheaper than SQL Server on Azure VM

But what about if you need the best performances? Costs are not always the main aspects to consider in this case, expecially if you’re starting a big project (many concurrent users). For very big projects, using SQL Server on an Azure VM without apsects like scaling, monitoring and so on could be a bottleneck.

Today I want to signal a very interesting new option that you can start considering: Azure SQL Database Hyperscale.

Azure SQL Database Hyperscale is a SQL-based and highly scalable cloud service tier for single databases. It has a cloud-born architecture which decouples compute, log and storage and it provides you with up to 100 TB of storage, with low latency and high throughput.

Main features of Azure SQL Database Hyperscale are the following:

  • Big size: it grows in size by 1 TB up to 100 terabytes
  • Very fast!
  • Instantaneous backups (time independent from the size of the db) and they don’t slow down database performance
  • Scalability: every page server stores up to 1 terabyte of data and it acts as an independent database with its own compute power and duplicate replicas. Hyperscale automatically adds a new page server when the used space in existing page servers reaches 1 TB.
  • Secure and reliable: 99.99% availability, disaster recovery, and mission-critical high performance.

The wonderful thing is that you can move your Azure SQL Database to the Hyperscale service tier when you want and without requiring any change to your applications using the database (connection strings are always the same).

I’ve done in the past days some tests on this new service tier and results are very interesting.

To check the gains on performances, I’ve installed a Dynamics 365 Business Central database instance on an Azure SQL Database with the Standard tier (General Purpose, Gen5, 2vCores), I’ve performed some tasks inside this instance and then I’ve moved the database to the Hyperscale service tier.

This is my Dynamics 365 Business Central database running on the Standard service tier:

AzureSqlHyperscale_00.jpg

and this is the monitoring of resources when performing some business tasks (a set of pre-defined tasks that runs for 15 minutes):

AzureSqlHyperscale_01.jpg

After these tests, I’ve switched the database service tier to Hyperscale tier (I suggest to select Gen5 as compute generation because in this way you run on the latest hardware generation). Here I’ve also set the vCore number to 2 (estimated price doubles, you are not forced to do that now and you can stay with defauls values and switch them later if you need more power):

AzureSqlHyperscale_02.jpg

When you click Apply, your database starts moving to the new service tier (wait few minutes):

AzureSqlHyperscale_03.jpg

and when ready you receive a confirmation message:

AzureSqlHyperscale_04.jpg

Now our Dynamics 365 Business Central database runs on the Azure SQL Database Hyperscale tier (you can check the new applyed service tier by going to the database properties):

AzureSqlHyperscale_05.jpg

Let’s repeat the same tests now (same set of tasks for the same time slot). The resource monitor when the database is switched to hyperscale is in the red box in the below image:

AzureSqlHyperscale_06.jpg

Results noted:

  • Database resource usage are lower
  • Query response time is faster

NOTE: results displayed here are on a database with a single user that performs intensive tasks. Gain on performances will be more when the number of users increases.

I’ve simulated a load test also by using a Powershell script that performs intensive queries on the database for a long period of time and what I can say is that Hyperscale tier is extremely interesting if you want you have great performances.

Remember that if you switch to the Hyperscale service tier you cannot go back to the Standard tier.

P.S. you pay per resource’s consumption, costs could be not too much compared to performances that you can have.

 

3 Comments

  1. Hi,
    I recently tried to upload a Business Central On-Prem database on Azure Hyperscale but it would seem that BC has Change Tracking enabled on some tables and Hyperscale informed me that Change Tracking is a known limitation. After disabling Change Tracking i managed to upload the DB but when i tried to start the BC service it would not run giving an error regarding Change Tracking. How did you manage to setup the system?

    Like

      1. I will give it another try to make sure but in my case the service always tried to enable Change tracking while starting and every time it failed to start with an error message that Change Tracking is not available on SQL.

        Even if it quits trying to enable Change Tracking and manages to start at some point, it is not really an issue you can live with in a production environment.

        Like

Leave a Reply to demiliani Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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