Best configuration practices for D365 Business Central and Azure SQL
For the past five years we’ve provided our customers with Dynamics NAV and D365 Business Central on Azure. We’ve seen Microsoft develop these products into a fantastic set of technical features at an amazing speed. Recently we had the good fortune to discuss proper configuration of the toolset with multiple development teams, generating a set of best practices for configuring D365 Business Central to run with Azure SQL.
Best configuration practices for D365 Business Central and Azure SQL
For the past five years we’ve provided our customers with Dynamics NAV and D365 Business Central on Azure. We’ve seen Microsoft develop these products into a fantastic set of technical features at an amazing speed. Recently we had the good fortune to discuss proper configuration of the toolset with multiple development teams, generating a set of best practices for configuring D365 Business Central to run with Azure SQL.
Best configuration practices for D365 Business Central and Azure SQL
For the past five years we’ve provided our customers with Dynamics NAV and D365 Business Central on Azure. We’ve seen Microsoft develop these products into a fantastic set of technical features at an amazing speed. Recently we had the good fortune to discuss proper configuration of the toolset with multiple development teams, generating a set of best practices for configuring D365 Business Central to run with Azure SQL.
An excellent opportunity for Microsoft partners
In our cooperation with Microsoft we discovered the Azure SQL and D365 Business Central teams don’t necessarily espouse corresponding views on systems configuration; even though both are part of the same company. This provides us as a Microsoft partner with an excellent opportunity to play our part. By operating and maintaining the systems in the field we are the front line. We turn great applications into practical solutions.
An excellent opportunity for Microsoft partners
In our cooperation with Microsoft we discovered the Azure SQL and D365 Business Central teams don’t necessarily espouse corresponding views on systems configuration; even though both are part of the same company. This provides us as a Microsoft partner with an excellent opportunity to play our part. By operating and maintaining the systems in the field we are the front line. We turn great applications into practical solutions.
An excellent opportunity for Microsoft partners
In our cooperation with Microsoft we discovered the Azure SQL and D365 Business Central teams don’t necessarily espouse corresponding views on systems configuration; even though both are part of the same company. This provides us as a Microsoft partner with an excellent opportunity to play our part. By operating and maintaining the systems in the field we are the front line. We turn great applications into practical solutions.
An unplanned stress test for Azure
These past three weeks the Corona crisis forced almost everyone to work from home. This put a serious strain on the cloud and internet. During these chaotic times, Azure was able to hold its own. But the severe load this extra demand put on the system translated into an unplanned stress test. This brought some issues to light. We had to handle misconfigurations of the new technical tooling, mostly caused by limited understanding or counterintuitive instructions.
An unplanned stress test for Azure
These past three weeks the Corona crisis forced almost everyone to work from home. This put a serious strain on the cloud. During these chaotic times, Azure was able to hold its own. But the severe load this extra demand put on the system translated into an unplanned stress test. This brought some issues to light. We had to handle misconfigurations of the new technical tooling, mostly caused by limited understanding or counterintuitive instructions.
An unplanned stress test for Azure
These past three weeks the Corona crisis forced almost everyone to work from home. This put a serious strain on the cloud. During these chaotic times, Azure was able to hold its own. But the severe load this extra demand put on the system translated into an unplanned stress test. This brought some issues to light. We had to handle misconfigurations of the new technical tooling, mostly caused by limited understanding or counterintuitive instructions.
“If the problem can be solved why worry? If the problem cannot be solved worrying will do you no good.”
“If the problem can be solved why worry? If the problem cannot be solved worrying will do you no good.”
“If the problem can be solved why worry? If the problem cannot be solved worrying will do you no good.”
“If the problem can be solved why worry? If the problem cannot be solved worrying will do you no good.”
Telemetric data versus our gut feeling
As IT-professionals we all know the feeling that something’s not right in our environment even though we can’t quite put our finger on it. In our case it was a series of slightly sluggish system responses. To detect hard to spot problems the Azure Cloud collects telemetric data. This helps predict issues and improve performance. Network issues seemed the obvious cause, but optimizing performance did not help. To delve deeper we contacted both the Azure SQL and the D365 Business Central teams.
Developing new best practices
Working with both Microsoft teams gave us a chance to combine anonymous telemetry data sets and analyze it with different skillsets. We used data from several customers recorded at the specific timeframe when issues arose. In the end we discovered network load wasn’t the cause of latency. In truth it was a system configuration issue caused by the way Azure SQL and D365 Business Central interact. Knowing this helped us improve performance and formulate new best practices to properly configure this environment.
Telemetric data versus our gut feeling
As IT-professionals we all know the feeling that something’s not right in our environment even though we can’t quite put our finger on it. In our case it was a series of slightly sluggish system responses. To detect hard to spot problems the Azure Cloud collects telemetric data. This helps predict issues and improve performance. Network issues seemed the obvious cause, but optimizing performance did not help. To delve deeper we contacted both the Azure SQL and the D365 Business Central teams.
Developing new best practices
Working with both Microsoft teams gave us a chance to combine anonymous telemetry data sets and analyze it with different skillsets. We used data from several customers recorded at the specific timeframe when issues arose. In the end we discovered network load wasn’t the cause of latency. In truth it was a system configuration issue caused by the way Azure SQL and D365 Business Central interact. Knowing this helped us improve performance and formulate new best practices to properly configure this environment.
Telemetric data versus our gut feeling
As IT-professionals we all know the feeling that something’s not right in our environment even though we can’t quite put our finger on it. In our case it was a series of slightly sluggish system responses. To detect hard to spot problems the Azure Cloud collects telemetric data. This helps predict issues and improve performance. Network issues seemed the obvious cause, but optimizing performance did not help. To delve deeper we contacted both the Azure SQL and the D365 Business Central teams.
Developing new best practices
Working with both Microsoft teams gave us a chance to combine anonymous telemetry data sets and analyze it with different skillsets. We used data from several customers recorded at the specific timeframe when issues arose. In the end we discovered network load wasn’t the cause of latency. In truth it was a system configuration issue caused by the way Azure SQL and D365 Business Central interact. Knowing this helped us improve performance and formulate new best practices to properly configure this environment.
"The art of a successful project lies in thinking about how to use ICT."
"The art of a successful project lies in thinking about how to use ICT."
"The art of a successful project lies in thinking about how to use ICT."
"The art of a successful project lies in thinking about how to use ICT."
D365 Business Central behaves like an OLTP tool
The primary takeaway is that the behavior of an ERP system like D365 Business Central differs from regular Azure SQL usage. This mainly comprises the level of diversity of queries fired at the Azure SQL database related to the timeframe. D365 Business Central behaves more like an OLTP tool: it fires a lot of small queries in a short period of time.
Best configuration practices for D365 Business Central and Azure SQL
Comparing this finding to the online documentation we suggest the following settings for optimal performance of D365 Business Central on Azure:
- Max degree of parallelism (MAXDOP) The SQL queries generated by D365 Business Central is of the OLTP type (a lot of small queries in a short period of time). It is therefore recommended to run D365 Business Central with MAXDOP set to the value 1.
- Index Autotuning Due to different processes running in a large and unpredictable timeframe, this Azure SQL feature can be tricky. Your best bet is to let the autotuning tool suggest keys and decide periodically how to handle these. That said, the situation can vary from customer to customer. Our advice is to follow these steps:
- Don’t let the Index Autotuning tool automatically create indexes. This might cause a performance penalty. For instance when indexes are created during business hours or when keys are created one day and dropped again the next. The diversity of queries fired by D365 Business Central causes this behavior.
- Create appropriate keys at the tables as an extension when these keys are valid and improve performance in a structural way. Remember you cannot delete keys due to backward compatibility, so only add generic keys.
- Create or drop the remaining suggested indexes manually through the Index Autotuning tool. Keys created by D365 Business Central are not touched because indexes are differentiated by the tool from the keys created by other apps (like D365 Business Central).
- When keys created as described in point 2 are only periodically valid, for instance during month or year closures, disable them at the level of Azure SQL using a script. Do not Drop and Recreate the keys at the Azure SQL side. This will cause issues with the D365 Service tier running at the Azure VM.
- Use accelerated networking at the VM Assuming the Azure SQL database and the Azure Virtual Machine running the D365 Business Central service tiers are running in the same tenant and region - It is advised to use Azure Accelerated Networking at the VM's, its greatly improving its networking performance, by reducing latency, jitter, and CPU utilization - It will enable speeds of up to 25Gbps per Virtual Machine. Best of all, it’s free!
- Continuous Monitoring Use the anonymous telemetry data and the suggested performance improvements, to create a monitoring dashboard. You can use Azure Monitoring or any monitoring tool will do. You can monitor the different tenants of your customers and take the appropriate actions. To add the specific Business Central telemetry for the tenant environments to be able troubleshooting and support for the tenant you can enable this throught the Business Central Administration Center. The Telemetry tab provides telemetry of top-level AL events, and any errors resulting from calls through the telemetry stack.
D365 Business Central behaves like an OLTP tool
The primary takeaway is that the behavior of an ERP system like D365 Business Central differs from regular Azure SQL usage. This mainly comprises the level of diversity of queries fired at the Azure SQL database related to the timeframe. D365 Business Central behaves more like an OLTP tool: it fires a lot of small queries in a short period of time.
Best configuration practices for D365 Business Central and Azure SQL
Comparing this finding to the online documentation we suggest the following settings for optimal performance of D365 Business Central on Azure:
- Max degree of parallelism (MAXDOP) The SQL queries generated by D365 Business Central is of the OLTP type (a lot of small queries in a short period of time). It is therefore recommended to run D365 Business Central with MAXDOP set to the value 1.
- Index Autotuning Due to different processes running in a large and unpredictable timeframe, this Azure SQL feature can be tricky. Your best bet is to let the autotuning tool suggest keys and decide periodically how to handle these. That said, the situation can vary from customer to customer. Our advice is to follow these steps:
- Don’t let the Index Autotuning tool automatically create indexes. This might cause a performance penalty. For instance when indexes are created during business hours or when keys are created one day and dropped again the next. The diversity of queries fired by D365 Business Central causes this behavior.
- Create appropriate keys at the tables as an extension when these keys are valid and improve performance in a structural way. Remember you cannot delete keys due to backward compatibility, so only add generic keys.
- Create or drop the remaining suggested indexes manually through the Index Autotuning tool. Keys created by D365 Business Central are not touched because indexes are differentiated by the tool from the keys created by other apps (like D365 Business Central).
- When keys created as described in point 2 are only periodically valid, for instance during month or year closures, disable them at the level of Azure SQL using a script. Do not Drop and Recreate the keys at the Azure SQL side. This will cause issues with the D365 Service tier running at the Azure VM.
- Use accelerated networking at the VM Assuming the Azure SQL database and the Azure Virtual Machine running the D365 Business Central service tiers are running in the same tenant and region - It is advised to use Azure Accelerated Networking at the VM's, its greatly improving its networking performance, by reducing latency, jitter, and CPU utilization - It will enable speeds of up to 25Gbps per Virtual Machine. Best of all, it’s free!
- Continuous Monitoring Use the anonymous telemetry data and the suggested performance improvements, to create a monitoring dashboard. You can use Azure Monitoring or any monitoring tool will do. You can monitor the different tenants of your customers and take the appropriate actions. To add the specific Business Central telemetry for the tenant environments to be able troubleshooting and support for the tenant you can enable this throught the Business Central Administration Center. The Telemetry tab provides telemetry of top-level AL events, and any errors resulting from calls through the telemetry stack.
D365 Business Central behaves like an OLTP tool
The primary takeaway is that the behavior of an ERP system like D365 Business Central differs from regular Azure SQL usage. This mainly comprises the level of diversity of queries fired at the Azure SQL database related to the timeframe. D365 Business Central behaves more like an OLTP tool: it fires a lot of small queries in a short period of time.
Best configuration practices for D365 Business Central and Azure SQL
Comparing this finding to the online documentation we suggest the following settings for optimal performance of D365 Business Central on Azure:
- Max degree of parallelism (MAXDOP) The SQL queries generated by D365 Business Central is of the OLTP type (a lot of small queries in a short period of time). It is therefore recommended to run D365 Business Central with MAXDOP set to the value 1.
- Index Autotuning Due to different processes running in a large and unpredictable timeframe, this Azure SQL feature can be tricky. Your best bet is to let the autotuning tool suggest keys and decide periodically how to handle these. That said, the situation can vary from customer to customer. Our advice is to follow these steps:
- Don’t let the Index Autotuning tool automatically create indexes. This might cause a performance penalty. For instance when indexes are created during business hours or when keys are created one day and dropped again the next. The diversity of queries fired by D365 Business Central causes this behavior.
- Create appropriate keys at the tables as an extension when these keys are valid and improve performance in a structural way. Remember you cannot delete keys due to backward compatibility, so only add generic keys.
- Create or drop the remaining suggested indexes manually through the Index Autotuning tool. Keys created by D365 Business Central are not touched because indexes are differentiated by the tool from the keys created by other apps (like D365 Business Central).
- When keys created as described in point 2 are only periodically valid, for instance during month or year closures, disable them at the level of Azure SQL using a script. Do not Drop and Recreate the keys at the Azure SQL side. This will cause issues with the D365 Service tier running at the Azure VM.
- Use accelerated networking at the VM Assuming the Azure SQL database and the Azure Virtual Machine running the D365 Business Central service tiers are running in the same tenant and region - It is advised to use Azure Accelerated Networking at the VM's, its greatly improving its networking performance, by reducing latency, jitter, and CPU utilization - It will enable speeds of up to 25Gbps per Virtual Machine. Best of all, it’s free!
- Continuous Monitoring Use the anonymous telemetry data and the suggested performance improvements, to create a monitoring dashboard. You can use Azure Monitoring or any monitoring tool will do. You can monitor the different tenants of your customers and take the appropriate actions. To add the specific Business Central telemetry for the tenant environments to be able troubleshooting and support for the tenant you can enable this throught the Business Central Administration Center. The Telemetry tab provides telemetry of top-level AL events, and any errors resulting from calls through the telemetry stack.
Delivering the best customer experience
These settings should optimize D365 Business Central within the Azure framework. We would like to thank Microsoft. Both the Azure SQL and D365 Business Central teams shared knowledge and actively helped us achieve our goals. We had the good fortune to experience a large company going all out in support of its smaller partners. We believe this cooperation and the resulting crossover between specializations is the only way to improve products and deliver the best customer experience.
Delivering the best customer experience
These settings should optimize D365 Business Central within the Azure framework. We would like to thank Microsoft. Both the Azure SQL and D365 Business Central teams shared knowledge and actively helped us achieve our goals. We had the good fortune to experience a large company going all out in support of its smaller partners. We believe this cooperation and the resulting crossover between specializations is the only way to improve products and deliver the best customer experience.
Delivering the best customer experience
These settings should optimize D365 Business Central within the Azure framework. We would like to thank Microsoft. Both the Azure SQL and D365 Business Central teams shared knowledge and actively helped us achieve our goals. We had the good fortune to experience a large company going all out in support of its smaller partners. We believe this cooperation and the resulting crossover between specializations is the only way to improve products and deliver the best customer experience.
Do you want to stay informed?
Do you want to stay informed?