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

Article:TECH51643  |  Created: 2007-01-01  |  Updated: 2010-01-10  |  Article URL http://www.symantec.com/docs/TECH51643
Article Type
Technical Solution


Environment

Issue



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

Solution



This document describes two strategies for applying Microsoft SQL Server 2005 Service Pack 2 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 2 "readme" file. A copy of this document can be found at:  http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.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 2 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 2 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 SP2 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 "PreSP2" copies of the folders onto the spare volume.
11. On the passive nodes, rename the "PreSP2" 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 2 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 "PreSP2" 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)
 
FileLocation
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:
a. Create the following empty folders (create a blank folder of the same name with "_PreSP2" appended):

X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Data_PreSP2
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Log_PreSP2

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_PreSP2
X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Config_PreSP2

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

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

d. Copy the following files from the original \Data folder into the new empty \MSSQL\MSSQL\Data_PreSP2 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_PreSP2 folder:

- ERRORLOG

f. If Analysis services are present:

- Copy the contents of \OLAP\Data into the new, empty \MSSQL\OLAP\Data_PreSP2 folder.
- Copy the contents of \OLAP\Config into the new, empty \MSSQL\OLAP\Config_PreSP2 folder.

g. If Full-Text Search is present, copy the contents of \FTData into the empty \MSSQL\FTData_PreSP2 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 2 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."PreSP2" 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 2 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 SP2 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 "PreSP2" 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 "PreSP2" 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 "PreSP2" 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 SP2.

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 2 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 "PreSP2" 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.

1. Determine the paths to the system databases
2. Take the SQL Server 2005 resources offline, leaving the MountV resources and their dependencies online.
3. Rename the original folders (add a "PreSP2" suffix) to "hide" them from the SQL SP2 upgrade process.
4. Create empty folders using the same path and drive letters as the original folders.
5. Copy the system databases (specified in "Strategy 2 - Details") from the "PreSP2" folders into the empty folders.
6. Close any Microsoft Management Console (MMC) windows.
7. Freeze the SQL service group.
8. Perform the SQL 2005 SP2 upgrade.
9. Unfreeze the SQL service group.
10. Offline the SQL service group.
11. Online the SQL service group (up to the MountV resources) on the next node to be upgraded.
12. Delete the contents of the newly created SQL folders (Do not delete the contents of the "PreSP2" folders).
13. Copy several system databases (specified in "Strategy 2 - Details") into the empty folders.
14. Close any Microsoft Management Console (MMC) windows.
15. Freeze the SQL service group.
16. Perform the SQL 2005 SP2 upgrade.
17. Unfreeze the SQL service group.
18.  Offline the SQL Service group

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 "PreSP2" folders).
21. Rename the "Pre SP2" 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 SP2 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
 
FileLocation
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.

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

3. Rename the following directories to "hide" them from the SQL upgrade process. The reason for hiding them is to maintain a "Pre SP2" copy of  the databases that can be used to upgrade SQL on the other nodes in the cluster. This ensures all nodes will be upgraded with Pre SP2 databases in place.

The database directory:
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Data_PreSP2

The Analysis Services directories (if present):
X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Data_PreSP2
X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Config_PreSP2

The Full-Text Search directory (if present):
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\FTData_PreSP2

4. Create the following empty folders. The names, structure and drive letters (or mount path) of these folders should be identical to the names of the original folders (excluding the "PreSP2" suffix).

The database directory:
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Data

The log directory:
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Log

The Analysis Services directories (if present):
X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Data
X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Config

The Full-Text Search directory (if present):
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\FTData

5. Copy the following files from the "Pre SP2" folders into the new, empty folders:
a. Copy the following files from the \Data_PreSP2 folder into the new, empty \MSSQL\MSSQL\Data folder:

- master.mdf
- mastlog.ldf
- model.mdf
- modellog.ldf
- msdbdata.mdf
- msdblog.ldf
- mssqlsystemresource.mdf
- mssqlsystemresource.ldf

b. Copy the following files from the \Log_PreSP2 folder into the new, empty \MSSQL\MSSQL\Log folder:

- ERRORLOG

c. If Analysis services are present:

