September 23, 2010 at 11:26 pm
which option will affect data and schema changes to the subscriber?
is mirroring support : schema changes to mirror server
is log shipping support : Schema changes to secondary server
is replication support : schema changes to subscriber( i think will support)
if i do any bulk operations ( BCP) then those changes will be applied to secondary server( Which option will support)
thanks guys,
🙂
October 1, 2010 at 6:59 am
SQL* (9/23/2010)
which option will affect data and schema changes to the subscriber?is mirroring support : schema changes to mirror server
is log shipping support : Schema changes to secondary server
is replication support : schema changes to subscriber( i think will support)
if i do any bulk operations ( BCP) then those changes will be applied to secondary server( Which option will support)
thanks guys,
cannot change schema without stopping and rebuilding replication.
DB mirroring and log shipping with both handle schema changes.
BCP will work with log shipping and replication, but not a good choice for mirroring. why? because BCP requires you to pass an instance name in the command AND if you have High Safety mirroring setup, the instance name for BCP will maybe change from run to run... (automatic failover and all)
October 1, 2010 at 8:26 am
Replication will support schema changes if you set the replicate_ddl flag when setting up the publication - no need to stop and start replication to replicate schema changes from the publisher to the subscriber if you have this option set.
If you want to modify the schema on the subscriber to be different from the publisher then this can only be done in replication - the mirrored database is read only and is only accessible through a snapshot and cannot be modified (i.e. you cannot have an extra column on the mirror and not on the principal).
And you cannot make schema changes like removing a column from a replicated table on the subscriber - the insert/update from the publisher will fail (unless you modify the SPs used by replication to propogate these changes). You can add columns to a replicated table on the subscriber - if you haven't specified a default value and the column is not nullable then you'll have to modify the replication system SPs that insert/update data to account for this. Plus if you re-setup replication after making the schema changes on the subscriber you'll lose those columns on your subscriber.
If this is the question you're asking - why would you want your subscriber schema to be different from your publisher schema?
October 1, 2010 at 10:41 am
winash (10/1/2010)
Replication will support schema changes if you set the replicate_ddl flag when setting up the publication - no need to stop and start replication to replicate schema changes from the publisher to the subscriber if you have this option set.
^^^^ only available in SQL 2005 and newer.. back in the SQL 2000 days, not possible.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply