table partition & filegroup partition

vuacorona

Hello All:

I know Navision has one primary data file and one secondary data file. Is it possible if we can split to more secondary data file?

Another question: I have a large table containt few million of records, now if I partition this table to be multiple tables (horizontal partittion), then will Navision be able to access all these tables? How?

Sorry I am not a Navision geek, but just an experienced DBA.


vua.

Savatage

What exactly are you trying to achieve?

Do you want to split the database up to increase performance?

vuacorona

Yes.

Would you have any advice?

Savatage wrote:

What exactly are you trying to achieve?

Do you want to split the database up to increase performance?

Savatage

it's all in the details: are you using Sql or Native Database. server size & specs, # of users, etc etc.

Best thing is to search the forum for "performance" there are 100's of posts to read thru.

colin_leversuch-roberts

yes you can use multiple filegroups - we have our db on 6 file groups.
table partitioning - for sql 2005 - yes you should be able to do this - we're looking at this and I've implemented partitioned tables in other applications.
You would want to make sure you test very very carefully with partitioned tables, there's quite a number of requirements and you'd also need to get the indexing absolutely right to avoid problems.
Navision would not know the table was partitioned because all this happens "under the covers" - easier than partitioned views in sql 2000 - so implementation would be transparent.

vuacorona

colin leversuch-roberts wrote:

yes you can use multiple filegroups - we have our db on 6 file groups.
table partitioning - for sql 2005 - yes you should be able to do this - we're looking at this and I've implemented partitioned tables in other applications.
You would want to make sure you test very very carefully with partitioned tables, there's quite a number of requirements and you'd also need to get the indexing absolutely right to avoid problems.
Navision would not know the table was partitioned because all this happens "under the covers" - easier than partitioned views in sql 2000 - so implementation would be transparent.

Sound great colin,

Do you have some document or know anywhere for me to do some research?

thanks

colin_leversuch-roberts

References:-
Partitioned Tables and Indexes in SQL Server 2005
By Kimberly L. Tripp
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

I also have a document , unfinished, but i was trying to do much more with regard to the sliding window scenario. I'll have a think about files and filegroups and get back to you

vuacorona

colin leversuch-roberts wrote:

References:-
Partitioned Tables and Indexes in SQL Server 2005
By Kimberly L. Tripp
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

I also have a document , unfinished, but i was trying to do much more with regard to the sliding window scenario. I'll have a think about files and filegroups and get back to you

Thank you very much Colin.

Thanks everyone for reading the message.

fufikk

As far as I know the more hdd disks you have on separate controllers the more efficient your db can be... If you divide your db into more filegroups backup can be perfomed on particular filegoups...

vuacorona

fufikk wrote:

As far as I know the more hdd disks you have on separate controllers the more efficient your db can be... If you divide your db into more filegroups backup can be perfomed on particular filegoups...

Absolutely.....

But if we have some documents to study it would help.

.

fufikk

The only thing I know about dividing navi db is "Tuning Navision for better performance" - http://www.mibuso.com/dlinfo.asp?FileID=356

As for backing up file groups I'd recommend SQL Books on line. So far my client's dbs are not that large so I haven't really used it, except for my own "testing" purposes

vuacorona

fufikk wrote:

The only thing I know about dividing navi db is "Tuning Navision for better performance" - http://www.mibuso.com/dlinfo.asp?FileID=356

As for backing up file groups I'd recommend SQL Books on line. So far my client's dbs are not that large so I haven't really used it, except for my own "testing" purposes

Thank you for your input. Our SQL Server database is getting bigger and bigger... so far it reachs 100 GB, so I need to prepare for the worst case. I understand there are some limitation of Navision not like .NET, but we got to do what we got to do right?

In addition I am in the process of upgrading our SQL 2K to be SQL 2005, then there are few advantage in SQL 2005 as Colin said above.

fufikk

Hi,
I came across a interesting set of atricles at MS site, maybe you'll find it helpfull
http://technet.microsoft.com/en-us/sqlserver/bb331801.aspx

and mabye a few articles from databasejournal.com - see section about sql high availability
http://www.databasejournal.com/article.php/1503191

vuacorona

fufikk wrote:

Hi,
I came across a interesting set of atricles at MS site, maybe you'll find it helpfull
http://technet.microsoft.com/en-us/sqlserver/bb331801.aspx

and mabye a few articles from databasejournal.com - see section about sql high availability
http://www.databasejournal.com/article.php/1503191

Thanks fufikk, we never learn enough right... I actually subscribed to technet and keep up to date with SQL technology. Unfortunally, my knowledge in Navision is very limited.

I already have cluster setup in our database. Once we upgrade to SQL 2005 I will addon database mirror.


Thanks again everyone.

Scott_Frappier

vuacorona:

1.) You can definitely add/change filegroups in Dynamics NAV. You cannot do it from the application itself, and you need to be careful if you are modifying the schema from within NAV, but I have implemented this functionality at customer sites without issues. You will need to use a "move script" that will move the indexes and data to the filegroup of your choice, but it can be done.

