Exclude table from MSA (db subscription) replication?

[#1]

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.
Ben

[bonusbrevis · Accepted solution]

Ben,

I think what you are looking for is this, it's set in the DB rep def:

create database replication definition mydef

with primary at primsrv.primdb

replicate DDL

not replicate tables in (dbo.dontwantthistable1,dbo.dontwantthistable2)

not replicate functions in (*.rs_update_lastcommit)

replicate transactions

not replicate system procedures in (*.sp_adduser,*.sp_config_rep_agent,*.sp_procxmode)

Regards,

[sladebe · Accepted solution]

Yes, that looks like what I want.

Can I just do:

createdatabase replication definition mydef
with primaryat primsrv.primdb
not replicate DDL  -- we explicitly don't want this for our system
not replicate tablesin(dbo.dontwantthistable1,dbo.dontwantthistable2)

I never used "not replicate functions" and "not replicate system procedures" in create database replication definition commands before. I assumed they were off by default. (we don't have any explicit function or procedure replication setup)

[bonusbrevis]

Ben

You don't need the clauses for the functions or system procedures. We have those because we do bi-directional replication using MSA and need to avoid transactions doing ping-pong between servers. We do the REPLICATE DDL only on the repdef of one side for the same reason, the other side has NOT REPLICATE DDL and we limit DBA changes to one side so they replicate one-way only.

Regards

[sladebe]

Ok, got it. I'm just doing single direction MSA replication.

Thanks.

[former_member89972]

With RS 15.7.1 SP306, you also have option to list tables from files for a database replication definition.

[[not] replicate tables in files (file 1, file 2...)]

Specifies whether to send tables specified in the <file> to the replicate database.

So just create the list of tables from sysobjects in the source database, customize it and use it.

You can also use wild cards for table names & owner if the list becomes too big.

More details under "create database replication definition ...." command, in the RS Reference Manual.

HTH

Avinash