SQL 快照重定向

公司正在使用快照實現數據庫鏡像非同步。這提供了一些帶有報告區域的災難恢復。伺服器 A 主體包含鏡像到伺服器 B 的數據庫，快照是在伺服器 B 數據庫上拍攝的。

為了啟用更新的數據，我們將在鏡像伺服器數據庫上每 30 分鐘創建一次數據庫快照。我們如何將所有 SSRS/report 儲存過程查詢重定向到最新快照？我們的報告查詢很短，一般在 10 秒左右。微軟在下面提到了程式解決方案，不確定那是什麼？所以 SalesDB1030, SalesDB1100, SalesDB1130, 24clock 添加在 dbname 之後

報告查詢可能是

select * from SalesDbSnapshot.dbo.SalesTransaction st
inner join SalesDbSnapshot.dbo.Customer cs
   on st.CustomerId = cs.CustomerId
inner join SalesDbSnapshot.dbo.Product pr
   on st.ProductId = pr.ProductId

創建數據庫快照

“要使用數據庫快照，客戶端需要知道在哪裡找到它。使用者可以在創建或刪除另一個數據庫快照時讀取一個數據庫快照。但是，當您用新快照替換現有快照時，您需要將客戶端重定向到新快照。使用者可以通過 SQL Server Management Studio 手動連接到數據庫快照。但是，為了支持生產環境，您應該創建一個程式解決方案，透明地將報告編寫客戶端指向數據庫的最新數據庫快照。 "

一種方法是創建一個僅包含引用快照中您需要報告的對象的同義詞的數據庫。然後，客戶端可以連接到同義詞數據庫並執行查詢，就像他們直接使用快照一樣。

每當創建新快照時，執行腳本以使用新快照數據庫名稱更新同義詞。下面是一個為快照中的所有表、視圖和過程重新創建同義詞的範例。如果需要，添加其他對像類型。

USE SynonymDatabase;

DECLARE
     @SnapshotDatabaseName sysname = N'YourSnapshotDatabase'
   , @SQL nvarchar(MAX)
   , @DropSynonymsSQL nvarchar(MAX)
   , @CreateSynonymsSQL nvarchar(MAX);

--generate DROP SYNONYM script
SELECT @DropSynonymsSQL = (SELECT N'DROP SYNONYM ' + QUOTENAME(sch.name) + N'.' + QUOTENAME(syn.name) + N';
'
FROM sys.synonyms AS syn
JOIN sys.schemas AS sch ON sch.schema_id = syn.schema_id
FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(MAX)');

--generate CREATE SYNONYM script
SET @SQL = N'USE ' + QUOTENAME(@SnapshotDatabaseName) + N';
SELECT @CreateSynonymsSQL = (SELECT N''CREATE SYNONYM '' + QUOTENAME(sch.name) + N''.'' + QUOTENAME(obj.name) +
   N'' FOR '' + QUOTENAME(@SnapshotDatabaseName) + N''.'' + QUOTENAME(sch.name) + N''.'' + QUOTENAME(obj.name) + N'';
''
FROM sys.objects AS obj
JOIN sys.schemas AS sch ON sch.schema_id = obj.schema_id
WHERE
   obj.type IN(''U'', ''V'', ''P'')
   AND obj.is_ms_shipped = 0
FOR XML PATH(''''), TYPE).value(''(./text())[1]'', ''nvarchar(MAX)'');';
EXECUTE sp_executesql
     @SQL
   , N'@SnapshotDatabaseName sysname, @CreateSynonymsSQL nvarchar(MAX) OUTPUT'
   , @SnapshotDatabaseName = @SnapshotDatabaseName
   , @CreateSynonymsSQL = @CreateSynonymsSQL OUTPUT;

BEGIN TRY
   BEGIN TRAN;
   EXEC sp_executesql @DropSynonymsSQL;
   EXEC sp_executesql @CreateSynonymsSQL;
   COMMIT;
END TRY
BEGIN CATCH
   IF @@TRANCOUNT > 0 ROLLBACK;
   THROW;
END CATCH;
GO

來自 Aaron：創建快照的作業可以將新數據庫名稱儲存在表中，SSRS 可以使用該數據源動態創建其連接。此範例略有不同，但適用相同的一般概念

https://www.mssqltips.com/sqlservertip/4302/implement-dynamic-data-sources-in-sql-server-reporting-services/

引用自：https://dba.stackexchange.com/questions/209848