I just want to start out by saying that I have enjoyed our dialogue very much and that anything coming across as criticism is not meant to be taken as ill-willed. They are merely comments based on my understanding of best practices and my experience, of which admittedly I have very little in terms of managing more than only a few instances on a server at any one time. So, thanks for the dialogue and kudos to you for working out a system that can handle that many instances, and I presume customers, on one server.
kevaburg (3/21/2013)
The 'sa' account as you have mentioned becomes a risk if someone can get hold of the password and that is simply a risk that I want to mitigate as much as possible. The fact is is that if your instance uses Integrated Security then even if the 'sa' account is unlocked, it can't be used. One of the reasons I try and separate those applications that require SQL Server authentication and those that don't. If the 'sa' account on one mixed-mode instance is compromised then it is possible to compromise other instances with a couple of nasty tricks. The fact here is that if someone REALLY wants to hurt you, they are going to find a way regardless. The best we can do is try and mititgate the risks as far as possible. Additionally, our 'sa' accounts (regardless of the authentication mode) have differing and complex passwords.
Resource management aside and focusing only on security for this one: I am still struggling to see how splitting what could be one instance into two, where one is mixed-mode and one is Windows Auth, helps you maintain a higer level of security. This is highlighted even more in light of your confidence that no one will be able to break into either instance as 'sa', either because it is not possible in the case of the Windows Auth instance or because you have a strong password in the case of the mixed-mode instance.
As a side note, while I do end up running mixed-mode in the overwhelming majority of cases, I choose to disable the sa Login on all instances. Anyone requiring sysadmin-level permissions has their own personal (Windows where possible) Login added as a member of sysadmin. This takes one attack vector, namely brute-force password attack on the sa Login, off the table and increases auditability of sysadmin-level activity a bit.
Your point about the Resource Governor is a valid one and, much as I hate to admit it, a subject that I am far from a master of. To be honest though, although I have received a fair amount of critism (constructive though!) about how I manage memory on my servers, I am happy with the outcome. As I have mentioned several times, teh ability for me to be able to in effect, sandbox activity to within a single instance, has saved me on a couple of occasions. When I consider the amount of time spent setting up, configuring and monitoring this activity in the fist place, I regard it as a good investment. Not only was disruption confined to a single instance but work happening on other instances carried on regardless!
I too am no expert with RG. In my comments above I was leaning far more on my understanding of best practices than my limited amount of experience using RG.
Your point about instance-based software licensing. This isn't one that applies to us as we don't have software that falls into this catagory.
What are you using for monitoring? What about backups? Vendors for those two types of tools notoriously go for 'per instance'-licensing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato