locked
Does Merge Replication have the Performance issues? RRS feed

  • Question

  • Hi Experts,

    I am working with sql server replication as a part of migrating the databases from old to new environment. I had two servers stage and prod so that some tables need to be replicated from stage to the prod. Previously we had a Snapshot replication on the old environment, we were planned to go for another type of replication this time (on the new servers) since there is a latency issue with the snapshot replications that customers were unable to view the report at a particular period of time. The tables which I am choosing doesn't had a Primary key on them. So, the transactional replication will not work. So, I have opted for Merge replication (since UID will be creating automatically on a table), but some articles specifying that we will be having performance issues.

    If this is true, which type of replication would be better considering low latency and Good performance? 

    What are the performance issues that we can able to face with Merge replication? 

    Or can we create a new column as PK with identity property and use the Transactional replication?

    Old environment: DB Size (Prod): 50GB, DB Size (stage): 80GB (SQL Server 2008R2)

    Thanks.

     


    Tuesday, July 25, 2017 9:42 AM

Answers

  • I can't answer the question as to whether you will have performance issues or not. It all depends on your workload. If you have no workload you will not have performance issues. If you have an extreme workload you will have performance issues.

    You will have less performance issues with transactional replication than you do with merge.

    Yes, you can add an identity column to your tables without a primary key and then add a PK to that identity column.

    Tuesday, July 25, 2017 2:03 PM
    Answerer

All replies

  • Yes, it uses tracking triggers which can slow down all dml and the sync process can be lengthy - 10s to several minutes - but this depends on your workload.

    I would use transactional replication over merge. You will need to add pk's to those tables.

    Tuesday, July 25, 2017 12:04 PM
    Answerer
  • Hi Cotter,

    Does we have performance issues for the database size of 50GB? If yes, I am ready to opt for transactional replication. I will try to add a unique column. 

    Can I add an identity column and assign PK to it? 


    Tuesday, July 25, 2017 1:15 PM
  • I can't answer the question as to whether you will have performance issues or not. It all depends on your workload. If you have no workload you will not have performance issues. If you have an extreme workload you will have performance issues.

    You will have less performance issues with transactional replication than you do with merge.

    Yes, you can add an identity column to your tables without a primary key and then add a PK to that identity column.

    Tuesday, July 25, 2017 2:03 PM
    Answerer