我需要使用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.
更多推荐
发布评论