The answer is the ubiquitous DBA answer – “It Depends“.
Both pros and cons exist to this configuration. I’ll outline them both for you.
Pros:
- If you are licensing based on the VM rather than the physical CPU cores, you can potentially minimize your licensing spend.
- You have fewer operating systems to manage and patch.
- You might need to isolate an application to its own instance for security purposes, but the resource demands are not great enough to necessitate another VM.
- You might need to mix different SQL Server versions to support different applications, but do not have available licensing for additional VMs.
- You can patch and restart individual SQL Server instances independent of the others as needed, which can reduce the downtime that is usually associated with one large consolidated instance with many applications connected to it.
- You have more VMs to manage and monitor, which means a greater level of involvement for daily operational activities.
Cons:
- You now have to manage resource consumption at both the VM and SQL Server instance levels. For example, if you have two instances on a given VM, one SQL Server instance could start to consume all of the available CPU on the VM, and can cause the other instance to take a background stance and cause performance problems as a result. To manage this in the long term, some sort of action must be taken, such as CPU affinity, resource governor, etc. This action goes along with the management of the VM-level resource consumption, such as vCPU count, host-level CPU contention, memory management and allocations, resource throttling and prioritization, etc.
- Smaller VMs are more flexible and are easier to migrate to another host or even upgrade.
- You might need a larger VM to properly power all of the instances, which could possibly negate the vCPU licensing savings from the consolidation exercise.
- You have more flexible outage windows. If Windows were to need to be patched, or even worse, fail – all instances are down unless you have an HA solution in place for each instance. Scheduling planned outages with multiple business groups, multiple applications, and multiple instances on the same OSE can be a nightmare, and sometimes not even possible.
- Security is improved because one support request might need OS-level access. If they have access to the OS but only one instance, they can still see the files of other instances and potentially cause problems.
- Depending on your workload, you might end up with resource contention inside the VM itself. It usually manifests in things such as disk queuing contention within Windows, and can exhibit signs of higher disk latency and slower SQL Server performance as a result.
Continue reading on DavidKlee.net.