我需要使用ADF将数百个表从内部部署的SQL Server复制到Azure SQL Server。 我没有访问数据库或网络的权限，但是如果AD帐户具有足够的数据库权限，则可以安装本地数据网关，然后使用“复制数据（预览）”来将所有表复制到blob存储。
我的问题是我无法访问数据库的模式，所以我无法轻松地为Azure SQL Server配置必要的表/列，因为有几百个表和手动执行会非常耗时。 我发现复制到Azure数据仓库具有“自动创建表”功能，我能够将本地SQL Server直接复制到Azure DW，而无需在目标上定义架构，但Azure SQL不支持此功能服务器。
有没有办法获得配置Azure DW架构并将其用于Azure SQL Server的相同脚本/方法？ 有没有其他方法通过本地数据网关获取源数据库的模式？
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?
最满意答案
假设您的AD帐户拥有对元数据的读取权限，您是否可以不使用DMG对数据库运行查询来为您的表生成模式？
因此，对于您的管道，而不是从每个表中选择*，让它运行查询以提取模式，这里有一些示例： 如何在SQL Server查询中显示表结构？
然后你会输出到一个blob。
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.
更多推荐
发布评论