I have been tasked with a taking a copy of our production database and replicating it to a second server on the network for our report engine to run reports from. Currently we did a backup of the production database and restored it to the report server. We need this type of action to take place every sunday night. Originally I was thinking of scripting a backup and a restore but then I found out about SQL replication.
I wanted to check with some SQL experts to verify this would be the best method of doing this. That is, using replication to basically make a copy of the production database on a secondary report server.
What do you think?
What are the risk?
Any performance issues we may see from the publisher "Production Server"?
Is it a must to have a dedicated server for the distributor or would the publisher server or
report server "Only Subscriber" be ok to use for this role.
And last...Is there a better method of doing this.