Database Management

One of the great things about SmarterTrack is that it can grow and adapt to the needs of any business. By default, SmarterTrack can be installed on SQLite, which is a small, fast, feature rich, and highly reliable SQL database engine. However, there may come a time when its lightweight footprint isn't enough for a growing organization. When that time comes, SmarterTrack can help transition your backend database to something more robust, like Microsoft SQL or even MySQL.

SmarterTrack can even help you move from one database to another. For example, if you want to remove the potential expense of Microsoft SQL and move to MySQL, SmarterTrack can help with that as well.

To access this section, log into the management interface with an administrator account. Select Settings from the Navigator, then click on Database Management in the navigation pane. The settings will load in the content pane and the following will be available.

Database Info

By default, the basic information about the database being used by SmarterTrack will be displayed on the page. This includes:

Database Name - The name of the database being used. (NOTE: This is only displayed when using MSSQL or MYSql.)
Database Size - The current size of the database.
Current Database Type - The type of database being used: SQLite, Microsoft SQL Server, or MySQL.
Table Name Prefix - The prefix being used by the various tables in the database. By default, SmarterTrack tables use an "st_" prefix. (E.g., st_agents.)

Convert Database

Note: Converting your database can take some time, depending on its size and total number of rows. For example, the SmarterTrack database for the SmarterTools installation was over 11GB in size and took approximately 60 minutes to convert from MSSQL to MySQL. In addition, during the conversion, all SmarterTrack services are stopped. Therefore, you will not be able to interact with SmarterTrack as the conversion runs. (Nor should you.)

In order to convert your existing database to a new database type, use the Convert Database button. This will allow you to select the database you want to use moving forward. (By default, your existing database type will not be available.) Clicking that button opens a modal with the following options, based on the database type being converted to:

note: You will need to install and set up whatever database server you're converting to as the Convert Database button will not install the new database software, just convert your existing database to the new format. You will also need to set up any target databases you plan to use: one for testing the conversion (should you so desire), and another for the actual production database.

MySQL

Database Type - MySQL
Server Name - The name of the new server that will host the database.
Username / Password - the username and password used to authenticate to the new MySQL database.
Database Name - By default, the wizard defaults to "SmarterTrack", but this can be changed as needed.
Table Name Prefix - By default, the wizard defaults to "st_", but this can be changed as needed.

Microsoft SQL Server

Database Type - Microsoft SQL Server
Server Name - The name of the new server that will host the database.
Authentication The type of authentication used to connect to the database: Integrated Authentication, Windows Authentication, Microsoft SQL Server Authentication. (Check your documenation for the best authentication type for your needs.)
Database Name - By default, the wizard defaults to "SmarterTrack", but this can be changed as needed.
Table Name Prefix - By default, the wizard defaults to "st_", but this can be changed as needed.

SQLite

Based on how SQLite performs, SmarterTrack will use default values for converting to this database type. Therefore, there's nothing that needs to be configured.

Testing and Finalizing the Conversion

It's advisable that, when testing, you create a secondary database on your platform of choice (MySQL or MSSQL) called "test_conversion" or something similar, in addition to the database you intend to use for production. This allows you to run a test of the conversion and not impact the database you want to use for production.

Testing the Conversion

At the bottom of the Convert Database modal is a Testing checkbox. Checking that, then clicking the Start button will run a test of the conversion. Once the Start button is clicked, the conversion starts up and a label appears on the Database Management page showing the status of the conversion. For example:

When completed, a toast message appears displayed on the Convert Database page letting you know the status of the test. It will either show success or display any errors you encountered.

Finalizing the Conversion

After you run your test conversion, you will need to re-run the conversion to the actual database you plan on using for production. This time, however, use the name (or hostname) of the database you want to use, then click the start button. The process will re-run and, once completed, all services are restarted and the new database will be in use moving forward.