Azure SQL Database 彈性查詢概觀 (預覽)

適用於：Azure SQL 資料庫

彈性查詢功能 （預覽版） 可讓您執行跨越 Azure SQL Database 中多個資料庫的 Transact-SQL （T-SQL） 查詢。 它允許你進行跨資料庫查詢以存取遠端資料表，並連接 Microsoft 及非 Microsoft 工具（Excel、Power BI、Tableau 等）跨多個資料庫層級查詢。 這項功能可讓您將查詢擴增到大型資料層，並將結果透過商務智慧 (BI) 報告視覺化。

為何要使用彈性查詢

Azure SQL Database

完全在 T-SQL 中查詢 Azure SQL Database 中的所有資料庫。 這可讓您對遠端資料庫進行唯讀查詢，並為目前的 SQL Server 客戶提供一個選項，將使用三部份和四部份名稱或連結伺服器的應用程式移轉至 SQL Database。

適用於所有服務層級

Azure SQL Database 的所有服務層級都支援彈性查詢。 請參閱下面＜預覽限制＞一節中較低服務層級的效能限制。

將參數推送到遠端資料庫

彈性查詢現在可以將 SQL 參數發送至遠端資料庫以供執行。

預存程序執行

使用 sp_execute _remote 執行遠端預存程序呼叫或遠端函式。

彈性

具有彈性查詢的外部資料表可以參考具有不同結構描述或資料表名稱的遠端資料表。

彈性查詢情境

目標是促進這類查詢情境：讓多個資料庫的資料列匯入同一個結果集。 查詢可以由使用者或應用程式直接撰寫，或透過連接到資料庫的工具來間接撰寫。 建立報表、使用商業 BI 或數據整合工具，或任何無法變更的應用程式時，這特別有用。 透過彈性查詢，您可以在 Excel、Power BI、Tableau 或 Cognos 等工具中使用熟悉的 SQL Server 連線體驗，以查詢多個資料庫。 彈性查詢可讓您透過 SQL Server Management Studio 或 Visual Studio 所發出的查詢，輕鬆存取整個資料庫集合，並協助更方便從 Entity Framework 或其他 ORM 環境執行跨資料庫查詢。 圖 1 顯示一種情境：現有的雲端應用程式 (使用 彈性資料庫用戶端程式庫) 建置於向外延展的資料層之上，而彈性查詢則用於跨資料庫報表。

圖 1 在向外擴展的資料層上使用的彈性查詢

用於擴展數據層的 Elastic 查詢的螢幕快照。

彈性查詢的客戶案例可依下列拓撲區分特性：

  • 垂直數據分割 - 跨資料庫查詢 （拓撲 1）：數據會在數據層中的數個資料庫之間垂直分割。 一般而言，不同的資料表集位於不同的資料庫。 這表示不同資料庫的結構描述不同。 比方說，庫存的所有資料表都位於一個資料庫上，而所有會計相關資料表則位於另一個資料庫上。 此拓撲的常見使用案例會要求使用者跨多個資料庫中的資料表進行查詢或編譯報表。

  • 水平分割 - 分片 (拓撲 2)：資料會進行水平分割，將資料列分散到水平擴充的資料層中。 使用此方法時，所有參與資料庫的結構描述都相同。 此方法也稱為 分區化。 可以使用 (1) 彈性資料庫工具程式庫或 (2) 自行分區，來執行及管理分區。 彈性查詢用於查詢或編譯跨多個分區的報表。 分區通常是彈性集區中的資料庫。 只要資料庫共用通用架構，您就可以將彈性查詢視為一次查詢彈性集區中所有資料庫的有效方式。

附註

彈性查詢最適合可在外部來源端執行大部分處理 (篩選、彙總) 的報告案例。 它不適用於從遠端資料庫傳輸大量數據的 ETL 作業。 對於繁重的報告工作負載或有更多複雜查詢的資料倉儲案例，也請考慮使用 Azure Synapse Analytics。

垂直資料分割 - 跨資料庫查詢

若要開始撰寫程式碼，請參閱 開始使用跨資料庫查詢 (垂直資料分割)。

彈性查詢可讓位於 SQL Database 中某個資料庫內的資料供 SQL Database 中其他資料庫使用。 這可讓來自一個資料庫的查詢參考 SQL Database 中任何其他遠端資料庫的資料表。 第一個步驟是定義每個遠端資料庫的外部資料來源。 外部資料來源已定義於本機資料庫中，您想要從中取得遠端資料庫上資料表的存取權。 遠端資料庫不需要進行任何變更。 在不同資料庫有不同結構描述的典型垂直資料分割案例中，彈性查詢可用來實作常見使用案例，例如存取參考資料和跨資料庫查詢。

