We are developing a new version of our existing product on a new scheme. This is an internal web application with up to 100 concurrent users (max.). This will work in a SQL Server 2008 database.
Recently, questions have been discussed about whether we need one database to separate the database for performance reasons in two separate databases.
The database can grow anywhere from 50-100 GB for 5 years.
We are developers, not database administrators, so it would be nice to get a general guide.
[I know that the answer is not simple, as it depends on the scheme, archiving policy, amount of data, etc.]
Option 1 A single core database
[This is my preferred option].
The plan is to have all the tables in one database, and possibly use filegroups and partitioning to split the data if necessary on multiple disks. Use the circuit if necessary. This should concern performance problems. One of the comments was that an instance of one server will still process this data, so there will still be a neck of the processing bottle.
For reporting, we may have a separate reporting database, but this is still under discussion.
Option 2 Divide the database into two separate databases
DB1 - clients, accounts, client resources, etc.
DB2 - this will contain the bulk of the data [i.e. Vehicle tracking data, financial transaction tables, etc.].
. [ ]
[DB1] [] [DB2]. DB1 , , .
[ , .]
, , , , 1 .
.
. , : 1 DB 2?
,