對發行集資料庫進行結構描述變更Make Schema Changes on Publication Databases

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

複寫支援對已發行物件進行大範圍的結構描述變更。Replication supports a wide range of schema changes to published objects. 當您對「MicrosoftMicrosoft SQL ServerSQL Server 發行者」端適當的已發行物件進行下列任何結構描述變更時,依預設,該變更會傳播給所有的「SQL ServerSQL Server 訂閱者」:When you make any of the following schema changes on the appropriate published object at a MicrosoftMicrosoft SQL ServerSQL Server Publisher, that change is propagated by default to all SQL ServerSQL Server Subscribers:

  • ALTER TABLEALTER TABLE

  • ALTER TABLE SET LOCK ESCALATION (如果已啟用結構描述變更複寫,且拓撲包含 SQL Server 2005 (9.x)SQL Server 2005 (9.x)SQL Server Compact 3.5SQL Server Compact 3.5 訂閱者,則不應使用)。ALTER TABLE SET LOCK ESCALATION should not be used if schema change replication is enabled and a topology includes SQL Server 2005 (9.x)SQL Server 2005 (9.x) or SQL Server Compact 3.5SQL Server Compact 3.5 Subscribers.

  • ALTER VIEWALTER VIEW

  • ALTER PROCEDUREALTER PROCEDURE

  • ALTER FUNCTIONALTER FUNCTION

  • ALTER TRIGGERALTER TRIGGER

    ALTER TRIGGER (僅可用於資料管理語言 [DML] 觸發器,因為無法複寫資料定義語言 [DDL] 觸發器。)ALTER TRIGGER can be used only for data manipulation language [DML] triggers because data definition language [DDL] triggers cannot be replicated.

重要

必須使用 Transact-SQLTransact-SQLSQL ServerSQL Server Management Objects (SMO) 對資料表進行結構描述變更。Schema changes to tables must be made by using Transact-SQLTransact-SQL or SQL ServerSQL Server Management Objects (SMO). SQL Server Management StudioSQL Server Management Studio中進行結構描述變更時, Management StudioManagement Studio 會嘗試卸除並重新建立資料表。When schema changes are made in SQL Server Management StudioSQL Server Management Studio, Management StudioManagement Studio attempts to drop and re-create the table. 您無法卸除已發行的物件,因此結構描述變更會失敗。You cannot drop published objects, therefore the schema change fails.

對於異動複寫與合併式複寫,結構描述變更會在「散發代理程式」或「合併代理程式」執行時進行累加傳播。For transactional replication and merge replication, schema changes are propagated incrementally when the Distribution Agent or Merge Agent runs. 對於快照式複寫,結構描述變更則在「訂閱者」套用新的快照集時進行傳播。For snapshot replication, schema changes are propagated when a new snapshot is applied at the Subscriber. 在快照式複寫中,每次發生同步處理時,結構描述的新副本便會傳送至「訂閱者」。In snapshot replication, a new copy of the schema is sent to the Subscriber each time synchronization occurs. 因此,對先前已發行物件進行的所有結構描述變更 (不僅僅是以上所列的變更) 會與每個同步處理一起自動傳播。Therefore, all schema changes (not just those listed above) to previously published objects are automatically propagated with each synchronization.

如需新增及移除發行集發行項的資訊,請參閱在現有發行集中新增和卸除發行項For information about adding and removing articles from publications, see Add Articles to and Drop Articles from Existing Publications.

若要複寫結構描述變更To replicate schema changes

依預設,會複寫以上所列的結構描述變更。The schema changes listed above are replicated by default. 如需有關停用結構描述變更複寫的詳細資訊,請參閱< Replicate Schema Changes>。For information about disabling the replication of schema changes, see Replicate Schema Changes.

結構描述變更之考量Considerations for Schema Changes

複寫結構描述變更時,請記住下列考量。Keep the following considerations in mind when replicating schema changes.

