November 7, 2002 at 1:10 pm
Does anyone know where the data on connected IP addresses (assigned to the NIC) are stored for SQL Server 2000?
We have found you can not have more than 54 IP addresses on the machine or you can not start/restart the SQL Server if you have the TCP/IP protocol enabled. Yes, I know, we probably shouldn't have the IIS and SQL on the same machine but we have no choice.
There must be some buffer, data table, etc. that carries this and is JUST large enough for 54 IPs. I am hoping that MS was not so stupid as to make it impossible to expand this for more IPs.
What this has done is forced us to enable ONLY the Named Pipes protocol which in turn has caused us no end of grief trying to connect from remote machines using ODBC through the internet.
Among all you experts out there I hope someone has the answer to this.
Orin R. Wells
Kent, WA
Orin R. Wells
Kent, WA
November 7, 2002 at 1:36 pm
What service pack are you running and what version (standard, enterprise, msde) of SQL? Do you get any specific error messages and have you checked event viewer? I don't recall a limit. Also, how many NICs are you running, and are you multihomed (two different networks)?
November 7, 2002 at 2:12 pm
Service Pack 2 for the SQL Server 2000 which is a standard version. What happens if you have 55+ IP addresses is it generates a stack dump when the server is started. If we back down to 54 IP addresses it runs fine. When we took out the TCP/IP protocol from the Server Network Configuration it would allow us to run more IP addresses.
We tried using two NIC cards to split the IP load, but that made no difference. Which is logical because SQL Server is apparently storing information on each of the TCP/IP connections by IP address somewhere.
We are not multi-homed. This is on a co-located server in a server farm.
Orin R. Wells
Kent, WA
Orin R. Wells
Kent, WA
November 7, 2002 at 2:30 pm
Seems you are the only person to catch this, noticed your post on SQLMag forums as well. I don't believe there is a setting, I am pretty sure SQL does a bind to all available addresses when starts. Unfortunately I am at home and no server setup right now here. You may want to open a ticket with MS since I find no information similar to you issue either.
November 7, 2002 at 9:08 pm
While we have easily expended far more than it would cost us to open a trouble ticket, I am opposed to having to pay THEM $250 to acknowledge a bug in their system.
Orin R. Wells
Kent, WA
Orin R. Wells
Kent, WA
November 8, 2002 at 5:14 am
I looked around and found no mention of this. Have you tested the 54 limit on another box? If you get the same problem I would ask MS still, I do believe if it is an issue that has not been seen they will not charge you. However, if you can duplicate it on another box, I will try as well. I have a contact I might can use here who will pass along. The only thing is I don't have a free test machine to do this right now. I also know a person on the SP3 beta for 2000 and I may interject that to see if they or anyone else wants to throw in on this.
I feel it may be related to resources on the machine thou and that 54 is the limit of that machine. I am pretty sure MS does a bind to all available address with SQL and each one means additional memory to keep track of them. So I really need to know if it is specifically 54 or based on server resources.
November 8, 2002 at 7:53 am
We don't have a similar machine available to test this on. The one where the problem is happening is a dual-processor AMD 1.6g with 1GB memory and a mirrored (through an ARCO DupliDisk Raid)40GB Western Digital IDE. I shouldn't think even Micorsoft could suck up all that in this case.
This happened the first time we loaded this machine and we couldn't identify what was going on. So the entire system was re-loaded. This time we gradually loaded stuff and added IP addresses until we hit the problem. It is the same hardware, so that may not be a sufficient test.
It would be good to throw this at your contact at MS on the SP 3 project. While you are at it you might suggest they consider expanding the 64 IP limit as with the expanded resources available on today's machines it is surely going to appear again if we are the first to trip over this.
Orin R. Wells
Kent, WA
Orin R. Wells
Kent, WA
November 8, 2002 at 8:04 am
I'll have to test at home when I get a chance but I will pass anything I find back to the thread.
November 11, 2002 at 5:31 am
This sounds exactly like the type of problem one runs into when not using the Enterprise edition. The standard edition is sold with the recommendation for use in department size applications. However, if this is the problem, the error trapping should friendly and provide a pointer to the Enterprise edition.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply