How to move an existing CMDB from one SQL server to a new SQL server
search cancel

How to move an existing CMDB from one SQL server to a new SQL server

book

Article ID: 180861

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

Due to either space constraints, OS version, or performance issues the Symantec_CMDB database needs to be moved to a new SQL Server.

Resolution

Disclaimer: The following steps are provided as a reference. Questions or concerns or for more details about how to migrate a database from a SQL Server, please contact Microsoft. Also, see document references below under 'Related Articles.'

Login to all servers, SMP, Current SQL, and New SQL servers using the Account used to install the SMP! This will maintain correct ownership of the database when exported and maintain alignment on the import.

The following solution involves several steps:

  1. Backup the Configuration Management Database
  2. Restoring the Configuration Management Database
  3. Verify the permissions of the CMDB
  4. Change the database location referenced by the Management Platform.

       NOTE: Symantec Management Platform 8.1 and later does not allow a database change in the console, please use the Symantec Installation Manager.
       See details below.

Pre-move check on the destination SQL Server.

1. Verify that the SQL Server version, service pack, and edition are supported by the Symantec Management Platform.
2. Verify that the Symantec_CMDB database collation matches the destination SQL Server collation.
Symantec_CMDB database collation will be in the database properties. SQL Server collation will be in the SQL Server instance properties.
Following SQL Query can also show the master database (SQL Server instance) and user database collation.

      select sdb.name ,sdb.collation_name from sys. databases as sub

The master database collation must match the Symantec_CMDB database collation to avoid a collation conflict.

3. Back up the Symantec Management Platform server KMS encryption keys following the steps from the IT Management Suite Disaster Recovery White Paper.
KMS encryption keys:
Certain elements typed into the console are encrypted before being saved to the database. For example, hierarchy credentials or Software Management policies that use RUNAS user names and passwords.
There is no way to read encrypted data from the database without a corresponding key. The absence of KMS encryption keys leads to unrecoverable data.

How to backup KMS encryption keys:
Go to Settings>All Settings>Notification Server>Notification Server Settings>Encryption Keys Management>Export


 

Backup the Configuration Management Database

  1. Open Microsoft SQL Manager Studio.
  2. In the left pane, expand the Databases folder.
  3. In the left pane, under Databases, right-click the name of your database.
  4. In the right-click menu, click Tasks > Back Up.
  5. In the Backup Database dialog box, in the Backup type drop-down list, click Full.
  6. In the Backup set section, enter a name for your backup.
  7. In the Destination section, add the location where you want your backup file to be stored.
  8. This location should be a secure storage location, and should not be on the local computer.
  9. Click OK.

Restoring the Configuration Management Database

  1. Open Microsoft SQL Manager Studio.
  2. In the left pane, on the right-click menu of the Databases folder, click Restore Database.
  3. In the Restore Database dialog box, click From device.
  4. Click the ellipsis option that is associated with the From device option that lets you select the database.
  5. In the Specify Backup dialog box, click Add.
  6. In the Locate Backup File dialog box, select the CMDB that you backed up on the Symantec Management Platform 7.0 server, and click OK.
  7. In the Specify Backup dialog box, click OK.
  8. In the Restore Database dialog box, in To database, enter a name for the database, select the database in the Select the backup sets to restore section, and click OK.
  9. After the database is restored, click OK in the dialog box that appears.

Setting the appropriate permissions to the SQL database

  1. When you restore the Configuration Management Database (CMDB) on a new server, you must set the appropriate permissions to the SQL database. If you use application permissions to access SQL in Symantec Installation Manager, you must give the application account database ownership (DBO). If you use a specific SQL account to access SQL in Symantec Installation Manager, you must give that account DBO.
  2. To set the appropriate permissions to the SQL database
  3. Open Microsoft SQL Manager Studio.
  4. In the left pane, under the Databases folder, on the right-click menu of the CMDB, click Properties.
  5. In the Database Properties dialog box, in the Select a page section, click Files.
  6. In the right pane of the Database Properties dialog box, click the ellipsis option that is associated with the Owner option.
  7. In the Select Database Owner dialog box, click Browse.
  8. In the Browse for Objects dialog box, select the appropriate account and click OK.
  9. In the Select Database Owner dialog box, click OK.
  10. In the Database Properties dialog box, click OK.

 

Symantec Management Platform 8.1 and newer

1. Logon to the Management Platform Server with the application service account (Altiris Service).
2. Start the Symantec Installation Manager.
3. Configure settings> Configure Database Settings, Next.
4. Change the SQL Server/Instance name to the new server with the moved Symantec_CMDB database.
5. Select the correct Symantec_CMDB database and press Next.
6. If prompted, you may need to select the backup file of your KMS encryption keys to move forward with attaching the database from the new SQL server.

 

Symantec Management Platform 8.0 and older

Change the database location referenced by the Management Platform:
Two methods to point the Symantec Management Platform to the new SQL Server and CMDB

After performing the operations required to relocate a copy of the existing database, choose one of the below methods to redirect the location used by the Management Console for storage.

A. Backup and Restore of existing database, then transfer of connectivity to new DB Server
     (DB running on both servers)

  1. Open the Symantec Management Console
  2. Click on Settings, then Notification Server and finally Database Settings
  3. In the SQL Server Name section enter in the details of the new SQL Server (If you did not install SQL Server as the default 'instance', then when specifying the server you need to use the following format: servername\instancename)
  4. Once you have entered in the new SQL Server details, click anywhere on the page. The page will new refresh and you will now be able to select the CMDB from the Database Name section (This will be listed under Use Existing Database).
  5. Click on Save Changes

 B. Backup and Restore of existing database, modify CoreSettings.Config and Registry to change to new DB Server 
     (DB running on new server only)

If the database is simply being moved from one database server to another, and the Notification Server has not changed etc then simply do the following steps: 

  1. Open Regedit on the Notification Server and go to "HKLM\Software\Altiris\eXpress\Notification Server"
  2. Modify the "DBDsn" string value in the right column.  Look for the separated value: SERVER= and change it to the name of the server and the instance the database resides in.   Verify that the DATABASE= tag reflects the correct name of the restored database.
  3. If the database is in the default instance then just put the name of the database server. If the database is in a named instance then specify the instance.
    Examples:
     SERVER=ServerName;   {If the database is in the default instance}
     SERVER=ServerNameInstance {If the database is restored in a named instance}
  4. Use Notepad++ or a text editor capable of editing xml files without stripping out certain xml tags to edit the CoreSettings.Config file. This file can be located in one of two locations, depending on the OS version and installed version of the Management Platform. (Please create a backup copy of this file prior to making any changes)
    Location 1: C:\Program Files\Altiris\Notification Server\Config\CoreSettings.Config
    Location 2: C:\ProgramData\Symantec\SMP\Settings\CoreSettings.Config
  5. Search for "DbServer" and change the name in the value tag has the name and instance of the new database server.
  6. Search for "DbCatalog" and verify that the name specified in the value is the same name as the database as it was restored on the new DB server.
  7. Verify that the database access logon account used by the NS has rights to the database on the new server. It should have dbo.
  8. Restart the Altiris Service