Though the specifics of setting up a SQL Server database for NAV will be discussed in Chapter 6, Performance Tuning, this section describes the raw method of how to do it.
Open the shortcut for the Dynamics NAV Classic client for SQL (finsql.exe). Once there, go to File | Database | New. Specify the SQL Server name in the Server Name field; we may also lookup the SQL Server name with the drop-down Assist Edit button provided.
The next option is to select the type of authentication—Windows or Database Server Authentication. If Database Server Authentication is selected, the User ID and Password for the database login will have to be specified. If Windows Authentication is selected, the system uses a Windows user login to log on to the SQL Server database. It is worth mentioning that the user selected in this step must have appropriate permissions to create SQL databases.
The next window asks for the database name and shows the following options:
- The database and transaction log file: There is an option to select Location of the files, Size, File Growth (amount by which the data file will expand in percentage, MB or KB), Unexpected Growth (Yes/No), and Maximum Size (MB).
- On the Collation tab, the following fields need to be specified:
- Windows Collation
- SQL Collation
- Collation Description (type)
- Validate Code Page
When creating a new database, the SQL Server collation is selected by default (Windows Collation or SQL Collation). It is recommended to select Windows Collation, which is closely related to our local, regional, and language settings. We can learn more about Collation in Chapter 6,
The next tab for Options has the following options:
- Members of db_owner, db_creator, or sysadmin: This limits access to the database to the users of these three SQL Server roles.
- Single User: This setting limits the access of a database to only one login at a time.
- Recovery Model: There are three options (Bulk-Logged, Full, and Simple) used for the way that the transaction log is managed for the SQL database:
- Bulk-Logged: The transaction log will contain information about the large transactions only. This model provides support against disk failure and does not affect the performance as much as the full mode does.
- Full: The advantage that full log has over all other models is that it guarantees the recovery of the database to the point of failure. It is advisable to use this method for production databases, provided the resources are available to do so.
- Simple: This is recommended to be used for development databases or non-production databases.
- Auto shrink: This provides the option to shrink the database automatically. This operation is performed by SQL Server. This option has had some performance issues associated with it while using with Dynamics NAV.
- Allow Find As You Type: This allows us to use find-as-you-type while searching for records in a form.
- Enable for Microsoft Dynamics NAV Server: This is a new feature added in the NAV 2009 release; it provides additional support for the new middle tier to be connected to the database.
The following set of options is under the Integration tab:
- Maintain relationships: This determines if the SQL Server will maintain relationships between tables that are defined using the table relations in Dynamics NAV.
- Save license in the database: This is an important property that allows the user to save the license in the database. Therefore, if we have multiple licenses that need to be installed on the SQL Server databases, we could store the license in each of the databases instead of storing it in the SQL Server.
The following set of options is under the Advanced tab:
- Lock timeout: The user can specify if the client will wait for the locks to be resolved by themselves or it will put a lock on the session that has already been locked.
- Timeout Duration (sec): Specify the time that the session will wait for the lock to clear itself before it locks the session resource.
- Always rowlock: SQL will decide what level of locking to choose, if this option is not selected. If this is selected, Microsoft Dynamics NAV will place row-level locks.
- Security- Models: Discussed in Chapter 4, Securing Dynamic NAV Applications.
- Caching- Record Set: Define how many recordsets are cached when fetched from the SQL Server using a single FINDSET operation. It will be slower to fetch a recordset that has more than the number specified here, using FINDSET.
Once all the relevant settings are done and an SQL database created, a backup of the CRONUS database can be restored from the program files folder of Dynamics NAV to create a demo database. For more information on how to restore backups, see Chapter 5.
An object file (.fob) can also be imported to create a "blank" NAV database.