mdf vs ndf ina a SqlServer Database

asieraies

Hi,

When I create a SqlServer Database in Navision, It creates an mdf, ndf and ldf file. Ok, ldf is the Log file an I see that all my tables are in ndf file. What is the purpose of mdf file?? What does mdf file contain??

Thanks

Asier

kine

The MDF file is primary file of the database. NDF files are additional files for the database.

See e.g. http://forums.microsoft.com/MSDN/ShowPo ... 2&SiteID=1

or "Understanding Files and Filegroups" chapter in MS SQL Books on-line.

colin_leversuch-roberts

with sql server 2005 there are particular reasons why you might want to use secondary file groups. Should you want to be able to make on-line recovery ( restores ) whilst users are still working then only system objects should exist in the primary filegroup - all user objects/data should be in a secondary/user filegroup. The names are largely irrelevent and a hangover from earlier sql versions, you may call them anything you like. The whole subject of files and filegroups is a lot more complex in sql 2005, ( it's complex anyway ) and to be honest most people don't understand the why's and when's and most usage probably does more harm than good.

btw. the info in the previously posted link is both inaccuarate and out of date.

DenSter

For whatever reason, Navision back in the day decided that it would be beneficial to have a primary and a secondary data file. The way they implemented it is that by default the objects are stored in the primary, and the data in the secondary.

Because the people at Navision are actually very smart people, that usually have very good reasons for what they do, I always assumed without questioning it, that it made a big difference. Because someone I used to wrk with did question it I started asking around and I've since been informed that it is not necessary to split the files up. You can actually have just the primary data file, and your NAV database will work perfectly.

I still don't think it matters much either way, unless you have uncommon circumstances (such as a TB database for instance). If it is split up, the objects and table definitions are by default in the primary, and the data is in the secondary. The only control you have over this is by manipulating it on SQL Server directly, there is nothing you can do in the NAV IDE.

Waldo

That is indeed what I was told as well.

I guess there is a very good reason performance wise when you split up the two files onto seperate spindels... :-k .

bbrown

Placing the *.mdf and *.ndf files on different arrays can also provide an additional level of recovery. The *.mdf file contains the catalog tables. If the array containing the *.ndf file fails, and the *.mdf is still accessible, you can backup the active portion of the transaction log. This would allow recover up to the last committed transaction prior to failure. Else the recover is to the last backup.

DenSter

It should be possible to backup the active part of the TL even when both the mdf and ndf fail.

As far as I understand it now, there is really no real reason to have two separate files, other than the Navision team thought it would make a difference back in the day. Remember that the first SQL Server option was on SQL Server 6.5, and these things have not changed since. But as I said before, it's there, just leave it as it is.

You can configure the data files however you want, but it also makes it more complicated. If you feel comfortable doing that type of maintenance, go ahead.

bbrown

DenSter wrote:

It should be possible to backup the active part of the TL even when both the mdf and ndf fail.

The Backup command (and many other admin functions) require access to the catalog tables to deermine where the physical database files reside. Along with other details. You can't login without access to the catalog.

goldeneye

bbrown wrote:

Placing the *.mdf and *.ndf files on different arrays can also provide an additional level of recovery. The *.mdf file contains the catalog tables. If the array containing the *.ndf file fails, and the *.mdf is still accessible, you can backup the active portion of the transaction log. This would allow recover up to the last committed transaction prior to failure. Else the recover is to the last backup.

I am the/a database administrator for the navision databases on our development server. I too questioned the need for the tiny .mdf file and the bulky .ndf. So I decided to always use 1 single data file from a certain point on.

Now we also have a pretty skilled SQL expert around who's generally extremely busy (as most skilled ppl are). So it took him a while to notice my move to 1 data file.

He then explained to me that, while the data is indeed mostly contained in the .ndf file, the tiny .mdf still contains a lot of system tables. And if you run a trace on those tables you'll see they are accessed a LOT.

So performance wise it's a good idea to have them seperate because then SQL-Server can access those with seperate file handlers. Even better is to have them on a seperate, small and fast drive.

So now I use both files again. And so should you

colin_leversuch-roberts

actually any table which is accessed frequently will be in cache so the disk stuff doesn't apply in that sense. You can certainly gain performance by splitting the database across multiple filegroups if the filegroups exist on seperate physical drive arrays. however given a multiple spindle array for one datafile only vs single disks( or small arrays ) per datafile with multiple data files will degrade performance due to the lack of io throughput. If you're just using filegroups to partition objects, e.g. procs and views in one and data in another - but i can't see much point. However as I said in sql2005 if you want to make on-line recovery then you need only system objects in the primary filegroup and user data in user filegroups. For better performance you need multiple files per filegroup.

David_Singleton

Looks like another PHPbb Bot...

8><

---

fixed...

Administrator

I have de-activated the "user" zlatan24, and removed the postings.