A.2 Preparing to Migrate the Repositories

Before you migrate a repository to a new version of Microsoft SQL Server, install a new, empty version 9.2 repository on the new SQL Server, record information about the repository you will migrate, close connections to it, and create a backup copy.

To prepare for repository migration:

On the new SQL Server, install a new version 9.2 QDB or CCDB. During installation, when you specify the repository name, specify the same name as the repository you will migrate.

For more information about installing a new repository, see Installing a Management Site in the Installation Guide for AppManager.
(Conditional) If you are migrating a QDB and Control Center manages it, ensure that Control Center uses Windows authentication to connect to the QDB for the migration.

If Control Center currently uses SQL Server authentication to connect to the QDB, use Control Center to change the authentication method. You can change back to SQL Server authentication after the migration. For information about changing the authentication method Control Center uses to connect to a QDB, see Performing QDB Management Tasks in the Control Center User Guide for AppManager.
On each computer, to ensure that the Distributed Transaction Coordinator (DTC) security settings are the same, complete the following steps:

WARNING:If the settings are not the same, the migration will fail.
1. In the Component Services application in Administrative Tools, expand Component Services\Computers\My Computer\Distributed Transaction Coordinator.
2. Right-click Local DTC and select Properties.
3. On the Security tab, note the settings.
4. (Conditional) If the settings on the new computer do not match the settings on the old computer, adjust the settings on the new computer and restart it.
On the old SQL Server, to note the properties for SQL Server logins with access to the repository, complete the following steps:
1. In Microsoft SQL Server Management Studio, expand SQL_Server_Name\Databases.
2. Right-click the repository and select New Query.
3. In the query window, type the following command, and then click Execute:
```
SELECT  name,
        CASE WHEN type = 'S' THEN 'SQL'
             ELSE 'Windows'
        END AS 'Type'
FROM    sys.database_principals
WHERE   type IN ( 'S', 'U' )
        AND name != 'dbo'
        AND default_schema_name IS NOT NULL
        AND default_schema_name != 'guest'
ORDER BY name ASC
```
4. Expand Databases\Repository_Name\Security\Users and compare the accounts listed in the results table for the query to the accounts in the Users folder.
5. For each account that appears in both the results table and the Users folder, right-click the user in the Users folder and select Properties.
6. On the General page, note the Login name and Database role membership.
7. On the Securables page, note the Explicit permissions.
  
  You will recreate the SQL Server logins after you restore the repository on the new SQL Server.
(Conditional) If you changed the schedule for any NetIQ SQL Server jobs, use the Task Scheduler Configuration Utility to view the job schedules and note the settings for each modified job:
1. In the utility, select the repository that contains the modified job.
2. In the job grid, select the job and then click Change Schedule.
3. Note the settings, and then click Cancel.
(Conditional) If you will migrate a CCDB that manages remote QDBs, on the SQL Server that hosts the CCDB you will migrate, complete the following steps to note the linked server properties for the remote QDBs:
1. In Microsoft SQL Server Management Studio, expand Server Objects\Linked Servers.
2. Right-click a linked QDB and select Properties.
3. On the General page, note the linked server name and server type.
4. On the Security page, note each local login defined and how Control Center makes the connection.
  
  A login can:
  - Be made without using a security context. If this option is selected, Control Center connects without using any login and password.
  - Be made using the login's current security context. If this option is selected, Control Center uses the Log On As account for the SQL Server Agent service to log in to the remote QDB.
  - Be made using this security context. If this option is selected, it implies you checked the Use SQL Server authentication option when you added the QDB to Control Center. When you restore the SQL Server link on the new CCDB computer, provide the same SQL Server user name and password you provided when you added the QDB to Control Center.
5. On the Server Options page, note the RPC and RPC Out values.
  
  You will restore the SQL Server links after you restore the CCDB on the new computer.
On each computer, to ensure that the SQL Server collation order, sort order, and character set are the same, complete the following steps:

WARNING:If the settings are not the same, the migration will fail.
1. In Microsoft SQL Server Management Studio, right-click the SQL Server instance and select Properties.
2. On the General page, note the Server Collation setting, and then click OK to close the Properties window.
3. Right-click the SQL Server instance and select New Query.
4. In the query window, type the following command, and then click Execute:
  
  sp_helpsort
  
  The sort order and character set is displayed in the results table.
  
  When you install SQL Server, the collation order is set by default according to the locale of the operating system. You can use advanced installation options to change the collation order. If the collation order is not the same, re-install SQL Server on the new computer and set the collation order to be the same as the collation order on the old computer.
