One database or multiple?

I am designing a database model based on an existing database that currently has 268 tables. These tables are all in 1 database.

The new model will have probably 300+ tables. We are discussing whether to keep all the tables in 1 database or separate them into multiple databases. We lose some foreign key constraints if we go multiple but that is a lot of tables to put in 1 DB.

So -- does anyone have experience with this or opinions? I can see pros and cons on both sides.

Thanks, Brett

more ▼

asked Dec 11, 2009 at 12:36 PM in Default

Brett gravatar image

27 2 2 2

Thanks everyone for the answers. I am definetly leaning to single DB but others are leaning in favor of multiple. Hmmm -- what to do? Thanks again.
Dec 11, 2009 at 01:16 PM Brett
(comments are locked)
10|1200 characters needed characters left

4 answers: sort newest

I also agree with Tom and wants to give some more things to consider.

If you have a lot of large tables with a lot of indexes it can be time consuming to rebuild all indexes with a "maintenance plan" and recommend you to create a script to rebuild only indexes that are fragmented.

If you have a lot of objects in the same schema it can be tricky to handle permissions to all the objects, but in SQL server 2005 and later you can group objects togehter in different schemas based on their functional area like sales, products etc. In SQL versions prior to SQL 2005, the schema handled the ownership of the objects, but later versions are more like containers

Quoute from books online:

Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.

New Behavior
The separation of ownership from schemas has important implications:

Ownership of schemas and schema-scoped securables is transferable. For more information, see ALTER AUTHORIZATION (Transact-SQL).

Objects can be moved between schemas. For more information, see ALTER SCHEMA (Transact-SQL).

A single schema can contain objects owned by multiple database users.

Multiple database users can share a single default schema.

Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases. For more information, see GRANT Schema Permissions (Transact-SQL) and GRANT Object Permissions (Transact-SQL).

A schema can be owned by any database principal. This includes roles and application roles.

A database user can be dropped without dropping objects in a corresponding schema.

more ▼

answered Dec 11, 2009 at 02:58 PM

Håkan Winther gravatar image

Håkan Winther
15.9k 35 37 48

(comments are locked)
10|1200 characters needed characters left

I agree with Tom, if the functionality is for one application, put it all in one database. Conversely, if you have seperate applications, please, seperate them into different databases. You can rely on methods within SQL Server to seperate out security or storage within a single database such that you can be sure that users only have access to the appropriate schema or large, active tables are stored on different files or file groups than less active tables... All kinds of things you can do there without having to resort to seperating your databases.

Worrying about referential integrity is only the tip of the iceberg. What about ensuring that restores, should they occur, are precisely synchronized? Point in time recovery should take care of it, but are you configuring the system for that? If not, you might be in trouble. There are more issues, but you get the idea.

more ▼

answered Dec 11, 2009 at 01:00 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left

I believe the most important question here is whether or not the tables belong together. Are they related? My general approach toward all development efforts is to try to maintain highly cohesive, loosely coupled components. If tables are related using foreign keys, for instance, they are tightly coupled and therefore should probably stay together in the same database. If, however, 2 tables are for different purposes (perhaps transactions vs reporting), then separate databases might make more sense.

What are you trying to achieve with multiple databases? If the goal is performance via multiple files, then perhaps multiple filegroups and files for the same database would work better.

SQL Server does not enforce a hard limit on the number of tables per database. The limit is on the total number of objects per database. On my last project, we had 1 database with over 200 tables plus over 100 stored procedures and dozens of views and functions. We then had other databases as well for separate functions.

more ▼

answered Dec 11, 2009 at 12:54 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Beautiful answer. To reinforce, if splitting tables between databases would require you to lose a foreign key, then they probably belong in the same database.
Dec 11, 2009 at 02:34 PM TimothyAWiseman
I would second Tim's test. If you lose an FK, keep them together.
Dec 14, 2009 at 12:04 AM Steve Jones - Editor ♦♦
(comments are locked)
10|1200 characters needed characters left

Just adding to Tom's comments..

If you are to use multiple filegroups you will only get real benefits if the ndf's are one seperate disk arrays.

Im my current prod database we have 455 tables (it does host 3 different applications - bad i know) and I dont have any issues with performance.

20 of the high transaction table on a seperate filegroup so IO isnt affected.

more ▼

answered Dec 11, 2009 at 01:03 PM

sp_lock gravatar image

9.3k 26 28 31

Thanks. Excellent point about the separate disk arrays.
Dec 11, 2009 at 01:24 PM Tom Staab
Multiple filegroups could be used for backup/restore purposes as well.
Dec 11, 2009 at 01:53 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 11, 2009 at 12:36 PM

Seen: 5471 times

Last Updated: Dec 11, 2009 at 12:36 PM