Sql-server – Best Possible Solution for Reporting Mirroring or Replication

mirroringreplicationsql serversql-server-2008-r2ssrs

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?

MY Requirments

  1. I can live with a few minutes old data.
  2. 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)
  3. 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.

Best Answer

Using mirroring alone won't be sufficient since the mirrored secondary is not available for querying. You have to create and maintain snapshots, which can be annoying.

Your options are, in no particular order:

  • Mirroring with snapshots : effective but has management overhead.
  • Backup and restore : Are you restoring from full backups rather than differential or logs? If so, you may be able to reduce the time spent on restores.
  • Log shipping : management overhead, database is unavailable when log backups are being applied
  • Replication : challenging from a management perspective, not really intended for syncing full databases
  • Report from transactional database - This option is all too often discarded over unsubstantiated performance concerns. Those concerns can also be mitigated somewhat through the use of Resource Governor, snapshot isolation, etc. This is an underutilized option, IMO.
  • ETL with SSIS or a similar tool. You would have to do your own schema changes, however.
  • Availability Groups, if you can upgrade to 2012 (I assume you're on 2008 R2). This is really the best option available these days.
  • Idera makes a tool called Virtual Database that allows you to mount and query a backup file. It's an interesting option in some cases.

It's difficult to make a clear recommendation without knowing a lot more information about your environment. I've used most of these methods to varying degrees of success. Note that most places end up building out more robust data marts / data warehouses for reporting and analytics, so you'll probably end up with the ETL route one day.

Oh, and make sure you have licensed the secondary server ;)

Related Question