MySQL and MsSQL real-time automatic data synchronization, mysqlmssql

MySQL and MsSQL real-time automatic data synchronization, mysqlmssql
SyncNavigator enables remote database synchronization, resumable data transfer, and heterogeneous data synchronization.

Recently, I encountered a problem about how to share, export, and import data between php and asp programs, so I have been studying Database Synchronization. I have checked a lot of information on the Internet and asked many people, A Method for copying snapshots is found. After some research, we found that this method was previously used, and the effect was not very good. We decided to give up. After some searching and other people's advice, I finally learned a very useful software, SyncNavigator, from an enthusiastic netizen.

<Special thanks to the technical support provided by pig that QQ users will crawl>

Good things will be shared with you, so today I will introduce you to this software and some usage methods. Next, let's take a look at its powerful functions!

Basic Features of SyncNavigator:

Automatic Data Synchronization/timed data synchronization
Both real-time synchronization, 24-hour uninterrupted synchronization, and scheduled automatic synchronization based on scheduled tasks (hourly, daily, weekly, and so on) are fully qualified.

Full support for Microsoft SQL Server
Fully supports Microsoft SQL Server 2000 2005 2008 database types. Data can be synchronized between different database versions.

MySQL 4.1 and later versions are supported.
MySQL 4.1 5.0 5.1 5.4 5.5 is supported. Data can be synchronized between different database versions.

Unattended and automatic fault recovery
When a database or network fault occurs, no manual intervention (or operation) is required to automatically resume synchronization and ensure that the data is completely accurate and reliable.

Homogeneous Database Synchronization/Heterogeneous Database Synchronization
SQL Server to SQL Server, MySQL to MySQL, SQL Server to MySQL, and so on can be easily implemented.

Resumable upload and incremental Synchronization
After the synchronization is completed (or interrupted), you can continue the last incremental synchronization to avoid the need to start from scratch.

 

Create a synchronization Project

After the installation is complete, there will be two icons:

1. Click the "SyncNavigator (client)" icon to enter the system.

2. Enter the address of the connected server in the logon interface and click "OK" to start the connection.

• Note: This is not to log on to your database, but to the management end of the software.
• By default, you can directly click the "Connect" button (the local machine has been installed by default ).
• The default logon username is "admin" and the password is blank.
• The local server address is 127.0.0.1.
• You can use the domain name or IP address as the server address.

 

3. Switch to the "synchronization management" Panel and click "new" to create a synchronization project.

 

4. Switch to the "source database" tab. Enter the synchronized source database information. (The database that stores the data to be synchronized is called the source database)

• Generally, you only need to enter the database address, database name, login user name, and login password.
• Database address: Address of the machine where the source data is located. It can be a domain name or an IP address.
• Database Name: name of the source database. If you have already specified the default logon database, it can be blank.
• Logon Username: the username used to log on to the database server. If you need to log on with a Windows Account, change the "Logon mode"
• Connection test: test whether the input information is correct. If the connection fails, a specific error message is returned.

5. Switch to the "target database" tab. Enter the target database for synchronization in the same way.

 

You can select an existing database as the source database, or create a new database using the software. The steps for selecting an existing database are roughly the same as those for creating a database:

• You only need to create a new target database. The software automatically creates a maximum compatible table structure.
• If you want the structure of the target database to be exactly the same as that of the source database, you only need to create an empty table structure in the target database.
• When you use MySQL as the source database, the synchronized table requires at least one primary key.
• The target database should not contain foreign key constraints because it may cause some data to fail to be synchronized.
• If your target database is incompatible with the source database structure (such as field type and constraints), some data may not be synchronized.
• If you set the source database to 192.168.1.2 and the target database to 192.168.1.3, the data will be synchronized from 192.168.1.2 to 192.168.1.3. If your direction is set incorrectly, data may be overwritten.

6. Switch to the "synchronization content settings" tab. Select the database table to be synchronized.

• To set the specific content of each table, click "Detailed Settings" to adjust the content.
• You can set filter conditions. For example, you can only query table records with scores greater than 90, or hide fields that do not need to be synchronized.
• If no table is selected in this step, NO content is synchronized.

 

7. Click "OK" to complete the project settings.

8. Switch to the "General console" Panel and select the project to start synchronization. Click "start" to start database synchronization.

 

 

 

 

 

 