重要

您必須擁有 ALTER ANY EXTERNAL DATA SOURCE 許可權。 此權限包括在 ALTER DATABASE 權限中。 需要 ALTER ANY EXTERNAL DATA SOURCE 許可權才能參考基礎數據源。

參考資料：拓撲是用來管理參考資料。 在下圖中，具有參考數據的兩個數據表 （T1 和 T2） 會保留在專用資料庫上。 利用彈性查詢，您現在可以在遠端從其他資料庫存取資料表 T1 和 T2，如圖所示。 如果參考資料表很小或參考資料表的遠端查詢有選擇性述詞，則使用拓撲 1。

圖 2 垂直資料分割 - 使用彈性查詢來查詢參考資料

垂直分割的螢幕快照 - 使用彈性查詢來查詢參考數據。

跨資料庫查詢：彈性查詢可促成需要跨 SQL Database 中多個資料庫進行查詢的使用案例。 圖 3 顯示四個不同的資料庫：CRM、庫存、HR 和產品。 在其中一個資料庫中執行的查詢也需要存取另一個或其他所有資料庫。 利用彈性查詢，您可以在上述每個資料庫上執行一些簡單的 DDL 陳述式，針對此案例設定您的資料庫。 進行此一次性設定之後，存取遠端資料表就像從 T-SQL 查詢或從 BI 工具參考本機資料表一樣簡單。 如果遠端查詢未傳回大型結果，則建議使用此方法。

圖 3 垂直資料分割 - 使用彈性查詢來查詢各種資料庫

垂直分割的螢幕快照 - 使用彈性查詢來查詢各種資料庫。

下列步驟會針對垂直資料分割案例設定彈性資料庫查詢，這些案例需要存取位於 SQL Database 中遠端資料庫上的資料表 (SQL Database 須具有相同結構描述)：

執行 DDL 語句之後，您可以存取遠端資料表 mytable 就像是本機數據表一樣。 Azure SQL Database 會自動開啟遠端資料庫的連線、處理您對遠端資料庫的要求，以及傳回結果。

水平分割 - 分片

重要

在分片地圖管理器模式下（水平分割）中的彈性查詢，使用 EXTERNAL DATA SOURCE 類型 SHARD_MAP_MANAGER，將於 2027 年 3 月 31 日結束支援。 過後，現有工作負載將持續運作，但不再獲得支援，且無法建立新的外部 SHARD_MAP_MANAGER 資料來源類型。 關於遷移選項，請參閱 彈性查詢分片地圖管理器模式的遷移指南。

使用彈性查詢在分區化 (即水平分割) 的資料層執行報告工作時，需要彈性資料庫分區對應來代表資料層的資料庫。 一般而言，在這種情境中通常只會使用單一分區對應表，而具備彈性查詢功能的專用資料庫 (head node) 會作為報表查詢的進入點。 只有這個專用資料庫需要存取分區對應表。 圖 4 說明此拓撲，以及其與彈性查詢資料庫和分區對應表之間的組態。 如需有關彈性資料庫用戶端程式庫和建立分區對應的詳細資訊，請參閱 分區對應管理。

圖 4 水平資料分割 - 使用彈性查詢來報告分區化資料層

水平分區的螢幕快照 - 使用 Elastic Query 來報告跨分片數據層。

附註

彈性查詢資料庫（主節點）可以是獨立的資料庫，也可以是裝載分區對應表的同一個資料庫。 無論您選擇何種組態，請確定該資料庫的服務層級和計算大小夠高，足以處理預期的登入/查詢要求數目。

下列步驟會針對水平資料分割案例設定彈性資料庫查詢，這些案例需要存取 (通常) 位於 SQL Database 中數個遠端資料庫的一組資料表：

執行這些步驟之後，您就可以存取水準分割數據表 mytable 就像是本機數據表一樣。 Azure SQL Database 會自動開啟遠端資料庫 (實際儲存資料表的位置) 的多個平行連線、處理對於遠端資料庫的要求，以及傳回結果。 關於水平分割情境所需步驟的更多資訊，請參閱 水平分割的彈性查詢。

若要開始撰寫程式碼，請參閱開始使用彈性查詢進行水平資料分割 (分區化)。

重要

在大量資料庫上成功執行彈性查詢的關鍵在於，每個資料庫在查詢執行期間的可用性。 如果其中一個資料庫無法使用，整個查詢就會失敗。 如果您打算一次查詢數百個或數千個資料庫，則請確定您的用戶端應用程式已內嵌重試邏輯，或考慮利用彈性工作，以及查詢較小的資料庫子集，將每個查詢的結果合併至單一目的地。

T-SQL 查詢

