what are the pros and cons of a replicated database?

Question

hi we run 2019 enterprise. Our new boss is wondering if a replicated oltp might solve some of out warehousing woes. she is probably thinking one of 2 things...1) start reporting off the replicated environment or 2) lessen the impact of reading from the oltp when loading the warehouse.

i worked once in an environment where the source data was from a replicated oltp. it didnt seem bad but from what i remember, if a column needs to be added to the oltp, the replicated table has to be rebuilt because the replicated environment has to match the source schema exactly. i dont recall if the same indexes have to be built in the replicated environ.

obviously oltp's arent shaped well for warehousing anyway so hopefully she is thinking more along the lines of #2.

I know cdc and other techniques are available for sourcing warehouses. So why would i want replication?

Answer 1

Your question is general and vague. There are many ways to skin this cat, and what is the best way depends on the circumstances.

Many people go with Availability Groups with readable secondaries, as this is also provides a HA/DR solution. I would also say that in comparison to Transactional Replication, they are easier to manage. But the distinct drawback is that the secondary is a 100% of the source database.

Transactional Replication has more moving parts, and as you mention schema changes require more planning. The upside is that the subscriber databases does not have to be 100% copies of the source, but you have more flexibility. For instance, you can have indexes in the subscriber to support reports that are not in the publisher, and thus does not slow down the OLTP business. And for matter you can also have less indexes if you are so inclined.

But while you have some flexibility, TR is probably not what you should go for if you want a completely different schema in the warehouse. As I said, there are many ways to skin the cat.

Answer 2

I have used transactional replication a lot for that purpose and it works fine.

The main con is you first need to initialized the snapshot. Depending on the size of your OLTP database, that can be painful. Also, you need to monitor the replication to make sure it is actually working. Otherwise the log will grow and grow until you run out of disk space and the database crashes.

Every table you replicate must have a PK.

Answer 3

thx tom, which purpose? reporting (off of) or lessening impact (ie avoiding deadlocks) from reading?

and if a table changes (new column), do i need to reload that entire table somehow from history in the replicated environment? or is it more like the whole db?

and do all the indexes from the source have to exist in the replicated environment?

Answer 4

As Erland said, you could also look at Availability Groups.

Answer 5

Hi @db042190 ,

Please check if below blogs could help you.

The Good, Bad, and Ugly of Data Replication

What is the best option for High availability on a data warehouse?

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".