Recently, I came across a customer, where a partner had implemented some Stored Procedures on a Microsoft Dynamics NAV 2017 database.
Now, for any change on SQL level that comes to my ears, my first reaction is always “Nooooooooo“. Or as a pictures tells more than a thousand words:
First thing I did was googling to see if the community shared my opinion. Apparently there is nothing to find about recommendations on this: nor good nor bad. I did find a few blogposts on how to call SPs from NAV (even from my own ;-)) .. . Well – time for me to start preaching about the disadvantages :-).
My recommendation is quite simple: don’t do any manual changes in SQL Server on a NAV database. And I mean ANY change. Like:
- Altering indexes
- Stored Procedures
- Table triggers
Well, may be not “any” change .. I’m not talking about maintenance jobs, of course – you have to maintain a NAV database like any other.
But let’s focus on Stored Procedures (although a lot of my arguments could count for any change on SQL). And yes, I expect lots of people not agreeing with this post. Before you do .. try to see it from my point of view: being responsible for +300 customer databases in a variety of NAV versions.. . “Repeatability” comes to mind .. . “No exotic, strange, unsupportable, … solutions” as well.
So, why don’t you like Stored Procedures on NAV databases?
For me, a solution based on Stored Procedures smells a lot like “I just do it like that because I can”. It’s the combination with NAV that doesn’t make it really interesting if you ask me. As you know, a lot in SQL Server is managed by the NAV environment, like:
- Creation of indexes
- VSIFT views
- Creating/changing Tables
- Creating/changing Fields
you doing some manual changes on top of that, means that it is NOT managed by NAV, which might result that (1) your change might just disappear because it’s overwritten by NAV, or (2) your change results in a conflict (error) with the NAV system.
Remember the PowerShell-command “Sync-NAVTenant” that is needed sometimes to sync the metadata with the SQL Schema. A Sync. Between two things: a metadata-table and the actual schema on SQL. Any manual change that disturbs the two might end up in a complete “un-ability” to ever synch the schema again. A “disturbance in the force” is easily and unintentionally done. To fix things like this can be very time-consuming. Ok, may be nothing to do with Stored Procedures – but something to think about nevertheless..
Separation of business logic
Any software doesn’t need to be just “working”. Things like “upgradability”, “maintainability”, “readability”, … are equally important! When you are introducing Stored Procedures as being part of your solution, you definitely are NOT making it more readable, maintainable, .. .
Just imagine that in a few years, when you’re enjoying your pension, other people have to extend your solution .. with parts in SPs, and parts in C/SIDE. You really think they are going to figure out that cryptic T-SQL? As business logic. Or part of it?
One of the biggest reason to NOT use Stored Procedures. The two things you do most when calling an SP: read and/or write.
When you just want to read data – just do it with queries and C/SIDE? You’ll use the NST cache, which even might speed up the process as well.. .
When you want to write data – then you actually should just go on a pension right away – you don’t belong to work on NAV databases. Sorry, but that is my opinion. People lose fingers over this in my company ;-).
Writing on SQL server without going through the NST means that everything you will touch, will not update the cache, even more your cache will be out of sync, resulting into showing different data in NAV than the actual data in the tables. Things like this lead to a very unstable and unreliable system and should be avoided by all times.
You won’t have access to flowfields because these are calculated by the service tier. I have seen people “simulating” the business logic in T-SQL that results in the same result – but then again – what if I change the “business logic” (i.e. calculation) of the flowfield? What if Microsoft updates it after an upgrade?
Should I say more? Duplicating code or business logic is like code cloning. Don’t do it! It’s paving the road to disaster!
There are indeed enough alternatives! We can use queries in combination with ODATA web services to efficiently read data on SQL Server. We can use Web Services to call for business logic from 3rd party applications. We can even call a codeunit from PowerShell! There are many types of decent integrations – so please, always consider one that doesn’t require a change on the SQL Server database!
Only when it’s absolutely necessary AND when you know 100% what you’re doing
I rarely see a situation where there is no way around. Performance can be a reason. Like I have been truncating a table because a DELETEALL just took too long. But I see lots of situations where SPs might have been avoided, and were considered a good solution “because he/she was not familiar with the concept of web services”.
All I can say is: make sure you know what you’re doing. And if you do go ahead with it, make sure you document it to all parties that are involved with the database – because they might not understand why you’re doing it the way you’re doing it.. or even not realize that you did.
In my 15 years in the world of NAV-craft, I can only think of two examples where I have actually implemented something straight onto SQL Server. One of which was because there were no web services just yet.
The first reaction I have when I see solutions with Stored Procedures is “that must be ‘Job Protection'”, because usually, there is a much more elegant solution available. Please don’t make any solution even more complex as it already is – unless there is really no other way .. .
Needless to say that this blogpost is completely and only based on my own opinion.