August 8, 2018 at 8:18 am
Hello,
We are supporting one of the legacy system (SQL 2008 R2). Transaction replication was set up by vendor long time back for couple of very big articles. By mistake, someone deleted subscription for that publication. Publication still exist, i just need to add subscription and sync data with publication. I can just add the subscription but it will take longer to sync data. What would be the best possible way to add subscription and keep data in sync with publication? Thanks for help in advance.
Publication contains 2 articles.
Both table contains rows (Publication): 1052052161
Table rows in subscription:1051240671
August 8, 2018 at 1:57 pm
EasyBoy - Wednesday, August 8, 2018 8:18 AMHello,
We are supporting one of the legacy system (SQL 2008 R2). Transaction replication was set up by vendor long time back for couple of very big articles. By mistake, someone deleted subscription for that publication. Publication still exist, i just need to add subscription and sync data with publication. I can just add the subscription but it will take longer to sync data. What would be the best possible way to add subscription and keep data in sync with publication? Thanks for help in advance.
Publication contains 2 articles.
Both table contains rows (Publication): 1052052161
Table rows in subscription:1051240671
If you don't want to initialize with a snapshot, you can initialize from a backup which may or may not make sense depending on the database and could require some other manual changes depending on your replication. You can use other methods to initialize - move the data with bcp, SSIS or other processes along those lines. You would need to take into account how to keep the data in sync during that process. Which ever method you chose, the data will still have to move from the publisher to the subscriber so the alternatives may not necessarily be any better than using the snapshot.
You can find additional details in the following article:
Initialize a Transactional Subscription Without a Snapshot
Sue
August 8, 2018 at 5:10 pm
This was removed by the editor as SPAM
August 9, 2018 at 12:12 pm
Sue_H - Wednesday, August 8, 2018 1:57 PMEasyBoy - Wednesday, August 8, 2018 8:18 AMHello,
We are supporting one of the legacy system (SQL 2008 R2). Transaction replication was set up by vendor long time back for couple of very big articles. By mistake, someone deleted subscription for that publication. Publication still exist, i just need to add subscription and sync data with publication. I can just add the subscription but it will take longer to sync data. What would be the best possible way to add subscription and keep data in sync with publication? Thanks for help in advance.
Publication contains 2 articles.
Both table contains rows (Publication): 1052052161
Table rows in subscription:1051240671
If you don't want to initialize with a snapshot, you can initialize from a backup which may or may not make sense depending on the database and could require some other manual changes depending on your replication. You can use other methods to initialize - move the data with bcp, SSIS or other processes along those lines. You would need to take into account how to keep the data in sync during that process. Which ever method you chose, the data will still have to move from the publisher to the subscriber so the alternatives may not necessarily be any better than using the snapshot.
You can find additional details in the following article:
Initialize a Transactional Subscription Without a SnapshotSue
Without backup, is there any other option?
Maybe i can sync both tables in sync and recreate the subscription without initializing?
Using @sync_type ='replication support only'
August 10, 2018 at 12:16 pm
EasyBoy - Thursday, August 9, 2018 12:12 PMSue_H - Wednesday, August 8, 2018 1:57 PMEasyBoy - Wednesday, August 8, 2018 8:18 AMHello,
We are supporting one of the legacy system (SQL 2008 R2). Transaction replication was set up by vendor long time back for couple of very big articles. By mistake, someone deleted subscription for that publication. Publication still exist, i just need to add subscription and sync data with publication. I can just add the subscription but it will take longer to sync data. What would be the best possible way to add subscription and keep data in sync with publication? Thanks for help in advance.
Publication contains 2 articles.
Both table contains rows (Publication): 1052052161
Table rows in subscription:1051240671
If you don't want to initialize with a snapshot, you can initialize from a backup which may or may not make sense depending on the database and could require some other manual changes depending on your replication. You can use other methods to initialize - move the data with bcp, SSIS or other processes along those lines. You would need to take into account how to keep the data in sync during that process. Which ever method you chose, the data will still have to move from the publisher to the subscriber so the alternatives may not necessarily be any better than using the snapshot.
You can find additional details in the following article:
Initialize a Transactional Subscription Without a SnapshotSue
Without backup, is there any other option?
Maybe i can sync both tables in sync and recreate the subscription without initializing?
Using @sync_type ='replication support only'
Just the alternatives as I explained and what is in the link I provided. Replication itself doesn't have some setting to say "synchronize everything I manually copied to the subscriber". You would need to manage that synchronization - typically by stopping activity on the publisher, copy over the schema and data you need to the subscriber to manually initialize.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply