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:

nav-database-files

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’.

Advertisements

Author: Ibrahiem Rasoelbaks

I'm a Technical Consultant working at 4PS.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s