SQL Server Express等效于EXTERNAL DATA SOURCE [英] SQL Server Express equivalent for EXTERNAL DATA SOURCE

问题描述

根据 https://azure.microsoft.com/zh-cn/documentation/articles/sql-database-elastic-query-getting-started-vertical/，现在Azure SQL中的一个数据库可以查询其他AzureSQL数据库.对于我的用例，我计划有一个数据库为其他数据库提供参考数据，这非常适合拓扑1(垂直分片).

As per https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-getting-started-vertical/, it is now possible for one database in Azure SQL to query other Azure SQL databases. For my use case, I plan to have one database serving reference data for other databases, which fits nicely to Topology 1 (vertical sharding).

这对于已部署的环境非常有用，但是对于本地开发，我通常使用SQL Server Express进行开发.从SQL Server 2012 Express开始， CREATE EXTERNAL DATA SOURCE 是无效的语法.

This is great for a deployed environment, but for local development I typically develop using SQL Server Express. As of SQL Server 2012 Express, the CREATE EXTERNAL DATA SOURCE is not a valid syntax.

是否还可以从外部数据源中受益以进行本地开发?

Is it possible to also reap the benefit of external data source for local development?

推荐答案

在权衡了功能集之后，我决定区分本地数据库和Azure SQL的设置.

After weighing the feature sets, I decided to differentiate the setup of my local database and Azure SQL.

• 当本地SQL Server数据库要引用Azure SQL数据库时，可以使用链接服务器来引用
• 当另一个Azure SQL数据库要引用另一个Azure SQL数据库时，只有使用外部数据源

即本地

-- Make a link to the cloud
EXEC sp_addlinkedserver   
   @server=N'MyExternalServer', 
   @srvproduct=N'Azure SQL Db',
   @provider=N'SQLNCLI', 
   @datasrc=N'<server address>',
   @catalog='<database name>';
GO

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = '<server address>', 
    @useself = 'FALSE', 
    @locallogin=NULL,
    @rmtuser = '<username>',
    @rmtpassword = '<password>'
GO

select * from [MyExternalServer].[<database name>].[<schema>].[<table name>]

Azure SQL的位置:

Whereas for Azure SQL:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'; 
CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred 
WITH IDENTITY = '<username>', 
SECRET = '<password>';  

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH 
    (TYPE = RDBMS, 
    LOCATION = '<server>', 
    DATABASE_NAME = '<database name>', 
    CREDENTIAL = ElasticDBQueryCred, 
) ; 


create schema <internalschema>

CREATE EXTERNAL TABLE <internalschema>.<internaltablename>
(
    ... // list of columns
WITH 
( DATA_SOURCE = MyElasticDBQueryDataSrc,
SCHEMA_NAME = <schema>,
OBJECT_NAME = <table name>
) 

select * from <internalschema>.<internaltablename>

现在的挑战是使用两种方法使数据库脚本通用.要使用链接服务器引用表，必须使用四部分标识符 [server].[database].[schema].[tablename] 对其进行寻址.将此与外部数据源进行对比，在外部数据源中，只需使用 [schema].[tablename] .

The challenge now is to make the database scripts be common using both approaches. To reference a table using Linked Server, it has to be addressed using four-part identifier [server].[database].[schema].[tablename]. Contrast this with external data source where it can be addressed just by using [schema].[tablename].

使用此问题的启示:

Using inspiration from this question: https://dba.stackexchange.com/questions/74566/sql-server-using-4-part-identifiers-when-database-may-be-on-the-same-server, my approach is to create a synonym on my local database that redirects [schema].[tablename] to [externalserver].[externaldatabase].[externalschema].[tablename].

即本地:

create schema <internalschema>
CREATE SYNONYM <internalschema>.<internaltablename> FOR [MyExternalServer].[<database name>].[<schema>].[<table name>]

此后，两种情况都可以使用相同的语句:

After which, the same statement would work for both cases:

select * from <internalschema>.<internaltablename>

这种方法的一个大问题是，由于Azure SQL不允许DTC，因此不能在分布式事务下使用包装脚本.

One big problem with this approach is that you cannot use wrap your script under distributed transaction, because Azure SQL does not allow DTC.

这篇关于SQL Server Express等效于EXTERNAL DATA SOURCE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！