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?
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
15 People are following this question.
