Microsoft SQL Server 2000 High Availability

Service Packs and Hotfixes

All systems need normal maintenance, which greatly increases your long- term availability. This means that from time to time you need to apply patches and upgrades. Many administrators would consider service packs to be normal maintenance, but in reality, they are a form of upgrade. A service pack for any Microsoft product is a collection of patches, bug fixes, and so on, collectively tested and released as one distribution. Some service packs might enhance functionality, and others might not. Hotfixes are different than an SQL Server 2000 service pack or a service pack for a version of Windows 2000 or Windows 2003 Server. A hotfix is exactly what it sounds like: an issue that needs to be addressed is detected . In this case a patch is made available for you to apply between service pack releases.

The following is a definition of critical hotfixes, which can be defined as those hotfixes that affect the following problems:

Keep these general rules in mind when applying any type of service pack or hotfix:

Emergency Hotfixes and Testing Requirements

In some rare cases you might need a fix immediately, even without the delay incurred from update testing. In general, it can be better for your availability to work around the problem instead of installing an untested kernel component. Note that because the provided file might be unsigned, the customer is notified in a pop-up message of that fact when the kernel component is installed.

In these cases, even if a hotfix is immediately required and testing cannot be accomplished before delivery to the customer, the responsible party should accomplish the testing as soon as possible to ensure the stability of the system. The driver should be submitted to Windows Update, if appropriate, only after this testing has occurred (see Figure 13-1).

Tip

If you are using Windows Update for operating system hotfixes and critical updates, you might want to disable automatic updates on a server that you want to be highly available to control what is installed. First and foremost, it might or might not be directly connected to the Internet. Second, if you choose to automatically download and apply all fixes that you have not tested, you might unknowingly affect your production server. If you enable automatic updates, the best thing to do would to be to have Windows Update notify you before downloading and installing updates.

Figure 13-1: Automatic Updates screen in Windows.

More Info

For more information on putting a plan together for your production changes, see Chapters 1 and 2.

Applying a Windows Service Pack

Applying a service pack to your operating system will affect the availability of your SQL Server in one way or another whether it is clustered or not. A Windows service pack is applied per server, or specifically , per instance of Windows (if you have a multiboot system). On a stand-alone system, the process is straightforward in that you install it on the operating system and the server, and its functionality is unavailable during the whole process. Clustered systems are similar, yet different.

Windows Service Packs and Server Clusters

One significant difference between a stand-alone server and a server cluster in terms of an upgrade is that your environment can be a bit more available (depending on how you have things configured). Because a Windows service pack is done per installation of Windows, in an N -node cluster, you will hopefully have N “ 1 nodes still completely up and available ( assuming no other problems). Because of this, you use a process similar to that of a rolling upgrade of the operating system. First, notify users that the clustered process (such as SQL Server) will be unavailable at certain specific times. You can then manually fail the resources over to another node and start servicing requests again. At the same time, you can also be upgrading node 1. However, here is where your planning comes into place. If you planned your system resource usage properly, you should have no performance impact after a failover, and there should be no need to cause another availability interruption by failing the resources back (or to yet another node) until you are going to upgrade the other nodes in the cluster.

Note

At the time of the writing of this book, no information about Windows Server 2003 Service Packs was available, so all information is based on Windows 2000.

Applying a SQL Server 2000 Service Pack

Before applying a SQL Server 2000 service pack, you need to understand how it works for both clustered and nonclustered environments. As you know, a single server or a server cluster running SQL Server 2000 supports up to 16 instances per operating system installation. Each installation has some dedicated binaries, as well as a set of shared binaries (including MDAC). The binaries for each instance are usually found on the drive you selected during installation under \Program Files\Microsoft SQL Server. Under that directory, you will find an \80 directory containing any shared binaries for all instances not installed under your Windows directories. You will find the dedicated binaries for a default instance in the \Mssql directory, and you will find the dedicated binaries for a named instance in the \Mssql$ instance_name directory. What does all of this background mean to you? Microsoft fully supports, should you have multiple instances of SQL Server 2000, a mixed service pack environment. However, consider the ramifications of such an environment: whenever you apply a service pack, you upgrade any shared binaries. So, for example, if you have two instances of SQL Server both at Service Pack 2, and then upgrade one, you now have one at Service Pack 3, one at Service Pack 2, with shared binaries at the Service Pack 3 level. Going back to the idea that everything affects everything, although this is fully supported from a Microsoft standpoint, make sure that it is supported by any third-party software vendors you are using. It also complicates any plan to roll back an environment to a previous version. If your installation of a service pack on one instance fails, and you install, for example, the RTM version of SQL Server 2000, you will have potentially downgraded your shared components until you upgrade to the right patch or service pack level.

A SQL Server 2000 service pack is applied per instance, so if you have five instances, you will have to run the installation process five times. There is no way to install them all at once with one process. You generally only have to reboot after the first one because there are locked files (by the operating system) that require the reboot to upgrade, and files that were not locked before will probably not be locked now, or the files will already have been upgraded and therefore be skipped by the installation. On nonclustered instances, you can script the installation of a SQL Server 2000 service pack.

