How do I move the AppManager Repository (QDB) to a different server? (NETIQKB2525)

How do I move the AppManager Repository (QDB) to a different server?
How do I migrate a repository to a different server?
How do I move the AppManager Repository (QDB) from one SQL Server to another SQL Server?

IMPORTANT: Prior to starting these steps, you should disable the ETL job for this Repository (QDB) if the Repository that you will be moving is a Data Source in Analysis Center.  Once all of the below steps are completed, you can re-enable the ETL job for this Repository.

Please read this entire article before beginning this process.

In order to move the AppManager Repository Database (QDB by default) to a different server:

1. Ensure that the default collation of the existing SQL Server instance is identical to instance where you will move the repository. AppManager will not function properly if the collations are different.

For more information, see the Microsoft article "Specifying the Default Collation for an Instance of SQL Server" at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0f3n.asp.

2. Select the AppManager Repository (QDB).

3. Execute the following SQL queries and save the results to a file for future reference.  To execute a SQL query, press F5 or click Execute Query (green arrow).

sp_configure
sp_helpdb 'QDB'
sp_helpsort
sp_server_info

4. Stop the NetIQ AppManager Management Server (AppManager 6.0.x & 7.0.x) service on the AppManager Management Server(s) that are registered to this repository database.

Note : If the service is set to automatically restart when it stops, you must disable the service.

5. Backup the existing AppManager Repository (database).

Note :  The following items must be identical on the newly installed SQL Server, and the original SQL Server:

• Must have the same sort order
• Must have the same character set defined in SQL.
• The Default Collation on the new SQL Server must match the Default Collation of the original SQL Server.

6. Launch the AppManager Setup Program (setup.exe) on the new SQL Server to install the AppManager Repository component.  This step must be completed before moving on to Step 7.

7. Restore the existing AppManager Repository (from the backup created in Step 5) over the newly installed AppManager Repository component on the new SQL Server.

Note : For cluster server installations, AppManager only supports installation on Microsoft Cluster Server.

8. If the new SQL Server has a different machine name than the previous SQL Server, update the Repository Name in the Repository table:

a. Open SQL Server Query Analyzer.
b. Select the AppManager Repository (QDB).
c. Execute the following SQL Query to update the Repository Name and Version information:

SELECT *
FROM Version
WHERE Component = 'Repository'
UPDATE Version
SET MachineName = 'NewServerName'
WHERE Component = 'Repository'

Where:

• 'Repository ' is typed in exactly as you see it ('Repository').
• 'NewServerName ' is the NetBIOS machinename of the new SQL Server.

Note : Step 9 is NOT required if you have installed Hotfix 72122 for the AppManager Operator Console, or if you have installed an Operator Console that was released after May of 2010.  If this is the case in your environment, you can skip to Step 10.

9. Recreate the Probe account.

a. Open SQL Server Enterprise Manager.
b. Expand the SQL Server.
c. Expand Databases.
d. Expand the AppManager Repository.
e. Select Users.
f. Right-click on Probe.
g. Select Delete.
h. Go to Security.
i. Select Logins.
j. In the right pane, verify login for Probe.
k. If login does not exist, create a new login with the following properties:

• No Password
• No SQL Server Role
• Public Access to QDB

Note : The login is more problem-free if the account is deleted and re-created with the properties listed above.

10. The Blob table holds the chart information for any charts created for use in the Chart Console. To enable these for use after the Repository is moved, issue the following T-SQL against the Repository Database :

