SQL Server – Finding Destination Owner for Replication

sql serversql-server-2008sql-server-2008-r2t-sql

We have a table replicating to another server with a different Schema at the destination. When I set the replication up it shows as destination owner in the GUI. I am trying to create some reports and need to look at the publication and look for what the destination schema would be.

Unfortunately when I query the table [distribution].[dbo].[MSarticles] the destination owner column is null. Is there somewhere else I can find this info?

Thanks

Best Answer

Instead of using [distribution].[dbo].[MSarticles] use [publicationDatabase].[dbo].[sysarticles]. Use dest_owner column.

Detail here: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sysarticles-transact-sql

Related Solutions

Sql-server – SQL Server 2008 R2 replication high delivery latency

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]

Sql-server – Replication Not Replicating

SQL Server Replication can in some situations hide errors from you. The behavior you are seeing could for example be caused by the snapshot agent not finishing, likely because of an incorrectly configured permission set.

Check out my Stairway to Replication here: http://www.sqlservercentral.com/stairway/72401/

Levels 1 - 5 give a solid foundation to the security requirements when setting up replication. Level 10 describes how to troubleshoot a replication setup. This includes information about where to find error messages and how to interpret them.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 R2 replication high delivery latency

Sql-server – Replication Not Replicating

Related Question