sql-server - 使用 Azure 本地网关连接到 SQL Server 高可用性群集时出错

Question

我们正在尝试配置本地数据网关，以供 Power BI/Power Apps 的 Azure 使用。我们希望连接到数据库“只读”实例上的 SQL Server 高可用性集群以进行实时/按需查询。下面是实现此目的的连接字符串示例:

Server=MyServer;Database=MyDb;Trusted_Connection=True;applicationintent=readonly

我们下载并安装了最新版本的网关，但无法将其连接到 HA SQL 数据库。我已经在互联网上搜索过，但尚未找到答案。我们尝试了 Azure Power BI 中的“SQL Server”和“OleDb”数据源，但没有成功。我们可以毫无问题地连接到 SQL Server 数据库的非 HA 实例。这可以做到吗？如果可以，如何做到？

https://powerbi.microsoft.com/en-us/gateway/

以下是我在尝试添加 SQL 连接时在 app.powerbi.com 门户网站中看到的错误:

Unable to connect: We encountered an error while trying to connect to MyServer.

ID: 90c2a52c-ba1f-4506-84ed-d6da5a44e684 Request

ID: 75e6338b-6806-9d42-8af3-7f6896485df8 Cluster

URI: https://wabi-west-us-redirect.analysis.windows.net

Status code: 400 Error Code: DMTS_PublishDatasourceToClusterErrorCode

Time: Fri Dec 01 2017 07:53:37 GMT-0700 (Mountain Standard Time)

Version: 13.0.3154.215

BI Gateway: Received error payload from service with ID 413243: SqlException encountered while accessing the target data source.

以下是运行网关服务的客户端中记录的异常:

GatewayPipelineErrorCode=DM_GWPipeline_UnknownError

InnerType=SqlException

InnerMessage=The target database ('MyDatabase') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

InnerToString=System.Data.SqlClient.SqlException (0x80131904): The target database ('MyDatabase') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass31_0.b__0(Task`1 _)

at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke() at System.Threading.Tasks.Task.Execute()

Answer 1

此后，我了解了有关 Azure 本地网关的更多信息，但这仍然是一个问题。可能的解决方法是使用“@provstr=N'ApplicationIntent=ReadOnly'”在 HA 只读辅助 SQL 服务器上创建链接服务器。

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'HaReadOnlyLink', @srvproduct=N'SQL', @provider=N'SQLNCLI11', @datasrc=N'SqlHaListnerHost', @provstr=N'ApplicationIntent=ReadOnly', @catalog=N'MyHaDb'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HaReadOnlyLink',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

然后，您可以使用 HA 辅助数据库上另一个数据库的链接服务器来连接只读 HA 数据库，如下所示:

SELECT * FROM HaReadOnlyLink.MyHaDb.dbo.MyTable

Microsoft SQL Linked Server Documenation