How to install Microsoft SQL Server 2005 Service Pack 4 on one or more servers that are running Veritas Storage Foundation and High Availability Solutions for Windows

Problem

How to install Microsoft SQL Server 2005 Service Pack 4 on one or more servers that are running Veritas Storage Foundation and High Availability Solutions for Windows (SFW/HA)

Solution

This document describes two strategies for applying Microsoft SQL Server 2005 Service Pack 4 in a SFW/HA.

Strategy 1 – Parallel upgrade using spare volumes

Advantages:

– Less maintenance time required, particularly when a large number of nodes are being updated (SQL down-time only required during the upgrade of the first/active node in the cluster)
– Updating the secondary (disaster recovery) site does NOT require a global fail-over.

Disadvantage:

– Greater user interaction required: A spare volume must be mounted manually on secondary/passive nodes for temporary use during the upgrade process.

Strategy 2 – Serial upgrade using the original volumes

Advantage:

– Less user interaction required: Uses the same volumes that are currently used by SQL (no spare volume is required).

Disadvantages:

– More maintenance time may be required. SQL down-time required during the entire upgrade process (active and passive nodes)
– Updating the secondary (disaster recovery) site requires a global fail-over

Before continuing with either strategy, review ALL of the following items:

– A production outage is required for both strategies
– Before upgrading, verify that there is a recent backup of all SQL system and user databases.
– These upgrade processes can only be used in environments where no user databases are participating in Microsoft SQL replication.
– Review the Microsoft SQL Server 2005 – Server Pack 4 “readme” file. A copy of this document can be found at: https://download.microsoft.com/download/B/7/3/B73AFEF4-1A6C-42E6-B5A8-CBF990687A9E/ReadmeSQL2005SP4.htm
For Strategy 1, a spare volume must be available for use by the passive nodes. This can be a single volume that resides on a shared disk that is imported and deported from node to node, or a separate volume that resides on the local disks of each node. Note: This volume must be large enough to hold a copy of the SQL system databases and must NOT be a part of the SQL Disk Group.

For Strategy 2, if the SQL service group is a global group that was created with the Global Cluster Option (GCO), the secondary site will also need to be updated to the same Service Pack level to match the primary site. In order to upgrade the secondary site, it will be necessary to fail-over the SQL Service Group(s) to the remote cluster.

Strategy 1 Overview:
Parallel upgrade using spare volumes

The following is a high-level overview of the upgrade process. This is followed by the detailed upgrade steps to be performed. Please review the complete procedure before performing any actions.

Note: This process requires a single outage of the active node. Updating the passive nodes will not require a SQL outage

1. Determine the paths to the system databases.
2. On the active node, take the SQL Server 2005 resources offline, leaving the MountV resources and their dependencies online.
3. On the active node, make copies of the SQL system databases (specified in “Strategy 1 – Details” section below).
4. Close any Microsoft Management Console (MMC) windows.
5. On the active node, freeze the SQL service group.
6. On the active node, run the SQL Server 2005 Service Pack 4 upgrade according to the normal instructions that are provided by Microsoft.

Note: If you are not planning to upgrade all of the instances, uncheck the instances that are not going to be upgraded during the Service Pack 4 (SP4) installation. Manually stopping the instances will not necessarily prevent them from being upgraded.

7. On the active node, unfreeze the SQL service group.
8. On the active node, after the SP4 upgrade is finished, bring the entire SQL Server 2005 resource group online.
9. On the passive nodes, create and mount a spare volume to the same drive letter or mount path that is used by the SQL databases on the active node. This can be done with Veritas Enterprise Administrator (VEA).

Note: If this is a global cluster, the nodes at the secondary site can be treated in the same manner as passive nodes.

10. On the passive nodes, copy the “PreSP4” copies of the folders onto the spare volume.
11. On the passive nodes, rename the “PreSP4” copies of the folders to their original names. Ensure that the paths are identical to the paths that are used by the active node, including the drive letter.
12. Close any Microsoft Management Console (MMC) windows.
13. On the active node, freeze the SQL service group. This is to prevent the upgrade activity on the passive nodes from interfering with the active node.
14. On the passive nodes, run the SQL Server 2005 Service Pack 4 upgrade according to the normal instructions that are provided by Microsoft. The upgrade must be performed for each SQL instance.
15. On the active node, unfreeze the SQL service group.
16. The “PreSP4” copies of the folders used to upgrade the passive nodes can be deleted after all of the servers have been upgraded.

