Administer a Peer-to-Peer Topology (Replication Transact-SQL Programming)
Applies To: SQL Server 2016
Administering a peer-to-peer topology is similar to administering a typical transactional replication topology, but there are a number of areas with special considerations. The principal difference in administering a peer-to-peer topology is that some changes require the system to be quiesced. Quiescing a system involves stopping activity on published tables at all nodes and ensuring that each node has received all changes from all other nodes. For more information, see Quiesce a Replication Topology (Replication Transact-SQL Programming).
Quiesce the system.
Stop the Distribution Agent at each node in the topology. For more information, see Replication Agent Executables Concepts or Start and Stop a Replication Agent (SQL Server Management Studio).
Execute the CREATE TABLE statement to add the new table at each node in the topology.
Bulk copy the data for the new table manually at all nodes by using the bcp utility.
After sp_addarticle is executed, replication automatically adds the article to the subscriptions in the topology.
Restart the Distribution Agents at each node in the topology.
Quiesce the system.
Execute the data definition language (DDL) statements to modify the schema of published tables. For more information about supported schema changes, see Make Schema Changes on Publication Databases.
Before you resume activity on published tables, quiesce the system again. This ensures that schema changes have been received by all nodes before any new data changes are replicated.
The following example demonstrates how to add a new table article to an existing peer-to-peer replication topology that has two nodes.
-- Create the new table at both nodes. CREATE TABLE AdventureWorks2012.dbo.ProductTest (column1 int, Column2 int); CREATE TABLE AdventureWorks2012Replica.dbo.ProductTest (column1 int, Column2 int); GO
REM Bulk insert data into both the publication and subscription databases. REM The BCP format depends on the snapshot format (native or character). REM Execute at the command prompt. bcp AdventureWorks2012..ProductTest in NewTable.bcp -T -SMYPUBLISHER n/c bcp AdventureWorks2012Replica..ProductTest in NewTable.bcp -T -SMYPUBLISHER n/c
--- Add the article to the publication. DECLARE @publication AS sysname; DECLARE @newtable AS sysname; SET @publication = N'AdvWorksProductTran'; SET @newtable = N'ProductTest'; USE AdventureWorks2012 EXEC sp_addarticle @publication = @publication, @article = @newtable, @source_table = @newtable, @destination_table = @newtable, @force_invalidate_snapshot = 0; GO