2

I want to sync databases to a secondary server that will be used read-only for SQL Server Reporting Services. These syncs can be done once or twice nightly and mid morning; synced database sizes would be 400 Gb, 50 GB, and 60 GB.

What are my options to approach this scenario?

I planned to implement log shipping using RedGate's product so my backup would be compressed and easier to implement, however I am not sure how to maintain the logfile size for maintenance purposes. For instance, when do I shrink the logfiles since they may grow enormous until I take a log backup during the night?

Are there any other options like mirroring or replication?

Improve this question
1
  • i'm confused. do you want high availability (so, high availability tag)? do you want disaster recovery (so, log shipping tag)? or do you just want a copy of the data that you can use to serve data to SSRS so that you're not interfering with production? if what your question states is taken at face value (option 3), then set up replication.
    – swasheck
    Sep 17, 2014 at 16:03

3 Answers 3

Reset to default
2

If you have SQL Server 2012 Enterprise Edition you could implement Always On Availability groups.

This is an extension of mirroring (built on-top of windows clustering) which allows the mirrored database to be a readable secondary. The benefit of this is that the secondary is updated as the primary database is updated. However it is expensive as the secondary server does need a full licence.

You can implement log shipping and have the secondary databases in STANDBY mode, which would make them readable. You can then control when the logs are applied. I would recommend a short interval between log backups, this should prevent your logs from expanding so that you do not have to shrink them.

Improve this answer
2
  • You should have waiting till OP tell about SQL server version and edition. why would you recommend somebody to buy 2012 and that too implement AlwaysON if he just need to sync 3 times a day.
    – Shanky
    Sep 17, 2014 at 15:56
  • 1
    I didn't mean it to come out that way. I did say if he had SQL Server 2012 Enterprise he could use Always On. I also went through using STANDBY mode for log shipping.
    – dbafromthecold
    Sep 17, 2014 at 16:02
1

In addition to DBAFromTheCold's response, another approach would be to use replication and replicate only the tables that you need to use for reporting to the reporting server. This may be a better option for you for a few reasons: 1) you will only get the data that you need 2) there will be no downtime for synchronization and 3) you can index the reporting queries differently than the transactional queries on the primary.

Improve this answer
0

It sounds to me like a 'redundant rack' style of reporting server would support your needs. Implemented by doing a nightly restore to the reporting server from the production backups. Your reporting data will be one day behind, but this is usually up-to-date enough to meet client needs.

Improve this answer

Your Answer

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

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