Replication

Script to run on Distribution database

This will bring all the articles information which relates to publisher database , servername and distributor agent job list etc..

USE Distribution 
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
-- Get the publication name based on article 
SELECT DISTINCT  
srv.srvname publication_server  
, a.publisher_db 
, p.publication publication_name 
, a.article 
, a.destination_object 
, ss.srvname subscription_server 
, s.subscriber_db 
, da.name AS distribution_agent_job_name 
FROM MSArticles a  
JOIN MSpublications p ON a.publication_id = p.publication_id 
JOIN MSsubscriptions s ON p.publication_id = s.publication_id 
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid 
JOIN master..sysservers srv ON srv.srvid = p.publisher_id 
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id  
     AND da.subscriber_id = s.subscriber_id 
ORDER BY 1,2,3  

Script to run on Publisher database

This script returns what publications have been setup. This will go through all the published databases and return information if the database has replication enabled. Sometimes, I just want to see the publication name and subscriber server names (no articles) to see what servers are being used with replication other times I want all of the information, so I added a variable called @Detail and if you set @Detail = 'Y' it will return data with the article list. Any other value will only return the publisherDB, publisherName and SubscriberServerName


-- Run from Publisher Database  
-- Get information for all databases 
DECLARE @Detail CHAR(1) 
SET @Detail = 'Y' 
CREATE TABLE #tmp_replcationInfo ( 
PublisherDB VARCHAR(128),  
PublisherName VARCHAR(128), 
TableName VARCHAR(128), 
SubscriberServerName VARCHAR(128), 
) 
EXEC sp_msforeachdb  
'use ?; 
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1 
insert into #tmp_replcationInfo 
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
' 
IF @Detail = 'Y' 
   SELECT * FROM #tmp_replcationInfo 
ELSE 
SELECT DISTINCT  
PublisherDB 
,PublisherName 
,SubscriberServerName  
FROM #tmp_replcationInfo 
DROP TABLE #tmp_replcationInfo 


Script to run on Subscriber database

This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find orphaned subscribers. This is rather simple since there is not much information to pull.


-- Run from Subscriber Database 
SELECT distinct publisher, publisher_db, publication
FROM dbo.MSreplication_subscriptions
ORDER BY 1,2,3
Ref : Link
Remove Article From Transaction Replication
EXEC Sp_dropsubscription
    @Publication = N'InterSiteTransfers',
    @Subscriber = N'SQLREP\REP',
    @Article = N'xtblOrgUntCategory'


EXEC Sp_droparticle
  @Publication = N'InterSiteTransfers',
  @Article = N'xtblOrgUntCategory',
  @Force_Invalidate_Snapshot = 1;
GO

Before running the sp_refreshsubscriptions SP, make sure that the publisher properties "allow_anonymous" and "immediate_sync" are set to "False", if these 2 options are set to "True" then this SP will mark all the articles for generating snapshot instead of marking only the newly added articles.

To Check the publication properties, use this query.


exec sp_helppublication 'PublicationName'
GO
If the values of the output columns "allow_anonymous" and "immediate_sync" are 0 then they are set to "False" if their values are 1 then they are set to "True"

To Change the publication properties for "allow_anonymous" and "immediate_sync", use this query


EXEC sp_changepublication

@publication = 'PublicationName',

@property = N'allow_anonymous',

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'PublicationName',

@property = N'immediate_sync',

@value = 'false'

GO
exec sp_refreshsubscriptions 'InterSiteTransfers'
GO



Step 4 : Start snapshot Agent in replication to apply changes in snapshot.
–> Replication Monitor.
–> Expand Publisher and select Subscription.
–> Go to Agent Tab.
–> Right click on Snapshot and click on Start.