Issue 1: Subscriber
end article rows are more than Publisher, how to fix this issue.
Issue 2: There are
two tables (Order_table_1 & Order_table_2) And user want and in both table
column name “Status” should have same value. Publisher side it is showing same
value for both tables but in subscriber side it is having the issue. Where the Order_table_2 have correct values for status column.
Issue 3: How to
remove existing article (Order_table) from transactional replication and ADD Article
(Coustomer_table) again but should not run snapshot agent for all the existing
articles.
Issue 4: How to Add Article
(Order_table_1) again but should not run snapshot agent for all the existing
articles.
Solution:
1.
Data validation:
Run the query on Publisher end and subscriber end to find out the data
mis-match between two table
--Run below query
total count on publisher and both subscriber.
SELECT S.STATUS ,P.STATUS, * FROM Order_table_1
S
INNER JOIN Order_table_2
P
ON S.ID=P.ID
WHERE S.STATUS <> P.STATUS
--Run below query
total count on on publisher and both subscriber.
select count(*) from [dbo].Order_table_1
2.
Check the immediate_sync , allow_anonymous values.
By default these value set as 1 and we need to make them 0
--Ran this on publisher server
sp_helppublication
-- Or ran this on Distributor server
select immediate_sync,allow_anonymous,*
from distribution.dbo.MSpublications
3.
Set immediate_sync , allow_anonymous as 0 (Zero)
--Run on your publisher database to
change the value as 0 for immediate_sync,allow_anonymous
EXEC sp_changepublication
@publication = 'IND-Replication',
@property = 'allow_anonymous' ,
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'IND-Replication',
@property = 'immediate_sync' ,
@value = 'false'
GO
4.
To remove existing Article
from replication
A. Go Publisher server à Open Replication à Go to replication property à Click on Articles à Uncheck the Article which
you want to remove à Click on OK.
B. Now connect to Subscriber
server à Select the replication
database à Drop the article which you
want to remove from replication.
5. To add Article in existing replication
--Run below query to add article on replication
database< Display_Expand_database> at publisher end, Make sure change the
@publication value, @article and @source_ object
USE Display_Expand_database
EXEC sp_addarticle
@publication = IND-Replication',
@article = Order_table_1,
@source_object = Order_table_1
6. To Add subscription on subscriber end in existing replication
--Run below query at publisher end to add subscription
on replication, Make sure change the @publication value, @destination_db.
Note- We have two subscriber so below
script is for both subscriber.
EXEC sp_addsubscription
@publication = IND-Replication',
@subscriber = 'IND_SQLDB01',
@destination_db = 'Display_Expand_database'
EXEC sp_addsubscription
@publication = 'IND-Replication',
@subscriber = 'IND_SQLDB02',
@destination_db = 'Display_Expand_database'
7.
Now open the replication monitor à expend “My Publisher” Click on Replication
Name à Click on “Agent” Tab à See the job name as “Snapshot
agent” à Right click on “Snapshot agent” and click on “Start Agent” à See the Last action (It will show the progress of snapshot for only Newly
added article.)
8. Data validation: Now match the total row count and check
the mis-match values. It will be same now on publisher and both subscriber end.
--Run below query total count
on on publisher and both subscriber.
select count(*) from [dbo].Order_table_1
--Run below query
total count on publisher and both subscriber.
SELECT S.STATUS ,P.STATUS, * FROM Order_table_1
S
INNER JOIN Order_table_2
P
ON S.ID=P.ID
WHERE S.STATUS <> P.STATUS
