How to move the Client Management database to a new MS SQL server

Version 3
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    BMC Client Management


    COMPONENT:

    Data Base Administration


    APPLIES TO:

    BMC Client Management All



    QUESTION:

    How to move the Client Management database to a new MSSQL server


    ANSWER:

     

       
    • To move the database to a new SQL server, please 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
      
       
    1. Open SQL Server Management Studio on the existing Client Management Microsoft SQL database server.
    2.  
    3. Login as SA or an SA equivalent account.
    4.  
    5. Expand the Databases folder.
    6.  
    7. Right-click the database that you would like to backup 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:
        •      
        • DB Backup Path and Filename ending in .BAK
        •     
      •   
    8.  
    9. Click Ok to begin the database backup.
    10.  
    11. Click Ok once the backup is completed successfully.
    12.  
    13. 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.  
    3. Login as SA or an SA equivalent account.
    4.  
    5. Right-click Databases on the left and select Restore Database.
    6.  
    7. 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
    8.  
    9. The Destination section will auto-populate, accept the remaining defaults, and select OK and OK again once complete
    10.  
    11. Example: 
      
      DB Restore 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. we 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"
    •  
    • Past the following query and alter line 3 as needed:
      
      exec sp_change_users_login
      
              @Action = 'Auto_Fix', 
              @UserNamePattern = 'bcmdbuser', 
              @LoginName = null, 
              @Password ='Bcmuser@06'  
      
      UserName and Schema Example
      
    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.

     


    Article Number:

    000101207


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles