SQL: 6.5 Compatibility in SQL Server 2005

This isn't a very common scenario, but I figured I'd write about it anyway. We recently implemented a Windows 2003 Active Directory domain at work. I suggested to my employer that, going forward, we only install SQL Server 2005 when we need a database server (phasing out 2000). I'd also suggested consolidating a couple of our older servers (7 and 2000) into one.

So I setup a test SQL Server 2005 server in our lab environment. I migrated a few of our most active databases using two different methods: detach -> reattach and backup -> restore. Both worked perfectly well. Actually, the detach -> reattach complained about possible corrupt indexes but it was a false alarm. Other than that, things went very smoothly.

Apparently, one of the databases I migrated has been around for quite some time. It showed up as SQL 6.5 Compatible and lacked most of the options in the Management Studio.

I assumed that this particular database started out on a SQL 6.5 machine which was then upgraded to SQL 7 and SQL 7 kept it in 6.5 compatibility mode. Still, the database worked and I was able to issue a few queries to it without a problem. I wondered if perhaps SQL Server 7 or 2000's Enterprise Manager would allow me to manage that SQL 6.5 database in the GUI but I couldn't even connect.. SQL 2005 requires that you connect via the management studio to administer the machine graphically. Query Analyser connected just fine though it took a little bit of tweaking from the SQL 7 machine. Initially, I ran into this error:

Named Pipes Connection Broken. Alright, we'll avoid NetBIOS and try by IP.. perhaps that would force a TCP/IP connection. No dice. It just forced a TCP/IP over NetBIOS connection. I thought back to the days of SQL 7 and did the following, which worked: Start -> Programs -> SQL Server 7 -> Client Network Utility -> Add -> Server Alias: labsql2k5 | Network Library: TCP/IP | Server Name: 192.168.1.x

That worked like a charm. Initially, I wondered if the Named Pipes connection was breaking because the two SQL Servers are on different domains (that have a 2 way trust) but I had no problems connecting to a SQL Server 2000 machine over a truested domain -- no alias needed. SQL Server 2000's Query Analyser required no additional tweaking to connect to SQL 2005. This may be due to the fact that TCP/IP was listed as the first network protocol to attempt.