Wow, here we go again!  This service pack for SQL Server is releasing with

much less fanfare, but it is equally as important of an install for DBAs and

developers. What does this mean to you?  Well, it's now nearly as large as

Service Pack 1, but it still does have about 50 bug fixes for SQL Server 2000

and Analysis Services.  Also included in the service pack is MDAC 2.6.2,

which installs automatically or independently with the SQL Server service pack

installation. After reviewing the beta of this service pack and reviewing the

bug fix list, this is a definite install for anyone running SQL Server 2000.

What Machines Do I Install SP2 On?

The service pack should be installed on every server and client machines

administrating your SQL Servers. If you have a web application or any other

application hitting your SQL Server, make sure you install the MDAC 2.6.2

upgrade at a minimal on the client or webserver. You can install the minimal

requirements for MDAC 2.6.2 by running \x86\other\sqlredis.exe. This will also

install a number of other DLL files on the client machine to fix DTS problems

among other things. The sqlredis.exe is redistributable to your clients and the

license terms and conditions for its use can be found in the redist.txt file in

the root folder of the service pack.  By running this, you also update:

• PivotTable Service
• Decision Support Objects (DSO) 
• English Query
• Repository
• Replication
• Data Transformation Services (DTS) 
• Distributed Management Objects (DMO)
• SQL Namespace (SQLNS)
• OSQL
• DB-Library
• Jet 4.0
• Jet OLE DB 4.0
• ODBC Driver for Desktop Databases
• SQL Server 2000 Network Libraries
• SQL ODBC drivers
• SQL OLEDB provider
• SQL XML provider

Before Installation

Before you can install the service pack, go to 

http://www.microsoft.com/sql/downloads/2000/sp2.asp to download the

appropriate files. The service pack comes in three flavors: SQL Server 2000,

Desktop Engine, and Analysis Services. The

sql2ksp2.exe file is what most people will use to update SQL Server and it

will work on any edition of SQL Server except the Desktop Engine. 

Not sure what service pack you're on currently? You can open Query Analyzer

and execute the command SELECT @@VERSION to determine this. Below is a list of

versions:

• 8.00.194 - Base SQL Server 2000
• 8.00.384 - Service Pack 1
• 8.00.534 - Service Pack 2

To determine the version of Analysis Services you're running, simply open

Analysis Manager and select About Analysis Services under the Help menu. Here

are a list of Analysis Manager's versions: 

• 8.0.194 Base Analysis Services
• 8.0.382 Analysis Services SP1
• 8.0.532 Analysis Services SP2

Before installing, extract the setup file to a directory without any spaces

and backup your databases. If you're installing the Analysis Services service

pack, backup all the cubes as well as your registry. Proper backups are very

important because rolling back is NOT easy. I will cover rolling the system back

in a moment, but I can not emphasize your backups enough here. If you're

installing the service pack in a replicated system, make sure that you install

the service pack on the Distributor first, then the Publisher and any

Subscribers. Also before you begin the setup, make sure all services related to

SQL Server are stopped. This would include the Microsoft Component Services,

Microsoft Message Queuing, and Microsoft COM Transaction Integrator services if

you have these on your machine. Also make sure that your control panel is

closed.

One note to keep in mind before installing is the existence of any hot fixes

that you may have applied. If you have applied any hot fixes, ensure that those

were wrapped into this service pack. Otherwise, you risk overwriting the

critical  DLL file(s) that fixed one of your problems. Most of the SQL Server

executables and DLL files are replaced during this service pack install and you

could cause regressions by not being careful. 

It is also worth noting that service pack 2 includes all the fixes from

service pack 1 in it. If you have not installed service pack 1 yet, you will

really want to test this before loading it into production.

Installation

Installation of the service pack should be easy. If you are performing

multiple installs, I recommend creating an unattended installation script, which

allows you to install it rapidly and without interaction. After you extract the

files, you can run the setup file called setupsql.exe in the \x86\setup folder.

If you're installing the service pack from the network, you'll have to execute

