Skip to main content

Microsoft Dynamics NAV 2017 - Setup SQL Database Locks.

Hi Readers,

Continuing from the Last post where we discussed the new feature of Monitoring SQL Database Deadlock that is released with Microsoft Dynamics NAV 2017.

In this article, we will discuss steps to Enable the feature on a database. This article will be used when we want to set-up in a customer environment.

** For the Demo I am using Network Service account. Suggested to use a Domain account to run Navision services.

** These steps need to be done for the account that is used for Running NAV Service Tier.


For Enabling the feature, we need to do set-up in three areas -

  1. SQL Server Instance.
  2. SQL Server User.
  3. Dynamics NAV Service Tier.
1. Configure the SQL Server instance - 
Connect to SQL Server Instance using SQL Server Managment Studio. 
From SQL Server Instance Properties - grant the login both Alter any event session and View server state permissions.

2. Configure the SQL Server User - 
Connect to SQL Server Instance, Under Security Node select Logins, Select the User Account. 

From the User account Properties, Navigate to Securable, Under Explicit node grant the login both Alter any event session and View server state permissions.


3. Configure the Dynamics NAV Service Tier - 
Open Microsoft Dynamics NAV Administration, Edit the Service Instance and then select the Enable Deadlock Monitoring check box in the Database section.



4. If you would like to Use SQL Queries and Powershell cmdlets then use below commands - 

4.1- Update Microsoft Dynamics NAV Service Tier- 
--------------------------------------------------
Set-NAVServerConfiguration -ServerInstance DynamicsNAV -KeyName EnableDeadlockMonitoring -KeyValue true
--------------------------------------------------
Where DynamicsNAV - Service Name


4.2- Update SQL Properties- 
--------------------------------------------------
USE [master]  
GO  
CREATE LOGIN [domain\accountname] FROM WINDOWS   

CREATE USER [domain\accountname] FOR LOGIN [domain\accountname]   
GRANT SELECT ON [master].[dbo].[$ndo$srvproperty] TO [domain\accountname]  
GO  

USE [Microsoft Dynamics NAV Database]  
GO  

CREATE USER [domain\accountname] FOR LOGIN [domain\accountname]  
ALTER ROLE [db_owner] ADD MEMBER [domain\accountname]  
GRANT VIEW DATABASE STATE TO [domain\accountname]  

GRANT VIEW SERVER STATE TO [domain\accountname]
GRANT ALTER ANY EVENT SESSION TO [domain\accountname]
--------------------------------------------------

Where [Microsoft Dynamics NAV Database] - Navision Database Name
and [domain\accountname] - Account Use to Run Navision Service Tier.

MSDN References.

Hope you find the information useful. Let me know your views as comments to the article.

Stay Tuned for More.

Regards,
Saurav Dhyani
saurav-nav.blogspot.com

Comments

Popular posts from this blog

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

Error After Restore SQL Backup of NAV 2013 Database

Hi all, we are facing a conman issue during restoring a SQL Database backup restored for NAV 2013. While Trying to run object from Object Designer we get below listed error (even the service is configured properly) - --------------------------- Microsoft Dynamics NAV Development Environment --------------------------- There are no NAV Server instances available for this database. You must ensure that a NAV Server instance is running and is configured to use the database before you perform this activity. --------------------------- OK    ---------------------------

Future - What Next After NAV 2016. Code Name - Madeira...

Hi All, While Browsing web I came across some article which says about next release of Microsoft Dynamics NAV. Let's see the History of Code Names Given to Previous Version  - NAV 2013 - Code Name 'Sicily' NAV 2015 - Code Name 'CRETE' NAV 2016 - Code Name 'CORFU' And as we all know that all these are the Islands.