五餅二魚工作室

在之前 Azure SQL Database 推出 Elastic Query 的時候，曾經寫過一篇「透過 Elastic Query 解決 Azure SQL Database 跨資料庫查詢」，來說明如果當需要跨資料庫存取的方式。而在前一陣子，剛好有朋友問到類似的狀況，於是就很順手的把部落格文章給他，想說又是一個簡單的任務。

沒有想到過了一個星期之後，朋友又發訊息來說，按照我的步驟上去設定，都不是很順利，因此協助他處理得過程中，覺得之前有些地方沒有說清楚，容易造成大家的誤會，因此又整理瞭一下幾個地方。

朋友的環境類似以下的配置，因此他希望在連接 DB1 的時候，還有機會去讀取其他 DB2 , DB3 和 DB4 的資料庫。

問題一：為什麼不能在 master 資料庫下建立  MASTER KEY ？這一點是 Azure SQL Database 的特殊限制，一般我們在地端使用 SQL Server 的時候，建立的 Master Key 都是存在 master 資料庫內，但在 Azure SQL Database 中的時候，master key 只能針對單一資料庫來使用，因此你需要道所在的資料庫下，使用 create master key 指令去建立，這點要稍微注意一下。

---

問題二：如果要跨在那麼多資料庫，那要建立相對數量的 CREDENTIAL  嗎 ？基本上每個 CREDENTIAL 就是一組的帳號和密碼，但如果你希望管理上方面，在其他的 server 上，都建立相同的帳號和密碼，那麼是可以都共用同一個 CREDENTIAL 。以上述的例子中，我們可以在各資料庫( DB2 , DB3 & DB4 )下去建立一個可以給外部存取的帳號，並賦予資料讀取的權限。

-- 建立帳號
CREATE LOGIN RemoteServer 
	WITH PASSWORD = 'XYZ@abc987' 
GO

-- 建立使用者
CREATE USER RemoteServer
	FOR LOGIN RemoteServer
	WITH DEFAULT_SCHEMA = dbo
GO

-- 指定權限
ALTER ROLE db_datareader ADD MEMEBR RemoteServer
GO

 

那麼回到 DB1 ，我們可以使用同樣的 CREDENTIAL ，分別建立不同的資料資料來源，來連到這些不同的資料庫上面去了

CREATE DATABASE SCOPED CREDENTIAL ConnectToOtherDB WITH 
    IDENTITY = 'RemoteServer', 
    SECRET = 'XYZ@abc987';
GO


CREATE EXTERNAL DATA SOURCE source1_db2
WITH
(
	TYPE=RDBMS,
	LOCATION='source1.database.windows.net',
	DATABASE_NAME='DB2',
	CREDENTIAL= ConnectToOtherDB
);
GO

CREATE EXTERNAL DATA SOURCE source2_db3
WITH
(
	TYPE=RDBMS,
	LOCATION='source2.database.windows.net',
	DATABASE_NAME='DB3',
	CREDENTIAL= ConnectToOtherDB
);
GO

CREATE EXTERNAL DATA SOURCE source3_db4
WITH
(
	TYPE=RDBMS,
	LOCATION='source3.database.windows.net',
	DATABASE_NAME='DB4',
	CREDENTIAL= ConnectToOtherDB
);
GO

 

---

問題三：要怎麼來測試建立的外部資料讀取是可以正常的？在此部分是可以先透過一個 Azure SQL Database 所提供的 SP : sp_execute_remote  ，可以用這個指令來針對外部資料來源來下 SQL 指令，因此我們就可以用類似以下的語法，來驗證是否是可以正常來連接外部資料來源。

EXEC sp_execute_remote  
    N'source1_db2',  
    N'SELECT DB_NAME()' 

EXEC sp_execute_remote  
    N'source2_db3',  
    N'SELECT DB_NAME()'

EXEC sp_execute_remote  
    N'source3_db4',  
    N'SELECT DB_NAME()'

 

---

問題四：要怎麼建立的外部資料表的名稱一定要和實際的資料表名稱相同呢？其實基本上是沒有這樣的限制的，只是外部資料表預設的來源端資料表是相同的 SCHEMA 和物件名稱，因此如果你有不同的話，那麼就要另外來做設定。在我們上述的案例中，因為在 DB2,DB3 和 DB4 下都和 DB1 有相同的資料表，因此我們在建立外部資料表的時候，會習慣的使用 SCHEMA 來做分類，但如果我使用以下的指令來建立的時候，那這樣可能連到 DB2 的時候，他會去抓 DB2.customer 的資料表，造成會有錯誤。

CREATE EXTERNAL TABLE [DB2].[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=source1_db2
)
GO

 

因此為了避免這樣的問題，最好把指令改成以下的用法，完整的指定 SCHEMA 名稱和物件的名稱，這樣就不會有對應上的異常了。

CREATE EXTERNAL TABLE [DB2].[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=source1_db2,
	SCHEMA_NAME = N'dbo',  
	OBJECT_NAME = N'customer'
)
GO

---

問題四：如果不同的資料庫有不同的定序，那該怎麼處理呢 ? 目前使用外部資料來源的時候，基本上是比較耗用資源的方式，Azure SQL Database 會直接將外部資料表的資料，都先拉回來到你所使用的資料庫，然後再進行處理，因此如果您有兩個資料庫是不同的定序，基本上當這兩個資料褲上有資料表需要相互關聯的時候，他都會將這些資料表的資料都先帶回到你所連線的資料庫上，然後才做處理。因此目前跨資料庫來做關聯的時候，是可以不用在指令上做特別的處理，就可以來進行的。