Start of this year we upgraded our NAV 2016 installation to NAV 2018 RTM. And February 26 we were live. It was a relative smooth and fast upgrade and we were quite pleased with it. We ran however into a couple of performance issues of which one them exposed itself immediately after go-live. We were pointed to it by one of our sistering team's consuming a number of NAV web services in their application. But unfortunately we couldn't get a hold on what was exactly happening, so it was lingering until a month or so later we were confronted with a seemingly different issue, but with the similar pattern.
Our sistering team reported that, while sending data to NAV through a web service the following exception occurred frequently:
For search engines sake, let me phrase the essential part of this even viewer screen:
The sql connection doesn’t have a database instance attached. This must not happen if the connection comes from the pool.
We had no idea whatsoever on where to find the cause (and solution!) and we let it be for the time being. It was not a showstopper in the operation as the major part of the web service calls were successful. Until some 6 weeks later, when a new project had started with an external partner connecting to a new web service, experiencing a same kind of issue. Roughly every 10th call connecting to NAV failed resulting in a 1000 longer waiting time and timeout:
“Apr 25 07:33:01 Update box: Time: 273 msApr 25 07:34:02 Update box: Time: 278 msApr 25 07:35:01 Update box: Time: 285 msApr 25 07:36:01 Update box: Time: 274 msApr 25 07:37:02 Update box: Time: 282 msApr 25 07:38:01 Update box: Time: 279 msApr 25 07:39:01 Update box: Time: 288 msApr 25 07:40:02 Update box: Time: 355 msApr 25 07:41:01 Update box: Time: 272 msApr 25 07:42:02 Update box: Time: 272 msApr 25 07:43:01 Update box: Time: 266 msApr 25 07:44:22 Update box: Time: 21.40 secApr 25 07:45:02 Update box: Time: 291 msApr 25 07:46:01 Update box: Time: 280 msApr 25 07:47:02 Update box: Time: 285 msApr 25 07:48:01 Update box: Time: 274 msApr 25 07:49:01 Update box: Time: 268 msApr 25 07:50:02 Update box: Time: 290 msApr 25 07:51:01 Update box: Time: 291 msApr 25 07:52:01 Update box: Time: 272 msApr 25 07:53:02 Update box: Time: 288 msApr 25 07:54:01 Update box: Time: 283 msApr 25 07:55:01 Update box: Time: 284 msApr 25 07:56:02 Update box: Time: 276 msApr 25 07:57:01 Update box: Time: 282 msApr 25 07:58:02 Update box: Time: 270 msApr 25 07:59:23 Update box: Time: 22.29 sec“
Our request for help on this issue towards MS was answered very fast resulting:
Our team investigated the exception
and discovered that we have fixed a bug for Dynamics 365 Business Central titled
Connection pool - concurrency issues when disposing NavSqlConnectionScope
that has not been backported to NAV2018 (and the exception trace that you provided looks similar to the one we saw when fixing this bug)
We have created a backport bug to have this fixed in an upcoming CU for NAV2018, preferably CU 06
And indeed it was released in CU06 for NAV 2018 as hotfix 267998. Once installed it did indeed fix the issue. No hiccups when disposing NavSqlConnectionScope.
Thanx to the MS team that helped us out.
More or less coincidentally I stumbled over the topic of this post a couple of weeks ago in my next endeavor to raise the success rate of the MS test suite on our solution. A task being picked up every once in while, next to all the normal work.
The major approach so far in getting as much as possible MS tests working has been a statistical one: look for the most occurring error and get it solved; subsequently take the next most occurring and so on. In 1 week the success rate raised from 23% to 72%. One week more we did get it to almost 80%, i.e. 79%. After six weeks altogether, spread over a couple of months, we hit the 90%. As in many other cases the Pareto principle also did apply here, and thus the remaining 10% will take almost as much time to get it successful. And you could wonder if that's worth the trouble. Well, this blog post could be seen as a proof for taken the trouble still. It has been worthwhile to continue to raise the success rate. And actually I deliberately continue for this very reason as we also started this project: to learn from the automated tests MS did conceive. And in the meanwhile we do get more tests working.
So how did I "stumble over"? Instead of using the statistical approach as pointed out above ("solving the most occurring error") I looked at the left side of my percentage graph (that's where you can find the lower test codeunits that hold the tests that are most meaningful to us; where those on the right are for a bigger part testing production and warehouse management, at this moment of lesser or no use at all to us):
And I focused on the white ditches between the blue. The deeper the lesser successful. And maybe the easier to get successful.
The one that struck my eye most was the one white all the way down.The narrow grand canyon.Or gorges du Tarn, being a Francophile. Do you see it? Somewhat left of the middle. 0% success in this test codeunit. It turned out to COD134926 (Table Relation Test). To be honest I didn't look at the name, and opened it in design mode to find out there was only one test function. Was I going to put my time on getting this fixed? So I looked at the codeunit name, I looked at the error thrown and studied the code closer.
Table Relation Test
The type of Table 23 in Field 50012 is incompatible with Table 50026 Field 1.
[Test] ValidateFieldRelationCompatibility()
LOCAL ValidateFieldRelation(VAR TempTableRelationsMetadata : TEMPORARY Record "Table Relations Metadata") : Boolean
Aha, it's checking the technical validity of a table relation between source and target field that make up a table relation:
Or as the in code explanation says:
// <Summary> Fields must have the exact length of the largest field they relate to.// <Summary> Fields must have the same type as the fields they relate to. Exception: if it relates to both Code and Text it must be Text
Wow. We have been cleaning up our code a lot in the last year, but hadn't been able to get this tackled, and now we did hit upon this nice gift. It pointed out - ouch, do I dare to say this - ... more than 50 incompatible table relations. Needless to say these were injected in our code long, long ago.
If, after all my previous posts on the test toolkit, you're still looking for reasons to start using it. Now you have surely one (more), be it a small, and very useful one.
Tomorrow exactly one month ago I wrote this post, based on the update in a number of test codeunits in NAV 2018 CU3: Let's talk about Shared Fixture and how to profit from this with the Dynamics NAV Test Toolkit. Just now I finished upgrading our test code to NAV 2018 CU4. It was a lot of work as I had to manually check a lot, but, wow, MS did an even greater job. The redesign introduced in CU3, and implemented on the first 100 test codeunits, now has been continued on an even larger number of test codeunits. Due to this I was able to revert our customization substantially:
Release
# Customized Test Codeunits
NAV 2018 RTM
532
NAV 2018 CU3
492
NAV 2018 CU4
200
Keep the good work going ... (as I have still 200 of 720 customized ;-)
It's been a while that I wrote on NAV and automated tests. In the meanwhile I have been teaching a lot all over Europe, and, yes, advocating test automation in NAV. And what did you do since my last post How-to: Run Standard Tests against Your Code? Did you dare and try? And did you also have the time/guts to continue with it? I know some that did.
In the same meanwhile I succeeded to get MS listen and implement a major request I had posed already some time ago. We'll get to that below as it relates to the topic I would like to talk about here: Test Fixture.
If you have been testing software, and probably you do being in my audience, you know that a major part of your testing effort is getting data prepared to allow you to execute your tests. Having been a tester I dare to claim it is THE major part. Test Fixture is the general term for the setup up your system before you execute your test(s), be it software or hardware.
With respect to NAV test automation I have adopted the three level approach Bas Graaf used in his NAV Tech Days presentation ages ago, which he based on xUnit Patterns:
Applying this to NAV:
When I started the project to get the Dynamics NAV Test Toolkit run on our code as per How-to: Run Standard Tests against Your Code, it was giving this result:
23 % percent of a total of 16.124 NAV 2016 tests where successful. Using a statistical approach, tackling the most occurring issues first, I could raise this to 72 % in less than a week. And mainly by focusing on the lazy setup:
By creating one generic Initialize function of our own:
Initialize() IF NOT IsInitialized THEN BEGIN IsInitialized := TRUE; N4LibraryLabel.CreateLabels; N4LibrarySetup.CreateTNTSetups; N4LibrarySetup.SetCompanyInformation; N4LibrarySetup.SetWarehouseSetup; N4LibrarySetup.SetInventorySetup; N4LibraryERM.CreateVATPostingSetups; N4LibraryERM.UpdateVATProdPostingGroups; N4LibrarySetup.SetPurchaseSetup; N4LibrarySetup.CreateSalesSetups; N4LibrarySetup.CreateVDESetups; N4LibrarySetup.CreateOWSSetups; N4LibrarySetup.CreateOWSCommunicationSetups; N4LibrarySetup.CreateCBSetups; N4LibrarySetup.CreateOWSNumbersSetups; N4LibrarySetup.CreateMultiple3SNoSeries; N4LibrarySetup.CreateTransactionModes; N4LibrarySetup.CreateMultiplePaymentTerms; N4LibrarySetup.CreateTermPeriodDefinitions; N4LibrarySetup.CreateOWSSelections; N4LibrarySetup.CreateReqWorksheetTemplates; N4LibrarySetup.CreateDeliverabilityCodes; N4LibrarySetup.UpdateItems; END;
... that I hooked into the local Initialize function of each test codeunits, like in the following example in COD134126 (ERM Reversal VAT Entries):
LOCAL Initialize() LibrarySetupStorage.Restore; IF IsInitialized THEN EXIT; //<<< N4LibraryInitialize.Initialize; //>>> LibraryERMCountryData.CreateVATData; LibraryERMCountryData.UpdateGeneralLedgerSetup; LibraryERMCountryData.UpdateGeneralPostingSetup; LibraryERMCountryData.UpdateVATPostingSetup; IsInitialized := TRUE; COMMIT; LibrarySetupStorage.Save(DATABASE::"General Ledger Setup");
What the h*ck, lazy setup? Sho-go-nai as the Japanese say, as I learned form Mark Brummel: Nothing can be done about it. Yes, I had to modify 480 test codeunits (of the total of 628) to get this working! And I told MS that I had to. This could have been prevented by a better design of there Initialize function from the start. And yessss, MS listened and now with NAV 2018 CU3 a redesign has been implemented in the first 100 test codeunits:
LOCAL Initialize() //<<< LibraryTestInitialize.OnTestInitialize(CODEUNIT::"ERM Apply Sales/Receivables"); //>>> LibraryVariableStorage.Clear; LibrarySetupStorage.Restore; IF isInitialized THEN EXIT; //<<< LibraryTestInitialize.OnBeforeTestSuiteInitialize(CODEUNIT::"ERM Apply Sales/Receivables"); //>>> LibraryERMCountryData.CreateVATData; LibraryERMCountryData.UpdateAccountInCustomerPostingGroup; LibraryERMCountryData.UpdateGeneralLedgerSetup; LibraryERMCountryData.UpdateGeneralPostingSetup; ModifyGenJnlBatchNoSeries; isInitialized := TRUE; COMMIT; LibrarySetupStorage.Save(DATABASE::"General Ledger Setup"); //<<< LibraryTestInitialize.OnAfterTestSuiteInitialize(CODEUNIT::"ERM Apply Sales/Receivables"); //>>>
where LibraryTestInitialize is COD132250 (Library - Test Initialize) and contains these three event publisher to which we now can subscribe:
[External] [IntegrationEvent] OnTestInitialize(CallerCodeunitID : Integer) [External] [IntegrationEvent] OnBeforeTestSuiteInitialize(CallerCodeunitID : Integer) [External] [IntegrationEvent] OnAfterTestSuiteInitialize(CallerCodeunitID : Integer)
So getting your lazy setup done on standard tests is a no-brainer. Another threshold taken to get test automation going.
The other day I wanted to get my local NAV 2018 installation fully working. Fully local, including VSCode. And ... setup an extra database with its own service tier.
In the end it was quite easy, not in the least to some very helpful resources (see below), but I ran into a couple of other issues I needed to get fixed. It might be of help to you. Knowing I will typically forget the details, it surely will to be me in the near future. But take advantage of it, if needed. And if you have other details to share be my guest below.
The following steps are in the order in which I tried to get started with VSCode. Typically some steps could be in changed order.
Of course both of these needs to be installed first. Note that the VSCode installation is not part of the Dynamics NAV 2018 installation. Be sure to have the installation option Modern Development Environment selected.
Just for names sake let's call the database My CRONUS and the service tier MyCRONUS110. Port numbers 7145, 7146, 7147, 7148 and 7149.
I.e. get VSCode and NAV connected. Follow Erik Hougaard's post to get that done. Thanx, Erik.
Changing the Enable Developer Service Endpoint needs a service tier restart, but this failed.
A service tier failing to start often has to do with the fact the either (1) the service account is not dbowner on the datbase it is servicing or (2) the URL's for the web services ports have not been registered. As the service tier had been running with no problem before I enabled the Developer Service Endpoint it probably needed the URL registered for Developer Service Endpoint: For this Arend-Jan Kauffmann's August 28, 2014 post did come to rescue me, as so many times before.
netsh http show urlacl
... did show it was not registered. So I executed the following command:
http add urlacl url=http://+:7149/MyCRONUS110/ user= "NT AUTHORITY\NETWORK SERVICE" listen=yes delegate=no sddl="D:(A;;GX;;;NS)"
Yoohoo, the service did start.
I am not fully sure, but it seems that developer service endpoint port URL is often not automatically registered.
Yesss, I opened VSCode and did create a new AL project using our future spell AL: Go!
One of the first things to do is setup the launch.json. BTW: calling AL: Go! will urge you to fill out a number of things in the launch.json. A very useful help is another post by Arend-Jan Kauffmann, on setting the
Web client URL? Did I have that? Nope, because I did a standard installation and did no have one setup for my new database My CRONUS and service tier MyCRONUS110. So, let's ...
Based on this msdn post I created a new web server instance with
New-NAVWebServerInstance -WebServerInstance MyCRONUS110 -Server MyLaptop -ServerInstance MyCRONUS110
And to make sure it accesses the right client port I updated the Microsoft.Dynamics.Nav.Client.ClientService.dll.config:
<add key="ClientServicesPort" value="7146" />
and the navsettings.json:
"ClientServicesPort": 7146 "ManagementServicesPort": 7145 "UnknownSpnHint": "(net.tcp://localhost:7146/MyCRONUS110/Service)=NoSpn"
Well, I didn't just do it like that straight away, to be honest. I did one after the other, in hindsight getting closer and closer to the solution. With each of these changes I restarted the IIS service.
... and it worked. The web client opened up showing, when click on the Customers link the message as programmed in the HelloWorld.al on the OnOpenPage trigger on Page 22.
With a standard (demo) installation, that includes the Web Server Components and the Modern Development Environment options, many of the above is already setup.