SQL Server...separate db vs a separate schema?

Hello - I am looking for some "best practice" tips on when i might want to have a separate DB vs just a separate Schema.

I am using SQL Server to build a Data Warehouse that will be updated once a month (it is not a transactional type db...).  There will be different types of data stored in the Server (detailed information and summary tables that will be built off of the detailed information). There will also be tables for many different topics. It is worth mentioning the same users will be accessing information on the server, regardless of whether the info is in a separate DB or not.

My initial thought is to try and set up the summary tables in a separate db on the server, but I am not sure how that would benefit me...other then getting some of the clutter out of the initial db. I could just as easily set them up in a separate schema...and that would help me keep them together and somewhat organized.

I know I can use different schemas as a way to organize a db. However, I am looking for some guidance on when I might want to consider using a separate DB instead.

I have read that having multiple db's could result in a performance hit (using more memory, etc.?.)..but I am not sure if that is true in SQl Server. I do not want to maintain separate backups, etc. if it will not benefit me in some way.

Does anyone have tips they would like to share?
Cynthia HillLead ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Cy,

There's hardly a concrete "always do it this way", but there are some pretty good guidelines.

I'm a firm believer that the OLTP and the OLAP belong on separate servers.  They are completely different animals, one is write intensive, the other read intensive, and they don't provide many opportunities for sharing objects.  (The indexes in the OLTP are useless to the OLTP, as is most of the table data.)

That said, if the OLTP and OLAP will both fit into the memory of a single server, from a performance perspective you can certainly put them both on the same server.  But that's not an absolute, either.  If you have 24/7 needs of the OLTP and fully rebuild the warehouse regularly you could see a performance hit.

Start by planning for them to be on separate servers.  Then look to see if you can justify putting them on the same server.
Scott PletcherSenior DBACommented:
I wouldn't normally ever put summary tables in a different db, and I wouldn't expect many other DBAs to do it either.  Or even in a different schema.  After all, at core it's the same data.  You could put summaries in a different filegroup.

You might also want to partition the tables so that you can easily archive old data and/or have different physical index options for newer data vs. older data (not as much compression, etc.).

There is some slight additional overhead for every db used, but that's not a major factor unless you're really tight on RAM.  

Btw, what's most important to performance is creating the best clustered index on every table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andrei FomitchevCommented:
For small DBs anything works.

Different DB or different schema (1 or 2 instances) on the same host does not make any difference in relation to CPU or memory utilization.

You should put online access and batch processing (or heavy query like BI) in different DBs on different hosts.

With monthly update - 2 DBs. 1st - daily full backup, 2nd - monthly full backup after update.

With different hosts you can ETL with SSIS or Linked Servers.
Cynthia HillLead ConsultantAuthor Commented:
Thanks all for the information!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.