I have 3 DB servers (DB1, DB2 & DB3 – all on Win 2008 R2 with SQL Standard 2008 R2 in the same datacenter). Let's assume AdventureWorks as an example database.
My goal:
– Setup DB mirroring with automatic failover i.e. DB2 becomes mirroring server
– Setup hourly transaction log backups for point in time recovery
– Setup reporting/warehouse environment i.e. DB3 becomes the warehouse/reporting server
Setting up DB mirroring and hourly txn log backups is easy but my question is – What are my options for replicating the principal database to the DB3 server for reporting/warehousing needs? Can I use log shipping considering I do txn log backups?
The AdventureWorks database on DB3 needs to be in read-only mode.
EDIT: The reporting database can be up to a day behind.
Best Answer
Quick points:
-Don't setup a separate 'transaction log backup' job outside of the 'log shipping' architecture. Doing so will break log shipping. Let log shipping handle all of your transaction log backups.
-You can use log shipping in 'STANDBY' mode but note that the instance will not be accessible when the server is doing a log restore. You can set the frequency in which to do log restores (for example, take a transaction log file backup every 5 minutes, restore to the reporting server every 4 hours) which should minimize connectivity issues.
-Paul Randall will probably kill me for this, but I have a 'mirrored' instance with a snapshot taken on it every 30 minutes. All queries are ran on the snapshot. I have not had a single complaint in 4 months of keeping this up. This was a 'intermediary' step before we could setup a replicated copy but it's working very well. Again, Paul Randall does not like this solution but it works for me thus far in this limited scenario (whilst being aware that often a limited scenario will turn out to be permanent solution).
-Have you considered a daily 'snapshot replication' job which refreshes the reporting instance? How far behind can your reporting server be?