Filegroups for Nav 4 on SQL Server

t · 2006-07-07

I would like to gather some opinions on the idea of separating the attachment table into a differant filegroup on SQL server. This is a database with a lot of attachments (document interactions) being processed daily.

With a lot of attachments being imported, it makes sence to me to separate the table into a differant file for both performance (so you can move the file to a differant drive array) and backup reasons but it seems so "non" standard that I am a little sceptical.

I can drop and recreate the table on the new filegroup outside of Navision but I know of no way to do this inside of Navision. All seems to work.

Any opinions or discussion is welcome.

ara3n · 2006-07-30

I don't think you can change the file groups for specific tables from Navision. I don't think Navision even cares or knows whic file group the tables are. So if you've tested it and you can make backups from navision, and run test database from navision, then it's safe to do it. I think there are several discussions on this, and other people have move tables to different file groups. I think the SQL performance toolkit also mentions this and that it can be done.

ta5 · 2006-09-29

Hi
I'm wondering how others are handling this isssue. Do you use the standard setup as provived when creating a new db from within navision client, or do you use more than 1 secondary datafile?

Thanks for your input.
Thomas

Marije_Brummel · 2006-09-29

I'll have to look into how to do it, but yes, you can change these specific SQL table properties without having problems in navision.

If you create a Navision backup, the backup will contain the NAV tabledefinition and the data. This aditional information I think will be lost at restore so it is best to use SQL backup.

thaug · 2006-09-29

This is suggested by MS themsevles in the "Tuning Navision for Better Performance" document.

SQL Database Files (does not apply with stripped RAID strategies)

If stripping is not used by the database disk system:
• You must create several database files
• You must store the database files in separate disks
• You can group the database files in different SQL File Groups

To tune the workload between each database file, you can assign the Navision tables to separate files (via different SQL File Groups), if the tables are involved in the same Navision transaction/process. Standard Navision Sales and Purchases posting processes involve the following Navision table types. The tables quoted with * support heavy write transactions during these processes: they must be stored in different database files.

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… 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…

Other Tables: Value Entry*

ta5 · 2006-10-02

Thanks for the replies!

1) So does this mean that the suggested practice does apply for example for RAID 5, but not for RAID 10, because it applies for non stripping systems only?
2) How to implement above? When I create a new DB with Navi client, all secondary files go to the secondary file group of navision. So I cannot choose to which filegroup a file belongs. :?
3) If 1) applies to non stripping only, whats the strategy for stripped disk subsystems?

Thanks in advance
Thomas

kriki · 2006-10-02

ta5 wrote:

Thanks for the replies!

1) So does this mean that the suggested practice does apply for example for RAID 5, but not for RAID 10, because it applies for non stripping systems only?
2) How to implement above? When I create a new DB with Navi client, all secondary files go to the secondary file group of navision. So I cannot choose to which filegroup a file belongs. :?
3) If 1) applies to non stripping only, whats the strategy for stripped disk subsystems?

Thanks in advance
Thomas

1) NEVER USE RAID5 for database-systems!!!!

bbrown · 2006-10-02

1) NEVER USE RAID5 for database-systems!!!!

RAID 5 can be a viable and cost-effective solution for some database systems. BUT NEVER in transaction processing systems It can be useful in systems that do not experience high write activity like archive databases used for offline reporting or standby systems.

Sorry for complicating this issue.

ta5 · 2006-10-03

bbrown wrote:

NEVER USE RAID5 for database-systems!!!!

Actually I didn't want to use RAID5, but it seemed to me that the information
"SQL Database Files (does not apply with stripped RAID strategies)" implicitely included RAID5. And it did not include RAID 10, or does it? :? Sorry for the confusion.

Anyway, I'm interested in a kind of best practice. Any comments to my questions 2) and 3)?

Thanks for clarification and info.
Thomas

ta5 · 2006-10-04

nobody?

DenSter · 2006-10-04

RAID 10 is also a striped RAID level. Check out this website for more information. Google knows a ton more website that will tell you all about the systems.

I think you set up filegroups in SQL Server, not in NAV. Make sure though that when you assign a table fo a filegroup, that it keeps the filegroup when you save the table in NAV.

ta5 · 2006-10-04

@DenSter

Thanks for your reply.
Sorry, I was not clear enough, my english is not too good..

As far as I understand, in thaughs answer there was a recommandation concerning file groups, but this recommandation does not apply for striped systems.

1) So I wonder what is the recommandation for a striped system like RAID10?
2) For non-striped systems, are the tables manually (in enterprise mgr) moved to the different filegroups?

Thanks
Thomas

Nobody · 2006-10-04

I personally can only think of two reason to use mutiple filegroups with NAV.

1. For performance, for larger database with high transaction volume you could put large heavily used tables such as Value Entry, Item Ledger Entry, and G/L Entry in their own file group(s) on one or three ndf files and put them on their own set of physical drives. Putting multiple filegroups on the same set of logical and physical drives will provide no performance improvement.

2. Multi-company, if you have many companies in a single database you could put each companies tables on their own filegroup that way you could back up and restore each company individually.

That is my 2 cents.

DenSter · 2006-10-04

Nobody wrote:

2. Multi-company, if you have many companies in a single database you could put each companies tables on their own filegroup that way you could back up and restore each company individually.

Have you made that work? What about shared tables?

bbrown · 2006-10-04

A filegroup in SQL may contain multiple files. SQL will strip the data across the files much in the same way the native database does.

Nobody · 2006-10-04

I have done it but it is tricky and time consuming, but once it is setup it is nice and you can only do SQL back-ups and restores. Not that you ever really need to do a NAV restore on SQL. I would put the shared tables in there own filegroup for restore purposes.

DenSter · 2006-10-04

So you would have filegroup 1 for the shared tables, filegroup 2 for the first company and filegroup 3 for the second company. When you backup the database, can you pick and choose which filegroups to include?

bbrown · 2006-10-04

In SQL an object is assigned to a Filegroup. A filegroup may consist of 1 or more data files. A table's data and primary key must be in the same filegroup but its secondary indexes may be moved to other file groups. The performance advantage of multiple filegroups is only gained if the data files are on their own disk arrays. Most Navision databases will not get large enough to justify this cost.

This can also create issues when working through the Navision client. The Table Designer will create new tables in the default filegroup. In the example given above (DenSter), you would then have to relocate each company's tables to its own filegroup.

Howdy, Stranger!

Quick Links

Categories

Filegroups for Nav 4 on SQL Server

Comments