How Many SQL Instances?

Question

Post reply

How Many SQL Instances?

Don Gamble

Grasshopper

Points: 14
More actions
June 20, 2006 at 3:33 pm

#168853

As an engineer, I'm responsible for installing, upgrading and maintaining the various Exchange and SQL MSCS clusters in our environment. Recently, I was asked to set up an Active/Active SQL 2000 cluster. After some trial and error, I learned of the importance of the ipconfg utility for creating the named pipe instances before actually running the setup to create that instance.

Then, being the curious type, I wanted to see what would happen when I created that 17th instance, the one that every source in the world claims is not possible. Low and behold, I've got 17 instances running, and I'm not sure why.

Using VMware ESX 3.0, I set up a two-node cluster using Win2003 Ent. sp1, and then created 17 failover cluster groups, each containing one 4GB volume. I installed 8 instances of SQL 2000 first, and then another 8 instances of SQL 2005, for a total of 16 named instances (no default instance). Each instance failed back and forth to each node with ease, so up to that point all was well.

Excitedly, I began the installation of the 17th instance of SQL 2005, knowing that I'd probably be seeing errors that few had ever seen (for who else, after all, would be such a geek as to attempt something as impractical?). To my surprise, setup didn't once warn me that there were already 16 instances. The installation continued to the end, until the final step, which was to bring all the cluster resources online: All came alive EXCEPT the SQL Server Agent. Here was the error that accompanied this event…

Source: SQLBrowser
Event ID: 3
“The configuration of the AdminConnection\TCP protocol in the SQL instance INSTANCE17 is not valid.”

So, I thought, this must be the famous 16-instance wall that I’d heard so much about. The limit must mean that MORE than 16 instances can be installed, but no more than 16 can be brought online at a time.

On a lark, I right-clicked on that failed resource and chose the “Bring Online” option. To my surprise, it came online. And it remained online, even while the other 16 instances continued buzzing happily. I spent the next 30 minutes failing every cluster group back and fourth waiting for some sign that one of the 17 instances was going to fail. At one time, I even had all 17 instances residing on one node.

My question: how is this possible, especially in light of the oft-quoted limitation of 16 instances? Is the limit hard-coded, of just a reccomendation?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

Which edition of SQL Server 2005?

Enterprise and Developer Editions are good to 50 named instances per server.

-Eddie

Eddie Wuerch
MCM: SQL

Don Gamble Grasshopper Points: 14 More actions · Answer 2

SQL Enterprise 2005. Yep, sure 'nuff, here's the link that states it pretty clearly...

SQL Server 2005 Books Online

Instance Name

http://msdn2.microsoft.com/en-us/library/ms143531.aspx

Duh! on my part. (There's an art to googling, and my artistic skills are wanting.)

But bear with this SLQ newbie: since SQL 2000 still has this 16-instance limit, as does SQL 2005 Standard Edition, does that mean that, given sufficient resources, a cluster could host up to 16 instances of 2000 TOGETHER with another 50 instances of 2005?

(Granted an unlikely scenario, but it's interesting as heck to know. Besides, in my world in which I do a lot of server consolidations using increasingly bigger and more powerful boxes, it could happen one day.)