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

BC 21 and Higher - PowerShell Cmdlet (Replacement of Business Central Administration).

Hi Readers, As discussed in last article about deprecating of Business Central Administration, there are few common actions that we use in administration till Business Central 20. For our on-prem customers, we will still require doing activities. As Microsoft suggest we need to start using PowerShell cmdlet.    Let's see how to do those via PowerShell, or Administration Shell. I will be keep adding commands as you comment to this article.

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 - 

MSDYN365BC - Data Upgrade To Microsoft Dynamics 365 Business Central on premises.

Hi Readers, We have already talked about the number of steps for upgrading to Business Central on Premises from different NAV versions. After that article, I received multiple requests for an article which list down steps for Data Migration. In this article, we will discuss steps of data migration to MSDYN365BC (on-Prem) from NAV 2017. For this article, I am considering a Cronus Demo Database without any customization. For an actual upgrade project, we will have to complete object merge using compare and Merge process. After the Merge Process, the next step is data migration. Let's discuss those steps. Direct Upgrade to Microsoft Dynamics 365 Business Central (on-Prem) is from following versions - 1. NAV 2015. 2. NAV 2016. 3. NAV 2017. 4. NAV 2018.