一般考量General Considerations

  • 結構描述變更必須遵從由 Transact-SQLTransact-SQL規定的任何條件約束。Schema changes are subject to any restrictions imposed by Transact-SQLTransact-SQL. 例如,ALTER TABLE 不允許對主索引鍵資料行執行 ALTER。For example, ALTER TABLE does not allow you to ALTER primary key columns.

  • 資料類型對應只會針對初始快照集執行。Data type mapping is performed only for the initial snapshot. 結構描述變更並不會對應到舊版的資料類型。Schema changes are not mapped to previous versions of data types. 例如,如果在 ALTER TABLE ADD datetime2 column 內使用陳述式 SQL Server 2012 (11.x)SQL Server 2012 (11.x),則資料類型不會針對 ssVersion2005 訂閱者轉譯成 SQL Server 2005 (9.x)SQL Server 2005 (9.x)For example, if the statement ALTER TABLE ADD datetime2 column is used in SQL Server 2012 (11.x)SQL Server 2012 (11.x), the data type is not translated to nvarchar for SQL Server 2005 (9.x)SQL Server 2005 (9.x) Subscribers. 在某些案例中,發行者上會封鎖結構描述變更。In some cases, schema changes are blocked on the Publisher.

  • 如果發行集設定為允許傳播結構描述變更,則不論發行集中發行項的相關結構描述選項如何設定,結構描述都會傳播。If a publication is set to allow the propagation of schema changes, schema changes are propagated regardless of how the related schema option is set for an article in the publication. 例如,如果選取不對資料表發行項的外部索引鍵條件約束進行複寫,但是接著發出 ALTER TABLE 命令,將外部索引鍵新增至「發行者」端的資料表,則外部索引鍵會新增至「訂閱者」端的資料表。For example, if you select not to replicate foreign key constraints for a table article, but then issue an ALTER TABLE command that adds a foreign key to the table at the Publisher, the foreign key is added to the table at the Subscriber. 若要防止發生這種情況,則在發出 ALTER TABLE 命令前停用結構描述變更的傳播。To prevent this, disable the propagation of schema changes before issuing the ALTER TABLE command.

  • 僅應在「發行者」而不是「訂閱者」端 (包括重新發行「訂閱者」) 進行結構描述變更。Schema changes should be made only at the Publisher, not at Subscribers (including republishing Subscribers). 合併式複寫防止在「訂閱者」端的結構描述變更。Merge replication prevents schema changes at the Subscriber. 異動複寫不會防止變更,但是變更會造成複寫失敗。Transactional replication does not prevent the changes, but the changes can cause replication to fail.

  • 依預設,傳播至重新發行訂閱者的變更會傳播至其訂閱者。Changes propagated to a republishing Subscriber are by default propagated to its Subscribers.

  • 如果結構描述變更參考了存在於「發行者」而不是「訂閱者」上的物件或條件約束,則結構描述變更會在「發行者」上成功,但會在「訂閱者」上失敗。If the schema change references objects or constraints existing on the Publisher but not on the Subscriber, the schema change will succeed on the Publisher but will fail on the Subscriber.

  • 新增外部索引鍵時所參考之「訂閱者」上所有物件的名稱和擁有者,必須與「發行者」上對應的物件相同。All objects on the Subscriber that are referenced when adding a foreign key must have the same name and owner as the corresponding object on the Publisher.

  • 明確新增、卸載或改變索引不會複寫,而且任何涉及明確索引的變更,都必須在每個複本集上個別執行。Explicit adding, dropping, or altering indexes is not replicated and any change involving an explicit index will need to be run on each replica set individually. 支援為條件約束 (例如主索引鍵條件約束) 隱含建立的索引。Indexes created implicitly for constraints (such as a primary key constraint) are supported.

  • 不支援改變或卸除由複寫管理的識別欄位。Altering or dropping identity columns that are managed by replication is not supported. 如需自動管理識別欄位的詳細資訊,請參閱複寫識別欄位For more information about automatic management of identity columns, see Replicate Identity Columns.

  • 不支援包括非決定性函數的結構描述變更,因為該變更會導致「發行者」和「訂閱者」上的資料各不相同 (稱為非聚合)。Schema changes that include nondeterministic functions are not supported because they can result in data at the Publisher and Subscriber being different (referred to as non-convergence). 例如,如果在「發行者」端發出下列命令: ALTER TABLE SalesOrderDetail ADD OrderDate DATETIME DEFAULT GETDATE(),則將命令複寫至「訂閱者」並執行此命令時,值會各不相同。For example, if you issue the following command at the Publisher: ALTER TABLE SalesOrderDetail ADD OrderDate DATETIME DEFAULT GETDATE(), the values are different when the command is replicated to the Subscriber and executed. 如需有關不具決定性函數的詳細資訊,請參閱< Deterministic and Nondeterministic Functions>。For more information about nondeterministic functions, see Deterministic and Nondeterministic Functions.

  • 建議對條件約束進行明確命名。It is recommended that constraints be explicitly named. 如果條件約束沒有明確命名, SQL ServerSQL Server 會產生條件約束的名稱,並且這些名稱在發行者及每個訂閱者會有所不同。If a constraint is not explicitly named, SQL ServerSQL Server generates a name for the constraint, and these names will be different on the Publisher and each Subscriber. 在結構描述變更的複寫期間,這可能會造成問題。This can cause issues during the replication of schema changes. 例如,如果您卸除發行者端的資料行,並且卸除相依性條件約束,複寫便會嘗試卸除訂閱者端的條件約束。For example, if you drop a column at the Publisher and a dependent constraint is dropped, replication will attempt to drop the constraint at the Subscriber. 因為條件約束名稱不同,所以訂閱者端的卸除動作會失敗。The drop at the Subscriber will fail because the name of the constraint is different. 如果同步處理因條件約束名稱問題而失敗,請手動卸除訂閱者端的條件約束,然後重新執行合併代理程式。If synchronization fails because of a constraint naming issue, manually drop the constraint at the Subscriber and then rerun the Merge Agent.

  • 發行資料表來進行複寫時,如果已產生發行集快照集,則無法將該資料表中的資料行改為 XML 資料類型。若要改變此資料行,您必須先移除複寫。If a table is published for replication, it is not possible to alter a column in that table to a data type of XML if a publication snapshot has already been generated To alter the column, you must first remove replication.

  • 在已發行的資料表上執行 DDL 時,讀取未認可不是一個支援的隔離等級。Read uncommitted is not a supported isolation level when doing DDL on a published table.

  • 如果針對已發行的物件執行結構描述變更,就不應該使用SET CONTEXT_INFO 來修改交易的內容。SET CONTEXT_INFO should not be used to modify the context of transactions where schema changes are performed against published objects.