- Copy the contents of \MSSQL\OLAP\Data_PreSP2 into the new, empty \MSSQL\OLAP\Data folder.
- Copy the contents of \MSSQL\OLAP\Config_PreSP2 into the new, empty \MSSQL\OLAP\Config folder.

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


6. Close any Microsoft Management Console (MMC) windows. An open MMC window may interfere with the service pack installation.
7. 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.
8. Install Microsoft SQL Server 2005 - Service Pack 2 according to the normal instructions provided by Microsoft. The installation should be performed for all SQL instances. When the upgrade has completed, the wizard may request a reboot to complete the upgrade. If this message appears, take the SQL service group completely offline before rebooting the server.

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 2 installation. Manually stopping the instances will not necessarily prevent them from being upgraded.


9. 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).
10. Completely offline the SQL service group.
11. On the next node to be updated, online only the MountV resource(s) and dependencies within the SQL service group (i.e., VMDg or RVGprimary) . Do not online the SQL resources at this time.
12. Delete the contents of the folders that were used to perform the upgrade. Do not delete the folders themselves.

By default, these folders should be:

The database directory:
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Data

The Analysis Services directories (if present):
X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Data
X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Config

The Full-Text Search directory (if present):
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\FTData

WARNING: Do not delete the contents of the original "Pre SP2" folders as these files will be copied to the original location so that all upgrades will be performed with Pre-SP2 databases.


13. Copy the following files from the "Pre SP2" folders into the new, empty folders:
a. Copy the following files from the \Data_PreSP2 folder into the new, empty \Data folder:

- master.mdf
- mastlog.ldf
- model.mdf
- modellog.ldf
- msdbdata.mdf
- msdblog.ldf
- mssqlsystemresource.mdf
- mssqlsystemresource.ldf

b. Copy the following files from the \Log_PreSP2 folder into the new, empty \Log folder:

- ERRORLOG

c. If Analysis services are present:

- Copy the contents of \OLAP\Data_PreSP2 into the new, empty \OLAP\Data folder.
- Copy the contents of \OLAP\Config_PreSP2 into the new, empty \OLAP\Config folder.

d. If Full-Text Search is present, copy the contents of \FTData_PreSP2 into the empty \FTData folder.

14. Close any Microsoft Management Console (MMC) windows. An open MMC window may interfere with the service pack installation.
15. Freeze the SQL service group (right-click service group in the Veritas Cluster Explorer Java GUI and choose Freeze > Persistent). This is to prevent the upgrade activity from causing resource faults and/or fail-overs.
16. Install Microsoft SQL Server 2005 - Service Pack 2 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. When the upgrade is has completed, the wizard may request a reboot. If this message appears, take the SQL service group completely offline before rebooting the server.

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 2 installation. Manually stopping the instances will not necessarily prevent them from being upgraded.

17. Unfreeze the SQL service group. This can be done using the Veritas Cluster Explorer Java GUI (right-click the service group and choose Unfreeze).
18. Completely offline the SQL service group.

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 Steps11 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.

Perform the following steps when upgrading the final node:

19. On the final node to be updated, online only the MountV resource(s) and dependencies within the SQL service group (i.e., VMDg or RVGprimary). Do not online the SQL resources at this time.
20. Delete the folders that were being used to perform the upgrades.

By default, these folders should be:

The database directory:
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\Data

The Analysis Services directories (if present):
X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Data
X:\Program files\Microsoft SQL Server\MSSQL\OLAP\Config

The Full-Text Search directory (if present):
X:\Program files\Microsoft SQL Server\MSSQL\MSSQL\FTData

WARNING: Do not delete the contents of the original "Pre SP2" folders.

21. Rename the "Pre SP2" 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. This is to prevent the cluster from interfering with the upgrade process. This can be done in the Veritas Cluster Explorer Java GUI (right-click the service group, choose Freeze > Persistent)
24. Install Microsoft SQL Server 2005 - Service Pack 2 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. When the upgrade has completed, the wizard may request a reboot to complete the upgrade. If this message appears, take the SQL service group completely offline before rebooting the server.

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 2 installation. Manually stopping the instances will not necessarily prevent them from being upgraded.

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


Legacy ID



288662


Article URL http://www.symantec.com/docs/TECH51643


Terms of use for this information are found in Legal Notices