How to move only the mdb database from ms sql server to new ms sql server for SDM, Service Catalog and ITAM

Products

• CA Service Management - Service Desk Manager
• CA Service Desk Manager

Issue/Introduction

We need to migrate the "mdb" database from an SQL Server MS SQL Server to a new MS SQL Server. How to do this?

Environment

Release : 17.x

Resolution

For SDM

1. Stop all services connected to the mdb database.

2. Perform a full backup of the mdb

3. Copy the full backup of the target SQL Server environment 

4. Restore the database in the target environment.

5. In the target environment the database user (example: mdbadmin) will not exist in the environment (Security > Logins) so you would need to manually create the mdbadmin user in the environment by going to Security > Logins > right-click on login > New Login. Ensure all pages when creating the user are exactly the same as in the source environment. IMPORTANT: Please use the same password for the mdbadmin.

6. Restore the mdb database in target.

7. Run the following query against mdb database in target:

sp_change_users_login 'AUTO_FIX','mdbadmin' 

8. Ensure the properties of the mdbadmin are exactly the same on both servers. Same user roles, user mappings, same default db (If you are not using mdbadmin, need to perform steps 5, 7, and 8 for the other database
user being used) 

9. 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

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.