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
A: 280 GB B: 43 GB C: 28 GB D: 1 GB E,F,G: < 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).