1

We have a suite of applications utilizing SQL Server have the following characteristics:

  1. Users create/destroy DBs via a UI, so DBs appear/disappear without DBA assistance.
  2. 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)
  3. DBs are many, but generally small. For example ~70 DBs totaling 28 GB, ~130 DBs totaling 2 GB.
  4. 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?

Improve this question
4
  • I would say if you're going to go VM and there is a chance that application version and currency might not be in lock step with all other applications, make it three separate VMs. It generally isn't the size of the DBs that is concerning as much as the transactions/batches needed to handle it. Do some load testing/UAT to make sure, otherwise it sounds like a good plan (not knowing much else).
    – Sean Gallardy
    Jul 25, 2014 at 16:54
  • Thanks for the feedback. The reasons go deeper. (Don't they always?). I was trying to keep this sort of simple. I already have 2 instances running like this without issue (the ones I gave examples for). And, there's a desire to NOT add more VMs to this particular location, but the applications don't perform well with the DBs in our data center and the users remote.
    – SandraV
    Jul 25, 2014 at 17:30
  • Well its little hard to say but since you say you have enough hardware resource and databases are small with not much concurrent users you can go. Make sure you fix max server memory for all instance you would not allow a poor query on one instance to take all RAM. Before doing that make sure you leave enough RAM for OS to function correctly
    – Shanky
    Jul 25, 2014 at 20:04
  • Yup. I cap the memory on all my instances, whether they're alone on the server or not. And, in this case, the new instance is actually a different SQL version. So, since it’s not practical to consolidate into a single instance and my infrastructure is really not wanting to add VMs, the is the most inoffensive solution. I don't believe it’ll be an issue, given the usage of these DBs, but Il might look back in a couple of years and wonder what I was thinking.
    – SandraV
    Jul 25, 2014 at 22:04

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • My server/storage admin reminds me often of why they don't like to hand out 32 GB RAM, 8 CPU VMs in the data center. But, this is looking to be more an 8 GB, 4 CPU VM, not in the data center, which he's OK with on the management side at this particular site. More OK than he is with allocating another VM. I believe the load to be very light on the 2 existing instances. They’ve run together like this for a couple of years. The third is a wildcard, since it’s not currently in place (still using MS Access as a backend). If it behaves differently than I think, it could be a problem.
    – SandraV
    Jul 28, 2014 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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