SQL Replication: Which tables/views should be a part of replication?

Posted on

Question :

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?

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *