How To Upgrade the Master and the Target Servers (MSX/TSX) to SQL Server 2000 Service Pack 3 (811326)

MORE INFORMATION

1. Identify the current service packs for the master and the target servers. Identify the servers that you want to upgrade to SQL Server 2000 SP3.
2. Defect any target servers that you do not want to upgrade to SQL Server 2000 SP3. You must upgrade both the master and the target servers to SQL Server 2000 SP3 to avoid problems in Multiserver Administration. You cannot have a master server with SQL Server 2000 SP3 and target servers with different service packs, or vice versa.
   
   Note: The changes introduced with SQL Server 2000 SP3 are not compatible with SQL Server 7.0 target servers, or with any servers that are running service packs that are earlier than SQL Server 2000 SP3.
3. Make sure that all the jobs are functioning correctly by reviewing the error logs, the SQLAgent logs, and the Jobs history. If you find any errors, resolve them before you upgrade to SQL Server 2000 SP3.
4. Verify the health of all the databases by running a DBCC CHECKDB command on the databases on the master and the target servers. Resolve any issues before the upgrade.
   
   Note: Make sure that you back up the master and the other required databases before you continue with the upgrade.
5. Use the MSX account for communication between the master (MSX) and the target (TSX) servers.
   
   You have two authentication options for a MSX account:
   
   Windows Authentication: This option promotes security because passwords are not stored. When you select this option, SQL Server and the SQL Server Agent are configured without local Windows Administrator rights.
   
   SQL Server Authentication: This option requires the SQL Server Agent service accounts to have local Windows Administrator rights because SQL Server stores the user name and the password as a local security authority (LSA) secret, and access is restricted to local Windows Administrators. You can create one account for all the target servers, or you can create different accounts for each target server.
   
   Note: Do not use the SQL Server Agent probe account (<computer_name>_msx_probe_login) for communication between the master and the target servers (MSX/TSX). While upgrading to SQL Server 2000 SP3, Setup removes the old probe accounts because the target servers no longer use them.
   
   Create the MSX account by using SQL Query Analyzer as follows:
   
   For Windows Authentication:

   
   use master 
   go 
   exec sp_grantlogin 'Domain\user' 
   go 
   use msdb
   go
   exec sp_adduser 'Domain\user','Domain\user','TargetserversRole' 
   go

   
   For SQL Authentication:

   
   use master 
   go 
   exec sp_addlogin <MSXAccount>, <MSXAccountPassword>, 'msdb' 
   go
   use msdb
   go
   exec sp_adduser <MSXAccount>, <MSXAccount>, 'TargetServersRole'
   go

   Note: Where <MSXAccount> represents the SQL Login name you select, and <MSXAccountPassword> represents the associated password. These values must be enclosed in single quotation marks.

1. Upgrade your target servers to SQL Server 2000 SP3 one at a time. For more information about upgrade timing, before you apply the service pack, see step 1 in the After You Upgrade TSX to SQL Server 2000 Service Pack 3 section.
   
2. Apply SQL Server 2000 SP3 to your master server. The old msx_probe accounts are removed by the SQL Server 2000 SP3 Setup, because the target servers no longer require them.
   
   Note: If an account owns SQL Server Agent jobs, the account is not removed. 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 Server Agent jobs, you must change the password of the msx_probe account.

1. To minimize the down time, run the extended stored procedure xp_sqlagent_msx_account on each of the target servers immediately after you upgrade to SQL Server 2000 SP3.
   
   Note: Before you run this, make sure that the SQLAgent service is running on the target and the master servers. Only members of the securityadmin fixed server role can run this extended stored procedure. After you run xp_sqlagent_msx_account, you must stop and then restart the SQL Server Agent on each server.
   
   Syntax
   
   xp_sqlagent_msx_account

   {N'GET' | N'SET' | N'DEL', N'MSX_domain_name', N'MSX_username',
   				N'MSX_password' } 

   
   
   Note: Parameters for xp_sqlagent_msx_account must be specified in order. You cannot use named parameters.
   
   Arguments
   
   N'GET'
   
   Retrieves the latest SQL Server Agent MSX account. N'GET' is an nvarchar data type with no default. The password is not reported for security reasons.
   
   N'SET'
   
   Sets the account that is to be used as the SQL Server Agent MSX account. Use the MSX_username, and the MSX_password parameters to specify the account to use as the SQL Server Agent MSX account. N'SET' is an nvarchar data type 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 that is to be used as the SQL Server Agent MSX account. Specify an empty string for the 'MSX_username' and the MSX_password parameters to set 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 data type with no default.
   
   'MSX_password'
   
   The password for the SQL Server account specified in MSX_username. Specify an empty string for this parameter, and for the MSX_username to select Windows security. In this case, the SQL Server Agent service account credentials are used to log on to the MSX server. MSX_password is an nvarchar data type with no default.
   
   Note: The parameters for xp_sqlagent_msx_account must be specified in order. You cannot use named parameters.
   
   Here is an example that 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
   

   
   Here is an example that sets the SQL Server Agent MSX account to SQL Server Authentication:

   
   EXEC master.dbo.xp_sqlagent_msx_account N'SET',
      N'',  -- Reserved for future use 
      N'user1',  -- MSX_username
      N'password'  -- MSX_password

   
   For more information about thexp_sqlagent_msx_account extended stored procedure, visit the following Microsoft Web site and review section 5.4.3:
   
   http://support.microsoft.com/kb/889551