Migrate DualShield from Microsoft SQL to MySQL

This article describes how to migrate a DualShield server with Microsoft-SQL to a DualShield server with MySQL.

1 - Install a new DualShield server with MySQL as the database engine. Make sure that the FQDN of the new DualShield server is identical to the FQDN of the old DualShield server.

2 - On the new server, Install MySQL Workbench

Install VC++ Redistributable Package

https://docs.microsoft.com/en-US/cpp/windows/latest-supported-vc-redist?view=msvc-170

https://aka.ms/vs/17/release/vc_redist.x64.exe

Install MySQL Workbench

https://dev.mysql.com/downloads/workbench/

https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community-8.0.28-winx64.msi

3 - On the new server, create an ODBC Data Source for Microsoft-SQL

Before running MySQL Migration Wizard, we need to create an ODBC data source for MS-SQL

On the target server, launch the ODBC Data Source Administrator

Click Add

Select "SQL Server"

Click Finish

Enter a Name, Description

Enter the address of the source Microsoft SQL server

Click Finish

Click Test Data Source...

4 - Stop the DualShield service on both the old and new servers

5 - Migrate database from the old MS-server to new MySQL using the MySQL Migration Wizard

Unable to render {include} The included page could not be found.

6 - Fix issues after the database migration

Fix 'Alert' table schema

The following 2 fields in the alert table should have been fixed in the process of the migration wizard.

If they were not fixed, due to mistakes in operation, then run the following SQL commands to fix them

alter table alert modify column message longtext;
alter table alert modify column advanced_options longtext;

Below is the corrected scheme of the alert table

Fix 'DatabaseChangeLog' table

Run the SQL command below to fix an issue in the 'databasechangelog' table

update databasechangelog set md5sum=null;

Fix auto increment in all tables

MySQL Workbench has a bug

https://bugs.mysql.com/bug.php?id=96231

that causes auto increment to be missing on all tables

To fix it, run the SQL commands below

SET FOREIGN_KEY_CHECKS = 0;
alter table `activation_code` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `agent` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `alert` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `application` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `application_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `attribute_definition` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `authentication_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `authorization_code` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `certificate_activation_code` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `certificate_authority` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `certificate_request` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `certificate_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `challenge_code` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `configuration` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `connection_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `customization_config` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `customization_template` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `data_cache` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `database_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `deepnet_authenticator` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `dna_auth_session` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `domain` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `dualshield_system` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `enum_value` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `fail_thru_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `history` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `i18n_message` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `identity_source` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `image` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `image_repository` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `internal_user_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `ip2location_db3` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `ip2location_db3_ipv6` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `jvmobservation` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `ldap_server_connection` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `license_group` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `log` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `log_field` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `log_filter` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `logon_procedure` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `logon_step` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `message` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `message_body` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `message_gateway` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `message_template` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `message_template_item` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `network` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `policy_category` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `product` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `product_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `push_notification_gateway` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `qna` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `question` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `radius_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `radius_client` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `radius_profile` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `radius_proxy` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `radius_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `realm` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `report` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `report_data` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `report_result` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `repository` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `role` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `serial_generator` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `server_certificate` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `server_job` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `site_stamp` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `spattribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `sso_federation` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `sso_identity_provider` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `sso_server` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `sso_service_provider` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `store` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `store_folder` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `store_item` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `system_binary_data` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `system_job` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `systicket` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `task` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `tbl_custom_application` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `tbl_policy` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `tbl_user` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `temp_pass` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `token` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `token_assignment` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `token_attribute` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `token_auth_session` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `token_session` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `unit` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `user_certificate` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `user_configuration` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `user_device` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `user_registration_session` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `user_secure_cookie` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
alter table `usergroup` modify column `id` bigint(20) NOT NULL AUTO_INCREMENT;
SET FOREIGN_KEY_CHECKS = 1;

Copy the CA certificate

copy the CA certificate file from the old DualShield server to the new server. It is located at:

C:\Program Files\Deepnet DualShield\jre\lib\security\cacerts

Copy the database encrypt key files

copy the database encryption key files from the old DualShield server to the new server. They are located at:

C:\Program Files\Deepnet DualShield\config\dbenc.jks
C:\Program Files\Deepnet DualShield\config\storeconf.xml

Page tree