| 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. |
 |
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-28 : 14:37:30
|
| The log reader agent is on the distributor.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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? |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
dolphin123
Yak Posting Veteran
84 Posts |
Posted - 2015-01-28 : 15:13:09
|
| Great. Thanks a lot Tara!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-28 : 15:13:43
|
| You're welcome, glad to help.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Transactional replication - Pausing