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.
To delete the users from a database during conversion from 2009 to 2015…
All fine with conversions – the chance you test the conversion is quite high, and you probably will restart NST a few times as well ;-).
I know people use it for costing but I can say that this is not nessesairy. Recently I’ve updated that C/AL code for a super large customer to run paralel with their 24/7 business. There is no reason to run code on SQL, especially now that we have smart queries.
For indexes, I completely agree. For sp, I agree as a rule. But sometimes you need to break rules to get somewhere. Like bringing down a 36 hours process to less than 1 hour. I wrote to a dedicated table that was than read by NAV after I did a SELECTLATESTVERSION.
With some changes, you can run the costing 24/7 without locking all for normal 24/7 business. Did that but costed me some time but now it is working as a charm. Ps: I also killed job queue. Still not working well. Using SQL Server Agent+PowerShell to call a webservice that does the job!
Well .. I assume you know what you’re doing 😉
Fully agree (trying to maintain a NAV database full of SP’s here, originally built in NAV4 to introduce webservices, and it’s difficult), except for the upgradeability and the elegance. First of all, I don’t share the opinion that SQL is less maintainable than NAV, especially when you start to implement custom .NET DLLs etcetera. In the last case, it’s possible to lose the source and introduce the need for a decompiler… thát is what I’d call not elegant.
Second, yes, SP’s in SQL aren’t needed anymore for NAV. But wouldn’t it be much more elegant if Microsoft had just made the link between SQL and NAV better? I think it would be truly elegant if the NST would also be triggered if a change was made on SQL level.
I have used stored procedure in some cases. But it has normally nothing to do directly with the NAV client. First of all, it is often in older Navision system and almost alway for integration purpose to other systems.
I have found that the performance on data exchange inside NAV with for instance PDM was way to slow. If i have to sync data from one system to NAV a stored procedure sometimes is a good way to go.
First of all, I can use the SQL job functionality and therefore I do not need to use all the NAS CPU time on syncing data. Furthermore it is running much faster. I have no idea why but it is.
Try for instance to do a simple job. Take a 1 mio record big change log and put some filters on here in NAV. Then do the same in a SQL query. You get the answer much faster on SQL. I modern system you can now use the Query, which is awesome and fast, but you didn’t have that option in the old days.
So back in the old days if you had to create a form that was showing the job journal lines and job ledger entries for the last 14 days og last week, in the same window you had to make a lot of code with temporary tables to compensate the missing JOIN functionality instead of just showing a view.
Sometimes I also make stored procedures in the NAV database to use in reporting services, because the select statement in the report would be way to complex or maybe impossible without.
This could of cause also be done in a separate database but the maintenance would be the same.
Agree … you should tell some ISV’s aboiut it;-)
I tried – and miserably failed .. 🙁
I’m much more concerned that people lose fingers in your company. For a developer that is serious hindrance.
We have many sql scripts that are running directly during the night or at the end of the month to INSERT, MERGE or DELETE Data in the NAV Database. You shouldn’t do anything that might cause the nav database integrity, but thats why you should know what you are doing, I guess thats why all are afraid of doing anything directly in the database, the most people are NAV developers and not database developers.
Why should I buy a developer License or Objects for NAV or pay bills to merge silly stuff throught every NAV Version if it is just a small ?
Thanks for your view on this. I really appreciate that!
If you allow me to react on that and give my personal opinion:
Updating SQL data by SP’s would completely mess up the NST data cache. After any update, you should restart the NST, of start to handle “resetting” the cache in (C/)AL code.. .
Also – in my view – you might know what you’re doing – but you never know in who’s hands the system ends up in a few years.. .
There is more to it than just “development cost” to get to a solution. Upgradability, maintenance, readability. A consistent architecture helps you in making a solution maintainable not only by the original developer, but by everyone in the long term…
Me personally, I’m totally against the developer license as well – “buying a license” doesn’t make one capable of doing ERP development or maintenance… . ;-). When my customers start to develop, I’m not eager anymore to take any more responsibility over the system.. .
Again – I don’t know the specific context, so I’m just stating my general opinion and approach .. . No judgement whatsoever.. . Thanks for keeping the discussion going!
“Writing on SQL server without going through the NST means that everything you will touch, will not update the cache”
Ok, but what will you do with the NST cache if you have 15 NST servers? A client located on an NST A will not benefit from the cache of the NST B.
Also you say one should not modify the indexes on the SQL Server directly.
Well, in my case, where the database’s size is over 1.5 TB and having some thousands users, optimised indexes (with filters and included columns) helps me to reduce the runtime significantly (by several hours per month and year closing jobs)).
And as of today you cannot specify filtered and included indexes on NAV directly
I’m not going to argue “one-off-exception-cases” in terms of best practices. That’s not going to take us anywhere, and you probably have very valid reasons anyway to do what you do. That’s why I have put in that disclaimer ;-).
Keep in mind I write my blog posts as “general” best practice for partners .. to support things like “upgradability”, maintainability, extensibility, .. .
You know that any best practice comes with limitations, where edge-cases can’t work with. And wether you like it or not – a 1.5Tb database and thousands of users – that IS an edge-case.