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:

Data is at risk of being corrupted.
Data is at risk of being lost.
Security is at risk of being compromised on the system.
System crashes (blue screens) that are repeatable (same failure) and occur regularly.
System hangs that are frequent (same criteria as for blue screens) and, if the Dump Switch, Service Processor, or Secure Admin Console is used to create a crash, the resulting dump file indicates a common cause (which would qualify as repeatable).
System performance is at an unacceptable level, such as data transfers that take hours instead of minutes.
The customer might have a system usage or configuration that is very similar to customers who have actually experienced one of these problems.

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

Test, test some more, and then test again. You want to make absolutely sure that when you roll this out in production, you know how it is done and that you will have no problems in terms of compatibility or availability as a result. Create a standard set of tests that are run for base operating system functionality and for application functionality. Record these results for comparison after the upgrade is performed.
Make backups of everything prior to applying the service pack or hotfix. Remember, system files such as DLLs and binaries are being changed. Should you need to go back to the state of your system if the installation does not work and cannot be backed out cleanly, having backups will save you. For a SQL Server service pack, back up all user databases and system databases before and after the upgrade.
Make sure your system has enough disk space to meet the requirements of the upgrade. This is sometimes overlooked. You might have to unlock your resources (for example, a read-only database) to run the upgrade scripts or add new procedures.
Again, as mentioned earlier, read all documentation that comes with the upgrade carefully . Do not apply and ask questions later; that could prove costly in both the short term and the long term.
After the application of the upgrade, test everything thoroughly alone as well as under load. Use the same standard set of tests that are run during the testing phase for base operating system functionality and application functionality so that you can do a before “after comparison. This will, more often than not, tell you if the upgrade was a success or a failure.
Once you are sure everything is okay, make full backups of your operating system and your databases so that you have a snapshot of a known, good post-upgrade configuration. Do not wait, because if something goes wrong, your backups might be older, and you will need to reapply any upgrades you just performed.

In terms of all versions of Windows Datacenter Server, these systems are often strictly managed from the standpoint of installing updates, adding enhancements, and making other changes. There are a number of reasons for this, such as the customer having an SLA with another party (for example, an OEM). For this reason and others, it is necessary to have retest requirements for systems and drivers so that the risk of change and possible reliability decrease is mitigated.

Datacenter Server customers occasionally require hotfixes. However, these should be infrequent and uncommon occurrences. You should apply hotfixes only when customers are actually experiencing problems, with some critical exceptions, as explained in this section.

Important

Hotfixes should not be applied in a preventive manner, except when it is clear you need them based on known issues. In all other cases, if a Datacenter Server system is not exhibiting signs of a failure that is resolved by a hotfix, OEMs and independent software vendors should not recommend that the customer install the hotfix. Remember that this applies to the operating system layer; any support and hotfixes for applications installed on top of Windows Datacenter editions should be cleared for installation to ensure that you will not invalidate your OEM support agreements.

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:

Distributor has to be upgraded before Publisher. If Publisher and Distributor are on the same instance of SQL Server, this is not a concern, as they will be upgraded together.
If you are using transactional replication with some read-only Subscribers (that is, they do not participate in replication other than receiving data), the Subscriber can be upgraded before or after you do Publisher and Distributor, so order does not matter for the Subscriber in this case.

If you are using merge or transactional replication with updating Subscribers (that is, they replicate data elsewhere), you must update the Subscriber after you update Distributor and then Publisher. The order in this case would be Distributor, Publisher, and then all Subscribers.

Tip	In this case, if you have a database that, for example, is a read-only subscriber for one publication but the source for another chain of replication servers, you must know your hierarchy so that you upgrade everything in the proper order.

If you are using a remote distributor with merge replication, you need to generate a new snapshot after applying SQL Server 2000 Service Pack 3. This might affect the availability of some servers, so you must plan for it.

As noted in many places, back up all databases participating in replication after the application of the service pack. This will ensure that you will not have to reapply the service pack if you need to restore the database.

Important

Heed the information in the previous section on log shipping about databases that are recovered and running sp_vupgrade_replication. This also applies to purely read-only databases, which are covered in the next section.

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

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 
```
Repeat step 1 for each read-only database on that instance that should be upgraded.
Apply (or reapply) the service pack.
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

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 
```
Repeat Step 1 for each read-only filegroup on that instance that should be upgraded.
Apply (or reapply) the service pack.
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.