replicate to a second subscriber without snapshot
in [Replication]
|
Prev: Replication Solution, what does it depend on? What does my Laundry List look like?
Next: Creating my own sp_MSins_dboTABLENAME
From: OceanDeep via SQLMonster.com on 14 Oct 2009 17:26 We are currently replicating a database (70G) from SQL 2008 std (server A) to SQL 2005 std (server B). We are using transactional and push method. The distributor db and publisher db are on the same server (server A). To create the second subscriber on a different SQL 2005 std computer(server C), I did the following steps: 1. stop the log reader on the publisher and the sync process on the current subscriber 2. Make some data changes on the publisher db on server A (for testing purpose). 3. do a backup of the subscriber in server B, and restore it to server C. 4. Create subscriber for server C using the replicatin wizard and generate the creation script. Then change the sync method to 'replication support only'. 5. start log reader and the sync process for both subscribers. When I test these steps, Any data changes made on the publsihed db on server A (step 2) during the log reader down time are not replicated to server B and C when step 5 is executed. Does this make sense or I did something wrong? Are there other requirements for this process to work? I could use snapshot but time is limited. od -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-replication/200910/1
From: Paul Ibison on 14 Oct 2009 17:53 Have a look at "initialize with backup" rather than "replication support only" is sp_addsubscription (http://msdn.microsoft.com/en-us/library/ms181702.aspx). HTH, Paul Ibison
From: OceanDeep via SQLMonster.com on 15 Oct 2009 00:42 Paul, thank for the reply. The ''initialize with backup' option won't work for us since we only published 1/3 of the tables. It seems if I really want to use the 'Replication support only' is to not let anyone make any changes to the published db while I am doing all the steps. I can turn the published db to offine. Do you see any problem with that? OD Paul Ibison wrote: >Have a look at "initialize with backup" rather than "replication support >only" is sp_addsubscription >(http://msdn.microsoft.com/en-us/library/ms181702.aspx). >HTH, >Paul Ibison -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-replication/200910/1
From: Paul Ibison on 15 Oct 2009 03:43 Your approach sounds good. Alternatively you could use the ''initialize with backup' option and chop out the redundant tables and objects at the subscriber side. HTH, Paul Ibison (www.replicationanswers.com)
From: OceanDeep via SQLMonster.com on 15 Oct 2009 09:00
Yes, I could. I would need to remove about 200 tables. Btw, I just want to clarify my understanding. When I started the testing process, my thinking was that if I stop the log reader and syn process, I thought the changes I made (step 2) would replicate to the current subscriber db and the new one after I finished all the steps. The fact is that not only the new subscriber didn't get the changes, but also the current one didn't either. I can make some sense of why the new one won't get it but why the current one didn't puzzle me. Can you clarify? OD Paul Ibison wrote: >Your approach sounds good. Alternatively you could use the ''initialize with >backup' option and chop out the redundant tables and objects at the >subscriber side. >HTH, >Paul Ibison (www.replicationanswers.com) -- Message posted via http://www.sqlmonster.com |