Harvest - how to migrate the SQL Server database

Products

CA Harvest Software Change Manager

Issue/Introduction

What is the correct procedure to move my Harvest database to a new SQL Server database server?

Environment

Release : 14.0

Resolution

In this example, we will assume that the Harvest Server software and the MSSQL Client installations will remain the same on the broker machine. The main thing will be to configure Harvest to connect to the Harvest database in its new location.

1. On the broker machine, shut down all SCM processes

Go to Control Panel -> Administrative Tools -> Services and stop the CA SCM Broker Service

Go to Task Manager and kill the rtserver.exe process

2. On the old MSSQL server, log in to SQL Server Management Studio. Expand the Databases node, right-click on the harvest database, and select Tasks -> Back Up…

On the Backup Database window ensure the correct database is selected, the Backup type is “Full,” the Backup component is “Database”, the Backup option is “Disk,” and a valid folder location and the name is selected for your database backup file. Click OK.

You will get a message that the backup has been completed successfully.

3. Locate the resulting backup file and copy it to the new MSSQL server.

4. On the new MSSQL, log in to SQL Server Management Studio. Right-click on Databases and select “Restore Database…”

On the Restore Database window select the “Device” radio button then click the “…” button to navigate to the folder where your backup file is stored.

On the Select backup devices window, ensure “Backup media type” is set to “File” and click the “Add” button.

On the “Locate Backup File” window, navigate to the folder containing your backup file, select the backup file, and click “OK.”

Back on the “Select backup devices” window, click “OK” to confirm.

Back on the “Restore Database” window, click “OK” to confirm.

You will receive a message when the database has been successfully restored.

5. One more thing to do in SQL Server Management Studio on the new MSSQL server is to add the userid that Harvest will use to connect to the database. In the SQL Server Object Explorer, expand “Security.” Right-click on “Logins” and select “New Login…”

In the Login Name field, type in the name of your Harvest database user. Select “SQL Server authentication” and provide the password your new userid will use to connect to the Harvest database. It is recommended to uncheck the “Enforce password expiration” and “User must change password at next login” checkboxes.

While still in the Login-New window, under “Select a page,” click on “Server Roles.” Ensure that “dbcreator” and “public” Server Roles have been selected.

While still in the Login-New window, under “Select a page,” click on “User Mapping.” Select the “harvest” database in the “Users mapped to this login” list, and below, under Database Role membership, select “db_owner.” When all is ready, click “OK” to confirm.

Your database has been successfully moved and configured. We will now configure Harvest to connect to your database in its new location.

6. On the broker machine, run hdbsetup "CO" option to configure ODBC data source for the new Harvest database and the “EP” option to update the hsrvr.dfo encrypted password file with the Harvest database login credentials.