2.) I played around with table partitioning in Dynamics NAV 4.0 SP3 on an Item Ledger Entry table...and after modifying the indexes and making it so that it set the "Entry No." as the partition separator, it worked wonderfully. I did not dig in very deeply, but I posted transactions and they worked without issues. I even did it with "Posting Date" but it required the keys to be updated, which I was a bit concerned with because the application sometimes needs to use an explicit key with reports.

I used the document that Kim used as my basis for the partitioning...it seemed to work without issues, but I would definitely test it in a development environment before releasing...

Feel free to email me at sfrappier@symtech.us if you have more questions. Also note - 4.0 SP3 Update 06 has some nice performance improvements with Dynamics NAV that will be out later this month (hopefully?). It now utilizes the TOP operator for queries, which can expedite queries (it also removes OPTION FAST on queries that are not forms if I recall correctly).

- Scott

vuacorona

Scott Frappier wrote:

vuacorona:

1.) You can definitely add/change filegroups in Dynamics NAV. You cannot do it from the application itself, and you need to be careful if you are modifying the schema from within NAV, but I have implemented this functionality at customer sites without issues. You will need to use a "move script" that will move the indexes and data to the filegroup of your choice, but it can be done.

2.) I played around with table partitioning in Dynamics NAV 4.0 SP3 on an Item Ledger Entry table...and after modifying the indexes and making it so that it set the "Entry No." as the partition separator, it worked wonderfully. I did not dig in very deeply, but I posted transactions and they worked without issues. I even did it with "Posting Date" but it required the keys to be updated, which I was a bit concerned with because the application sometimes needs to use an explicit key with reports.

I used the document that Kim used as my basis for the partitioning...it seemed to work without issues, but I would definitely test it in a development environment before releasing...

Feel free to email me at sfrappier@symtech.us if you have more questions. Also note - 4.0 SP3 Update 06 has some nice performance improvements with Dynamics NAV that will be out later this month (hopefully?). It now utilizes the TOP operator for queries, which can expedite queries (it also removes OPTION FAST on queries that are not forms if I recall correctly).

- Scott

Scott:

Thank you very much for your kindness, and especially posting your personal email here to help out other. That is wonderful, and I will follow your guide as well as keep your email into my "Navision Geek" black list...


thanks again.

kriki

[Topic moved from Navision forum to SQL Performance forum]

colin_leversuch-roberts

the use of files and filegroups is mainly for performance or to allow for speed in backups and on-line recovery. ( most with Ent sql 2005 only )
I would mention that on-line filegroup recovery requires that you do not use the PRIMARY partition for any user objects of data, I have no idea if Navision can support this - it's difficult for most developers to follow!
The decision to make use of all these technologies is the based upon your application throughput and to a certain extent the database size. I'm not 100% certain on how much benefit filegroups will bring on a 100gb database, typically i might suggest a minimum of a 12 disk raid 10 per filegroup, would it actually go quicker on a 24 disk raid 10 ? You'll only know by testing. On a multi proc box, files per cpu per mdf/ndf will certainly aid write threads, but serialising transactions will still effectively place the database into single user mode - in this case partitioning may help - but then the whole environment becomes more complex to maintain, understand and support.
I did some work on partitioning, my table resided in 8 filegroups each of which consisted of 8 files, so one table spanned 64 physical files, that makes for complex support - it did work well, BUT I was considering a table which could grow up to 25gb ( or more ) per day.
There's no fit all answer I'm afraid - and finally putting lots of memory on your server negates physical storage anyway, so if in doubt increase memory.

vuacorona

colin leversuch-roberts wrote:

the use of files and filegroups is mainly for performance or to allow for speed in backups and on-line recovery. ( most with Ent sql 2005 only )
I would mention that on-line filegroup recovery requires that you do not use the PRIMARY partition for any user objects of data, I have no idea if Navision can support this - it's difficult for most developers to follow!
The decision to make use of all these technologies is the based upon your application throughput and to a certain extent the database size. I'm not 100% certain on how much benefit filegroups will bring on a 100gb database, typically i might suggest a minimum of a 12 disk raid 10 per filegroup, would it actually go quicker on a 24 disk raid 10 ? You'll only know by testing. On a multi proc box, files per cpu per mdf/ndf will certainly aid write threads, but serialising transactions will still effectively place the database into single user mode - in this case partitioning may help - but then the whole environment becomes more complex to maintain, understand and support.
I did some work on partitioning, my table resided in 8 filegroups each of which consisted of 8 files, so one table spanned 64 physical files, that makes for complex support - it did work well, BUT I was considering a table which could grow up to 25gb ( or more ) per day.
There's no fit all answer I'm afraid - and finally putting lots of memory on your server negates physical storage anyway, so if in doubt increase memory.

Hi Colin and all,

Our backend hardware is SAN with two cluster servers each containt 16GB of RAM, and we are in the process of upgrading SQL2000 to SQL2005, then we will take the advantage of database mirroring.

Currently everything is running all right, but as a proactive DBA, I am prepare for the worst case will come.

Yes we have tables takes more than 10GB of data, and I am planning to break it down.

Under SQL point of view, partition table, partition file group is not a problem for me. But I don't know if those Navision developers can be able to adapt this technology since I have very little of knowledge in Navision.