Need to set up Pull subscription to Publications already involved in Push subscriptions


  • I have a SQL 2005 instance that is a Publisher in a Push subscription scenario.  There are currently two subscribers.  I want to develop a pull subscription to a SQL 2008R2 instance on a different box.  I want to run the pull subscription on a schedule from 8am to 2359.  I then have a thrid party etl process that is going to read the data from the 2008R2 instance some time after midnight when there is no contention due to data changing via replication.  When I run sp_helppublication on my SQL 2005 Publisher, the "allow_pull'' option is set to off.  Can I have both allow_pull and allow_push enabled?  Is this really as simple as change the allow pull option to yes?  If so, how would I do that?

    Lee Markum

    Friday, July 12, 2013 3:45 PM


All replies

  • You do not need to set "allow_pull" yourself.  Just create the pull subscription, it will do it for you.

    Brandon Williams (blog | linkedin)

    Friday, July 12, 2013 3:48 PM
  • Thanks Brandon.  However, I can't set up the pull subscription using the wizard because the radio button for creating a Pull Subscription is not available.  I am unfamiliar with the myriad of stored procs involved in setting up replication.

    Lee Markum

    Friday, July 12, 2013 3:51 PM
  • Hi Lee,

    Sorry I mispoke.  You are correct, using the New Subscription Wizard will set "allow_pull" for you.

    You should be able to create the Pull Subscription from the Subscriber using the New Subscription Wizard.

    To setup the pull subscription using replication stored procs you will need to set it "allow_pull" yourself.  You can follow the steps in How to: Create a Pull Subscription (Replication Transact-SQL Programming).

    Brandon Williams (blog | linkedin)

    Friday, July 12, 2013 3:54 PM
  • Brandon, I appreciate the link to the T-SQL for replication. 

    I actually can't use the SSMS wizard to set up the pull subscription.  I mentioned earlier that when I run the wizard the option to create a pull subscription is greyed out for some reason. 

    Lee Markum

    Monday, July 15, 2013 3:07 PM