SQL Snapshot Redirect

Question

Company is implementing Database Mirroring Asynchronous with Snapshot. This provides some Disaster Recovery with Reporting area. Server A Principal contains database which mirrors to Server B, Snapshot is taken on ServerB database.

To enable updated data, we will create database snapshots every 30 min on Mirror server databases. How do we redirect all SSRS/report stored procedure queries to latest snapshot? Our report queries are pretty short, general around 10 sec. Microsoft mentions programmatic solution below, not sure what that is? So SalesDB1030, SalesDB1100, SalesDB1130, 24clock added after dbname

Report queries may be as

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

Create a Database Snapshot

"To use a database snapshot, clients need to know where to find it. Users can read from one database snapshot while another is being created or deleted. However, when you substitute a new snapshot for an existing one, you need to redirect clients to the new snapshot. Users can manually connect to a database snapshot by means of SQL Server Management Studio. However, to support a production environment, you should create a programmatic solution that transparently directs report-writing clients to the latest database snapshot of the database."

Answer 1

One approach is to create a database containing only synonyms referencing the objects in the snapshot you need for reporting. Clients can then connect to the synonym database and run queries just as if they were using the snapshot directly.

Whenever a new snapshot is created, run a script to update the synonyms with the new snapshot database name. Below is an example that recreates synonyms for all tables, views, and procedures in the snapshot. Add additional object types if needed.

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

From Aaron: The job that creates the snapshot could store the new database name in a table, and SSRS can use that data source to dynamically create its connection. This example is slightly different, but same general concept applies

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