SQL When to create a new database?

Question

I have three different applications, they all share the ASP.NET membership aspect of the database and almost definitely they won't share anything else.

Should I have a separate database for each of the applications, or would one suffice?

All the application tables are prefixed, so that wouldn't be a problem in integration. Although I was wondering if there would be any performance issues, or if having all three applications share the same database would be some kind of grave mistake.

The applications in question are three web applications, the "main site", a forum and a bug tracker. I'm wondering if this is viable because integration could be easier if I had a single database. For instance, the bug tracker registers asp.net membership tables in it's db connection, and it even creates an "admin" user, where the db that is actually supposed to be holding the membership tables would be the "main site" one.

Update: I added a bounty to this question since the answers seem to have pretty split opinions about whether I should or not use multiple databases for different applications that share only membership providers.

Answer 1

Separate apps = separate databases - unless you have to "squeeze" everything into a single DB (e.g. on a shared web hoster).

• Separate databases can be backed up (and restored!) separately.
• Separate databases can be distributed onto other servers when needed.
• Separate databases can be tweaked individually.

Answer 2

I have always found it would be better to have more databases so that it is easier to:

1. Migrate to more servers if needed
2. Manage security / access easier
3. Easier (and Faster) restores and backups

I would actually go with four databases. A Membership database, and then one for each application (if the membership is truly shared). This will allow you to lock security across applications as well.

Looking at your question closer... You say that the data would "likely not be shared"... will a lot of your queries be joining tables with the membership? If so, might be easier if they are in the same database. However if you are going with a more entity based approach, I would think you would still be better with multiple databases. You might even want to look at something like an LDAP database or some other type of caching for your membership database to speed things up.

Answer 3

You should use the same database unless you have a current need to place them in separate databases - HOWEVER where possible you should architect your system so that you could move the data into a separate database should the need arise.

In practise this means that you should keep SQL procedures working the smallest amount of data possible - i.e. Don't have multi-step stored procs which do lots of separate actions. Have separate usps and call each from code.

Reasons to use separate databases:

1) Unrelated data - Group data that is interrelated - andonce databases get beyond a certain complexity, look to separate out blocks of related data into separate databases in order to simplify.

2) Data that is of either higher importance (e.g. Personal Details) should be separated to allow for greater security measures: e.g. screening this data from developers

3) or lower importance (e.g. Logging Info) - this probably does not need backing up - and if it's particularly volumous, you probably don't want it increasing the time taken to back up the main site database.

4) Used by applications living on different servers at different locations. Quite obviously you want to site data as close as possible to the consuming application.

Without really knowing the size and scale of your system, difficult to give full opinion, if it's just your own site, one db may work for now - if it's commercial then i'd have 4 dbs from the word go: Membership details, Forum, Bug Tracker and MainSite related stuff.

Thus in code you would have a Membership manager which only talks to the Membership db, A BugManager, A ForumManager and anything else will only talk to the MainSite db. I can't think of any reason you'd need any of these databases talking to each other.