Database Management

SmarterTrack ships with SQLite as its default database, which is a solid choice for most smaller installations. It requires no separate database server, no credentials to manage, and no additional licensing costs. For larger or more demanding deployments, however, SmarterTrack also supports Microsoft SQL Server, MySQL, and PostgreSQL — server-grade databases that offer better performance under heavy load, more robust backup and high-availability options, and deeper integration with existing infrastructure.

The Database Management page is where administrators view the current database configuration and, when the time comes, migrate the database to a different engine. To access it, log in with a system administrator account, click the Settings icon, and then click Database Management in the navigation pane.

Database Info

The Database Info tab displays a read-only summary of the current database configuration. The following fields are shown:

Database Name - The name of the database SmarterTrack is connected to. This field is only populated when using Microsoft SQL Server, MySQL, or PostgreSQL. SQLite does not use a named database in the traditional sense, so this field is hidden when SQLite is in use.
Database Size - The current on-disk size of the database.
Current Database Type - The database engine in use: SQLite, Microsoft SQL Server, MySQL, or PostgreSQL.
Table Name Prefix - The prefix applied to all SmarterTrack database tables. The default is st_, so tables are named things like st_tickets, st_agents, and so on. This prefix can be changed during a conversion if needed, for example to coexist with another application sharing the same database server.

Note: If SmarterTrack is licensed for Failover and the current database type is SQLite, a warning will appear on this page. SQLite is not suitable for failover configurations because it is a file-based database that cannot be shared across servers. Migrating to Microsoft SQL Server, MySQL, or PostgreSQL is required before enabling failover.

Convert Database

The Convert Database button opens a modal that walks through the migration to a different database engine. The current database type is not selectable as a destination — you can only convert to a different type. Before starting, the target database server must already be installed, running, and accessible from the SmarterTrack server. The conversion wizard does not install database software; it only migrates the data.

It is strongly recommended to create two databases on the target server before beginning: one named something like SmarterTrack_test for a test run, and one (e.g., SmarterTrack) for the final production conversion. Running a test first against a throwaway database confirms that the migration will succeed before touching production.

Note: During the conversion, all SmarterTrack services are stopped. The system will be unavailable to agents and end users until the conversion finishes and services restart. Plan accordingly — on a large installation, the process can take an hour or more. For reference, an 11 GB SmarterTrack database took approximately 60 minutes to convert from Microsoft SQL Server to MySQL.

MySQL

Database Type - MySQL
Server Name - The hostname or IP address of the MySQL server.
Username / Password - Credentials for a MySQL account that has permission to create and write to the target database.
Database Name - The name of the target database. Defaults to SmarterTrack.
Table Name Prefix - The prefix to use for SmarterTrack tables in the new database. Defaults to st_.

Microsoft SQL Server

Database Type - Microsoft SQL Server
Server Name - The hostname or IP address (and optional instance name) of the SQL Server.
Authentication - How SmarterTrack will authenticate to SQL Server. Three options are available: Integrated Authentication (uses the application pool identity), Windows Authentication (uses a specific Windows domain account), and SQL Server Authentication (uses a SQL Server login with username and password). Consult your database administrator or SQL Server documentation to determine which method is appropriate for your environment.
Database Name - The name of the target database. Defaults to SmarterTrack.
Table Name Prefix - The prefix to use for SmarterTrack tables. Defaults to st_.

PostgreSQL

Database Type - PostgreSQL
Server Name - The hostname or IP address of the PostgreSQL server.
Username / Password - Credentials for a PostgreSQL account with permission to create and write to the target database.
Database Name - The name of the target database. Defaults to SmarterTrack.
Table Name Prefix - The prefix to use for SmarterTrack tables. Defaults to st_.

SQLite

SQLite uses a local file for storage, so no server connection details are required. When converting to SQLite, SmarterTrack uses default values and nothing needs to be configured in the modal.

Testing the Conversion

The Convert Database modal includes a Testing checkbox at the bottom. When this is checked and Start is clicked, SmarterTrack runs the full conversion process against the target database credentials provided, but marks the result as a test rather than switching production over to the new database.

While the conversion runs, the Database Management page shows a live progress indicator that updates every few seconds, displaying the current step and rows processed. When the test completes successfully, two buttons appear:

Switch - Promotes the test database to production. SmarterTrack will restart using the newly converted database. This option is only available for approximately 5 minutes after the test completes; after that it expires and the full conversion must be re-run.
Finish - Discards the test result without switching to the new database, allowing you to re-run the conversion or make adjustments.

If the test conversion fails, the page displays the failure status and error details. Resolve any connectivity or permission issues with the target database and try again before running the production conversion.

Finalizing the Conversion

After a successful test, either use the Switch button to promote it to production, or re-run the conversion without the Testing checkbox checked, pointing to the production database. Once the conversion completes, SmarterTrack restarts automatically and begins using the new database.

Actions

Administrators with Developer Mode enabled will see an Actions menu in the toolbar containing two maintenance utilities. These are advanced operations intended for use when directed by SmarterTools support or when a specific data integrity issue is known to exist.

Repair Ticket Timings

SmarterTrack tracks detailed timing data for every ticket: when it was first responded to, how long it was open, handle time, and so on. This data powers the time-related columns and metrics in reports. In rare circumstances — such as after a crash, an interrupted import, or certain upgrade scenarios — ticket timing records can become corrupted or go missing entirely, causing inaccurate report figures.

Clicking Repair Ticket Timings starts a background thread that scans for tickets with broken or missing timing records and corrects them. Broken timings are recalculated from the underlying ticket event data; missing timing records are created from scratch. The process runs in the background and does not interrupt normal use of SmarterTrack. It also skips running if a database conversion is in progress.

This action is safe to run at any time, but is generally only needed when report data appears inaccurate or when advised by SmarterTools support after an upgrade.

Clean Up Orphaned Data

Over time, certain database records can lose their parent reference — for example, a ticket attachment record pointing to a ticket that was permanently deleted, or a canned reply event log entry whose parent canned reply no longer exists. These orphaned records don't cause visible errors, but they consume database space and can slow down queries that scan the affected tables.

Clicking Clean Up Orphaned Data runs a synchronous cleanup that removes orphaned child records across the following areas:

Autoresponder attachments and autoresponders whose parent brand no longer exists
Canned reply event log entries and language records without a parent canned reply
Live chat comments, event logs, attachments, messages, and custom field data without a parent chat session
Ticket attachments, event logs, comments, emails, messages, custom field data, and merge records without a parent ticket
Form offered and form response records whose parent chat, ticket, form, or user no longer exists
Knowledge base article language records, comments, attachments, and event logs without a parent KB article
Related item links referencing tickets or chats that no longer exist
Search index tokens linked to deleted tickets, tasks, chats, call logs, KB articles, or threads
Community thread posts, scores, tags, and user flags without a parent thread
Survey context records referencing deleted tickets or chats
User comment records whose comment type no longer exists

This operation runs immediately (not in the background) and completes silently. On a very large database it may take a few minutes. As with Repair Ticket Timings, this action is generally run when advised by SmarterTools support or as part of routine maintenance on an installation that has been in use for many years.