0

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

  • What version of sql server and edition are you using ? – Kin Shah Jun 3 at 19:51
  • Publisher is 2008 R2 Enterprise and subscriber is 2012 Business Intelligence – BustaRhymes313 Jun 4 at 11:46
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.