In this age of cost-saving after cost-saving, one way you may be looking at saving money is by combining multiple instances into one in order to save on licensing/machine costs. Or possibly you have a new database and want to decide if it can reside on an existing instance or if you need a whole new installation. Here are a few things you should keep in mind.

Required SQL Version

This should be a fairly obvious non-starter. If one application is designed with a feature that started in 2016 and another application that only supports up to 2014 then you need two instances.

Cross Database Calls

This may not be quite as obvious but it is very important. If you have two (or more) databases with frequent cross-database calls then for performance reasons you will probably want those databases on the same instance. Cross-database calls aren’t a big deal, cross instance calls on the other hand are a killer.

SDLC Level

If at all humanly possible different SDLC (Systems Development Life Cycle, i.e. DEV, TEST, PROD etc) levels should not share an instance or even a physical machine or VM. Developers are working on new code or modifying old code that can cause unexpected spikes in resource usage or even downtime. A testers job is to break things. Do you really want either of those groups touching your production instance? The need for keeping production up and moving quickly would restrict their work and their work puts production at risk. Not a good combination.

If you want 3 or 4 levels and can only afford, say, 2 instances then group DEV and TEST together and depending on how you use your MODEL OFFICE environment (how big a deal is it if it goes down) it gets put with DEV/TEST or with PROD.

Another reason I hate combining SDLC levels is that you end up with multiple copies of the database with different names. Personally, I find that far harder to code for but sometimes you have to do what you have to do.

Database Name

Another obvious one. You can only have one database with a given name at a time. If you have two applications that HAVE to have a database with the name APPDB then you have a problem and need multiple instances.

Security

Security is of course always important and one of the biggest reasons to separate databases/applications into different instances is security. If you have an application, for instance, that says it has to have sysadmin (not that this ever happens right?) then it needs to be isolated to its own instance to minimize risk. If you have power users with high-level permissions associated with one application and another application with high risk/highly secure information then you probably want to keep them apart too.

Down Time, Patching and Upgrades

The more databases/applications you have on an instance the higher the impact when you have a downtime (for patching maybe?) or have to do an upgrade. Ever had a support person restart a production database instance at the request of a developer? Yea, I have. It was a big shared instance. It wasn’t pretty.

Support Team

If you work in a small company then you probably have one team of devs/dbas managing all of the applications. If you have a big company with dozens or even hundreds of development teams then this becomes more of an issue. That downtime I just mentioned? Imagine trying to coordinate it with 20-30 different teams, all of whom want to have a say in when the downtime happens. (FYI that’s dev teams for DEV/TEST instances, business groups for PROD)

Resource Contention

Space, memory, CPU etc. There is a reason I put this last. It’s probably the one I worry about least. I realize that may sound counter-intuitive but honestly, I find that the vast majority of instances are overpowered for what we ask of them. Adding a database to a shared instance that’s running at max 50% CPU etc probably isn’t that big a deal. Not to mention that adding more memory, CPUs, disk space etc to an existing machine is a lot cheaper than adding a new one.

That said, if you have a high importance/visibility app that does require most if not all of the resources of an instance then leave it alone. If your worried about this one then get some baselines, do some testing, etc.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication Tagged: best practices, microsoft sql server