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.
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.
Continue reading at SQLStudies.com.
Continue reading on SQLStudies.com.