1

I need to consolidate several older instances to SQL Server 2016 Enterprise on a single VM. Since all of the databases are third party vendor systems, they are a sprawling mess of SQL Agent jobs, custom roles, dynamically created users, and demands for doing installs/upgrades with SA access that I have to fight frequently.

I yearn for the isolation of multiple instances, but hear that they will create resource contention and other woes. In a VM environment where resources such as I/O would be shared across all VMs anyways and being able to set max memory per instance, should I still be avoiding multiple instances?

Breaking out to separate VMs is not an option for me due to licensing costs. Also, the scale I'm looking at is about 15 different vendor platforms, some of which I know could happily sit in the default instance together without the above-mentioned issues, so I would probably have a maximum of 5 instances if I went that route.

|improve this question
  • 1
    Possible duplicate of A few huge instances or many small ones. – Solomon Rutzky Aug 16 '16 at 18:17
  • 1
    Even though from a licence point of view you can install as many instances as you want on the same VM at no additional monetary cost, there are other resource costs to consider. Do these vendor apps allow for a named instance, or do they expect to be on the default instance? How much memory do you have available for each instance? Does the VM have enough network bandwidth to service all these applications simultaneously? – datagod Aug 16 '16 at 18:20
1

Perhaps others can add more info but I'd say first ensure that sharing a server is fully supported by the vendors you want to clump together, and get that in writing.

Then consider how important it is for these apps to maintain different instance level settings such as memory/processor/MAXDOP/Parallel threshold/Supported DB Version/auto param/strict Security settings (such as no shared users and disabled SA account where as the other DB needs full SA to everything).

From there you should be off to a good point in picking which instances belong to which DBs. Do note you are now sharing all the physical resources and their metadata across all the servers now on 1, so you might possibly notice more logical/physical file contention among other things.

|improve this answer
2

Obviously an opinion here, but... Personally, I think separate instances is definitely the way to go. We have several servers at my work like that. You have so much more control of stuff, especially server related objects like linked servers, etc. Being able to restart one instance and leave the others alone is a valuable benefit. And you can upgrade each instance independently as the vendor allows

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.