Hi I have SQL SERVER 2008R2 Data Centre Edition and I use SSRS and a Vendor application for reporting. I have a Reporting Server where we backup and restore everyday from Live Server to have Data available for our reports.
I face many problems because of this any code developed has to wait a day until the restore is done to test. Any code developed on Reporting Server is Over written. Surprise surprise reports show a day old data and I could go on and on.
I know I have two options available Mirroring and Replication:
Mirroring will allow read-only mode and will reports run fine? Does this Read-only mode any other implications that I am missing ?
Replication I do not have Primary Keys on all the tables so I am left only with the option of Merge replication which uses a globally unique identifier (GUID), I cannot let sql server to add this column to all the tables in my databases.
Can anyone please advise me a solution for having a Live Copy of Sql Server Database on another Server for Reporting Purposes?
- I can live with a few minutes old data.
- Any changes made on Production Server needs to be copied over to Reporting Server i.e Data, Sql Server Objects (Tables, Views, Store Procedures, Everything)
- Any Objects created on Reporting Server does not need to copied over to production server/databases.
Please advise any solution as it will be a great help Thank you in Advance.