跨数据库查询(纵向分区)入门(预览)Get started with cross-database queries (vertical partitioning) (preview)

Azure SQL 数据库弹性数据库查询(预览版)可让你使用单一连接点运行跨多个数据库的 T-SQL 查询。Elastic database query (preview) for Azure SQL Database allows you to run T-SQL queries that span multiple databases using a single connection point. 本文适用于垂直分区数据库This article applies to vertically partitioned databases.

完成时,会:了解如何配置和使用 Azure SQL 数据库执行跨多个相关数据库的查询。When completed, you will: learn how to configure and use an Azure SQL Database to perform queries that span multiple related databases.

有关弹性数据库查询功能的详细信息,请参阅 Azure SQL 数据库弹性数据库查询概述For more information about the elastic database query feature, see Azure SQL Database elastic database query overview.

先决条件Prerequisites

需要 ALTER ANY EXTERNAL DATA SOURCE 权限。ALTER ANY EXTERNAL DATA SOURCE permission is required. 此权限包含在 ALTER DATABASE 权限中。This permission is included with the ALTER DATABASE permission. 引用基础数据源需要 ALTER ANY EXTERNAL DATA SOURCE 权限。ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.

创建示例数据库Create the sample databases

首先,我们在相同或不同 SQL 数据库服务器中创建两个数据库:Customers 和 Orders。To start with, create two databases, Customers and Orders, either in the same or different SQL Database servers.

Orders 数据库中执行以下查询以创建 OrderInformation 表并输入示例数据。Execute the following queries on the Orders database to create the OrderInformation table and input the sample data.

CREATE TABLE [dbo].[OrderInformation](
    [OrderID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL
    )
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (123, 1)
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (149, 2)
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (857, 2)
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (321, 1)
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (564, 8)

现在,在 Customers 数据库中执行以下查询以创建 CustomerInformation 表并输入示例数据。Now, execute following query on the Customers database to create the CustomerInformation table and input the sample data.

CREATE TABLE [dbo].[CustomerInformation](
    [CustomerID] [int] NOT NULL,
    [CustomerName] [varchar](50) NULL,
    [Company] [varchar](50) NULL
    CONSTRAINT [CustID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
)
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (1, 'Jack', 'ABC')
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (2, 'Steve', 'XYZ')
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (3, 'Lylla', 'MNO')

创建数据库对象Create database objects

数据库范围的主密钥和凭据Database scoped master key and credentials

  1. 在 Visual Studio 中打开 SQL Server Management Studio 或 SQL Server Data Tools。Open SQL Server Management Studio or SQL Server Data Tools in Visual Studio.

  2. 连接到 Orders 数据库,并执行以下 T-SQL 命令:Connect to the Orders database and execute the following T-SQL commands:

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

    “username”和“password”应是用于登录到 Customers 数据库的用户名和密码。The "username" and "password" should be the username and password used to log in into the Customers database. 当前不支持使用 Azure Active Directory 通过弹性查询进行身份验证。Authentication using Azure Active Directory with elastic queries is not currently supported.

外部数据源External data sources

若要创建外部数据源,请对 Orders 数据库执行以下命令:To create an external data source, execute the following command on the Orders database:

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
    (TYPE = RDBMS,
    LOCATION = '<server_name>.database.chinacloudapi.cn',
    DATABASE_NAME = 'Customers',
    CREDENTIAL = ElasticDBQueryCred,
) ;

外部表External tables

在 Orders 数据库中创建外部表,该表应与 CustomerInformation 表的定义相匹配:Create an external table on the Orders database, which matches the definition of the CustomerInformation table:

CREATE EXTERNAL TABLE [dbo].[CustomerInformation]
( [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NOT NULL,
  [Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)

执行示例弹性数据库 T-SQL 查询Execute a sample elastic database T-SQL query

定义外部数据源和外部表后,现在可以使用 T-SQL 查询外部表。Once you have defined your external data source and your external tables, you can now use T-SQL to query your external tables. 对 Orders 数据库执行以下查询:Execute this query on the Orders database:

SELECT OrderInformation.CustomerID, OrderInformation.OrderId, CustomerInformation.CustomerName, CustomerInformation.Company
FROM OrderInformation
INNER JOIN CustomerInformation
ON CustomerInformation.CustomerID = OrderInformation.CustomerID

成本Cost

目前,弹性数据库查询功能包含在 Azure SQL 数据库的成本中。Currently, the elastic database query feature is included into the cost of your Azure SQL Database.

有关定价信息,请参阅 SQL 数据库定价For pricing information see SQL Database Pricing.

后续步骤Next steps