3

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.

Improve this question

2 Answers 2

Reset to default
12

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

Improve this answer
4
  • 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
    Commented Oct 29, 2013 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
    Commented Oct 29, 2013 at 23:44
  • Jon, thanks for the insights. But why is replication (specifically the transactional variant) more management overhead than for example using ETL? And why would I choose ETL over transactional replication?
    – syneticon-dj
    Commented Apr 22, 2014 at 14:34
  • 1
    I'm no TR expert, so I can't go into much depth. However, it is notoriously complex and finicky. There is plenty out there on the internet (like this brentozar.com/archive/2013/09/…). At a high level, you'd chose ETL if you wanted to (T)ransform the data in any way or if you just wanted to avoid the complexities of replication (managing jobs, restarting replication if anything goes sideways, etc.). Someone else with more replication experience could give you a better answer.
    – Jon Boulineau
    Commented Apr 23, 2014 at 21:02
1

I have personally had experience with Transactional Replication. In my case, I configured the Distributor at the Report Server, and then created the Publication on Live server to be controlled from the Report Server. The Subscription from the Report Server worked perfectly.

With this configuration, I was successfully able to replicate data from the Live server with only the Log Reader (on the live server) as an overhead, while the Report server's job was to receive replicated commands, and distribute them to the subscribed database(s) independently of the live server.

All in all, this option is perfect.

Points to ensure you have configured beforehand and afterwards:

  1. Ensure your log files are on a separate hard drive.
  2. Ensure you never modify data in the Subscription database. I have encapsulated all user access (on the report server) through roles and public logins with little (or required) permissions.
  3. Lastly, I have configure this over a domain network, and over a non-domain network. Personally, the domain network option is far simpler. But both are possible (as the snapshot articles and bulk copy files are used to initialise the subscription - eventually, the commands are passed directly to the Distributor, and the rest of the magic happens on the Report server)

I have experience in all the other areas of database copying. I would agree with the previous statement, in that you will eventually end up utilising a data mart; unless your company has this configured through a third party agent.

For me though, replication is simpler (while difficult to configure), as later on it always pays off.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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