EgoSecure DB migration guide from MySQL to MSSQL
Overview
Starting from version 24.0.0, EgoSecure no longer supports MySQL, and this article will guide you on how to migrate your installation from a MySQL database to a MS SQL database. This guide was written for MS SQL server 2022, and we will detail below where you need to make adjustments if you are using an older version.
Prerequisites
- Your EgoSecure installation using MySQL database is updated to v23.0.3.3. on server side (agents can still run older versions). If you are on a previous version, you need to update to v23.0.3.3 before migrating to MS SQL.
- MS SQL server installation, SQL Server Management Studio (SSMS) and SQL Server Migration Assistant (SSMA)
- MySQL connector ODBC
- MS SQL database creation script (attached to this article)
- In this guide, the assumption is that your MySQL database name is "egosecure". If necessary, change the name of the database to be migrated in MySQL to "egosecure" (without quotation marks). The newly created MS SQL database name will also be "EgoSecure". If you wish to change the name, we recommend to rename it after migration has finished and was verified.
Migration
Before you begin, make sure to perform a backup of your MySQL database, and to stop the EgoSecure server service to avoid ingestion of new data from the agents during the migration. SSL communication certificates will also have to be recreated after migration, alternatively you can also export them before migration and import after the migration is finished.
Creating the MS SQL database
After you installed the MS SQL Server and SSMS, use SSMS (or other similar software) to execute the database creation script. If needed, use a text editor to change the paths on line 7 and 9 to match your MS SQL Server installation. If you are using a different MS SQL version than the one mentioned above, you will also need to change the compatibility level accordingly on line 12. For additional information about MS SQL compatibility levels, please refer to the Microsoft compatibility level table.
Setting up the migration
Install SSMA and the required connector (MySQL connector ODBC). Open SSMA and create a project for your version of MS SQL (2022):
Click connect to MySQL and fill in the required fields:
Click connect to MS SQL and fill in the required fields, making sure to set database name to “EgoSecure”:
Select the MySQL EgoSecure database and modify the target schema to EgoSecure.dbo:
Migrating your data
Now click migrate data having selected just the EgoSecure database:
This process will take some time, depending on your database size.
Connecting to the MS SQL database
Now you need to change the connection string. Open up the AdminTool, change the database settings to MS SQL, and fill in the required fields:
Click Test and then Save. If you get the error below, your installation might be missing some components required for communication with the MS SQL server. If so, open the EgoSecure installer for your version, and use the repair option. The installer might restart the EgoSecure server service, so make sure to block ports on the server before running the installer, to avoid writing of new information in the old database after migration. Once the installer is finished, you can stop the service and then reopen the ports. Now open the AdminTool and you should be able to change the connection string with no errors.
Database creation script
Below you can find the SQL script for creating the MS SQL EgoSecure database: