How to move the Client Management Database to a new MS SQL Server?

How to Migrate the Client Management Database from old server to the New server ?

• To move the database to a new SQL server, follow the steps below:
• On the Master Server, go to Start > Administrative Tools > Services, and stop the BMC Client Management Agent service.

Backup the current Database by following below steps : 

1. Open SQL Server Management Studio on the existing Client Management Microsoft SQL Database Server.
2. Login as SA or a SA equivalent account.
3. Expand the Databases folder.
4. Right-click the Database that needs to be backed up and select Tasks -> Back Up.

• If a file path already appears in the "Destination" window at the bottom, take note of the path and name of the backup file that will be created.  
• If a file path does not exist in the window, click the Add button

• Click the ellipse button to the right of the "File name" field.
• In the "File name" field at the bottom of the "Locate Database Files" window, enter the desired path and backup file name. This will be the name of the backup file once the backup is complete.
  • Take note of the path where the backup file will be created.
• Click Ok Locate Database Files window and then click Ok to close the "Select Backup Destination" window.

​

1. Click Ok to begin the Database backup.
2. Click Ok once the backup is completed successfully.
3. Copy the backup file to the new Database server

Restore the database onto the new server 

1. Open SQL Server Management Studio on the new SQL Database server.
2. Login as SA or a SA equivalent account.
3. Right-click Databases on the left and select Restore Database.
4. In the "Source" section select the Device option, then click on the triple ellipses and select the DB backup file created previously and select OK
5. The Destination section will auto-populate, accept the remaining defaults, and select OK and OK again once complete
6. Example: 

Recreate the SQL login used by the Client Management Service:

• Expand Databases -> Restored DB Name -> Tables

• If the table names do not start with bcmdbuser, one must change line 3 in the query below to match the DB Schema before executing the query.
• Possible names from prior version of Client Management might be:
  • facdbuser
  • ampdbuser
• Right click on the restored DB name and select "New Query"
• Paste the following query and alter line 3 as needed:

exec sp_change_users_login

        @Action = 'Auto_Fix',
        @UserNamePattern = 'bcmdbuser',
        @LoginName = null,
        @Password ='Bcmuser@06'​​

Update the Client Management Configuration file to use the new SQL Server

• On the Master Server, open the following file for edit:
• C:\Program Files\BMC Software\Client Management\Master\config\Vision64Database.ini
• Update this file to use New DB Server and login password: (Highlighted values indicate the changes required)

[Database]
DatabaseType=odbcdirect

DatabaseName=Driver={ODBC Driver 17 for SQL Server};Server=MyNewSQLServerl\MyInstance;Database=bcmdb

User=bcmdbuser
Password=Bcmuser@06
PasswordEnc=

• Start the Master Server Service
• Monitor the mtxagent.log file to verify the new database connection is working properly.

Note:

• On Service Startup, Password= value in clear text will be read, encrypted, and the configuration file updated to remove the value for Password= and store the Encrypted Password in the PasswordENC= entry.