What is the correct procedure to move my Harvest database to a new SQL Server database server?
Release : 14.0
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 login 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 Back up to option is “Disk” and a valid folder location and name is selected for your database backup file. Click OK.
You will get a message that the backup has completed successfully.
3. Locate the resulting backup file and copy it to the new MSSQL server.
4. On the new MSSQL login 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 you new user id 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 new Harvest database, and “EP” option to update the hsrvr.dfo encrypted password file with the Harvest database login credentials.
7. Restart broker
Use Control Panel -> Administrative Tools -> Services to start the CA SCM Broker Service
Check Task Manager and for bkrd.exe, hserver.exe and rtserver.exe processes
From a command prompt window execute the following to confirm connectivity to the broker:
hgetusg -b %COMPUTERNAME% -prompt -cu & type hgetusg.log
(or you can try to connect with Workbench)