Putting seperate company data into seperate .ndf files

Cem_Karaer

Hello,
Is there any way for putting seperate company data into seperate .ndf files? (SQL Server secondary data files)

DenSter

Only by modifying that property manually (or with some script) from SSMS. Why would you want to do that?

Cem_Karaer

My main purpose is backing-up companies' data separately. For example if any error occurs in one company, I want to restore this company's data only. Also it is highly probable that the single .ndf will bloat very quickly to hundreds of gigabytes (within only 5 months of active work, it mounted up to 15 GB!). Spliting the fatty single file into meaningful pieces will also boost performance with proper harddisk usage, I think.

kine

That the performance will go up is not 100%... one company per disc can be slower than data distributed between discs...

DenSter

Do you have any maintenance on your database? Backups? Reindexing? Is the data file growing more quickly? Is performance going down?

You still haven't told us what the problem is. You will not be able to back up individual companies in a way that is supported by Microsoft Dynamics NAV. To them it is either oyu back up the entire database through the SQL Server tools, or you back up individual companies through the NAV client.

bbrown

That would be a great feature if the NAV client supported it. If you could specify the filegroup when adding a new database file (File - Database - Alter) and then specify the filegroup when creating a new company.

Since SQL supports filegroup backups it would let you backup the companies that are in a single filegroup. Filegroups can be placed on different drive set based on that company's needs. Also the filegroups for larger companies could be split to multiple files.

The limitation is that Navision (Object Designer) will create all tables in the default filegroup. Until that changes this is rather impractical to implement in NAV.

kine

And what you will do with the Tables which are not PerCompany during that backup/restore? Do not forget that they can make the database inconsistent if you restore just one company and in the shared table are some entries created from both companies...

Cem_Karaer

Thank you for all of your comments. There is no particular problem for now. I just only wanted to do something as a precausion. But your experience shows that putting several companies' data into seperate SQL data files is a sweet day dream

bbrown

kine wrote:

And what you will do with the Tables which are not PerCompany during that backup/restore? Do not forget that they can make the database inconsistent if you restore just one company and in the shared table are some entries created from both companies...

What data would be in a shared table that applied to only one company?

The shared (not PerCompany) tables are already in a their own filegroup. The additional file groups would contain only company specific data.