德瑞克：SQL Server 學習筆記: [SQL Server]: Query Replication Configuration - Get completed setup replication information

2017-11-30

[SQL Server]: Query Replication Configuration - Get completed setup replication information

在擁有多組 SQL Server Replication 的環境，使用 T-SQL Script 方式來查詢相關的組態資訊，會是更有效率的方式。

連線到 Distributor，由 Distribution 取得 Replication 相關的組態資訊，這是 Lightweight(輕量級) 且無須使用 Publisher, Subscriber 的系統資源。

Get completed setup replication information

連線到 Distributor，執行以下 T-SQL Script:

若 Distributor 有多個 Distribution Database，此 Script 可完整取得各個 Distribution Database 的相關資訊。

查詢 Replication 的組態資訊，包含：
Publisher, Publisher Database, Publication, Subscriber, Subscriber Database, Distributor and Distribution Database, etc.

-- 111_Get completed setup replication information

-- EX1. Get completed setup replication information:
-- Publisher, PublisherDB, Publication, Subscriber, SubscriberDB, Distributor, Distribution
-- Connect to Distributor, for example: Dist01
USE master
DECLARE @tsql varchar(4000) 

SET @tsql = 'IF ''?'' IN((SELECT name FROM sys.databases WHERE is_distributor=1)) 
BEGIN
 USE [?]
 SELECT DISTINCT srv.name Publisher, p.publisher_db PublisherDB, p.publication Publication, 
  ss.name Subscriber, s.subscriber_db SubscriberDB, 
  @@SERVERNAME Distributor, DB_NAME() [Distribution Database]
 FROM MSpublications p INNER JOIN MSsubscriptions s 
  ON p.publication_id = s.publication_id 
  INNER JOIN sys.servers ss ON s.subscriber_id = ss.server_id 
  INNER JOIN sys.servers srv ON srv.server_id = p.publisher_id 
 ORDER BY 1,2,3
END';

EXEC sp_MSforeachdb @tsql;
GO

Review Replication Configuration on the Distributor

-- 121_View_Distributor_Distribution