We have a suite of applications utilizing SQL Server have the following characteristics:
- Users create/destroy DBs via a UI, so DBs appear/disappear without DBA assistance.
- DBs of the same name can be created for each of the applications (because they're related to the same subject matter and the apps are used by the same set of people)
- DBs are many, but generally small. For example ~70 DBs totaling 28 GB, ~130 DBs totaling 2 GB.
- Only a handful of DBs are in use at any one time.
I've been asked about configuring a VM with three instances to support these three applications. And, while DBAs generally run screaming when the question of multiple instances on a server comes up, I'm wondering whether this is a scenario where multiple instances would be, if not blessed, tolerated.
I do have large physical servers that I run multiple instances on. But, not VMs. So, I'm comfortable managing the multiple instances in this fashion although the idea of doing on a VM made me a bit queasy.
Thoughts?
Best Answer
I'd say it's all based on the resource consumption (cpu / ram / concurrent disk activity) of each instance, as well as the overall health of the virtualization environment. Virtualization is all about resource queues. If it looks like physical environment might hit a bottleneck based on the cumulative resource consumption of three instances in one VM, it's better to break that into three VMs. Smaller VMs are more agile and have less of an overall impact to the physical machine, which lends to better performance. Larger VMs give you less individual OSs to manage, but tend to be larger consumers of resources and therefore harder to maneuver in the virtual environment.
If the overall load looks reasonable, you might be ok with the three instances on a VM. If the overall load is high and growing, split them apart into three VMs to be safe now and for the future.