Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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 ;)

share|improve this answer
    
Thank you Jon for your Advice Yes I am doing full backups and restores at the moment its not that bad I have scheduled the jobs around midnight for backup and restore. I have SQL Server 2008 R2 with Data centre Edition all the servers are licensed :) , We do not have any plans in place of upgrading to sql 2012 in near future. Is there any performance penalty for principal server in Database mirroring even when operating mode is set to high-performance (asynchronous) ? –  M.Ali Oct 29 '13 at 21:25
    
In high performance mode you still have some slight overhead due to the fact that you have to deal with two sets of log records, one committed to the log file and the other sent over the wire to be committed to the other database. However, I wouldn't be concerned about that. There are multiple DMVs designed to monitor mirroring performance, so develop a reasonable monitoring methodology and just keep an eye on it. –  Jon Boulineau Oct 29 '13 at 23:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.