Site With Multiple MSSQL DBs

coreybryant
coreybryant used Ask the Experts™
on
I have a few sites that use MSSQL db.  Is it better to create a MSSQL DB for each of their programs.

Would it be better to use one MSSQL DB for each script or to have one DB for them all?  Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John EastonDirector

Commented:
I guess it depends what you are doing.

If the site are totally seperate and have no intergration then I would usually use a seperate database for each.  This potentially has security advantages if one site gets hacked.

Also, if a site is going to have a huge amount of data you may prefer to split this into multiple databases.

However, if the sites do interact however, then one database may be easier.  I.e. one site which is for sales staff and another public site for customers but they share product lists etc.

If you provide more detail of what the site are you may be able to get better advice.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
You have another option that might be viable:

Put all the programs in one db, but put each program in its own schema.

In some cases, this is more advantageous than separate dbs.

Author

Commented:
Thanks all - I would definitely create one MSSQL DB for each site.  I just did not know if having multiple MSSQL DBs would cause the site to load slower.  

I am not worried about having scores of DBs (when it comes to the hosting part).   Basically, some websites have a MSSQL DB for the information that will be displayed and another DB for another script that is being ran on the pages.  Plus some might have an 'ad script' with its own DB.  

So opening one page could possibly need to get access to three databases.  

On one of the scripts, the tables start with an mt_ - so I doubt I would have issues with that.  I have not looked closely at the other names of the tables, but I would think that if the other two scripts have a table named user that might cause some problems.   Of course, I could change one script to begin with ad_ - although that seems like a lot of work.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
>> I just did not know if having multiple MSSQL DBs would cause the site to load slower. <<

Typically, yes, somewhat.  You have to connect to each db separately, and db metadata has to be loaded by SQL for every db you access, using slightly more memory.  But the RAM overhead for a few extra dbs is relatively very low.
John EastonDirector
Commented:
I agree with Scott on regard to the extra overhead.  Generally adding a second connection will have very little impact on your site.  If however your site starts getting a large volume of hits and you are needing to increase the speed, having one database could yield improvements.

On sites I have used multiple database connections for I have seen no noticeable difference.

Author

Commented:
Thanks - since I was getting ready to move the sites to another server, I wanted to make sure that I started things off right

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial