9

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: < 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:

  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.

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

My thoughts: This hardly seems large in my opinion.

  1. 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.

  1. 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.

  1. 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).

  • 1
    Yes, this one has more detailed and enumerated reasons however. The question has large scope in general. There are probably dozens of valid, and not very valid, reasons to use multiple databases instead of one. I'm trying to ferret out exactly why. 99% of the information available online usually deals with multi-tenant or multi-client reasons when assessing this problem. The literature out there for when to split a database OTHER than multi-tenant concerns is very sparse. It appears it generally just becomes self-evident to DBAs after finding problems with the 1-db approach. – user45867 Sep 26 '17 at 20:39
  • 1
    @LowlyDBA - the OP asks about more than just the valid reasons for doing so - he has come up with several why it doesn't apply to his own system and wants confirmation (all valid AFAICS!). p.s. I do with people who vote to close would put in a reason! – Vérace Sep 26 '17 at 20:42
  • @Vérace We'll agree to disagree then - while I applaud the OP for figuring out why it isn't a good idea, the question is still the same in both IMO, "What ARE valid reasons for splitting a Data Warehouse into multiple databases?" despite there being additional confirmation sought on their theories. It seemed straightforward to me, hence the lack of additional comment. – LowlyDBA - John McCall Sep 26 '17 at 20:48
  • I didn't know it was LowlyDBA. I normally put "Voting to close..." so the person knows and knows why! – Vérace Sep 26 '17 at 21:24
  • The kind about the size of database is hilarious. We have number of tables that are at least twice the size of your whole database. 😁 It is more important to know what your hardware can support than the mere size of a database. – clifton_h Sep 26 '17 at 22:17
10

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!

  • I appreciate the help. Yeah ... I suspected some of these things were illogical, but ... well really we probably need a dba who is a bit more seasoned, but budgets are tight around here. Miraculously our data warehouse has been working great thus far though, despite the relative lack of db maturity (daily BI updates instead of hourly, substandard indices live etc) – user45867 Sep 26 '17 at 20:49
  • 1
    One more thing if you happen to know --- I suspect another reason that will be offered is that database BACKUPS/ RESTORES will be easier with 7 dbs than 1 (bear in mind again, 80% is being moved to 1 db still). Because the transaction log will be smaller for each DB, and restores of 1 satellite db will be easier than the whole thing. We've never had to do a restore in 2 years, but obviously I suppose it's important. Something tells me that isn't the most important criterion for splitting databases though. – user45867 Sep 26 '17 at 20:51
  • You mean a "restore" in 2 years? Or does your DW get completely cleared down every night? Is it growing? Again, I'm not a Microsoft SQL Server Expert, but I know that in many backup systems, you can individually specify tables that are to be or not to be backed up. You probably have lookup/reference tables that don't get updated very often (think list of states of the USA or similar) - you should prune your list of tables rather than hiving them off - unless there is a very good case to do so (tenancy, confidentiality...). – Vérace Sep 26 '17 at 20:56
  • In case of catastrophe I mean and a restore from backup is needed. Of course there are a few different ways of doing this, one of course uses the transaction log. I'll have to do more research, but if this were the case, you would think it would be based on transaction usage or storage space, not data domains but eh – user45867 Sep 26 '17 at 21:00
  • It's not unusual for a database not to require a backup and restore for long periods (even years!). However, that is not a reason not to do one every quarter (or at least every 6 months) just to ensure that your procedures for any catastrophic meltdown are working satisfactorily! I'm unclear what you mean by "I'll have to do more research, but if this were the case, you would think it would be based on transaction usage or storage space, not data domains but eh". – Vérace Sep 28 '17 at 18:24
2

While it sounds like a classic straw man tactic being taken by your colleague, could he or she mean the creation of formal data marts when saying "splitting up the data warehouse"?

The two main approaches to Data Warehousing are attributed to Ralph Kimball and Bill Inmon. Here are a couple of high-level overviews ([1], [2]) on the difference between these two common approaches if you've got a few minutes to burn.

What I believe may be applicable to your situation is that Bill Inmon's approach calls for the formal creation of Data Marts that the reporting tool(s) pull(s) data from. These Data Marts are designed for specific departments or business units to access exclusively, and I think this may be what you're colleague is trying to move towards. The identical nature of the copies is odd, but it may be easier to create a copy of the data warehouse in its current form and then only load a specific department's data into said copy going forward?

From what you've provided, it sounds like your current data warehouse is using Kimball's approach where the Data Marts are a logical subset of data within the dimensional data warehouse that your reporting tool accesses directly. These two design approaches have their pros and cons, and hopefully the crux of your colleague's issue is that he or she is just more comfortable with Inmon's approach.

Hopefully this is just a misunderstanding of terms and a in-depth discussion of these two different approaches with your colleague will lead to some clarifications about the hurdles he or she is trying to move past.

  • 1
    I doubt he intends to use database split to create data marts or else he misunderstands them. Our entire database is fed into a 3rd party BI software using OLAP cubes. This BI software essentially does a select/ data read on all our fact/ dimensions tables once per night at midnight. Splitting the database would achieve nothing in terms of load balancing. Users query the BI cubes, not the database. The database tables are read "once" each per night. Ditto goes for end-user permissions, they are already managed via the BI/ OLAP cubes, as is the whole front-end. The split isn't entirely by dept. – user45867 Sep 26 '17 at 21:06
  • 1
    Bummer, hoped there was a possibility of a simple misunderstanding. – John Eisbrener Sep 26 '17 at 21:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.