一旦您已定義外部資料來源和外部資料表，您可以使用一般 SQL Server 連接字串來連接到您定義外部資料表的資料庫。 然後，您可以透過您的外部數據表，在該連線上執行 T-SQL 語句，但需遵守本文稍後所述的限制。 您可以在文件中找到 T-SQL 查詢的詳細資訊和範例，關於 水平數據分割 和 垂直數據分割。

外部資料來源安全

LOCATION外部資料來源可以透過完全限定的網域名稱（FQDN）或 IP 位址來識別 SQL 端點，包括非 Azure SQL Database 邏輯伺服器端點。 當彈性查詢執行時，會將認證資訊、查詢文字、參數及查詢傳輸的資料傳送給已設定的端點。 只為組織信任的端點配置外部資料來源。

只有擁有 ALTER ANY EXTERNAL DATA SOURCE 權限的主體才能建立或變更外部資料來源。 將此權限限制給需要的資料庫管理員，並定期透過查詢 sys.external_data_sources來檢視外部資料來源。

作為獨立的網路控管措施，請由網路管理員限制該邏輯伺服器的對外網路連線，並只允許已核准的目的地 FQDN。 在每個外部資料來源中使用 FQDN 取代 IP 位址，這樣其目的地才能明確被允許列出。 將資料庫設定與網路審核分離，實現職責分離，並防止資料庫管理員在未獨立網路政策變更前建立未經核准的通訊路徑。

網路安全邊界 （預覽）提供了另一個你可以套用到邏輯伺服器的網路邊界。 彈性查詢運作於單一邊界內，因此執行查詢的資料庫與遠端資料庫必須屬於同一邊界。 如需詳細資訊，請參閱預覽限制。

工具的連線能力

您可以使用一般 SQL Server 連接字串，將您的應用程式、BI 或資料整合工具連接到具有外部資料表的資料庫。 請確定 SQL Server 可支援做為您的工具的資料來源。 連線之後，請參考彈性查詢資料庫和該資料庫中的外部資料表，就如同您會使用您的工具連接的任何其他 SQL Server 資料庫一樣。

重要

只有在使用 SQL Server 驗證連線時，才支援彈性查詢。

成本

彈性查詢會算在 Azure SQL Database 的成本內。 支援遠端資料庫設置在與彈性查詢端點不同的資料中心，但從遠端資料庫輸出的數據將按照 Azure 費率定期收取費用。

預覽限制

  • 在較小型的資源以及標準和一般用途服務層級上，執行第一個彈性查詢最多可能需要幾分鐘的時間。 需要這些時間才能載入彈性查詢功能；較高的服務層級和計算大小可改善載入效能。

  • 彈性查詢目前僅支援以唯讀方式存取外部資料表。 不過，您可以在定義外部資料表的資料庫上使用完整的 Transact-SQL 功能。 這可以很有用，例如，使用 SELECT <column_list> INTO <local_table>來保存臨時結果，或在彈性查詢資料庫上定義參考外部數據表的預存程序。

  • 除了 nvarchar（max）之外，外部數據表定義不支援 LOB 類型（包括空間類型）。 因應措施是，您可以在遠端資料庫上建立檢視，將 LOB 類型轉換成 nvarchar（max），在檢視上定義外部數據表，而不是基表，然後將它轉換回查詢中的原始 LOB 類型。

  • 結果集中的 nvarchar(max) 資料類型會停用在彈性查詢實作中使用的進階批次處理技術，並可能大幅降低查詢效能，甚至在某些非標準使用案例中，效能可能下降一個數量級甚至兩個數量級，特別是當查詢傳輸大量非聚合數據時。

  • 目前不支援外部資料表的資料行統計資料。 支援資料表統計資料，但必須以手動方式建立。

  • Azure SQL Database 中的外部資料表不支援資料列指標。

  • 本文所記錄並支援的彈性查詢情境，使用 Azure SQL Database 作為遠端資料來源。 外部資料來源語法不會將 LOCATION 限制為 Azure SQL Database 邏輯伺服器。 依照 安全外部資料來源 中所述，保護任何已設定的目的地。

  • 對於作為外部資料來源目標的那些資料庫，elastic query 目前不支援私人連結。

  • 當執行查詢的資料庫與遠端資料庫在同一範圍內時，彈性查詢可透過 網路安全邊界 （預覽）支援。 你無法查詢位於不同邊界的遠端資料庫。 彈性查詢不需要自己的邊界設定，因為同一邊界內的資源間存取始終被允許。

  • 如果邊界阻擋了遠端資料庫的存取，故障會在查詢執行時顯現，而非建立外部資料來源時。 建立外部資料來源不會聯絡遠端端點。

相關內容

  • Last updated on