Upgrading Schema For a SQL Server Database with HDBSetup

Products

CA Harvest Software Change Manager CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

When upgrading the Harvest SCM server to the next version, two steps are involved. The first step is installing the software, and the second is upgrading the Harvest SCM database schema to the next level. This document explains the second of these two steps, upgrading the Harvest SCM database schema on SQL Server.

Environment

CA Harvest SCM all versions, the Windows platform

Resolution

The first thing to know is if your Harvest SCM Server software is not installed on the same computer with the SQL Server database software, some client components must be installed to enable connectivity between Harvest SCM Server and the database.

SQL Server Management Studio
Microsoft Command Line Utilities for SQL Server

Download links for all these prerequisite components can be found at the bottom of this article.
Ensure you can connect to the Harvest database on the SQL Server server using SQL Server Management Studio and the login credentials for the Harvest database owner

Before beginning the HDBSetup application, you should check to be sure all Harvest related processes have been shut down on the server. The easiest way to do this is to open Task Manager and go to the Processes tab. If you see any processes named "Bkrd.EXE", "hserver.EXE", or "RTserver executable (32 bit)," end those tasks, starting with the "Bkrd.EXE" first, then any "hserver.EXE" tasks and finally the "RTserver" task.

You can execute the HDBSetup application in either of these ways.

At the end of the Harvest SCM Server installation program, it will ask if you want to execute HDBSetup.
- Check the "Launch the Database Configuration Utility" checkbox and click "Finish" to begin.
If you missed the checkbox and the installation program ends, HDBSetup does not start. In this circumstance, you can execute it from a command prompt window.
- Open a command prompt window, navigate to the %CA_SCM_HOME% folder, and execute "hdbsetup.exe"

Once the HDBSetup application has begun, these are the steps to take

1. 1. The first question will let HDBSetup know the type of database you are using. Type "s" for SQL Server and hit the <enter> key.
  2. If your Harvest SCM version is 14 or higher, you will see a second question about whether your SQL Server database is in the cloud. Answer "y" or "n" to this question as appropriate for your installation.
  3. You will see the main menu of the application. We will not use all of these options, but the ones we will use will be in the order listed on the menu. Type "co" and hit the <enter> key to begin configuring the ODBC DSN
    1. You will be asked to enter a name for your DSN. This label references the settings that allow Harvest to connect to your database. The name can be anything you want. Enter the name you prefer, or hit the <enter> key to accept the default value of "harvest".
    2. You will be asked to enter the server name for the computer that is hosting SQL Server and your Harvest database. If SQL Server is installed on the same computer as the Harvest broker software, you can accept the default "(local)." If not, type in the hostname of the computer hosting SQL Server and hit the <enter> key
    3. You will be asked what type of SQL Server authentication you want to use when logging into the SQL Server database. The simplest method is SQL Server Authentication, using the login user ids and password stored in SQL Server. Type "2" and hit the <enter> key to select this option.
    4. You will be asked the name of the database inside the SQL Server that contains all the Harvest data. Type in the database name and hit the <enter> key.
    5. You will be presented with a recap of all the information submitted so far. Review these entries, and make any changes by selecting the number of the option you want to change. When all the information is correct, type "0" and hit the <enter> key to continue.
    6. You will be asked if you want to test the database connection. Type "y" for yes, and hit the <enter> key.
    7. You will be asked for the username and password of the SQL Server user that owns the Harvest database. Provide these credentials and hit the <enter> key
    8. HDBSetup then tries to make the connection to the database. If it succeeds, you will see the message "Connection test succeeded." Hit the <enter> key to return to the HDBSetup menu.
  4. Type "tr" and hit the <enter> key to test for the existence of the Harvest database.
    1. The utility remembers all the information already provided and simply displays the confirmation menu, allowing you to verify if all the information is correct, make any changes by selecting the number of the setting to change, then to continue, type "0" and hit the <enter> key
    2. If the connection test succeeds and a Harvest database is found, you will see the message "SCM Repository exists". Hit the <enter> key to return to the HDBSetup menu.
  5. Type "ur" and hit the <enter> key to upgrade the Harvest database to the new schema level.
    1. The utility remembers all the information already provided and simply displays the confirmation menu, allowing you to verify if all the information is correct, make any changes by selecting the number of the setting to change, then to continue, type "0" and hit the <enter> key
    2. You will see a warning message that the database schema is about to be upgraded and that this change cannot be rolled back. If a problem happens during the upgrade, you must restore your database from the backup taken just before your upgrade and start again. Type "y" to accept the warning and hit the <enter> key to continue.
    3. If the database schema upgrade succeeded you will see the message "Database schema updated." Hit the <enter> key to return to the HDBSetup menu.
  6. Type "ep" and hit the <enter> key to create an encrypted password file with the login credentials for your SQL Server database.
    1. The password encryption utility is executed and you will see a "Encrypted user information saved" message when it is complete. Hit the <enter> key to return to the HDBSetup menu.
  7. All the needed options have been executed. Type "x" and hit the <enter> key to exit the HDBSetup application.
  8. It is always recommended to check the %CA_SCM_HOME%\Log\updateschema.log file to ensure the database schema upgrade was completely successful. Open this file in your favorite text editor and scroll to the bottom. If you see the message "UPGRADE TO v##.##.## COMPLETE" and "DATABASE AT VERSION ######", this means the database schema upgrade succeeded.
  9. We can further verify our success by trying to start up the CA Harvest SCM Broker Service.
    1. We will know if the startup was completely successful if we see the Harvest-related processes in Task Manager.
  10. As a last check, we can try to log in to the broker from Workbench.