Author Topic: Can we reverse Engineer Azure Synapse SQL Databses?  (Read 600 times)

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 8323
  • Karma: +236/-129
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Can we reverse Engineer Azure Synapse SQL Databses?
« on: September 02, 2022, 03:20:17 pm »
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
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 12061
  • Karma: +470/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Can we reverse Engineer Azure Synapse SQL Databses?
« Reply #1 on: September 02, 2022, 09:04:03 pm »
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

  • EA User
  • **
  • Posts: 77
  • Karma: +14/-7
    • View Profile
Re: Can we reverse Engineer Azure Synapse SQL Databses?
« Reply #2 on: September 02, 2022, 10:11:49 pm »
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

  • EA Guru
  • *****
  • Posts: 8323
  • Karma: +236/-129
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Can we reverse Engineer Azure Synapse SQL Databses?
« Reply #3 on: September 03, 2022, 04:07:29 pm »
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
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

MichaelJ

  • EA User
  • **
  • Posts: 77
  • Karma: +14/-7
    • View Profile
Re: Can we reverse Engineer Azure Synapse SQL Databses?
« Reply #4 on: September 03, 2022, 06:45:29 pm »
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

  • EA Guru
  • *****
  • Posts: 8323
  • Karma: +236/-129
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Can we reverse Engineer Azure Synapse SQL Databses?
« Reply #5 on: September 08, 2022, 08:30:34 am »
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
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!