Imagine that you are using synchronous database mirroring with SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012 Standard Edition, but you want to upgrade the instances on both sides of the mirroring partnership to SQL Server Enterprise Edition so that you can take advantage of some of the valuable Enterprise Edition-only features in that version of SQL Server (such as online index operations, data compression, table partitioning, etc.). How would you go about doing this without having to take a long outage or re-establish your mirroring partnership?

I had this question come up recently, but I was not 100% sure of the best answer without some testing. Remember, this is going to be a rolling edition upgrade from SQL Server Standard Edition to SQL Server Enterprise Edition, which is different from a rolling version upgrade from an older major version of SQL Server to a newer major version of SQL Server. If you need to do a rolling version upgrade, you can use log shipping or database mirroring to get it done with a very short outage (with database mirroring being a better way, in my opinion). Here is some guidance from Microsoft about how to do a rolling version upgrade: How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances

Here is some background on the terminology that Microsoft uses to differentiate between the different flavors of the SQL Server boxed product.

First, you have the major version of SQL Server, which could be one of these:

  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012

Next you have the edition of SQL Server, which depending on the major version involved could be one of these:

  • SQL Server Express Edition
  • SQL Server Workgroup Edition
  • SQL Server Standard Edition
  • SQL Server Business Intelligence Edition
  • SQL Server Developer Edition
  • SQL Server Enterprise Edition
  • SQL Server Data Center Edition

Most people will be dealing with upgrading from SQL Server Standard Edition to SQL Server Enterprise Edition, which is what we will discuss here.

If you need to to a rolling edition upgrade, you need to make sure that you have an existing database mirroring partnership for all of the databases that need to be mirrored between the two instances. This is because SQL Server will prevent you from establishing a new mirroring partnership between databases that are on different editions of SQL Server. Figure 1 shows the error message that you will get from the “Configure Database Mirroring Security Wizard” if you try to create a new mirroring partnership between a database on an instance running SQL Server 2008 R2 Standard Edition to a database running on SQL Server 2008 R2 Enterprise Edition.

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 1: Error Message When Trying to Create a New Mirroring Partnership between Different Editions of SQL Server

This means that you will need an existing mirroring partnership between your databases on the two instances before you attempt to do an Edition Upgrade of SQL Server from Standard Edition to Enterprise Edition. In order to minimize your downtime, and actually perform a rolling edition upgrade, you are going to want to upgrade the mirror instance first. You start this out by running Setup.exe from the SQL Server installation media, and then choosing Maintenance in the left hand pane of the screen, as shown in Figure 2.

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 2: Maintenance Screen Showing Edition Upgrade Option

After this, you just have to click though several different screens for various setup checks and licensing prompts as shown in Figures 3 through 10. The actual installation portion portion of the Edition Upgrade will take about 30 seconds. During the install, the mirrored database(s) will go into a disconnected state for about 10 seconds, and then will automatically reconnect and change from a synchronizing to a synchronized state.

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 3: Setup Support Rules Screen Results

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 4: Product Key Screen

You will have to enter a valid product key for SQL Server 2008 R2 Enterprise Edition.

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 5: License Terms Screen

You must accept the license terms in order to proceed.

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 6: Select Instance Screen

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 7: Edition Upgrade Rules Screen

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 8: Edition Upgrade Detailed Report Screen

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 9: Ready to Upgrade Edition Screen

After you click on Upgrade, it will take about 30 seconds to complete the process

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 10: Upgrade Complete Screen

Here is what you want to see when you are done with this instance.

image thumb How to Perform a Rolling Edition Upgrade While Using Database Mirroring

Figure 11: SSMS showing Mirror instance upgraded to SQL Server 2008 R2 Enterprise Edition

Running SELECT @@VERSION shows that this instance has been upgraded to SQL Server 2008 R2 Enterprise Edition. You can also see that the mirrored database has reconnected and is also synchronized. Your next step would be to failover all of the mirrored user databases from the original Principal instance (which is still running on SQL Server 2008 R2 Standard Edition) to this newly upgraded instance. After that, you would repeat the Edition Upgrade process on the other instance, with the same steps and results. After both sides of the mirroring partnership have been upgraded to SQL Server 2008 R2 Enterprise Edition, you would probably want to failover all of your mirrored user databases back to the original Principal instance so that you don’t end up violating the licensing requirements that allow you to only need licenses for the active side of the mirroring partnership.

The key takeaway of this exercise is that by following these steps, you can perform a rolling Edition Upgrade with an existing database mirroring partnership without having to reinitialize your mirrored databases or take a longer outage while you are doing an Edition Upgrade of the active instance.  You do have to have an existing mirroring partnership to make this work, since SQL Server will not let you create a new mirroring partnership between SQL Server Standard Edition and SQL Server Enterprise Edition.