Physical Database Structure of a Dynamics NAV SQL database

A Microsoft Dynamics SQL database consists at least out of three files:

– MDF File
– NDF File
– LDF File

The MDF file is the master data file and holds all the information for the other files that make up the database. The recommended file extension for MDF files is .mdf Windows Server 2012 R2 will automatically recognize this file as the ‘primary data file’ if you have installed SQL Server 2016.

The NDF file is the non-data file and holds all the information. The recommended file extension for NDF files is .ndf Windows Server 2012 R2 will automatically recognize this file as the ‘secondary data file’ if you have installed SQL Server 2016.

The LDF file is the log data file and saves transactions. The recommended file extension for LDF files is .ldf Windows Server 2012 R2 will automatically recognize this file as the ‘log data file’ if you have installed SQL Server 2016.

You could use more LDF or NDF Files and assign each one to another disk to improve your database performance.

An example:

Interesting to see that since the introduction of NAV 2016 the Cronus database now consists out of just 2 files. One data file (.mdf) and one logfile (.ldf). There are no obvious reasons anymore to use three files? In previous versions at least three files were used because of legacy reasons I think. By default SQL Server itself is also using just 2 files by default.

So I did a little bit of research and created a new NAV 2016 database from the Development Environment. The result? The traditional structure: 1 MDF, 1 NDF and 1 LDF file…

Please note: if you create multiple NDF files via the Development Environment it’s always best to put every file on a separate physical disk if possible. If that’s not possible please always try to put at least the Log Datafile on a different disk. This improves the performance for SQL’s mechanism ‘Write-Ahead Logging’ (WAL) I’m referring to this Wikipedia article which describes this important feature of SQL. On MSDN there’s also a nice article about ‘Write Ahead Transaction Logging’.

Work smarter with SQL Server Management Studio

Do you work with the ‘Microsoft SQL Server Management Studio’ regularly. Let’s say at a weekly basis? Then please read on as I have some useful information for you which saves you time and eases your activities.
When you start the ‘Microsoft SQL Server Management Studio’ you are actually excecuting ‘C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe’ You can however add some parameters like servername, instancename, databasename and credentials. Example:

Suppress the splash screen at startup:
“C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe” -nosplash

Suppress the splash screen at startup and connect to the default instance using your current Windows Account (Windows Authentication):
“C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe” -nosplash -S . -E
Please note the little dot! The little dot represents the current local SQL Server!

Suppress the splash screen at startup and connect to the instance ‘NAV’ using your current Windows Account (Windows Authentication):
“C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe” -nosplash -S .\NAV -E

If you don’t use SQL Server Management Studio 2016 please replace 130 with the corresponding version you are using.

A list of parameters you could specify:

Microsoft SQL Server Management Studio
Usage:

ssms.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P password] [-E] [-nosplash] [file_name[, file_name]*] [-log [file_name]?] [-?]

[-S The name of the SQL Server instance to connect to]
[-d The name of the SQL Server database to connect to]
[-E] Use Windows Authentication to login to SQL Server
[-U The name of the SQL Server login to connect with]
[-P The password associated with the login]
[-nosplash] Suppress splash screen
[file_name[, file_name]*] Names of files to load
[-log [file_name]?] Logs SQL Server Management Studio activity to the specified file for troubleshooting
[-?] Displays this usage information

With this information you could now easily create some shortcuts to work  smarter. An example:

I Hope I saved you a little bit of time and Mouse Clicks.

An update to SQL Server Management Studio is available

Recently I’ve been getting the message ‘An update to SQL Server Management Studio is available’:

This message pops up in my system tray. Ofcourse great information! However the message always comes at a time I don’t have the opportunity to update. My first question was: how to control the auto update feature? The steps:

Start SQL Server Management Studio
Select ‘Check for Updates…’ from the Tools Menu:

The Updates window is opened. Now click ‘Details’ in order to Enable/Disable Updates, you can now see there is a checkbox ‘Automatically check for updates…’:

 

How to create an NAV Serviceaccount

During the Setup of Dynamics NAV 2016 you are asked to provide a username and password of the NAV serviceaccount. If you don’t specify an account then ‘NETWORK SERVICE’ will be used. This is a local Built-in account.

If SQL Server is running on the same machine (a two tier setup) as the NST (NAV Service Tier) you could get NAV working also. Just grant the NETWORK SERVICE account db_owner permissions to the NAV database. If SQL Server is running on a different server (a three tier setup) you can’t grant permission to Local Builtin ‘NETWORK SERVICE’ account on the NAV machine. In order to accomplish this task you need to assign a serviceaccount to your NAV Service Tier. The following configuration is needed for the serviceaccount:

• Create a ‘Domain User’ in your AD. You can do this for example on your ‘Domain Controller’.
• Enable the account to log in as a service on the NAV server
• Enable the account to register an SPN
• Give the account necessary database privileges in SQL server

How to Grant the ‘Log on as service’ permission:
Start the ‘Local Group Policy Editor’ on your NAV server. Go to Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment and select ‘Log on as a service’.

For standard usage of NAV no additional NTFS permissions are needed.

Dynamics NAV 2016 Architecture

Microsoft Dynamics NAV 2016 has been built on a three tier architecture. Using this architecture provides scalability and flexibility. The three tier architecture consist of the following::

– Data tier
– Service tier (middle tier)
– Client tier

Each tier should be installed on a separate server but for demonstration or development purposes you could install them all on one server.

The data tier consists of Microsoft SQL Server 2012 or higher with a Dynamics NAV database. For NAV 2016 you need to have at least SQL 2012 or newer. The data tier stores all the business data.

The service tier is in fact a multithreaded Windows Service which is running as Microsoft Dynamics Server. We also call this the ‘NST’ which is an abbreviation for ‘NAV Service Tier’. The service tier handles all the authentication, business logic en communication and provides caching. The Service Tier requires at least Windows Server 2012 R2 as an OS.

The client tier provides a frontend for the users so that they can work within NAV. NAV 2016 provides several clients you could work with. For example: we have a Windows Client, Web Client, Tablet Client and a Universal Client.

The 3 tier architecture in a ‘simple’ diagram: