Sql-server – Restoring data into a replicated table after failed purge process

I presume you have substantial data volumes if you have separate staging and data warehouse servers. From your posting It sounds like your staging server is doing a bit more than just staging the data. You describe merging data into the fact table on the warehouse server, so I presume that the staging server actually has ETL processing on it as well.

I would not generally have recommended a transform-push approach like this; running ETL processing from the warehouse and pulling from a staging area is generally better. The merge happens on the warehouse. If you need to scale out you can replicate the warehouse, and the same operations will happen on the source and targets.

However, I guess you're stuck with the transform-push architecture, so you've got a couple of options:

• Use SSIS or another ETL tool to copy the incremental load to a staging table on the warehouse and merge it to the fact tables from there. This will be fairly efficient and can be done with set operations - i.e. a merge statement or insert and update.

• Replicate the staging area into another database on your warehouse server and execute the merge from there after the ETL processing on the staging server has run. This is probably more fiddly than the first option, and I can't really see any advantages to it.

• If your staging and warehouse databases are located on the same database instance then you can create synonyms in the warehouse and merge through the synonyms. The synonyms prevent a need to hard-code database refererences.

Below script will help you find out how many undistributed commands are there. Also, do you see any errors in sql server or windows logs ? Is it just slow or any failures reported ?

SELECT ( CASE 
           WHEN mdh.runstatus = '1' THEN 'Start - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '2' THEN 'Succeed - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '3' THEN 'InProgress - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '4' THEN 'Idle - ' + CAST(mdh.runstatus AS 
                                                          VARCHAR) 
           WHEN mdh.runstatus = '5' THEN 'Retry - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '6' THEN 'Fail - ' + CAST(mdh.runstatus AS 
                                                          VARCHAR) 
           ELSE CAST(mdh.runstatus AS VARCHAR) 
         END )                                   [Run Status], 
       mda.subscriber_db                         [Subscriber DB], 
       mda.publication                           [PUB Name], 
       RIGHT(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 )), 
       Len(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 ))) - ( 
       10 + Len(mda.publisher_db) + ( CASE 
                                          WHEN mda.publisher_db = 'ALL' THEN 1 
                                          ELSE Len(mda.publication) + 2 
                                        END ) )) [SUBSCRIBER], 
       CONVERT(VARCHAR(25), mdh.[time])          [LastSynchronized], 
       und.undelivcmdsindistdb                   [UndistCom], 
       mdh.comments                              [Comments], 
       'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + 
       CAST(mdh.error_id AS VARCHAR(8))          [Query More Info], 
       mdh.xact_seqno                            [SEQ_NO], 
       ( CASE 
           WHEN mda.subscription_type = '0' THEN 'Push' 
           WHEN mda.subscription_type = '1' THEN 'Pull' 
           WHEN mda.subscription_type = '2' THEN 'Anonymous' 
           ELSE CAST(mda.subscription_type AS VARCHAR) 
         END )                                   [SUB Type], 
       mda.publisher_db + ' - ' + CAST(mda.publisher_database_id AS VARCHAR) 
                                                 [Publisher DB], 
       mda.name 
       [Pub - DB - Publication - SUB - AgentID] 
FROM   distribution.dbo.msdistribution_agents mda 
       LEFT JOIN distribution.dbo.msdistribution_history mdh 
         ON mdh.agent_id = mda.id 
       JOIN (SELECT s.agent_id, 
                    maxagentvalue.[time], 
                    SUM(CASE 
                          WHEN xact_seqno > maxagentvalue.maxseq THEN 1 
                          ELSE 0 
                        END) AS undelivcmdsindistdb 
             FROM   distribution.dbo.msrepl_commands t (nolock) 
                    JOIN distribution.dbo.mssubscriptions AS s (nolock) 
                      ON ( t.article_id = s.article_id 
                           AND t.publisher_database_id = s.publisher_database_id 
                         ) 
                    JOIN (SELECT hist.agent_id, 
                                 MAX(hist.[time]) AS [time], 
                                 h.maxseq 
                          FROM   distribution.dbo.msdistribution_history hist ( 
                                 nolock) 
                                 JOIN (SELECT agent_id, 
                                              Isnull(MAX(xact_seqno), 0x0) AS 
                                              maxseq 
                                       FROM 
                                 distribution.dbo.msdistribution_history ( 
                                 nolock) 
                                       GROUP  BY agent_id) AS h 
                                   ON ( hist.agent_id = h.agent_id 
                                        AND h.maxseq = hist.xact_seqno ) 
                          GROUP  BY hist.agent_id, 
                                    h.maxseq) AS maxagentvalue 
                      ON maxagentvalue.agent_id = s.agent_id 
             GROUP  BY s.agent_id, 
                       maxagentvalue.[time]) und 
         ON mda.id = und.agent_id 
            AND und.[time] = mdh.[time]