新增資料行Adding Columns

  • 若要將新資料行新增至資料表,並將該資料行包含在現有發行集中,則執行 ALTER TABLE <資料表> 新增 <資料行>。To add a new column to a table and include that column in an existing publication, execute ALTER TABLE <Table> ADD <Column>. 依預設,資料行然後便會寫至所有「訂閱者」。By default, the column is then replicated to all Subscribers. 資料行必須允許 NULL 值或包含預設條件約束。The column must allow NULL values or include a default constraint. 如需有關加入資料行的詳細資訊,請參閱本主題中的「合併式複寫」一節。For more information about adding columns, see the "Merge Replication" section in this topic.

  • 若要將新資料行新增至資料表,並且在現有發行集中不包含該資料行,則停用結構描述變更的複寫,然後執行 ALTER TABLE <資料表> 新增 <資料行>。To add a new column to a table and not include that column in an existing publication, disable the replication of schema changes, and then execute ALTER TABLE <Table> ADD <Column>.

  • 若要在現有發行集中包含現有資料行,則使用 sp_articlecolumn (Transact-SQL)sp_mergearticlecolumn (Transact-SQL) 或 [發行集屬性 - 發行集>]< 對話方塊。To include an existing column in an existing publication, use sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or the Publication Properties - <Publication> dialog box.

    如需詳細資訊,請參閱 Define and Modify a Column FilterFor more information, see Define and Modify a Column Filter. 這需要重新初始化訂閱。This will require subscriptions to be reinitialized.

  • 不支援在已發行的資料表上加入識別欄位,因為可能在資料行複寫至「訂閱者」時,導致無法聚合。Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. 簽發者端識別欄位中的值,隨著受影響的資料表之資料列的實際儲存順序而不同。The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. 資料列可能以不同方式儲存在訂閱者端;因此識別欄位的值可能與同資料列的不同。The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows.

