Can we reverse Engineer Azure Synapse SQL Databses?

[Paolo F Cantoni]

As we move to the cloud, we need to reverse-engineer Azure Synapse SQL databases.  Is there some secret sauce to do this?  Is it even possible?
If so, could some kind soul "spill the beans"?

TIA,
Paolo

[Geert Bellekens]

I think we used to get a DDL from the db developers, and import that into a local SQL Server database.
Then we do a reverse engineer.

I'm not sure if a direct reverse engineering is possible.

Geert

[MichaelJ]

As we move to the cloud, we need to reverse-engineer Azure Synapse SQL databases. Is there some secret sauce to do this? Is it even possible?

Yes, I believe it is possible since I successfully reverse-engineered Azure SQL DBs using the "Import DB Schema" function found under Develop->Data Modelling->Import. Then on the "..." button (for the DB source), I selected "Direct Native Connection". In the next window, under the "DBMS" option, "Microsoft SQL Server" and "OLEDB" a connection methods. Then specify the usual suspects for a SQL authentication connection, and hit the "Test" button to ensure the connection is good. And away EA went and got information for the tables, columns, stored procedures and indexes.

[Paolo F Cantoni]

As we move to the cloud, we need to reverse-engineer Azure Synapse SQL databases. Is there some secret sauce to do this? Is it even possible?

Yes, I believe it is possible since I successfully reverse-engineered Azure SQL DBs using the "Import DB Schema" function found under Develop->Data Modelling->Import. Then on the "..." button (for the DB source), I selected "Direct Native Connection". In the next window, under the "DBMS" option, "Microsoft SQL Server" and "OLEDB" connection methods. Then specify the usual suspects for a SQL authentication connection, and hit the "Test" button to ensure the connection is good. And away EA went and got information for the tables, columns, stored procedures and indexes.

Thanls, Michael, it seems like it might work if I "get all my ducks in a row".  I'm having login problems with the synapse DB.  I'll have to check with the infrastructure group to ensure I have access.

Paolo

[MichaelJ]

I'm having login problems with the synapse DB.  I'll have to check with the infrastructure group to ensure I have access.
Paolo

No worries, happy to help. A few pointers include (1) the connection string may need to have a "tcp:" prefix in front of the server name (at least, for SQL Server), for example "tcp:server.<database>.azurewebsites.co.au"; (2) in addition, check to ensure the allowed tcp port number (usually 1433); (3) check any virtual networks, firewalls, private IP addresses, or custom domain names setup for networking requirements to host the Azure Synapse DB; (4) last, check the username/password combo ;-)


M

[Paolo F Cantoni]

Thanks for all the help.
We managed to get Database Builder to see the DB.  However, as we finally realised, this is a Databricks entity,` and all the tables are SQL Server External Tables and thus not amenable to Reverse-engineering via ODBC (AFAIK).

Does anyone know of any mechanism to get at SQL Server external tables with Database Builder?

We ended up copying and pasting the INFORMATION_SCHEMA.COLUMNS into our MS Access Helper application and processing that to create the EA objects and relationships we needed behind the scenes.

Paolo

[Richard Freggi]

Hello Paolo I'm curious if you were able to reverse/forward engineer Databricks schemas using EA?  Because I may need to in near future and I prefer Sparx over DBeaver.

[Paolo F Cantoni]

Hello Paolo, I'm curious if you were able to reverse/forward engineer Databricks schemas using EA.  Because I may need to in the near future, and I prefer Sparx over DBeaver.

Sorry, Richard.  I think it's a case of "you can't get there from here" unless Sparx figures out how to dig into external tables in SQL Server.


Paolo