跨多個資料庫 (Azure SQL 資料庫) 執行臨機操作分析查詢

發行項
03/13/2024

在本教學課程中，您會跨整個租用戶資料庫集執行分散式查詢，以啟用互動式隨選報表。這些查詢可以擷取內嵌在 Wingtip Tickets SaaS 應用程式日常操作資料中的深入解析。若要進行這些擷取，您可以將額外的分析資料庫部署至目錄伺服器，並使用彈性查詢來啟用分散式查詢。

您會在本教學課程中學到：

如何部署隨選報表資料庫
如何跨所有租用戶資料庫執行分散式查詢

若要完成本教學課程，請確定已完成下列必要條件：

已部署 Wingtip Tickets SaaS 多租用戶資料庫應用程式。若要在五分鐘內完成部署，請參閱部署及探索 Wingtip Tickets SaaS 多租用戶資料庫應用程式
已安裝 Azure PowerShell。如需詳細資料，請參閱開始使用 Azure PowerShell
SQL Server Management Studio (SSMS) 已安裝。若要下載並安裝 SSMS，請參閱下載 SQL Server Management Studio (SSMS)。

隨選報表模式

adhoc reporting pattern

SaaS 應用程式可以分析集中儲存在雲端中的大量租用戶資料。分析會顯示應用程式的作業和使用方式的深入解析。這些深入解析可以引導應用程式和服務中的功能開發、使用性改進及其他投資。

在單一多租用戶資料庫中存取此資料很容易，但當此資料大規模分散到可能數千個資料庫時，則並不輕鬆。其中一種方法是使用彈性查詢，其可跨一組具有通用結構描述的分散式資料庫進行查詢。這些資料庫可以分散到不同的資源群組和訂用帳戶。然而，一個常見登入必須具有從所有資料庫擷取資料的存取權限。彈性查詢會使用單一前端資料庫，其中會定義外部資料表，以鏡像分散式 (租用戶) 資料庫中的資料表或檢視。提交至此前端資料庫的查詢會進行編譯，以產生分散式查詢計畫，並視需要將部分查詢向下推送至租用戶資料庫。彈性查詢會使用目錄資料庫中的分區對應來判斷所有租用戶資料庫的位置。設定和查詢使用標準 Transact-SQL，非常簡單並支援從 Power BI 和 Excel 等工具進行臨機操作查詢。

彈性查詢透過將查詢分散到租用戶資料庫，可立即深入解析即時生產資料。不過，當彈性查詢從潛在的大量資料庫接收資料時，查詢延遲有時會高於提交至單一多租用戶資料庫的對等查詢。務必設計查詢，以將傳回的資料降至最少。彈性查詢通常最適合用來查詢少量即時資料，而不是建置常用或複雜的分析查詢或報表。如果查詢效能不佳，請查看執行計畫，以了解查詢的哪個部分已下推至遠端資料庫。然後評估傳回的資料量。透過將擷取的租用戶資料儲存至已針對分析查詢最佳化的資料庫，可能有利於妥善處理需要複雜分析處理的查詢。 SQL Database 和 Azure Synapse Analytics 可以裝載這類分析資料庫。

租用戶分析教學課程會說明此分析模式。

取得 Wingtip Tickets SaaS 多租用戶資料庫應用程式原始碼和指令碼

可在 WingtipTicketsSaaS MultitenantDB GitHub 存放庫中使用 Wingtip Tickets SaaS 多租用戶資料庫指令碼和應用程式來源程式碼。關於下載和解除封鎖 Wingtip Tickets SaaS 指令碼的步驟，請參閱一般指引。

建立票證銷售資料

若要針對更有趣的資料集執行查詢，請透過執行票證產生器來建立票證銷售資料。

在 PowerShell ISE 中，開啟 ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1 指令碼，並設定下列值：
- $DemoScenario = 1，購買各地事件的票證。
按 F5 以執行指令碼並產生票證銷售。當指令碼執行時，請繼續進行本教學課程中的步驟。票證資料會在 [執行特定分散式查詢] 區段中查詢，因此請等候票證產生器完成。

探索租用戶資料表

在 Wingtip Tickets SaaS 多租用戶資料庫應用程式中，租用戶儲存在混合式租用戶管理模型中，其中租用戶資料會儲存在多租用戶資料庫或單一租用戶資料庫中，而且可以在兩者之間移動。跨所有租用戶資料庫執行查詢時，彈性查詢必須將資料視為租用戶所分區之單一邏輯資料庫的一部分，這點很重要。

