I've avoided having multiple instances in the past based on two, possibly flawed!, assumptions:
- Resources allocated separately to each instant, rather than shared (Memory principally, also TEMPDB)
- Management of the whole system becomes split, rather than all-in-one-pot
I suppose I could partly solve the Management issue by having, for example, the MDF, LDF and Backups from both/all instances using the same folders. I'd have to avoid databases of the same name in each instance (but that would drive me insane anyway!)
[Master] etc. are still going to use separate folders (for MDF/LDF)
We now have a situation where I have a Vendor that has Admin rights on the (single shared) SQL box and we need to mount some databases that they should not be allowed to see (including not fiddling/viewing SQL Agent jobs associated with them etc., so just hiding the DBs would not be sufficient)
We also perceive that we might need to prevent the current Admin Users we have (as distinct from IT folk and Database DBAs - who are trusted with acess to all company confidential information) from viewing some forthcoming financial and HR databases
So I'm thinking of a second instance for "restricted databases" ... but really I have no idea what criteria you Big Iron folk use to decide which databases to put in which Instances, and what your view is on any physical etc. resources that you then have to apportion between the Instances.
I could also do with education, please, on any issues / CONs that arise - do you think that management of multiple instances is more hassle than a single instance for example??
I'm also assuming that you will, without doubt!, put me straight if my understanding of Instances (e.g. allocation of physical resources) is flawed