Database in parts or not?

GeflipteKip · 2009-11-26

Hello all,

Which is the best solution?

We are using a 50 GB database on Microsoft Dynamics NAV 5.01.
Is it better to make one big database-file of 50 GB, or seperate this database in 16 parts of 3,125 GB?

We always used 16 database parts, but our Navision solution center told us it's better to make a new database in 1 piece.
Personallly, I think it's better to seperate the database in pieces.

Did anyone have an experience with this?

garak · 2009-11-26

It's better to split it in separate files on different drives (physical drives not partitions on one drive) with the same size. This improves performance

Read also for this the w1w1ism.pdf. It's on the product CD in Folder Doc. And maybe, your partner should it also read.

But the question for this is also: How looks your Hardware (Server). What for an RAID has this ?

David_Singleton · 2009-11-26

One big file.

defiant701 · 2009-11-26

Hi there,

this massivly depends on what kind of storage you are running your db and what kind of technology you are using.

E.G.
native with DAS without RAID --> split db to 5-10 GB parts per physical drive
native with DAS with RAID --> split db to 15-25 GB per RAID volume to increase performance and never use RAID 5 (RAID 01 recommended)
native with SAN: --> is not suitable/recommened for larger db's

SQL with DAS without RAID: --> is not suitable/recommened for larger db's
SQL with DAS with RAID: --> split db to 15-25 GB per RAID volume and one single log file also never use RAID 5 (RAID 01 recommended for db and RAID 0 for log)

SQL with SAN: create 1 small master data file (.mdf) <= 5 GB, one big .ndf and one log file and make sure to configure correctly the LUN(s) one should be enough when building correctly (no caching, no RAID 5 volumes etc.)

BR
defiant701

DenSter · 2009-11-26

You only split the files when you have multiple physical drive volumes. If everything is on one volume, one file is better.

Database in parts or not?

Comments

Categories