none
Subscription deleted

    Question

  • 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

    Wednesday, August 8, 2018 2:16 PM

Answers

  • Since there's already a good difference in row-counts, you'd have to reinitialize. As Hilary mentioned, you can speed the synchronization if you choose the "replication support only" parameter instead of the traditional snapshot.

    Roughly, your steps would include (for "replication support only" method):

    1) Stop whatever populates the two tables on the publisher so no new data comes in.

    2) Truncate the tables on the subscriber and use import/export or BCP etc to reload them from the publisher. Alternatively, you can only synchronize the difference but you'd have to build a customized script or use a third party tool etc to get the delta. 

    3) Once both publisher and subscriber are in sync, go ahead and create the subscription. I've discussed quick steps on this thread. It's important that you choose "replication support only" so that it's a join-only. 

    4) It'll start replicating from that point on. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, August 8, 2018 11:32 PM

All replies

  • You really need to recreate the subscription and reinitialize from a snapshot or backup. There will be some locking while you do this.

    You might be able to recreate the subscription and not select initialize (use @sync_type of 'replication support only' - of sp_addsubscription), but then there will be missing data in the subscriber. You will need to use the continue on data consistency error profile to stop the distribution agent from failing.

    Wednesday, August 8, 2018 2:22 PM
    Moderator
  • You have no choice, you need to reinitialize the subscription.  As Hilary said, you might be able to do that from a backup, but it needs to be reinitialized.

    Wednesday, August 8, 2018 3:18 PM
  • Since there's already a good difference in row-counts, you'd have to reinitialize. As Hilary mentioned, you can speed the synchronization if you choose the "replication support only" parameter instead of the traditional snapshot.

    Roughly, your steps would include (for "replication support only" method):

    1) Stop whatever populates the two tables on the publisher so no new data comes in.

    2) Truncate the tables on the subscriber and use import/export or BCP etc to reload them from the publisher. Alternatively, you can only synchronize the difference but you'd have to build a customized script or use a third party tool etc to get the delta. 

    3) Once both publisher and subscriber are in sync, go ahead and create the subscription. I've discussed quick steps on this thread. It's important that you choose "replication support only" so that it's a join-only. 

    4) It'll start replicating from that point on. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, August 8, 2018 11:32 PM
  • Thanks Mohsin and Hilary for your response.

    But, the problem is this database is 1 TB and it takes 2 hours to backup and 2 hours to restore. So, most probably, i will take a look at the option provided by Mohsin.





    • Edited by Chickoo79 Thursday, August 9, 2018 5:32 PM
    Thursday, August 9, 2018 11:52 AM
  • I suspect you will find that the ETL process and index creation will take over 4 hours. I would look at the backup and restore operation.
    Thursday, August 9, 2018 6:53 PM
    Moderator
  • I suspect you will find that the ETL process and index creation will take over 4 hours. I would look at the backup and restore operation.
    But the only problem with backup and restore is, we have many more tables in subscriber databases including indexes  compare to publisher, that we dont want to delete.
    Thursday, August 9, 2018 6:55 PM
  • I suspect you will find that the ETL process and index creation will take over 4 hours. I would look at the backup and restore operation.

    But the only problem with backup and restore is, we have many more tables in subscriber databases including indexes  compare to publisher, that we dont want to delete.
    Yeah. Was going to say the same. Backup and restore might be faster but you'd lose any other objects that you have on the subscriber (that do not exist on the publisher). Not to mention you'd have to deal with permissions/orphan users etc.

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, August 9, 2018 7:23 PM
  • Thanks Mohsin for your help.

    But can you please take a look at the script for step 3 after syncing tables and creating publication on publisher without initializing? Its little urgent. If you can reply as earliest would be great. Please let me know if i am missing anything or do i need to add/perform any steps afterward?

    use [Test_Chart]
    exec sp_addsubscription @publication = N'TestInfo', 
    @subscriber = N'TestDev2', 
    @destination_db = N'Test_Chart', 
    @subscription_type = N'Push', 
    @sync_type = N'replication support only'
    --@article = N'all', @update_mode = N'read only', @subscriber_type = 0
    
    exec sp_addpushsubscription_agent @publication = N'TestInfo', 
    @subscriber = N'TestDev2', 
    @subscriber_db = N'TEST_CHART', 
    @job_login = null, 
    @job_password = null, 
    @subscriber_security_mode = 0, 
    @subscriber_login = N'administrator',
    @subscriber_password = null, 
    @frequency_type = 64, 
    @frequency_interval = 1, 
    @frequency_relative_interval = 1, 
    @frequency_recurrence_factor = 0, 
    @frequency_subday = 4, 
    @frequency_subday_interval = 5, 
    @active_start_time_of_day = 0, 
    @active_end_time_of_day = 235959, 
    @active_start_date = 0, 
    @active_end_date = 0, 
    @dts_package_location = N'Distributor'
    GO


    • Edited by Chickoo79 Thursday, August 16, 2018 8:29 PM
    Thursday, August 16, 2018 8:27 PM
  • Looks good to me. After you have run the first part of the code (sp_addsubscription) under publisher DB, make sure that the MSIns, MSupd, MSdel stored procedures are created in the subscriber database. This is important. Once verified, go ahead with the second part of adding subscription agent. 

    If you have a similar setup in dev/test, see if you can try there before you do this is prod. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, August 17, 2018 1:19 PM