Azure Data Factory - Azure SQL Server Destination Schema

Question

I need to copy several hundred tables from an on-premise SQL Server to an Azure SQL Server using ADF. I don't have access to the DB or the network it's on, but I was able to get the on-prem data gateway installed, given an AD account with sufficient DB privileges, and then use the "Copy Data (Preview)" to copy all tables to blob storage.

My problem is that I don't have access to the DB's schema, so I can't easily provision the Azure SQL Server with the necessary tables/columns since there are several hundred tables & performing manually would be extremely time consuming. I found that copying to an Azure Data Warehouse has a "Auto table creation" feature & I am able to copy from on-prem SQL Server directly to Azure DW without defining a schema at the destination, but this isn't supported on Azure SQL Server.

Is there a way to obtain the same script/method that provisions the Azure DW schema & use it for Azure SQL Server? Is there any other way to obtain the source DB's schema via the on-prem data gateway?

Answer 1

Couldn't you use DMG to run a query against the database to generate the schema for your tables, assuming your AD account has read access to the metadata?

So with your pipeline, instead of it selecting * from each table, have it run a query to exract the schema, some examples here: How can I show the table structure in SQL Server query?

You would then output that to a blob.

Answer 2

given that you were able to run the Copy Data tool to extract data out of on-premises SQL Server, you must have credentials to access the database. Can you run SSMS (SQL Server Management Studio) on the on-prem data gateway and examine/extract the schema?

"Auto table creation" feature is currently only available for Azure Data Warehouse. Supporting this feature when loading into Azure SQL Server is on our backlog but we don't have a committed timeline for this yet.

Shirley Wang