Sql-server – Transactional Replication data during rebuild

etlreportingsql servertransactional-replication

To offload reporting I have transactional replication running between my prod box and my reporting server (top image #1). This works great until the snapshot has to be rebuilt. During this time users/apps/reports are reading from an incomplete dataset while the reporting db is being rebuilt.

Does anyone else experience this problem? What is the best work around?

Is #2 in the image a reasonable solution? Create a shell database on top of a data repo. The reporting DB no longer contains data but only views that replicate the structure of the tables in the data repo. During a rebuild of the "data repo" database the "reporting db" is set offline to prevent users/apps/repos from reading incomplete data?

enter image description here

Best Answer

The reporting DB no longer contains data but only views that replicate the structure of the tables in the data repo. During a rebuild of the "data repo" database the "reporting db" is set offline

Yes, that's reasonable. You can further optimize this approach by pointing the users to a snapshot or restored copy of the subscriber database during the downtime, eg per https://techcommunity.microsoft.com/t5/DataCAT/Microsoft-SQL-Server-Database-Snapshots-and-Synonyms/ba-p/304987, or by reinitializing the subscription from a backup, which might be faster than using a snapshot.

Or you could just disable the reporting users logins and kill their sessions during the downtime.