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

Replication internals...

Hi,
I need to answer a question for management and I have little replication
knowledge. I'd appreciate any input here.
What happens to the dbs on either side i.e. dist and subscriber, when in the
middle of the operation, one of the systems crashes? vs. what happens when
you click the stop sync button? How do you recover both in the event of the
former assuming the stop synch does not separate, but controls, parent from
children records.
Thanks much!
replication stored the last recorded transaction in the meta data tables on
the subscriber and the publisher\distributor.
When the system comes back up the data will be replayed to the Subscriber
provided it has not being cleaned up on the publisher\distributor.
Transactions are applied on the subscriber within a transactional context.
If you are replicating data which is part of a pk-fk relationship these
commands will be replicated as past of a transaction - unless you are not
enforcing the relationship for replication.
So it is very much like the ACID properties you see when you apply the
transaction on the publisher.
If the distribution database is corrupted its all bets off, as it holds the
transactions to be applied on the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JimMac" <JimMac@.discussions.microsoft.com> wrote in message
news:5BF14F6D-AB6E-4CB6-AC8E-125F43FFE652@.microsoft.com...
> Hi,
> I need to answer a question for management and I have little replication
> knowledge. I'd appreciate any input here.
> What happens to the dbs on either side i.e. dist and subscriber, when in
the
> middle of the operation, one of the systems crashes? vs. what happens
when
> you click the stop sync button? How do you recover both in the event of
the
> former assuming the stop synch does not separate, but controls, parent
from
> children records.
> Thanks much!