My SQL Server 2014 gets flooded and stops responding

All right, I must confess;  my SQL Server is not installed on a super computer.  Also, it is not installed and configured by a SQL Server MVP nor by Alain Krikilion (picture from NAV TechDays 2013).Alain_Krikilion

The Hyper-V machine running my SQL Server is using two processors and 12GB of memory.  Operating system is Windows Server 2012 R2 x64 and SQL Server version 2014.

I have two NAV 2016 CU5 instances on two servers running a multi tenant application.  Every time I started the second instance everything froze.  Even the SQL Server stopped responding.  I am not going to pretend that I understand the issue, but still, I found a solution and wanted to share that with you.

We have a property for the NAV Service called “Max Concurrent Calls”.  This is, by default, set to 40.  I found out that if I lowered this value to 10 on the second instance I was able to start it.  That is a workaround, not a solution.  What if I want to start the third service instance, do I then need to lower that even more or update the configuration for all the previously running instances?

So I turned my attention to my SQL Server.  On MSDN Microsoft states:

This topic describes how to set the user connections server configuration option in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL. The user connections option specifies the maximum number of simultaneous user connections that are allowed on an instance of SQL Server. The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that the maximum (32,767) user connections are allowed.

This page also shows an example on how to configure this setting.  In that example the property is set to 325.  So, I tried that, returning the “Max Concurrent Calls” back to the default value for all NAV Services.  Happy times, everything is running smooth.

So how can two NAV Servers that have “Max Concurrent Calls” set to 40 flood a SQL server that supports 325 concurrent user connections?  I even looked at the network connections by executing

netstat -nao | find “1433” > connections.txt

in command prompt and looking at the output.  The total number of connections was less than 50!  I think I must leave this to Microsoft or my friend Alain to explain this.

So it was clear; the Next, Next, Finish methodology failed me this time.  I needed to get my hands dirty and fix the SQL Server configuration.

First, I looked at the “Max Degree of Parallelism” property.  The default value is zero but is should be set to “No. of CPUs” – 1.

Parallelism

Then updated the connections property.

UserConnections

I restarted the SQL Server Service to apply changes.

I updated one more property and below is the SQL Query I used to update these properties.

 

Why this turns up in NAV 2016 and not in previous NAV versions, what changed, I don’t know.  Perhaps someone out there has the answer.

If you get stuck in a problem like this, see if you have NAS services running on both instances.  That seemed to be my problem.

SQL Native Client Version

I wrote a solution to log blocking in NAV with the help of Microsoft SQL Native Client.  I wanted to be able to use this solution for a client but then ran into a problem with the SQL Native Client.  There are three versions out there and the connection string must include the version installed on the client machine.

The first step is to find the “Program Files” folder
[code] LOCAL PROCEDURE GetProgramFilesPath@1200050010() ProgramFilesPath : Text[1024];
VAR
EnviormentPath@1200050000 : Code[50];
BEGIN
IF ISCLEAR(SystemShell) THEN
CREATE(SystemShell);
EnviormentPath := ‘PROCESS’;
SystemEnviroment := SystemShell.Environment(EnviormentPath);
ProgramFilesPath := SystemEnviroment.Item(‘ProgramW6432’);
IF ProgramFilesPath = ” THEN
ProgramFilesPath := SystemEnviroment.Item(‘ProgramFiles’);
END;[/code]and the System32 folder[code] LOCAL PROCEDURE GetSystemRootPath@1100408000() SystemRootPath : Text[1024];
VAR
EnviormentPath@1200050000 : Code[50];
BEGIN
IF ISCLEAR(SystemShell) THEN
CREATE(SystemShell);
EnviormentPath := ‘PROCESS’;
SystemEnviroment := SystemShell.Environment(EnviormentPath);
SystemRootPath := SystemEnviroment.Item(‘SystemRoot’) + ‘\System32’;
END;[/code]

Then I use the EXISTS function to check for the client version.