When a SQL Server 2000 service pack is installed, a log file is created under your Windows installation directory, such as C:\Windows. If there has been more than one execution of a SQL Server 2000 service pack install, the log files are numbered sequentially, such as Sqlsp1.log, Sqlsp2.log, and so on.

Note

There are separate service packs for SQL Server 2000 (including Standard Edition, Enterprise Edition, and Developer Edition), SQL Server 2000 Desktop Engine (MSDE 2000), and SQL Server 2000 Analysis Services. You might need to get different versions of the service pack and apply each of them separately depending on what is installed on your server.

Although there are no 64-bit SQL Server 2000 service packs as of the publication of this edition of the book, the 32-bit and 64-bit service packs should behave the same. If the behavior winds up being different for some reason under the 64-bit version, it will be documented in the information that comes with the service pack and in Knowledge Base articles.

Important

You must install SQL Server 2000 Service Pack 3 (or later) for SQL Server 2000 to work with Windows Server 2003.

Installing a SQL Server 2000 service pack is a permanent option for a SQL Server instance. There is no way to revert to the previous version of SQL Server without a complete reinstall from an installation point or CD-ROM, which obviously causes an availability problem. However, keep in mind that rolling back could destroy other things like MDAC versions that were upgraded in the interim and needed for an application, so you must keep track of everything done to the server so that you can put the server back in the state you need it to be in for your applications.

Failover Clustering and SQL Server 2000 Service Packs

Applying SQL Server 2000 service packs is not the same as applying a service pack to a clustered server running SQL Server 7.0. The older process for SQL Server 7.0 involved using a wizard to uncluster SQL Server 7.0. You would then apply the service pack and recluster. It was an awkward procedure at best, and it caused problems for some. With a SQL Server 2000 service pack, as with the main installation process, the service pack is cluster-aware. It will detect that it is going to be applied to a virtual server.

However, there is no concept of a rolling upgrade as there is with the operating system, so SQL Server is unavailable during the entire operation. During the installation process, the installer will then proceed to not only upgrade the database, but all binaries on each node defined for that specific virtual server. Therefore, you run the service pack install once and have it applied to all nodes. You do not run the service pack install on each node; however there is one (and only one) exception to that rule: should you encounter a catastrophic failure on one node and have to rebuild it (see Chapter 5, Designing Highly Available Microsoft Windows Servers, and Chapter 6, Microsoft SQL Server 2000 Failover Clustering, for details), you do not have to rerun the setup to patch all nodes. You can just patch the newly repaired node. This is possible because when the node is added back into the SQL virtual server definition, a registry key of need_sp_key is added to the newly rebuilt node, indicating it needs to have a service pack applied to it. You can now run the SQL Server 2000 service pack installation on the node, and Setup checks for need_sp_key and proceeds to update the binaries on that node only because the databases were already updated. This also allows you to fully service requests from applications or clients because SQL Server 2000 is not put into single-user mode. On the new node you are updating, you will see some entries in the Sqlsp.log file that will look similar to the following:

[args] NumRemoteServers=1 Server.1=<newnodename> [Server.1] NumRemoteServices=1 ...

These entries confirm that the unattended setup processes are running on the one node only. From a GUI standpoint, you will see the standard Setup Is Performing Required Operations On messages, but behind the scenes, it is doing the right thing.

Caution

If you rerun the SQL Server 2000 service pack installation from the node currently owning the SQL Server resources and not the failed ones added back into the SQL virtual server definition, you will then be reapplying the service pack to all nodes defined for the SQL virtual server. This should be seen as a potential risk, as you have one or more perfectly functioning nodes that are now being affected. Make sure before you start the process that you are on the right node!

Warning

Due to file replacements that need to be registered, the first installation of a SQL Server 2000 service pack usually requires a reboot of all nodes. Once you do this, unless there is some other requirement for a reboot in the service pack, all other instances on the cluster that have the service pack applied should not need a reboot because the shared files are already installed and registered. You can check the HKLM\CCS\Control\Microsoft\Session Manager\PendingFileRename s registry key for a list. By stopping whatever services were using these files before running the upgrade you might avoid the reboot in the future, but perform this procedure with caution. It might just be best to do the reboot. Again, test before doing it in your production environment.

Log Shipping and SQL Server 2000 Service Packs

If you employ log shipping, you must also understand the impact of a SQL Server 2000 service pack. First and foremost, a service pack upgrade does not break log shipping. Setup automatically detects user databases (as well as filegroups) that are not able to be written to and skip those. Because a database that is being log shipped on a secondary is either in NORECOVERY or STANDBY, it cannot be written to or updated. These skipped databases are documented in the Sqlsp.log file mentioned earlier. The following message is also displayed during the process:

Setup has detected one or more databases and filegroups which are not writable.

