In the first article of this series, we configured a two-node Peer-to-Peer replication topology. In this article, we will add a third node to the topology and demonstrate how to resolve an update-update conflict. In SQL Server 2005, if you need to change an existing peer-to-peer topology, the entire topology has to be quiesced. In SQL Server 2008, adding a new node can be done online. This is a big improvement.
Assume we have a new SQL Server 2008 server PowerServer3 in UK that serves customers from UK. We need to add it to the existing topology. Here are the steps.
1. Configure distribution at PowerServer3. If you don’t know how to do this, please refer to the first article of this series.
2. Initialize the schema and data at PowerServer3 to prepare for the addition.
As in the first article, we back up the GlobalSales database on POWERPC and restore the backup to PowerServer3.
-- Back up GlobalSales database on POWERPC BACKUP DATABASE GlobalSales TO DISK='D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\
MSSQL\Backup\GlobalSales.bak' -- Restore the backup on PowerServer3 RESTORE DATABASE GlobalSales FROM DISK='\\POWERPC\D$\Microsoft SQL Server\MSSQL10.MSSQLSERVER\
MSSQL\Backup\GlobalSales.bak' WITH MOVE 'GlobalSales' TO 'D:\SQLServer\MSSQL10.MSSQLSERVER\
MSSQL\DATA\GlobalSales.mdf', MOVE 'GlobalSales_log' TO 'D:\SQLServer\MSSQL10.MSSQLSERVER\
MSSQL\DATA\GlobalSales_log.LDF'
3. Add PowerServer3 to the topology.
Right-click the PubSales publication on POWERPC, and then click Configure Peer-to-Peer Topology to invoke the Configure Peer-to-Peer Topology Wizard
On the Publication page, select the PubSales publication. On the Configure Topology page, right click on the design surface. Select Add a New Peer Node.
In the Connect to Server dialog box, connect to PowerServer3. In the Add New Peer Node dialog box, select the publication database and select Connect to ALL displayed nodes. Since Peer ID 1 and 2 have been assigned to POWERPC and DEMOPC, assign 3 as the ID for PowerServer3, and then click OK.
After clicking OK, PowerServer3 now appears on the design surface. It connects with POWERPC and DEMOPC with double arrows.
Follow the instructions in the wizard to set up the security for the log reader and distribution agents. On the New Peer Initialization page, select “I restored a backup of the original publication database, and the publication database was changed after the backup was taken” and select the backup on POWERPC that was used to initialize the GlobalSales database on PowerServer3.
Configure all necessary publications and subscriptions at the following Peer(s): POWERPC Create a push subscription to publication '[GlobalSales].[PubSales]' on 'POWERPC' with the following options: Subscriber: PowerServer3 Subscription database: GlobalSales Agent location: Distributor Agent process account: Impersonate process account Connection to Distributor: Impersonate process account Connection to Subscriber: Impersonate process account Synchronization type: Initialize from Backup DEMOPC Create a push subscription to publication '[GlobalSales].[PubSales]' on 'DEMOPC' with the following options: Subscriber: PowerServer3 Subscription database: GlobalSales Agent location: Distributor Agent process account: Impersonate process account Connection to Distributor: Impersonate process account Connection to Subscriber: Impersonate process account Synchronization type: Initialize from LSN (0x000000350000001A0007) PowerServer3 Create a peer-to-peer transactional publication with the following options: Publication database: GlobalSales Publication name: PubSales Conflict detection: Enabled Peer originator id: 3 Log Reader Agent process: Impersonate 'SQLServerAgent service account' Create a push subscription to publication '[GlobalSales].[PubSales]' on 'PowerServer3' with the following options: Subscriber: POWERPC Subscription database: GlobalSales Agent location: Distributor Agent process account: Impersonate process account Connection to Distributor: Impersonate process account Connection to Subscriber: Impersonate process account Synchronization type: Replication support only Create a push subscription to publication '[GlobalSales].[PubSales]' on 'PowerServer3' with the following options: Subscriber: DEMOPC Subscription database: GlobalSales Agent location: Distributor Agent process account: Impersonate process account Connection to Distributor: Impersonate process account Connection to Subscriber: Impersonate process account Synchronization type: Replication support only
As you can see above, on POWERPC, a push subscription is created with PowerServer3 as the subscriber. On DEMOPC, a push subscription is also created with PowerServer3 as the subscriber. On PoweServer3, a publication with the same name, PubSales is created. Two push subscriptions for this publication are created with POWERPC and DEMOPC as the subscribers separately. You can also see that new Distribution Agents were created.
POWERPC-GlobalSales-PubSales-POWERSERVER3-12 (on POWERPC) DEMOPC-GlobalSales-PubSales-POWERSERVER3-8 (on DEMOPC) POWERSERVER3-GlobalSales-PubSales-DEMOPC-3 (on PowerServer3) POWERSERVER3-GlobalSales-PubSales-POWERPC-4 (on PowerServer3)
4. Test the topology by adding a row on PowerServer3:
-- Add a row on PowerServer3
USE GlobalSales
INSERT INTO Customers (ID, FirstName, LastName, CountryCode)
VALUES ('GBR-0001', 'Martin', 'Innes', 'GBR')
The row will be replicated to the Customers table on POWERPC and DEMOPC. Now the Customers table on all three nodes has 3 rows with ID: USA-0001, CHN-0001 and GBR-0001.
Let’s create a conflict situation by updating the row with ID “USA-0001” on POWERPC. Right after that, update the same row on DEMOPC.
-- On POWERPC UPDATE dbo.Customers SET LastName = 'Smith' WHERE ID='USA-0001' -- On DEMOPC UPDATE dbo.Customers SET LastName = 'Tomei' WHERE ID='USA-0001'
The Distribution Agents on POWERPC and DEMOPC detect the conflict and fail to deliver changes. All three servers are marked “X” in the Replication Monitor.
You can check the Distributor To Subscriber History tab in Replication Monitor or view the job history of the Distribution Agent. The error message is as follows.
On PowerPC, A conflict of type 'Update-Update' was detected at peer 2 between peer 1 (incoming),
transaction id 0x0000000000001287 and peer 2 (on disk),
transaction id 0x0000000000001460 (Source: MSSQLServer, Error number: 22815) On DEMOPC, A conflict of type 'Update-Update' was detected at peer 1 between peer 2 (incoming),
transaction id 0x0000000000001460 and peer 1 (on disk),
transaction id 0x0000000000001287 (Source: MSSQLServer, Error number: 22815)
If the transaction from POWERPC has been distributed to POWERSERVER3, you will see also see this error on POWERSERVER3.
On PowerServer3, A conflict of type 'Update-Update' was detected at peer 2 between peer 1 (incoming),
transaction id 0x0000000000001287 and peer 2 (on disk),
transaction id 0x0000000000001460 (Source: MSSQLServer, Error number: 22815)
The data inconsistency needs to fix manually. By default, Distribution Agent is configured to stop applying changes when it detects a conflict and no conflict data is logged into the conflict table. Usually, you will need to find out the inconsistent data. If the Distribution Agent is allowed to continue, a conflict is logged locally on the node where it was detected and you can view the conflict.
To allow the Distribution Agent on DEMOPC to continue, change the ‘p2p_continue_onconflict’ option to true.
-- On POWERPC USE [GlobalSales] EXEC sp_changepublication @publication = 'PubSales', @property = N'p2p_continue_onconflict', @value = true GO
To verify the option has been changed, run the following command. You should see the value 1 in the p2p_continue_onconflict column.
USE GlobalSales EXEC sp_helppublication @publication = 'PubSales'
Restart the Distribution Agent on DEMOPC. To view the conflict data, you can use the conflict viewer. Right click the PubSales publication on DEMOPC and click View Conflicts to invoke the Conflict Viewer.
In the Conflict Viewer, you can see the conflict data.
As shown above, the peer with the higher ID, in this case, DEMOPC wins the conflict. The peer with higher ID has higher priority.
You can also view the conflict data by running the following query on DEMOPC.
USE GlobalSales SELECT * FROM [dbo].[conflict_dbo_Customers] Result: __$originator_id,__$origin_datasource,__$tranid,__$conflict_type,__$is_winner,__$pre_version,__$reason_code,__$reason_text,
__$update_bitmap,__$inserted_date,__$row_id,__$change_id,ID,FirstName,LastName,CountryCode,CreationDate 2,1,0x0000000000001287,1,0,0x02000000A213000000000000,1,An update-update conflict between peer 1 (incoming) and peer 2 (on disk)
was detected and resolved. The incoming update was skipped by peer 2.,0x04,2009-03-29 20:02:26.763,
0x0000000000001F80,NULL,USA-0001,NULL,Smith,NULL,NULL 2,2,0x0000000000001460,1,1,NULL,1,An update-update conflict between peer 1 (incoming) and peer 2 (on disk) was detected and
resolved. The incoming update was skipped by peer 2.,NULL,2009-03-29 20:02:26.763,0x0000000000001F81,
0x0000000000001F80,USA-0001,
Melissa,Tomei,USA,2009-03-29 10:47:53.8670000 +00:00
Two rows have been returned. As you can see, the second row has a value 1 in the __$is_winner column, which indicates the row belongs to the conflict winner. The __$origin_datasource has a value 2, which indicates the winner is the peer with originated ID 2, DEMOPC.
Do the same for PowerPC as the conflict was also detected at this node, the Distribution Agent will continue. The conflict data on POWERPC is shown as below.
If the conflict is resolved, all nodes will be brought to a consistent state. If you check the LastName column for the Customer with ID ‘USA-0001’ on POWERPC and DEMOPC, you should see it has the value “Tomei” from DEMOPC.
However, to ensure that the conflicting rows converged correctly, run a validation test on all the subscriptions.
In the detail windows in Replication Monitor: on the Distributor to Subscriber History tab for transactional replication, you should see validation output like this:
Table 'Customers' passed rowcount (3) and checksum validation. Checksum is not compared for any text or image columns.
If data is still inconsistent after this step, you must manually update rows on the node that has the highest priority, and then let the changes propagate from this node.
After everything is normal, disable the ‘p2p_continue_onconflict’ option on the distribution agent at DEMOPC and POWERPC. This is to ensure that the data must be checked manually after a conflict is detected to ensure data consistency before the Distribution Agent continues.
USE [GlobalSales] EXEC sp_changepublication @publication = 'PubSales', @property = N'p2p_continue_onconflict', @value = false GO
As you can see above, Peer-to-Peer Replication does not provide sophisticated conflict resolution. The resolution is simply based on peer ID. You also need to manually check the data to make sure the conflicting data converge correctly. During my testing, I found that sometimes conflicts could not be resolved even after the subscriptions were validated successfully. It is quite annoying when that happens. You will need to either reinitialize nodes or add the parameter “-SkipErrors 22815” to the Distribution Agent to allow the replication to continue. Therefore, it is important to partition data well beforehand and make sure any data row is updated only at one server.
Summary
In this article series, we have shown you how to set up a two node Peer-to-Peer replication topology, add a third node to the topology, and demonstrate how to resolve an update-update conflict. As you can see, Peer-to-Peer replication is a very suitable solution for scaling out workload, especially in the cases where you need to localize data updates, but at the same time, making the same data available throughout all the locations.
