How to fix SQL 2008 R2 Replication objects not being created on Subscriber

How to fix SQL 2008 R2 Replication objects not being created on Subscriber

Chris Skorlinski
Microsoft SQL Server Escalation Services

Just a heads up if you are still running any version of SQL Server prior to SQL Server 2012 SP1 for Transactional Replication and the following hold true:

1) Use ‘initialize with backup’

To initialize a transactional subscriber from a backup
https://msdn.microsoft.com/en-us/library/ms147834(v=sql.105).aspx

2) Publishing non-tables objects such as views and stored procedures.

exec sp_addarticle …@type = N’view schema only’, or @type = N’proc schema only’, or @type = N’func schema only’

The Replication tracking tables and Replication created stored procedures will not get created on a Transactional Replication subscriber.  The solution is to create 2 publications, 1 for tables-only using ‘initialize with backup’ one for non-tables using “Replication Support Only”.

Remember when using ‘initialize with backup’ to always use sp_addpublication @allow_initialize_from_backup = N’true’ and  @immediate_sync = N’true’ to avoid missing transactions while subscribers are being brought online.

exec sp_addpublication
    @publication = N’Publication_ProcsOnly’,
    @immediate_sync = N’true’,
    @allow_initialize_from_backup = N’true’,…

exec sp_addpublication
    @publication = N’Publication_TablesOnly’,
    @immediate_sync = N’true’,
    @allow_initialize_from_backup = N’true’,…

–Full Database backup of Published Database

–Restore backup from Published Database to Subscriber(s)

exec sp_addsubscription
    @publication = N’Publication_ProcsOnly’,
    @sync_type = N’Replication Support Only’…

exec sp_addsubscription
    @publication = N’Publication_TablesOnly’,         
    @sync_type = N’initialize with backup’,
    @backupdevicetype=’Disk’,
    @backupdevicename=’Z:\SQLBACKUP\FullDB.bak’…

You’ll see 2 Distribution Agents, one for each Subscription.  You can schedule the “ProcsOnly” publication to run less frequently, perhaps once a day while the “TableOnly” can run continuously.  When changing the “ProcsOnly” schedule remember to also remove the “-Continuous” parameter from the SQL Agent Distribution Job.