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