(Conditional) If you will migrate the QDB, complete the following steps to close connected services:
1. Click Start > Administrative Tools > Services.
2. For each of the following services, right-click the service and select Stop:
  - On the SQL Server that hosts the QDB, SQL Server Agent service
  - On primary and secondary management servers that connect to the QDB, NetIQ AppManager Management Service
  - (Conditional) If you are running NetIQ Advanced Analytics, on the computer where you installed the services, NetIQ Advanced Analytics Configuration Service and NetIQ Advanced Analytics Service
  - On primary and secondary management servers that connect to the QDB, NetIQ AppManager Client Communication Manager and NetIQ AppManager Client Resource Monitor services
  - On the computer where you installed the Task Scheduler service, NetIQ AppManager Task Scheduler Service
  - (Conditional) If Control Center manages the QDB, on the command queue service computer, NetIQ AppManager Control Center Command Queue Service
  - (Conditional) If Control Center manages the QDB, on the SQL Server that hosts the CCDB, SQL Server Agent service
  - (Conditional) If report agents connect to the QDB, on the agent computers, NetIQ AppManager Client Communication Manager and NetIQ AppManager Client Resource Monitor services
  If a service is set to automatically restart when it stops, disable the service.
(Conditional) If you will migrate the CCDB, complete the following steps to close connected services:
1. Click Start > Administrative Tools > Services.
2. For each of the following services, right-click the service and select Stop:
  - On the computer where you installed the Task Scheduler service, NetIQ AppManager Task Scheduler Service
  - On the command queue service computer, NetIQ AppManager Control Center Command Queue Service
  - On the SQL Server that hosts the CCDB, SQL Server Agent service
  - On the Deployment Service computer, NetIQ AppManager Deployment Service
  - On the Deployment Web Service computer, World Wide Web Publishing Service that manages the Deployment Web Service and the Web Depot virtual directories
(Conditional) If you will migrate the QDB, stop AppManager Connectors that connect directly to it, such as the AppManager Connector for Micromuse Netcool/OMNIbus or AppManager Connector for Security Manager.
Close any AppManager consoles, such as the Control Center console and Operator Console, that connect to the repository.
(Conditional) If you will migrate the QDB and it is a data source for Analysis Center, complete the following steps on the Data Mart computer to stop the Analysis Center ETL job:
1. In Microsoft SQL Server Management Studio, expand SQL_Server_Name\SQL Server Agent\Jobs.
2. Navigate to the ETL job.
3. Right-click the job and select Disable.
To verify that there are no open connections to the repository you will migrate, complete the following steps:
1. On the repository computer, in Microsoft SQL Server Management Studio, expand SQL_Server_Name\Databases.
2. Right-click the repository and select New Query.
3. In the query window, type the following command, and then click Execute:
```
USE master
GO
Exec sp_who2
GO
```
4. In the results table, check the DBName column for the repository name. The column should not contain entries for the repository.
(Conditional) If the DBName column contains entries for the repository, complete the following steps for each entry:
1. In the SPID column for the row in which the repository name appears, note the SPID number.
2. Right-click the repository and select New Query.
3. In the query window, type the following command, and then click Execute:
```
kill SPID_Number
```
Repeat Step 13 and Step 14 until the DBName column does not contain entries for the repository.
Create a backup copy of the repository you will migrate.

For more information about creating backup copies, see Creating Backup Copies of the Repositories.

After you create a backup copy of the repository you will migrate, you can restore the backup copy over the new, empty repository. For more information about restoring the repository, see Restoring Repositories on the New SQL Server.

A.2.1 Creating Backup Copies of the Repositories

This section describes how to use Microsoft SQL Server Management Studio to create backup copies of the QDB and CCDB before you migrate them to the new computer.

To create a backup copy of the QDB or CCDB:

In Microsoft SQL Server Management Studio, expand SQL_Server_Name\Databases.
Right-click the repository and select Tasks > Back Up.
On the General page, complete the following steps:
1. From the Database list, select the repository.
2. Note the setting in the Recovery model field.
3. From the Backup type list, select Full.
4. For Backup component, select the Database radio button.
On the Options page, complete the following steps:
1. Select the Back up to the existing media set radio button.
2. (Conditional) If you want to add this backup to existing backups, select the Append to the existing backup set radio button.
3. (Conditional) If you want to discard existing backups, select the Overwrite all existing backup sets radio button.
Click OK to start the backup.
(Conditional) If the Recovery model is set to FULL on the General page, after the backup completes, complete the following steps to add a backup device for the transaction log and to back up the transaction log:
1. Right-click the repository and select New Query.
2. In the query window, type the following command, and then click Execute:
```
USE master
EXEC sp_addumpdevice 'disk', 'dump_device_log',
'C: \repository_nameBACKUP\repository_name_Log.bak'
GO
BACKUP LOG repository_name TO dump_device_log
GO
sp_dropdevice 'dump_device_log'
GO
```
  where repository_name is the name of the QDB or CCDB you are backing up and dump_device_log is the name of the backup device or file
Copy the backup file to the computer where you will restore the QDB or CCDB.

After you copy the backup file to the new computer, you can restore the backup copy over the new, empty repository on the new computer. For more information about restoring the repository, see Restoring Repositories on the New SQL Server.