NAV on SQL - why 2 database files?

alexjensen

Hi all

When creating a NAV database on SQL the deafult is 2 database files and 1 log file. But what is the explanation for 2 database files and not 1? Can you have a normal setup with only 1 file or?

AJ

kriki

1 database file is for the objects. The other for the data.

The reason is that Navision (before it became Microsoft) wanted to be able to easily separate objects from the data on another drive to improve performance. In reality, it has very little impact (if any).
You can also create only 1 database file and store both data+objects in it. I have 1 or 2 customer databases with this and they just work fine. But generally I keep the standard of 2 database files.

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

bbrown

The first filegroup contains the system catalog. The second contains user tables and data, including NAV objects. While you don't always see this done, it is not unique to NAV.

rdebath

That's not really a mistake in kriki's message, it's more of a good idea.
ie: Create the database with just one data file but immediately add a second.
That way the objects are in the first file along with the user, permissions and other application tables.
It's probably the only filegroup manipulation that's generally useful as it has no maintenance issues and it can actually make a difference to Navision even on one drive.

Other changes (eg: company per filegroup) can also make a difference, but the separation will deteriorate over time as objects get added and SIFTs get recreated. So they have to be moved; using SQL. But, it all works fine and is safe.

BTW: The SQL version of Cronus is in one file.

bbrown

I never said it was a mistake. It really depends on what you mean by "objects". The SQL objects (tables, views, etc.) are part of the system catalog and are always in the primary filegroup (the first one created). The NAV objects (tables, forms, codeunits, etc.) are just data, as far as SQL is concerned. This data is in the Objects table which is created in the default file group. With standard "New Database" settings that will be the second filegroup (Data Filegroup 1). This also contains the "Data Common" tables.

Whether user and system tables are in the same or separate filegroups is not really going to make a big difference as far has how the systems runs. The difference comes in recovery ability oppurtunity. Specifically the ability to execute a "tail log backup". A "Tail Log Backup" is a backup of the active portion of the transacton log". This requires access to system catalog. If the drive(s) holding "Data Filegroup 1" fail but the system catalog is still accessable, you can recover back to the point of failure. Provide, of course, you have the prior required backups.

I've noticed that CRONUS is just 1 filegroup. I suspect it has something to do with making the "Auto Magical" install easier to develop. But you'll notice the the default "New Database" behavior still creates two.

rdebath

bbrown wrote:

The difference comes in recovery ability oppurtunity. Specifically the ability to execute a "tail log backup". A "Tail Log Backup" is a backup of the active portion of the transacton log". This requires access to system catalog.

Not true for SQL2000+, all you need is the log file ...

http://support.microsoft.com/kb/253817

Having some of the rest of the DB does make it easier though, you don't have to make a database 'similar' to the old one.

bbrown

Interesting. thanks for the info. Just shows that old knowledge doesn't always remain relevant. This was something they taught in the old "NAV SQL Install" course. It's in the manual. Don't know if they still do.

So now I'd have to say that I don't really see a difference. Maybe someone from Microsoft can chime in here?