Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Transactional replication - Pausing

Author  Topic 

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2015-01-28 : 14:03:59
Hi All,

I have a transactional replication from database A to database B. It only replicates 40 tables out of 200.

Database B serves as a backend to an application.

1. During an upgrade I stop the replication (Replication monitor - Subscription watchlist - stop synchronising).

Replication did not run for a while. During that time there was an upgrade (Schema changes) and data load on database A.

2. Once that was completed, I ran the same schema changes on database B and I enabled the replication (Replication monitor - Subscription watchlist - stop synchronising)

Replication was back on and if i make any data change on A i can see it on database B.

But i could not see the data that was loaded on A during the time replication was not running.

I realized that transactional replication will replicate data while it is running but will not sync the data if there are data changes on the publisher.

What I did was - I deleted the replication, recreated with "create a snapshot immediately". It looks like the data have been populated to subscriber (B) now.

Is it the only way to achieve what I am trying to do?


Regards,
D

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2015-01-28 : 14:05:21
I tried this already.
This transactional replication created 3 agents job. I disabled them but the replication kept running. Any update to the publisher was propagated to subscriber.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-28 : 14:09:40
1. I only ever disable the log reader agent if I want to pause the data flow. By doing this it still moves the data from the publisher to the distributor but not from the distributor to the subscriber.
2. Are the articles setup to replicate schema changes? We let replication handle the schema changes on the subscriber, so we don't even stop replication/log reader agent for a scenario like you described.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2015-01-28 : 14:34:46
Thanks Tara. I don't see a logreader agent on the SQL server instance under jobs or something named closer.
Is it generic to all replications on an instance?
When I created the transactional replication, it created 3 jobs (publisher and subscriber are on the same instance). Should it be one of them? I tried disabling all 3. but it did not stop the data flow.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-28 : 14:37:30
The log reader agent is on the distributor.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2015-01-28 : 14:42:08
Interesting this. I tried this as a test.
I did the exact same thing I did before.

1. I stop the replication (Replication monitor - Subscription watchlist - stop synchronizing).
2. changed a data on a table and added a column. Made sure the subscriber did not get it.
3. I enabled the replication (Replication monitor - Subscription watchlist - Start synchronizing)

This time the changes were propagated.

I have no idea why it did not work before.

One of the developer told me he heard somewhere that if the replication was down for more than 72 hours data will not propogate. May be that was the reason.

Is that true?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-28 : 14:45:08
Yes it's true if the retention is 72 hours. 72 hours is the default. Check the retention in the distributor properties on the distributor.

The value is configurable. At my last job, we had it set to 24 hours due to volume of data being replicated which caused performance issues on the distributor. It was an extremely busy replication topology.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2015-01-28 : 15:13:09
Great. Thanks a lot Tara!!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-28 : 15:13:43
You're welcome, glad to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -