Performing a Dynamics NAV Database setup could appear as an easy step solvable in few minutes, but what is extremely important to remember is that this task will affect the database performance in the future and an aspect must always be taken into consideration: scalability.
The standard database installation process could be ok for installations with few concurrent users, but how can you handle a situation with many concurrent users and an heavy load database with a frequent size growth every day? In this case the right installation must be carefully evaluated.
I've recently received a question from a reader that has submitted me his case in details and the problems he has, so the main topic of this post wants to be a series of tips on how to perform a scalable Dynamics NAV database installation (I hope that my reader will leave here again other questions he could have).
The first advice on setting up a scalable system is using a RAID technology for your server and the best choice is using RAID 10 (0+1), that has the benefits of RAID 0 and RAID 1.
With RAID 0, data are stripped across several disks and Write/Read transactions are faster (parallel access).
With RAID 1, data are duplicated on mirror disks and disk failure can be recovered with no system interruptions. Write transactions are duplicated and read transactions are faster due to concurrent use of all functioning disks (if you use a single controller per disk you can have higher performance).
On an ERP system like NAV, with numerous write and read transactions, this is a great choice to consider.
If you're not using a stripping technology, you've to remember to start your installation by:
- creating several database files
- storing the different database files in sepatate physical disks
and then you can
- group the database files in different SQL Filegroups
- assign the NAV tables to separate files (on different SQL Filegroups), if the tables are involved in the same NAV transaction.
then create a Transaction Log file on a separate disk:
During the database creation process, you've to pay a few extra-time of attention when choosing the Recovery Model (this can affect performance and your backup strategy).
Dynamics NAV offers 3 types of Recovery Model:
- Bulk-Logged: it provides protection against media failure combined with the best performance and the minimal use of log space for certain large-scale or bulk copy operations. The backup strategy for this recovery model consists of:
- Database backups.
- Differential backups (optional).
- Full: with this recovery model, the details of every transaction are stored in the transaction log.
Full Recovery allows you to recover the database to the point of failure or to a specific point in time. All the operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk loading data, are fully logged to guarantee that the database is completely recoverable. Obviously, with this recovery model the Transaction Log size will increase a lot.
The backup strategy for Full recovery consists of: - Database backups.
- Differential backups (optional).
- Transaction log backups.
- Simple: only few details of every transaction are logged. The database can be recovered to the point at which the last backup was made (you cannot restore the database to the point of failure or to a specific point in time). This is the strategy that has the maximum performance but the disadvantages are in evidence. The backup strategy for simple recovery consists of:
- Database backups.
- Differential backups (optional).
Regarding assigning the tables to different SQL Filegroups, we can group the Dynamics NAV tables into these main categories:
- Un-posted Document Tables: Sales Header, Sales Line, Purchase Header, Purchase Line
Document Dimension - Posted Document Tables: Sales Invoice Header, Sales Invoice Line, Purchase Invoice Header, Purchase Invoice Line, Purch. Rcpt. Header, Purch. Rcpt. Line, Posted Document Dimension
- Journal Tables: General Journal Line, Item Journal Line, Job Journal Line, Journal Line Dimension
- Ledger Entry Tables: General Ledger Entry, Item Ledger Entry, Customer Ledger Entry, Vendor Ledger Entry, Detailed Customer Ledger Entry, Detailed Vendor Ledger Entry, Ledger Entry Dimension
- Register Tables: General Ledger Register, Item Register, FA Register, VAT Register
- Other Tables.
To manually tune-up an installation, we can split these tables into different filegroups, each for every of the above categories.
To place an existing table on a different filegroup, if you're using SQL Server 2000 and Enterprise Manager:
• Expand a server group, and then expand a server.
• Expand Databases, expand the database in which the table belongs, and then click Tables.
• In the Details pane, right-click the table, and then click Design Table.
• Right-click any column, and then click Properties.
• On the Tables tab, in the Table Filegroup list, select the filegroup on which to place the table.
• Optionally, in the Text Filegroup list, select a filegroup on which to place any text, image, and text columns.
If you're using SQL Server 2005 with SQL Management Studio the steps are exacly the same, except for Filegroups fields. The image below is an explanation of the SQL Server 2005 fields:
These are tips on how you can work at a more granular level in order to increase your Dynamics NAV database performance.
However, I can't close this post without saying this: too many times I see that customers works hard on the server side (SQL) in order to increase the database performance and scalability, without be worried of the code.
My experience with NAV says that 70% of the times, poor performances are caused not by a poor architecture design but comes from a bad code-writing.
The first step to do is an hard check of the code in order to retrieve the less-performant part. Remember to use in code the right indexes and to use the standard NAV best practices with SQL Server. If you've a good code, your application will perform good for a long time...