When is it a good idea to consolidate multiple instances?


October 23, 2017 by Kenneth Fisher

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

One thought on “When is it a good idea to consolidate multiple instances?

  1. Colin Allen says:

    Entirely agree about business units. Getting several to agree on downtime is impossible, also had situation where no-one even has a contact for the application team.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: