1.0 Introduction
Microsoft SQL Server 2000 Service Pack 3a (SP3a) incorporates
changes to SQL Server 2000 SP3 that address issues raised by the Slammer worm:
- SP3a can be applied to SQL Server 2000 Enterprise
Evaluation Edition in order to apply fixes to instances of that edition of SQL
Server.
- SP3a addresses setup issues some customers encountered with
the version of the Microsoft Data Access (MDAC) components in SQL Server 2000
SP3
- The MSDE 2000 SP3a Setup utility now defaults to turning
off support for network connections when installing new instances of MSDE 2000.
The current network support settings are retained when instances are upgraded
to SP3a.
- When an instance of SQL Server 2000 SP3a or MSDE 2000 SP3a
is configured to not support network connections, the instance also stops using
UDP port 1434.
Because most changes introduced in SP3a are related to
Setup, you do not need to apply SP3a to instances of SQL Server 2000 or MSDE
2000 that have already been upgraded to SP3. If you have instances that have
not yet been upgraded to SP3, however, you should always upgrade those
instances directly to SP3a instead of SP3. If you have any copies of the SP3
download files that you were planning to use for future upgrades, you should
delete those copies and instead use either the SP3a download files or the SP3a
CD-ROM. Applications that distribute and install MSDE 2000 should install SP3a
instead of SP3. While you can apply SP3a to instances of SQL Server 2000 SP3 or
MSDE 2000 SP3, doing so has little effect on those instances.
This release
of Service Pack 3a (SP3a) for Microsoft SQL Server 2000 is provided in three
parts:
- Database Components SP3a provides updates for the database
components of an instance of SQL Server 2000, excluding instances of the SQL
Server 2000 Desktop Engine. Database Components SP3a includes updates to:
- The database engine.
- All of the database client tools and utilities such as
SQL Server Enterprise Manager and the osql utility.
- Database client connectivity components, such as the
Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver,
and the client Net-Libraries.
- Analysis Services SP3a provides updates for the SQL Server
2000 Analysis Services components of a SQL Server 2000 installation, including:
- Analysis Services.
- Analysis Services client components, which include
Analysis Manager and the Microsoft OLE DB Provider for Analysis Services.
- Database client connectivity components, such as the
Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver,
and the client Net-Libraries.
- Desktop Engine SP3a provides updates for the database
components of an instance of SQL Server 2000 Desktop Engine (MSDE 2000),
including:
- The database engine.
- The database command prompt utilities that come with
MSDE 2000, such as the osql and bcp utilities.
- Database client connectivity components, such as the
Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver,
and the client Net-Libraries.
These three parts of SP3a can be applied individually, as
follows:
- SQL Server 2000 sites can use Database Components SP3a to
upgrade their database components without upgrading their Analysis Services
components or instances of the SQL Server 2000 Desktop Engine.
- Analysis Services 2000 sites can use Analysis Services SP3a
to upgrade their Analysis Services components without upgrading instances of
Desktop Engine or their database components.
- MSDE 2000 sites can use Desktop Engine SP3a to upgrade
instances of MSDE 2000 without upgrading Analysis Services or instances of the
SQL Server 2000 database engine.
Note If separate instances of both MSDE 2000 and other editions of the
SQL Server 2000 database engine are installed on the same computer, you must
apply Desktop Engine SP3a to instances of MSDE 2000, and Database Components
SP3a to instances of the SQL Server 2000 database engine, such as Personal
Edition, Standard Edition, or Enterprise Edition.
Note Desktop Engine SP3a is the only part of the service pack that is
available in Portuguese (Brazil), Swedish, and Dutch, because SQL Server 2000
Desktop Engine (MSDE 2000) is the only version of SQL Server 2000 that is
produced for those languages. The SQL Server 2000 components upgraded by
Database Components SP3a or Analysis Services SP3a are not available in those
languages. Portuguese (Brazil), Swedish, and Dutch users who want to apply SP3a
to a version of SQL Server other than Desktop Engine must download the SP3a
files that match the language of the edition they want to upgrade. For example,
English-language SP3a files must be downloaded to upgrade the English-language
version of the SQL Server 2000 database engine. For information about how to
download the service pack, see Section 2.0 Downloading and Extracting SP3a.
1.1 Overview of Database Components SP3a Installation
The Database Components SP3a Setup program automatically detects
which edition of SQL Server 2000 is present on the instance of SQL Server 2000
being upgraded. Setup upgrades only the components that are installed for that
instance. For example, if you apply the service pack to a computer running SQL
Server 2000 Standard Edition, the service pack does not attempt to upgrade
components that are included only with SQL Server 2000 Enterprise Edition.
You can apply Database Components SP3a to a single default instance
or a named instance of SQL Server. If you are upgrading multiple instances of
SQL Server 2000 to SP3a, you must apply SP3a to each instance. When one
instance on a computer with one or more instances of SQL Server 2000 is
upgraded to SP3a, all of the tools are upgraded to SP3a. There are not separate
copies of the tools for each instance.
1.2 Overview of Desktop Engine (MSDE 2000) SP3a Installation
Desktop Engine SP3a can be used to:
- Upgrade an existing instance of SQL Server 2000 Desktop
Engine (MSDE 2000) to SP3a.
- Install a new instance of MSDE 2000 SP3a, provided you have
an MSDE 2000 license that allows you to install instances of MSDE 2000.
- Upgrade an instance of MSDE 1.0 to MSDE 2000, provided you
have an MSDE 2000 license that allows you to run instances of MSDE 2000.
- Provide the files needed for a developer to write an
application setup utility that can install an instance of MSDE 2000 SP3a as
part of the installation of the application. The developer must have a license
to distribute MSDE with applications.
For more information about MSDE 2000 licensing, see
Uses of MSDE
2000.
The procedures for applying SQL Server 2000
Service Pack 3a to an existing instance of MSDE 2000 depend on how the instance
was installed. The Desktop Engine SP3a files from Microsoft can only be used to
apply SP3a to instances of MSDE 2000 that were installed using the MSDE 2000
setup utility. Most applications install MSDE 2000 using one of two mechanisms:
- The setup utility of the application calls the MSDE 2000
setup utility during installation. Instances of MSDE 2000 installed this way
can be upgraded to SP3a using the Desktop Engine SP3a files.
- If the setup utility of the application uses Microsoft
Windows Installer technology, the setup utility can directly consume the MSDE
2000 merge modules. Instances of MSDE 2000 installed this way cannot be
upgraded using the Desktop Engine SP3a files. You must contact the application
vendor for a patch file. Desktop Engine (MSDE 2000) SP3a provides merge modules
to support existing applications that use merge modules. The Setup utilities
for new applications should be written to call the MSDE 2000 Setup utility
instead of directly consuming the MSDE 2000 merge modules.
Instructions for determining whether an
instance of MSDE 2000 can be upgraded to SP3a using Desktop Engine SP3a are in
section 2.1 Downloading and Extracting Desktop
Engine (MSDE 2000) SP3a. Even if you are applying Desktop Engine
SP3a from the SQL Server 2000 SP3a CD, read the instructions in section 2.1 to
determine whether you can apply Desktop Engine SP3a or you must contact an
application vendor for a patch file.
If there are multiple instances
of MSDE 2000 on a computer, you must evaluate each instance individually to
determine whether you can apply Desktop Engine SP3a. You must also apply SP3a
separately to each instance.
Review the material in sections 1 and 2
of this readme, and then follow the instructions in section 3 if:
- You have determined that you can apply Desktop Engine SP3a
to your existing instance of MSDE.
- You are installing a new instance of MSDE 2000.
- You are incorporating the Desktop Engine SP3a files into
the setup utility of an application.
These sections give examples for the most common
scenarios of either upgrading an existing instance of MSDE to SP3a, or
installing a new instance of MSDE 2000 SP3a:
- 3.7.4 Upgrading Existing
Instances of MSDE 2000 To SP3a
- 3.7.5 Installing a New
Instance of MSDE 2000 SP3a
- 3.7.6 Upgrading MSDE 1.0
to MSDE 2000 SP3a
1.3 Removing SP3a
The way in which you remove SQL Server 2000 SP3a depends on the
SQL Server 2000 SP3a components that you are removing.
Removing SQL
Server Database Components and Desktop Engine SP3a When
either SQL Server Database Components SP3a or Desktop Engine SP3a are
installed, they make changes to the system tables for maintenance reasons, and
they also upgrade user and distribution databases that are members of a
replication topology. Because of these changes, SP3a cannot be removed easily.
To revert to the build that you were running before installing SP3a, first, you
must uninstall the instance of the SQL Server 2000 database engine or MSDE
2000; then, you must reinstall that instance. If you were running a previous
SQL Server 2000 service pack or applied any Quick Fix Engineering (QFE) fixes,
you must reapply that service pack and any QFE fixes to the instance.
Note To remove SP3a, you must have backups of the master, model, and
msdb databases, which were taken immediately prior to applying SP3a. For more
information, see Section 3.1 Back Up Your SQL
Server Databases and Section 3.2
Back Up Your Analysis Services Databases and Repository.
For more information, see Uninstalling SQL Server 2000 Components and Desktop Engine SP3a.
Removing SQL Server Analysis Services Components SP3a
To be able to return SQL Server Analysis Services to its
pre-SP3a state, you must back up the registry key
HK_LOCAL_MACHINE\Software\Microsoft\OLAP Server and all of its
sub-keys before installing SP3a. When uninstalling SP3a, you must delete this
registry key and restore the pre-SP3a version from the backup.
Note Updates to MDAC 2.7 SP1a that are made during SP3a setup cannot
be uninstalled.
For more information, see
3.1.4 Uninstalling SQL Server 2000 Analysis
Services SP3a.
1.4 Identifying the Current Version of SQL Server or Analysis Services
Use the techniques in the following sections to determine
which version of SQL Server or Analysis Services you have installed.
SQL Server
To identify which version of the SQL Server 2000 database engine
or MSDE 2000 you have installed, type SELECT @@VERSION or
SERVERPROPERTY('ProductVersion') at the command prompt using the
osql
or
isql utility or in the Query
window in SQL Query Analyzer.
Similarly, the product level for a
given version of the SQL Server 2000 database engine or MSDE 2000 can be
determined by executing SELECT SERVERPROPERTY('ProductLevel').
The
following table shows the relationship between the SQL Server 2000 version and
level and the version number reported by @@VERSION and the product level
reported by SERVERPROPERTY('ProductLevel').
| SQL Server 2000 version and
level | @@VERSION | ProductLevel |
| SQL Server 2000 Original
Release | 8.00.194 | RTM |
| Database Components SP1 or Desktop Engine
SP1 | 8.00.384 | SP1 |
| Database Components SP2 or Desktop Engine
SP2 | 8.00.534 | SP2 |
| Database Components SP3 or SP3a, or Desktop Engine SP3 or
SP3a | 8.00.760 | SP3 |
If you are not sure which edition of the SQL Server
2000 database engine or MSDE 2000 you are running, view the last line of output
returned by SELECT @@VERSION. The last line should match one of the following:
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 2)
Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Note The beginning of the line indicates the edition of the SQL Server
database engine or MSDE 2000. This is followed by current operating system
information.
You can also determine the edition by typing SELECT
SERVERPROPERTY('Edition') at the command prompt using the
osql
or
isql utility or in the Query
window in SQL Query Analyzer.
Analysis ServicesTo identify which version of Analysis Services you have
installed, follow these steps:
- From the Start menu, point to
Program Files, point to SQL Server 2000,
point to Analysis Services, and then click Analysis
Manager.
- In the Analysis Manager tree, right-click the Analysis
Servers node, and then click About Analysis
Services.
- Use the following table to determine which version of
Analysis Services you have.
| Analysis Services version | Build Number in Help
About |
| SQL Server 2000 Analysis Services Original
Release | 8.0.194 |
| Analysis Services SP1 | 8.0.382 |
| Analysis Services SP2 | 8.0.534 |
| Analysis Services SP3 or SP3a | 8.0.760 |
Distinguishing Between SP3 and SP3aTo determine whether you have SP3 or SP3a installed,
look at the version number of the Net-Library file, Ssnetlib.dll. If the
version number of this file is 2000.80.760.0, you have SP3; if the version
number of this file is 2000.80.766.0, you have SP3a.
Assuming that an
instance was installed using the default settings, you can find the
Ssnetlib.dll file in one of these locations:
- Default instance: C:\Program Files\Microsoft SQL
Server\Binn\Ssnetlib.dll
- Named instance: C:\Program Files\Microsoft SQL
Server\MSSQL$<InstanceName>\Binn\Ssnetlib.dll
To find out whether you have the SP3 or SP3a setup folders,
open the SP3Readme.htm file and read the first page. The service pack version,
SP3a or SP3, is listed at the top of the first page.
1.5 Additional Information about SP3a
A list of the fixes contained in this service pack will be
provided in Microsoft Knowledge Base article
306908. Each fix listed in
306908 has a link to a Knowledge Base article describing
the problem addressed by the fix. Follow the links to the individual Knowledge
Base articles to see information about each fix
To find an article in the Knowledge Base- From the Select a Microsoft Product
list, select SQL Server 2000.
- In the Search for field, type the
number of the article you want.
- Under Search Type, select Article
ID.
- Click the Go button.
Any information relevant to SQL Server 2000
Service Pack 3a that was not available in time to be included in this readme
file will be published in Microsoft Knowledge Base article 816502.
The
Knowledge Base articles mentioned in this readme are available in the
Microsoft
Product Support Services Knowledge Base.
Microsoft Data Access ComponentsThis service pack includes updates to the Microsoft Data
Access Components (MDAC), including updates for MSXML.
For more
information, see Section 5.5.1 Updates to
Microsoft Data Access Components.
QFE Fixes
All publicly issued SQL Server 2000 SP2 security bulletins have
been addressed in SP3a. If you received a SQL Server 2000 hotfix after October
14, 2002, that hotfix is not likely to be included in SP3a.
Contact
your primary support provider about obtaining the same hotfix for SQL Server
2000 SP3a.
SQL Server CE Server Tools UpdatesUsers of Microsoft SQL Server 2000 Windows CE Edition (SQL
Server CE) who have upgraded or plan to upgrade SQL Server 2000 database and
publisher servers to SP3 or later should also update the server replication
components on Microsoft Internet Information Services (IIS) servers. An updated
SQL Server CE Server Tools installer is available at this
Microsoft Web
site.
1.6 Updated Books Online Documentation Is Available
Updated documentation for SP3 and SP3a is available. SQL Server
2000 Books Online (Updated - SP3) contains minor revisions plus new information
that is related to SP3 and SP3a.
You can download SQL Server 2000
Books Online (Updated - SP3) from this
Microsoft Web site.
1.7 Updated SQL Server and Analysis Services Samples Are Available
Samples for the SQL Server 2000 database engine and
Analysis Services that have been updated for SP3 and SP3a are available. You
can download these updated samples from this Microsoft Web site.
2.0 Downloading and Extracting SP3a
SP3a is distributed in the following ways:
- On a SQL Server 2000 Service Pack 3a CD-ROM that contains
service packs for the following components:
- Database Components SP3a
- Analysis Services SP3a
- Desktop Engine SP3a
- In three self-extracting files that can be downloaded from
the Internet at the
Microsoft SQL Server
Downloads Web site:
- Sql2ksp3.exe (Database Components SP3a)
- Sql2kasp3.exe (Analysis Services SP3a)
- Sql2kdesksp3.exe (Desktop Engine SP3a)
Note Both the SQL Server 2000 Service Pack 3a CD-ROM and
Sql2kdesksp3.exe contain all of the files that are required to install a new
instance of Desktop Engine.
Note The download files for SP3a have the same names as the files for
SP3. If you have an SP3a CD, you can install SP3a directly from the
CD.
If you do not have an SP3a CD, you can download the appropriate
file and then run it. When the download file is run on your computer, it will
create a set of folders and files on your disk that have the same organization
as the folders and files on the SP3a CD. After this file extraction phase is
complete, you can install SP3a from the folders on your disk.
When
downloading and extracting SP3a installation files from the Internet, use the
following guidelines:
- Download one of the files listed in section 2.0 and place
the self-extracting file either in a folder on the computer that is running the
SQL Server 2000 software that you are upgrading to SP3a, or in a folder on a
network share. From that folder, run the file. The self-extraction program
prompts you for the name of the folder into which you want the service pack
files placed.
Note When you extract the service pack to a network share, the path to
the folder that you specify is relative to the folder in which you ran the
self-extraction program. - On the local drive or network share to which you download
the service pack installation files, you must have free space that is
approximately three times the size of the self-extracting file. This includes
space to store the self-extracting file, space for storing the extracted
service pack files, and temporary working space required by the self-extraction
program itself.
- You can rename or move the folder after extracting the
components; however, make sure that the directory path name does not contain
blank spaces. You can use the same target folder for each of the
self-extracting files; they will not overwrite each other or interfere with
each other.
- The self-extracting files are used to build a set of
directories and files that are the same as those on the SP3a CD-ROM. If you
download SP3a you must extract the files to build the service pack directories
before you can run SQL Server Setup.
- For Database Components SP3a and Analysis Services SP3a,
the setup process is the same for both the CD-ROM and the extracted files.
Note Some of the files in the service packs are system files. You
cannot view them unless you follow this procedure: In Windows Explorer, on
the
View menu, click
Options, click the
View tab, and then select the
Show all files check
box.
How to Determine the Language of an Instance of the SQL Server 2000 Database Engine or MSDE 2000SQL Server 2000 service packs are language-specific. To
upgrade SQL Server 2000, you must download and apply the service pack that has
the same language as your SQL Server 2000 software. For example, if you are
upgrading an instance of MSDE 2000 that uses Japanese, you must download the
Japanese version of Desktop Engine SP3a.
- Click Start, then click
Run.
- In the Open box, type Regedit, then
click OK
- Locate and select one of the following keys:
- For a default instance of the SQL Server 2000 database
engine or MSDE 2000, locate and select this key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion - For a named instance of the SQL Server 2000 database
engine or MSDE 2000, locate and select this
key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\InstanceName\CurrentVersion where InstanceName is the name of the
instance
- In the right pane, read the value of the Language entry.
Compare that value to the following table to determine the language for your
instance of the SQL Server 2000 database engine or MSDE 2000:
| Language registry value (in hexadecimal) | Language
registry value (in hexadecimal) | Language registry value (in
hexadecimal) |
| 0x00000404 | 1028 | Traditional Chinese |
| 0x00000407 | 1031 | German |
| 0x00000409 | 1033 | English |
| 0x0000040a | 1034 | Spanish |
| 0x0000040c | 1036 | French |
| 0x00000410 | 1040 | Italian |
| 0x00000411 | 1041 | Japanese |
| 0x00000412 | 1042 | Korean |
| 0x00000413 | 1043 | Dutch |
| 0x00000416 | 1046 | Portuguese (Brazil) |
| 0x0000041d | 1053 | Swedish |
| 0x00000805 | 2053 | Simplified Chinese |
Database Components and Analysis Services SP3a FilesDatabase Components and Analysis Services installation files
both contain updated setup documentation that you can access by clicking Help
during SP3a setup. This documentation does not update the version of SQL Server
2000 Books Online that is already installed on your computer. For information
about how to get an updated version of SQL Server Books Online, see Section 1.6
Updated Books OnlineDocumentation Is
Available. If you want to access just the updated SQL Server 2000
SP3a setup documentation without updating SQL Server Books Online, run the
Setupsql.chm file. Setupsql.chm is located in the \Books subfolder of the
directory on the SP3a CD-ROM, the local directory, or the network share that
contains the extracted service pack files.
2.1 Downloading and Extracting Desktop Engine (MSDE 2000) SP3a
Users who have an existing instance of MSDE 2000 must
know how their instance of MSDE 2000 was installed to know how to patch it. You
can determine this from the information recorded as a
ProductCode entry in a registry key by following the
instructions in Microsoft Knowledge Base Article
311762.
- If the original package name for your ProductCode value was SqlRun01.msi through SqlRun16.msi, you can
upgrade your instance of MSDE 2000 using the SQL Server 2000 SP3a download
files. See above for information on how to determine the language of an
instance of MSDE 2000.
- If the original package name for your ProductCode value refers to a Microsoft application, see the following page for
instructions on how to upgrade that instance of MSDE 2000: Microsoft MSDE
Applications Web page.
- If the ProductCode value is not listed in
the article, then the instance was installed by the setup utility of an
application. You cannot use the SQL Server 2000 SP3a download files to apply
SP3a to such MSDE instances. Instead, you must get a patch file from the
company that wrote the application. If the application came from a company
other than Microsoft, you must contact that company for a patch file. If the
application came from Microsoft, see the following page that lists the MSDE
applications from Microsoft (this page will be updated with information about
how to upgrade these instances of MSDE 2000):
Microsoft MSDE
Applications Web page.
- If the original package name for your
ProductCode value was Sample.msi or SampleUpg.msi, see
Knowledge Base article
314131.
Downloading MSDE 2000 SP3a
If your instance of MSDE 2000 was installed using the
MSDE setup utility (original package name was SqlRun01.msi through
SqlRun16.msi), you can upgrade your instance of MSDE 2000 using a SQL Server
2000 SP3a download file:
- Go to the
Microsoft SQL
Server Downloads Web site.
- In the Full Download box in the
upper-right of this page, select the language that matches the language for
your instance of MSDE 2000, and click Go.
- On the next page, locate the Files in this
Download section at the bottom of the page. Depending on the language,
select one of these files:
- If you selected English, download the file
SQL2KDeskSP3.exe to a folder on your computer.
- If you selected another language, download the file
LLL_SQL2KDeskSP3.exe, where LLL varies by language.
Extracting the MSDE 2000 SP3a Files
After downloading either SQL2KDeskSP3.exe or
LLL_SQL2KDeskSP3.exe, you must extract the MSDE 2000 SP3a files:
- Open Windows Explorer and navigate to the folder in which
you downloaded the MSDE 2000 SP3a download file (either SQL2KDeskSP3.exe or
LLL_SQL2KDeskSP3.exe, where LLL varies by language)
- Double click on the download file to extract the SP3a MSDE
files. When asked, specify the folder into which the SP3a files can be
extracted.
3.0 Service Pack Installation
To install SP3a, use the installation instructions in the
following sections. Not all steps are required, depending on which of the
following SQL Server 2000 components or configurations you are applying the
service pack to:
- Database Components
- Analysis Services
- Desktop Engine
- Failover cluster
- Replication server
The first sentence in each of the following sections
indicates the component(s) to which that section applies.
Before Starting an InstallationIf your instance of the SQL Server 2000 database engine
or MSDE 2000 is being used by an application, first check with the provider of
the application to see if there are any database engine or MSDE 2000 upgrade
considerations specific to that application.
An SP3a installation
will fail if either of the following security policies has been set to
Do not allow installation:
- The Devices:Unsigned driver installation
behavior local security policy for Windows XP
- The Unsigned non-driver installation
behavior local security policy for Windows 2000
If you use
the Do not allow installation setting, you must change it to
Silently succeed before installing SP3a. If necessary, you can
return the policy to its previous setting after the installation is complete.
Note Do not allow installationis not the default
setting for these security policies.
When installing SQL Server 2000
SP3a on a computer running Windows XP, MDAC 2.7 will not be updated to MDAC 2.7
Service Pack 1a (SP1a). If your system requires any of the fixes included in
MDAC 2.7 SP1a, you must first apply Windows XP Service Pack 1 before you
install SQL Server 2000 SP3a. Windows XP SP1 will apply all the fixes included
in MDAC 2.7 SP1a.
When installing this service pack on a pre-release
version of Microsoft Windows Server 2003 build 3683 or older, you will receive
the following error message:
The software you are installing has not
passed Windows Logo testing to verify its compatibility with this version of
Windows. This software will not be installed. Contact your system
administrator.
You can choose to ignore this message. Click
OK to continue Setup.
Note This message blocks an unattended installation.
When
installing this service pack on a computer running Windows NT 4.0 Service Pack
6a, you must apply the hotfix described in Microsoft Knowledge Base article
258437.
Before you install SP3a on the French version of Windows NT 4.0,
follow the instructions in Knowledge Base article
259484. You can access the article from the
Microsoft Product Support Services Knowledge Base. For
instructions on searching the Knowledge Base, see Section 1.5
Additional Information About SP3a.
Before Starting a Database Components Installation
If you apply Database Components SP3a to an instance of SQL
Server on a computer on which Analysis Services is also installed, Setup may
fail when executing the script, Sp3_serv_uni.sql. If this happens, restart the
computer and run Setup again.
- SQL Server 2000 service packs do not affect the ability to
back up user databases from one release (original release or a service pack) of
SQL Server 2000 or MSDE 2000 and restore them on another release, except when
the user database is part of a replication topology. If the user database is
not part of a replication topology, you can back it up from an instance running
any release of SQL Server 2000 or MSDE 2000, and restore it on an instance
running any other release of SQL Server 2000 or MSDE 2000. There are additional
considerations for replication that are detailed in Section 5.3.6
Backup and Restore Issues for Merge
Replication.
SP3a Setup upgrades user databases that are members of
a replication topology. Before installing SP3a, make sure that replication
databases and filegroups are writable and that the user account that is running
Setup has permission to access the databases. For more information about
applying SP3a to databases that are included in replication topologies, see
Section 3.11 Installing on Replicated
Servers.
If SP3a Setup detects user databases or filegroups
that are not writable, it:
- Applies the SP3a replication updates to all writable user
databases.
- Writes a list of the non-writable databases to the Setup
log, which is located at Winnt\Sqlsp.log.
- Displays the following warning message:
Setup has
detected one or more databases and filegroups which are not writable.
You can ignore this warning unless some of the
databases listed in the Setup log are members of a replication topology. If any
of the non-writable databases listed in the Setup log are members of a
replication topology, you must make those databases writable and reapply SP3a
Setup to that instance of SQL Server 2000.
Note This message does not affect unattended installations. For more
information about unattended installations, see Section 4.1
Unattended
Installations.
Because non-writeable databases no longer
cause Setup to fail, you do not have to remove log shipping before upgrading to
SP3a. However, if the database is shipping logs to a database that is a
replication publisher, you must:
If you apply SP3a without having taken offline all
non-writeable databases shipping logs to publication databases, you will
receive this error:
Error Running Script sp_vpupgrade_replication
(1)
If you receive this error, follow the procedure above.
Note During installation, Setup makes no distinction between read-only
databases and databases that are offline or in a suspect state. If a
replication database or filegroup is in any of these conditions during setup
and is involved in a replication topology, you must reapply the service pack
after making the database writable.
Note Because non-writable databases no longer cause Setup to fail, you
do not have to remove log shipping before upgrading to SP3a.
Preparing for a Systems Management Server Distributed InstallationYou cannot install SQL Server 2000 Service Pack 3a
remotely. However, you can use Microsoft Systems Management Server to install
SP3a automatically on multiple computers running Windows NT Server 4.0. To do
so, you must use a package definition file (Smssql2ksp3.pdf) that automates the
creation of a SQL Server package in Systems Management Server. The SQL Server
package can then be distributed and installed on computers that are running
Systems Management Server. The file Sms2kdef.bat is a batch file that starts an
unattended setup using Systems Management Server. In this type of installation,
the Setup program automatically detects relevant system information that it
needs and no user input is required.
Note You cannot use Systems Management Server to install Desktop Engine
SP3a.
3.1 Back Up Your SQL Server Databases
The following information applies to all component
installations except database client components.
Before installing
either Database Components SP3a or Desktop Engine SP3a, back up the
master, msdb, and model databases. Installing SP3a
modifies the master, msdb, and model
databases, making them incompatible with pre-SP3 versions of SQL
Server. These backups are required if you decide to reinstall SQL Server 2000
without SP3 or SP3a.
It is also prudent to back up your user
databases, although SP3a performs updates only on user databases that are
members of replication topologies.
3.2 Back Up Your Analysis Services Databases and Repository
The following information applies only to Analysis
Services.
Before installing Analysis Services SP3a, back up your
Analysis Services databases by making a copy of the Microsoft Analysis
Services\Data folder, which is installed by default under the C:\Program Files
folder. If you have not migrated your Analysis Services repository to SQL
Server, make a backup copy of the file Msmdrep.mdb, which is located in the
Microsoft Analysis Services\Bin folder. You should also save your Analysis
server registry entries by running Regedit.exe and by using
the
Export Registry File item on the
Registry
menu to export the key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAPServer
to a file for backup. If you have migrated your Analysis Services
repository to SQL Server, back up the database that contains the repository
before installing SP3a. For more information, see
Uninstalling SQL Server 2000 Analysis
Services SP3a.
3.3 Make Sure the System Databases Have Enough Free Space
The following information applies to all component
installations except database client components and Analysis Services.
If the autogrow option is not selected for the master and msdb
databases, the databases must have at least 500 kilobytes (KB) of free space.
To verify that they have this space, run the sp_spaceused system stored
procedure for the master or msdb database. If the unallocated space in either
database is less than 500 KB, increase the size of the database. For more
information, see "Expanding a Database" in SQL Server Books Online.
If the autogrow option is selected for the master and msdb databases,
and there is sufficient room on the drives, you can skip this step.
To verify that the autogrow option is selected in SQL Server 2000, open SQL
Server Enterprise Manager, right-click the icon for the database, and then
click Properties.Verify that the Automatically grow file check box is selected.
To verify that this option is selected in MSDE 2000, use the osql
command prompt utility to issue the following SQL statements:
- sp_helpdb master
- sp_helpdb msdb
In the output of these statements, verify that the
value of the growth column is not 0.
3.4 Stop Services and Applications Before Running SP3a Setup
The following information applies to all components. You can apply
SP3a without shutting down services.
If you do not shut down
services, you are prompted to reboot the computer when Setup is complete.
Without rebooting, the following services fail to start:
- Microsoft Distributed Transaction Coordinator (MS DTC) and
the Microsoft Search and MSSQLServerOLAPService services. .
- The MSSQLServer and SQLServerAgent services for the
instance being upgraded (for example, MSSQL$NamedInstance).
- Microsoft Component Services, Microsoft Message Queuing,
and Microsoft COM Transaction Integrator.
- All applications, including Control Panel. (optional)
You can apply SP3a without restarting your computer by
stopping these services and applications before applying the
SP3a.
You cannot stop the services in a clustered environment.
For more information, see Section 3.10 Installing on a Failover Cluster.
3.5 Install Database Components SP3a
The following information applies to all component
installations except Desktop Engine and Analysis Services.
Run the
Setup.bat script from one of the following locations:
- The directory on the local computer containing the
extracted service pack files from SQL2KSP3.exe.
- The service pack directory on the SQL Server 2000 SP3a
CD-ROM.
- The directory on the network share containing the
extracted service pack files from SQL2KSP3.exe.
Note In order to install database components from a network share, you
must first do one of the following:
- Map the network share containing the extracted service pack
files to an available drive letter on the local computer, navigate to the
directory on the mapped drive that contains Setup.bat, and then run
Setup.bat.
- Start Setup by running the Setupsql.exe file found in
\X86\Setup, which is a subfolder of the directory on the network share
containing the extracted service pack files.
Setup displays a dialog box that prompts you for
information, such as whether you want to use SQL Server Authentication or
Windows Authentication. If you choose SQL Server Authentication, you must
supply the Setup program with the password for the sa login. If you choose
Windows Authentication, you must be running the Setup program while logged on
to Windows using a Windows login account. This login account must be a part of
the
sysadmin fixed server role for the instance of SQL Server
2000 you are upgrading.
The Setup program then performs the following
tasks:
- Displays an Authentication Mode dialog box
if it detects that the installation is using Mixed Mode Authentication with a
blank password for the sa login. Leaving the sa login password
blank provides users with easy administrative access to SQL Server or Desktop
Engine and is not recommended; protect your systems by enforcing an sa password
or by using Windows Authentication. To learn more about this dialog, see
Authentication Mode Dialog Box later in this
section.
- Displays an SA Password Warning dialog box if it detects a
blank password for the sa login. Although you can continue your installation
with a blank password for the sa login by explicitly choosing to ignore the
recommendation and continue Setup, a blank password poses a security risk and
is not recommended. This dialog is displayed regardless of the authentication
mode you use.
Note This password change is made immediately; even if Setup fails,
the password is still changed. - Displays a Backward Compatibility Checklist
dialog box that warns of any backward compatibility issues that you
may encounter when applying the service pack. To learn more about this dialog,
see
Backward Compatibility Checklist Dialog Box later in this
section.
- Displays a dialog box if it detects that any databases or
filegroups are not writable. At this point, if any of these databases or
filegroups are part of a replication topology you can either:
- Stop the setup process, make these objects writable,
and restart Setup.
- Continue with the current SP3a setup process and
reapply SP3a at a later time after making these objects writable.
Note
The previous steps are necessary only when applying SP3a to non-writable
databases or filegroups that are part of a replication topology. For more
information, see Section 3.12 Applying SP3a
to Read-Only Databases or Filegroups.
- Displays an Error Reporting dialog box
with information about how to enable the new error reporting feature that is
included in SP3a. For more information about error reporting, see Section 5.10
Error Reporting.
- Updates MDAC components if necessary. For more
information, see Section 5.5.1
Updates
to Microsoft Data Access Components.
- Replaces existing SQL Server 2000 files with SP3a
files..
- Runs Transact-SQL script files to update system stored
procedures
- Displays an option to reboot the computer in the final
dialog box if Setup determines that a reboot is needed.
Authentication Mode Dialog Box
The Authentication Mode dialog box does not default to
the current settings for the installation. The dialog box defaults
are:
- On computers running Windows 98 or Windows Millennium
Edition, the default setting is Mixed Mode Authentication (the only
authentication mode supported on these operating systems). The dialog box
prompts you to specify a password for the sa login. If you specify a password,
Setup changes the sa login password. If you leave the password blank, Setup
connects without changing the password
- On computers running Windows NT 4.0 or Windows 2000, the
default setting is Windows Authentication. Use the dialog box to switch to
Windows Authentication Mode or Mixed Mode with an sa login password that is not
blank.
Note Before changing the authentication mode or the password for the
sa login, make sure that this change does not affect existing applications. For
example, if you change an instance of SQL Server from using Mixed Mode
Authentication to using only Windows Authentication, existing applications
attempting to connect using SQL Server Authentication cannot connect until the
authentication mode is set to Windows Authentication. Also, if you change the
sa login password, applications or administrative processes using the old
password cannot connect until they are configured to use the new
password.
Important For security reasons, you
should never have a blank password on the sa login.
The Setup
program places a record of the actions it performs in the Sqlsp.log file. This
log file is stored in the Windows directory of the computer on which Setup is
run. If you upgrade multiple instances, only the most recent upgrade is
recorded in this log.
Backward Compatibility Checklist Dialog BoxThe
Backward Compatibility Checklist
dialog box lists backward compatibility issues that you may encounter when
applying the service pack. The backward compatibility issues that appear in the
checklist vary depending on the configuration of the instance of SQL Server
2000 that is being upgraded.
The following backward compatibility
issues may be addressed in this dialog:
- When upgrading from SP2 or earlier, SP3a upgrades Microsoft
Search service and automatically rebuilds the full-text catalogs for all
applications using the service. During the rebuild, full-text functionality may
not be fully available. You must select this check box before continuing Setup.
For more information, see Section 5.1.5 Full-Text Catalogs Are Rebuilt After
Setup Completes.
- Before you apply the service pack, you must upgrade your
SQL Server 2000 master/target server configuration. The enhancements introduced
with SP3a are compatible only with servers that are running SQL Server 2000 SP3
or SP3a. You must select this check box before continuing Setup. For more
information, see Section 5.4.2 Changes to Master/Target Server
Configurations.
- Cross-database ownership chaining is turned off by default
in this service pack. After installation, you can enable cross-database
ownership chaining for individual databases. Selecting this optional check box
enables you to allow cross-database ownership chaining for all databases. For
more information, see Section 5.1.11 Cross-Database Ownership Chaining.
Note Enabling cross-database ownership chaining for all databases is
not recommended.
Note If you had previously enabled cross-database ownership chaining
on a pre-release version of SP3 (before build 8.00.760) you will need to enable
it again when installing SP3a.
3.6 Install Analysis Services SP3a
The following information applies only to Analysis
Services.
To install Analysis Services SP3a, run Setup.exe from
either of the following locations:
- The Msolap\Install subdirectory in the directory containing
the extracted Analysis Services SP3a files from SQL2KASP3.exe.
- The Msolap\Install directory on the SP3a CD-ROM.
Setup then performs the following tasks:
- Opens a Setup dialog box that prompts you for information.
For more information about error reporting, see 5.10 Error Reporting.
- Displays an Error Reporting dialog box with information
about how to enable the new error reporting feature included in
SP3a.
- Copies the necessary files, and completes the
installation
Additional Analysis Services Installation Issues
After installing Analysis Services SP3a you must also upgrade
any computers used for remote administration to SP3 or SP3a. Otherwise, you
receive the following error message when you attempt to connect remotely
through Analysis Manager:
Unable to connect to the registry on the
server (server_name), or you are not a member of the OLAP Administrators group
of this server.
Meta Data Services has added a new dedicated role
named
RepositoryUser, which can be used to access and update
repository information in the
msdb database. The
RepositoryUser role has create, read, update, delete, and
execute permissions on the
msdb repository. The
public
role has been replaced by this new role and no longer has permissions
on this repository. If the following conditions are met, the
OLAP
Administrators group must be added to the
RepositoryUser role so that members of this group can access
the repository after the service pack is applied:
- The repository is in the Meta Data Services format.
- The repository uses the msdb
database.
- The OLAP Administrators group previously
used the public role to access the repository.
Note This change also affects remote servers that access the Meta Data
Services repository on a server that has been upgraded to SP3 or SP3a. Remote
server logins must be added to the RepositoryUser role as well.
Note The OLAP Administrators group must be added to the
RepositoryUser role before you restore a Meta Data Services
repositorythat was backed up prior to upgrading to SP3 or SP3a; otherwise, the
restore operation will fail.
For more information about the
RepositoryUser role, see Section 5.6.3 New RepositoryUser Role for Accessing Repository
Information.
If the Analysis Services Data folder is
located on a computer other than the one on which the Analysis server is
running, you must modify the permissions on the folder after running SP3a
Setup. For more information, see Section 5.2.10 Permissions Must be Modified on a Remote Data Folder.3.7.2 Security Considerations for MSDE 2000 SP3a
The following information applies only to Desktop Engine
(MSDE 2000).
Desktop Engine SP3a contains a complete set of the files
required to install or upgrade instances of SQL Server 2000 Desktop Engine
(MSDE 2000). You can perform all of the MSDE 2000 setup actions with the files
from Desktop Engine SP3a, provided you have a license to install or upgrade an
instance of MSDE 2000. For more information about MSDE 2000 licensing, see Uses
of MSDE 2000.
You may not be able to apply Desktop Engine SP3a to
instances of MSDE 2000 that were installed by the setup utility of an
application.You must contact the application provider for a patch file that can
be used to upgrade these instances of MSDE 2000. For instructions on how to
determine if an instance of MSDE 2000 falls into this category, see section 2.1
Downloading and Extracting Desktop Engine (MSDE 2000) SP3a.
The following
sections provide important background information about the MSDE 2000 SP3a
Setup:
- 3.7.1 Prerequisites for
MSDE 2000 SP3a
- 3.7.2 Security
Considerations for MSDE 2000 SP3a
- 3.7.3 MSDE 2000 Setup
Parameters
The following sections cover the most common scenarios for
either upgrading an existing instance of MSDE to SP3a, or installing a new
instance of MSDE 2000 SP3a:
- 3.7.4 Upgrading Existing
Instances of MSDE 2000 To SP3a
- 3.7.5 Installing a New
Instance of MSDE 2000 SP3a
- 3.7.6 Upgrading MSDE 1.0
to MSDE 2000 SP3a
If you need additional information, the primary source
of documentation for MSDE 2000 SP3a setup is in the SQL Server 2000 Books
Online (Updated - SP3). There were some changes made to MSDE 2000 SP3 and SP3a
that are not fully reflected in the SQL Server 2000 Books Online (Updated -
SP3); those features are covered in this readme file. For more information
about installing SQL Server 2000 Books Online (Updated - SP3), see section
1.6 Updated Books Online Documentation Is
Available.3.7.1 Prerequisites for MSDE 2000 SP3aThe following information applies only to Desktop Engine
(MSDE 2000).
The following requirements apply to SQL Server 2000
Desktop Engine (MSDE 2000) SP3a installations.
When upgrading MSDE
2000, you must separately apply Desktop Engine SP3a to every instance of MSDE
2000.
Note Microsoft does not support configurations where there are
more than 16 instances of the database engine on one computer. This includes
instances of SQL Server 6.5, SQL Server 7.0, SQL Server 2000, MSDE 1.0, and
MSDE 2000.
Always start the MSDE 2000 Setup by running Setup.exe. Do
not start Setup by directly invoking one of the MSDE 2000 .msi files, such as
by double-clicking one of the MSDE 2000 .msi files. You must run Setup from the
command prompt and specify parameters to install or upgrade any instance of
MSDE. For more information on the required parameters, see sections 3.7.2
through 3.7.6.
Problems can occur if you use a Terminal Services
connection to attempt to upgrade an existing instance of MSDE to SP3a, or to
install a new instance of MSDE 2000 SP3a. If problems are encountered, restart
Setup from the local computer.
You must know the instance name of the
instance of MSDE you are installing or upgrading using the MSDE 2000 SP3a
setup. If you are installing or upgrading a named instance of MSDE, you must
use the INSTANCENAME parameter to specify the instance name. If you do not
specify INSTANCENAME, Setup operates on the default instance of MSDE on that
computer. In versions of MSDE 2000 Setup before SP3, users needed to specify
the .msi installation package file used to install or upgrade an instance of
MSDE 2000. In SP3 and later versions of Setup, Setup manages the .msi files and
you do not need to specify the .msi file for either an upgrade or a new
installation.
You can run the MSDE 2000 SP3a Setup from your hard
drive, a network share, or a CD-ROM. When upgrading an instance of MSDE 2000 to
SP3a, you can only run Setup from a network share or a CD-ROM if the original
files used to install the instance of MSDE 2000 are still present in the
location they occupied during the original installation. If the original
installation was from a CD-ROM, MSDE 2000 SP3a setup will request the original
CD-ROM during the upgrade. If the original files are no longer in their
original location, or the original CD-ROM is not available, you must copy the
MSDE 2000 SP3a files to your hard drive and run Setup from there.
If
you create your own CD-ROM for installing SP3a, the volume label on the CD-ROM
must be SQL2KSP3. If you are an independent software vendor (ISV) creating a
CD-ROM for the purpose of distributing Desktop Engine as a component of your
application, the CD-ROM volume label must be identical to the VolumeLabel
property in the Media table of the Windows Installer package (*.msi).
You
can install the upgrade for Desktop Engine from a CD-ROM only if you are using
Windows Installer version 2.0.2600.0 or later.If you need to upgrade Windows
Installer, SP3a includes the files needed to upgrade Windows Installer.
To upgrade Windows Installer:
- Using Windows Explorer, navigate to the \MSDE\MSI folder on
either the SQL Server 2000 SP3a CD, or in the folders that you extracted from
SQL2KDeskSP3.exe.
- Run InstMsi20.exe.
- When prompted, reboot the computer.
3.7.2 Security Considerations for MSDE 2000 SP3a
The following information applies only to Desktop Engine
(MSDE 2000).
The behavior of MSDE 2000 setup has been changed in SP3a
so that the default settings result in a more secure configuration.
Desktop Engine SP3a changes the default behavior of the
DISABLENETWORKPROTOCOLS setup parameter that specifies the configuration of the
network connection support for an instance of MSDE 2000. If no application
running on another computer will connect to your instance of MSDE 2000, the
instance has no need for network support and it is prudent to turn off a
resource that is not being used. SP3a will turn off the network support by
default when installing new instances of MSDE 2000. If you disable the network
support when installing an instance of MSDE 2000 SP3a, you can later
reconfigure the instance to enable the support. For more information about
disabling and restoring network access, see Microsoft Knowledge Base article
814130.
By default, the MSDE 2000 SP3a setup
will not install a new instance of MSDE 2000 unless you use the SAPWD parameter
to specify a strong sa password. By default, the MSDE 2000 SP3a setup will not
upgrade an existing instance of MSDE 2000 unless you have assigned a strong
password to the sa login. You should assign a strong password to the sa login,
even when upgrading an existing instance, unless the application using your
instance of MSDE depends in some way on a null sa password. Even if the
instance of MSDE 2000 is running in Windows Authentication mode, the sa login
becomes immediately active if the instance is ever switched to Mixed Mode. A
null, blank, simple, or well-known sa password could be used for unauthorized
access. If you need to assign a strong sa password before upgrading your
instance of MSDE 2000 to SP3a, see Microsoft Knowledge Base article
322336.
Whenever possible, for greater security, use Windows
Authentication with your MSDE 2000 installation. Consider switching from Mixed
Mode to Windows Authentication Mode if:
- The applications using MSDE 2000 can run using Windows
Authentication.
- You have defined Windows Authentication logins for all
users who need to connect to the instance. For more information about adding
logins, see Adding a Windows User or Group in the SQL Server
2000 Books Online.
- The instance of MSDE 2000 is not running on Windows 98 or
Windows ME.
For more information on changing an instance of MSDE 2000
from Mixed Mode to Windows Authentication Mode, see Microsoft Knowledge Base
article 322336. 3.7.3 MSDE 2000 Setup ParametersThe following information applies only to Desktop Engine
(MSDE 2000).
MSDE 2000 is designed to be distributed with
applications and installed by the setup program of the application. MSDE 2000
does not have an interactive setup program. The setup mechanisms for MSDE 2000
are designed to be called by an application setup utility, where any required
interaction with the end-user is handled by the application setup. MSDE 2000
has two installation mechanisms:
- A command prompt Setup utility. The setup utility is
usually called by an application setup utility, but can also be run from a
command prompt window. The MSDE 2000 setup utility does not have a graphical
user interface through which a user can control the behavior of the utility.
Instead, this utility accepts a set of parameters that define what actions the
utility should take.
- A set of Windows Installer merge modules. Developers who
write Windows Installer-based setup utilities can code their application setup
to install an instance MSDE 2000 by consuming the MSDE 2000 merge modules.
Developers can control the configuration of the installed instance by
specifying installation package properties that correspond to the MSDE 2000
setup utility parameters.
Users can use the command prompt MSDE 2000 Setup utility to
either upgrade existing instances of MSDE or to install new instances of MSDE
2000. Users control the behavior of the MSDE 2000 Setup program by specifying
parameters. The setup parameters can be specified in one of two ways:
- On the command line.
- In an .ini file read by the Setup utility.
Most of the parameters supported by the MSDE 2000 SP3a
version of Setup are documented in the topic "Customizing Desktop Engine
Setup.exe" in SQL Server 2000 Books Online (Updated - SP3). For more
information about installing SQL Server 2000 Books Online, see section 1.6
Updated Books Online Documentation Is
Available.
You must enclose the values for MSDE Setup
parameters in double quotation marks if the value specified has special
characters, such as blanks. Otherwise the quotation marks are optional.
These MSDE 2000 SP3a Setup parameters are either not documented in
the SQL Server 2000 Books Online (Updated - SP3), or behave differently than
described in the Books Online.
| Parameter name | Description |
| ALLOWXDBCHAINING=1 | Enables cross-database ownership
chaining. For more information, see Section 5.1.11 Cross-Database Ownership
Chaining. |
| SAPWD=sa_password | Specifies the password to be
assigned to the sa login when installing a new instance of MSDE 2000. SAPWD is
ignored when you upgrade an existing instance of MSDE 2000, so you should
ensure the sa login has a strong password before upgrading. You should always
specify a strong sa password, even when using Windows Authentication Mode.
While the SAPWD property not written to the installation log file when running
Setup.exe, it is if you install using merge modules. |
| /upgradesp { SQLRUN | [<MSIPath>]SqlRunXX.msi }
| Specifies that setup will upgrade an existing instance of MSDE 2000 to
SP3a. For SP3 and later, this switch replaces the /p switch supported by
earlier versions of Setup. Do not use the /p switch with SP3 or later. When
upgrading to SP3a, you are no longer required to specify the .msi file used to
install the existing instance of MSDE 2000. Most users simply specify SQLRUN,
in which case the MSDE 2000 SP3a setup utility determines which .msi file to
use. When you specify SQLRUN without specifying an INSTANCENAME, setup will
upgrade the default instance of MSDE 2000. If you specify both SQLRUN and an
INSTANCENAME, setup will upgrade the instance you specified using the
INSTANCENAME parameter. When you specify the name of an MSDE 2000 .msi
installation package file, setup will upgrade whichever instance on the
computer was originally installed with a merge module of the same name. For
example, if you specify SqlRun01.msi, setup will upgrade whichever instance of
MSDE 2000 was originally installed using SqlRun01.msi. MSIPath is the path to
the folder holding the .msi file. MSIPath defaults to Setup\. For examples of
specifying /upgradesp, see section 3.7.4 Upgrading Existing Instances of MSDE
2000 To SP3a |
| UPGRADEUSER=AnAdminLogin | Specifies the login to be
used when you upgrade an instance of either MSDE 1.0 or MSDE 2000 using SQL
Server Authentication. The login must be a member of the sysadmin fixed server
role. This parameter is only used when you specify SECURITYMODE=SQL when
upgrading an instance of MSDE. |
| UPGRADEPWD= AdminPassword | Specifies the password for
the login specified in UPGRADEUSER when you upgrade Desktop Engine using SQL
Server Authentication. |
Desktop Engine SP3 introduced a new
DISABLENETWORKPROTOCOLS parameter. The behavior of
DISABLENETWORKPROTOCOLS changed in the Desktop Engine SP3a to
be more secure by default.
These are the behaviors of
DISABLENETWORKPROTOCOLS in SP3a.
| DISABLENETWORKPROTOCOLS Value | Upgrading Existing
Instance | Installing New Instance |
| 1 | Instance is configured with all server
Net-Libraries disabled. | Instance is configured with all server
Net-Libraries disabled. |
| 0 | The existing server Net-Library configuration is
retained. | Instance is configured with default server Net-Libraries and
addresses enabled. |
| Parameter not specified, or is any value other than 0 or
1 | The existing server Net-Library configuration is
retained. | Instance is configured with all server Net-Libraries
disabled. |
In SP3, DISABLENETWORKPROTOCOLS has two differences in
behavior compared to SP3a:
- When installing a new instance using SP3, and
DISABLENETWORKPROTOCOLS is either not specified or set to a value other than 0
or 1, then the instance is installed with the default Net-Libraries and
addresses enabled. In SP3a, the Net-Libraries are disabled.
- Whenever all Net-Libraries are disabled for an instance of
MSDE 2000 SP3, the instance will still use UDP port 1434. In SP3a, the instance
will not use UDP port 1434 in that configuration. For more information, see
section 5.1.15 Operations On UDP Port
1434.
For more information about default Net-Library
configurations, see the topic "Controlling Net-Libraries and Communications
Addresses" in SQL Server 2000 Books Online.
If you are a developer
incorporating the MSDE 2000 merge modules (.msi files) into a Windows Installer
setup, you can specify the setup parameters above as properties in the .msi
file. Desktop Engine (MSDE 2000) SP3a provides merge modules to support
existing applications that use merge modules. The Setup utilities for new
applications should be written to call the MSDE 2000 Setup utility instead of
directly consuming the MSDE 2000 merge modules.
| Parameter name | Merge Module Property |
| ALLOWXDBCHAINING | SqlAllowXDBChaining |
| DISABLENETWORKPROTOCOLS | SqlDisableNetworkProtocols |
| SAPWD | SqlSaPwd |
| UPGRADEUSER | SqlUpgradeUser |
| UPGRADEPWD | SqlUpgradePwd |
For information about the merge module properties that
correspond to other Setup parameters, see the topic "Using the SQL Server
Desktop Engine Merge Modules" in SQL Server 2000 Books Online.
Requesting a Setup Log If you encounter difficulties running setup and need a
verbose log to assist in debugging, specify /L*v <LogFileName>.
<LogFileName> is the name of a log file where setup will record all of
its actions. If you do not specify a path as part of the name, the log file is
created in the current folder. If you are executing setup from the SQL Server
2000 SP3a CD, you must specify the full path to a folder on your disk. This
example creates a log file MSDELog.log in root folder of the C: drive:
setup /upgradesp sqlrun DISABLENETWORKPROTOCOLS=1 /L*v C:/MSDELog.log
3.7.4 Upgrading Existing Instances of MSDE 2000 To SP3aThe following information applies only to Desktop Engine
(MSDE 2000).
The examples in this section will upgrade an existing
instance of MSDE 2000 to SP3a, and also disable the network connectivity for
that instance of MSDE 2000. If the instance must accept connections from
applications running on other computers, do not specify the
DISABLENETWORKPROTOCOLS parameter.
The examples in this
section assume that the sa login has a strong password. For more information
about the sa login password, see section 3.7.2
Security Considerations for MSDE 2000
SP3a.
To upgrade SQL Server 2000 Desktop Engine- Open a command prompt window.
- From the command prompt, use the cd command to navigate to
the folder containing the MSDE 2000 SP3a setup utility:
cd
c:\MSDESP3aFolder\MSDE
where c:\MSDESP3aFolder is either the path to the
folder into which you extracted the MSDE 2000 SP3a files, or the Desktop Engine
SP3a folder on the SQL Server 2000 SP3a CD. - Execute one of the following commands:
- For a default instance using Windows Authentication
Mode, execute:
setup /upgradesp sqlrun
DISABLENETWORKPROTOCOLS=1 - For a named instance using Windows Authentication
Mode, execute:
setup /upgradesp sqlrun INSTANCENAME=InstanceName
DISABLENETWORKPROTOCOLS=1 - For a default instance using Mixed Mode (where
AnAdminLogin is a member of the sysadmin fixed server role),
execute:
setup /upgradesp sqlrun SECURITYMODE=SQL UPGRADEUSER=AnAdminLogin
UPGRADEPWD=AdminPassword DISABLENETWORKPROTOCOLS=1 - For a named instance using Mixed Mode (where
AnAdminLogin is a member of the sysadmin fixed server role),
execute:
setup /upgradesp sqlrun INSTANCENAME= InstanceName
SECURITYMODE=SQL UPGRADEUSER=AnAdminLogin UPGRADEPWD=AdminPassword
DISABLENETWORKPROTOCOLS=1
Important If you use an .ini file during
setup, avoid storing credentials in the .ini file.
Note When upgrading Desktop Engine on a computer running Windows 98 or
Windows Millennium Edition, the instance of Desktop Engine that you are
upgrading must be stopped before starting Setup. 3.7.5 Installing a New Instance of MSDE 2000 SP3aThe following information applies only to Desktop Engine
(MSDE 2000).
The examples in this section will install a new instance
of MSDE 2000 SP3a that has been configured with its network connectivity
disabled. If the instance must accept connections from applications running on
other computers, also specify
DISABLENETWORKPROTOCOLS=0.
These examples install instances using the defaults for all
configuration items such as collation and file locations. The configurations
can be controlled by setup parameters, such as
COLLATION, DATADIR,
and
TARGETDIR. For more information about the
configuration parameters that you can specify with setup, see "Customizing
Desktop Engine Setup.exe" in the SQL Server 2000 Books Online (Updated - SP3).
For more information about installing SQL Server 2000 Books Online (Updated -
SP3), see section 1.6 Updated Books Online
Documentation Is Available.
To install a new instance of Desktop Engine - Open a command prompt window.
- From the command prompt, use the cd command to navigate to
the folder containing the MSDE 2000 SP3a setup utility:
cd
c:\MSDESP3aFolder\MSDE - where c:\MSDESP3aFolder is either the path to the folder
into which you extracted the MSDE 2000 SP3a files, or the Desktop Engine SP3a
folder on the SQL Server 2000 SP3a CD.
- Execute one of the following commands:
To install a default instance configured to use Windows Authentication Mode, execute:
setup SAPWD="AStrongSAPwd"
Where AStrongSAPwd is a strong password to be assigned to the sa login.
To install a named instance configured to use Windows Authentication Mode, execute:
setup INSTANCENAME="InstanceName" SAPWD="AStrongSAPwd"
Where AStrongSAPwd is a strong password to be assigned to the sa login and InstanceName is the name to be assigned to the instance.
To install a default instance configured to use Mixed Mode, execute:
setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL
Where AStrongSAPwd is a strong password to be assigned to the sa login.
To install a named instance configured to use Mixed Mode, execute:
setup INSTANCENAME="InstanceName" SECURITYMODE=SQL SAPWD="AStrongSAPwd"
Where AStrongSAPwd is a strong password to be assigned to the sa login and InstanceName is the name to be assigned to the instance.
Important If you are using an .ini file
during setup, avoid storing credentials in the .ini file.
Important Always specify a strong password for the sa login, even when
the instance is configured to use Windows Authentication Mode.
3.7.6 Upgrading MSDE 1.0 to MSDE 2000 SP3aThe following information applies only to Desktop Engine
(MSDE 2000).
The examples in this section will upgrade an existing
instance of MSDE 1.0 to MSDE 2000 SP3a, and also disable the network
connectivity for the instance. If the instance must accept connections from
applications running on other computers, do not specify the
DISABLENETWORKPROTOCOLS parameter.
MSDE 1.0 operates
in the same fashion as a default instance of MSDE 2000, and is always upgraded
to a default instance of MSDE 2000.
To upgrade from Desktop Engine version 1.0
- Open a command prompt window.
- From the command prompt, use the cd command to navigate to
the folder containing the MSDE 2000 SP3a setup utility:
cd
c:\MSDESP3aFolder\MSDE - where c:\MSDESP3aFolder is either the path to the folder
into which you extracted the MSDE 2000 SP3a files, or the Desktop Engine SP3a
folder on the SQL Server 2000 SP3a CD.
Execute one of the following
commands:
- When using Windows Authentication Mode, execute:
setup UPGRADE=1 DISABLENETWORKPROTOCOLS=1 - When using Mixed Mode (where AnAdminLogin is a member
of the sysadmin fixed server role), execute:
setup UPGRADE=1
SECURITYMODE=SQL UPGRADEUSER=AnAdminLogin UPGRADEPWD=AdminPassword
DISABLENETWORKPROTOCOLS=1
Note If you use
BLANKSAPWD=1, you are not required to
specify
SECURITYMODE=SQL or UPGRADEUSER and
UPGRADEPWD. Caution The use of
blank passwords is strongly discouraged.
Important
If you use an .ini file during setup, avoid storing credentials in the .ini
file. 3.7.7 Redistributing MSDE 2000 SP3aThe following information applies only to Desktop Engine
(MSDE 2000).
Desktop Engine SP3a contains all of the files necessary
to redistribute MSDE 2000 with applications, provided the application vendor
has a license to distribute MSDE 2000. You can distribute the Desktop Engine
SP3a files as documented in the SQL Server 2000 Books Online (Updated SP3),
with changes specific to SP3a covered in these sections:
- 3.7.2 Security
Considerations for MSDE 2000 SP3a
- 3.7.3 MSDE 2000 Setup
Parameters
For more information about installing SQL Server 2000
Books Online (Updated - SP3), see section 1.6 Updated Books Online Documentation Is
Available.
Distributing Desktop Engine SP3a PatchesIf an application has a Windows Installer-based setup
utility, it can install an instance of MSDE 2000 by consuming the MSDE 2000
merge modules. Desktop Engine (MSDE 2000) SP3a provides merge modules to
support existing applications that use merge modules. The Setup utilities for
new applications should be written to call the MSDE 2000 Setup utility instead
of directly consuming the MSDE 2000 merge modules.
Vendors who choose
to install instances of MSDE 2000 using the MSDE 2000 merge modules must also
supply all subsequent MSDE 2000 patches to their customers. Instances of MSDE
2000 installed using merge modules are marked with a product code GUID
associated with the application by Windows Installer. Only patch files that
also contain the application product code GUID can patch those instances of
MSDE 2000. Only patch files produced by the application vendor will contain the
proper product codes. The Desktop Engine (MSDE 2000) service packs supplied by
Microsoft cannot be applied to those instances. The application vendor must
build patch files using the Desktop Engine SP3a files and distribute those
patch files to any of their MSDE customers who need the fixes in
SP3a.
For more information about creating patch packages, see
the documentation included with the Windows Installer Software Development Kit
(SDK), which can be downloaded from
the Microsoft Platform SDK Web site.
If an
application setup utility installs an instance of MSDE 2000 by calling the MSDE
2000 setup utility, the instance of MSDE 2000 is marked with the MSDE 2000
product code GUID. Customers can patch these instances using the standard
Microsoft MSDE 2000 service pack files. The application vendor can choose one
of the following alternatives for distributing MSDE 2000 SP3a:
- Instruct their customers to install Desktop Engine SP3a
from either the SQL Server 2000 SP3a download page or a SQL Server 2000 SP3a
CD.
- Download the SQL2KDeskSP3.exe download file and distribute
that to their customers with instructions on how to apply the service
pack.
- Using the Desktop Engine SP3a files, build a service pack
application utility that will install SP3a on the instances of MSDE 2000
installed by the application.
Application vendors who elect to instruct their
customers to apply SP3a directly should develop a set of instructions
customized to the needs of their customers.
For examples of the types
of MSDE 2000 service pack instructions Microsoft application teams prepared for
their customers, see this
Microsoft MSDE
Applications Web page.3.7.8 MSDE 2000 SP3a FilesThe following information applies only to Desktop Engine
(MSDE 2000).
All Desktop Engine SP3a installation files and folders
are located in the \MSDE folder:
- On the SQL Server 2000 SP3a CD-ROM
- In the directory containing the extracted service pack
files from SQL2KDeskSP3.exe The \MSDE folder holds the SP3readme.htm file, the
readme.txt file, the license.txt file, and the executable files for the Setup
utility. It also has the following subfolders:
- \Msi: contains the executable files needed to install
Windows Installer, or upgrade Windows Installer if it is earlier than
2.0.2600.0.
- \MSM: Contains the merge modules needed for a merge module
setup.
- \Setup: Contains the .msi installation package files
required by the MSDE 2000 setup to install a new instance of MSDE 2000 SP3a, or
the msp patch package files to upgrade existing instances of MSDE 2000 to SP3a,
and the sqlrun.cab cabinet file containing the files installed by
Setup.
For a description of using the Desktop Engine
merge modules, see the topic "Using SQL Server Desktop Engine Merge Modules" in
SQL Server Books Online.
If your application setup calls MSDE 2000
Setup, build a folder with this structure and sets of files (where
MSDEInstallFolder represents an example folder name):
MSDEInstallFolder
Copy in these files from the Desktop
Engine SP3a \MSDE folder: Setup.exe, Setup.ini, Setup.rll, and
sqlresld.dll.
MSDEInstallFolder\Msi
Copy in all of the
files from the Desktop Engine SP3a \MSDE\Msi folder.
MSDEInstallFolder\Setup
Copy in all of the files from the
Desktop Engine SP3a \MSDE\Setup folder.
You can then execute
Setup.exe to install or upgrade instances of MSDE 2000 SP3a.
If
Windows Installer has not been installed on the computer, or is a version
earlier than the version tested with MSDE 2000 SP3a Setup, Setup will use the
files in the MSDEInstallFolder\Msi folder to upgrade Windows Installer.
3.8 Restart Services
The following information applies to all components.
When Setup completes, it may prompt you to restart the system. After
the system restarts (or after Setup completes without requesting a restart),
use the Services application in Control Panel to make sure that any services
you stopped before applying the service pack are now running. This includes
services such as MS DTC and the Microsoft Search, MSSQLServer,
MSSQLServerOLAPService, and SQLServerAgent services or their instance-specific
equivalents.
It is prudent to also back up the upgraded
master and
msdb databases at this time.
3.9 Restart Applications
The following information applies to all components.
Restart the applications you closed before running the service pack
Setup program.
3.10 Installing on a Failover Cluster
The following information applies only to SQL Server
2000 components that are part of a failover cluster.
To install the service pack on a failover cluster
- If any resources have been added with dependencies on SQL
Server resources, those dependencies must either be removed or taken offline
before you install SP3a.
- Run the service pack from the node that owns the group
containing the virtual server that you are going to upgrade. If you do not do
this, the installation of SP3a may cause those dependent resources to fail
over.This installs the service pack files on all nodes in the failover
cluster.
- In the Setup dialog box, type the name of the virtual
server that you are upgrading.
- Keep all nodes of the cluster online during setup. This
ensures that the upgrade is applied to each node of the cluster.
- If you removed dependencies or took resources offline in
Step 1 above, add back the dependencies or bring the resources back
online.
Note Setup might require rebooting of the failover cluster nodes. This
replaces the files that were in use during setup with the updated files.
If you are upgrading a default (non-clustered) instance of SQL Server
to a virtual server, first, you must upgrade the default (non-clustered)
instance to a virtual instance, and then apply SP3a.For more information about
upgrading, see "How to upgrade from a default instance to a default clustered
instance of SQL Server 2000 (Setup)" in SQL Server Books Online.
For
additional information on installing SP3a on a failover cluster, see Knowledge
Base article
811168.
If you need to rebuild a node in the failover cluster, perform the following steps
- Rebuild the node in the failover cluster. For more
information about rebuilding a node, see "How to recover from failover cluster
failure in Scenario 1" in SQL Server Books Online.
- Run the original SQL Server 2000 Setup program to add the
node back to the failover cluster.
- Run SP3a Setup on the newly added node. This will update to
SP3a only the binaries on the new node.
Note If you run Setup from the node where the
virtual server is running, you must reapply SP3a to all the nodes. You must
also rerun the database upgrade scripts.
When installing Analysis
Services SP3a on a cluster, each instance must be upgraded
separately.
To install SP3a on an Analysis Services cluster- Install SP3a on a failover node.
- Fail over to the newly upgraded node.
- Repeat steps 1 and 2 until all instances in the cluster are
upgraded.
3.11 Installing on Replicated Servers
The following information applies only to SQL Server
2000 components that are part of a replication topology:
- You must upgrade the Distributor before you upgrade the
Publisher.
- For replication topologies based on transactional
replication with read-only Subscribers, you can upgrade the Subscriber before
or after the Publisher and Distributor.
- For replication topologies based on merge replication or
transactional replication with updating Subscribers, you must upgrade the
Subscriber after the Publisher and Distributor.
Note In many cases, especially in merge replication,
the Distributor and Publisher are on the same server and are upgraded at the
same time.
Installing SP3a on a Server that Acts as a Publisher and a Subscriber
For replication topologies based on merge replication or
transactional replication with updating Subscribers that include one or more
servers that act as both a Subscriber and a Publisher (or Distributor), you
might need to quiesce the system (in other words, stop all updates) and upgrade
all servers simultaneously.
Example 1: Topology That Requires
Simultaneous Upgrades
The following table includes servers that both
publish and subscribe to publications that allow updates at the Subscriber. As
noted earlier, you must follow the upgrade order Distributor, Publisher,
Subscriber for topologies that allow updates at the Subscriber.This order
requires you to upgrade Server A first for the merge publication and Server B
first for the transactional publication with updating Subscribers. In this
case, you must quiesce the system and upgrade the servers simultaneously.
| Server A | Server B |
| Publisher/Distributor for merge
replication | Subscriber for merge replication |
| Subscriber for transactional replication with
updating | Publisher/Distributor for transactional replication with
updating |
Example 2: Topology That Allows Sequential Upgrades
.
In this example, you can upgrade Server A first because the
read-only transactional publication allows a Subscriber to be upgraded before
the Publisher/Distributor.
| Server A | Server B |
| Publisher/Distributor for merge
replication | Subscriber for merge replication |
| Subscriber for read-only transactional
replication | Publisher/Distributor for read-only transactional
replication |
Additional Replication Installation Issues
Important Before you upgrade to SP3a, ensure that the Windows account under
which the SQL Server service runs is a member of the sysadmin fixed server
role. You must do this because replication distribution databases are upgraded
under the context of the SQL Server service account. After upgrading to SP3a,
you should remove the Windows account from the
sysadmin role.
If you are using merge replication and the Distributor is located on
another computer or database instance (a remote Distributor), after applying
SP3a you must generate a new snapshot.
SP3a introduces a change in
the requirements for attaching or restoring replication databases. For more
information, see Section 5.3.17 Change to
Requirements for Attaching or Restoring a Replication
Database.
SP3a Setup upgrades user databases that are
members of a replication topology. If any of the databases that are members of
a replication topology are non-writable, to apply SP3a to those databases you
must make them writable, and then reapplySP3a Setup. For more information about
making a database writable, see Section 3.12 Applying SP3a to Read-Only Databases or Filegroups. For
information about reapplying SP3a, see Section 3.14
Reapplying SP3a.
An
existing backup scheme that accounts for replication allows you to restore a
database to a known point after the SP3a upgrade in case of a failure. After
applying SP3a, a log or full database backup is recommended for any user
database that is included in a replication topology. This way, if a replication
database fails, you do not have to reapply SP3a after the database is restored.
3.12 Applying SP3a to Read-Only Databases or Filegroups
The following information applies only to SQL Server 2000
components that are part of a replication topology.
When non-writable
databases or filegroups exist, Setup displays the following message:
Setup has detected one or more databases and filegroups which are not
writable.
In general, you can ignore this warning and setup will
continue. However, if any of the non-writable databases listed in the Setup log
are members of a replication topology, you must make those databases writable
and reapply SP3a Setup to that instance of SQL Server 2000.
Note This message does not affect unattended installations. For more
information about unattended installations, see Section 4.1 Unattended
Installations.
Note During installation, Setup makes no distinction between
non-writable databases and databases that are offline or in a suspect state. If
a database or filegroup is in any of these conditions during setup, you must
reapply the service pack. For more information about bringing a database
online, see the topic "Attaching and Detaching a Database" in SQL Server Books
Online. For more information about diagnosing suspect databases, see the topic
"Server and Database Troubleshooting" in SQL Server Books Online.
To apply SP3a to a read-only database
- Make the read-only database writable using the ALTER
DATABASE statement, as follows:ALTER DATABASE database SET READ_WRITE
.
- Repeat Step 1 for all read-only databases.
- Apply (or reapply) the service pack.
- If required, make the database read-only again using ALTER
DATABASE, as follows:
ALTER DATABASE database SET READ_ONLY
To apply SP3a to a read-only filegroup
- Make the read-only filegroup writable using ALTER
DATABASE, as follows: ALTER DATABASE Database MODIFY FILEGROUP
filegroup_name READWRITE
- Repeat Step 1 for all read-only filegroups.
- Apply (or reapply) the service pack.
- Make the filegroup read-only again using ALTER
DATABASE, as follows:
ALTER DATABASE Database MODIFY FILEGROUP
filegroup_name
READONLY
For more information about ALTER DATABASE, see the "ALTER
DATABASE" reference topic in SQL Server Books Online.For more information about
reapplying SP3a, see Section 3.14 Reapplying
SP3a.
3.13 Uninstalling SP3a
The way in which you remove SQL Server SP3a depends
on the SQL Server 2000 SP3 components that you are removing.
Note
MDAC updates are not uninstalled. For more information, see Section 5.5.1
Updates to Microsoft Data Access
Components.
Uninstalling SQL Server 2000 Database Components and Desktop Engine SP3a
To revert to the pre-SP3a versions of SQL Server 2000
components, you must have back ups of the
master, msdb, and
model databases taken prior to installing SP3a. For more
information, see Section 3.1 Back Up Your SQL
Server Databases.
Note If any of the databases are involved in replication, you must
disable publishing. To disable publishing:
- In SQL Server Enterprise Manager, expand a SQL Server
group, expand a server, right-click the Replication folder,
and then click Configure Publishing, Subscribers, and Distribution.
- Click the Publication Databases
tab.
- Clear the check box for each database that is involved in
replication. This allows the databases to be detached.
To revert to the pre-SP3a version of SQL Server
- Detach all user databases. For more information, see "How
to attach and detach a database (Enterprise Manager)" in SQL Server Books
Online.
- Uninstall SQL Server. In Control Panel,
double-click Add/Remove Programs, and then select the instance
of SQL Server that you want to uninstall.
- Install SQL Server 2000 from the CD-ROM or the location
from which you originally installed SQL Server.
- Apply any service packs and QFE fixes that were installed
prior to SP3a.
- Restore the databases master, msdb, and
model from the last backup that was created before applying
SP3a.This automatically attaches any user databases that were attached at the
time the backup was created, assuming that the location of the data files has
not changed.
- Attach any user databases that were created after the last
backup of the master database.
- Configure replication if necessary.
Warning When you revert to the pre-SP3a version of SQL Server, all
changes made to the databases master, msdb, and model
since applying SP3a are lost
Uninstalling SQL Server 2000 Analysis Services SP3aTo be able to return Analysis Services to its pre-SP3a
state, you must back up the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server registry key prior to installing SP3a. For more information, see
Section 3.2 Back Up Your Analysis Services
Databases and Repository.
Note If you did not backup this registry key, you must follow the
process documented in Microsoft Knowledge Base article
330244.
To revert to the pre-SP3a version of SQL Server
- Uninstall SQL Server 2000 Analysis Services. In
Control Panel, double-click Add/Remove
Programs, click SQL Server 2000 Analysis Services,
and click Remove.
- Reinstall SQL Server 2000 Analysis Services from the CD-ROM
or the location from which you originally installed Analysis
Services.
- Apply any service packs and QFE fixes that were installed
prior to installing SP3a.
- Remove the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server registry key.
- Reinstall the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server registry key from the pre-SP3a backup.
3.14 Reapplying SP3a
The following information applies to all components.
In the following cases, you must reapply SP3a:
- If new SQL Server 2000 components are added after SP3a is
applied.
- If databases or filegroups that are part of a replication
topology and were read-only during the initial application of SP3a are made
writable
To reapply SP3a, follow the steps in Section
3.0 Service Pack Installation.
4.0 Additional Installation Considerations
This section documents additional service pack
installation considerations that apply only in special cases.
4.1 Unattended Installations
Database Components SP3a can be applied in unattended
mode to an instance of SQL Server. The Database Components SP3a CD-ROM contains
.iss files that can be used to perform unattended SP3a setups and other types
of installations. The following files are located in the root directory on the
CD-ROM:
- Sql2kdef.iss is the unattended setup file used to apply
Database Components SP3a to a default instance of SQL Server 2000 running on
Windows 2000 or Windows NT 4.0. The corresponding batch file to use is
Sql2kdef.bat.
- Sql2knm.iss is the setup file used to apply Database
Components SP3a to a named instance of SQL Server 2000 running on Windows 2000
or Windows NT 4.0. You must change the instance name in this .iss file to
identify the instance that is being upgraded.
- Sql2ktls.iss is the setup file used to apply Database
Components SP3a to a tools-only installation. This file can be used for
installation without any modifications.
- Sql2k9x.iss is the setup file used to apply Database
Components SP3a on a computer running Windows Millennium Edition or Windows 98.
You must change the instance name in this .iss file to identify the instance
being upgraded.
- Sql2kcli.iss is the setup file used to apply Database
Components SP3a to a client component installation on a computer running
Windows 95.
- Msolap.iss is the setup file that stores the list of
parameters for running an unattended setup of Analysis Services
SP3a.
For more information about performing unattended
installations of SQL Server 2000, see the topic "Performing an Unattended
Installation" in SQL Server Books Online.
Unattended Installation Considerations
The following considerations relate to unattended
installations:
- Run unattended installations using Windows Authentication
whenever possible.
- If you must run an unattended setup using SQL Server
Authentication; for example if you are running an unattended setup of SP3a on a
computer running Windows 98 or Windows Millennium Edition, you should not
include a password for the sa login in the .iss file or batch file because
these files are not encrypted. Instead, you should supply the password for the
sa login in the command line using the sapwd parameter as follows:
start /wait setupsql.exe -s -sms -f1 C:\sql2knm.iss -sapwd password - If the SQL Server instance being upgraded to SP3a has a
blank password for the sa login account, a password must be provided prior to
running an unattended installation. You should change this password by running
sp_password.
- When running an unattended installation of SP3a, you must
manually set the following setup switches that correspond to the
Backward Compatibility Checklist dialog box or the unattended
installation will fail.
| Unattended setup switch | Description |
| UpgradeMSSearch | This switch is required to address
the required rebuild of full-text catalogs. If Full Text Search is enabled, you
must set this switch to 1. For more information, see Section 5.1.5 Full-Text Catalogs are Rebuilt After Setup
Completes. |
| MSXTSXUpgraded | This switch is required to address the
issue regarding upgrading master/target server configurations. If you are
applying SP3a to a master or target server, you must set this switch to 1. For
more information, see Section 5.4.2 Changes
to Master/Target Server Configurations. |
| EnableCrossDBChaining | (Optional) This switch is used
to enable cross-database ownership chaining. To enable cross-database ownership
chaining, set this switch to 1. For more information, see Section 5.1.11
Cross-Database Ownership
Chaining. |
- During an unattended installation, if Setup detects any
non-writable databases or filegroups, that information is written to the Setup
log and the unattended installation continues. If any of the non-writable
databases listed in the Setup log are members of a replication topology, you
must make those databases writable and reapply SP3a Setup to that instance of
SQL Server 2000. For more information about making a database writable, see
Section 3.12 Applying SP3a to Read-Only Databases or Filegroups. For more
information about reapplying SP3a, see Section 3.14 Reapplying SP3a.
4.2 Redistributing SP3a Data Access Components
Database Components SP3a includes the self-extracting file
Sqlredis.exe. When Sqlredis.exe runs, it:
- Executes the file Mdac_typ.exe from Microsoft Data Access
Components (MDAC) 2.7 Service Pack 1a. This installs the MDAC 2.7 SP1a core
components (if the same or newer version is not detected) and the versions of
the SQL Server and Desktop Engine client connectivity components that are
included with SP3a. For more information, see 5.5.1
Updates to Microsoft Data Access
Components.
- Installs Microsoft Jet ODBC drivers and connectivity
components.
You can redistribute the Sqlredis.exe file under the
terms and conditions noted in the Redist.txt file that accompanies SP3a.
5.0 Documentation Notes
This section covers issues that can occur in addition to
new features that are available when you run SP3a. These issues apply when
running the service pack to upgrade from SQL Server 2000, SQL Server 2000 SP1,
or SQL Server 2000 SP2. This section is not meant to be a description of all of
the fixes provided in SP3a. For a complete list of these fixes, see Microsoft
Knowledge Base article
306908.
The Analysis Services and Meta Data
Services segments in this section do not apply to Desktop-Engine-only
installations.
Any information relevant to SQL Server 2000 Service Pack 3a
that was not available in time to be included in this readme file will be
published in
Microsoft Knowledge Base article 816502.
These Microsoft Knowledge Base articles
are available at the Microsoft Product Support Services Knowledge Base.
5.1 Database and Desktop Engine Enhancements
The following enhancements apply to SQL Server 2000
instances on which Database Components SP3a is installed. They also apply to
MSDE 2000 instances on which Desktop Engine SP3a is installed.
5.1.1 Using Chinese, Japanese, or Korean Characters with Database Components SP3a
Introduced in SP1
If you install Database Components SP3a on a server
running Windows NT 4.0 or Windows 98 and later upgrade to Windows 2000, the
Windows 2000 upgrade replaces certain system files. These system files are
necessary for sorting Chinese, Japanese, or Korean characters. If you use
Chinese, Japanese, or Korean characters in your SQL Server databases, rerun the
version of Sqlredis.exe included with SP3a after you upgrade to Windows 2000.
For more information about running Sqlredis.exe, see Section 4.2 Redistributing SP3a Data Access Components.
Note You do not have to reapply Sqlredis.exe on client computers or on
servers that do not have databases containing Chinese, Japanese, or Korean
characters. 5.1.2 Hash Teams Removed
Introduced in SP1 Hash teams have been removed. Because of certain
enhancements to SQL Server 2000, hash teams no longer produce the performance
benefits they offered in SQL Server 7.0. In addition, removing hash teams makes
SQL Server 2000 more stable.
Therefore, the query optimizer no longer
generates query plans using hash teams.
In rare cases, the removal of
hash teams can cause a query to be processed more slowly. Analyze such queries
to see whether creating more suitable indexes will return query performance to
its previous level. 5.1.3 Affinity Mask Switches Added
Introduced in SP1 Two affinity mask switches have been added to this
service pack.
Affinity Mask I/O SwitchWith this service pack, you can specify which CPUs are
used to run threads for disk I/O operations. This switch must be used in
conjunction with the
affinity mask option. For more
information, see article
298402 in the
Microsoft
Product Support Services Knowledge Base. For instructions on
searching the Knowledge Base, see Section 1.5 Additional Information About SP3a.
Affinity Mask Connection Switch
With this service pack, you can configure systems that are
enabled for Virtual Interface Architecture (VIA) to bind the SQL Server
connections from certain network cards to a processor or a set of processors.
This switch must be used in conjunction with the affinity mask option. For more
information, see article
299641 in the
Microsoft
Product Support Services Knowledge Base.5.1.4 Filtered Indexed View
Introduced in SP2 If you have encountered SQL Server 2000 bug
355069 as documented in Microsoft Knowledge Base Article
306467, this service pack will only prevent future occurrences of unexpected
results because of data modifications. In addition to applying this fix, all
indexes that are based on views with filter conditions must be re-created. For
more information, see
the Microsoft Product Support Services Knowledge
Base.5.1.5 Full-Text Catalogs Are Rebuilt After Setup Completes
Introduced in SP3 All full-text catalogs are rebuilt as part of the
installation of SP3a, except when upgrading from SP3. The rebuild is automatic
and resource-intensive. Queries against full-text catalogs may return partial
results or no results until the rebuild process is complete. After SP3a is
installed, the system event logs contain messages stating that the catalogs
were corrupt, of an older version, and had to be rebuilt.
For
information , see the Knowledge Base article
327217, which also
discusses possible workarounds for keeping full-text search available during
the rebuild process and to avoid an automatic rebuild. 5.1.6 Syntax Changes for sp_change_users_login
Introduced in SP3
When you run
sp_change_users_login with
the
@Action=Auto_Fix argument, you must now specify a
password.
sp_change_users_login assigns the password to any
new login it creates for the user. The following example shows the new
@Password argument.
sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
[ , [ @Password = ] 'password' ]
Use the @Password argument only with @Action=Auto_Fix. The following example shows the new syntax for the sp_change_users_login command when using Auto_Fix. Other examples in SQL Server Books Online remain unchanged.
USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
go
5.1.7 Ad Hoc Access to OLE DB Providers Disabled by Default
Introduced in SP3If the
DisallowAdhocAccess registry option
is not explicitly set, by default, ad hoc access to OLE DB providers is not
allowed. This means ad hoc query syntax, such as OPENDATASOURCE and OPENROWSET,
will not work against remote servers. To allow ad hoc access, you must
explicitly set the
DisallowAdhocAccess option to
0.5.1.8 New SqlServerLike Provider Option
Introduced in SP3To enable more efficient processing of remote queries
that include LIKE predicates, the
SqlServerLike option was
added in SP3. SQL Server 2000 SP3 or later now has two options for sending LIKE
operations to linked servers. If the OLE DB provider for a linked server
supports the SQL Server syntax for the LIKE operator and wildcards, you can
specify the
SqlServerLIKE option to have SQL Server send LIKE
operations using SQL Server syntax. If the OLE DB provider for a linked server
reports that it supports the Entry Level ANSI/ISO SQL-92 syntax or returns the
SQLPROP_ANSILIKE property, SQL Server will send LIKE
operations to the linked server using SQL-92 syntax. For more information
on
SQLPROP_ANSILIKE, see the topic "Programming the
SQLPROPSET_OPTHINTS Property Set" in SQL Server 2000 Books Online.
You
must add a registry key value to enable the SqlServerLIKE option for an OLE DB
provider.
Caution Incorrectly editing the registry can cause serious
problems that may require you to reinstall your operating system. Microsoft
cannot guarantee that problems resulting from editing the registry incorrectly
can be resolved. Before editing the registry, back up any valuable data.
- Open Regedit32.
- Locate the proper registry key:
- For named instances, locate this key:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance
Name>\Providers\<Provider Name> - For default instances, locate this key:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Providers\<Provider
Name>
- In the <Provider Name> key, add a DWORD value named
SqlServerLIKE and set its value to 1.
5.1.9 Expanded Error Messages for Distributed Queries
Introduced in SP3 For distributed queries, SQL Server returns provider
error information in addition to server error information. When a query between
linked servers results in an error, SQL Server checks to see if the provider
supports the
IErrorRecords OLE DB interface. If this interface
is supported, SQL Server calls the
GetErrorInfo function to
get additional error information from the provider and returns this information
to the user as part of the error message. If the
IErrorRecords
interface is not supported, there is no change in SQL Server behavior: SQL
Server returns a generic error.
For example, run the following query
against a server that uses MSDASQL, which does not support
sql_variant:
SELECT * FROM remote2k.dqtable.dbo.sqlvariantnotnull
--Remote2k is a loopback server.
Prior to SP3, SQL Server returned the following error message:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'msdasql' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
After you apply SP3 or later, SQL Server returns the following error message:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'msdasql' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'sql_variant' (compile-time
ordinal 3) of object '"dqtable"."dbo"."sqlvariantnotnull"' was reported
to have a DBCOLUMNFLAGS_ISFIXEDLENGTH of 16 at compile time and 0 at run time].
5.1.10 New Function fn_get_sql Returns SQL Statement
Introduced in SP3SP3 and later includes the new function
fn_get_sql that returns the text of the SQL statement for the
specified SQL handle. In addition, to support this function, three new columns
have been added to the
sysprocesses system table, which are
listed in the following table.
| Column name | Data type | Description |
| sql_handle | binary(20) | Represents the
currently executing batch or object |
| stmt_start | int | Starting offset of the current
SQL statement for the specified sql_handle |
| stmt_end | int | Ending offset of the current SQL
statement for the specified sql_handle. A value of -1 indicates that the
current statement runs to the end of the results returned by the fn_get_sql
function for the specified sql_handle. |
Syntax
fn_get_sql ([ @SqlHandle = ] SqlHandle )
Arguments
[ @SqlHandle = ] SqlHandle
The handle value. SqlHandle is binary(20).
Tables Returned
| Column name | Data type | Description |
| dbid | smallint | Database ID. This value is NULL
for ad hoc SQL statements |
| objectid | Int | ID of the database object. This
value is NULL for ad hoc SQL statements |
| number | smallint | The number of the group, if
the procedures are grouped. This value is 0 for entries that are not procedures
and NULL for ad hoc SQL statements. |
| encrypted | Bit | Indicates whether the object is
encrypted. The value is 0 if the object is not encrypted and 1 if the object is
encrypted. |
| text | Text | Text of the SQL statement. This
value is NULL for encrypted objects. |
Remarks You can get a valid SQL handle from the
sql_handle column of the
sysprocesses system table.
If you pass a handle that no longer exists in cache,
fn_get_sql returns an empty result set. If you pass a handle
that is not valid, the batch stops, and the following error message is
returned:
Server: Msg 569, Level 16, State 1, Procedure
fn_get_sql, Line 12 The handle passed to
fn_get_sql
was invalid.
SQL Server 2000 cannot cache some Transact-SQL
statements, such as bulk copy statements and statements with string literals
larger than 8 KB. You cannot retrieve handles to those statements by using the
fn_get_sql function.
The
text column of the result set is
filtered for text that may contain passwords.
The information
returned by the
fn_get_sql function is similar to the DBCC
INPUTBUFFER command. Use the
fn_get_sql function when the DBCC
INPUTBUFFER cannot be used, for example:
- When events have more than 255 characters. W
- hen you have to return the highest current nesting level of
a stored procedure. For example, there are two stored procedures that are
named sp_1 and sp_2. If sp_1 calls
sp_2 and you get the handle from the sysprocesses system table
while sp_2 is running, the fn_get_sql function returns
information about sp_2. Additionally, the fn_get_sql function
returns the complete text of the stored procedure at the highest current
nesting level
Permissions
Only members of the
sysadmin fixed
server role can run the
fn_get_sql function.
ExamplesDatabase administrators can use the
fn_get_sql function to help diagnose problem processes. After
an administrator identifies a problem server process ID (SPID), the
administrator can retrieve the SQL handle for that SPID, call the
fn_get_sql function with the handle, and use the start and end
offsets to determine the SQL text of the problem SPID. For example:
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@Handle)
5.1.11 Cross-Database Ownership Chaining
Introduced in SP3 This service pack provides new
options for turning cross-database ownership chaining on and off.
When installing Database Components SP3a, the Setup
Backward
Compatibility Checklist dialog displays an option for configuring
cross-database ownership chaining. By default, Setup turns off cross-database
ownership chaining for all user databases. You can enable cross-database
ownership chaining for all databases. For more information, see
Backward Compatibility Checklist dialog box.
When installing Desktop Engine SP3a, you can use the
ALLOWXDBCHAINING setup parameter to enable cross-database ownership chaining
for all databases. For more information, see section 3.7.3
MSDE 2000 Setup Parameters.
Note Enabling cross-database ownership chaining for all databases is
not recommended.
After installation, you can use the following
methods to turn cross-database ownership chaining on and off for all databases
in the instance:
- Use the new Cross DB OwnershipChaining
argument of the sp_configure system stored procedure.
- Use the Allow Cross-Database Ownership Chaining
option on the Security tab of the SQL Server
Properties dialog box in Enterprise Manager.
- Use the
SQLServer.Configuration.ConfigValues Database Management
Objects (DMO) collection. When the SQLServer object references
an instance of SQL Server 2000 SP3 or later, this collection contains a
ConfigValue object named Cross DB Ownership Chaining.
If cross-database ownership chaining is turned off for
the instance, you can configure it for individual databases. Use the following
methods to turn cross-database ownership chaining on and off for a database:
- Use the new db chaining option of the
sp_dboption system stored procedure.
- Use the AllowCross-Database Ownership
Chaining option on the Options tab of the
Database Properties dialog box in Enterprise Manager.
- Use the DBChaining property of the
DBOption2 DMO object.
Note If you previously enabled cross-database
ownership chaining on a pre-release version of SP3 (earlier than build
8.00.[BUILD_NUMBER]), you must enable it again after installing SP3a.
For more information, click the Help button on the Backwards Compatibility
Checklist page when you run Setup, download the updated edition of
SQL Server 2000 Books Online, or
see Knowledge Base article
810474.5.1.12 Enhancement for Trace Flag 1204
Introduced in SP3 Trace flag 1204 returns the type of locks participating
in the deadlock and the current command affected. In SP3 and later, when this
trace flag is on, the deadlock information is automatically written to the
error log 5.1.13 Permissions Change for sp_changedbowner
Introduced in SP3 Only members of the
sysadmin fixed
server role can run the
sp_changedbowner system stored
procedure. 5.1.14 Debugging Functionality Changes
Introduced in SP3 The functionality for debugging stored procedures with
Microsoft Visual Studio 6.0 and older or with SQL Server Query Analyzer prior
to SP3 is turned off by default. Application debugging (stopping at a SQL
Server Transact-SQL breakpoint while debugging a client application) is also
turned off by default. To enable debugging functionality, run
sp_sdidebug, passing the parameter
legacy_on.
To disable debugging, pass
legacy_off to this procedure.
Note Running the
sp_sdidebug
stored procedure on production servers is not recommended.
For more information, see Microsoft Knowledge Base article
328151. This article is available at the Microsoft Product
Support Services Knowledge Base. 5.1.15 Operations On UDP Port 1434
Introduced in SP3a Starting with SQL Server 2000 SP3a, instances of the
SQL Server 2000 database engine and MSDE 2000 that are not configured to
support network communications will stop using User Datagram Protocol (UDP)
port 1434. Instances that are configured to support network communications will
use UDP 1434
An instance upgraded to SP3a will stop using UDP 1434
whenever all of the server Net-Libraries for the instance, except the shared
memory Net-Library, are disabled. The instance will start using port 1434
whenever you enable any of the server Net-Libraries. For information on
disabling or enabling server Net-Libraries, see the topic "SQL Server Network
Utility" in SQL Server 2000 Books Online.
The use of UDP port 1434 by a
computer will not stop until all instances of SQL Server 2000 and MSDE 2000 on
the computer have been upgraded to SP3a and configured to not support network
communications.
Whether UDP port 1434 is open or closed does not depend on
the state of the shared memory Net-Library. The shared memory Net-Library is
used only for local connections, and does not use a network. The shared memory
Net-Library is always active; it cannot be enabled or disabled.
You can
also specify whether the server Net-Libraries are disabled when installing or
upgrading an instance of MSDE 2000. Use either the MSDE 2000 Setup utility
DISABLENETWORKPROTOCOLS parameter, or the MSDE 2000 merge module
SqlDisableNetworkProtocols property. For more information on these options, see
3.7.3 MSDE 2000 Setup
Parameters.
You cannot disable all of the server Net-Libraries
when installing or upgrading instances of the SQL Server 2000 database engine.
5.2 Analysis Services Enhancements
This section discusses enhancements to SQL Server 2000
Analysis Services that are included with SP3a. 5.2.1 Remote Partitions
Introduced in SP1When a remote partition is created on a local server that has
SP1 or a later version installed on it, the remote server must use a domain
user account that has full access permissions to the parent cube on the local
server. Any user account that is a member of the OLAP Administrators group on
the local server has full access permissions.
In addition, if the local
server has SP1 or a later version installed on it, the remote server also must
have SP1 or a later version installed on it so that the local server can create
or administer remote partitions. 5.2.2 Updated Analysis Services Redistributable Client Setup
Introduced in SP1Analysis Services SP1 and later versions include updated
versions of the following client redistributable setup programs:
These files are located in the \Msolap\Install\PTS
path of the service pack installation directory.
Note PTSFull.exe
includes MDAC; PTSLite.exe does not.
Use these updated client setup
programs in your applications to prevent or solve client setup issues that you
may encounter when using Analysis Services and Microsoft Office XP.
Note
When using Analysis Services with Office XP, upgrading the client is highly
recommended. 5.2.3 Support Enabled for Third-Party Data Mining Algorithm Providers
Introduced in SP1Analysis Services SP1 and later versions include support
for the addition of third-party data mining algorithm providers. For more
information about developing a data mining algorithm provider, see the
"Third Party Data Mining Providers" white paper and the
OLE DB for Data Mining Resource Kit, which includes the code for a sample data
mining algorithm provider. 5.2.4 Installing Analysis Services on a Computer with Updated Client Files
Introduced in SP1If you install SQL Server 2000 Analysis Services on a
computer that contains updated client files, such as SQL Server 2000 SP1 or
Office XP, you must apply Analysis Services SP1 or a later version to ensure
that the client works properly and that you can browse through cubes.
5.2.4 Installing Analysis Services on a Computer with Updated Client Files
Introduced in SP1If you install SQL Server 2000 Analysis Services on a
computer that contains updated client files, such as SQL Server 2000 SP1 or
Office XP, you must apply Analysis Services SP1 or a later version to ensure
that the client works properly and that you can browse through cubes.
5.2.5 Increased Limit for OLAP Cubes Referenced by a Virtual Cube
Introduced in SP3Virtual cubes can now reference up to 255 cubes.
However, any virtual cube that references more than 64 cubes is not visible to
any version of Microsoft PivotTable® Service released before SP3.
5.2.6 New DESCRIPTION Keyword
Introduced in SP3Local cubes now support the intrinsic member property
DESCRIPTION for measures and dimensions. The DESCRIPTION keyword, added to the
CREATE CUBE Multidimensional Expressions (MDX) statement, is used to support
the use of the DESCRIPTION intrinsic member property. The following BNF clauses
describe the changes made to the CREATE CUBE statement:
<dimensions def> :: = DIMENSION <dimension name> [<time def>]
[DIMENSION_STRUCTURE <sub_type>] [<hidden def>]
[DESCRIPTION <description expression>]
<options def> <comma> <hierarchy def list>
<measures def> :: = MEASURE <measure name> <measure function def>
[<measure format def>] [<measure type def>] [<hidden def>]
[DESCRIPTION <description expression>] [<comma> <measures def>]
5.2.7 New PivotTable Service Restricted Client Property
Introduced in SP3SP3 introduced a new PivotTable Service connection
string property, Restricted Client. This property can be used to restrict
PivotTable Service from using local cube functionality. Any attempt to use a
statement that involves the creation or use of a local cube, such as the CREATE
CUBE, CREATE GLOBAL CUBE, and CREATE SESSION CUBE statements, raises an error.
Additionally, any statement that involves deep recursion, such as a series of
nested DRILLDOWN statements, raises an error if the statement can potentially
overflow the statement stack maintained by PivotTable Service.
This
property stores a string value. If the value is set to any string value
starting with the characters "Y", "y", "T", or "t", or any string value that
can be converted to a numeric value other than 0, PivotTable Service is
restricted as described earlier. If the value is set to any other string value,
including an empty string (""), or any string value that can be converted to a
numeric value equal to 0, PivotTable Service is unrestricted. The default value
for this property is "0".
Note This property does not restrict the use of
local data mining models.5.2.8 Change in the Safety Options Property
Introduced in SP3Setting the
Safety Options property to
DBPROP_MSMD_SAFETY_OPTIONS_ALLOW_SAFE also prevents usage of the PASSTHROUGH
keyword in local cubes. 5.2.9 Migrate Repository to Meta Data Services Disabled by Default
Introduced in SP3The option to use the Meta Data Services repository
format in the Migrate Repository Wizard has been disabled in SP3 or later, and
using this format is not recommended. However, if this format is required for
business reasons, you can enable the option through the
EnableMigrationToMetaDataServicesFormat registry key.
By
default, this registry key does not exist; it must be manually created at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Server Connection
Info and set to enable repository migration to the Meta Data Services
format. This registry key stores a case-sensitive string value. If the value is
set to
1 or True, repository migration to the Meta Data
Services format is enabled. If the value is set to any other string value, or
if the registry key does not exist, repository migration to the Meta Data
Services format is disabled.
Note If the value of this registry key is
changed, the change takes effect immediately. 5.2.10 Permissions Must be Modified on a Remote Data Folder
Introduced in SP3If the Analysis Services Data folder is located on a
computer other than the one on which the Analysis server is running, you must
modify the permissions on the folder after running SP3a Setup. On the remote
computer, set the permissions on the folder to allow full control access by all
members of the OLAP Administrators group on the computer running the Analysis
server. Over time, as members are added to or removed from the OLAP
Administrators group, be sure to modify the permissions on the remote Data
folder to so that it reflects the current membership of the OLAP Administrators
group. This will allow backup and restore to function properly.
Additionally, after running SP3a Setup, you must allow the account
under which the Analysis server is running full control access to the remote
Data folder.
If you apply SP3a to an instance of Analysis Services
running in a clustered configuration, the domain-level OLAP Administrators
group must be allowed full control access to the remote Data folder. For more
information about creating and using a domain-level OLAP Administrators group,
see support article 308023 available at the Microsoft Product Support Services
Knowledge Base.
5.3 Replication Enhancements
This section discusses enhancements to SQL Server 2000
replication that are included with SP3a. 5.3.1 Transactional Replication UPDATE Custom Stored Procedure
Introduced in SP1During transactional replication setup, custom stored
procedures for insert, delete, and update actions are created in the
subscription database. Regardless of how many columns are affected by an UPDATE
statement, the update custom stored procedure updates all of the columns in the
subscription table. Any column that has not changed is reset to the same values
that existed before the update. Typically, this action causes no problems.
However, if any of these columns are indexed, this resetting can become
expensive.
If you use transactional replication and have several
indexes on the subscription table, and only a few column values change because
of updates, the overhead of maintaining the index can limit performance when
changes are applied at the Subscriber. For example, a subscription database
that is used for reporting purposes may have many more indexes than the
publication database. Dynamically building the UPDATE statement at run time can
improve performance. The update includes only the columns that have changed,
creating an optimal UPDATE string.
This service pack includes a new
stored procedure, sp_scriptdynamicupdproc, which generates a custom stored
procedure that you can use at the Subscriber to dynamically build the UPDATE
statement at run time. However, building the dynamic UPDATE statement at run
time requires extra processing.
sp_scriptdynamicupdprocThe stored procedure sp_scriptdynamicupdproc generates
the CREATE PROCEDURE statement that creates a dynamic update stored procedure.
The UPDATE statement within the custom stored procedure is built dynamically,
based on the MCALL syntax that indicates which columns to change. Use this
stored procedure if the number of indexes on the subscribing table is growing
and the number of columns being changed is small. This stored procedure is run
at the Publisher on the publication database.
Syntax
sp_scriptdynamicupdproc [ @artid =] artid
Arguments
[@artid =] artid
The article ID. artid is an int, with no default.
Result Sets Returns a result set that consists of a single
nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE
statement that is used to create the custom stored procedure.
Remarks
sp_scriptdynamicupdproc is used in
transactional replication. The default MCALL scripting logic includes all
columns within the UPDATE statement and uses a bitmap to determine the columns
that have changed. If a column did not change, the column is set back to
itself, which usually causes no problems. If the column is indexed, extra
processing occurs. By contrast, this stored procedure uses a dynamic approach:
it includes only the columns that have changed, which provides an optimal
UPDATE string. However, extra processing is incurred at run time when the
dynamic UPDATE statement is built. It is recommended that you test both the
dynamic stored procedure approach and the static default approach, and then
choose the optimal solution for your particular needs.
Permissions
Members of the
public role can execute
sp_scriptdynamicupdprocExamples
This example creates an article (with artid set to 1) on
the authors table in the pubs database, and specifies that the UPDATE statement
is the custom stored procedure to execute:
'MCALL sp_mupd_authors'
Generate the custom stored procedures to be executed by
the Distribution Agent at the Subscriber by running the following stored
procedure at the Publisher:
EXEC sp_scriptdynamicupdproc @artid = '1'
The statement returns:
create procedure [sp_mupd_authors]
@c1 varchar(11),@c2 varchar(40),@c3 varchar(20),@c4 char(12),
@c5 varchar(40),@c6 varchar(20),@c7 char(2),@c8 char(5),
@c9 bit,@pkc1 varchar(11),@bitmap binary(2)
as
declare @stmt nvarchar(4000), @spacer nvarchar(1)
select @spacer =N''
select @stmt = N'update [authors] set '
if substring(@bitmap,1,1) & 2 = 2
begin
select @stmt = @stmt + @spacer + N'[au_lname]' + N'=@2'
select @spacer = N','
end
if substring(@bitmap,1,1) & 4 = 4
begin
select @stmt = @stmt + @spacer + N'[au_fname]' + N'=@3'
select @spacer = N','
end
if substring(@bitmap,1,1) & 8 = 8
begin
select @stmt = @stmt + @spacer + N'[phone]' + N'=@4'
select @spacer = N','
end
if substring(@bitmap,1,1) & 16 = 16
begin
select @stmt = @stmt + @spacer + N'[address]' + N'=@5'
select @spacer = N','
end
if substring(@bitmap,1,1) & 32 = 32
begin
select @stmt = @stmt + @spacer + N'[city]' + N'=@6'
select @spacer = N','
end
if substring(@bitmap,1,1) & 64 = 64
begin
select @stmt = @stmt + @spacer + N'[state]' + N'=@7'
select @spacer = N','
end
if substring(@bitmap,1,1) & 128 = 128
begin
select @stmt = @stmt + @spacer + N'[zip]' + N'=@8'
select @spacer = N','
end
if substring(@bitmap,2,1) & 1 = 1
begin
select @stmt = @stmt + @spacer + N'[contract]' + N'=@9'
select @spacer = N','
end
select @stmt = @stmt + N' where [au_id] = @1'
exec sp_executesql @stmt, N' @1 varchar(11),@2 varchar(40),@3 varchar(20),
@4 char(12),@5 varchar(40),@6 varchar(20),@7 char(2),@8 char(5),@9 bit',
@pkc1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
After running this stored procedure, you can use the resulting
script to manually create the stored procedure at the Subscribers.
5.3.2 Transactional Replication UPDATE Statements on Unique Columns
Introduced in SP1In transactional replication, UPDATE statements usually
are replicated as updates. But if the update changes any column that is part of
a unique index, a clustered index, or an expression that is used as a unique
constraint, the update is performed as a DELETE statement followed by an INSERT
statement at the Subscriber. This is done because this type of update can
affect multiple rows and a uniqueness violation can occur if updates are
delivered row by row.
If the update affects only one row, there is no
chance for a uniqueness violation. Therefore, trace flag 8207 has been added to
this service pack to allow updates to any unique column that affect only one
row to be replicated as UPDATE statements. This optimization has been added
specifically for applications that install user-defined UPDATE triggers at the
Subscriber and require these triggers to fire for updates that affect only one
row on a unique column.
To use trace flag 8207, turn it on from the
command prompt (sqlservr.exe -T8207) or at run time using DBCC TRACEON(8207,
-1) before the Log Reader Agent is started.
Important Typically, trace flag 8207 is used with read-only transactional
replication. Do not use this trace flag with updatable subscriptions if the
primary key UPDATE can occur at the Subscriber. 5.3.3 Restrictions Removed from Concurrent Snapshot Processing
Introduced in SP1In SQL Server 2000, concurrent snapshot processing was
not recommended if the publishing table had a unique index that was not the
primary key or the clustering key. If data modifications were made to the
clustering key while a concurrent snapshot was being generated, replication
could fail with a duplicate key error when applying the concurrent snapshot to
a Subscriber. With this service pack, restrictions on using concurrent snapshot
processing are removed. 5.3.4 Transactional Replication Scripting Custom Stored Procedures
Introduced in SP1When setting up nosync subscriptions (that is,
subscriptions that do not receive the initial snapshot), the custom stored
procedures for INSERT, UPDATE, and DELETE statements must be created manually.
Typically, these statements are created at the Subscriber when the initial
snapshot is delivered. A new stored procedure,
sp_scriptpublicationcustomprocs, has been added to generate
scripts for the custom stored procedures at the publication level. This new
functionality may make it easier to set up nosync subscriptions.
sp_scriptpublicationcustomprocsIn a publication in which the option to auto-generate a
custom procedure schema is enabled, the stored procedure
sp_scriptpublicationcustomprocs scripts the custom
INSERT, UPDATE, and DELETE procedures for all table articles.
sp_scriptpublicationcustomprocs is particularly useful
for setting up subscriptions for which the snapshot is applied manually.
Syntax
sp_scriptpublicationcustomprocs [@publication]= publication_name
Arguments
[@publication] = publication_name
The name of the publication. publication_name is a sysname with no default.
Return Code Values
0 (success) or 1 (failure
Result Sets
Returns a result set that consists of a single
nvarchar(4000) column. The result set forms the complete
CREATE PROCEDURE statement that is necessary to create the custom stored
procedure.
Remarks
Custom procedures are not scripted for articles without
the auto-generate custom procedure (0x2) schema option.
PermissionsExecute permission is granted to
public; a procedural security check is performed inside this
stored procedure to restrict access to members of the
sysadmin
fixed server role and
db_owner fixed database role in
current database.
Example
This example generates a script of the custom stored
procedures in a publication named
Northwind..
exec Northwind.dbo.sp_scriptpublicationcustomprocs
@publication = N'Northwind'
5.3.5 Merge Replication Retention-Based Meta Data Clean Up
Introduced in SP1
When merge replication system tables contain large
amounts of meta data, cleaning up the meta data improves performance. Prior to
SQL Server 2000 SP1, meta data could be cleaned up only by running
sp_mergecleanupmetadata. However, SQL Server 2000 SP1 and later versions
include retention-based meta data clean up, which means that meta data can be
automatically deleted from the following system tables:
- MSmerge_contents
- MSmerge_tombstone
- MSmerge_genhistory
- Any before image tables, if present
Note Before image tables are present if the
@keep_partition_changes synchronization optimization option is
enabled on the publication.
Retention-based meta data clean up occurs
as follows:
- If the -MetadataRetentionCleanup Merge
Agent parameter is set to 1, as it is by default, the Merge Agent cleans up the
Subscriber and the Publisher that are involved in the merge.
- If the -MetadataRetentionCleanup parameter is set to 0,
automatic clean up does not occur. In this case, manually initiate
retention-based meta data clean up by executing
sp_mergemetadataretentioncleanup. This stored procedure must
be executed at every Publisher and Subscriber that should be cleaned up. It is
recommended, but not required, that the Publisher and Subscribers be cleaned up
at similar points in time (see
Preventing False Conflicts later in this section).
Note The -MetadataRetentionCleanup parameter is set
to 1 for all Merge Agent profiles that are included with SQL Server 2000 SP1
and later versions. If you upgrade a server to SP1 or later and then add merge
replication, the Merge Agent profile is automatically updated to include this
parameter. If you upgrade a server that already has merge replication enabled
to SP1 or later, the Merge Agent profile is not automatically updated; update
the profile by running sp_add_agent_parameter (see
Additional Parameter for sp_add_agent_parameter later in this
section).
Important The default retention period for publications is 14 days. If an
article belongs to several publications, there might be different retention
periods. In that situation, the longest retention period is used to determine
the earliest possible time that clean up can occur. If there are multiple
publications on a database, and if any one of those publications uses an
infinite publication retention period (@retention=0), merge
meta data for the database is not automatically cleaned up. For this reason,
use infinite publication retention with caution
Additional Parameter for sp_add_agent_parameterThe system stored procedure sp_add_agent_parameter now
has a MetadataRetentionCleanup parameter, which allows you to add or remove
meta data retention clean up from Merge Agent profiles. A value of 1 indicates
that the profile should include clean up; a value of 0 indicates that it should
not include clean up. For example, to add meta data retention clean up to a
profile, execute the following code:
EXEC sp_add_agent_parameter @profile_id=<my_profile_id>,
@parameter_name='MetadataRetentionCleanup', @parameter_value=1
Meta Data Cleanup in Topologies with Different Versions of SQL Server
For automatic retention-based clean up to occur in a
database involved in merge replication, the database and the Merge Agent must
both be on servers running SQL Server 2000 SP1 or later. For example:
- A SQL Server 7.0 pull Subscriber does not run clean up at a
SQL Server 2000 SP1 Publisher.
- A SQL Server 2000 SP1 push Merge Agent does not run clean
up in a pre-SP1 SQL Server 2000 Subscriber database.
- A SQL Server 2000 SP1 push Merge Agent runs clean up in a
SQL Server 2000 SP1 Publisher database, even if it has subscribers that are
running SQL Server 2000 or earlier.
Automatic clean up on some servers and not on others
will at most cause false conflicts, and those should be rare. For topologies
that include versions of SQL Server prior to SQL Server 2000 SP1, you may see
performance benefits by running sp_mergemetadatacleanup on all servers that are
not cleaned up automatically.
Preventing False Conflicts
Retention-based meta data clean up prevents
non-convergence and silent overwrites of changes at other nodes. However, false
conflicts can occur if the following conditions are met:
- The meta data is cleaned up at one node and not another.
- A subsequent update at the cleaned-up node occurs on a row
whose meta data was deleted.
For example, if meta data is cleaned up at the
Publisher but not at the Subscriber, and an update is made at the Publisher, a
conflict occurs even though the data appears to be synchronized.
To
prevent this conflict, make sure that meta data is cleaned up at related nodes
at about the same time. If -MetadataRetentionCleanup is set to 1, both the
Publisher and Subscriber are cleaned up automatically before the merge starts,
thereby ensuring that the nodes are cleaned up at the same time. If a conflict
occurs, use the merge replication conflict viewer to review the conflict and
change the outcome if necessary.
If an article belongs to several
publications or is in a republishing scenario, it is possible that the
retention periods for a given row at the Publisher and Subscriber are
different. To reduce the chance of cleaning up meta data on one side but not
the other, it is recommended that those different publications have similar
retention periods.
Note If the system tables contain large amounts of meta data that must
be cleaned up, the merge process may take longer to run. Clean up the meta data
on a regular basis to prevent this issue. 5.3.6 Backup and Restore Issues for Merge Replication
Introduced in SP1
A publication database that is restored from a backup
should first synchronize with a subscription database that has a global
subscription (that is, a subscription having an assigned priority value) to
guarantee correct convergence behavior. Synchronization ensures that the
changes that were lost at the publication database because of the restore
operation are reapplied accurately.
Do not synchronize the
publication database with a subscription database that has an anonymous
subscription. Because anonymous subscriptions do not have enough meta data to
apply changes to the publication database, such synchronization can lead to the
non-convergence of data.
When you are planning back up and restore
operations for merge replication, consider the following additional issues:
- Restore the subscription databases from backups that are
not beyond the retention period.
Restore a subscription database from a backup only if the backup is no
older than the shortest retention period of all publications to which the
Subscriber subscribes. For example, if a Subscriber subscribes to three
publications with retention periods of 10, 20, and 30 days, respectively, the
backup used to restore the database should not be more than 10 days old.
- Synchronize before generating a backup.
It is
strongly recommended that a Subscriber synchronize with the Publisher before
you perform a backup. Otherwise, the system might not converge correctly if the
Subscriber is restored from this backup. Although the backup file itself might
be new, the last synchronization with a Publisher could be almost as old as the
retention period. For example, consider a publication with a retention period
of 10 days. The last synchronization was 8 days ago, and now the backup is
performed. If the backup is applied 4 days later, the last synchronization will
have occurred 12 days ago, which is past the retention period. If the
Subscriber had synchronized right before the backup, the subscription database
would be within the retention period. - Reinitialize the Subscriber if you change the publication
retention value.
If you need to change the publication retention
value, manually reinitialize the Subscriber to avoid the non-convergence of
data. The retention-based meta data clean up feature deletes outdated meta data
from merge system tables when the publication retention period is reached.
The publication retention value is used to determine when
subscriptions that have not synchronized within the retention period should
expire. If, after a clean up, the publication retention period is increased and
a subscription attempts to merge with the Publisher (which has already deleted
the meta data), the subscription will not expire because of the increased
retention value. Furthermore, the Publisher does not have enough meta data to
download changes to the Subscriber, which leads to non-convergence. - Use the same publication retention values for all
Publishers and their alternate synchronization partners. Using different values
can lead to non-convergence.
5.3.7 Restoring Replicated Databases from Different Versions of SQL Server
Introduced in SP1
Restoring a backup to the same server and database-running
the same version as the server from which the backup was created-preserves your
replication settings. If you are restoring a replicated database to a version
of SQL Server that is different from the version used to back up the database,
consider the following issues:
- If you are restoring the database to SQL Server 2000 SP3a
from a backup created with SQL Server 2000 and you want to preserve replication
settings, you must run sp_vupgrade_replication. Running
sp_vupgrade_replication ensures that the replication meta data
is upgraded. If you do not run sp_vupgrade_replication, the
replication meta data can be left in an unpredictable state.
- If you are restoring the database to SQL Server 2000 from a
backup created with SQL Server 7.0 (the release version, SP1, SP2, SP3, and
SP4) and you want to preserve replication settings, you must re-create the
backup before installing service packs. It is possible to restore the database
to SQL Server 2000 SP3a directly from a backup of a replicated database created
in SQL Server 7.0, but replication settings are not maintained.
5.3.8 New -MaxCmdsInTran Parameter for Log Reader Agent
Introduced in SP1
Starting with SP1, a new command prompt parameter,
-
MaxCmdsInTran, has been added for the Log Reader Agent. For
transactions affecting a large number of commands (typically mass updates or
deletes), the Distribution Agent must wait for the Log Reader Agent to write
the entire transaction to the distribution database before it can start
propagating the transaction to the Subscriber. This delay blocks the
Distribution Agent and reduces the parallelism between the two agents.
By using
-MaxCmdsInTran, the Log Reader Agent breaks
large transactions into smaller chunks, and each chunk contains the same or
fewer commands as the
-MaxCmdsInTran input. Therefore, the
Distribution Agent can start processing earlier chunks of a transaction while
the Log Reader Agent is still working through later chunks of the same
transaction.
This improvement in parallelism between the Log Reader
Agent and the Distribution Agent contributes to better overall replication
throughput. Note, however, that the transaction chunks are committed at the
Subscriber as individual transactions, which breaks the property of atomicity,
one of the ACID (Atomicity, Consistency, Isolation, and Durability) properties.
This outcome is not a problem in most circumstances, though it is recommended
that you test this to make sure.
Defining the -MaxCmdsInTran ParameterSpecify a positive integer (1 or above) for the
-MaxCmdsInTran parameter value. Specifying a value of 0 is equivalent to not
using the parameter. Because this parameter improves performance only when the
transaction is very large, a value of 5000 or greater for this parameter is
typical. For example:
logread.exe -MaxCmdsInTran 10000.
To
use this parameter, the Publisher must be running SQL Server 2000 SP1 or a
later version, and the Log Reader Agent and distribution database must be
upgraded to SP3 or later. Otherwise,
-MaxCmdsInTran is
ignored. 5.3.9 Restriction on Non-unique Clustered Indexes
Introduced in SP2 (applies to Transactional Replication
only)You cannot create a non-unique clustered index on a table
after it is published for transactional replication. Before creating the index
you must first drop any publication that includes the table.
5.3.10 New -MaxNetworkOptimization Command Line Argument for Snapshot Agent
Introduced in SP2During normal processing, merge replication can send
DELETE commands to Subscribers for rows that do not belong to the Subscriber's
partition. DELETE commands of this type are referred to as irrelevant deletes.
Irrelevant deletes do not affect data integrity or convergence, but they can
result in unnecessary network traffic.
To reduce network traffic
caused by irrelevant deletes, you can use the new Snapshot Agent parameter
-
MaxNetworkOptimization with merge replication publications.
Setting the parameter to 1 minimizes the chances of irrelevant deletes, which
maximizes network optimization.
Note Setting this parameter to 1 is
useful only when the synchronization optimization option of the merge
publication is set to true (the
@keep_partition_changes
parameter of
sp_addmergepublication).
The
default is 0 because setting the parameter to 1 can increase the storage of
meta data and cause performance to degrade at the Publisher if multiple levels
of join filters and complex subset filters are present. You should carefully
assess your replication topology and set
-MaxNetworkOptimization to 1 only if network traffic from
irrelevant deletes is unacceptably high.
You can add this parameter
to the Snapshot Agent profile by executing the system procedure
sp_add_agent_parameter as follows:
EXEC
sp_add_agent_parameter 1, 'MaxNetworkOptimization', 1 5.3.11 Merge Replication Uses New Role
Introduced in SP3
SP3 and later automatically creates a new role for use
by merge replication. The name of the new role is in the form
MSmerge-<publication ID>. The role is created on the Publisher
for each merge replication publication and acts as the publication access list
(PAL) to control access to merge publications on the Publisher. If this role is
dropped, you can run a new stored procedure included with SP3 or later,
sp_createmergepalrole, to re-create the role. This stored
procedure is executed at the Publisher on the publication database to re-create
the role.
Syntax
sp_createmergepalrole [ @publication = ] 'publication'
Arguments
[@publication = ] 'publication'
The publication name. publication is sysname, with no default. This parameter is used to select the publication to use when re-creating a role used by merge replication.
Return Code Values
0 (success) or 1 (failure)
Remarks
Running
sp_createmergepalrole adds a
new row to the sysusers table for the new role. The name of this new role is
based on the value of the
pubid column in the
sysmergepublications table for the given publication. The
prefix of the role name is 'MSMerge_' and the
pubid value is
appended (without the hyphens) to the role name.
Permissions
Only members of the sysadmin fixed server role or
db_owner fixed database role can run
sp_createmergepalrole.5.3.12 New Requirements for Subscriptions Created by Non-sysadmin Users
Introduced in SP3
If a subscription is created by a user who is not a
member of the sysadmin fixed server role, you must do one of the following:
- Configure the SQL Server Agent proxy account with a login
and password so that the SQL Server Agent job step associated with the
replication agent runs with sufficient privileges. For more information, see
the topic "xp_sqlagent_proxy_account" in SQL Server Books Online.
- Change the owner of the job step to a user that is a member
of the sysadmin fixed server role before running the agent.
Note The remote agent activation feature always requires the job step
to run in the context of a user account in the sysadmin fixed server role.
5.3.13 Changes to Permissions for Stored Procedures
Introduced in SP3
Permissions have been changed on a number of the stored
procedures used to implement, administer, and monitor a replication topology.
Most of these changes involve a tightening of the permissions that are required
to run the stored procedures. For more information about the new permissions,
review the Transact-SQL reference documentation for the replication stored
procedures in the updated version of SQL Server Books Online. For more
information about the updated SQL Server Books Online, see Section 1.6
Updated Books Online Documentation Is
Available. 5.3.14 New Parameter for sp_addmergearticle and sp_changemergearticle
Introduced in SP3
A new parameter,
@published_in_tran_pub, has been added to both
sp_addmergearticle and
sp_changemergearticle.
This parameter is used to indicate that an article in a merge publication is
also published in a transactional publication.
@published_in_tran_pub is
nvarchar(5), with a
default of
FALSE. TRUE specifies that the article is also
published in a transactional publication.
Note When you change this parameter in sp_changemergearticle, the
snapshot must be invalidated and subscribers must be
reinitialized.5.3.15 New Page for Configure Publishing and Distribution Wizard
Introduced in SP3
The Configure Publishing and Distribution Wizard now
includes a new page: Distributor Password. You must type a password on this
page if you select one or more Publishers to use the server as a remote
Distributor and one or more of those Publishers require a password. The
connection between a Publisher and a remote Distributor is a hybrid of a linked
server and a remote server. The connection uses the login
distributor_admin. By default, the Publisher is configured as
non-trusted at the remote Distributor, so a password is required.
Note If you have downloaded and installed SQL Server 2000 Books Online
(Updated - SP3), this information is available when you click the
Help button for the new page.5.3.16 Changes to Windows Synchronization Manager Support
Introduced in SP3
SQL Server allows you to enable existing subscriptions
(created using SQL Server Enterprise Manager, SQL-DMO, and replication stored
procedures) for use with Windows Synchronization Manager. You can also create
new subscriptions using Windows Synchronization Manager. After you apply the
service pack, when synchronizing a subscription, Windows Synchronization
Manager will prompt you to enter the password or passwords required to connect
to the servers involved in the synchronization.5.3.17 Change to Requirements for Attaching or Restoring a Replication Database
Introduced in SP3
Under a certain set of conditions, replication could
malfunction in the process of attaching or restoring a published database.
These conditions are:
- SP3 or later has been applied.
- The user attaching the database is not a member of the
sysadmin fixed server role.
- Cross-database chaining has not been allowed.
If all of these conditions are true, you should
execute the
sp_changedbowner stored procedure on the attached
or restored database. Assign ownership to the
sa built-in
administrator login. This will ensure that replication functions correctly.
Note You must be a member of the
sysadmin fixed
server role to execute
sp_changedbowner.
For more
information about cross-database ownership chaining, see Section 5.1.11
Cross-Database Ownership
Chaining.
5.4 SQL Server Agent Enhancements
This section discusses enhancements to SQL Server Agent
that are included in SP3a. 5.4.1 SQL Server Agent Logs Account Information
Introduced in SP2The SQL Server Agent Job History now records the Windows
account under which each job step runs. This information helps administrators
diagnose security issues with scheduled jobs, including scheduled jobs that are
defined for replication and Data Transformation Services (DTS)
tasks.5.4.2 Changes to Master/Target Server Configurations
Introduced in SP3
Multiserver administration is the process of automating
administration tasks across multiple instances of SQL Server. Use multiserver
administration if you manage two or more servers and you want to centralize
maintenance tasks.
In SP3a, the SQL Server Agent service account does
not have to be a Windows administrator unless you need to use the SQL Server
Agent Proxy Account. For more information about the SQL Server Agent Proxy
Account, see Section 5.7.3 SQL Server Agent
Proxy Account Improvements. The SQL Server Agent service account
must be a member of the sysadmin fixed server role.
With multiserver
administration, you must have at least one master server and at least one
target server. A master server distributes jobs to and receives events from
target servers. A master server stores the central copy of job definitions for
jobs that run on target servers. Target servers connect periodically to their
master server to update their list of jobs to perform. If a new job exists, the
target server downloads the job and disconnects from the master server. After
the target server completes the job, it reconnects to the master server and
reports the status of the job.
Before you apply SP3a, you must
complete several steps to upgrade your SQL Server 2000 master/target server
configuration. The changes that are introduced with SP3a are not compatible
with SQL Server 7.0 target servers, or with any servers not running SP3a. This
is a change from the original SQL Server 2000 functionality.
To upgrade your master/target server configuration
- Create a new MSX (master server) account on your master
server. This is to prepare TSX (target server) server (or servers) for the
upgrade. To do this, run the following commands.
--Option A: Windows authentication
EXEC sp_grantlogin 'DOMAIN\user'
GO
USE msdb
GO
EXEC sp_adduser 'DOMAIN\user', 'DOMAIN\user', 'TargetServersRole'
GO
--Option B: SQL Server authentication - see explanation below for
--details.
EXEC sp_addlogin <MSXAccount>, <MSXAccountPassword>, 'msdb'
GO
USE msdb
GO
EXEC sp_adduser <MSXAccount>, <MSXAccount>, 'TargetServersRole'
GO
Where <MSXAccount> represents the SQL Login name you choose
and <MSXAccountPassword> represents the associated password.
Note These values must be enclosed in single quotation marks.
You have the following options when choosing an MSX account:
- Windows Authentication. This is the most secure option,
because passwords do not have to be stored, and SQL Server and SQL Server Agent
can be configured without local Windows administrator rights.
- SQL Server Authentication. This requires the SQL Server
Agent service accounts to have local Window administrator rights because SQL
Server stores the user name and password as a local security authority (LSA)
secret, and access is restricted to local Windows administrators. You can
create an account for all TSX servers, or you can create one account for each
TSX server.
Do not specify a SQL Server Agent probe account
(<computer_name>_msx_probe_login). As part of the upgrade to SP3 or
later, SQL Server removes the old probe accounts because the TSX servers no
longer use them. - Upgrade your TSX servers to SP3a one at a time. (Before you
apply the service pack, see Step 3 for more information about timing your
upgrade.)
- To minimize down time, run the extended stored procedure
xp_sqlagent_msx_account on each TSX server shortly after the
SP3a update is complete.
Note After running
xp_sqlagent_msx_account, SQL Agent must be stopped and
restarted on each server.
For more information about
xp_sqlagent_msx_account, see Section 5.4.3 New SQL Server Agent Extended Stored
Procedure. - Apply SP3a to your master server. The old _msx_probe
accounts are removed by SP3a Setup, because the TSX servers no longer use them.
If an account owns SQL Agent jobs, the account is not removed, and you must
change the owner of the jobs to another user and manually remove these
accounts. If you want to continue to use the old _msx_probe accounts that own
SQL Agent jobs, you may have to change the password of the _msx_probe
account.
5.4.3 New SQL Server Agent Extended Stored Procedure
Introduced in SP3
SP3a includes a new extended stored procedure that
allows you to configure the account that the SQL Server Agent TSX server uses
to download instructions from an MSX server. This account is also known as the
MSX account, or master server account.
xp_sqlagent_msx_account
The
xp_sqlagent_msx_account extended
stored procedure sets or retrieves the SQL Server Agent MSX account user name
and password to or from the LSA secrets on the TSX server. Only members of the
securityadmin fixed server role can run this extended stored
procedure.
Before you can execute this extended stored procedure, SQL
Server Agent must be running. In addition, if the account specified is a SQL
Server login, SQL Server Agent must have local Windows administrator rights.
SQL Server Agent stores the user name and password as an LSA secret, and access
is restricted to local Windows administrators.
Syntax
xp_sqlagent_msx_account
{N'GET' |
N'SET' | N'DEL', N'MSX_domain_name', N'MSX_username', N'MSX_password'
}
Arguments
N'GET'
Retrieves the current SQL Server Agent MSX account. N'GET' is
an nvarchar with no default. The password is not reported for security reasons.
N'SET'Sets the account to be used as the SQL Server Agent MSX
account. Use the MSX_username, and MSX_password parameters to specify the
account to use as the SQL Server Agent MSX account. N'SET' is an nvarchar with
no default.
N'DEL'
Deletes the SQL Server Agent MSX account.
'MSX_domain_name'
Reserved for future use.
'MSX_username'
The name of the Windows account to be used
as the SQL Server Agent MSX account. Specify an empty string for this parameter
and MSX_password to select Windows security. In this case, the SQL Server Agent
service account credentials are used to log on to the MSX server. MSX_username
is an nvarchar with no default.
'MSX_password'
The password
for the SQL Server account specified in MSX_username. Specify an empty string
for this parameter and the MSX_username to select Windows security. In this
case, the SQL Server Agent service account credentials are used to log in to
the MSX server. MSX_password is an
nvarchar with no default.
Note Parameters for xp_sqlagent_msx_account must be specified in
order. Named parameters cannot be used.
Return Code Values
Returns a value of
0 on success or
1
on failure
When
xp_sqlagent_msx_account fails and
returns a value of
1, SQL Server generates an error message
with information about the error.
Result SetsIf a SQL Server Agent MSX account has been set,
xp_sqlagent_msx_account returns a result set with the following
information when you specify
N'GET'.
| Column | Data type | Description |
| domain | sysname | N/A. Reserved for future
use. |
| username | sysname | Account used as the SQL
Server Agent MSX account. |
If a SQL Server Agent MSX account has not been set,
or if
N'SET' is specified, no result set is returned.
Permissions
Execute permissions for
xp_sqlagent_msx_account default to members of the
securityadmin fixed server role.
Examples
- Retrieve the currently assigned SQL Server Agent MSX
account
The following example retrieves the account currently
assigned for use as the SQL Server Agent MSX account:
EXEC
master.dbo.xp_sqlagent_msx_account N'GET' - Set the SQL Server Agent MSX Account to use Windows
Authentication
The following example sets the SQL Server Agent MSX
account to use Windows Authentication: EXEC master.dbo.xp_sqlagent_msx_account N'SET',
N'', -- Reserved for future use
N'', -- MSX_username
N'' -- MSX_password
- Set the SQL Server Agent MSX account to SQL Server
Authentication
The following example sets the SQL Server Agent MSX
account to Ralph and specifies a password: EXEC master.dbo.xp_sqlagent_msx_account N'SET',
N'', -- Reserved for future use
N'Ralph', -- MSX_username
N'RalphPwd' -- MSX_password
- Delete the SQL Server Agent MSX account
The
following example deletes the SQL Server Agent MSX account. This means that SQL
Server Agent defaults to Windows integrated security authentication.
EXEC master.dbo.xp_sqlagent_msx_account N'DEL'
5.4.4 SQL Server Agent Permission Checks
Introduced in SP3
SQL Server now checks to ensure that the Agent job owner
has permission to append or overwrite the output log file from each job. This
happens in three ways:
- If the job owner is a member of the sysadmin fixed server
role, the job output log file can be written to the server.
- If the job owner is a Windows user, SQL Server tests to see
if the user has permission to write to the selected job output log file
location on the server.
- If the job owner is a SQL Server user, SQL Server tests the
SQL Server Agent proxy account for permission to write to the selected job
output log file location on the server. If the proxy account has not been set,
no log is written.
In all cases, jobs are written with SQL Server Agent
credentials, but SQL Server now tests to ensure the user has permission to
write to the selected job output log file location on the server. Errors appear
in the job history, but the job steps do not fail if the log file cannot be
written. 5.4.5 SQL Agent Mail MAPI Profiles
Introduced in SP3In the 32-bit version of SQL Server 2000, SQL Agent Mail
can be configured to use an Extended MAPI e-mail profile for sending e-mail
alerts. You can use an Extended MAPI e-mail application, such as Microsoft
Outlook, to create an Extended MAPI profile. In the 64-bit version of SQL
Server 2000, SQL Agent Mail can only use a Simple MAPI profile for sending
e-mail alerts. Do not use Simple MAPI profiles in the 32-bit version of SQL
Server 2000.
5.5 SQL Server Connectivity Components Enhancements
This section discusses enhancements to connectivity
components of SQL Sever 2000 that are included with SP3a. 5.5.1 Updates to Microsoft Data Access Components
Introduced in SP3aSP3a includes updates to the Microsoft Data Access
Components (MDAC). When you install SP3a, MDAC 2.7 Service Pack 1a is also
installed. MDAC 2.7 SP1a has no feature changes from the version of MDAC
installed by SQL Server 2000 (MDAC 2.6), but it does include fixes and security
enhancements. MDAC 2.7 SP1a includes an upgrade to MSXML 3 SP3.
Note This version of MDAC is not installed if Setup detects the same
version or a newer version.
For more information about this version
of MDAC, see the Microsoft Universal Data Access Web site at this page on
microsoft.com.
Fixes included in this version of MDAC will be documented in Knowledge Base
article 326848. You can access this article from the
Microsoft
Product Support Services Knowledge Base. 5.5.2 Support for QLogic Virtual Interface Architecture
Introduced in SP3
SQL Server now supports QLogic Virtual Interface
Architecture (VIA) System Area Network (SAN) implementations. To enable SQL
Server support for connections over QLogic VIA, both the client and server
computers must provide IP address resolution in a file named Vihosts in the
appropriate Windows system32\drivers\etc folder.
The Vihosts file should be formatted as follows:
<VI IP Address of server computer> <SERVER_COMPUTERNAME>
<VI IP Address of client computer> <CLIENT_COMPUTERNAME>
For example:
139.4.130.1 SQLCOMPUTER
139.4.130.2 SQLCLIENT
Use IP addresses from the respective QLogic VIA network cards
and actual computer names. Otherwise, connections cannot be made to named
instances or with other IP protocols such as TCP or Named Pipes. The Vihosts
file is not necessary for Giganet VIA connectivity.
Note You must identify the correct VIA vendor on client computers by
using Client Network Utility. Select the appropriate value in the Vendor
drop-down box. The corresponding action should also be completed on server
computers through Server Network Utility.
5.6 Meta Data Services Enhancements
This section discusses enhancements to SQL Server 2000
Meta Data Services that are included with SP3a. 5.6.1 Meta Data Browser Exports in Unicode
Introduced in SP1Meta Data Browser now exports XML-based meta data in
Unicode. Before SQL Server 2000 SP1, the browser exported ANSI code, which does
not support non-English characters. This functional change is invisible to the
user. As of this SP3a release, exported data is always expressed as Unicode.
You can still export in ANSI code by setting the value of the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Repository\Engine\XMLExport to 0. The
following list represents the values that you can set for this registry key:
- NOOBJID=1
- NOHEADER=2
- INDENTATION=4
- UNICODE=8
- LOGUNMAPPEDTAGS=16
- EXPORTBASE=32
For more information about each flag, see "IExport::Export
Method" in SQL Server Books Online. 5.6.2 Scripting Support Disabled
Introduced in SP3Script support in information models has been disabled.
After installing SP3a, you receive the following error if your application
accesses a property or method for which a script is defined:
EREP_SCRIPTS_NOTENABLED
To enable script support
If you need to continue executing scripts, you can
use the following procedure to create a registry setting that enables script
support.
- Open Registry Editor and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft.
- Create a registry key named Repository (if
one does not already exist), and then create a sub-key named Engine so that the
path equals Repository\Engine.
- On the Engine registry key, add a new
DWORD value named AllowScripting and set the value to
1. If you want to disable scripting later, set the value of this new
registry key to 0.
Important You should always secure information models and the repository
database to prevent unauthorized access. 5.6.3 New RepositoryUser Role for Accessing Repository Information
Introduced in SP3SQL Server includes in the
msdb
database a set of tables, stored procedures, and views that store
information used by the Meta Data Services repository engine. In SP3, a new
dedicated role named
RepositoryUser was added and must be used
to access and update repository information. This role has been granted create,
read, update, delete, and execute permissions on these objects. The
public role no longer has permissions on these objects. .
This change affects existing repository objects in addition to any
additional objects created by the repository engine in the future. Users and
applications that access the repository through the
public
role must be added to the
RepositoryUser
role.
5.7 Data Transformation Services Enhancements
This section discusses enhancements to SQL Server 2000
Data Transformation Services that are included with SP3a. 5.7.1 DTS Wizard No Longer Limits String Columns to 255 Characters
Introduced in SP2When you export data to a text file, the DTS
Import/Export Wizard now configures the package to write up to 8000 characters
of any column containing string-type data. 5.7.2 Security Context Logged for DTS Packages Run by SQL Server Agent
Introduced in SP2SQL Server Agent records the security context under
which each step in a job runs. In SP3 or later, the security context appears in
the Job History dialog box. When you run a DTS package from a step in a job,
the SQL Server Agent logs the user account under which the package runs. This
information helps administrators diagnose permission and authentication issues
that occur when DTS packages are scheduled to run on a server.
5.7.3 SQL Server Agent Proxy Account Improvements
Introduced in SP2Prior to SP2, DTS packages stored on the server could
not be run under the credentials of the SQL Server Agent Proxy Account unless
the proxy account had access to the user Temp folder for the account under
which either the server (in the case of jobs run from
xp_cmdshell), or the agent (in the case of agent jobs) was
running. Because of this, users often had to adjust the TEMP environment
variable for the SQL Server or SQL Agent startup account to point to a
directory that was accessible to both the startup and the proxy accounts, such
as C:\Temp. For SP2 and later, DTS has been enhanced to use the system Temp
folder if the user Temp folder is unavailable, greatly reducing the need for
these adjustments.5.7.4 Save to Meta Data Services Disabled by Default
Introduced in SP3By default, SP3 and later disables the option to store
DTS packages in
Meta Data Services. This means that the Meta
Data Services option does not appear in the Location drop-down list in
the
Save DTS Packages dialog box. In addition, this option is
disabled on the Save, schedule, and replicate package page in the DTS
Import/Export Wizard.
To allow packages to be saved in Meta Data Services- In SQL Server Enterprise Manager, right-click the
Data Transformation Services node, click Properties,
and then click Enable Save to Meta Data Services.
Note You must be logged on with administrator privileges to modify this
property.
When the option to store packages in Meta Data Service is
disabled, you can load existing packages from Meta Data Services, edit them,
and
save them to Meta Data Services using the Save option.
However, Meta Data Services is not available from the
Save As
option. For example, you cannot re-save a package to Meta Data Services using a
different name.
5.8 XML Enhancements
The following topic discusses an enhancement for XML and
SQLXML in SP3a. 5.8.1 Improved Validation of XPath Expressions
Introduced in SP3Prior to SP3, the version of MSXML that was installed
with SQL Server 2000 (MSXML 2.6) permitted a predicate in an XPath expression
to follow the special character abbreviation that identifies the current
context node, which is denoted by a period (.) in XPath syntax. This violates
the XPath syntax specification, which requires that this character be followed
by a location path expression.
When you apply SP3 or later, an
updated version of MSXML (3.0 SP3) is installed as part of the MDAC upgrade.
For more information, see Section 5.5.1 Updates to Microsoft Data Access
Components.
With the new version of MSXML, a predicate
cannot immediately follow the current context node abbreviation special
character. XPath expressions in SQLXML queries (XPath queries against annotated
mapping schemas and in XSLT style sheets written to transform the results of
SQLXML queries) that use the faulty syntax will fail after you upgrade to SP3
or later
To prevent these failures, identify and fix any expressions
that use the incorrect syntax. For example, the syntax of the XPath expression
that is specified as the value of the test attribute in the following xsl:if
element is not valid because the predicate, [@ResourceTypeID='2'], immediately
follows the special character abbreviation that identifies the current context
node.
The following statement, which previously did not generate an
error, will fail after SP3 or later is installed.
xsl:if test=".[@ResourceTypeID='2']">
To prevent a failure, the XPath expression must be amended as follows:
<xsl:if test="@ResourceTypeID='2'">
5.9 Virtual Backup Device API Enhancements
The following item applies to the SQL Server 2000
Virtual Backup Device API.5.9.1 Capturing Multiple Databases in a Single Snapshot
Introduced in SP2
The Virtual Backup Device API allows ISVs to integrate SQL
Server 2000 into their products. This API is engineered to provide maximum
reliability and performance. It fully supports the backup and restore
functionality of SQL Server 2000, including the complete range of hot and
snapshot backup capabilities.
In SP1 and earlier, there was no way to
freeze and back up more than a single database at one time. SP3 or later now
provides server-side support for freezing and capturing multiple databases in a
single snapshot through the
VDC_PrepareToFreeze command.
The Virtual Backup Device Specification in SP3a contains updated
information about the
VDC_PrepareTo Freeze command. An updated
version of the Virtual Device Interface header file (Vdi.h) can be found at
\Devtools\Include in the SP3a setup directory
You can download the
updated specification from the Microsoft Download center at the
Microsoft SQL Server Downloads Web
site.
5.10 Error Reporting
Introduced in SP3Microsoft SQL Server error reporting is disabled by default.
You can enable it during installation through SQL Server Setup or Analysis
Services Setup, or after installation through the
Server
Properties dialog box in Enterprise Manager or the
Server
Properties dialog box in Analysis Manager. Enabling error reporting
while you are running SQL Server Setup allows error reporting for the SQL
Server database engine and SQL Server Agent. Enabling error reporting while you
are running Analysis Services Setup allows error reporting for Analysis
Services. If you want to enable error reporting for both SQL Server and
Analysis Services, you must enable error reporting for SQL Server while running
SQL Server Setup and Analysis Services while running Analysis Services Setup.
If you enable this feature, SQL Server is configured to send a report
to Microsoft automatically if a fatal error occurs in the SQL Server database
engine, in SQL Server Agent, or in SQL Server Analysis Services. Microsoft uses
error reports to improve SQL Server functionality and treats all information as
confidential.
Information about errors is sent over a secure (HTTPS)
connection to Microsoft, where it is stored with limited access. Alternatively,
this information can be sent to your own Corporate Error Reporting server. See
this
Microsoft Web site for more information about setting up
a Corporate Error Reporting server.
The error report contains the
following information
- The condition of SQL Server when the problem occurred.
- The operating system version and computer hardware
information.
- Your digital Product ID, which can be used to identify your
license.
- The network IP address of your computer.
- Information from memory or files of the process that caused
the error.
Microsoft does not intentionally collect your files,
name, address, e-mail address, or any other form of personal information. The
error report can, however, contain customer-specific information from the
memory or files of the process that caused the error. Although this information
can potentially be used to determine your identity, Microsoft does not use this
information for that purpose.
For the Microsoft error reporting data
collection policy, see this
Microsoft
Web site.
If you enable error reporting and a fatal error
occurs, you may see a response from Microsoft in the Windows Event log that
points to a Microsoft Knowledge Base article about a particular error. A
response may look similar to the following example:
Source = MSSQLServerOlapServicesDW
EventID = 1010
data = http://support.microsoft.com/support/misc/kblookup.asp?id=Q123456
&iBucketTable=1&iBucket=39980&Cab=21474432.cab&LCID=1033
&OS=5.1.2600.2.00010100.0.0
To disable error reporting for the SQL Server database engine
and SQL Server Agent, go to SQL Server Properties (General Tab) in Enterprise
Manager and clear the
Enable the error reporting check box. To
disable error reporting for Analysis Services, go to Server Properties in
Analysis Manager and clear the
Enable Error Reporting check
box. If error reporting is enabled for both SQL Server (database engine and SQL
Server Agent) and Analysis Services, you must disable error reporting for SQL
Server and Analysis Services individually.
5.11 English Query Enhancements
Introduced in SP21Microsoft has released a security enhancement for English
Query applications. This enhancement is not installed as a part of the service
pack. However, it is recommended that you apply it if you use English Query.
The security enhancement is located on the SP3a CD-ROM in the folder \EQHotfix.
Details about the English Query enhancement are available at the
Microsoft
Product Support Services Knowledge Base. Search the Knowledge Base
for article
297105.
5.12 DB-Library and Embedded SQL for C
Introduced in SP1While the DB-Library and Embedded SQL for C APIs are still
supported in SQL Server 2000, no future versions of SQL Server will include the
files that are necessary to program applications that use these APIs.
Connections from existing applications written using DB-Library and Embedded
SQL for C will still be supported in the next version of SQL Server, but this
support will be dropped in a future release. When writing new applications,
avoid using these components. When modifying existing applications, you are
strongly encouraged to remove dependencies on these technologies. Instead of
DB-Library or Embedded SQL for C, you can use ADO, OLE DB, or ODBC to access
data in SQL Server. For more information about these technologies, see SQL
Server Books Online.