The Good, the Bad, and the Ugly of Losing Direct Access to SQL Server from Business Central in the Cloud
Many users of Microsoft Dynamics NAV or Business Central (BC) on-premises are used to being able to access SQL Server directly. That has been useful for several reasons, and one of the main reasons is that traditionally it has been much faster to run reports directly using SQL Server’s Report Builder than using Dynamics NAV or Business Central’s native reporting functions. And a lot of people got used to that.
But once you move to cloud-based BC (BC SaaS), users can no longer access SQL Server directly. This means any reports you’ve created that query SQL Server directly won’t work anymore.
But is that a bad thing or a good thing?
The problem with direct access to SQL Server from BC
Not long ago, if you had multiple companies within a single NAV/BC database, there was a specific table for each company. So, you could create customizations and then query the database for a particular company’s information. While this process might have been faster than using native NAV/BC functionality, it has never been a recommended practice by Microsoft.
And for good reason.
To support a true SaaS deployment model, Microsoft introduced Extensions in Business Central. This architecture separates modified business logic from the core product, including database tables.
A new company table is added whenever a modification or an ISV add-on is introduced for that table using table extension. Figure 1 shows an example of four separate database tables for ‘dbo.CRONUS USA, Inc_$Item Ledger Entry’ as a result of making modifications.
Since you don’t know how many tables you have when you use multiple ISVs and customizations, you can’t rely on the single table that comes from Microsoft using SQL Server. Plus, extensions can come and go at any time, so it is best not to rely on this practice in the first place.
Even if you still have direct access to SQL Server, these types of hacks will either fail over time or you will have to update your queries continuously. It is far better to use native NAV or Business Central functions for reporting.
Consider other options to direct SQL Server access
If your organization has been using direct access queries to SQL Server, it is time to stop that practice and start using any of the following options:
- Web Services: Web Services provides access to the data from the front end. Choose the fields and endpoint you want and use Power BI or another analysis tool to take the data in (Web Services works for NAV 2019 and higher).
- APIs: Identify the reporting API endpoints to expose the data to Power BI. APIs do not need complex coding, you just need to know the data fields you want. There is an API called Reports Finance available from Microsoft for reporting on Financial Data.
- Queries: query objects enable you to retrieve records from one or more tables and then combine the data into rows and columns in a single dataset. Query objects can also perform calculations on data, such as finding the sum or average of all values in a dataset column. Queries can also be exposed as APIs for reporting.
- Third-party software solutions: Several ISVs have solutions that allow you to sync your data and create reports without accessing SQL Server directly.
Ultimately, when using Business Central, you should use front-end tools. You can’t be sure of the table structure if you’re using BC and SQL Server on-premises, and you won’t have access to SQL Server if you’re using BC SaaS.
If you have any questions on this issue, contact ArcherPoint.