How to move the Client Management Database to a new MS SQL 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.

     • Example:

     • ​

5. Click Ok to begin the Database backup.

6. Click Ok once the backup is completed successfully.

7. 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
  **Note: the schema name that prefixes each Client Management Table**

  • 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]
; Section for database settings.
; The database connection type. This can be Postgres, ODBC,
; ODBCDirect, ORACLE or DB2. The value is not case-sensitive.
 
DatabaseType=odbcdirect
 
; The database name. The value is interpreted based on the type:
;
; Postgres: The name of the database.
; ODBC: The name of the DSN which should be correctly configured already.
; ODBCDirect: The ODBC driver connection string which is DBMS dependent ; and something like the following:
;
; Driver={SQL Server};Server=MyServerName;Database=myDatabaseName;Uid=myUsername;Pwd=myPassword
; Driver={Oracle ODBC Driver};Dbq=myDBName;Uid=myUsername;Pwd=myPassword
;
; If the Uid and Pwd are not specified they are taken from the User and
; Password settings in this file.
; ORACLE: DatabaseName is the service name specified in tnsnames.ora
; Login and password used are in User and password field.
; DB2: DatabaseName is the name of the database, user the system login.
DatabaseName=Driver={SQL Server};Server=MyNewSQLServerl\MyInstance;Database=bcmdb
 
; The host name where the DBMS is running. Ignored for ODBCDirect, Oracle and DB2.
Host=
 
; The connection port. Only avalaible for postgres database.
Port=
 
; The user id to use for loggin on to the DBMS.
User=bcmdbuser
 
; The clear text password to use with the user login. If supplied, this
; gets encrypted and stored as PasswordEnc. The Password field is then
; deleted.
Password=Bcmuser@06
 
; If true, we drop our current connection if there's an SQL error on
; it. If false, we handle the error by printing the status but then keep the
; connection.
DropConnectionOnError=true
 
; Comma-separated list of prioritary operations to perform.
; Possible values can include: SoftwareInventory, HardwareInventory, CustomInventory
; or EventManagerEvent
FileTypePriority=EventManagerEvent,PatchInventory2,PatchUpload,PackagerZipPackage
 
; If true, assignment are sent in a pre-5.2 compatible way
 LegacyAssignments=false
 
; Used to define the maximum number of Retry and the gap between each of them,
; should the connection to the database be unavailable
RetryMax=5
RetryGap=10
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.