Documentation forWeb Help Desk

Prepare the Web Help Desk database

WHD supports the following databases:

  • Embedded PostgreSQL 
  • MySQL
  • Microsoft SQL Server Standard or Enterprise Edition with server collation set to case insensitive.

If your WHD deployment requires database management features such as failover clusters, do not use the embedded PostgreSQL database included with WHD. Failover clusters are not available with the embedded PostgreSQL database.

To prevent database errors in the application, do not modify the default schemas.

If you choose embedded PostgreSQL as your primary database, WHD installs the database on the WHD server during the installation. You can create a backup schedule or initiate a manual backup in the Database Connection page at Setup > General > Database.

If you choose non-embedded, non-default MySQL as your primary database, install the database engine and management tools on a separate server prior to installing WHD. See the MySQL website at https://www.mysql.com for the installation instructions.

Install MySQL on a dedicated drive with at least 20 GB of space to accommodate the database engine, management tools, help desk tickets, and ticket file attachments. After you install MySQL, prepare the MySQL time zone tables.

If you choose non-embedded, non-default SQL Server as your primary database, install the database engine and management tools on a separate server prior to installing WHD. See the Microsoft Docs website for the installation instructions.

Install SQL Server on a dedicated drive with at least 20 GB of space to accommodate the database engine, management tools, help desk tickets, and ticket file attachments. You can also configure WHD to use a new SQL Server database instance on an existing server running SQL Server.

After you install SQL Server on your database server, enable TCP/IP in SQL Server and then create and configure your SQL Server database.

Prepare the MySQL time zone tables

For additional information on how to populate the time zone data, see MySQL Server Time Zone Support and Time Zone Description Tables located on the MySQL Developer Zone website at https://dev.mysql.com.

If you choose non-embedded, non-default MySQL as your primary database, install the database and manually populate your time zone system tables.

You can search for tickets using two new qualifiers: Due Date and First Call Resolution. These qualifiers rely on data located in four MySQL system tables:

  • time_zone
  • time_zone_name
  • time_zone_transition
  • time_zone_transition_type

These tables are created when you install MySQL in your deployment, but are not populated by default with data. WHD requires this data because the Due Date and First Call Resolution qualifier logic is implemented from within the database. If the database is missing time zone data, these qualifiers do not work properly.

When you install your MySQL database, be sure to manually populate these system tables with time zone data. See the MySQL website at www.mysql.com and follow the instructions for MySQL Server time zone support.

You can check the system tables by executing the following query:

SELECT * FROM mysql.time_zone

If the query does not create new table rows, the tables are not populated with data.

Back up the PostgreSQL database

If you selected the embedded PostgreSQL as your primary WHD database, enable the application to automatically back up your data on a selected day and time each week.

All backups are stored by default to the pgsql/13/Backups directory in the WHD home folder. You can also select a custom folder, if required.

In the WHD product manuals, <WebHelpDesk> represents the WHD home folder. The default home folders for the supported operating systems are listed below.

Operating system Location
macOS /Library/WebHelpDesk
Microsoft Windows Server \Program Files\WebHelpDesk

CentOS

Fedora Linux

Red Hat

 /usr/local/webhelpdesk

It is your responsibility to copy the database backup files to a network or tape drive. Perform the database backup before you apply any WHD update.

All database connection settings are applied to the database currently in use. If you select a new database, this database will not be in use until you restart WHD.

  1. Log in to Web Help Desk as an administrator.

  2. Click Setup > General > Database.

  3. Ensure that PostgreSQL is the selected vendor and the Use Embedded Database checkbox is selected.

  4. Select the days of the week and time of day for your automatic backup. If you do not select any days of the week, WHD will not create a backup.

  5. Click Backup Now.

    WHD creates each backup based on your selected schedule. Each backup file is named based on the backup time. For example, a backup performed on July 14, 2022 at 2:30 am would be named B_2022_07_14_02_30_00.

Manage the PostgreSQL database

Each WHD installation includes pgAdmin3—a web-based graphical user interface tool used to perform administration tasks on your PostgreSQL database. This utility is located at:

<WebHelpDesk>\pgsql3\bin\pgAdmin3

If your WHD becomes corrupted for any reason, you can use pgAdmin 3 to restore the database from a backup. You can also connect and view the PostgreSQL database.