Strategy 1 – Details

1. Determine the paths to the system databases

The path of the databases can be found by running the following query in SQL Server Management Studio:

For the master database:
use master
go
sp_helpfile
go

For the model database:
use model
go
sp_helpfile
go

For the msdb database:
use msdb
go
sp_helpfile
go

Table 1 – Sample Data Output (varies per environment)

File
Location

Master Database file:
X:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Data\master.mdf

Master Log file:
X:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Data\master.ldf

Model Database file:
X:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Data\model.mdf

Model Log file:
X:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Data\modellog.ldf

MSDB Database file:
X:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Data\msdbdata.mdf

MSDBLog file:
X:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Data\msdblog.ldf

To illustrate the upgrade process, the following configuration will be assumed. The instructions should be adjusted according to the actual paths in the environment that is being upgraded.

The database directory is:

X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Data

The Analysis Services directories are (if applicable):

X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Data

X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Config

The Full-Text Search directory is (if applicable):

X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\FTData

2. On the first node to be updated, ensure that only the MountV resource(s) within the SQL service group are online. Only start the resources that are needed to bring the MountV resource(s) online (i.e., VMDg or RVGprimary, do NOT online the SQL resources at this time). This can be performed using the Veritas Cluster Explorer Java GUI.

Note: The NIC resource is a persistent resource that will remain online.

3. On the active node, make a copy of the SQL system databases:

To illustrate the upgrade process, the following configuration will be assumed. The instructions should be adjusted according to the actual paths in the environment that is being upgraded.The database directory is:X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\DataThe Analysis Services directories are (if applicable):X:\Program files\Microsoft SQL Server\MSSQL\OLAP\DataX:\Program files\Microsoft SQL Server\MSSQL\OLAP\ConfigThe Full-Text Search directory is (if applicable):X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\FTData2. On the first node to be updated, ensure that only the MountV resource(s) within the SQL service group are online. Only start the resources that are needed to bring the MountV resource(s) online (i.e., VMDg or RVGprimary, do NOT online the SQL resources at this time). This can be performed using the Veritas Cluster Explorer Java GUI.Note: The NIC resource is a persistent resource that will remain online.3. On the active node, make a copy of the SQL system databases:

a. Create the following empty folders (create a blank folder of the same name with “_PreSP4” appended):

X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Data_PreSP4

X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Log_PreSP4

Note: It is not necessary to copy the user databases.

b. If the Analysis services need to be upgraded, create these additional folders:

X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Data_PreSP4

X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Config_PreSP4

c. If Full-Text Search is installed, create this additional folder:

X:\Program files\Microsoft SQL Server\MSSQL\FTData_PreSP4

d. Copy the following files from the original \Data folder into the new empty \MSSQL\MSSQL\Data_PreSP4 folder:

– master.mdf

– mastlog.ldf

– model.mdf

– modellog.ldf

– msdbdata.mdf

– msdblog.ldf

– mssqlsystemresource.mdf

– mssqlsystemresource.ldf

e. Copy the following files from the original \Log folder into the empty \MSSQL\MSSQL\Log_PreSP4 folder:

– ERRORLOG

f. If Analysis services are present:

– Copy the contents of \OLAP\Data into the new, empty \MSSQL\OLAP\Data_PreSP4 folder.

– Copy the contents of \OLAP\Config into the new, empty \MSSQL\OLAP\Config_PreSP4 folder.

g. If Full-Text Search is present, copy the contents of \FTData into the empty \MSSQL\FTData_PreSP4 folder.

4. Close any Microsoft Management Console (MMC) windows. An open MMC window may interfere with the service pack installation.

5. On the active node, freeze the SQL service group (right-click service group in Cluster Explorer and choose Freeze > Persistent). This is to prevent the upgrade activity from causing resource faults and/or fail-overs.

6. On the active node, run the SQL Server 2005 Service Pack 4 upgrade according to the normal instructions that are provided by Microsoft. The installation should be performed for all SQL instances. When the upgrade has completed, the wizard may request a reboot. If this message appears, reboot the server before continuing.

Warning: Do not upgrade the active node until copies of the SQL system databases have been made.”PreSP4″ copies of the system databases are required to perform the upgrades on the passive nodes without requiring additional outages.

