How To: Point HighGear to a new Database Server

This article will explain how to point the HighGear web server to a new database location. Please follow this process precisely as described in this document. There is no danger of data loss or corruption on the source server, but the destination server will not operate correctly unless the entire process is performed correctly.

Backing up the source server’s HighGear database:

1. Open SQL Management Studio on the source server
2. Open the HighGear database
3. Back up the database

Restoring the database to the new server:

1. Open SQL Management Studio on the destination server
2. Open the HighGear database
3. Restore the backup file into the HighGear database, with the Overwrite option checked
4. On the web server, open the settings.xml file (located in the Program Files/HighGear folder) find the line that starts with <DB_Server>. Change the value to point to the new database server.

Once the database is restored, the database logins must be created/fixed before HighGear will work on the new database server. The instructions for this depend on the version of HighGear that is installed on the web server.

Fixing SQL users for HighGear 8.4 and later:

1. On the web server, navigate to C:\Program Files\HighGear
2. Run the FixSQLUsers.exe tool. It will prompt for the sysadmin credentials for the destination SQL Server and recreate the logins based on the credentials in Settings.xml.

Fixing SQL users for older HighGear versions:

1. Open SQL Management Studio on the destination server and navigate to the restored database.
2. Expand the Users folder
3. Delete all of the users that are there
4. Go to the server’s Security folder, and open the Logins folder
5. You will see three HighGear users: HighGear, HighGear_Admin, and HighGear_Reporting
6. Delete these three users.
7. Create 3 SQL login with the same username as on the original database.
   1. The username for these will most likely be similar to HighGear, HighGear_Admin, and HighGear_Reporting
   2. The password for these users should be the same as development database logins. If these passwords are not available, send the HighGear settings.xml file in development and we can decrypt the password and communicate them over the phone. This file is located in the Program Files/HighGear folder
   3. Go to the User Mapping page
   4. Check the “Map” box for the HighGear database
   5. In the Role Membership section, check db_datareader and db_datawriter
   6. Click OK
   7. Do the same thing for HighGear_Admin, but check db_datareader, db_datawriter, and db_owner
   8. Do the same thing for HighGear_Reporting, but only check db_datareader
8. Log into the database as the admin user and run the following query for each of the usernames
   1. GRANT EXECUTE ON SCHEMA ::dbo TO HighGear
   2. GRANT EXECUTE ON SCHEMA ::dbo TO HighGear_Admin
   3. GRANT EXECUTE ON SCHEMA ::dbo TO HighGear_Reporting

Fixing the BI Module Settings.lgx file:

If the BI Module is installed, you will need to update the Settings.lgx file to point to the new database server. These strings are located in ..\inetpub\wwwroot\Reporting\_Definintions\Settings.lgx. You will need to update the Data Source= value in these 4 lines

1. String starting with <Connection ID=”ahMetadata”…
2. String starting with <Connection ID=”1″…
3. String starting with <Connection ID=”ahLog”…
4. String starting with <Connection ID=”ahRepository”…