Purpose:

The purpose of this guide is to assist with migrating SafeConsole On Premise from using the built in database solution to utilizing MySQL OR MariaDB. Depending on the SafeConsole On Premise usage, these alternative database solutions can improve the resiliency of your SafeConsole. If any questions arise, please contact support by email at support@datalocker.com / by phone at 913-310-9088.


This guide does not cover the full configuration of SafeConsole or the full setup of MySQL/MariaDB. The below steps assume SafeConsole and MySQL/MariaDB have been installed and have the connection configuration(s) completed.


Prerequisite(s):

SafeConsole On Prem version 5.8.3+

    - Administrative access on host for SafeConsole service

MySQL DB (Tested with version 5.7.18) OR MariaDB (Tested with version 10.5.8)

    - Configuration of the server will also need to be known as communication setup may vary

    - Username & Password to access


Expected Downtime:

The migration process depends on the size of your SafeConsole database. The migration is expected to move roughly 500 lines per second. For example, if you have 10000 user logs and 750 admin logs (total 10750 logs) you can expect these logs to be moved in 21.5 seconds. Keep in mind this is an estimate to assist in pre-planning. As there are multiple tables and other factors to consider, this estimate may differ from your experience.


Process:


MySQL Pre-Config 


Using MySQL Shell - Enter the following commands:

\sql   #Switches to SQL mode

\c <user>@<host>:<port>   #e.g. '\c root@localhost:3306'

Enter password:   #Enter the password for your user/root account

CREATE DATABASE<database-name>   #e.g. 'CREATE DATABASE safeconsole'

<Ctrl-C>   #Exits the shell

NOTE: You do not have to specify tables for the schema as the migration tool will perform this.

Using MySQL Workbench - Follow the below steps:

1. Database->Connect to Database OR <Ctrl+U>.

2. If the connection is not filled out, ensure appropriate information is input (e.g. Connection Method, Hostname, etc).

3. When prompted, put in the appropriate password.

4. Enter 'CREATE DATABASE <database-name>;' in the query pane and click the *Lightning Bolt* to run OR Click *Create new Schema* and input the desired DataBase Name before clicking "Apply".

OR

5. Exit the MySQL Workbench.

NOTE: You do not have to specify tables for the schema as the migration tool will perform this.


MariaDB Pre-Config


Using MySQL Shell: 

You can utilize the MySQL shell to interact with a MariaDB instance, if preferred. The commands will be the same as were used in the MySQL Pre-Config section above.


Using HeidiSQL:

MariaDB uses HeidiSQL for it's workbench functionality by default. Follow the below steps to set up your database using the HeidiSQL workbench.

1. Click "New" -> Fill in the appropriate connection settings -> Click "Open".

2. <Right-Click> in the left pane -> Click "Create new" -> Click "Database".

3. Input your desired database name and Click "OK".

4. Exit the HeidiSQL workbench.

NOTE: You do not have to specify tables for the schema as the migration tool will perform this.


SafeConsole Migration Steps:

NOTE: This process will take down the SafeConsole service for the duration of the transfer. Please plan accordingly.

1. On your SafeConsole server -> Open Start -> Search for and run Configure SafeConsole.

2. When prompted, Stop the SafeConsole service.

3. You do not need to change any information on Steps 1 & 2 -> Click Continue until Step 3.

4. Select "MySQL external server" from the dropdown.

5. Input the appropriate information in the following fields:

Host: Default is "localhost"

Port: Default is "3306" (Both MySQL and MariaDB use this as default)

Database Name: Default is "safeconsole" (input the name saved during pre-config)

Username: Default is "root"

Password: Default is BLANK

NOTE: If your organization requires SSL communications to your Database server, configure the SSL certificate by checking the "Connect with SSL" option and Importing the SSL Certificate.

6. Click "Test connection" and ensure that you get a successful response.

7. You do not need to change any information on Steps 4 & 5 -> Click Continue. 

8. When prompted to start your SafeConsole service, Click "No".

9. Download the batch file at the bottom of this article and save this to your "C:\Program Files\safeconsole\db\" folder.

*Example of the contents of the batch file*

@echo on
cd /d %~dp0
cd ..\lib\
..\jre\bin\java.exe -d64 -Dcatalina.base=../ -cp * -Dfile.encoding=UTF-8 com.blockmaster.manage.utils.DBTools --type=hsqldb --src=jdbc:hsqldb:file:../db/ConsoleDB --user=sa --all-tables
PAUSE

10. Run the batch file as Administrator. You will see the results of the database copy as it progresses through the results.

11. Once this completes you will see "Press any key to continue . . .", Press a key to close the window.

12. Open the "SafeConsole Configurator" again.

13. Press next through the steps and when prompted "Do you want to start SafeConsole", Click "Yes".


Your SafeConsole will now start utilizing your MySQL OR MariaDB. This may take a few moments.