Transactional replicatiion - pull subscription question

[kodaksmile]

I have a large db (over 300 gig) that I need to set up transactional replication on. I wanted to use a backup for initialization and followed the steps of creating the publication, marking it to allow initialization from backup, taking a backup, restoring the backup then I get a little stuck. I need to create a pull subscription - I tried running sp_addsubscription (which worked but, created a push subscription) and sp_addpullsubscription. The problem came in that since a push subscription was already created I could not successfully run sp_addpullsubscription and sp_addpullsubscription does not have the parameters to allow to initialize from a backup.

Any suggestions on what I am missing?

Thanks,

 

[kodaksmile]

I think I figured it out - need to add a parameter to the sp_addsubscription called subscription_type='pull' which is run on the publisher then run the sp_addpullsubscription and sp_addpullsubscription_agent on the subscriber and sync it.

 

[kodaksmile]

Well I am closer - the problem is when I try to sync the subscriber I get a message in the job history that shows the publication does not exist (but it does) when I look in replication monitor it shows an unitialized subscriber....

Any suggestions on what I missed?

 

[kodaksmile]

Here are the steps I followed so far:

1. configure distribution
2. create a pulbication and set it to allow initialization from backup
3. perform full backup
4. restore backup at subscriber
5. run the following command on the publisher:
sp_addsubscription
@publication='Test_Publication',
@subscriber='testserver',
@destination_db='replication_test',
@sync_type='initialize with backup',
@backupdevicetype='disk',
@backupdevicename='i:\test4replication.bak',
@subscription_type='pull
6. run the following command on the subscriber:
sp_addpullsubscription
@publisher='testserver',
@publication='Test_Publication',
@subscription_type='pull'
7 run the following command on the subscriber:
sp_addpullsubscription_agent
@pulisher='testserver',
@publication='Test_Publication',
@distributor='testserver'

All of these commands run however when I look in replication monitor it shows the subscription as uninitialized and the distribution job shows failed - it starts up, connects to the subscriber, connects to the distributor and then gives an agent message code 20026. The publication 'Test_Publication' does not exist.

What did I miss in my steps?

 

[kodaksmile]

I just heard that you cannot use initialize from backup on a pull subscription - only on a push subscription which unfortunately I cannot do.

 

[kodaksmile]

After some additional validation you can use the initialize from backup for a pull subscription. The issue above requires an additional parameter called publisher_db that needs to be included in the last 2 sql scripts. I did get this to work when it was all on one server however when I try it on 2 servers I received the same error message regarding the publication not existing:

I ran into an issue when setting this up on 2 servers - Here are the steps:

1. Set up distribution on subscriber server
2. Create a publication and set it to allow intialization from backup
3. Perform full backup
4. copy backup over to subscriber and restore
5. Run the following command on the publisher:
sp_addsubscription
@PUBLICATION='Test_Publication',
@SUBSCRIBER='XXXXXXSQL012',
@destination_db='Repl_Subscriber',
@SYNC_TYPE='initialize with backup',
@BACKUPDEVICETYPE='disk',
@subscription_type='pull',
@backupdevicename='\\XXXXXXSQL012\d$\Backup\Repl_Publisher4test.bak'

6. Run the following command on the subscriber:
sp_addpullsubscription
@PUBLISHER='XXXXXXSQL010',
@PUBLICATION='Test_Publication',
@subscription_type='pull',
@publisher_db='Repl_Publisher'

7. Run the following command on the subscriber:
sp_addpullsubscription_agent
@PUBLISHER='XXXXXXSQL010',
@PUBLICATION='Test_Publication',
@DISTRIBUTOR='XXXXXXSQL012',
@PUBLISHER_DB='Repl_Publisher',
@frequency_type=64

All of these commands run however when I look in replication monitor it shows the subscription as uninitialized and the distribution job shows failed - it starts up, connects to the subscriber, connects to the distributor and then gives an agent message code 20026. The publication 'Test_Publication' does not exist which is the same issue I had initially before the recommendation to add the publisher_db variable so I am not sure what the heck is missing here. Here is the details of the command in the distribution job:

-Publisher XXXXXXSQL010 -PublisherDB [Repl_Publisher] -Publication [Test_Publication] -Distributor [XXXXXXSQL012] -SubscriptionType 1 -Subscriber [XXXXXXSQL012] -SubscriberSecurityMode 1 -SubscriberDB [Repl_Subscriber] -Continuous

Also I noticed that 2 of the 3 sql jobs (snapshot and logreader) are created on the publisher whereas when you do this using the snapshot they are created on the subsciber. I am concerned with where the overhead would be placed so I am wondering if there is a way to have those created on the subscriber as well.

If I can get this to work I'll post the answer.