Dear Tech Giants,
I have around 30 companies to setup for a group, where the companies are
grouped based on the business nature. i.e. the companies can be grouped based
on the nature of its trade (eg. 3 groups) where the items are shared, but the
price policies are different and even the base currency is different based on
its location. Intercompany transactions are very few and which would be
entered manually through general journals.
As the companies serve for general trading, supply chain, and warehousing
the database size is expected to be 100+ GB for each companies and the total
number of users will be 100+. The entire database will be hosted in a high
performance server (Nav 5 + SQL Server 2005).
What could be the best practice for setting up the companies in the above
scenario without affecting the performance of Navision? 30 databases and 30
companies or 3 databases (1 database for 1 business nature) and 10 companies
each (10 companies belong to each business nature)?
I wish to have an expert opinion from your solid experience in this matter.
Many thanks in advance.
0
Comments
3 Terra bytes of Navision data on one server needs a huge server, re you aware just how powerful this server is and how much it will cost?
You are going to need to put together a team of people with significant experience in large systems to make this work. Of course you could just build 30 servers to handle 100Gig each, but that But even at a cost of say 30 servers at $75,000 per server, that budget of $2,250,000 could be much better spent building one huge server.
As to answering you specific questions, that will need a lot of analysis before decisions like that can be made. Be sure not to commit to any infrastructure and database setup until you have done significant testing and profiling to be certain of what needs to be done.
Anyway do the client have the budget to do this? If so it will be a fun project that's for sure.
Server management team is good too. Here the issue is how to plan the database.. whether make 3 database with 10 companies each or 30 different databases. Which you could suggest for high performance and better management ?
As many companies share the Item information, we do have to replicate the Item information to each companies or databases where all common items got shared. Thats the one point where we have to be carefu, but we could manage it.
Please give your valuable tips.
Thanks,
3T Navision and 32 gig ram. Good luck with that.
Do you really have experience with managing 3 terra of Navision data on one server?
Which means the configuration is not sufficient ?
SQL Server bases solution <> Navision solution!!!
With a SQL Server bases solution, everything is in stored procedures and set based (should be!).
NAV runs the code on the client, so all data has to go from the server to the client. And in general, NAV makes SQL create a cursor for reading records.
What counts in a DB-server is not the total size of the disks (Ok,it is important but more as an afterthought).
First and for all count the number of spindles and how they are configured. Most NAV installations with enough spindles for performance generally have a lot of free diskspace on it.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
But for better performance, what could you suggest.. Multiple Companies in the Database or multiple database with 1 company each. Please advice
But I'm sure the sql admins have considered this :-#
I believe may of the implementation might have faced such issues or we may face in the future.
Its still not clear about the performance grading of Multiple companies in single database comparing with 1 company 1 database model.
Regarding customization the advantages and inconvenients were:
With 3 databases, developing and testing a customization affecting just one company was easier than now.
On the other hand developing a customization needed on the 3 databases ment *almost* 3 different developments. Testing was more difficult. Trying to keep all databases updated was difficult.
With 1 database one change in code affects all company. So you only have to do it once.
It is easier for users to swith between companies.
Developing a customization affecting just 1 company becomes more difficult, because you have to make your code work AND you have to make your code not to bother other companies processes, which is not easy.
So you have to consider how many equal/different customization each company needs.
Salut!
Author of the book Implementing Dynamics NAV 2013
Cursos Dynamics NAV (spanish) : http://clipdynamics.com/ - A new lesson released every day.
If you companies have different objects (30 companies in different countries), it is best to do 1 company per database. This has also another advantage: when you upgrade, you can do it company per company instead of all-or-nothing.
Now I come to think of it:
There is 1 (big) problem with having all companies in 1 database : 1300 tables * 30 companies = 39000 tables in 1 company. That can be a problem. I know Mark Brummel has had the problem once and solved it by putting unused tables DataPerCompany=No.
If all your companies have the same code-base, you might create multiple databases and in each database some companies divided in such a way that each of the databases is less or more the same size.
You can still put all databases together in 1 big (big=lots of disks!) RAID10 (at least 8 or even 16 disks) and each transactionlog on a dedicated RAID1 (or maybe RAID10).
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
Is there any difference in licensing talking about one big or many small database? As far as I remember, on classic db the license was dedicated to one navision server = 1 database.
Thomas
In this case, I think that per server is better.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
In one scenario Multiple databases may be faster, in another one big database may be better. You need to resolve all those issues first.
This is like planning a road trip from LA to Boston, and you are trying to resolve whether to take the George Washington or the Tappen Zee Bridge. Yet you haven't even decided if you plan to travel trough Detroit, Indianapolis or Dallas.
http://mibuso.com/blogs/davidmachanick/