SQL: 6.5 Compatibility in SQL Server 2005

calendar May 15, 2006 · 2 min read  ·
Share on: mastodon linkedin bluesky copy

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.

SQL Server 6.5 Compatibility mode in SQL Server 2005 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

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.