1

What is best practice for SQL Server 2014 regarding number of instances per node? I know SQL Server 2014 added CSVs that will do away with the drive letter limitation, but what is best practice on the number of instances per node? To my knowledge, SQL Server 2008 R2 best practice was 5 per node.

  • 3
    Best practice is 1 instance per node, regardless of version. Instance stacking is hard to manage. I would not ever have 5 instances on one node in a production environment. Maybe in a test environment. I wouldn't even o 2 instances on a node in production. – Tara Kizer Feb 14 '17 at 23:46
  • 1
    I'm totally with @TaraKizer on this one. There are people who would disagree with us (but those people are wrong). – AMtwo Feb 14 '17 at 23:48
3

You've asked a subjective question, but you'll find that many of us will say that the best practice is to have only one instance per node if it's a production cluster. If you have two instances, then many of us will say you need 3 nodes: one node for instance1, one node for instance2, one node is passive and is ready to be used if either of the other two nodes go down.

Brent Ozar recently blogged about this instance stacking topic. There are benefits and drawbacks to instance stacking. One such benefit is licensing and one such drawback is performance tuning.

Disclaimer: I work for Brent Ozar Unlimited. The referenced article is from our web site and is written by my boss.

SQL Server has supported mount points since SQL Server 2005, so drive letters didn't limit the number of instances even ~12 years ago unless you got really crazy with tons of instances in a cluster.

I have never heard that there's a 5 instance per node best practice for 2008 R2. I have had 4 instances on one node in a test environment, but the databases were tiny and very little utilization. I did not have to worry about performance tuning on that server at all.

Now if you were to change your question to ask what is the maximum number of instances you should have on a node, then you might get a different answer. But that answer is going to be dependent on the hardware of the server and workload of the instances. I still say 1 instance for each node though if it's a production cluster.

  • Thank you for answering. I agree that it is quite subjective and there are a lot of "it depends" answers. We are replacing 10 year old servers with Dell blades @2.9 Ghz, 24 core hyper-threaded to 48 and 256GB RAM. We would like to possibly go virtual and are looking into that instead of staying physical. I would also like to upgrade to 2014 from 2008r2 and possibly use CSVs for storage. We are also going to move some of these to all flash arrays. I inherited instance stacking on most of my servers, some having 8 to 12 instances per node, so I'm trying to make a plan to split them. – bwilliamson Feb 15 '17 at 14:06
  • @bwilliamson, I suppose if I currently had 8-12 instances per node, I'd be ecstatic to bring that number down to 5 or under. I can't imagine what that person was thinking to setup an environment like that. Is it because of duplicate database names? If there is no duplicate names, then why not just use one instance? – Tara Kizer Feb 16 '17 at 1:09
  • I believe it has to do with limited resources and the business not wanting to spend the money on the needed hardware. I'm still encountering that now because we put in for a budget that would do what we needed and they chopped it 75% and now we are trying to figure out how to make due with 4-6 blades instead of the much needed extra that our budget called for. That is also why I am looking to take things virtual in our dev / test environments to change it to one instance per node / server. You don't want to know what they think about a DR plan. It's scary. – bwilliamson Feb 16 '17 at 4:35
  • But hey, if they are willing to do only 15 minute transaction log backups and allow for a 15 minute data loss across the business then not much I can do. Though I do send out an email every few months reminding them what they have so I don't get fallout when things blow up and they are missing data. CYA – bwilliamson Feb 16 '17 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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