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?