Microsoft Software for Comparing Data from two different Databases

Question

I have performed a migration of systems to Azure and I want to compare if the data cycle performed locally gives the same result as the transformations performed in Azure. To do this, I want to compare if the data obtained in the final tables of the current system is exactly the same as the data obtained in the final tables of Azure. In other words, I want to compare the data from 2 different databases, looking for possible differences record by record. I am looking for a tool to use, if it can be from Microsoft the better. I have tried SQL Server Data Comparer from Visual Studio, but for large tables (10M records) it takes almost 1h. Is there any Microsoft tool, free or paid, dedicated to comparing data between two different databases? Thank you very much!

Answer 1

@ADM. Javier Gaig Mellado Thank you for reaching out. Depending on what you want as the result, there are two different tools. As the first option - use Database compare, it is more flexible in terms of options etc. There is a second newer tool that generates a change script vs. just a log of differences. You can run the tool on a Window platform anywhere, but for better performance, running it on an Azure VM would be best.  The tool link is in the article, or you can download it here: https://www.microsoft.com/en-us/download/details.aspx?id=103016 Hope that helps. Regards, Oury

Answer 2

It is possible that Red Gate's tool works better than the data compare in Visual Studio, but the scenario of comparing two databases that are in different places will always be challenging, since no matter how you do it, a lot of data will have to travel.

You can do it on SQL level with queries like:

SELECT *
FROM  localtbl a
FULL JOIN SERVER.db.dbo.remotetbl b ON ...
WHERE NOT EXISTS (SELECT a.* INTERSECT SELECT b.*)

But again, data from the remote server will have to travel to the local server, and it will not be fast.