0

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?

nbb007
  • 3
  • 1

2 Answers2

1

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

ShirleyWang-MSFT
  • 266
  • 1
  • 3
  • I have credentials to the on-prem DB, but the only method of access I have is via the Data Gateway (all other "inbound" access is blocked to the subnet). Can SSMS somehow connect "through" the data gateway to access the on-prem DB? – nbb007 May 02 '17 at 15:17
  • You can install SSMS only (without SQL Server installation) on the same machine as the data management gateway. – ShirleyWang-MSFT May 04 '17 at 00:32
  • I don't have access to that machine either - the data management gateway was setup on an edge server by my IT department (after allot of politics) & after install, I provided them with a key from my Azure ADF instance to establish the connection. I am trying to get schema info about the DB without having to contact IT again, as this is a POC that I've already received allot of IT "security" push-back on as it removes the need for IT. Since I can access the data through the gateway & the portal shows me info about the DB's schema, I thought it may be possible to "extract" this info? – nbb007 May 04 '17 at 16:27
0

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.

Community
  • 1
  • 1
Nick Beagley
  • 46
  • 4