Does SQL Data Compare support External Tables?

rgben

I need to compare External Tables in an Azure SQL Database with "standard" tables in the same database. Does SQL Data Compare work with External Tables? I do not see them in the list of tables.

DanC

Hi @rgben

I got the following info:

External tables are similar to Linked Servers in on-prem SQL Server - it maintains a connection to an outside database so that you can connect and query easily from within this database.

If compatible - Data Compare can connect directly to the database that the "external table" is actually in - but it cannot use the external table connection to compare data.

rgben

Thanks, Dan, for getting back with me. It sounds like I may be able to connect to the Azure Synapse Lake Database, which is the source for the External Tables in the Azure SQL Database.

Not a bad idea I think my trial has expired, though, so I cannot confirm it will work.

DanC

Hi @rgben

Brilliant, sounds like a plan!

If you get in contact with Sales they can extend a trial license for you to test this!

rgben

I got an extension and connected to the Azure Synapse on-demand instance using authentication type Active Directory universal with MFA.

Everything seemed to go well, but I do not think Data Compare can properly list the databases in Azure Synapse. I got an error message: 'sysdatabases' is not supported.

I think Data Compare is trying to query a sysdatabases object (to populate the Database drop-down menu) which does not exist in Synapse apparently.

DanC

Hi @rgben
 
Unfortunately using Azure Synapse as the source for comparison isn't supported in either SQL Compare nor SQL Data Compare and this will be the issue!

rgben

Hey, so I made a workaround for this recently. I was not working on this for a while, but when I returned, I had a new idea. It is a little tedious, but it works wonderfully.

I do a Select * Into NewTableName From ExternalTableName to copy all data from the External Table into a "real" table in the database. Then I can use the Data Compare tool against the two "real" tables. It works!

So, I will need to use this technique table by table, but that is ultimately a great solution for people with a small table count. And in the case of one of my sample tables, I was comparing 400 columns' data, so the tool was still extremely helpful!