June 5, 2009 at 2:58 pm
Hello
I am new to this SQL Server replication stuff. Would appreciate if some one out there can clarify these.
For tables involved in Replication, how do i carry out DDL's ALTER TABLE add column or modify column size?
1. Should i have to 'SUSPEND' replication and carry out DDL?
2. Does it involve regeneration of any associated procedures / underlying trigger?
3. Can DMLs be carried out when the ALTER table is taking place (depends on 1 above)
4. Are the changes at the Source pushed to the Destination by SQL Server automagically Or the DBA has
to explicitly carry them out?
TIA
June 8, 2009 at 9:59 am
It all depends on the version you are using and the replication type.
In SQL 2005 and above for transaction replication.
1. Should i have to 'SUSPEND' replication and carry out DDL?
NO, You can use alter statements
2. Does it involve regeneration of any associated procedures / underlying trigger?
Replication based Stored procs? NO
Normal Stored procs? If you have added those stored procs for replication, then it will get propogated.
3. Can DMLs be carried out when the ALTER table is taking place (depends on 1 above)
I think the answer is already provided
4. Are the changes at the Source pushed to the Destination by SQL Server automagically Or the DBA has to explicitly carry them out?
I did not understand what kind of changes you are talking about.
-Roy
June 8, 2009 at 4:58 pm
For question # 4 , i meant the DDL changes. Should the DBA carry out the DDL in both the databases involved in replication?
June 9, 2009 at 7:54 am
DDL issued against the published articles will propogate to the subscribers when they synchronize.
You do have the ability to disable DDL replication if you need to:
-- Turn off DDL replication for the publication.
EXEC sp_changemergepublication
@publication = @publication,
@property = N'replicate_ddl',
@value = 0,
@force_invalidate_snapshot = 0,
@force_reinit_subscription = 0;
keith
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply