July 29, 2011 at 8:07 am
Hi,
Hoping to hear more experienced views on how to set up the following replication scenario:
Database x is restored early morning each day from the live server to the reports server.
We now want to keep a couple of the tables on the reports server synced with live.
The solution I've come up with so far is Transactional Replication with the report server as a pull subscriber - each day the process is:
1. Backup the live database
2. Delete the Repl-Distribution job from the reports server
3. Run the Repl-Snapshot job on the live server
4. Restore the database
5. Recreate the subscription from scratch (running sp_addpullsubscription and sp_addpullsubscription_agent)
Please can anyone tell me - is there a better way to do this?
August 4, 2011 at 9:49 am
Instead of Backing up and restoring the report server every night, why not snapshot all tables that are needed for report server every night(Except the tables in Transactional replication) and keep your Transactional replication intact?
This has less chances of getting messed up.
-Roy
September 13, 2011 at 8:34 am
Belated thanks Roy - why indeed? This is certainly the way to do it, might get rid of the time-consuming backup/restore to do it properly like this in the fullness of time.
September 16, 2011 at 2:20 pm
i have used transactional replication for reporting
Make sure you set all the report users to read only so they dont change it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply