We currently have a single large database for use in reporting. This contains multiple tables, of which only some are related.

For example, we have 5 tables for one division, 2 tables from another division, etc. Data can flow between divisions, but there is not a structural link between the tables - no foreign key relationships, no duplication of tables. We also have some lookup tables that are used by procedures that populate a lot of the tables in each division - calendar, postcodes, that kind of thing.

Is this best as a single large database, or separated out into smaller databases with thematically linked tables? These tables will grow, and the number of them will increase as new divisions are set up. We will notice any degradation over time, or is this so small as to be insignificant?

Any advice or pointers on things to read are gratefully received.

  • 3
    Maybe you want different schemas rather than databases. dba.stackexchange.com/questions/4075/… – EzLo Mar 6 '19 at 13:29
  • Actually It does not make difference if you have same database or different if table is going to be same! – MarmiK Mar 6 '19 at 13:50
  • @MarmiK The tables are different, and there are very few similarities between them. I consider them to be disparate, and could easily have been in separate databases from the start. – BishNaboB Mar 6 '19 at 14:38

First, define what "large" is. For the rest of your career, you're always going to be working with what you think is big data, and the volumes will keep growing. However, what seems big to you may not seem all that big to others.

Second, ask about backup/restore. Odds are, if you need to do a restore, you're going to want all of the objects restored to the same point in time. The easiest way to do that is to keep 'em in a separate database.

Finally, what's the problem you're trying to solve? Whenever you're thinking about doing something, ask about the problem you're trying to solve. Here, it sounds like you're just randomly looking for ways to use a tool (in this case, breaking data into separate databases.) Focus on the problem at hand, not new solutions. By adding solutions to a problem you don't have, you're only injecting complexity that will come back to bite you later (like at restore time.)

  • 1
    Hi Brent, thank you for your response. "Large" in this context is ~210GB in storage, which I appreciate will be small to many others. It's also about 40 disparate tables, which again is probably not many others.The problem I'm trying to solve doesn't exist - you're right there. What I'm trying to work out is if I can head off a problem later by taking steps now. Does this help clarify? – BishNaboB Mar 6 '19 at 13:33
  • Focus on the backup/restore aspect. Do not split up the database unless it is OK to have different pieces of data from different points in time. And, make sure that you are 100% certain it is ok, if you decide to split to several databases. (Yeah, you can use marked transactions to sync a restore point, but you don't want to dig that hole for yourself.) – Tibor Karaszi Mar 6 '19 at 17:03
  • 1
    @BishNaboB right, 210GB isn't large, and I certainly wouldn't separate a database of that size. For reference, the term VLDB (very large database) refers to 1TB or 1 billion rows in a table. I wouldn't separate the tables into different databases. Focus on the problems you need to solve today first. – Brent Ozar Mar 6 '19 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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