Checking Replication Programatically.
-
On many occasions, I have been asked to find out if a table or table(s) are being replicated to various servers. This is a tedious task. Is there a way, using t-sql, that I can check if a table has been published for replication and get eh name of the publication as well as the subscribers of that publication? (BTW - I am new to the administration side of SQL Server but experienced in the programming side)
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
You can run the following and see if there is an article for a particular table, and if so, what the Publication name is (from the database in question):
select * from sysarticles
where Dest_Table =
There is also the syssubscriptions system table table - 'Contains one row for each subscription in the database. This table is stored in the publication database.'
-
So the sql:
select * from sysarticles where Dest_Table = <table> and select * from syssubscriptions where artid=<artid from previous> will tell me if the table is published and has a subscriber.
I assume there will one record for each subscriber for that publication or is it only one entry period? What other tables can be used to make this information less cryptic. IE What is the actual server subscribing to the publication. For example, table one is being transactionally replicated as a push. Server A and B are subscribers to this publication. I want to show <publication name> is pushing data from <table name> to server <server name>.
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
I created a few utilities for my own use to list what tables are being replicated, including filter data, and also to verify that the record counts on the publisher & the subscriber are the same (or close due to latency). Let me know if you'd like some examples.
-
That would be good. I will take whatever you have to offer.
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
and if the replicated data was done with merge then have alook at
sp_validatemergepulication and sp_validatemergesubscription
or if it is transactional or snapshot:
sp_publication_validation may prove usefull
* Noel -
Here's what I run to build replication info into a table, and verify the counts between publisher & subscriber.
I use the "replication_stats" table for reference whenever I want to see what's being replicated. It's simpler than waiting for Publication properties to display one at a time.
Disclaimer: I quickly gathered this together & "genericized" some of it, so let me know if it seems something's missing or not clear.
++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- GET REPLICATION INFO & VERIFY COUNTS BETWEEN PUBLISHER & SUBSCRIBER
-- All Tables & SPs are created in my "Utility" database, not a production DB
-- Shows statistics of replicated tables.
CREATE TABLE [dbo].[Replication_Stats] (
[name] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[publisher_db] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filter_clause] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[artid] [int] NOT NULL ,
[objid] [int] NOT NULL ,
[pubid] [int] NOT NULL ,
[dest_db] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[publisher_server] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dest_server] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-------------==========================================================================================
-- Shows counts of records in replicated tables
CREATE TABLE [dbo].[Replication_Counts] (
[Rep_Table] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_DB_Pub] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_DB_Pub#] [int] NULL ,
[Rep_DB_Sub] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_DB_Sub#] [int] NULL ,
[Rep_Count_Date] [datetime] NULL ,
[Rep_Filter_Clause] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_Pub_Server] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_Sub_Server] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Replication_Counts] ADD
CONSTRAINT [DF_Replication_Counts_Rep_Filter_Clause] DEFAULT (' ') FOR [Rep_Filter_Clause]
GO
-------------===========================================================================================
-- Shows record count discrepancy between publisher & subscriber
CREATE TABLE [dbo].[replication_mismatches] (
[Rep_Table] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_DB_Pub] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_DB_Pub#] [int] NULL ,
[Rep_DB_Sub] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_DB_Sub#] [int] NULL ,
[Rep_Count_Date] [datetime] NULL ,
[Rep_Filter_Clause] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_Pub_Server] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep_Sub_Server] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-------------============================================================================================
-- >>>>>>>>>>>>>>>>>>>>>>>>>>> STEP 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<
--Build Replication_Stats from system replication info. (I run this, along w/ other unrelated stuff, as a DTS, which is Step 1 in my nightly job)
truncate table Utility_DB_NameHere.dbo.Replication_Stats
-- Insert PRD Replication info
insert into Utility_DB_NameHere.dbo.Replication_Stats
select art.name, publisher_db,
pub.description, filter_clause,
art.artid, objid, art.pubid ,sub.dest_db, 'PUBLISHER_SERVERNAME_HERE', 'SUBSCRIBER_SERVERNAME_HERE'
from Publisher_DB_NameHere.dbo.sysarticles art
join "distribution"."dbo".MSarticles msart on name = article
and msart.article_id = art.artid
join Publisher_DB_NameHere.dbo.syspublications pub
on pub.pubid = art.pubid
join Publisher_DB_NameHere.dbo.syssubscriptions sub on art.artid = sub.artid and srvid = 2
order by art.name
-------------============================================================================================
-- >>>>>>>>>>>>>>>>>>>>>>>>>>> STEP 2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<
-- This sp gets the counts of the replicated table from the publisher ( Production) based on any filter, and gets the count from the subscriber
--and puts a record in table replication_counts. Later, another job can run to look at the 2 counts and see if they are the same or not
-- (They should be close, depending on frequency of replication)
-- I don't truncate "replication_counts" because I thought I may want history
CREATE procedure sp_ReplicationCounts as
-- temp table holds the select statements to be executed
create table #select_statements
(statement_id int identity(1,1) not null,
statement_text varchar(4000))
-- the INSERT to the temp table R1
insert into #select_statements
select 'insert into UtilityDBNameHere.dbo.replication_counts select '''+ [name] + ''','''
+ publisher_db + ''',(select count(*) from '
+ publisher_db + '.dbo.' + [name] + ' with (nolock) '
+ case
WHEN filter_clause IS not NULL THEN ' where ' + rtrim(convert(varchar(500),filter_clause)) + '), '''
ELSE '), '''
end
+ dest_db + ''',(select * from openrowset (''SQLOLEDB'',''SUBSCRIBER_SERVERNAME_HERE'';''YOUR_SQL_LOGIN'';''YOUR_LOGIN_PASSWORD'','' select count(*) from '
+ dest_db + '.dbo.' + [name] + ' with (nolock) ' + ''')), getdate(), ''''' + ' ,''' + rtrim(publisher_server) + '''' + ' ,''' + rtrim(dest_server) + ''''-- blank in field after getdate to be updated later w/ filter clause details
from Utility_DB_NameHere.dbo.replication_stats
where publisher_server = 'PUBLISHER_SERVERNAME_HERE'
-- the statement execution
set nocount on
declare @statement_text varchar(4000),
@i int,
@rows int
select @rows = count(*) from #select_statements
select @i = @rows
declare statement_cur cursor for
select statement_text
from #select_statements
order by statement_id
open statement_cur
fetch next from statement_cur
into @statement_text
while @@FETCH_STATUS = 0
begin
exec (@statement_text)
fetch next from statement_cur
into @statement_text
set @i = (@i - 1)
end
close statement_cur
deallocate statement_cur
-- Update Replication_Counts with filter information
update Utility_DB_NameHere.dbo.replication_counts set rep_filter_clause = rtrim(convert(varchar(500),Utility_DB_NameHere.dbo.replication_stats.filter_clause))
from Utility_DB_NameHere.dbo.replication_stats
where name = rep_table and dest_db = rep_db_sub and publisher_db = rep_db_pub
and publisher_server = 'PUBLISHER_SERVERNAME_HERE'
-- cleanup
drop table #select_statements
set nocount off
GO
-------------============================================================================================
-- >>>>>>>>>>>>>>>>>>>>>>>>>>> STEP 3 <<<<<<<<<<<<<<<<<<<<<<<<<<<<
-- Identify discrepancies between records counts on PRD and record counts in replicated tables, taking filer clause(s) into account.
-- Drop & recreate mismatch table with new data if there are records in Replication_Counts
CREATE PROCEDURE tp_Replication_Mismatches AS
SELECT *
FROM Replication_Counts
WHERE (CONVERT(char(10),Rep_Count_Date,101) = CONVERT(char(10),getDate(),101)) AND (Rep_DB_Pub# <> Rep_DB_Sub#) OR
(CONVERT(char(10),Rep_Count_Date,101) = CONVERT(char(10),getDate(),101)) AND (Rep_DB_Pub# <> Rep_DB_Sub#)
ORDER BY rep_db_sub, rep_table, rep_count_date
IF @@ROWCOUNT > 0
begin
drop table replication_mismatches
SELECT *
into replication_mismatches
FROM Replication_Counts
WHERE (CONVERT(char(10),Rep_Count_Date,101) = CONVERT(char(10),getDate(),101)) AND (Rep_DB_Pub# <> Rep_DB_Sub#) OR
(CONVERT(char(10),Rep_Count_Date,101) = CONVERT(char(10),getDate(),101)) AND (Rep_DB_Pub# <> Rep_DB_Sub#)
ORDER BY rep_db_sub, rep_table, rep_count_date
end
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply