Transactional Replication to Multiple Subscribers

  • Hi all:

    I'm attempting to set up replication from a SQL 2017 Instance to multiple subscribers.  One is another SQL 2017 instance and the other is a SQL 2014 instance.  The Distributor and Publisher are on the same instance and I'm setting up a push subscription to the two instances.  After I set up the subscription, when I look at the Replication Monitor I see  two sessions on the "Distributor to Subscriber History."  One of them shows numerous actions where the records are bulk copied, etc. as the snapshot is propagated.  The other, however, shows a message saying "The concurrent snapshot for publication "My_Publication" is not available because it has not been fully generated or the Log Reader Agent is not running to Activate it.  If generation of the concurrent snapshot was interrupted, the Snapshot Agent for " (the message abruptly ends). 

    The log reader is definitely running, there are no errors in the errorlog, and test transactions are replicating immediately to both subscribers (so it appears to be working).  So why the message?

    This is a test run and I've got more adjustments to do before going live with this.  I'm just trying to figure out what I did wrong when setting this up to see this kind of message.  Should I not set up the two targets at the same time when I go through the wizard or what?  I feel like if this was the problem, then I wouldn't have the option to set up multiple subscribers.  SQL 2014 is within 2 versions of SQL 2017, so I know replication should be supported in this configuration.

    I would appreciate any thoughts on what may have happened or what I should do differently on my next test run.



    Del Lee

  • I think you're good:-)
    Basically, "The concurrent snapshot for publication "My_Publication" is not available because it has not been fully generated ..." is very common. When setting up a replication, there must be some gap between you add subscriber(s) and generate the snapshot. So, there might some chance that you see the message above. As you confirmed subscribers works well, without other error/warning, I think it should be OK.

    GASQL.com - Focus on Database and Cloud

  • Del Lee - Saturday, February 10, 2018 2:28 PM

    Hi all:

    I'm attempting to set up replication from a SQL 2017 Instance to multiple subscribers.  One is another SQL 2017 instance and the other is a SQL 2014 instance.  The Distributor and Publisher are on the same instance and I'm setting up a push subscription to the two instances.  After I set up the subscription, when I look at the Replication Monitor I see  two sessions on the "Distributor to Subscriber History."  One of them shows numerous actions where the records are bulk copied, etc. as the snapshot is propagated.  The other, however, shows a message saying "The concurrent snapshot for publication "My_Publication" is not available because it has not been fully generated or the Log Reader Agent is not running to Activate it.  If generation of the concurrent snapshot was interrupted, the Snapshot Agent for " (the message abruptly ends). 

    The log reader is definitely running, there are no errors in the errorlog, and test transactions are replicating immediately to both subscribers (so it appears to be working).  So why the message?

    This is a test run and I've got more adjustments to do before going live with this.  I'm just trying to figure out what I did wrong when setting this up to see this kind of message.  Should I not set up the two targets at the same time when I go through the wizard or what?  I feel like if this was the problem, then I wouldn't have the option to set up multiple subscribers.  SQL 2014 is within 2 versions of SQL 2017, so I know replication should be supported in this configuration.

    I would appreciate any thoughts on what may have happened or what I should do differently on my next test run.

    Check the log reader history in the distribution database and also check the distributor - the tables are MSlogreader_history and MSdistribution_history
    Those can give you an idea if the snapshot was delivered and if there are transactions still to be delivered. The distribution agent would be applying snapshots and replicated records.
    If  there is a significant number of undelivered transactions that would be on the undistributed commands in replication monitor. You may need the distributor to run and may want to check the schedule. If the log reader history has a lot of messages like log records have been scanned in pass nn, n of which were marked for replication", you'd want to check the number of VLFs for the published database using dbcc loginfo.

    Sue

  • What I ended up doing here was scripting out everything and running things in a particular order.  I created the publisher, then started the snapshot agent and waited for it to finish before creating the subscribers and initiating the synchronization.  I was able to do this multiple times without error, whereas I could consistently see the error using the wizards.



    Del Lee

  • Sue_H - Monday, February 12, 2018 12:28 PM

    Del Lee - Saturday, February 10, 2018 2:28 PM

    Hi all:

    I'm attempting to set up replication from a SQL 2017 Instance to multiple subscribers.  One is another SQL 2017 instance and the other is a SQL 2014 instance.  The Distributor and Publisher are on the same instance and I'm setting up a push subscription to the two instances.  After I set up the subscription, when I look at the Replication Monitor I see  two sessions on the "Distributor to Subscriber History."  One of them shows numerous actions where the records are bulk copied, etc. as the snapshot is propagated.  The other, however, shows a message saying "The concurrent snapshot for publication "My_Publication" is not available because it has not been fully generated or the Log Reader Agent is not running to Activate it.  If generation of the concurrent snapshot was interrupted, the Snapshot Agent for " (the message abruptly ends). 

    The log reader is definitely running, there are no errors in the errorlog, and test transactions are replicating immediately to both subscribers (so it appears to be working).  So why the message?

    This is a test run and I've got more adjustments to do before going live with this.  I'm just trying to figure out what I did wrong when setting this up to see this kind of message.  Should I not set up the two targets at the same time when I go through the wizard or what?  I feel like if this was the problem, then I wouldn't have the option to set up multiple subscribers.  SQL 2014 is within 2 versions of SQL 2017, so I know replication should be supported in this configuration.

    I would appreciate any thoughts on what may have happened or what I should do differently on my next test run.

    Check the log reader history in the distribution database and also check the distributor - the tables are MSlogreader_history and MSdistribution_history
    Those can give you an idea if the snapshot was delivered and if there are transactions still to be delivered. The distribution agent would be applying snapshots and replicated records.
    If  there is a significant number of undelivered transactions that would be on the undistributed commands in replication monitor. You may need the distributor to run and may want to check the schedule. If the log reader history has a lot of messages like log records have been scanned in pass nn, n of which were marked for replication", you'd want to check the number of VLFs for the published database using dbcc loginfo.

    Sue

    Thanks, Sue. It's very informative. Did you mean, most of log-reader performance issues were caused by bad log settings(such as size, auto-growth-size)? If you run into this issue (log-reader performance issue which is caused by too many small VLFs), what will you do for a fast fix? You know, changing log setting doesn't 'merge' any existing VLFs.

    GASQL.com - Focus on Database and Cloud

  • Alexander Zhang - Saturday, April 21, 2018 10:34 PM

    Sue_H - Monday, February 12, 2018 12:28 PM

    Del Lee - Saturday, February 10, 2018 2:28 PM

    Hi all:

    I'm attempting to set up replication from a SQL 2017 Instance to multiple subscribers.  One is another SQL 2017 instance and the other is a SQL 2014 instance.  The Distributor and Publisher are on the same instance and I'm setting up a push subscription to the two instances.  After I set up the subscription, when I look at the Replication Monitor I see  two sessions on the "Distributor to Subscriber History."  One of them shows numerous actions where the records are bulk copied, etc. as the snapshot is propagated.  The other, however, shows a message saying "The concurrent snapshot for publication "My_Publication" is not available because it has not been fully generated or the Log Reader Agent is not running to Activate it.  If generation of the concurrent snapshot was interrupted, the Snapshot Agent for " (the message abruptly ends). 

    The log reader is definitely running, there are no errors in the errorlog, and test transactions are replicating immediately to both subscribers (so it appears to be working).  So why the message?

    This is a test run and I've got more adjustments to do before going live with this.  I'm just trying to figure out what I did wrong when setting this up to see this kind of message.  Should I not set up the two targets at the same time when I go through the wizard or what?  I feel like if this was the problem, then I wouldn't have the option to set up multiple subscribers.  SQL 2014 is within 2 versions of SQL 2017, so I know replication should be supported in this configuration.

    I would appreciate any thoughts on what may have happened or what I should do differently on my next test run.

    Check the log reader history in the distribution database and also check the distributor - the tables are MSlogreader_history and MSdistribution_history
    Those can give you an idea if the snapshot was delivered and if there are transactions still to be delivered. The distribution agent would be applying snapshots and replicated records.
    If  there is a significant number of undelivered transactions that would be on the undistributed commands in replication monitor. You may need the distributor to run and may want to check the schedule. If the log reader history has a lot of messages like log records have been scanned in pass nn, n of which were marked for replication", you'd want to check the number of VLFs for the published database using dbcc loginfo.

    Sue

    Thanks, Sue. It's very informative. Did you mean, most of log-reader performance issues were caused by bad log settings(such as size, auto-growth-size)? If you run into this issue (log-reader performance issue which is caused by too many small VLFs), what will you do for a fast fix? You know, changing log setting doesn't 'merge' any existing VLFs.

    With any databases having a ton of small VLFs, the recovery as well as the restore times are going to be very slow. A lot of small VLFs with replication can backup replication to where it is hours behind. Not sure what you mean by merging VLFs - not aware of any such thing. You would do the same with replication or without - you shrink the log size down as much as possible, reset the size and increase the growth increment. With an active replication scenario, it can be more challenging and you may need to do something like adding another log file temporarily to push the active portion of the log to the added file to allowing shrinking the original.

    Sue

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply