In this topic, we will provide a detailed guide on how to compare data between two databases that are hosted on separate servers - SQL Server and MySQL. To achieve this, we will first outline the steps to configure an ODBC MySQL linked server. Then, we will demonstrate how to use this linked server to compare data between the SQL Server and MySQL databases.
Note
The topic primarily focuses on utilizing the Devart ODBC driver for MySQL to establish a connection with a MySQL server and compare data with SQL Server. However, it is important to note that the flexibility of Devart ODBC allows for using any other supported data source as either the source or target. For example, Snowflake can be effectively integrated into the workflow. If you are interested in exploring the full range of available Devart ODBC drivers, you can find a comprehensive list on our website.
ODBC (Open Database Connectivity) is a standard interface that allows applications to access data from different database management systems (DBMSs) using SQL queries. An ODBC Linked Server is a way to connect to a remote SQL Server data source, allowing you to query data from another database system as if it were a local database.
An ODBC Linked Server works by creating a connection to a remote database using an ODBC driver. Once the connection is established, you can execute SQL queries against the remote database as if it was hosted locally. This allows you to join data from multiple sources in a single query, without having to move the data between systems.
Prerequisites:
1.1 Download Devart ODBC driver for MySQL.
1.2 Launch the downloaded file and follow the installation wizard instructions to install the driver.
1.3 Once the driver is installed, open the ODBC Data Source Administrator utility on your machine. You can find it by searching for “ODBC Data Sources” in the Start menu.
1.4 On the System DSN tab, click Add to create a new data source.
1.5 In the Create New Data Source dialog that appears, select Devart ODBC driver for MySQL and click Finish.
1.6 In the Devart ODBC Driver for MySQL Configuration dialog, enter a name for the data source and fill in the required information, including the server name, the port number, the database name, and the user credentials.
1.7 Once you have entered all the required information, click Test Connection to verify that the ODBC driver is properly installed and configured.
1.8 If the test is successful, click OK to save the data source. You can now use this data source to connect to the MySQL server from other applications, such as SQL Server.
To configure the linked server in SQL Server, follow these steps:
2.1 Open SQL Server Management Studio and connect to the SQL Server instance.
2.2 In the Object Explorer, expand the Server Objects node, right-click Linked Servers, and select New Linked Server.
2.3 In the New Linked Server dialog, provide the necessary configurations for the linked server:
Click OK to save the linked server configuration.
2.4 Expand the corresponding node in Object Explorer to ensure that the linking process was successful - you should be able to see the database tables associated with the linked server.
Once you have configured the linked server, you can use it to access the MySQL database from SQL Server.
To work with data from the linked server in SQL Server, you can use standard T-SQL statements to query the data from the linked server. Here’s an example of how to retrieve data from a table in the linked database:
SELECT * FROM [<linked_server_name>].[<database_name>].[<schema_name>].[<table_name>];
When working with object names in SQL Server, they typically consist of four parts. Two of these parts - [<linked_server_name>] and [<table_name>] - are mandatory, while the other two - [<database_name>] and [<schema_name>] - are optional.
MySQL does not have a schema concept like SQL Server. So, when querying data from a MySQL table using a linked server in SQL Server, you would use the following syntax, where [<linked_server_name>] and [<table_name>] - are mandatory, while [<database_name>] is optional:
SELECT * FROM [<linked_server_name>].[<database_name>]..[<table_name>];
3.1 Open dbForge Data Compare for SQL Server, navigate to the Comparison menu, and select New Data Comparison.
3.2 In the New Data Comparison wizard, choose the source and target servers and databases you would like to compare.
3.3 On the Mapping page of the wizard, unmap all the tables.
3.4 Click Custom Query on the toolbar.
3.5 In the Custom Queries Mapping dialog that opens, select Query as a source object and enter the following query:
SELECT * FROM [OLYMPIC_GAMES_PROD].[olympic_games_prod]..[country]
Next, under Target object, select Table or View and then select the country table in the list that will appear. Click Validate to check the mapping. If it is successful, click OK.
3.6 Now, we need to map columns and set a comparison key. For this, select Custom in the Comparison Key dropdown list.
Then, in the Column Mapping dialog that appears, map the source and target columns and select a comparison key.
3.7 Follow the same steps (3.4-3.6) for any additional tables that you want to include in the comparison.
3.8 Once done, click Compare.
After you have compared the data sources, the results are displayed in the upper pane. The grid shows all the objects you can select for synchronization. The objects in a grid are grouped by Type of difference.
Now, you can manipulate the results of the comparison and synchronize the source and target as you usually do.
Note
You can not use the linked server as a target since it is not possible to synchronize the source with the custom query results. You can find a workaround to overcome this limitation here.
While we used an example of a MySQL database in this topic, you can utilize any other data source. The process for configuring different data sources is described in the How to configure Devart ODBC drivers documentation topic. Also, if you want to learn how to compare PostgreSQL and SQL Server databases using Linked Server, feel free to refer to the Linking and Comparing SQL Server and PostgreSQL Databases article.