Max number of SQL Server instances can install on Active-Active cluster servers for each node.

Question

Hi,

I know, we can install up to 50 instances in standalone.

Can any one tell me how many instances we can install in Active-Active cluster servers for each node?

Thanks in Advance

Sriram 

Answer 1

Hello,

It depends:

1. All clustered instances of SQL Server require at least 1 unique drive letter, assuming mount points will be used.

2. There are a maximum of 26 letters in the English alphabet.

3. Take out A, B, C and D for reserved letters.

4. Assume NO disk witness will be used.

This leaves a total maximum of 26 - 4 = 22 Instances. Personally, just because you CAN doesn't mean you SHOULD. In reality it'll probably be much less than 22 instances before the shared hardware environment is overburdened.

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 2

You can install 25 instance in case of failover cluster.

Refer Link : http://technet.microsoft.com/en-us/library/ms143432.aspx

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 3

Rohit,

This assumes that all available (I would personally not use A, B, or D for drive letters in a clustered environment but others may) drive letters would be used. I would not advise someone to use the first 4 letters for clustered drives. Again, that's my personal opinion and preference.

While the link is correct and accurate, I would advise AGAINST installing 25 instances.

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 4

Sean

You & me posted on very less time diffrence that why i did not get chance to read you post.

Yes you are correct & I also preffer not to use these 4 drives letter (A,B,C,D).

But we have a option of mount points also where we can overcome this limitation & we can havemore than 26 drives also.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.

Answer 5

Rohit,

Yes, I understand about the posting time. I was replying to yours to make sure the OP knew that even though 25 could be done, it depends on implementation of said instances.

we can havemore than 26 drives also

You can't have more than 26 drive letters in a cluster. Each instance REQUIRES a unique drive letter to install to, regardless of mount points. It's a limitation of drive letters, not mount points and this limitation can NOT be overcome.

BOL (bolding is mine):

• The base drive, the one with the drive letter, cannot be shared among failover cluster instances. This is a normal restriction for failover clusters, but is not a restriction on stand-alone, multi-instance servers.

• The clustered installations of SQL Server are limited to the number of available drive letters. Assuming that you use only one drive letter for the operating system, and all other drive letters are available as normal cluster drives or cluster drives hosting mount points, you are limited to a maximum of 25 instances of SQL Server per failover cluster.

• http://msdn.microsoft.com/en-us/library/ms189910.aspx

The reason it is 25 (max) is because at least 1 drive letter is always used for windows.

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 6

Note that you can install 50 FCI's on a cluster if you use SMB for database storage.

"SQL Server supports 50 instances on a failover cluster if you choose SMB file shares as the storage option for your cluster"

Maximum Capacity Specifications for SQL Server http://technet.microsoft.com/en-us/library/ms143432.aspx

And Windows Server 2012 makes SMB storage for database files pretty attractive.  See

New SMB 3.0 features in the Windows Server 2012 file server http://support.microsoft.com/kb/2709568

For instance you can have a cluster where a subset of the nodes are a HA file server, storing the database files for all the rest of the nodes on the cluster. 

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 7

David,

Correct, but specifically for Windows Server 2012 with SQL Server 2012.

Do you see many people using SMB shares in clusters or using SMB shares in general with 2012?

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 8

Thank you all for explaining on this. 

Answer 9

@Sean  It's a brand-new configuration, and isn't really compelling for most customers who use traditional enterprise shared storage for clusters and FCI's.  Going forward, if you're building a bare-metal cluster for SQL Server, it's probably for a handful of critical workloads.  Not for a large number of miscellaneous instances in consolidation.

Even when consolidating onto bare-metal clusters, I wouldn't like to see more than 8-12 nodes per cluster, and 1 or two instances per node.  Add to this that for consolidation, everything is moving to VMs, and having a single WSFC with a large number of FCI's a very niche configuration.  It's something you might do in hosting, or in a SaaS provider, but not often in IT.

Using a subset of a cluster's nodes for a HA File Server is a really cool configuration for Hyper-V clusters, because you don't have to attach the storage to all the nodes, which enables you to use new low-cost storage types, like SAS JBOD enclosures.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 10

David,

Great, thanks for the insight on what you see and experience! I am not as exposed to the multitude of configurations as you would be (in the technology center and I'm sure dealing with customer inquiries), so it helps in seeing what direction people are taking and adopting.

-Sean

---

Sean Gallardy | Blog | Twitter