Hi,

We are designing a new version of our existing product on a new schema. Its an internal web application with possibly 100 concurrent users (max)This will run on a SQL Server 2008 database.

On of the discussion items recently is whether we should have a single database of split the database for performance reasons across 2 separate databases.

The database could grow anywhere from 50-100GB over 5 years.

We are Developers and not DBAs so it would be nice to get some general guidance.

[I know the answer is not simple as it depends on the schema, archiving policy, amount of data etc. ]

Option 1 Single Main Database [This is my preferred option].

The plan would be to have all the tables in a single database and possibly to use file groups and partitioning to separate the data if required across multiple disks. [Use schema if appropriate]. This should deal with the performance concerns One of the comments wrt this was that the a single server instance would still be processing this data so there would still be a processing bottle neck.

For reporting we could have a separate reporting DB but this is still being discussed.

Option 2 Split the database into 2 separate databases

DB1 - Customers, Accounts, Customer resources etc

DB2 - This would contain the bulk of the data [i.e. Vehicle tracking data, financial transaction tables etc].

These tables would typically contain a lot of data. [It could reside on a separate server if required]

This plan would involve keeping the main data in a smaller database [DB1] and retaining the [mainly] read only transaction type data in a separate DB [DB2]. The UI would mainly read from DB1 and thus be more responsive. [I'm aware that this option makes it harder for Referential Integrity to be enforced.]

Points for consideration As we are at the design stage we can at least make proper use of indexes to deal performance issues so thats why option 1 to me is attractive and its more of a standard approach. For both options we are considering implementing an archiving database.

Apologies for the long Question. In summary the question is 1 DB or 2?

Thanks in advance,

Liam