Does SQL Data Compare support External Tables?

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.
Tagged:

Answers

  • 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.


    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • rgbenrgben Posts: 4 New member
    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.
  • 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!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • rgbenrgben Posts: 4 New member
    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.
  • 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!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • rgbenrgben Posts: 4 New member
    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!
Sign In or Register to comment.