Automatically synchronize database data

Use HKROnlineSyncNavigator to automatically synchronize database data. Similar to Google Calendar and Hotmail calendar, you only need to set the time for the synchronization task to run automatically at the specified time.

1. on the "synchronization management" Panel, select the project to be set and click "modify" to start setting.

 

2. Switch to the "schedule Settings" tab and click "new" to create a new run plan.

 

3. specify the time to run. Click "OK" to complete the settings.

• Start date: the scheduled start time. It can be set to a later time point to indicate future execution.
• Once: Only one synchronization task is executed. The plan becomes invalid immediately after execution.
• Daily: runs at the specified time every day. To run the task multiple times every day, check the "Duplicate task" check box and set the interval.
• Weekly: runs at the specified time every week. If you do not need to run at the specified time, you can cancel the specific time check box.
• Expiration date: the task will expire at the specified time. Tasks that expire are no longer executed.
• Use this plan: indicates whether the plan is valid. If you cancel this check box, scheduled tasks will no longer be executed.

 

4. Click "OK" to save the current settings.

• The expected next run time column shows the next automatic run time of the synchronization task.

 

 

 

Start/stop server services

1. Click the "SyncNavigator (client)" icon to enter the system.

2. Click "cancel" on the logon page and then click "local service.

 

2. After the interface is opened, the green area in the figure shows the startup status of the current service. Click the button in the area to switch to "start" or "stop ".

• Started: the server has started. You can run Database Synchronization tasks and process client commands normally.
• Stopped: the server has stopped. All synchronization tasks are not run, and client commands are not processed.

 

 

 

 

 

SQL Server automatically synchronizes data to MySQL

This article describes how to automatically or periodically synchronize data from SQL Server to the MySQL database (or automatically or synchronously from MySQL to the SQL Server database ). It can be easily implemented in just a few steps.

1. Click "new" in the "synchronization management" Panel to create a synchronization project.

 

 

2. Switch to the "source database" tab. Enter the synchronized source database information.

• Select SQL Server 2000/2005/2008 for the database type.

 

3. Switch to the "target database" tab. Enter the target database for synchronization in the same way.

• Select Mysql 4.1 5.0 5.1 5.4 5.5 as the database type
• When you synchronize data from MySQL to SQL Server, each table requires at least one primary key (not required from SQL Server to MySQL)

4. Switch to the "synchronization content settings" tab. Select the database table to be synchronized.

 

5. Switch to the "schedule Settings" tab to create a new run plan.

• If automatic synchronization is required (for example, 24 hours without interruption), select automatic.
• If you need timed synchronization, click the corresponding option.

 

 

6. Click "OK". The setting is successful!

 

Note: When you use the software to automatically create a table structure, problems may occur when synchronizing data from MySQL to SQL Server:

1. For the DateTime type of MYSQL, the value '2017-1-0' may exist. It is incompatible with the DateTime value range of SQL Server.

DateTime type to DateTime2.

2. MySQL UTF-8 character set database, when synchronizing to SQL Server, you may need to change the SQL Server varchar type to nvarchar type.

 

 

 

 

Improve Database Synchronization speed and Performance

The default synchronization project configuration of SyncNavigator is only applicable to small and medium databases. When you need to synchronize large and super large databases, perform the following settings to significantly improve the Database Synchronization speed.

1. on the "synchronization management" Panel, select the project to be set and click "modify" to start setting.

 

2. Switch to the "synchronization parameter settings" tab.

 

Parameter introduction:

• Automatic Optimization of network transmission throughput: allows software to dynamically adjust the amount of data transmitted during Synchronization Based on the table structure. We recommend that you select.
• Round-Robin query cache size: We recommend that you set it to 2048 or above when using a large database.
• Data receiving cache size: We recommend that you set it to 2048 or above when using a large database.
• BLOB cache size: when using a large database, we recommend that you set it to 50 or more.
• Cache database structure: After you officially deploy a project, you can check this option to increase synchronization speed.
• Optimize super large databases: After you officially deploy the project, you can select this option to increase synchronization speed.

This blog introduces many features of SyncNavigator, and many other features are not listed one by one, leaving some opportunities for comrades in need to explore on their own. I hope this blog will be helpful to people who have the same requirements as me!

[Appendix]Software Download