question

raadee avatar image
raadee asked

Read only copy of whole database - Your prefered method?

Goal: To have a copy of production database on another server for reporting and other read intensive operations. SQL version/ed: 2005 Enterprise DB size: 300GB, the whole database is to be "synchronized". Requirements: 1 Hour old data, disconnects are ok but not prefered. So a log shipping (standby)solution with Red Gate SQL Backup Pro meets the criteria. Con: Disconnected clients. Mirroring with Snapshots and programatically redirected clients does too. Con: The redirection of clients to new snapshots. Replication.. well I rather not? Does anybody have any other good solution for this case? Third party magical tool?
replicationmirroringlogshipping
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
In addition to the choices you list, with SQL Server 2012 & 2014 you also get [Availability Groups][1] that can set up read only secondaries. These are pretty awesome because it's much easiser to set up and maintain than replication, but you don't have to deal with disconnects or changes to connection strings. Given the choice, I'd go there. Otherwise, in order by degrees of pain, Log Shipping, Replication, Snapshots. [1]: http://technet.microsoft.com/en-us/library/ff877884.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yeah Availability groups would be nice but I need to stay on 2005 for now. I feel your degree of pain and will problably go with that order. Thanks Grant.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.