Yesterday evening (European Time zone) Microsoft has released the SP2 for SQL Server

2008 R2. You can download the Service Pack here: http://www.microsoft.com/en-us/download/details.aspx?id=30437

Because I'm currently working on a Database Mirroring Test Lab that is based on SQL

Server 2008, I thought it would be a good exercise to perform a Rolling Upgrade to

SP2 and document the correct sequence in this blog posting. In my Lab I have 3 Virtual

Machines that are performing the following roles:

• VM1: Principal
• VM2: Mirror
• VM3: Witness

When you are performing a Rolling Upgrade you have to do 2 failovers (Principal =>

Mirror => Principal), which means that you should perform the upgrade at a time where

the user impact of those failovers is very little. The idea of a Rolling Upgrade in

a Database Mirroring scenario is very straightforward:

• Disable the Witness (when you are using Automatic Failovers)
• Install the Service Pack on the Mirror
• Failover to the upgraded Mirror
• Install the Service Pack on the old Principal
• Failover to the upgraded Mirror
• Install the Service Pack on the Witness
• Add the Witness back to the Mirroring session

Let's have a more detailed look at these steps.

1. Disable the Witness

In my lab I have a dedicated VM which acts as a Witness to provide automatic Failovers.

In that case, you have to remove the Witness from your Database Mirroring session:

ALTER DATABASE TPC_E SET WITNESS OFF

GO

Before you remove the Witness you should also write down its network address, because

you need that address afterwards, when you want to add the Witness again to the Database

Mirroring session. You can use the view sys.database_mirroring to

find out the network address of the Witness:

SELECT

database_id,

mirroring_witness_name

FROM sys.database_mirroring

GO

2. Install the Service Pack on the Mirror

In the next step you can install the Service Pack on the Mirror. The update process

itself just takes a few minutes and during the process your SQL Server Instance on

the Mirror is restarted.

During the restart, your Principal runs in the Exposed state, which means, when you

lose your Principal in that timeframe, your database is down.

As soon as your SQL Server Instance is restarted, the upgraded Mirror is automatically

synchronized with the Principal:

3. Failover to the upgraded Mirror

In the next step you have to failover to the upgraded Mirror, which will become the

new Principal in your Database Mirroring session.

ALTER DATABASE TPC_E SET PARTNER FAILOVER

GO

During the failover your applications have to reconnect to your SQL Server databases,

which means you could have a short outage (it mainly depends on how well-written your

applications are…). During the failover the new Principal (the old Mirror) has also

to redo all transactions from the REDO queue, which also impacts how long the failover

will actually take.

Note: Only the Enterprise Edition SQL Server will provide you

multiple REDO threads. You will get 1 REDO thread for every 4 CPU cores that are assigned

to your SQL Server (per database). In Standard Edition the REDO is always done with

just 1 thread (per database).

As soon as the failover completed, your new Principal will run on the new Service

Pack Level:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30

Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1

(Build 7601: Service Pack 1) (Hypervisor)

4. Install the Service Pack on the Mirror (the old Principal)

After you have applied the new Service Pack to the old Mirror, you just do the same

on the old Principal.

5. Failover to the upgraded Mirror

After you have installed the Service Pack on the old Principal, you again fail back

to the upgraded old Principal.

ALTER DATABASE TPC_E SET PARTNER FAILOVER

GO

Now both the Principal and the Mirror are running on the new Service Pack Level, and

you are almost complete with your Rolling Upgrade. The only thing that is left is

the upgrade of the Witness, when you are using it for providing Automatic Failovers.

6. Install the Service Pack on the Witness

Nothing special is to note here.

7. Add the Witness back to the Mirroring session

As soon as your Witness is also upgraded, you can add the Witness back to your Database

Mirroring session:

ALTER DATABASE TPC_E SET WITNESS = 'TCP://sql2008hadr3.sqlpassion.com:5022'

GO

Conclusion

As you have seen in this blog posting, installing a new Service Pack through a Rolling

Upgrade in a Database Mirroring scenario is not a big deal. But you have to plan it

carefully, because you have to do 2 failovers, and those failovers will impact your

applications.

Thanks for reading!

-Klaus