為了達成此模式，所有租用戶資料表都包含 VenueId 資料行，以識別資料所屬的租用戶。 VenueId 會計算為場地名稱的雜湊，但可以使用任何方法引進此資料行的唯一值。這種方法類似於計算租用戶鑰匙以用於目錄中的方式。彈性查詢會使用包含 VenueId 的資料表來平行處理查詢，並將其下推至適當的遠端租用戶資料庫。這可大幅減少傳回的資料數量，並導致效能增加，特別是當有多個租用戶的資料儲存在單一租用戶資料庫中時。

部署用於臨機操作分散式查詢的資料庫

此練習會部署 adhocreporting 資料庫。這是前端資料庫，其中包含用於查詢所有租用戶資料庫的結構描述。資料庫會部署到現有的目錄伺服器，這是用於範例應用程式中所有管理相關資料庫的伺服器。

在 PowerShell ISE 中，開啟 ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1 並設定下列值：
- $DemoScenario = 2，部署臨機操作分析資料庫。
按 F5 以執行指令碼並建立 adhocreporting 資料庫。

在下一節中，您會將結構描述新增至資料庫，以便用來執行分散式查詢。

設定用於執行分散式查詢的「前端」資料庫

本練習會將結構描述 (外部資料來源和外部資料表定義) 新增至隨選報表資料庫，以便跨所有租用戶資料庫進行查詢。

開啟 SQL Server Management Studio，並連線到您在上一個步驟中建立的臨機操作報表資料庫。資料庫的名稱為 adhocreporting。
在 SSMS 中，開啟 ...\Learning Modules\Operational Analytics\Adhoc Reporting\ Initialize-AdhocReportingDB.sql。
檢閱 SQL 指令碼並注意下列幾點：

彈性查詢會使用資料庫範圍的認證來存取每個租用戶資料庫。此認證必須可在所有資料庫中使用，而且通常應授與啟用這些臨機操作查詢所需的最低權限。

藉由使用目錄資料庫做為外部資料來源，查詢會在執行查詢時散發到在目錄中註冊的所有資料庫。因為每個部署的伺服器名稱都不同，所以此初始化指令碼會藉由擷取執行指令碼的目前伺服器 (@@servername) 來取得目錄資料庫的位置。

參考租用戶資料表的外部資料表會以 DISTRIBUTION = SHARDED(VenueId) 來定義。這會將特定 VenueId 的查詢路由傳送至適當的資料庫，並改善許多案例的效能，如下一節所示。

建立和填入的本機資料表 VenueTypes。此參考資料表在所有租用戶資料庫中通用，因此可以在這裡以本機資料表表示，並填入通用資料。對於某些查詢，這可能會減少在租用戶資料庫與 adhocreporting 資料庫之間移動的資料量。

如果您以這種方式加入參考資料表，請務必在更新租用戶資料庫時更新資料表結構描述和資料。
按 F5 執行指令碼，並初始化 adhocreporting 資料庫。

現在您可以執行分散式查詢，並收集所有租用戶的深入解析！

運行臨機操作分散式查詢

現在已設定 adhocreporting 資料庫，請執行一些分散式查詢。包含執行計畫，以進一步了解查詢處理發生的位置。

檢查執行計畫時，將滑鼠停留在計畫圖示上以取得詳細資料。

在 SSMS 中，開啟 ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReportingQueries.sql。
請確定您已連線到 adhocreporting 資料庫。
選取 [查詢] 功能表，然後按下 [包括實際執行計畫]
反白顯示 [目前註冊的地點？] 查詢，然後按 F5。

此查詢會傳回整個場地清單，說明查詢所有租用戶及傳回每個租用戶的資料有多快多容易。

檢查計畫並看到整個成本都是遠端查詢，因為我們只是前往每個租用戶資料庫並選取場地資訊。
選取下一個查詢，然後按 F5。

此查詢會聯結租用戶資料庫和本機 VenueTypes 資料表的資料 (本機，因為它是 adhocreporting 資料庫中的資料表)。

檢查計畫並看到大部分成本是遠端查詢，因為我們查詢了每個租用戶的場地資訊 (dbo.Venues)，然後執行了與本機 VenueTypes 資料表的快速區域聯結，以顯示自訂名稱。
現在選取 [票證最多的是哪一天？] 查詢，然後按 F5。

此查詢會執行更複雜的聯結和彙總。請務必注意的是，大部分處理都是從遠端完成，而且我們再一次僅帶回我們需要的資料列，只針對每個場地每天的彙總票證銷售計數傳回單一資料列。