It is fully supported to log ship from any version of SQL Server (RTM, Service Pack 1, Service Pack 2, and Service Pack 3 as of the writing of this book) to another because there are no metadata changes that would affect log shipping. If there were to be any metadata changes to the user databases themselves in future service packs, this might require you to have all databases at the same service pack level. Read the documentation that ships with the service pack to see if there are any user database metadata changes that are included.

You should, however, consider service pack upgrades to an instance of SQL Server if your primary is at one level and your secondary is at another.

Important

If you recover a database from secondary status to be the active database servicing requests, you do not need to reapply the SQL Server 2000 service pack to it unless there are metadata changes that would affect the database in question (as noted earlier). This would be clearly documented in whatever comes with the update. The one exception to this rule is if you are using replication and use the keep_replication flag when bringing the database online. Once the database is fully recovered, before opening it up to users and applications, run the stored procedure sp_vupgrade_replication to upgrade the replication metadata. If you do not do this, the replication metadata for that database will be out of sync. If running sp_vupgrade_replication is not necessary, it will be noted in the accompanying documentation.

Warning

Do not apply any SQL Server 2000 service pack prior to Service Pack 2 on a log shipped server. Service Pack 1 would not apply successfully on a server that had databases that could not be written to.

Replication and SQL Server 2000 Service Packs

Replication and SQL Server 2000 service packs are a bit more complicated a combination than log shipping or failover clustering. The order in which you upgrade your instances participating in a replication chain absolutely matters, specifically:

Applying a SQL Server Service Pack to a Nonwritable Database or Filegroup

There are cases where you literally have a read-only database (such as in replication) or one that is used for reporting but needs to be upgraded (that is, it is a Subscriber) to the latest service pack. Because read-only databases are skipped in the service pack install for SQL Server, these need to be upgraded. To accomplish this task, follow these steps prior to applying the service pack or after, depending on your needs. It is better to do it beforehand so that you have only one availability outage and not multiple outages:

Nonwritable Database

  1. Alter the state of the database that needs to be modified to make it writable. You can either clear the Read-Only option in the Options tab of the database Properties dialog box or run the following Transact -SQL statement:

    ALTER DATABASE database_name SET READ_WRITE

  2. Repeat step 1 for each read-only database on that instance that should be upgraded.

  3. Apply (or reapply) the service pack.

  4. Reselect the Read-Only option, or run the following Transact-SQL statement for each database to make it read-only again:

    ALTER DATABASE database_name SET READ_ONLY

Nonwritable Filegroup

  1. Alter the state of the filegroup that needs to be modified to make it writable. Run the following Transact-SQL statement:

    ALTER DATABASE database_name MODIFY FILEGROUP filegroup_name READWRITE

  2. Repeat Step 1 for each read-only filegroup on that instance that should be upgraded.

  3. Apply (or reapply) the service pack.

  4. Run the following Transact-SQL statement for each database to make it read-only again:

    ALTER DATABASE database_name MODIFY FILEGROUP filegroup_name READONLY

Hotfixes

Hotfixes are ultimately added, in most cases, to the next major service pack. Should you apply a hotfix when it is released? Do you avoid the hotfix? The answer is not clear cut. Not all hotfixes apply to you, so you have to read any relevant Knowledge Base articles. Like anything else, assess the risk and reward of applying the hotfix. Be aware that because they are essentially one-off fixes, and, because of their critical nature, hotfixes might not undergo the rigorous months of regression testing that, say, a service pack is subjected to. They fix a problem, but in doing so, they might affect other applications or processes running on the server.

In addition to individual hotfixes, often bundles or rollups of hotfixes are released from time to time. For example, a common rollup would be security fixes that are wrapped together. These packages sometimes receive additional testing before release to help address some of the more common issues at that moment. Security is very important, of course, and putting off the testing and deployment of these patches could cost you the server, its data, and ”in a worst-case scenario ”potentially even your job. Finding the correct middle ground between testing and deployment is the only safe way to proceed.

Note

As of the release of this book, Microsoft policy is to support the creation of hotfixes on the previous service pack for 60 days after the current service pack ships. For example, hotfixes of any nature (security, bug, and so on) can be made for up to 60 days after SQL Server 2000 Service Pack 3 is released specifically for SQL Server 2000 Service Pack 2.

Note

Keep in mind that the next service pack will also contain other enhancements or fixes that might not have been released as a patch for the version of the service pack that you have installed. Once the new service pack is released, you should test your applications against it and upgrade as soon as possible. If you cannot upgrade for other reasons, such as those that are business- related (for example, a third- party application must be certified with it otherwise you will invalidate your support contract), evaluate these situations and take the appropriate actions to ensure that you can upgrade to the latest service pack.

You will be supported on whatever version of a service pack you are using as long as the platform itself is in active support; you just might not be able to get new patches for it. If this policy changes for future service packs, such as when SQL Server 2000 Service Pack 4 is released, please check http://support.microsoft.com or consult a Microsoft support professional about the policy for hotfixes and service packs.

Категории