this rather than the setup.bat file included in the root directory.

In the first few screens you'll be asked which instance you'd like to install

the service pack on and if you'd like to install it on a virtual server. By

these two questions, you can see some major upgrades to SQL Server 2000. First,

the service pack is instance aware. More importantly, the service pack is

cluster aware. Those that have installed SQL Server service packs in the past

know what a blessing this is. If you install the service pack on a clustered

server, make sure that all the nodes participating in the cluster are online.

Second, make sure you install the service pack on the server that owns the SQL

Server resources at the time of install. A reboot may be required so

prepare a maintenance window. Since the service pack is cluster aware,

you'll only have to install it once on each virtual server in your cluster.

Active/Active clusters will require two installations since there are two

instances running. As you install the service pack, the binaries are copied to

each instance automatically for you.

Next, you will be required to provide a sysadmin account to the server if you

are using Windows Authentication. If you'd like to use SQL Server

authentication, you can only provide the sa account password. On Windows 98 and

ME machines, you'll only have SQL Server authentication available.  If SQL

Server is not started already, it will be started at this point.

The first step is the MDAC upgrade, which takes only a brief moment. The next

step is where the setup program files are copied. Finally, the longest step is

where various scripts are run to upgrade your system catalog. This is why it is

so important to have a backup. If the setup breaks anywhere in the installation,

your SQL Server may be left in an unstable state. This is because your system

catalog may only be half upgraded or only some of the files may have been

copied. If a problem persist with the installation, do not leave the server in

this state.  Instead, you must roll back the service pack using the instructions

provided later in this article. After the installation completes, you will be

asked to backup your master and msdb databases. This is because these databases

have been upgraded during the install. You may be required to reboot the system

after the service pack install.  I recommend rebooting even if you're not asked

to since you will be ensured that all the services you stopped earlier are

correctly started.

One final step after installing the service pack is the hot fix that was

included in the service pack CD or download. This hot fix applies to English

Query and I would recommend that you install it only if you're using this

feature. If you are using the feature, you can find the hot fix in the \EQHotfix

directory and it involves replacing the DLL and EQD file.

Rolling Back

To roll back the service pack you will have to jump through a few hoops. To

remove the service pack, you must  first detach your databases using the

SP_DETACHDB command. Next, uninstall SQL Server 2000 and then install the base

version of SQL Server 2000. Finally, use the SP_ATTACHDB command to attach your

database files back to the server.

After you reinstall SQL Server 2000, you must recreate the logins needed for

the databases. In some cases, I've seen where you have to run

sp_change_users_login to remap the logins back to the users. You'll then need to

recreate the scheduled jobs (or restore then msdb database from an earlier

backup). Finally redo any replication and changes you've made to the model

database. As you can see, it's much easier just to restore from a earlier

backup. If you have backups, then reinstall SQL Server and restore you backups.

Summary of Bug Fixes

The service pack has your standard flavors of fixes I see in every service

pack. This service pack did seem to have a large amount of replication fixes in

it. If you are actively using any type of replication, this service pack

deserves extra testing before installing and it is of added importance that you

install it. If you're a user of Visual Studio, then this service pack will be

worthwhile as it fixes a number of regressions experienced when users applied

service pack 1. Also fixed in this service pack is an important memory leak

issue with SQL Mail. Really, the list of bug fixes in this service pack is

pretty tame compared to previous releases.

For a full list of bug fixes, see the

Microsoft support article.

Conclusion

After reviewing this service pack in detail and running the beta for a month

in a non-product environment, I must say that this is a definite easy install.

As always, just because it works in my lab and production room, it doesn't mean

it will work in yours. Always be careful before installing a vendor's service

pack or hot fix as they can lead to regressions. Service pack 2 for SQL Server

2000 can be downloaded at :

http://www.microsoft.com/sql/downloads/2000/sp2.asp. Please share your

experiences with the service pack in our special

Service Pack 2 forum. You can also click on the Your Opinion tab below to

share any opinions on this article.