是否可以在Azure和SQL中使用跨数据库查询? [英] Is it possible to use cross database queries in both Azure and SQL?

问题描述

我正在设计一个项目，该项目旨在使用Azure数据库和在本地使用本地sql服务器在azure平台上运行.我们已经存储了包含跨数据库调用的过程.但是，它在Azure服务器上不起作用.我需要能够在azure和本地sql服务器上都可以使用的跨数据库查询.

I am working on a project designed to run on azure platform using Azure database and Locally using local sql server. We have stored procedure that contain cross database calls. However It doesn't works on Azure server.I need cross database queries that capable of working in both azure and local sql server.

我们使用了弹性查询，该查询使我们可以跨Azure SQL数据库进行查询

We an use Elastic Query which allows us to query across Azure SQL Databases

https://azure. microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/

我们可以使用以下代码在azure服务器中设置外部数据源.在这种情况下，我们可以执行跨数据库调用，例如在单个数据库中联接表.

We can setup external data source in azure server Using following code. In this case we can execute cross database calls likes joining tables in a single database.

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourPassword';  

 CREATE DATABASE SCOPED CREDENTIAL yourServeradminlogin  
 WITH IDENTITY = 'yourServeradminlogin',  
 SECRET = 'yourPassword';  

 CREATE EXTERNAL DATA SOURCE RefmyDemoDB2  
 WITH  
 (  
    TYPE=RDBMS,  
    LOCATION='testdbdemoserver.database.windows.net',  
    DATABASE_NAME='myDemoDB2',  
    CREDENTIAL= yourServeradminlogin  
 );  

 CREATE EXTERNAL TABLE [dbo].[Department](  
    [DeptId] [int] NOT NULL,  
    [Name] [varchar](50) NULL  
 )  
 WITH  
 (  
    DATA_SOURCE = RefmyDemoDB2  
 );  

在不添加第三方数据库引擎的情况下，似乎不可能在我们的本地SQL服务器上使用上述方法. 在本地版本的SQL Server 2017及更高版本中安装名为PolyBase的东西以进行跨数据库查询，仅支持HADOOP之类的外部数据源，而不是在sql服务器中引用另一个数据库(或任何数据存储).

It doesn't seem possible to use above method on our local SQL server without adding a third party database engine. Install something called PolyBase to do cross database query in local sql server version 2017 and above.It only supported with external data source of type such as HADOOP rather than referencing another database(or any data storage) within the sql server.

我们可以使用以下代码完成

We can accomplish that using following code

  CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyP@ssword123secretword';

  CREATE DATABASE SCOPED CREDENTIAL mycredential  
  WITH IDENTITY = 'credential', Secret = 'secretkey' 

  CREATE EXTERNAL DATA SOURCE mycustomers
  WITH (
    TYPE = HADOOP,
    LOCATION =  'wasbs://azurestorage.blob.core.windows.net/',
    CREDENTIAL = mycredential
  );

  CREATE EXTERNAL FILE FORMAT csvformat 
  WITH ( 
    FORMAT_TYPE = DELIMITEDTEXT, 
    FORMAT_OPTIONS ( 
      FIELD_TERMINATOR = ','
    ) 
  );

  CREATE EXTERNAL TABLE TableName
  ( 
    [did] [int] NOT NULL,  
    [Dname] [varchar] (50) NULL 
  ) 
  WITH 
  ( 
     LOCATION = '/', 
     DATA_SOURCE = mycustomers, 
     FILE_FORMAT = csvformat
  )

使用HADOOP和polybase，我们可以在本地sql服务器中创建一个外部数据源.但是它会创建该外部数据存储的外部数据源.那就是位于该外部存储中的外部数据表.确实，我的要求是在sql服务器中创建数据库的外部数据源.这样我就可以在azure和本地sql服务器中使用相同的corss数据库查询

Using HADOOP and polybase we can create an external data source in local sql server. But it create external data source of that external data storage. That is External data table located in that external storage.Exactly, my requirement is create an external data source of database within the sql server. So that i can use same corss data base query in both azure and local sql server

有什么解决方案可以解决此问题.还是在Azure和本地sql服务器中运行跨数据库查询的任何解决方案?

Is there any solution to solve this. or any solution to run cross database queries in both azure and local sql server?

推荐答案

您不应假定在SQL Azure中使用外部表与在SQL Server中进行跨数据库查询远程相似.它们不是同一件事，它们具有非常不同的性能配置文件.外部表比SQL Server中的跨数据库查询更靠近链接服务器.

You should not assume that using external tables in SQL Azure is remotely similar to doing cross-database queries in SQL Server. They are not the same thing and they have very different performance profiles. External tables is closer to linked servers than cross-database queries in SQL Server.

在SQL Server中，跨数据库查询为: -在同一个SQL实例中运行 -与单个数据库SQL Azure运行相同的基本执行代码路径以读取数据 -与SQL Server中的单数据库操作相比，您在事务语义上有一些不同，但是从您的角度来看，体验通常是相似的.

In SQL Server, cross-database queries are: - running in the same SQL instance - run the same basic execution code path to read data vs. single-database SQL Azure - you have some slightly different transactional semantics vs. single-database operaitons in SQL Server, but the experience is generally similar from your perspective.

在SQL Azure单例(传统")SQL Azure中，通常在同一物理计算机上没有数据库.因此，您必须进行跨服务器查询(使用链接服务器之类的称为外部表的机制公开该查询，该机制主要支持分片/扇出场景).尝试使用此功能来模拟跨数据库查询可能在功能上可行，但是由于性能差异，这并不是一个很好的计划.在这条路径上也没有任何真正的交易保证(没有DTC).

In SQL Azure singletons ("traditional") SQL Azure, you generally do not have the databases on the same physical machines. So, you have to cross-server queries (which are exposed using a linked-server like mechanism called external tables which support sharding/fan-out scenarios mostly). Trying to use this feature to simulate cross-database queries may functionally work, but it is not really a great plan due to the performance differences. There are also no real transactional guarantees in this path at all (no DTC).

SQL Azure托管实例确实在内部支持单个SQL Server实例中的跨数据库查询.因此，如果您确实想使用跨数据库查询，那么这将是您使用的最相似的路径.如果要使用SQL Azure单一数据库，通常通常根本不想为旧版工作负载执行跨数据库查询，并且需要重写以避免依赖. (否则，由于性能差异，这将一直令人头疼)

SQL Azure Managed Instance does support cross-database queries within a single SQL Server instance internally. So, this would be the path that would be most similar for you to use if you really want to use cross-database queries. If you want to use SQL Azure single databases, you generally would not want to do cross-db queries at all for a legacy workload and you would want to rewrite to avoid the dependency. (Otherwise it will just be an ongoing headache due to the performance differences)

这篇关于是否可以在Azure和SQL中使用跨数据库查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！