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?
Best Answer
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.