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 DatabaseServerAuthentication 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:
• Thedatabaseandtransactionlogfile: There is an option to select Location ofthefiles, Size, FileGrowth (amount by which the data file will expand in percentage, MB or KB), UnexpectedGrowth (Yes/No), and Maximum
Size (MB).
• On the Collation tab, the following fields need to be specified:
° WindowsCollation
° SQLCollation
° CollationDescription (type)
° ValidateCodePage
When creating a new database, the SQL Server collation is selected by default (WindowsCollation or SQLCollation). 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, Performance Tuning.
The next tab for Options has the following options:
• Membersofdb_owner, db_creator, orsysadmin: This limits access to the database to the users of these three SQL Server roles.
[ 37 ]
• SingleUser: This setting limits the access of a database to only one login at a time.
• RecoveryModel: 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.
• Autoshrink: 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.
• AllowFindAsYouType: This allows us to use find-as-you-type while searching for records in a form.
• EnableforMicrosoftDynamicsNAVServer: 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:
• Maintainrelationships: This determines if the SQL Server will maintain relationships between tables that are defined using the table relations in Dynamics NAV.
• Savelicenseinthedatabase: 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:
• Locktimeout: 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.
• TimeoutDuration(sec): Specify the time that the session will wait for the lock to clear itself before it locks the session resource.
• Alwaysrowlock: 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.
[ 39 ]
Once all the relevant settings are done and an SQL database created, a backup of the CRONUS database can be restored from the programfiles folder of Dynamics NAV to create a demo database. For more information on how to restore backups, see Chapter 5, Backups.
An object file (.fob) can also be imported to create a "blank" NAV database.