Page History

...

• Microsoft SQL Server Migration Assistant 7.6 for MySQL
• MySQL ODBC Driver (This is optional as you are also prompted to download the driver when installing the SSMA)

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.

...

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.Image Removed

Image Added

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.Image Removed

Image Added

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.

Image Added

You may get the following error message when connecting.Image Removed  Just click "Yes"

Image Added

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

Image Added

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

Image Modified

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.

Image Modified

"Target Schema" should now be updated as below.

 Image Modified

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.

Image Modified

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

Image Modified

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.

...

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

Image Modified

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:

...

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

Image Modified

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.

...

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.

Image Modified

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

...

<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" 
testOnBorrow="true" 
maxTotalmaxActive="1000" maxIdle="5" driverClassName="com.mysql.jdbc.Driver"/>

...

<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>"
testOnBorrow="true" 
maxActive="200" maxIdle="10" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>

...