Replication of check constraints

I have tables that are replicated using transactional and merge replication.As a result I am unable to use automatic identity management as transactional replication doesn’t seem to understand it.

Therefore I have implemented a version of the automatic mechanisms that seems to work in a hybrid environment.It is based on a central table that holds the maximum identity for each table that has been issued to date.Valid identity ranges are issued to each publisher and subscriber as needed in a similar way to the automatic mechanisms and tables are reseeded as needed.

I want to enforce the ranges in a similar way to the automatic mechanism using a check constraint similar to this:

alter table [dbo].[test1] with NOCHECK add CONSTRAINT repl_identity_range_48DF13ED_D503_4F5C_AED9_4E504D03E752 check NOT FOR REPLICATION (([id] > 10001 and [id] <= 20001) or ([id] > 50001 and [id] <= 70001))

This works OK on a client subscriber, but if the change is made on the publisher, then the alter statement itself is replicated out to all clients – which is not what is wanted.I have traced the automatic mechanisms using profiler and they issue an alter statement as above – following dropping of the constraint – but the check constraint isn’t replicated. I can't see how this is achieved.

How do I stop the check constraint being replicated?

The article property schema option can be set to stop replicating check constraints, but this seems to have no effect.If the publication property replicate_ddl is set to 0 then I do see the behaviour that I want.However, I do need to be able to replicate most schema changes due to upgrades etc – so this doesn’t look like a viable option – except possibly for the transactional publication.

Any help would be much appreciated

Thanks

aero1

I think I have found a way forward. I had been considering the publication setup as pretty static, but on subsequent thoughts I realised that there is no reason why I can't change the replicate_ddl property as and when I need to

I have tested out the following and it seems to work (Other unrelated schema changes get synched correctly)

begin tran

exec sp_changemergepublication to set replicate_ddl to 0

make the check constraint change

exec sp_changemergepublication to set replicate_ddl back to 1

commit

Is there any problem with this approach?

|||This is the supported way of not wanting to replicate DDL-type statements (enable/disable publication property replicate_ddl). Please let us know if you run into problems.|||

Thanks for the info

aero1