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,281 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CathyJi-MSFT 21,121 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.