SQL Server – When is a new database appropriate?

I frequently get requests from our developers to create new databases.  This is no biggie, and using the Standardising new database creation using Powershell and SMO scripts takes no time at all to implement.  However, something that usually isn’t considered at the point of a request is: why do we need a new database?

Let’s think about that for a few moments.  A new database comes with a whole bunch of new overheads; it’s an additional set of files to maintain, an additional log file to write to (which if you’re still working with spindles is going to mess up your sequential write IO), an additional database to backup, an additional database to run CHECKDB against, and additional database to add to your Availability Group, an additional database to do index maintenance against, additional security to maintain…. the list seems endless.  Add to that: cross database security can be a pain, referential integrity can’t cross databases, and you’re opening yourself up to being unsupported if you do cross database transactions and you’re using Availability Groups then it’s starting to look like a bad choice.  Cross database functionality is also a pain when it comes to source control and CI style release programs, I’ve yet to see a tool that does this well, and definitely seen it be a complete blocker to adopting SSDT projects or Redgate’s SQL Source Control from being implemented.

Furthermore, remember RPO and RTO.  Brent Ozar has a nice little article discussing this.  Each database backup on that server takes time and resources, and gives you a bit less of a window in your RPO and RTO.  Furthermore, I’ve heard anecdotal evidence of extreme slow start up times for a server with thousands of databases; crash recovery requires the server to read through each database’s log and roll forwards/back committed/uncommitted transactions.  For a couple of dbs, this isn’t so bad.  For thousands, that’s going to be a problem.

Given that databases can have separate schemas and filegroups for logical domain separation, and the ability to do piecemeal restores if required, then the argument of separating out related data for “prettiness” is moot.  However, if the data is truly unrelated, say in third party applications, then a separate database is the correct choice.  But please don’t go creating a new database for every new project that comes along, whilst needing to plug into your already existing Customer database, or whatever.

A further reason why you might consider a new database is if the workload is substantially different, e.g. staging databases.  That way, you can make use of features such as minimal logging by having a separate database in simple or bulk logged recovery model, and then process the data later.

In summary, as with most things, the real answer is: it depends.  Making sure you consider why you’re creating a new database is a positive first step in making that decision.