Bad performance in SQL Server 2019 to SQL Server 2016 Snapshot replication

Juan Antonio Riquelme Cutillas 1 Reputation point
2021-03-11T21:35:11.003+00:00

Hi.

I'm trying to replicate a database from SQL Server 2019 to another database on SQL Server 2016. Both servers are connected through internet.
I have a perfomance trouble with a table with 80 millions of records. SQL Server does a bulk copy of 5 millions of records each step.
First step took about 3 hours.
Second step took about 4 hours.
Third step took about 5 hours.
Fourth step took about 6 hours.
Now is in fifth step.
The message in the Replication Monitor is: The process is running and is waiting for a response from the server.
Table has not FK and has not indexes. Only have a PK (Identity). Database model is inherited.
Any ideas to accelerate the process or any configuration option that I forgot?

I updload an image of the process log.
76914-logreplicacion-forum.png

TIA.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,984 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CathyJi-MSFT 22,306 Reputation points Microsoft Vendor
    2021-03-12T08:40:59.627+00:00

    Hi @Juan Antonio Riquelme Cutillas ,

    Suggest you check if there is any blocking process that is stopping your replication.

    USE Master  
    GO  
    SELECT *   
    FROM sys.dm_exec_requests  
    WHERE blocking_session_id <> 0;  
    GO  
    

    Or

    USE Master  
    GO  
    SELECT session_id, wait_duration_ms, wait_type, blocking_session_id   
    FROM sys.dm_os_waiting_tasks   
    WHERE blocking_session_id <> 0  
    GO  
    

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


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.