卸除資料行Dropping Columns

  • 若要從現有發行集卸除資料行,並從「發行者」端的資料表卸除該資料行,則執行 ALTER TABLE <資料表> 卸除 <資料行>。To drop a column from an existing publication and drop the column from the table at the Publisher, execute ALTER TABLE <Table> DROP <Column>. 依預設,資料行然後便會從所有「訂閱者」端的資料表中卸除。By default, the column is then dropped from the table at all Subscribers.

  • 若要從現有發行集卸除資料行,但要將該資料行保留在「發行者」端的資料表中,則使用 sp_articlecolumn (Transact-SQL)sp_mergearticlecolumn (Transact-SQL) 或 [發行集屬性 - 發行集>]< 對話方塊。To drop a column from an existing publication but retain the column in the table at the Publisher, use sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or the Publication Properties - <Publication> dialog box.

    如需詳細資訊,請參閱 Define and Modify a Column FilterFor more information, see Define and Modify a Column Filter. 這需要產生新的快照集。This will require a new snapshot to be generated.

  • 要卸除的資料行不可以用於資料庫內任何發行集,任何發行項的篩選子句。The column to be dropped cannot be used in the filter clauses of any article of any publication in the database.

  • 卸除已發行之發行項的資料行時,請考慮會影響資料庫的任何條件約束、索引或資料行屬性。When dropping a column from a published article, take into consideration any constraints, indexes, or properties of the column that could affect the database. 例如:For example:

    • 您無法從交易式發行集的發行項中卸除使用於主索引鍵的資料行,因為它們由複寫使用。You cannot drop columns used in a primary key from articles in transactional publications, because they are used by replication.

    • 無法從合併式發行集的發行項中卸除 rowguid 資料行,或從支援更新訂閱之交易式發行集的發行項中卸除 mstran_repl_version 資料行,因為它們由複寫使用。You cannot drop the rowguid column from articles in merge publications or the mstran_repl_version column from articles in transactional publications that support updating subscriptions, because they are used by replication.

    • 索引變更不會傳播至訂閱者:如果您卸除發行者端的資料行,並且卸除相依性條件約束,則不會複寫索引卸除。Index changes are not propagated to Subscribers: if you drop a column at the Publisher and a dependent index is dropped, the index drop is not replicated. 您必須先卸除訂閱者端的索引,然後卸除發行者端的資料行,如此一來,從發行者複寫至訂閱者時才會成功將資料行卸除。You should drop the index at the Subscriber before dropping the column at the Publisher, so that the column drop succeeds when it is replicated from the Publisher to the Subscriber. 如果同步處理因訂閱者端的索引而失敗,請手動卸除索引,然後重新執行合併代理程式。If synchronization fails because of an index at the Subscriber, manually drop the index and then rerun the Merge Agent.

    • 條件約束應明確命名,才能允許卸除。Constraints should be explicitly named to allow for dropping. 如需詳細資訊,請參閱本主題中的「一般考量」一節。For more information, see the "General Considerations" section earlier in this topic.

異動複寫Transactional Replication

  • 結構描述變更傳播至執行舊版 SQL ServerSQL Server的「訂閱者」,但是 DDL 陳述式應僅包含「訂閱者」端版本所支援的語法。Schema changes are propagated to Subscribers running previous versions of SQL ServerSQL Server, but the DDL statement should only include syntax supported by the version at the Subscriber.

    如果「訂閱者」重新發行資料,則唯一受支援的結構描述變更會新增及卸除資料行。If the Subscriber republishes data, the only supported schema changes are adding and dropping a column. 應該在「發行者」端使用 sp_repladdcolumn (Transact-SQL)sp_repldropcolumn (Transact-SQL) 進行這些變更,而不是使用 ALTER TABLE DDL 語法。These changes should be made on the Publisher using sp_repladdcolumn (Transact-SQL) and sp_repldropcolumn (Transact-SQL) rather than ALTER TABLE DDL syntax.

  • 結構描述變更不會複寫到非 SQL Server 訂閱者。Schema changes are not replicated to non-SQL Server Subscribers.

  • 結構描述變更不會從非SQL ServerSQL Server 發行者傳播。Schema changes are not propagated from non-SQL ServerSQL Server Publishers.

  • 您無法改變複寫為資料表的索引檢視表。You cannot alter indexed views that are replicated as tables. 複寫為索引檢視的索引檢視可以改變,但是改變它們會使其變成一般的檢視而不是索引檢視。Indexed views that are replicated as indexed views can be altered, but altering them will cause them to become regular views, rather than indexed views.

  • 如果發行集支援立即更新或佇列更新訂閱,則在進行結構描述變更之前必須停止系統:已發行資料表上的所有活動必須在「發行者」或「訂閱者」端停止,且暫止資料變更必須傳播至所有節點。If the publication supports immediate updating or queued updating subscriptions, the system must be quiesced before making schema changes: all activity on the published table must be stopped at the Publisher and Subscribers, and pending data changes must be propagated to all nodes. 結構描述變更傳播至所有節點之後,可於已發行的資料表上繼續進行活動。After the schema changes have propagated to all nodes, activity can resume on the published tables.

  • 如果發行集在點對點拓撲中,則進行結構描述變更前必須停止系統。If the publication is in a peer-to-peer topology, the system must be quiesced before making schema changes. 如需詳細資訊,請參閱停止複寫拓撲 (複寫 Transact-SQL 程式設計)For more information, see Quiesce a Replication Topology (Replication Transact-SQL Programming).

  • 將時間戳記資料行新增至資料表並將時間戳記對應至 binary(8),會造成所有作用中訂閱的發行項重新初始化。Adding a timestamp column to a table and mapping the timestamp to binary(8) causes the article to be reinitialized for all active subscriptions.

