Sql-server – Remove Column From Replicated Article without re-snapshoting in 2008r2

replicationsql serversql-server-2008-r2

I've inherited an undocumented 2008r2 Transactional Replication set-up.

There are close to 500GB of replicated articles with lots of hidden gotchas such as Indexed views being replicated to Tables.

The Development team need to Drop 1 column from a small lookup table, and Create a new column on another Table. In addition the Triggers on the 2 tables will be modified.

How can I accomplish this without re-taking the snapshot and copying all the data over again? (Am I on a fools errand? should I just start planning for a long weekend? the sooner I have a plan the better.)

The entire Publisher database isn't replicated – only selected articles. (So my snapshot options are limited, can't restore from backup)

I've done some research but can't find anything specific to 2008R2.

Best Answer

The article below talks about adding, dropping, and modifying columns in detail for 2008 R2 for various types of replication. I would start there, let me know if this is not what you were looking for.

Replication supports a wide range of schema changes to published objects. When you make any of the following schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers:

  • ALTER TABLE
  • ALTER TABLE SET LOCK ESCALATION should not be used if schema change replication is enabled and a topology includes SQL Server 2005 or SQL Server Compact 3.5 Subscribers.ALTER VIEW
  • ALTER VIEW
  • ALTER PROCEDURE
  • ALTER FUNCTION

  • ALTER TRIGGER

    ALTER TRIGGER can be used only for data manipulation language [DML] triggers because data definition language [DDL] triggers cannot be replicated.

You can use replication stored procedures to specify whether these schema changes are replicated. The stored procedure that you use depends on the type of publication.

Source

Related Question