Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 18, 2009

Peer-to-Peer Replication in SQL Server 2008 - Add a node and resolve conflict

By Yan Pan

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

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.

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.

, assign 3 as the ID for PowerServer3

After clicking OK, PowerServer3 now appears on the design surface. It connects with POWERPC and DEMOPC with double arrows.

PowerServer3 now appears on the design surface

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.

the New Peer Initialization page

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.

The Distribution Agents on POWERPC and DEMOPC detect the conflict and fail to deliver changes.

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.

invoke the Conflict Viewer

In the Conflict Viewer, you can see the conflict data.

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.

The conflict data on POWERPC

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.

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.

» See All Articles by Columnist Yan Pan



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM