Multiple Companies or Multiple Databases !! ??

eMPTY_Mind

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.

David_Singleton

What's the biggest Navision implementation you have done till now, and how many really big implementations have you done.

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.

eMPTY_Mind

Yes.. they are fully aware of the server expenses and the plan is 1 OLTP server with 3 TB and one OLAP server with 4 TB. each will have 32+ GB RAM.

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,

David_Singleton

eMPTY Mind wrote:

Yes.. they are fully aware of the server expenses and the plan is 1 OLTP server with 3 TB and one OLAP server with 4 TB. each will have 32+ GB RAM.

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?

eMPTY_Mind

I haven't work with 3T yet, but 1.5T. It wasn't Navision, but SQL Server based solution.

3T Navision and 32 gig ram. Good luck with that.

Which means the configuration is not sufficient ?

kriki

eMPTY Mind wrote:

I haven't work with 3T yet, but 1.5T. It wasn't Navision, but SQL Server based solution.

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.

eMPTY_Mind

Thank you very much for your valuable updates. We will add 2 more servers (Business Unit wise) to solve the issue.

But for better performance, what could you suggest.. Multiple Companies in the Database or multiple database with 1 company each. Please advice

Idleness

I cannot help you in terms of Navision performance but a major consideration of how you setup any large sql databases (in my opinion the first consideration) is backup, restoration, replication and continuity. Generally this is never given enough thought up front. If you need to roll back a database or three during testing or for any other reason then a single database per company would be far easier both to manage, troubleshoot and restore than 1 huge database.

But I'm sure the sql admins have considered this :-#

eMPTY_Mind

Thanks for the comment..
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.

kitik

I've never worked with such a big database, so I know nothing about performance. But I've worked with a smaller company with 3 different databases. One year ago we merged all the databases in one.

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!

kriki

I don't think it changes a lot. After all it is 1 server that handles all data.

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).

ta5

Just to think about, possible not relevant...
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

kriki

You can license NAV per server or per DB on SQL.
In this case, I think that per server is better.

David_Singleton

There are multiple issues that will affect performance of this system. This is one of the lesser in terms of significance. You have 50 other issues to resolve before this one. If you want this sytem to work then you need to look at the system as a whole and address all the issues.

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.

davmac1

You also need to consider number of simultaneous users, transactions per day, proportion of online transcations to batch, etc.