Skip to Content
Nov 05, 2018 at 09:39 PM

Exclude table from MSA (db subscription) replication?


We're replacing old type repdef/pub/article/subscription replication with MSA replication (database subscription replication).

With the old pubsub replication, if we didn't create a repdef/article, it would be excluded from subscription replication (good for work tables that need to be on the primary only)

But with MSA/database subscription replication, all the tables included with warm standby replication also get included with MSA replication (I think...)

So, if I have a database setup for warm standby replication with settings:

> sp_reptostandby testdb1;
The replication status for database 'testdb1' is 'ALL'.
The replication mode for database 'testdb1' is 'off'.

> sp_config_rep_agent testdb1,"send warm standby xacts";
 Parameter_Name          Default_Value Config_Value Run_Value
 ----------------------- ------------- ------------ ---------
 send warm standby xacts false         true         true    

And where I've added a database subscription (aka MSA replication) using:

create database replication definition my_db_rep_def ...
create subscription <db_sub_name> for database replication my_db_rep_def

Is there a way to have a specific table in the primary NOT replicate for the database subscription but still replicate for the warm standby?

My quick test using

sp_setreptable mytable,'false'

Didn't stop replication for the database subscription.

Setting sp_setreptable mytable,'never' stopped warm standby replication too, which I don't want.

Thanks in advance and apologizes if I'm missing some thing obvious here.