what are the pros and cons of a replicated database?

db042190 1,521 Reputation points
2022-07-18T11:23:19.397+00:00

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?

Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-07-18T15:35:13.767+00:00

    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.

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,771 Reputation points
    2022-07-18T12:24:16.433+00:00

    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.

    0 comments No comments

  2. db042190 1,521 Reputation points
    2022-07-18T13:39:50.887+00:00

    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?


  3. Tom Phillips 17,771 Reputation points
    2022-07-18T18:19:47.073+00:00

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

    0 comments No comments

  4. CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
    2022-07-19T09:55:12.763+00:00

    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".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.