I am urgently looking for a document that defines how to split Navision tables amongst different SQL database files.
Can anybody help?
Regards
I can probably help by saying that such a document probably does not exist.
I don’t knwo of a way to split Navision over multiple servers, though I guess technically everythign is possible.
But why are you trying to do this? and why is it urgent. In the ERP business, if a change as drastic as this is required, then Urgent passed by some months ago.
Depending on the version of NAV you should be able to do an ALTER TABLE in Sql. You can use normal SQL statements.
I would however advise to test it very well, I’ve never tried it, maybe someone else has. This is something I have heard at TechEd.
Also contact you Microsoft partner before you proceed.
I am not aware of any such document. Even though this is technically possible, the issue is how to determine which objects to place in which files. Yes, objects. it is possible to place a table’s data and primary index in one file and the secondary indexes in other files.
Without a complete understanding of how your Navision is being used, it is nearly impossible to determine the optimal configuration for splitting database objects. The most ecomomical solution would be to increase the number of physical drives in the RAID-10 array. Unless you work for free.
Well, “splitting” a table to distribute it on different files means actually “table partitioning”, which is a feature that is avaiable in SQL Server 2005.
So, technically it is possible to distribute a table over several db-files - How To: see “Books Online” “partition scheme” “partition function”
But please could you tell us WHY you want to do this? Usually, this kind of distribution is implemented on “supersize” tables - speaking of TBytes - have never seen a NAV db where this was really necessary …
Table partitioning was available in SQL 2000.
Table partitioning was available in SQL 2000.
Not really. In 2000 you could create multiple tables with same structure/fields but with a different name on different files (filegroups); like “Sales Line 1”, “Sales Line 2” and then create joined queries or views on that. Thus, a view could be named “Sales Line” but looking into the 2 different tables.
Or do I miss something?
I don’t consider this as partitioning.
In 2005 you define a column as “partitioning” criteria via “PartitionScheme” and “PartitionFunction”. E.g. in “Sales Line” you could define “Document Type” as partitioning column, then define one partitions for DocType 0 (Quotes), one for 1 (Orders) etc… Each partition could be stored on a different file/filegroup. I consider this as real partitioning.
Or do I miss something?
Your definition of Table Partitioning (which I agree with) was not available in SQL 2000. However I think the original question was asking about partioning the database by placing certain tables on different filegroups (not partitioning single tables). This was available in SQL 2000.
Reading the initial posting again, I have to admit that I probably misunderstood the issue. [:$]
If that’s the case - distributing different tables on different files -, then of course that’s no problem in SQL 2000 or 2005 …
So, again my question to MelvinS: What’s the purpose of all this?
The document Tuning Navision for Better Performance mentions the use of SQL filegroups. And yes, SQL 2000 and up does have this ability through the Enterprise Manager, very easy to do. MS suggests that filegroups be used when you are not doing any kind of RAID striping. See page 10 of the document for more information.
Yeah, but the usage of different filegreoups does ony improve disk I/O performance if the different files are stored on different disks/channels/controllers (whatsoever) - that’s some kind of “replacing” RAID. Another benefit of using different filegreoups is, that SQL Server processes each filegreoup with separate CPU Threads - independent from the physical storage! One thing we usually implement - I guess I’ve mentioned that here previously - is to create a dedicated filegreoup for indexes, hence transactions can be processed with multiple Threads - simplified: one for data, one for indexes.
So, having more filegreoups has impact on the CPU load, too. If you don’t have the CPU capacity, you should take care …