0

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.

Improve this question
  • How near real time does your reporting server need to be? The further away from 'real time' we get, the more options we have. – Ali Razeghi Jun 17 '13 at 18:01
  • Just posted a comment below. It can be up to a day behind. But I'll update the original question too. – avakharia Jun 17 '13 at 18:08
1

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?

Improve this answer
  • Reporting instance doesn't need to be real time but one day lag is acceptable. While doing log shipping to create the reporting DB, it increases the log file disk space to be as much as the database size sometimes and that has created problems for us with not enough storage on the disk. That's why doing transaction log backup's would not allow the log file to grow that big. I haven't looked in to snapshot replication but I'm open to it. If you can share some articles or way of doing it, that will be helpful. – avakharia Jun 17 '13 at 18:05
  • Quick Points: If your transaction logs are blowing up in size, you can increase the frequency of transaction log backups to keep it small or troubleshoot as to why they are blowing up. Reindexing, shrinking, and other factors can cause this to happen so just a heads up. Are you OK with having the queries ran on the mirrored server? If so, you can make a 'SNAPSHOT' of the mirrored database and it'll work fine, it'l just be a different name and there won't be any snapshot replication required. – Ali Razeghi Jun 17 '13 at 18:29
  • Snapshot (not replication, this would run on the mirrored instance and create a snapshot of the data). I would put this in a job and have it refresh daily: msdn.microsoft.com/en-us/library/ms175876.aspx – Ali Razeghi Jun 17 '13 at 18:29
  • Snapshot Replication. This requires agents and a distributor setup, I would personally just go with 'snapshots' instead of 'snapshot replication': databasejournal.com/features/mssql/article.php/1458491/… – Ali Razeghi Jun 17 '13 at 18:30
  • I'd rather keep the mirroring instance separate from reporting as I do have 3 DB servers. And plus there are more databases that I'll be mirroring eventually. I'll look into snapshot/replication. Thanks for sharing. – avakharia Jun 17 '13 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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