kevaburg (3/20/2013)
You would love my environment then! I have 30 instances on a single box!
Love might not be the correct sentiment 😀
For the two instance argument it means I can protect the databases that run Integrated Security in an isolated environment. If by some horrible freak of circumstance an SQL Server login (sa for example) becomes compromised I can at least help to minimise the damage.
It will depend on how diligent you are in terms of managing your SQL Server service accounts and the permissions they have on the server but there is a good chance that if someone get's sa in any of your mixed-mode instances that you're going to be cooked, i.e. they can likely take over the server and all instances on it. There [should] be a way (never had to deal with it but I will venture a guess it is possible) to configure each instance so a server takeover would not be possible if someone got sysadmin-level privileges in an instance, but I'd take the challenge to see if I could break into the other instances on the server if I were handed sa in one of them 😉
The amount of transactional databases we have I decided warrant the additional instances. The reason for it is that previously sysadmins paid absolutely no attention to memory management when the databases were installed. That meant (and this happened on two occasions) a runaway query brought the entire instance down (about 130 databases). My train of thought is that although from my side it is far more work and attention to detail, I can reduce the amount of damage that a single developer (that incidentally still insist on developing on production databases) can do to the entire instance. The problem is we have quite a few 24/7 databases and also time sensitive data that needs to be protected by almost any means possible. The only way I can do this is by ensuring the amount of memory available to each of these databases is as guaranteed as I can possibly make it!
Just my two cents, but I would probably spend my time getting really good at tricking out Resource Governor than with juggling more and more instances.
Licensing is a moot point: When you install a SQL Server, you are entitiled to install as many instances as you want without incurring additional costs! SQL Server supports up to 50 instances on a single box so off we trot then....!
You are focusing only on SQL Server...what if you are faced with the prosepct of using third-party software that is licensed per instance?
Although you are right about patch updates and so on, another problem I have is patch level support for certain applications, differering collations and authentication methods. It is a brain tickler of an environment for sure!
It sounds like you have a lot of vendor-based apps to deal with and I concede that sometimes isolating those to their own instance is going to be necessary, but it should be the exception instead of the rule. As you may know, the general recommendation is to consolidate instances to remove barriers to full utilization because it allows SQL Server to achieve a more complete use of the available server resources.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato