[MSSQL]新增article到已存在的publication操作

[tim]

參考微軟 SQL 2005 help 內說明:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b148e907-e1f2-483b-bdb2-59ea596efceb.htm
線上說明連結: http://msdn.microsoft.com/en-us/library ... L.90).aspx
Adding Articles to and Dropping Articles from Existing Publications

After a publication is created, it is possible to add and drop articles. Articles can be added at any time, but the actions required for dropping articles depend on the type of replication and when the article is dropped.

Adding Articles
1. Adding an article involves: adding the article to the publication; creating a new snapshot for the publication; synchronizing the subscription to apply the schema and data for the new article.

Add one or more articles through one of the following methods:

Microsoft SQL Server Management Studio: How to: Add Articles to and Drop Articles From a Publication (SQL Server Management Studio)
Replication Transact-SQL programming: How to: Define an Article (Replication Transact-SQL Programming)
Replication Management Objects (RMO) programming: How to: Define an Article (RMO Programming)


2. After adding an article to a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters). The Distribution Agent or Merge Agent then copies the schema and data for the new article to the Subscriber (it does not reinitialize the entire publication).

To create a new snapshot


SQL Server Management Studio: How to: Create and Apply the Initial Snapshot (SQL Server Management Studio)


Replication Transact-SQL programming: How to: Create the Initial Snapshot (Replication Transact-SQL Programming)


RMO programming: How to: Create the Initial Snapshot (RMO Programming)


To create a new snapshot for a merge publication with parameterized filters


SQL Server Management Studio: How to: Generate a Snapshot for a Merge Publication with Parameterized Filters (SQL Server Management Studio)


Replication Transact-SQL programming: How to: Create a Snapshot for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming)


3. After the snapshot is created, synchronize the subscription to copy the schema and data for the new article.

To synchronize a push subscription


SQL Server Management Studio: How to: Synchronize a Push Subscription (SQL Server Management Studio)


Replication Transact-SQL programming: How to: Synchronize a Push Subscription (Replication Programming)


RMO programming: How to: Synchronize a Push Subscription (RMO Programming)


To synchronize a pull subscription


SQL Server Management Studio: How to: Synchronize a Pull Subscription (SQL Server Management Studio)


Replication Transact-SQL programming: How to: Synchronize a Pull Subscription (Replication Programming)


RMO programming: How to: Synchronize a Pull Subscription (RMO Programming)



Dropping Articles
Articles can be dropped from a publication at any time, but you must take into account the following behaviors:

Dropping an article from a publication does not remove the object from the publication database or the corresponding object from the subscription database. Use DROP <Object> to remove these objects if necessary. When you drop an article that is related to other published articles through foreign key constraints, we recommend that you drop the table at the Subscriber manually or by using on-demand script execution: specify a script that includes the appropriate DROP <Object> statements. For more information, see How to: Execute Scripts During Synchronization (Replication Transact-SQL Programming).


For merge publications with a compatibility level of 90RTM or higher, articles can be dropped at any time, but a new snapshot is required. Additionally:


If an article is a parent article in a join filter or logical record relationship, the relationships must be dropped first, which requires reinitialization.


If an article has the last parameterized filter in a publication, subscriptions must be reinitialized.


For merge publications with a compatibility level lower than 90RTM, articles can be dropped with no special considerations prior to the initial synchronization of subscriptions. If an article is dropped after one or more subscriptions is synchronized, the subscriptions must be dropped, recreated, and synchronized. For more information about compatibility level, see the "Compatibility Level for Merge Publications" section in the topic Using Multiple Versions of SQL Server in a Replication Topology.


For snapshot or transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized. For more information about dropping subscriptions, see Subscribing to Publications and sp_dropsubscription (Transact-SQL). sp_dropsubscription allows you to drop a single article from the subscription rather than the entire subscription.


Dropping an article from a publication involves dropping the article and creating a new snapshot for the publication. Dropping an article invalidates the current snapshot; therefore a new snapshot must be created.

To drop an article from a publication


SQL Server Management Studio: How to: Add Articles to and Drop Articles From a Publication (SQL Server Management Studio)


Replication Transact-SQL programming: How to: Delete an Article (Replication Transact-SQL Programming)


RMO programming: How to: Delete an Article (RMO Programming)


After dropping an article from a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters).

To create a new snapshot


SQL Server Management Studio: How to: Create and Apply the Initial Snapshot (SQL Server Management Studio)


Replication Transact-SQL programming: How to: Create the Initial Snapshot (Replication Transact-SQL Programming)


RMO programming: How to: Create the Initial Snapshot (RMO Programming)


To create a new snapshot for a merge publication with parameterized filters


SQL Server Management Studio: How to: Generate a Snapshot for a Merge Publication with Parameterized Filters (SQL Server Management Studio)


Replication Transact-SQL programming: How to: Create a Snapshot for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming)


As noted above, in some cases dropping an article requires subscriptions to be dropped, recreated, and then synchronized. For more information, see Subscribing to Publications and Synchronizing Data.

重點在於上面紅色那段, 新增 article 到 publication, 要做一份新的 snapshot, 然後 replication 的機制會自動將該 snapshot 的資料轉到所有的 subscriber 上, 而不用整個重寫一次, 但是做一份新的 snapshot 也是夠累的(我是說 server 啦)!!