1

We need a reporting Database & the Production Database.

The reporting team is unsure of which tables in the database are used in the reporting tools and hence wants us to replicate the complete database to the reporting.

I would like to understand what will be the pros and cons of replicating the complete database?

What I think:

Pros: The reporting databases will serve as a disaster recovery option too.

Cons: More disk consumption on Reporting Server, higher resource utilization and more difficult maintenance.

Anything more?

0

1 Answer 1

1

I have replicated entire databases, performance is an issue with frequently changed data when your talking larger volumes. For us, a larger amount of data was 500gb of data being supported by 32gb of memory and 8 cores (didn't have control or say in hardware specs). We had transactional replication, and about 1-2 times a month it would get behind, far enough behind where we had to re-snapshot it to get our subscriber current (this would take us about 4 hours each time). Whenever replication had an issue and data started to queue up, it just couldn't catch up.

2
  • I agree. Performance goes for a toss. But for smaller databases <30 GB, do you think it can be an effective HA/DR option too?
    – Alok Singh
    Jul 28, 2016 at 13:46
  • Definitely, I've been in several situations where this was the goal. It's not good enough for 5 9s support, or even 3 9s, but it can serve as some form of HA/DR. If your intent here is purely for reporting purposes and the databases are small, another option is to just do drops and restores. This is typically fine for development and reporting purposes, and there is very little impact to the production system during business hours.
    – Jason B.
    Jul 28, 2016 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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