Sql-server – All pull subscriptions cannot connect to Distributor

replicationsql server

recently all my PULL subscriptions have this message in the synchronization status. :-

The process could not connect to Distributor 'DISTRIBUTIONDB'

This is only happening with the PULL subscriptions. I have attempted to reinitialize with a new snapshot, but the message remains.

when attempting to look at the history it suggests that the agent job doesn't exist. So I removed it and recreated the subscription and the job from a T-SQL script. monitor now suggests that the job doesn't exist but when run the T-sql sp_addpullsubscription code on the subscriber i get

Msg 21002, Level 11, State 1, Procedure sp_addpullsubscription_agent, Line 250
The Distribution Agent for this subscription already exists (YAZOO-MLT-Statics-KINKS-MLT-Replicated-94CD221C-4F8E-4732-8738-A6101E9D571B).

Best Answer

This is a permissions issue, the Distribution Agent process account does not have rights on the distributor.

Verify the Distribution Agent process account is a member of the PAL, has read permissions on the snapshot share, and is a member of the db_owner fixed database role in the subscription database.

Related Solutions

Sql-server – Transactional replication from SQL Server 2000 to 2008

Could you try moving the distributor over on to the SQL 2008 box. Replication got much better after SQL 2005. Looks like you are using anonymous subscriptions, do you not have VPN so that servers can see each other? I am not familiar with Internet based subscribers so probably not much help on its specifics.

Sql-server – Transactional Replication snapshot locking Publisher db from beginning

To get this to work I scheduled switching off the 'immediate sync' to very low traffic period

EXEC sp_changepublication  
@publication = 'statics',
@property = 'Immediate_sync',
@value = 'false'
GO

I then ran a snapshot which only took a fraction of the time at 6 mins.

I then had to drop the current subscription for that article only

    EXEC sp_dropsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR'

and rerun the snapshot. Then i could add the subscription back into the publication

    EXEC sp_addsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR',
    @destination_db = 'MLT-Replicated',
    @reserved='Internal'

but it would not allow this without the fairly obscure setting

@reserved ='Internal'

or I got an error message saying I needed to subscribe to all articles.... replication then automatically sync'd the new table. I'm a happy man as this has been causing me some long nights!

Best Answer

Related Solutions

Sql-server – Transactional replication from SQL Server 2000 to 2008

Sql-server – Transactional Replication snapshot locking Publisher db from beginning

Related Question