Skip to main content
Matrix42 Self-Service Help Center

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. 

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

 

Picture 1.jpg

 

Click connect to MySQL and fill in the required fields:


Picture 2.jpg
 

Click connect to MS SQL and fill in the required fields, making sure to set database name to “EgoSecure”:

 

Picture 3.jpg

 

Select the MySQL EgoSecure database and modify the target schema to EgoSecure.dbo:

 

Picture 4.jpg

Picture 5.jpg

Picture 6.jpg

 

Migrating your data

Now click migrate data having selected just the EgoSecure database:

 

Picture 7.jpg

 

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:

 

Picture 8.jpg

 

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.
 

Picture 9.jpg

 

Database creation script

Below you can find the SQL script for creating the MS SQL EgoSecure database:

DbCreationScript.sql

  • Was this article helpful?