Moving the MDB database from one MS SQL Server to a different MS SQL Server for SDM, Service Catalog and ITAM

Products

CA Service Management - Service Desk Manager CA Service Desk Manager CA Service Catalog CA Service Management - Asset Portfolio Management CA IT Asset Manager CA IT Asset Manager Asset Portfolio Management

Issue/Introduction

The following document describes the steps necessary to transfer an MDB database from one instance of SQL Server to another instance, maintaining the original SDM/Catalog/ITAM server implementation. The instructions may be followed to replace a SQL Server instance that is to be retired.

Environment

Release : 17.x
SQL Server (all supported releases)

Resolution

For SDM

Stop all services connected to the mdb database.
Perform a full backup of the mdb. The most direct way to achieve this is to do the following:
1. Login to SQL Server Management Studio and connect to the source DB Server
2. Right click on the mdb database and choose Tasks -> Back Up.
Make sure to select "Full" under Backup type backup. Select "Disk" under the Destination - Back up to field, and use the default location for the backup location. This is usually C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup. Make sure there is just one single backup file involved.
Copy the full backup of the target SQL Server environment. The most direct way is to transfer the backup file across to the target host server running SQL Server, then copying the backup file to the SQL Server default location for backups. This is usually a location such as C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup
Restore the database in the target environment. On the target environment, right click on Databases and choose Restore Database. Select the Device radio button, click on the ellipsis icon, then add the backup file that was previously copied into the target server.
Configure the "mdbadmin" user in the target SQL Server.

The following is ideally performed with two separate SQL Server Management Studio sessions, one connected to the original SQL Server, the other connected to the target SQL Server.
1. If the mdbadmin user does not exist on the target SQL Server, create the "mdbadmin" user in SQL Server, under Security folder, Logins. Right click Logins and choose "New Login". If the mdbadmin user is already present, continue to the next step.
2. On the original SQL Server instance, under Security folder, Logins, right click the mdbadmin user and choose properties. Do the same on the target SQL Server.
3. Go down each entry of the Login properties of the original SQL Server for the mdbadmin, setting the same values for the mdbadmin on the target SQL Server. Make sure that the target SQL Server's version of the mdbadmin user is set to use the same password as in the original SQL Server. Most importantly, make sure under the Status page on the target SQL Server that the Login is set to "Enabled".
Run the following query against mdb database in target:
```
sp_change_users_login 'AUTO_FIX','mdbadmin' 
```
For SDM: run pdm_configure and point to the target database.

For Catalog

Follow the steps provided in the following link to update the Database host: Update the Database Host, Password, Instance, Service Name, or Port

For ITAM

Follow the steps provided in the following link: Change Database MDB Server for IT Asset Manager/Asset Portfolio Management

Additional Information

The above procedure contains specific instructions on transferring the mdb database. While there are many ways to achieve this task, the above approach was tested in the Broadcom Support lab environment. Please consult with the local DB Admin regarding such activity. Broadcom Support does not endorse a specific database transfer method and is not responsible for providing direct support for a given DB implementation.

If the pdm_configure run experiences any issues during its connection attempt to the new database server, review the checkdb.0 log and configure.log in the SDM install directory log directory

If, after moving your MDB database, SDM is having trouble connecting to it, please check the DNS directory to ensure the hostname of the SQL Server server resolves to the correct IP address.

If, after moving your MDB database, Catalog is having trouble connecting to it, when you restart Catalog after running the setup utility you will see an error like this:

"CA Service Catalog is successfully installed but not configured. Click here to launch the configuration screen and complete the setup process.
Note: If you have already configured CA Service Catalog, restart the service for the changes to reflect."

If this happens, do the following:

In the config.properties file, change the value of the setup.utility.active setting to "true" and save the config.properties file.This file is found in 2 different folders:

<Service Catalog Install Folder>
<Service Catalog Install Folder>\view\conf

The setup.utility.active setting is in both of these files, so be sure to update the setting in both places.