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?