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.


Please Note: For Microsoft SQL users, the steps to connect should be the same as with the two DB's mentioned below.
We do not provide documentation at this time because SafeConsole does not currently support using an encrypted connection with Microsoft SQL.  


Prerequisite(s):

SafeConsole On Prem version 5.8.3+

    - Administrative access on the 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.


NEW: With SafeConsole v5.9.1 the DBTool has been expanded to support large transfers while the SafeConsole service is running. This is done by skipping the User (and optionally Admin) Audit Logs during the initial migration process, starting the SafeConsole Service, and then migrating the Audit Logs while the service is running. Please review Step 9 of the migration process to consider your unique needs.


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 its 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:

NEW: With SafeConsole v5.9.1 the DBTool has been expanded to support large transfers while providing SafeConsole service. This is done by skipping the User (and optionally Admin) Audit Logs during the migration process, starting the SafeConsole Service, and then migrating the Audit Logs while the service is running. Please review Step 9 and plan for your unique needs.

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. There are three options for migration: Full Migration, Migration Without User Logs, and Migration Without User or Admin Logs. If your console has many (100k+) logs, choosing to skip these initially will help provide SafeConsole uptime while the migration takes place. An explanation and example of each of these options are listed below.


Download the required batch file(s) at the bottom of this article and save them to your "C:\Program Files\safeconsole\db\" folder.


    9a. Full Migration (SafeConsole is down while migration continues)

        *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

    9b. Migration without User Audit Logs (SafeConsole is down for initial migration, SafeConsole is started without User Logs,     and User Logs are imported using the second script)

       *Example contents of each 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 --skip-tables="logentry"
PAUSE
@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 --table=logentry --dst-table=logentry --id-col=id_logentry --dst-id-col=id_logentry  --load-mode=merge --reverse-insert
PAUSE

    9c. Migration without User or Admin Audit Logs (SafeConsole is down for initial migration, SafeConsole is started without 

    User or Admin Logs, and User/Admin Logs are imported using the second script)

       *Example contents of each 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 --skip-tables="logentry,admin_logentry"
PAUSE
@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 --table=logentry --dst-table=logentry --id-col=id_logentry --dst-id-col=id_logentry  --load-mode=merge --reverse-insert
..\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 --table=admin_logentry --dst-table=admin_logentry --id-col=id_admin_logentry --dst-id-col=id_admin_logentry --load-mode=merge --reverse-insert
PAUSE

NOTE: Once you decided which path is best for you, please follow the remaining steps as necessary.

Full Migration (9a):

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

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".

Skip Logs (9b and 9c):

10. Run the 'migrate without logs' batch file as Administrator. You will see the results of the database copy as it progresses.

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".

NOTE: Be sure to run the next step as soon as possible after starting the console. This will ensure the correct order of user logs.

14. Run the 'after live' batch file as Administrator. You will see the results of the log transfer as it progresses. This may take some time.

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

16. Your logs will now be in your SafeConsole.


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