Unanswered: SQL Server 2000 install with existing SQL Server 2005
The hardware boys surprised me by seting up a new 2003 server with SQL Server 2005 to support an application we're getting geared up to use (intranet-based timekeeping system, ) and migrate our intranet to this server as well. They got this done, and accounting populated all the data to allow the timekeeping system to work, and tested it offline. Just before they went live, they finally told me about it & ("Oh, by the way..." ) asked if anyone was using the SQL Server 2K database that was residing on the existing server...
It turns out (by the way) that there IS an important app using the database that will halt production if it's not available. Ooops.
So, I install SQL Server 2K (SP3) on the new server in it's separate named instance, drop in the users, & restore a snapshot of the existing databases & jobs on the new server. It all looks good, until I try to connect remotely. And, I can't. Nothing is connecting. (Error -21472215504, SQL Server does not exist or access denied.) The new instance IS configured to support named pipes & TCP/IP... However, IF I explicitly provide the SQL Server 2000 port number, the external apps DO connect.
In researching the issue, I've found that the 2005 SQL Browser has taken over the task of listening on the ports from SQL Server itself, and it's recommended that you install 2005 after 2000, and not the reverse...
Originally Posted by MSDN Blog
Emphasizing the need for SP3 or SP4
If you want to install SQL 2005 and SQL 2000 on the same box, you must patch SQL 2000 to SP3 or above. Let me explain why.
In SQL Server 2000, the identification of the server connection endpoints is performed by the SQL Server service. SQL Server 2005 replaces that function with the SQL Server Browser service.
In case you have SQL 2000 already installed, the SQL Server Service is will listen at UDP port 1434. However, when you install SQL 2005 on the same box, the SQL Browser service will wait for 5 seconds for SQL 2000 to relinquish port 1434
With SQL 2000 SP3 and above, this works fine. However, versions earlier than SP3 do not properly share port 1434 and may not make your instances of SQL Server available to requesting client applications. The SQL Server Browser might fail to start in instances running on less than SP3.
To resolve this problem with versions of SQL Server 2000 earlier than Service Pack 3, stop SQL Server 2000, start SQL Server Browser, then restart SQL Server 2000. The SQL Server 2000 listener service continues to attempt to start on port 1434; therefore, the instance of SQL Server 2000 should be upgraded to Service Pack 3 as soon as possible.
Apparently, during the 2K5 install, if there's an instance of 2000 running, SQL browser add a delay to it's response to connection requests on port 1434, allowing SQL Server 2K 'first crack' at responding, but, if SQL server 2000 is not present, the delay is not configured.
Does anyone have information on how to manually configure the 2K5 SQL browser to peacefully coexist with SQL Server 2000, even if 2K was installed after 2K5 ???
Last edited by loquin; 02-17-10 at 11:26.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
I have had a similar setup (SQL 2K installed after SQL 2K5), but have not had this problem. Although, instead of going to the hostname of the server, we go by DNS alias. I know this plays havoc with Kerberos so much that SQL Server reverts back to NTLM security. Maybe something similar happens with the browser. Try etting up a DNS alias for the machine, and see if you can get to the SQL 2k Instance via that DNS alias.