Database Mirroring From SQL Server 2005 to SQL Server 2008 R2 SP1 CU3

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.

 

clip_image001

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.

About these ads
This entry was posted in Microsoft, SQL Server 2005, SQL Server 2008 R2, Windows Server 2008 R2 and tagged . Bookmark the permalink.

9 Responses to Database Mirroring From SQL Server 2005 to SQL Server 2008 R2 SP1 CU3

  1. Pingback: Something for the Weekend – SQL Server Links 13/01/12

  2. Rudy Panigas says:

    Hello Mr. Berry,

    Great article!
    During your testing do you find any issues with mirroring the database from SQL 2005 to SQL 2008R2? We are in the situation but from a standalone server to another standalone server.

    Thanks,

    Rudy

    • Glenn Berry says:

      No real issues. Just remember that it is a one time, one way trip. You can mirror from 2005 to 2008 R2, but once you failover a database from 2005 to 2008 R2, the database will be upgraded to 2008 R2 and the mirror will be suspended. You won’t be able to go back t0 2005 with that database.

  3. rafiq says:

    Hi Glen
    you mention if you failover from SQL2005 to SQL2008R2, the database gets upgraded to 2008R2, which database are you referring to which will be upgraded? the one on the previous principal or the previous Secondary?

  4. Kurt says:

    Interesting, I may be in a position where I may have to mirror from 2005 to 2008 r2. Just wondering if I left the database in Compatibility Level 90 on the 2008 r2 box would I still have issues/problems if I need to fail back over to the 2005 db?

    • Glenn Berry says:

      Kurt,

      There is no going back to SQL Server 2005. Once you failover to SQL Server 2008 R2, the database gets upgraded to 2008 R2 format. See my other comments below.

  5. Sanjay. says:

    Glen,

    Is it possible to do mirroring from SQL Server 2005 sp3 to SQL Server 2012 sp1? Unfortunately I do not have a test environment to test it out. Does any one had tried it before? I have to upgrade a server from 2005 to 2012 and data base size is around 280 GB and down time given to me was 2 hours. Its not possible to move big backup file from one data center to another and then restore it on 2012 in 2 hours. Hence, I want to do mirroring before and then fail it over on node 2 and database gets moved without any issues. Let me know.

    Thanks in advance.

    • Glenn Berry says:

      You can easily test this ahead of time. Just create an empty database on your SQL Server 2005 instance and try to mirror it to your new SQL Server 2012 instance. If can get it mirrored, than you can try failing it over to see how that works.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s