Note: If you are not planning to upgrade all of the instances, uncheck the instances that are not going to be upgraded during the Service Pack 4 installation. Manually stopping the instances will not necessarily prevent them from being upgraded.

7. Unfreeze the SQL service group on the active node. This can be done using the Veritas Cluster Explorer Java GUI (right-click the service group and choose Unfreeze).

8. On the active node, after the SP4 upgrade is finished, bring the entire SQL Server 2005 resource group online. This can be done using the Veritas Cluster Explorer Java GUI.

At this time, the SQL outage is over and client access can resume. The SQL Server will not be highly available until the passive nodes in the cluster have been upgraded. Please perform the following steps to upgrade the passive nodes. Note: These steps will not require any additional outages.

9. On the passive nodes, mount a spare volume to the same drive letter and/or mount path that is used by the SQL. This can be done within the Veritas Enterprise Administrator (VEA). Make sure that the spare volume is NOT created on a disk that is part of the SQL Disk Group. This volume will need to reside in a different Disk Group so that it can be brought online on the passive nodes while SQL is online on the active node.

Note: If this is a global cluster, the nodes at the secondary site can be upgraded in the same manner as the passive nodes.

10. On the passive nodes, copy the “PreSP4” database copies onto the spare volume. Since the user databases are not being copied, the amount of data transferred should be minimal. They can simply be copied over the network or by using removable media (such as a USB drive or CD). If a CD is used, it is necessary to reset the read-only property on the files.

11. On the passive nodes, rename the “PreSP4” copies of the folders to their original names. Ensure that the paths are identical to the paths that are used by the active node, including the drive letter (i.e. X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Data).

Note: If the same disk/volume is to be used to upgrade all passive nodes, make sure to maintain a copy of the original “PreSP4” databases to be restored to the disk/volume prior to upgrading each additional passive node. This is to ensure that all passive nodes load with system databases that have NOT been upgraded to SP4.

12. Close any Microsoft Management Console (MMC) windows. An open MMC window may interfere with the service pack installation.

13. On the active node, freeze the SQL service group in the Veritas Cluster Explorer Java GUI (right-click the service group, choose Freeze > Persistent). This will prevent the upgrade activity on the passive node(s) from interfering with the active node.

14. On the passive nodes, install Microsoft SQL Server 2005 – Service Pack 4 according to the normal instructions provided by Microsoft. The installation should be performed for all SQL instances. During this time, the SQL Server logs may complain about non-availability of the user databases. These warnings can be ignored.

Note: When the upgrade has completed, the wizard may request a reboot to complete the upgrade. If so, proceed with the reboot.

15. Once all passive nodes have been successfully upgraded, unfreeze the SQL service group on the active node (right-click the service group, choose Unfreeze from within the Veritas Cluster Explorer Java GUI).

16. After all nodes have been upgraded, the “PreSP4” database copies can be deleted. The temporary volume(s) used for upgrading the passive nodes can also be removed at this time.

Strategy 2 – Overview

Serial upgrade using the original volumes

The following is a high-level overview of the upgrade process. This is followed by the detailed upgrade steps to be performed. Please review the complete procedure before performing any actions.

Repeat Steps 11 through 18 for each additional node in the cluster excluding the final node to be upgraded.

Note: If a secondary (disaster recovery) site is present, update all of the nodes on the primary site according to Steps 11 though 18. Then, update all of the nodes of the secondary sites (also according to Steps 11 through 18) excluding the final node to be upgraded.

19. On the *final* node being upgraded, online the SQL service group up to (and including) the MountV resources.

20. Delete the folders that were being used to perform the upgrades (do NOT delete the contents of the “PreSP4” folders).

21. Rename the “Pre SP4” copies of the folders back to their original names.

22. Close any Microsoft Management Console (MMC) windows. An open MMC window may interfere with the service pack installation.

23. Freeze the SQL service group.

24. Perform the SQL 2005 SP4 upgrade.

25. Unfreeze the SQL service group.

26. Online the SQL service group on the active node.

Strategy 2 – Details

1. Determine the paths to the system databases

The path of the databases can be found out by running the following query in SQL server Management Studio:

For the master database:

use master

go

sp_helpfile

go

For the model database:

use model

go

sp_helpfile

go

For the msdb database:

use msdb

go

sp_helpfile

go

Table 1 – Sample Data Output (varies per environment