I recently ran into a production instance of SQL Server 2005 SP2 (Build 3042) that is running in a two node, single instance fail-over cluster, running on Windows Server 2003 R2 SP2. As you may be aware, this entire stack is out of Mainstream support from Microsoft (both the OS and SQL Server). The objective is to be able to migrate a single, important database from this cluster to a new server running SQL Server 2008 R2 SP1 CU3 on top of Windows Server 2008 R2 SP1. Based on previous experience moving from SQL Server 2005 to SQL Server 2008, I was pretty sure that we would need to install a newer build of SQL Server 2005 on the cluster (at least SQL Server 2005 SP2 CU5) in order to be able to establish a database mirroring partnership from SQL Server 2005 to SQL Server 2008 or greater.

My first instinct was to install SQL Server 2005 SP4 on the fail-over cluster, but unfortunately, you cannot easily do a graceful rolling upgrade of a SQL Server 2005 cluster. Instead, you have to run the Service Pack setup from the node that owns the cluster resources (aka the “active node”), which means that the entire cluster will go down during the installation, plus the nodes will probably require a reboot. This would have meant at least a 15-20 minute outage, which was unacceptable.

Since I have a pretty decent test lab of machines on a Windows domain down in my basement, I decided to do some testing before I made any rash decisions. I had to look around a bit to find an x64 Windows Server 2003 R2 disc, along with an x64 SQL Server 2005 Enterprise Edition disc. After getting Windows Server 2003 R2 with SP2 installed on an actual physical machine, I had to download and install the chipset and NIC drivers, and then install about 150 updates from Windows Update to get the OS fully patched.

Then I installed the RTM (Build 1399) of SQL Server 2005 on the machine with no issues. Next, I tried to establish a mirroring session from SQL Server 2005 RTM to a separate physical machine running SQL Server 2008 R2 SP1 CU3 on top of Windows Server 2008 R2 SP1. I was able to “prepare the mirror” by running a full database backup and a transaction log backup on the Principal instance, and then restoring the two backups on the Mirror instance with no recovery. When I tried to create the mirroring TCP endpoints, I got an error about database mirroring being disabled in SQL Server 2005 RTM unless you enabled a trace flag. This was the case until Microsoft released SQL Server 2005 SP1.

Rather than installing SQL Server 2005 SP1, I just jumped to SP2, since this was what I had in Production. I reinitialized the mirror, and then to my surprise, I was able to create the mirroring endpoints and establish the database mirroring partnership between 9.0.3042 and 10.50.2789 (as you see in Figure 1).  This combination did not work when I had to do it  in back in 2008, going from SQL Server 2005 to SQL Server 2008, until we upgraded to a newer build of SQL Server 2005.

Next, I tried failing over the database, (which is a one-way trip), and this worked, although it puts the mirroring session into a suspended status and essentially breaks the mirror.  After failing over from 2005 to 2008 or greater, you will have to remove the mirroring partnership, and your 2005 database may be corrupted. That is the expected behavior, and it is just fine with me.

Figure 1: Mirroring From SQL Server 2005 SP2 to SQL Server 2008 R2 SP1 CU3

I guess the moral of this story is the value of a test lab to validate things like this before you are forced to try them in a Production environment. Having a few physical machines available rather than just a collection of VMs is also useful in some situations.

Filed under: Microsoft, SQL Server 2005, SQL Server 2008 R2, Windows Server 2008 R2 Tagged: Database Mirroring