合併式複寫Merge Replication

  • 合併式複寫如何處理結構描述變更是由發行集相容性層級以及快照集是設定為原生模式 (預設值) 還是字元模式而定:How merge replication handles schema changes is determined by the publication compatibility level, and whether the snapshot is set to native mode (default) or character mode:

    • 若要複寫結構描述變更,則發行集的相容性層級必須至少為 90RTM。To replicate schema changes, the compatibility level of the publication must be at least 90RTM. 如果訂閱者端執行舊版 SQL ServerSQL Server 或相容性層級小於 90RTM,則可以使用 sp_repladdcolumn (Transact-SQL)sp_repldropcolumn (Transact-SQL) 新增及卸除資料行。If Subscribers are running previous versions of SQL ServerSQL Server or the compatibility level is less than 90RTM you can use sp_repladdcolumn (Transact-SQL) and sp_repldropcolumn (Transact-SQL) to add and drop columns. 不過,這些程序都已被取代。However, these procedures are deprecated.

    • 如果您嘗試將具有 SQL Server 2008SQL Server 2008中導入之資料類型的資料行加入到現有發行項, SQL ServerSQL Server 便具有以下行為:If you try to add to an existing article a column with a data type that was introduced in SQL Server 2008SQL Server 2008, SQL ServerSQL Server has the following behavior:

      100RTM,原生快照集100RTM, native snapshot 100RTM,字元快照集100RTM, character snapshot 所有其他相容性層級All other compatibility levels
      hierarchyidhierarchyid 允許變更Allow change 封鎖變更Block change 封鎖變更Block change
      geographygeometrygeography and geometry 允許變更Allow change 允許變更*Allow change* 封鎖變更Block change
      檔案資料流filestream 允許變更Allow change 封鎖變更Block change 封鎖變更Block change
      datetimedatetime2datetimeoffsetdate, time, datetime2, and datetimeoffset 允許變更Allow change 允許變更*Allow change* 封鎖變更Block change

      * SQL Server Compact 訂閱者會在訂閱者上轉換這些資料類型。*SQL Server Compact Subscribers convert these data types at the Subscriber.

  • 如果套用結構描述變更時發生錯誤 (例如,因為新增參考了「訂閱者」端上不可用之資料表的外部索引鍵而發生的錯誤),則同步處理會失敗且必須重新初始化訂閱)。If an error occurs when applying a schema change (such as an error resulting from adding a foreign key that references a table not available at the Subscriber), synchronization fails and the subscription must be reinitialized.

  • 如果在聯結篩選或參數化篩選涉及的資料行上進行結構描述變更,則必須重新初始化所有訂閱並重新產生快照集。If a schema change is made on a column involved in a join filter or parameterized filter, you must reinitialize all subscriptions and regenerate the snapshot.

  • 合併式複寫會提供預存程序,以在疑難排解期間略過結構描述變更。Merge replication provides stored procedures to skip schema changes during troubleshooting. 如需詳細資訊,請參閱 sp_markpendingschemachange (Transact-SQL)sp_enumeratependingschemachanges (Transact-SQL)For more information, see sp_markpendingschemachange (Transact-SQL) and sp_enumeratependingschemachanges (Transact-SQL).

另請參閱See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
ALTER VIEW (Transact-SQL) ALTER VIEW (Transact-SQL)
ALTER PROCEDURE (Transact-SQL) ALTER PROCEDURE (Transact-SQL)
ALTER FUNCTION (Transact-SQL) ALTER FUNCTION (Transact-SQL)
ALTER TRIGGER (Transact-SQL) ALTER TRIGGER (Transact-SQL)
發行資料和資料庫物件 Publish Data and Database Objects
重新產生自訂交易程序以反映結構描述變更Regenerate Custom Transactional Procedures to Reflect Schema Changes