Compare same Databases and Tables, one is present on the on-premise sql server and the other one present on the Azure cloud

Question

Compare same Databases and Tables, one is present on the on-premise sql server and the other one present on the Azure cloud

Nilesh Patel 111

Want to compare the same databases and tables after the migration from the on-premise SQL server to the Azure SQL database.
Is there any script that can compare all the databases, tables, functions, stored procedures in one go? So that I can make sure all the things are migrated and none of the data is misplaced.

e.g., let say I have 5 rows and 6 columns in a table on on-premise SQL server and after the migration I want to check all the rows and columns are present in the target.

I have large numbers of databases and tables. So, Can't do this manually for each databases and tables, need to automate this so I can save time.

Thanks.

AmeliaGu-MSFT 14,011 Reputation points Microsoft External Staff

2021-12-08T09:47:37.81+00:00

Hi NileshPatel-7672,

Do the answers help you?
If you find any post in the thread is helpful, you could kindly accept it as answer.
If you have any question, please feel free to let us know.

Best Regards,
Amelia
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-12-21T15:58:27.563+00:00

@Nilesh Patel Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Thanks
Saurabh

4 answers

Your answer

AmeliaGu-MSFT 14,011 Reputation points Microsoft External Staff

2021-12-08T09:47:37.81+00:00

Hi NileshPatel-7672,

Do the answers help you?
If you find any post in the thread is helpful, you could kindly accept it as answer.
If you have any question, please feel free to let us know.

Best Regards,
Amelia
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-12-21T15:58:27.563+00:00

@Nilesh Patel Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Thanks
Saurabh

Answer 1

AmeliaGu-MSFT 14,011 Microsoft External Staff

Hi NileshPatel-7672,
You can go to SSDT Tools menu ->SQL Server-> new data comparison to compare data (record to record) between SQL Server databases and Azure SQL databases; and new schema comparison to compare schema (field to field).

Please refer to Use Schema Compare to Compare Different Database and Compare and Synchronize the Data of Two Databases which might be helpful.

Best Regards,
Amelia

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator

2021-12-06T22:03:52.783+00:00

Thanks Ameila for filling. I did actually not know that there was a Data Comparison in Visual Studio. I only have had reason to use the Schema Comparison. And it wasn't clear to me that Nilesh wanted to compare both schema and data.

Re-reading Nilesh's question, I see that he wants to automate this, as there are many databases. I never had reason to compare more than one pair of databases at a time, so I don't know VS offers in this area.

Red Gate are famous for their Schema Compare and Data Compare, but same thing there. I don't know if they lend themselves easily to automation. (But any that has a command-line interface should do, I think.)

Answer 2

Erland Sommarskog 133.7K MVP Volunteer Moderator

In addition to the other suggestions, there is a very good database compare tool in Visual Studio, once you have installed SSDT, SQL Server Data Tools.

Nilesh Patel 111 Reputation points

2021-12-06T06:01:57.707+00:00

Can I do field to field and record to record comparison too?

Answer 3

Alberto Morillo 35,501 MVP Volunteer Moderator

Allow me to recommend SQLDBDiff, this commercial tool that will simplify the post-migration validation test by comparing data and schema between source and destination databases. It supports SQL Server Version 2005 to 2019, SQL Azure and SQL on Linux. There is free version and a commercial version.

Nilesh Patel 111 Reputation points

2021-12-06T06:01:51.367+00:00

Can I do field to field and record to record comparison too?

Answer 4

Olaf Helper 47,616

Microsoft release a new, free tool: Database Compare Utility

Nilesh Patel 111 Reputation points

2021-12-06T05:46:12.517+00:00

Can I do field to field and record to record comparison too?
Homebrew9 26 Reputation points

2021-12-23T12:48:46.417+00:00

Thanks for pointing out this utility, OlafHelper. I had a cursory look at it - seems like it compares hashes of each row for each specified table. And it requires some unique id - primary key or unique constraint on both tables. Wish we had this utility earlier. We have a major ongoing migration project in my organization and I wrote a similar utility in Python.
Definitely interested in how the Microsoft utility develops going forward. Thanks again!

Share via

Compare same Databases and Tables, one is present on the on-premise sql server and the other one present on the Azure cloud

4 answers

Your answer