Nav developer: Basic SQL - Overview of NAV specific SQL features for application consultants - Dynamics CRM: Blogs

Blog bot · 05.11.2008, 11:52

As the title says, this post is not really for NAV developers, but for application consultants who don't use SQL Server every day but still need to know enough about SQL Server to at least get a Microsoft Dynamics NAV database to run on it.

The challenge is, that even a basic SQL course or book will cover material which is 85% unrelated to NAV, and cover SQL features which you may never use when you only need to run NAV on a SQL server in a test environment. So the point of this post is to explain the basic steps needed to get NAV up and running on SQL Server (2005 / 2008) in the simplest and shortest way possible.

I will assume that the instructions here will only be used to get NAV to run in a test environment, and not cover any features like security and performance which should be considered for a live system. Also, I will only explain the minimum steps needed to get NAV to run - not any background of why this or that needs to be done.

This post contains a very general overview, and then links to a number of seperate posts for each individual area. So you can use this for an overview / table of content, and then link to the specific topic that you need.

These are the areas planned for now - more may follow:

All the posts are tagged "Basic SQL", so you can list them from here.

1. Installing SQL Server

When installing a NAV client you have the option to install the SQL Server engine as well, and the installation will automatically attach a NAV database ready to use. As long as this works for you you don't have to install SQL Server as well. But if something needs changing or troubleshooting, or if you need to attach a different SQL database, then you need the admin tool (SQL Server Management Studio), which is when you need to install SQL Server.

The installation is fairly straigthforward. You should just change two of the default options:

Set all SQL Server services to use the System account, and
change the authentication mode from "Windows Authentication" to "Mixed Mode"

For further details, go to this post:

Installing SQL Server

2. Create stored procedure from xp_ndo.dll for Windows authentication

NAV requires two extended stored procedures called xp_ndo_enumusergroups and xp_ndo_enumusersids, both based on xp_ndo.dll (can be found in the sql_esp folder on the product DVD). If these extended stored procedures are not present, and you try to log in with Windows Authentication, you will get this error message:

====
The extended stored procedure xp_ndo_enumusersids in the library file xp_ndo.dll, is not available on the LOHNDORF1 server. Until this procedure and library have been added, it will not be possible to connect to this server from Microsoft Dynamics NAV with Windows Authentication, but you will still be able to connect with Database Server Authentication. You can read more about adding this extended stored procedure in the help pages on the product CD. Follow the hyperlink to the readme.txt file on the Servers page under the Documentation section of SQL Server.
====

For details on how to create these two extended stored procedures, follow this post:

Creating extended stored procedures in xp_ndo.dll

3. Enable trace flag 4616

Since NAV version 4 Sp3 the NAV client requires this trace flag to be enabled on SQL Server. If it's not, then you will get this error when trying to connect to a SQL database from NAV:

====
The trace flag 4616 is not set on the server (local). You must set this flag and restart the server before you can connect using Microsoft Dynamics NAV.

====

Enable the trace flag by adding ;-t4616 to the end of startup parameters, and then restarting the SQL Server service. For furhter details how to do this, follow this post:

Enabling trace flag 4616

4. How to create a NAV database

Creating a new database on SQL Server is almost identical to creating a new database on native: File -> Database -> New, then enter name etc. And, from there restore a NAV backup (.fbk file). More details - if needed - here:

Creating a NAV database from a NAV backup (.fbk file)

5. Attach a SQL database

Attaching a SQL database is done entirely from the SQL Server environment (SQL Server Management Studio). It's not often you would need to do this. But for example if you want to attach the demo database from the product DVD, then this is how to do that:

Attaching a SQL database

6. Restore a SQL backup of a NAV database

With NAV on SQL Server you can either use the native backup or SQL Server backups. If a backup was done with SQL Server, you also need SQL Server to restore it. SQL Server backups are generally quicker than a NAV backup, but also often larger. Your SQL Server backup strtegy can be as complex as you like, but restoring a singe, complete backup is quite simple:

Restoring a SQL backup of a NAV database

7. Creating users on SQL Server for use in NAV

When setting up a user for NAV, this user first has to exist on SQL Server. So you have to create the user under Security -> Logins in SQL Server Management Studio before creating them in NAV:

Creating users on SQL Server for use in NAV

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.