五餅二魚工作室

當我們在使用 Azure SQL Database 服務中，基本上每個資料庫服務都是獨立的，彼此之間是無法直接連接，因此沒有辦法像一般在使用 SQL Server 的時候，直接利用「完整名稱」的方式，利用 server_name.database_name.schema_name.object_name 來指定物件 ，達到跨資料庫來存取。

 

前一陣子 Azure SQL Database 提供了一些 Elastic 的功能，讓我們可以更有效的運用 Azure SQL Database 服務，像是 Elastic Database Pool 的管理方式，當我們有許多資料庫服務的時候，可以利用 Pool 的機制讓費用有不少的節省。但是因為 Azure SQL Database 會有一些 DTU 和容量大小的限制，使得我們必須將資料庫，使用垂直或水平的方式拆分成多個小資料庫，但當我們拆分之後，則又會遇到一開始所討論的跨資料庫存取的問題，因此這個時候就需要透過 「Elastic Query」 來解決了。

 

在 Elastic Query 中，如果採用水平切割，目前只有 .Net 的程式有支援，就先暫時不討論，我們就先針對垂直分割的方式來討論，為了測試這個功能，首先我們先在 Azure 上面建立兩個資料庫服務，這兩個資料庫服務是可以不要在同一個伺服器上。

並且先在 Main 的資料庫內建立一個測試用的資料表，並且放入一些測試資料

CREATE TABLE [dbo].[customer](
	[c_id] [int] NOT NULL PRIMARY KEY,
	[c_firstname] [nvarchar](30) NULL,
	[c_lastname] [nvarchar](30) NOT NULL,
	[street] [nvarchar](256) NOT NULL,
	[city] [nvarchar](20) NOT NULL,
	[state] [nvarchar](20) NULL,
	[country] [nvarchar](50) NOT NULL
)

INSERT INTO [dbo].[customer]
           ([c_id],[c_firstname],[c_lastname],[street],[city],[state],[country])
     VALUES
           (1,'James','Fu' ,'','Taichung','Taiwan','Taiwan'),
           (2,'Sky','Chang','','Taipei'  ,'Taiwan','Taiwan')
GO

接下來我們希望在 User1 的資料庫下，可以去取得 Main 資料庫下該資料表內的資料，因此我們會有四個步驟需要去進行：

1. 建立 MASTER KEY：因為會透過這個主要金鑰，將我們要連線的資訊儲存的時候給加密，因此如果之前沒有建立，則可以透過下面的語法來做建立，至於密碼的部份就按照你所想要的去指定囉。但這個部分有個觀念要注意一下，以往我們在 SQL Server 上，一個 Instance 只需要一個 MASTER KEY 就可以了，但在 Azure SQL Database 服務上，因為每個都是獨立的，所以不同的資料庫服務雖然在同一個伺服器下，但 MASTER KEY 是無法共用的。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'A@abc3939889'

2. 建立資料庫層級認證：如同 MASTER KEY 一般，在 Azure 上需要把認證建立在自己的資料庫服務內，因此語法上會有點不同，透過建立認證去指定連接其他資料庫服務的連線帳號與密碼。因此下面的範例中我們建立一個認證名稱是 ConnectToMainDB，並且設定連線的帳號和密碼，這樣 SQL Server 就會將這組連線資料透過金鑰將這些資訊給加密儲存起來，確保安全。

CREATE DATABASE SCOPED CREDENTIAL ConnectToMainDB WITH 
    IDENTITY = 'HelloKitty', 
    SECRET = 'ABC@abc123';
GO

3. 指令外部資料來源：這裡我們會透過 EXTERNAL DATA SOURCE，類似以往在 SQL Server 建立 Link Server 的概念，指定要連接的資料庫服務。在這裡我們建立一個外部資料來源  MainDataSource，要設定他的 TYPE 是 RDBMS，並且設定好要連接的伺服器、資料庫服務名稱和前面所建立的認證，這樣就可以讓 Azure 知道當要跨資料庫去連接的時候的必要資訊。

CREATE EXTERNAL DATA SOURCE MainDataSource
WITH
(
	TYPE=RDBMS,
	LOCATION='5l2fdb.database.windows.net',
	DATABASE_NAME='Main',
	CREDENTIAL= ConnectToMainDB
);

4. 建立外部資料表：為了支援 Elastic Query，因此在 Azure SQL Database 上新增了 EXTERNAL TABLE，有點類似建立 View 的方式，讓你在所使用的 Azure SQL Database 服務中，建立一個虛擬的資料表，用來對應一個在外部資料來源中的一個資料表，在建立的時候要注意一點，就是在建立這個資料表的時候，不能設定 Primary Key 這類的條件約束，而且要指定 DATA_SOURCE 到你所要關聯的外部資料來源，否則會有錯誤。

CREATE EXTERNAL TABLE [dbo].[customer](
	[c_id] [int] NOT NULL,
	[c_firstname] [nvarchar](30) NULL,
	[c_lastname] [nvarchar](30) NOT NULL,
	[street] [nvarchar](256) NOT NULL,
	[city] [nvarchar](20) NOT NULL,
	[state] [nvarchar](20) NULL,
	[country] [nvarchar](50) NOT NULL
) WITH
(
	DATA_SOURCE=MainDataSource
)

 

---

透過上述的步驟，就可以完成相關設定，因此我們可以測試看看，都我透過下面這樣的語法

SELECT DB_NAME()

SELECT * FROM [dbo].[customer]

可以看到我們的確在 User1 的資料庫下，取得 Main 資料庫服務下相同名稱資料表的資料了

從上述的測試中看起來可以取得資料，但這裡要注意一下，目前還不支援跨資料庫的異動，我個人猜想應該是會有跨資料庫交易管理的問題，因此如果您下了 DELETE、 INSERT 或 UPDATE 的指令的時候，就會得到有「DML Operations are not supported with external tables.」的錯誤訊息，這點在使用上也要稍微注意一下囉。