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
"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."