Compare two identical table structure on different database for different data

Question

I know this question is similar to a previously answered question such as

How to compare data between two table in different databases using Sql Server 2008?

But my concern here is, what if the two databases are in DIFFERENT locations? How would the SQL query look like?

To clarify, I have two MySQL servers, let's name the first server MONITOR, and the second server SNAPSHOT. Server MONITOR is used and maintained by a third party inventory management software. Server SNAPSHOT is my custom MySQL server that stores MONITOR's key tables and makes comparisons. Both of the servers have the same database named INVENTORY and a table named HARDWARE.

The SQL command in the above two links is exactly what I am striving for, however, since the databases are on two different servers, both requires different username/logins, which makes the SQL command fails to execute, and I'm seeking if there's a solution to this.

Also, another question being, in the case of both tables having large amounts of data (say over a million entries), would it be recommended for me to dump the MONITOR's table into SNAPSHOT's database first, and run the comparison query on the SNAPSHOT server?

PS: I would like both a PHP/PDO and a SQL solution, just to compare which method is much suited for a large scale process, if possible.

Questions that people might ask:

Q: Why don't you make both databases on MONITOR?

A: Because we wouldn't want if MONITOR goes down, the platform which uses SNAPSHOT's comparison goes down with it. Also, since MONITOR is generated and maintained by a third party software, we would like to interact as little as possible with it.

Q: Can't you write a trigger when MONITOR's data got updated and forward that to SNAPSHOT?

A: As mentioned above, we prefer not to touch the database generated by the third party to prevent possible errors/corruptions.

You could create linked servers and do the monitoring as a separate third party service. — Anthony Horne, Jun 14 '17 at 04:20
On Something like MS SQL, create a linked server to each MySQL (I do that with redmine - for SSRS reporting). See answer below for linked server creation. — Anthony Horne, Jun 14 '17 at 09:50

score 0 · Answer 1 · answered Jun 14 '17 at 09:53

After installing the "MySQL ODBC 5.2 ANSI Driver".

Add the below procedure (making it easier to add additional servers)

    --http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx
    ALTER PROC uspCreateLinkToMySQLDB @linkedservername varchar(50), @mysqlip varchar(50), @dbname varchar(100), @username varchar(50), @password varchar(50) AS
    --@linkedservername = the name you want your linked server to have
    --@mysqlip = the ip address of your mysql database
    --@dbname = the name of the mysql database you want to operate against. Without this, some of the features of openquery fail
    --@username = the username you will use to connect to the mysql database
    --@password = the password used by your username to connect to the mysql database

    BEGIN
    --DROP THE LINKED SERVER IF IT EXISTS
    IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @linkedservername)
    EXEC master.dbo.sp_dropserver @server=@linkedservername, @droplogins='droplogins'

    --ADD THE LINKED SERVER
    DECLARE @ProviderString varchar(1000)

    Select @ProviderString = 'DRIVER={MySQL ODBC 5.2 ANSI Driver};SERVER=' + @mysqlip + ';Port=3306;OPTION=3;DATABASE=' + @dbname + ';'

    EXEC master.dbo.sp_addlinkedserver 
    @server=@linkedservername, 
    @srvproduct='MySQL',
    @provider='MSDASQL', 
    @provstr=@ProviderString

    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'collation compatible', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'data access', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'dist', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'pub', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'rpc', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'rpc out', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'sub', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'connect timeout', @optvalue=N'0'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'collation name', @optvalue=null
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'lazy schema validation', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'query timeout', @optvalue=N'0'
    EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'use remote collation', @optvalue=N'true'
    EXEC sp_addlinkedsrvlogin @linkedservername, 'false', NULL, @username, @password
    END

Now run "uspCreateLinkToMySQLDB 'HLP_BB', '172.19.49.181', 'bitnami_redmine', '{Your Mysql User}', '{Password of Admin}'"

Success!

Query from MySQL:

select * from HLP_BB...{Whatever table}

NOTE: Remember that mysql user permissions is often linked to the IP address, so be sure to change / check that based on where you are accessing it from.

Then you can link and test. Using some temp table or in-memory tables may help to mitigate the delays or limit the data coming from the other servers / databases.

Compare two identical table structure on different database for different data

1 Answers1