How to apply a service pack to Mirrored SQL Servers

I patched a set of Mirrored servers last week, and I wanted to post the sequence of events here. Hope to help somebody else who needs to do the same.  To minimize downtime, I used a 'rolling' upgrade.  Very simply, this is the upgrade of the Mirror instance, followed by a manual fail-over, and then the upgrade of the new mirror server, formerly the Principal instance.

Say the servers are SERVERA and SERVERB and the mirrored database is JTSdr.  These are my steps to prepare for, and then to apply SQL Server 2008 sp4:

Prepare:

1.       Script dependencies to file from both instances

a)      Configuration settings

     SELECT * FROM sys.configurations ORDER BY name;

b)      Startup parameters

     SELECT registry_key,value_name,value_data

     FROM sys.dm_server_registry

     WHERE registry_key LIKE '%MSSQLServer\Parameters';

c)       Linked Server definition, if exists

     SELECT a.* FROM sys.servers a LEFT OUTER JOIN sys.linked_logins b

       ON b.server_id = a.server_id LEFT OUTER JOIN sys.server_principals c

         ON c.principal_id = b.local_principal_id

d)      SQL Server Agent Job definition

      SQL Server Agent\Jobs\ right click each job\Script Job as CREATE To File

e)      ALL SQL Server logins, with corresponding database users

           ...back soon on this one

f)       Mirror Session definition 

  SELECT d.name, d.database_id, m.mirroring_role_desc,

  m.mirroring_state_desc, m.mirroring_safety_level_desc,

  m.mirroring_partner_name, m.mirroring_partner_instance,

  m.mirroring_witness_name, m.mirroring_witness_state_desc

  FROM sys.database_mirroring m JOIN sys.databases d

    ON m.database_id = d.database_id

  WHERE mirroring_state_desc IS NOT NULL

Perform: 

1.         Connect to Principal, pause mirroring session:

ALTER DATABASE JTSdr SET PARTNER SUSPEND

2.         Backup JTSdr database on Principal instance, SERVERA

BACKUP DATABASE JTSdr TO DISK = 'E:\backups\JTSdr_20160601.bak'

WITH FORMAT;

3.         Connect to Principal, run CHECKDB on JTSdr, SERVERA

              USE JTSdr;

       DBCC CHECKDB

4.       Apply the patch to the Mirror, SERVERB

5.      Connect to Principal, resume mirroring session

    ALTER DATABASE JTSdr SET PARTNER RESUME

6.       Connect to Principal, perform manual fail-over so Mirror assumes Principal role

    USE master;

    ALTER DATABASE JTSdr SET PARTNER FAILOVER

7.       Connect to Principal, pause mirroring session

                  ALTER DATABASE JTSdr SET PARTNER SUSPEND

8.       Apply patch to the new Mirror, SERVERA

9.       Connect to Principal, resume mirroring session

    ALTER DATABASE JTSdr SET PARTNER RESUME

10.   Analyze logs, validate patch application success

Review SQL Server Error Log, Windows Application Event Log, and a Summary.txt and Detail.txt file at this location:        

      C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

Assuming success, the Final result in the Summary.txt file be one of these:

    Passed

    Passed but reboot required, see logs for details

Of course, SELECT @@VERSION  will now return this:

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (Intel X86)   Sep  2 2014 22:55:13 Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition on Windows NT 5.2 <X86> (Build : )