Configuring Database Deadlock Detection and Monitoring

On NAV Techdays 2016 in Antwerpen (Belgium) Microsoft demonstrated a beautiful new feature. Microsoft Dynamics NAV 2017 now offers ‘Database Deadlock Detection and Monitoring’. If NAV 2017 detects a deadlock it will rollback one of the transactions.

If you run the NST under a serviceaccount you need to apply some extra configuration to make this work:

  • Alter any event session
  • Grand View Server State

Go to the properties of your serviceaccount in SSMS and go to the ‘Securables’ tab:

alter-any-event-session

view-server-state

nav-deadlock-monitoring-configuration-sql-server

It’s also possible by executing some T-SQL:

use [master]
GRANT ALTER ANY EVENT SESSION TO [CONTOSO\sa_nav]
use [master]
GRANT VIEW SERVER STATE TO [CONTOSO\sa_nav]

If you don’t do this you will notice errors like this in your application Log:

Server instance: NST2017-RTM
Category: Sql
ClientSessionId: d7ea6dfa-622c-48e1-a6ed-9d5d8402b17c
ClientActivityId: a419d67f-0f56-4394-93cc-b5a1e753344b
ServerSessionUniqueId: 857d43f9-be50-4f2e-abcf-533364da2935
ServerActivityId: 2364cada-9b04-49e4-9268-25260ecdd20f
EventTime: 12/05/2016 16:16:04
Message <ii>Deadlock monitoring feature is enabled but current SQL server user does not have ‘VIEW SERVER STATE’ or ‘ALTER ANY EVENT SESSION’ permissions for the database: NAV2017RTM. Please grant both of those permisions in order to use this feature.</ii>
ProcessId: 4052
Tag: 00000DE
ThreadId: 9
CounterInformation:

By Default DeadLock Monitoring is already enabled on the NAV Service Tier (NST):

enable-deadlock-monitoring

Advertisements

Show OData webservice response directly in IE

As a Technical consultant I need to regularly install new NAV software for our customers. After installing I always try to check if web services are also working. In NAV 2017 I check at least three things:

1. Call the SOAP URL

http://nav01:7047/NST2017-RTM/WS/CRONUS%20Nederland%20BV/Page/SalesOrder
http://nav01.contoso.lan:7047/NST2017-RTM/WS/CRONUS%20Nederland%20BV/Page/SalesOrder

First link contains the hostname. Second link contains the FQDN. Always test with both to ensure it’s working flawless!

2. Call the ODataV3 URL

http://nav01:7048/NST2017-RTM/OData/Company('CRONUS%20Nederland%20BV')/SalesOrder
http://nav01.contoso.lan:7048/NST2017-RTM/OData/Company('CRONUS%20Nederland%20BV')/SalesOrder

3. Call the ODataV4 URL

http://nav01:7048/NST2017-RTM/ODataV4/Company('CRONUS%20Nederland%20BV')/SalesOrder
http://nav01.contoso.lan:7048/NST2017-RTM/ODataV4/Company('CRONUS%20Nederland%20BV')/SalesOrder

Please note: the links of course only work in my Lab environment!

The SOAP URL shows the WSDL so that’s okay:

nav2017-soap-response

Now the ODataV3 URL shows the following response:

nav2017-odatav3-response

This isn’t the response I want to see, however… If I show the source of the page (F5) I see the OData output! So this works also! Normally this is a good thing: Internet Explorer is processing the output as an RSS feed. In this case I don’t want that, so the first thing we should do is to disable this:

Go to Internet options > Content > Settings and disable ‘Turn on feed reading view’. Close the browser and retry.

Now we see the OData response immediately! It took me some time to figure this out so I hope I can save you some time with this handy info! To test the ODataV4 URL there is also a problem with the browser. I will investigate this and share my findings soon!