1

Background

Hi, as a BI developer I create various scripts for a middle-size company (200-300 employees). These scripts are used to manipulate / aggregate / clean data from different sources and usually the result of these operations is stored into different tables (to perform extractions, analysis, whatever).

Problem

My boss is a great guy but he likes to create a new database and SQL Server Agent job (on the same server) each time a new business case pops in. For instance we're managing 10+ databases but a lot of them contain only a few tables. Same issue with the SQL Server Agent jobs, most of them perform only one step / action.

Personally I prefer a clean environment and to group tables / jobs together as much as possible (i.e.: storing all our derived tables into one DB rather 2-3 different DBs).

However before I engage any action / audit I would like to hear about your feedback. I mean, from your point of view, what's the best practice? Is it ok to have 10+ DBs / 50+ jobs when we could divide these numbers by 2 or 3?

Thanks,

Edit

I notice the question has been put on hold. Sorry for the inconvenience, I'll try to rephrase my question: I run a SQL Server containing 10+ databases (same company). Some DBs, for no particular reason (legal or whatever), contains only a couple of tables. I feel like I could consolidate these databases. Is it the right way to proceed? Or it's ok to create a new DB whenever you want?

PS: maybe the answer to this question is truly "opinion based". I just can't figure out what is the best way to proceed. In a way that's an answer too.

closed as primarily opinion-based by SqlWorldWide, McNets, Tom V - Team Monica, mustaccio, Erik Darling Jan 10 '18 at 13:09

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

1

Best practice is what works best for your organization.

If you're going to discuss this with your boss, who has a certain way of doing things, then you need to plan out the discussion in terms of a business case.

So, first: Rank the following twice each, in terms of What's important to you, and what's important to your boss. If these rankings are the same, you need to re-evaluate; that's pretty unlikely

  • Maintainability
    • via the GUI?
    • via handwritten SQL?
    • via automated SQL?
  • File count on disk
  • Cross-database queries
  • Cross-database permissions
    • Remember, EXECUTE on DatabaseA allows the object executed to access other objects in schemas with the same schema owner; however, going cross-database changes the rules
  • Quickness of the initial, first draft of a solution
  • Quickness of adding dozens of followon, similar solutions
  • Quickness of adding dozens of followon, identical solutions
  • Quickness of making changes to dozens of already existing similar solutions
  • Quickness of making changes to dozens of already existing identical solutions
  • Ease of investigating why solution A6 is not quite acting like solution A23, when they "should be" the same
  • Ease of restoring the entire set of databases somewhere else
  • Separation of data so customer A's data is not mixed with customer B (often a contractual requirement)
  • Ease of moving the entire set of Agent jobs somewhere else
  • Overall performance of ALL the solutions at once - profiler, extended events, indexing, monitoring
  • Foreign keys
    • which CANNOT go cross-database

Essentially, most of this boils down to: If you want one-off results as quickly as possible for each one-off unique thing, a separate database makes sense, but they should be destroyed when they are no longer required. However, long term, you're going to have hundreds of databases; is that desired? Or required, perhaps, due to contracts?

I would say that the arguments to consolidation are firmly around foreign keys (data integrity), identicalness of solutions (data integrity), and doing stuff by hand. The latter should be replaced with automated techniques which can just as easily handle 500 databases as 2, so I would ignore that.

Again, know what's important to your boss; if consolidating is a solution that is superior in terms of what's important to them, you've got a good potential for a successful argument. If not, you don't.

  • Thank you for you comment. "Best practice is what works best for your organization." That is very interesting and I guess I get your point about the "successful argument" part. – Marc Aurel Jan 11 '18 at 3:09

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