如何通过 Visual Studio/SSDT 将外部表添加到 Azure SQL DB [英] How do I add external tables to Azure SQL DB through Visual Studio/SSDT

问题描述

我的问题是，如何开发一个数据库项目并将其部署到使用外部表的 Azure SQL DB?

My question is, how to you develop and deploy a database project to Azure SQL DB that uses external tables?

我正在使用 Visual Studio 2017 数据库项目来管理 Azure SQL 数据库.我一直遵循在VS中构建数据库的路径，然后点击发布按钮，效果很好.

I’m using a Visual Studio 2017 database projects to manage Azure SQL databases. I’ve been following the path of building the database in VS, then hitting the publish button, and it works great.

现在我正在尝试添加外部表来执行弹性查询.在 SSMS 中，我为其创建了外部数据源和凭据，以及一个远程表:

Now I am trying to add external tables for doing Elastic Queries. In SSMS, I created the external data source and credential for it, and a remote table:

CREATE EXTERNAL DATA SOURCE [RemoteServer] 
    WITH (TYPE = RDBMS, LOCATION = N'myserver.database.windows.net',
     CREDENTIAL = [RemoteUser], DATABASE_NAME = N'MyRemoteDb')
GO

CREATE EXTERNAL TABLE dbo.MyTable (
     ID int not null,
    MyColumn varchar(10) not null
)  WITH (
    DATA_SOURCE = [RemoteServer]
    );
GO

(远程用户的凭据也存在.)

(Credential for RemoteUser also exists.)

SELECT * FROM MyTable  

产生了预期的结果，一切都很好.

produces the expected result and all is fine in the world.

现在我想将它添加到我的数据库项目中，以便它可以通过发布(在 VS 中)进行部署，以用于将来的任何更改(并保存在源代码管理中).

Now I want to add this to my database project so it can be deployed via publishing (in VS) for any future changes (and saved in source control).

我收到错误:

SQL71501: External Data Source: [RemoteServer] has an unresolved reference to Database Scoped Credential [RemoteUser].

我可以通过将所有外部部分的属性设置为不构建来消除错误，但这违背了能够从 Visual Studio 发布的目的.我已尝试从数据源中删除凭据，因此它不存在.它消除了错误，但由于缺少凭据而导致发布失败:

I can remove the error by setting the properties of all the external pieces to not build, but that defeats the purpose of being able to publish from Visual Studio. I’ve tried removing the credential from the data source, so it doesn’t exist. It removed the error, but publish fails due to it missing the credential:

CREATE EXTERNAL DATA SOURCE [RemoteServer] 
    WITH (TYPE = RDBMS, LOCATION = N'myserver.database.windows.net',
    DATABASE_NAME = N'MyRemoteDb')
GO

 SQL72014: .Net SqlClient Data Provider: Msg 46505, Level 16, State 15, Line 1 Missing required external DDL option 'CREDENTIAL'.)

有什么建议吗?

推荐答案

在撰写本文时，Azure 中的弹性查询仍处于预览阶段，Microsoft 在概述页面中列出了许多限制.不幸的是，其中之一是关于通过 SSDT 编写外部表和外部数据源的脚本.请注意此处(有关 SSDT，请参阅列表中的第二项).

Elastic query in Azure is still in preview at the time of writing and Microsoft list a number of limitations in the overview page. Unfortunately one of them is regarding the scripting of external tables and external data sources via SSDT. It is noted here (see second item in the list in regards to SSDT).

这篇关于如何通过 Visual Studio/SSDT 将外部表添加到 Azure SQL DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！