Schema is a better approach or Separate database

Question

Post reply

Schema is a better approach or Separate database

SQL006

SSCertifiable

Points: 5071
More actions
December 29, 2010 at 6:30 am

#232813

Hi
In my company we had 3 separate database all are connected to the same application
1.DB for static data(contain millions of static data)
2.DB for application settings (contain only intial settings for appliation)
3.DB for Inventory (Contai transaction,booking etc.)
I just want to know whether it is a better aproach to Use one DB with schema.
which one will be better option in terms of management, scalability,reusable
Thank you
With regards
Dilip D

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

I would use separate databases that would give you more flexibility in terms of optimizing and scalability.

I'd consider keeping the static data and application settings in the same database though.

The probability of survival is inversely proportional to the angle of arrival.

fawwad SSC Eights! Points: 965 More actions · Answer 2

I believe using different schema in same db will give you benefit.

since, you can easily fetch and join tables and using schema you alredy put different data in different box.

you may have some advantages with separate databases with same application but i think single would be easier to manage...

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

If it's all for a single application, I'd go with schema's to seperate out storage, access, security. This way you can have referential integrity across the schema's as needed, something you absolutely cannot do with seperate databases. In general, I try to stick to one database per application unless there are some extreme extenuating circumstances to suggest otherwise.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL006 SSCertifiable Points: 5071 More actions · Answer 4

Thanks for the reply, i think i should go with seperate DB. I have one static DB which contain millions of data which don't changes fequently. In terms of DB backup there is no need to take the backup of static data..so disk space will be free.

So i think it is better to go with seperate DB.

fawwad SSC Eights! Points: 965 More actions · Answer 5

@dilip : if you are going for separate db just only to avoid lengty backup time.

so, you may try FileGroups, you can keep different data in different filegroups.

so, you can backup desired filegroup and leave fixed Bulk data portion in other filegroup...