SQL Server 2000 named instances / TCPIP
-
Hoping for help on an issue with SQL Server 2000 named instances. Have a vmware server (win2003) which now has 2 named instances of sql 2000 SP4 (but problem also existed under sp23a)
If client connections use named pipes either with or without a client side alias no problem exists and connections can be made (using EM, Q analyser or any other client app) to either instance.
Connections to instance1 via tcp ok even without a client side alias using dynamic port (i.e listening on 1434 and handing to the instances dynamic port) all ok so far. Enter instance 2, if this instance is started no connection is possible over tcpip to it, unless a client side tcp alias is defined specifying the dynamic port that instance2 is using. Its as if the 1434 listener does not know about the second named instance or its dynamic port. Here is where it gets interesting. Stop instance 1 and connections to instance2 now work without the need for the client side tcp alias. Restart instance 1 and generally any existing client connections continue to work for instance 1. Any new tcp connections to instance1 now fail unless a client side tcp alias is created specifying instance 1's dynamic port!!.
Why is this happening? SQL should be able to listen for multiple named instances and I have systems where this works so what is happening here? Is it a quirk of VMware or am I missing something obvious. I can find no articles where others have experienced this problem. Would love to hear from anyone with thoughts as its a real show stopper. I do not want to have clients being forced to use named pipes nor do I want to have to establish tcp aliases as SQL Server should handle this.
Thanks in advance
Derek
-
Hi Derek:
I have also had a similar experience with application port conflicts on a multi-instance SQL Server.
The fix for this problem is by isolating the ports that are needed by SQL Server from Dynamic allocation. To do this, you would need to make changes in the SQL server registry (use Regedt32)
Make the following changes to the registry; this will resolve the Port conflict issue.
a) Start Registry Editor (using regedt32, Regedit should not be used to edit Windows 2000 registry as it can corrupt the registry / registry entries)
b) Locate and then click the following registry key:
c) HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
e) Locate the Value Name ReservedPorts. Edit (or create if non-existent an entry using the following steps):
i. On the Edit menu, click Add Value.
ii. In the Value Name box, type ReservedPorts.
iii. In the Data Type list, click REG_MULTI_SZ, and then click OK.
Else
g) In the Multi-String Editor dialog box, type the range of ports that you want to reserve.
Note: You must type the range of ports in the following format: xxxx-yyyy (1047-1433). To specify a single port, use the same value for x and y. For example, to specify port 1047, type 1047-1047.
Warning: You can not type comma separated values or non contiguous values in this registry key, it has to be contiguous values only.
h) Click OK.
i) Quit Registry Editor.
Reboot the server for this registry changes to take effect.
HTH,
Rgds,
GSV
-
You do not mention if the 2 instances are on the same 'virtual' server or not. If they are on the same 'virtual' server then 'alias'ing and registry hacks are options (aliasing first). If these 2 named instances are on different 'virtual' servers, then I'd say that you've run into a VMWARE 'bug'.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Both instances are on the same vmware server. Aliasing may be an option but why is SQL refusing to dynamically pass connections to the dynamic port for each listener? By the way after more testing noted that the reserved port entries for 1433-1434 get created by the apply of SP4, prior to this no entries, wonder if removal might achieve anything?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply