What are the main reasons to split a Data Warehouse into multiple databases? I'm working with a colleague who's proposed to split our 1 instance-database into about 7 databases (divided by data domain) for development and 7 identical databases for production. I get the test-production duality logic, but in what case or what advantages are there to splitting our 1, relatively straightforward database into 7 databases? Our data warehouse is only consumed/ used by ONE business intelligence application, period.

I am concerned about this direction so hopefully you can discuss the general reasons proposed for this split, and I can give you a run down of the current properties of the database.

1 database Data Warehouse: 352 GB total, 203 tables, 170 views

Proposed split:

A: 280 GB
B: 43 GB
C: 28 GB
D: 1 GB
E,F,G: &lt; 1 GB combined


As you can see, this is already a head-scratcher in terms of proposed benefits as the storage won't even be remotely evenly split, with 80% remaining on 1 database. Apparently partitioning our db by schema isn't possible (from a hardware perspective) because we do not have Enterprise level SQL Server.

Reasons given for split:


Current db is poorly optimized, little documentation, sub-optimal datatypes, sub-optimal indices.


My rookie thoughts: Aren't these problems irrelevant to splitting the database? They are simply problems that need to be solved on their own either way.


There are 372 objects in the current database which makes it slow.


My thoughts: This hardly seems large in my opinion.


One database is harder to document and draw schema diagrams for than 7 databases (we will have views that will span multiple database).


My thoughts: .... This seems completely ridiculous to me, but maybe I'm wrong. We've already organized our data warehouse by 13 'source system' schemas.


One database will lead to more database deadlocks.


-- Isn't this problem also completely irrelevant to having multiple databases? It's my understanding that deadlocks occur at the table level (actually usually even just the row level, but eh). Even then, all our data inserts happen at midnight, all our selects downstream to the BI happen at 2 am. Having two processes update the same table is irrelevant to multiple databases, is it not (deadlock would happen either way)? Also, I personally have seen no evidence of table deadlocks occurring during normal operations.


Database technical ownership/ ownership. 


It's only the two of us that work on the database. It's possible he wants to really segregate our 'fiefdoms'. Really, hasn't been an issue, but can't user permissions be determined at the schema level anyway?

What ARE valid reasons for splitting a Data Warehouse into multiple databases?

Would love to further my knowledge here about databases in general. Yes, I happen to be doing a lot of work on one with gaps in my knowledge, but well the job is what it is, what I've been thrust into. Stuff has been working great so far (knock on wood).
You are definitely on the right track! 320GB is not huge for a database, particularly a DW. 


  1) Current db is poorly optimized, little documentation, sub-optimal
  datatypes, sub-optimal indices.
  
  My rookie thoughts: Aren't these problems irrelevant to splitting the
  database? They are simply problems that need to be solved on their own
  either way.


This is bang on the money. Splitting one large(ish) poorly organised, optimised and documented database into 7 poorly organised, optimised and documented databases is a waste of time! You need to tackle the root of the problem!


  2) There are 372 objects in the current database which makes it slow.
  
  My thoughts: This hardly seems large in my opinion.


Again, you are correct! 372 is positively small in terms of number of objects - many large servers have 10's of thousands. From here


  The sum of the number of all objects in a database cannot exceed
  2,147,483,647.


Your 370 divided by ~ 2E9 ~= 1.7E-7 - so no worries on that score! :-)


  3) One database is harder to document and draw schema diagrams for than 7 databases (we will have views that will span multiple
  database).
  
  My thoughts: .... This seems completely ridiculous to me, but maybe
  I'm wrong. We've already organized our data warehouse by 13 'source
  system' schemas.


Again, you're correct. If there are 372 entities with inter-relationships between them, you'll need to document and diagram them. It's going to have an inherent degree of complexity. What you can do is try to split your overall system into subsystems and document them and then try and fit them into the bigger picture - great oaks from little acorns grow!


  4) One database will lead to more database deadlocks.
  
  -- Isn't this problem also completely irrelevant to having multiple databases? It's my understanding that deadlocks occur at the table
  level (actually usually even just the row level, but eh). Even then,
  all our data inserts happen at midnight, all our selects downstream to
  the BI happen at 2 am. Having two processes update the same table is
  irrelevant to multiple databases, is it not (deadlock would happen
  either way)? Also, I personally have seen no evidence of table
  deadlocks occurring during normal operations.


What you will lose in the multiple database scenario is ACID transactions within the same schema - OK, you can have 2-phase commit, but it's not as robust as transactions within the same schema (IMHO). I'm not sure of a valid reason to hive off tables if they're necessary for your requirements. 

You appear to be talking about writes blocking reads? Well, you also appear to have a batch process at midnight followed by a querying process at 02:00? If you can make transactions/tables read only, this will take some load off the server engine as it is processing your data. Only you can tell if this can be applied to your scenario!


  5) Database technical ownership/ ownership.
  
  It's only the two of us that work on the database. It's possible he
  wants to really segregate our 'fiefdoms'. Really, hasn't been an
  issue, but can't user permissions be determined at the schema level
  anyway?


Certainly, ownership is at the table level and access can, depending on your server/version, be granted on a column and/or a row level - so the business of ownership is a complete red herring! If you are a server DBA performing a reorganisation (as opposed to simply scheduling backups and other mundane tasks), then you will need "access all areas"! 

You should have a comment on every table and field in your system - you can put "ownership" (in the organisational as opposed to database sense of things) in there - commenting tables and fields is an excellent first step to documenting a system - it becomes self-documenting!


  What ARE valid reasons for splitting a Data Warehouse into multiple
  databases?


There can be many reasons - some are associated with multi-tenancy (both in terms of machine resources (CPU, RAM, HDD and Network) and client confidentiality or requirements. Have a look here and also google "database multi-tenancy" or similar.

Everyone says it, but it is a struggle - "documentation is very important"! As a first step, document your tables and fields in the comments. Produce ERD diagrams for all of your subsystems. Don't let anything new into the system without these steps being implemented. Best of luck in your new role!