update dbo.blob set comment = replace(comment, '_OLDSERVER\', '_NEWSERVER\')
from dbo.blob
where charindex('_OLDSERVER\', comment) > 0

Note : If the Repository is installed on an instance ? the instance name should be used (i.e. if the new server is in instance named SERVER1\INSTA, then replace _NEWSERVER\ with _SERVER1\INSTA\).

11. Recreate the NetIQ account.

For SQL 2000 Repository Servers:

a. Open SQL Server Enterprise Manager.
b. Expand the SQL Server.
c. Expand Databases.
d. Expand the AppManager Repository.
e. Select Users.
f.  Right-click netiq (if the account does not exist, skip down to step i). 
g. Select Delete > Security > Logins.
h. On the right pane, verify login for netiq.
i.  If the netiq login account does not exist, create a new login with the following properties:

• Password: netiq
• SQL Server Role = SA
• Default Database = QDB

For SQL 2005 Repository Servers:

a. Open SQL Server Management Studio.
b. Expand the SQL Server.
c. Expand Databases.
d. Expand the AppManager Repository (QDB by default).
e. Select Security.
f.  Select Users.
g.  Right-click netiq (if the account does not exist, skip down to step j).
h. Select Delete > Security > Logins.
i. On the right pane, verify login for netiq.
j.  If netiq login account does not exist, create a new login with the following properties:

• Password: netiq
• SQL Server Role = SA
• Default Database = QDB

Note : In either version of SQL (2000 or 2005) the login is more problem-free if the account is deleted and re-created with the properties listed above. Take the following additional steps to reassign jobs specified to run using this ID:

For SQL 2000, open SQL Server Query Analyzer and select the AppManager Repository (QDB).  For SQL 2005, right-click the AppManager Repository (QDB) and select New Query.

a. Issue the following command to change the db owner to ?sa? for a short period of time, so that SQL will allow you to delete the netiq login:

sp_changedbowner 'sa'

b. Follow Steps g and h above to get back to the logins window, then right-click netiq and select Delete.
c. When prompted to re-assign the jobs to a different database ID, choose Assign.
d. Re-create the ID using the properties above.
e. Issue the following command to change the db owner back to netiq:

sp_changedbowner 'netiq'

Note : If you receive the following error in SQL Query Analyzer, 'The proposed new database owner is already a user in the database.' , go back to Users (not Logins) in SQL Server Enterprise Manager and delete the netiq user account. Once that is complete, re-issue the SQL command listed above.

On SQL 2000 Servers:

f.  Open SQL Server Enterprise Manager.
g. Expand SQL Server.
h. Expand Databases.
i.  Expand AppManager Repository (QDB by default).
j.  Right-click the Users directory and select Refresh.
k. Verify that netiq is the login name for the dbo user. If not, there is a problem with the configuration. Resolve the problem and repeat the steps above.
l.  Expand the AppManager Repository.
m.Expand Management.
n. Expand SQL Server Agent.
o. Expand Jobs.
p. Right-click on each job, select Properties, and change the owner back to netiq.

On SQL 2005 Servers:

f.  Open SQL Server Management Studio.
g. Expand SQL Server.
h. Expand Databases.
i.  Expand AppManager Repository (QDB by default).
j.  Expand Security.
k. Right-click the Users window and select Refresh.
l.  Verify that netiq is the login name for the dbo user. If not, there is a problem with the configuration. Resolve the problem and repeat the steps above.
m. Expand the SQLServer.
n. Expand SQL Server Agent.
o. Expand Jobs.
p. Right-click on each job, select Properties, and change the owner back to netiq.

12. Issue the following command in SQL Server Query Analyzer to verify the owner of QDB is netiq:

sp_helpdb 'QDB'

13. If the owner specified for the DB is not 'netiq', issue the following command to change it to 'netiq':

sp_changedbowner 'netiq'

Note : Before proceeding with Step 14, see the Warning in the Notes section of this article (below) for more information in regards to re-registering a Management Server.

14. Re-register the NetIQ AppManager Management Server (AppManager 6.0.x & 7.0.x) service and QDBMS DSN with the following command:

netiqms -r QDBMS:QDB:netiq:netiq:SQLServerName -i

Where:

• QDB is the name of the AppManager Repository
• netiq is the name of the NetIQ SQL Account.
• netiq is the password of the NetIQ SQL Account.
• SQLServerName is the name of the SQL Server. (or SQLServerName\Instance)

Note :  When registering the Management Server service, the default port (9999) is set in the registry key:

HKEY_LOCAL_MACHINE | SOFTWARE | NETIQ | AppManager | 4.0 | NetIQms | Port

If you have customized Management Server port settings, you will need to change this registry data value back to the appropriate port.

Note : See the Warning in the Notes section of this article (below) for more information in regards to making Registry Key changes.

15. Re-enable and then start the NetIQ AppManager Management Server (AppManager 6.0.x, and 7.0.x) service on the AppManager Management Server.

The Migration of this Repository should be completed at this point.

Formerly known as NETIQKB2525

Warning : Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. NetIQ cannot guarantee that problems resulting from the incorrect use of Registry Editor can be resolved. Make sure that you back up your Registry prior to making any changes.

Warning : When manually registering a Management Server, if you are using the -ur switch to preserve the RPCKey value for Encrypted Communication, you should use the -ur prior to the -i switch. 

Example: C:\> netiqms -r QDBMS:QDB:Username:Password:SQLServerName -ur -i

Note :   Re-registering the MS using the format noted will disable the PIOC settings and UNIX port in the registry.  To correct this you should do the following:

1. Open Regedit
2. Browse to HKLM\Software\NetIQ\AppManager\4.0\NetIQms\Config
3. Edit the Persistent IOC key setting the value to 1
4. Edit the PIOC Map File Path key setting it to the path of your PIOC files (typically C:\PROGRA~1\NetIQ\APPMAN~1\dat\pioc)

If you have UNIX agents perform the following additional steps:

1. Browse to HKLM\Software\NetIQ\AppManager\4.0\NetIQms
2. Edit the UNIX Port key
3. Set the Base to Decimail
4. Set the value to 9001 or other custom port numer you may have specified during AppManager installation

NetIQ is working on a fix for this registry issue.

For more information about removing the AppManager Management Server from the AppManager Operator Console, refer to the following NetIQ Knowledge Base article: NETIQKB2870:How do I remove the Management Server from the Operator Console?