[code] LOCAL PROCEDURE OpenConnection@1200050006(ConnectAsUserID@1200050003 : Text[30];ConnactAsPassword@1200050002 : Text[30]);
VAR
MyServer@1200050001 : Record 2000000047;
MyDatabase@1200050000 : Record 2000000048;
ClientVersion@1100408000 : Text[30];
ProgramFilesPath@1100408001 : Text[50];
SystemRootPath@1100408002 : Text[50];
BEGIN
IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ISCLEAR(ADORecordset) THEN
CREATE(ADORecordset);

IF ISCLEAR(ADOStream) THEN
CREATE(ADOStream);

IF ADOConnection.State = 1 THEN
EXIT;

IF ISSERVICETIER THEN BEGIN

IF ISCLEAR(DomDoc) THEN
CREATE(DomDoc);

DomDoc.load(APPLICATIONPATH + ‘CustomSettings.config’);
DomNode := DomDoc.selectSingleNode(‘//appSettings/add[@key=”DatabaseServer”]’);
MyServerName := DomNode.attributes.item(1).text;

DomNode := DomDoc.selectSingleNode(‘//appSettings/add[@key=”DatabaseName”]’);
MyDatabaseName := DomNode.attributes.item(1).text;

END ELSE BEGIN
MyServer.SETRANGE("My Server",TRUE);
MyServer.FINDFIRST;
MyServerName := MyServer."Server Name";

MyDatabase.SETRANGE("My Database",TRUE);
MyDatabase.FINDFIRST;
MyDatabaseName := MyDatabase."Database Name";
END;

ProgramFilesPath := GetProgramFilesPath;

CASE TRUE OF
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\90\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI’;
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\100\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI10’;
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\110\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI11’;
ELSE
BEGIN
SystemRootPath := GetSystemRootPath;
CASE TRUE OF
EXISTS(SystemRootPath + ‘\sqlncli.dll’):
ClientVersion := ‘SQLNCLI’;
EXISTS(SystemRootPath + ‘\sqlncli10.dll’):
ClientVersion := ‘SQLNCLI10’;
EXISTS(SystemRootPath + ‘\sqlncli11.dll’):
ClientVersion := ‘SQLNCLI11’;
ELSE
ERROR(Text002);
END;
END;
END;

IF ConnectAsUserID <> ” THEN
ADOConnection.Open(
STRSUBSTNO(‘Provider=%5;Server=%1;Database=%2;Uid=%3;Pwd=%4;’,
MyServerName,MyDatabaseName,ConnectAsUserID,ConnactAsPassword,ClientVersion))
ELSE
ADOConnection.Open(
STRSUBSTNO(‘Provider=%3;Server=%1;Database=%2;Trusted_Connection=yes;’,
MyServerName,MyDatabaseName,ClientVersion));

ADOConnection.CommandTimeout(0);
END;[/code]

where Text002@1100408002 : TextConst ‘ENU=Microsoft SQL Native Client not found;ISL=Microsoft SQL Native Client finnst ekki.’;

The Kill Idle batch

In an earlier post I showed how to kill idle sessions from C/AL code.  I have been asked to provide a batch that works with Job Queue.

To set this up please add a field to table 91.  Field name is “Kill Idle Duration” and the data type is “Duration”.  In my case this field has the number 50000.  If that number is unavailable just use another number and import the text object and compile.

Attached is the report that can be used in the job queue.  Just make sure that the user running the job queue has enough permissions as shown in my earlier post.

If this is of any value to you please check my copyright page and donate to the web.

Report 89209

Importing data from MS SQL via CSV File

I have a database with a number of tables that I need to import into NAV in order to create a NAV solution to replace the old outdated solution.

The first step was to build identical tables in NAV.  In my case this was done manually, but there should not be difficult to convert a “CREATE TABLE” command into a NAV object text file.

I then got the name of all the tables in the database with

and copied the result to Excel. In Excel I first created the select statement in column C

and then created the sqlcmd function in column G

Then I fill down column C and column G and have all the lines for my command file ready. Note that I use “;” as a decimal separator.

I create a folder for the data and within the folder I create a file called ExtractData.cmd and copy all the lines from column G to that file. Then this command file is executed and that folder fills up with CSV files for each table.

The final step is to import the data with the attached NAV Report.

MSSQL-CSV Import