This article explains how to migrate a current installation of DualShield using a MySQL installation over to using Microsoft SQL Database. Some external tools must be installed before starting this procedure, links are below for these required tools.

These can be installed anywhere providing the machine has network access to both target databases,  typically on the machine where DualShield is installed. The following pictures were taken from the installation on SQL server. The ODBC Driver must then be registered in ODBC Data Source Administrator under the "System DSN" category.


Before Starting this procedure, Ensure that the Dualshield service is stopped on all DualShield Servers.

SQL Server Migration Assistant (SSMA) for MySQL - Setup

Load up the SSMA for MySQL and start by creating a new project from the "File" menu. Select your destination SQL server version from the "Migrate To" drop-down menu. For this guide, "SQL Server 2017 (Windows) - Preview" has been used.

Once that is done, Click the button   at the top of the window. The connection dialog box for MySQL will appear, fill out the appropriate connection details of your existing MySQL server.

Note. You will need to have an MySQL user account with sufficient privileges for this step, if you do not, refer to this link for how to accomplish this. Generally we can use the credential configured in server.xml MySQL Connection String.

Click the "Connect" button and if successful, you should see text in the Output window stating "Connection to MySQL established successfully". You may need to allow the remote access to MySQL, Please check MySQL: How to allow remote connection to mysql.

Next, connect to the Microsoft SQL server by clicking the  button. Fill out the MSSQL server connection details, making sure to use the Database: Dualshield and using either Windows/SQL Authentication. However, as later on when we configure DualShield, we will require an SQL Authenticated Account so it may be desirable to set this up now. Refer to this link on how to do this.

You may get the following error message when connecting, go ahead and click "Yes" so the target Dualshield database will be setup.

Again, after connecting, you will see an Output confirming successful connection.

Once both database connections have been setup, we can now start the migration process. Begin by selecting the "Dualshield" database from the "MySQL Metadata Explorer".

Now with the "Schema Mapping" tab selected, click the "Modify" button. On the new popup "Choose Target Schema" window, modify the "Target schema" line to be dualshield.dbo and click "OK" to save. E.g.

"Target Schema" should now be updated as below.

 

Now with Dualshield still selected in the list of databases, click the  button to start the schema conversion process. During the conversion, you may see errors

Once conversion has reported it is done in the Output window, proceed to the "SQL Server Metadata Explorer" and find the newly generated schema under <MSSQLHOST> > Databases > dualshield > Schemas > dbo.

Right-Click the dbo schema and then select "Save as Script" from the context menu.

Save this file (dbo.sql) in an accessible location as we will need it for the next step. Leave the SSMA tool open as we will need to come back to it later on to migrate the data over.

DualShield MySQL to MS-SQL Migration Tool

Even though we have the converted Schema from the SSMA tool, we will still need to modify it before we can generate the table structure in the new SQL database for DualShield. We have a tool to automate this process which requires Java to be installed, however you can utilise the java distribution that comes with DualShield if you wish.

The tool is available at our downloads page, in the "Tools" section.

To run the tool on your DualShield server, copy the DSSchemaConvert.jar to your DualShield's jre\bin folder, typically located at C:\Program Files\Deepnet DualShield\jre\bin. Open a command prompt window at that location and then start the tool with the following command:

java -jar DSSchemaConvert.jar

The conversion tool will load up and you should be presented with a GUI.

Click the "Load Schema" button and select the dbo.sql file you generated from the SSMA tool in the last section. You should see "Loading...Done!" in appear in the large white text box if loading the schema file has been successful.

The "MSSQL Schema" and "SQL to run POST Data Migration" buttons will output SQL statements to the text area when pressed. These two SQL statements will be used in the next step to setup the new SQL database using Microsoft SQL Server Management Studio.

Microsoft SQL Server Management Studio - Creating the Dualshield database from the modified Schema

Load up your management studio software on your SQL database server and connect to the SQL server where the dualshield database was created earlier.

Once connected, select "New Query" from the toolbar at the top. Copy in the SQL Output you receive when pressing the "MSSQL Schema" button on the Schema Conversion Tool. Execute the SQL statement and wait for it to finish. This SQL statement is the converted schema from MySQL without any constraints, default values or indexes. The reason we do this as it is required we modify a datatype in most tables later on after the data has been migrated over, to ensure compatibility with DualShield and SQL. However it cannot be done while constraints, default values or indexes are in place so we split the schema creation into two stages. If there are no Errors after executing the SQL statement, proceed to the next step.

SQL Server Migration Assistant (SSMA) for MySQL - Migrating the Data

Before migrating the data over, it may be necessary to empty the log & log_field tables in the MySQL database as this can cause a massive slowdown in the migration process if there is a substantial amount of data in these tables. Run the following two commands on the MySQL database before starting migration if the log tables are too large.

use dualshield;
truncate log;
truncate log_field;


Head back to the SSMA tool and ensure that the dualshield database is still selected in the "MySQL Metadata Explorer". Then select the  button from the tool bar. You will be prompted to re-connect to both databases again, use the same credentials for both that were used in the previous stages. After connecting again, the tool will start the data migration into the new modified SQL schema we have setup already. You will see a lot of console text cycle in the Output window, wait until you a new window appear called "Data Migration Report". Check through the list and ensure you receive a 100% Success rate for all tables before continuing. E.g.

If all tables were migrated successfully, you can go ahead and close the SSMA tool now.

Microsoft SQL Server Management Studio - Adding back the Database Constraints, Default Values and Indexes

Now with the data migrated over to our new SQL database, we will need to apply the final SQL statement generated by the Schema Conversion Tool. Select the "SQL to Run POST Data Migration" button and copy the output into a New Query again in the management studio. Execute the statement and wait for successful completion. If there are no errors, then we are finished migrating the database over now and can proceed to linking Dualshield to use the new SQL database.

DualShield - Swapping Database Connections

Head to your DualShield server and navigate to the DualShield tomcat\conf folder typically located at "C:\Program Files\Deepnet DualShield\tomcat\conf". Create a backup of the server.xml file as we are going to make some changes to it.

Load the server.xml file up in a text editor and navigate to the section detailing the javax.sql.DataSource Resource Type. It will look something like this:

<Resource type="javax.sql.DataSource" name="jdbc/DasDS" validationQuery="Select 1" username="root" 
url="jdbc:mysql://localhost:3306/dualshield?useUnicode=true&characterEncoding=UTF-8" password="password" 
maxTotal="1000" maxIdle="5" driverClassName="com.mysql.jdbc.Driver"/>

This is the MySQL connection string that will need to be deleted and replaced by the below:

<Resource type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" name="jdbc/DasDS" validationQuery="Select 1" username="<USERNAME>"
url="jdbc:sqlserver://<SQL HOST>:1433;DatabaseName=dualshield;SelectMethod=cursor;encrypt=true;trustServerCertificate=true;" password="<PASSWORD>"
maxActive="200" maxIdle="10" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>

Replace the values for <USERNAME>, <PASSWORD> and <SQL HOST> to reflect the appropriate connection details and user account used to interact with your SQL server. Note. The account used here must be an SQL Authenticated account (with Read/Write priveleges) and not using Windows Authentication. Save and Close the file when done.

Now start the Dualshield service up and wait for it to load completely. Check you can access the Management Console and login. Also test a few of your applications that are protected by DualShield to make sure users can login now. If all is working at intended, congratulations! You have successfully migrated DualShield from MySQL to Microsoft SQL!

  • No labels