Achieving Scalability and Availability with Peer-to-Peer Transactional Replication

SQL Server Technical Article

Writers: Michael Blythe

Technical Reviewers: Gopal Ashok, Qun Guo, Peng Song, Dennis Tighe

Project Editor: Jeannine Nelson-Takaki

Published: June 2006

Applies To: SQL Server 2005

Summary: This paper first discusses the benefits and evolution of peer-to-peer transactional replication. It then outlines the steps for configuring a peer-to-peer topology and provides scripts and detailed directions for completing setup of this topology. The paper closes with some frequently asked questions about peer-to-peer replication.

On This Page

Introduction
The Evolution of Peer-to-Peer Transactional Replication
Configuring a Peer-to-Peer Topology with SQL Server Management Studio
Frequently Asked Questions
Conclusion

Introduction

Two of the biggest concerns for databases administrators (DBAs) are the availability and scalability of the database servers for which they are responsible. Data should be available even if a server is down or being upgraded, and the databases serving that data should respond to user queries in a timely manner, even when those requests are at a peak. The most common approach to providing availability and scalability is to copy data to one or more servers and to have that data available for users to query. In SQL Server 2005, peer-to-peer transactional replication is typically the best way to copy data to support both availability and scalability.

Peer-to-peer replication is built on the proven foundation of transactional replication, which provides a high-performance way to deliver data changes to remote servers, whether those servers are across the building or across the world. In the default read-only transactional replication you update data at only one server, and then updates are replicated to other servers. In peer-to-peer replication you can update data at any one of the servers and then updates are replicated to all the other servers.

An online ordering system provides a clear example of how peer-to-peer replication can be used. A Web site that supports online ordering typically experiences significantly more read activity than write activity. Therefore, it is very important to scale out this read activity. Replication provides copies of the data, so that users can be directed to different servers to spread out the load. Because data is available at multiple servers, if one of the servers is down because of planned maintenance or an unplanned issue, such as a disk failure, the other servers are available to handle queries until the offline server is brought back online.

To give you an understanding of how you can use peer-to-peer replication, this paper first describes the evolution of peer-to-peer replication, and then addresses how to configure this technology, and finally provides answers to frequently asked questions.

The Evolution of Peer-to-Peer Transactional Replication

When transactional replication was introduced in SQL Server 6.0, it was primarily used to support reporting applications. Although replication now satisfies a variety of other scenarios, about eighty percent of SQL Server replication customers still use replication for reporting whether or not they use other replication functionality. The following diagram shows a typical reporting scenario with read-only data in the Staging and Reporting databases.

Figure 1. Replicating data to staging and reporting servers

The main goal in this scenario is to offload activity from the online transaction processing (OLTP) system so that reporting does not affect OLTP performance. Replicating the data to another database also lets you index data appropriately: an OLTP system might be lightly indexed, whereas a reporting system with well-defined reports might be heavily indexed. Transactional replication moves incremental changes with very low latency, which enables the reporting database(s) to be continuously synchronized in near real time with one or more source OLTP databases. Replication provides row and column filtering if only a subset of data is required for reporting.

After customers configured replication for reporting, they frequently realized that the reporting servers could be used for other things: to scale out read-activity on data other than reporting data; and to provide warm standby servers for the OLTP system. In some cases, these scenarios could be satisfied using read-only transactional replication, but many customers also used a little-known feature of SQL Server 7.0 and SQL Server 2000: bidirectional transactional replication.

Bidirectional replication enables two servers to exchange changes with each other: each server publishes data and then subscribes to a publication with the same data from the other server. A loopback_detection parameter is set to ensure that changes originating from a server are not sent back by the Subscriber receiving those changes. Many customers used bidirectional replication successfully in SQL Server 2000, including a major telecommunications company and a large credit card processing company. However, bidirectional replication was not a full-fledged feature, and it required a fair amount of manual configuration. Moreover, it did not support the types of topologies that some of our customers required, such as the ring and mixed topologies shown in the following diagrams.

Figure 2. Comparison of replication topologies

After we saw how customers used bidirectional replication, and listened to how customers wanted to extend its capabilities, we developed peer-to-peer replication for SQL Server 2005 Enterprise Edition.

Peer-to-peer replication is designed to provide high availability and scalability in one solution, and to expand our support for more complex topologies. It lets you build any kind of topology you want and ensures efficient delivery of changes to each server. For example, you could create a fully-connected topology in which all servers communicate with each other; this is called a ring topology (see the diagram) and is appropriate if you have three or four servers. If you have a larger number of servers, consider configuring replication so that each server communicates with at least two other servers. This provides availability without the complexity of many servers being fully-connected. Regardless of whether the servers are all fully-connected, peer-to-peer replication routes changes in such a way that a change is applied only one time at each server, and changes are sent over the fastest route possible.

The following diagrams show typical uses of peer-to-peer replication. Figure 3 shows replication used to scale out a Web site backend with high availability. Figure 4 shows replication used for distributed geographic data processing with high availability.

Figure 3. Using replication to scale read activity

Figure 3 illustrates a configuration in which each Web and application server uses data from a particular caching server. Reads and updates for a given user flow to a specific application server and then to a specific caching server. Because the application server updates the cache directly, a central source server is not required. Updates at each cache are propagated to the other caches.

In both Figure 3 and Figure 4, write activity for the databases is partitioned. If the database for a Web application contained a product catalog, you could, for example, direct updates for product names beginning with A-I to the first database, updates for J-R to the second database, and updates for S-Z to the third database. The updates are then replicated to the other databases.

Figure 4. Peer-to-peer replication to dispersed locations

Figure 4 shows three participating databases that provide the backend for a worldwide software support organization, with offices in Los Angeles, London, and Taipei. The support engineers at each office take customer calls and enter and update information about each customer call. The time zones for the three offices are eight hours apart, so that there is no overlap in the workday: as the Taipei office closes, the London office is opening for the day. If a call is still in progress as one office is closing, the call is transferred to a representative at the next office to open.

Each location has a database and an application server, which are used by the support engineers as they enter and update information about customer calls. The topology is partitioned by time; therefore, updates are applied only at the database that is currently open for business. The updates then flow to the other participating databases in near real-time. This topology provides the following benefits:

• Independence without isolation: each office can insert, update, or delete data independently but can also share the data because it is replicated to all other participating databases.

• Higher availability in case of failure or to allow maintenance at any of the participating databases.

Comparison of Replication Types that Allow Updates at the Subscriber

The following types of replication and replication options let you make changes at a Subscriber and have those changes flow to the Publisher. This table is provided to help you determine whether peer-to-peer transactional replication is appropriate for your application. For more information, see the following topics in BOL: "Peer-to-Peer Transactional Replication" and "Selecting the Appropriate Type of Replication".

Table 1 Updatable Replication Options

Configuring a Peer-to-Peer Topology with SQL Server Management Studio

You can configure peer-to-peer replication using SQL Server Management Studio, Transact-SQL stored procedures, and Replication Management Objects (RMO). This section of the paper shows how to configure and monitor replication using the following tools available in SQL Server Management Studio:

• New Publication Wizard

• Publication Properties dialog box

• Configure Peer-to-Peer Topology Wizard

• Replication Monitor

Configuration and monitoring is composed of the following steps:

• Creating the sample databases

• Creating the first publication

• Enabling the publication for peer-to-peer replication

• Backing up and restoring the publication database

• Creating a publication and subscriptions for each node

• Verifying that changes are replicated to each node

• Monitoring the topology

The configuration in this paper uses a single computer to simulate a three node peer-to-peer topology, with a local Distributor for the Publisher. If you configure a topology on multiple computers, each of them must have a Distributor associated with it before you can use the Configure Peer-to-Peer Topology Wizard. For more information, see "Configuring Distribution" in SQL Server Books Online.

The topology you will create is fully-connected: each node has a subscription to each of the other nodes. It is also possible to configure a topology that includes subscriptions only between some of the nodes: for example, you could configure a publication at each node, and enable subscriptions only between those nodes that should be connected. This can be accomplished in several different ways: by running the Configure Peer-to-Peer Topology Wizard multiple times on different computers, or by using the New Publication Wizard and New Subscription Wizard, by running Transact-SQL stored procedures, or by using RMO.

Creating the Sample Databases

The first step in setting up the peer-to-peer topology is to create three sample databases, and to create and populate a sample table in one of the databases. To create the databases and table, follow these steps:

1. In SQL Server Management Studio, click New Query.

2. In the Connect to Server dialog box, connect to the instance of SQL Server that you are using for this sample.

3. Copy the following script into the Query Editor window, and then click Execute.

-------Create databases for a three node topology-------

PRINT '*** Creating databases ***';
PRINT '';

CREATE DATABASE P2P_London
GO
CREATE DATABASE P2P_LosAngeles
GO
CREATE DATABASE P2P_Taipei
GO

---------------------------------------------------------

-------Create and populate a table in P2P_London---------

PRINT '*** Creating and populating table ''SampleTable'' ***';
PRINT '';

USE P2P_London
GO

CREATE TABLE SampleTable
(SampleID INT PRIMARY KEY, SampleDate DATETIME DEFAULT GETDATE(), SampleData VARCHAR(50))

INSERT INTO SampleTable (SampleID,SampleData) VALUES (1,'Furniture')
INSERT INTO SampleTable (SampleID,SampleData) VALUES (2,'Books')
INSERT INTO SampleTable (SampleID,SampleData) VALUES (3,'Toys')
INSERT INTO SampleTable (SampleID,SampleData) VALUES (4,'Appliances')
INSERT INTO SampleTable (SampleID,SampleData) VALUES (5,'Carpet')

Creating the First Publication

Now create a publication in the P2P_London database by using the New Publication Wizard. To start the New Publication Wizard:

1. Expand the Replication folder, right-click Local Publications, and then click New Publication.

   

   Figure 5. Starting the New Publication Wizard

2. On the introduction page, click Next.

   

   Figure 6. New Publication Wizard introduction page

   If the instance of SQL Server is not already configured as a Distributor or as a Publisher with a remote Distributor, you will configure distribution by using the Distributor and Snapshot Folder pages of the New Publication Wizard.

3. On the Distributor page, select <ServerName> will act as its own Distributor, and then click Next.

   

   Figure 7. Specifying the Distributor

4. On the Snapshot Folder page, type the path of a network share for the snapshot folder, and then click Next. For information about how to create a share, see the Windows documentation.

   Note

   Peer-to-peer replication does not initialize subscriptions with a snapshot, but you must still configure a snapshot folder for the Distributor. This folder can be used by other publications that use this Distributor.

   

   Figure 8. Specifying the snapshot folder

5. On the Publication Database page, select P2P_London, and then click Next.

   

   Figure 9. Selecting the publication database

6. On the Publication Type page, select Transactional publication, and then click Next.

   Note

   The option Transactional publication with updatable subscriptions is used only for immediate updating and queued updating subscriptions. These subscription types cannot be used with peer-to-peer replication. For more information, see "Updatable Subscriptions for Transactional Replication" in SQL Server Books Online.

   

   Figure 10. Specifying the publication type

7. On the Articles page, expand Tables and SampleTable(dbo).

8. Select SampleTable(dbo), and then click Next.

   Note

   Do not clear any columns, because peer-to-peer replication does not support column filtering.

   

   Figure 11. Selecting the tables to replicate

9. On the Filter Table Rows page, click Next.

   Note

   Do not create any filters on this page, because peer-to-peer replication does not support row filtering.

   

   Figure 12. Filtering data

10. On the Snapshot Agent page, click Next.

    Note

    Peer-to-peer replication does not initialize subscriptions with a snapshot; therefore, you do not have to configure snapshot creation for this publication.

    

    Figure 13. Scheduling the snapshot

11. On the Agent Security page, click Security Settings.

    Note

    Peer-to-peer replication does not initialize subscriptions with a snapshot, but you must still configure security settings for the Snapshot Agent. The same security settings can be specified for the Log Reader Agent that is used in peer-to-peer replication.

    

    Figure 14. Setting security options for replication agents (I)

12. On the Snapshot Agent Security page, enter in the Process account field a Windows user under which agents will run. For more information about the permissions that are required for the Log Reader Agent, see "Replication Agent Security Model" in SQL Server Books Online.

13. Specify the password for the Windows user, and then click OK.

    Note

    The Windows user and password are not validated in this wizard. If there are any problems, such as insufficient permissions, you will not see the problems until after the publication is created, when the Log Reader Agent runs and tries to replicate changes.

    

    Figure 15. Setting security options for replication agents (II)

14. On the Agent Security page, click Next.

    

    Figure 16. Setting security options for replication agents (III)

15. On the Wizard Actions page, select both check boxes, and then click Next.

    Note

    We recommend that you select the check box for scripting so that you will have a copy of the replication configuration for future reference. For more information about scripting, see "Scripting Replication" in SQL Server Books Online.

    

    Figure 17. Scripting and creating the publication

16. On the Script File Properties page, type a path and file name for the script.

    

    Figure 18. Specifying scripting properties

17. On the Complete the Wizard page, type a name for the publication.

18. Verify options, and then click Back to change your selections if necessary.

19. Click Finish.

    

    Figure 19. Completing the New Publication Wizard

20. On the Creating Publication page, after the wizard finishes, click Close.

    

    Figure 20. Creating the publication

Enabling the Publication for Peer-to-Peer Replication

After you have created a transactional publication, you must enable it for peer-to-peer replication in the Publication Properties dialog box. Enabling the publication sets several required properties and creates a new item (Configure Peer-to-Peer Topology) in the publication shortcut menu in SQL Server Management Studio. For more information, see "Creating a Publication and Subscriptions for Each Node" later in this paper. To open the Publication Properties dialog box:

1. Expand the Replication folder, and the Local Publications folder.

2. Right-click the publication you created, and then click Properties.

   

   Figure 21. Opening the Publication Properties dialog box

3. In the Publication Properties dialog box, on the Subscription Options page, select a value of True for the option Allow peer-to-peer subscriptions.

4. Click OK.

   

   Figure 22. Specifying that the publication should enable peer-to-peer replication

Backing Up and Restoring the Publication Database

Now that the publication has been created and enabled for peer-to-peer replication, initialize the other databases in the topology. In peer-to-peer replication, databases are typically initialized by using database backup and restore: you back up the first publication database (P2P_London) and then restore it to the other databases that you will configure for replication (P2P_LosAngeles and P2P_Taipei).

The following script performs the backup and restore and also inserts a row into the SampleTable table in the P2P_London database. The script performs the insert so that we can demonstrate that replication will apply this insert to the other two databases after they are configured. For more information, see "Verifying That Changes are Replicated to Each Node" later in this paper. To back up and restore the publication database, follow these steps:

1. In SQL Server Management Studio, click New Query.

2. In the Connect to Server dialog box, connect to the instance of SQL Server that you are using for this sample.

3. Copy the following script into the Query Editor window, and then click Execute.

Note

This script uses the variables @BackupLocation and @RestoreDir to specify the location where files should be backed up and restored. If you have a non-default installation of SQL Server, you might have to change the values of these variables.

-------Back up P2P_London and restore to other nodes-----
PRINT '';
PRINT '*** Backing up database ''P2P_London'' ***';
PRINT '';

USE master
GO

DECLARE @BackupLocation VARCHAR(1000)
SET @BackupLocation = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\P2P_London.bak'

BACKUP DATABASE P2P_London 
TO DISK = @BackupLocation 

PRINT '';
PRINT '*** Restoring database ''P2P_London'' to other nodes ***';
PRINT '';

DECLARE @RestoreDir VARCHAR(1000),
        @RestoreLaMdf VARCHAR(1000),
        @RestoreLaLdf VARCHAR(1000),
        @RestoreTaipeiMdf VARCHAR(1000),
        @RestoreTaipeiLdf VARCHAR(1000)
SET @RestoreDir = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'
SET @RestoreLaMdf = @RestoreDir + 'P2P_LosAngeles.mdf'
SET @RestoreLaLdf = @RestoreDir + 'P2P_LosAngeles_log.LDF'
SET @RestoreTaipeiMdf = @RestoreDir + 'P2P_Taipei.mdf'
SET @RestoreTaipeiLdf = @RestoreDir + 'P2P_Taipei_log.LDF'

RESTORE DATABASE P2P_LosAngeles
FROM DISK = @BackupLocation 
WITH 
  MOVE N'P2P_London' TO @RestoreLaMdf,
  MOVE N'P2P_London_log' TO @RestoreLaLdf,
  REPLACE

RESTORE DATABASE P2P_Taipei
FROM DISK = @BackupLocation 
WITH 
  MOVE N'P2P_London' TO @RestoreTaipeiMdf,
  MOVE N'P2P_London_log' TO @RestoreTaipeiLdf,
  REPLACE

PRINT '';
PRINT '*** Inserting into SampleTable in ''P2P_London'' ***';
PRINT '';

USE P2P_London
GO
INSERT INTO SampleTable (SampleID,SampleData) VALUES (6,'Sporting Goods')

Creating a Publication and Subscriptions for Each Node

The next step in this process is to configure the databases P2P_LosAngeles and P2P_Taipei to participate in the peer-to-peer topology. Configure the databases using the Configure Peer-to-Peer Topology Wizard. This wizard creates publications in the P2P_LosAngeles and P2P_Taipei databases (identical to the publication that you created in the P2P_London database) and creates subscriptions between all databases. To start the Configure Peer-to-Peer Topology Wizard:

1. Expand the Replication folder, and then expand the Local Publications folder.

2. Right-click the publication you created, and then click Configure Peer-To-Peer Topology.

   

   Figure 23. Starting the Configure Peer-To-Peer Topology Wizard

3. On the introduction page, click Next.

   

   Figure 24. Configure Peer-To-Peer Topology Wizard introduction page

4. On the Publication page, select the publication you created and then click Next.

   

   Figure 25. Selecting the publication

   On the Peers page, the instance of SQL Server you are using for this sample should be displayed in the Peer Server Instance column.

5. Select the check box next to the instance, and then select P2P_LosAngeles from the Peer Database drop-down list.

6. Click Add SQL Server.

7. In the Connect to Server dialog box, connect to the instance of SQL Server that you are using for this sample.

8. Select P2P_Taipei from the Peer Database drop-down list.

9. Click Next.

   

   Figure 26. Specifying the peer instances and databases

   In this wizard, you specify security settings for the Log Reader Agent and the Distribution Agent. The Windows user and password are not validated in this wizard. If there are any issues, such as insufficient permissions, you will not see any warnings or errors until after the publications and subscriptions are created, when agents run and try to replicate changes. For more information about the permissions that are required for each agent, see "Replication Agent Security Model" in SQL Server Books Online.

10. On the Log Reader Agent Security page, select Use the first peer’s security settings for all other peers.

11. Click the Properties button (…) in the first row of the Agent security properties grid.

12. In the Log Reader Agent Security dialog box:

    1. Enter in the Process account field a Windows user under which the Log Reader Agent will run.

    2. Enter the password for the Windows user, and then click OK.

13. On the Log Reader Agent Security page, click Next.

    

    Figure 27. Setting security options for the Log Reader Agent

14. On the Distribution Agent Security page, select Use the first peer’s security settings for all other peers.

15. Click the Properties button (…) in the first row of the Agent security properties grid.

16. In the Distribution Agent Security dialog box:

    1. Enter in the Process account field a Windows user under which the Distribution Agent will run.

    2. Enter the password for the Windows user, and then click OK.

17. On the Distribution Agent Security page, click Next.

    

    Figure 28. Setting security options for the Distribution Agent

18. 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.

19. Click Browse and the backup file for the P2P_London database. By default, the location is: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\P2P_London.bak.

    Note

    As you might recall, one of the scripts performed an insert in the first publication database (P2P_London) after the backup of that database finished. When a publication is enabled for peer-to-peer replication, replication immediately starts to track changes in the first publication database. If you select I restored a backup of the original publication database, and the publication database was changed after the backup was taken, these changes can be delivered to a restored database at one or more nodes after replication is configured.

20. Click Next.

    

    Figure 29. Specifying how you initialized subscriptions

21. On the Complete the Wizard page, verify options, and then click Back to change your selections if necessary.

22. Click Finish.

    

    Figure 30. Completing the Configure Peer-To-Peer Topology Wizard

23. On the Building the Peer-To-Peer Topology page, after the wizard finishes, click Close.

24. Note The wizard displays a warning for the publication in P2P_London to let you know that it will not create a publication or Log Reader Agent for this database, because the New Publication Wizard already created these. This is expected behavior, but any other warnings and errors indicate an issue with the configuration.

    

    Figure 31. Creating the peer-to-peer topology

Verifying That Changes are Replicated to Each Node

Now that the topology is configured, query each database to determine whether changes are being replicated. The first change to verify is the one that you made to the P2P_London database after you backed it up. Replication should deliver this change to P2P_LosAngeles and P2P_Taipei. After verifying that change, execute the second and third scripts in this section to insert rows and verify that those rows are replicated.

1. Execute the following script in SQL Server Management Studio.

   1. In SQL Server Management Studio, click New Query.

   2. In the Connect to Server dialog box, connect to the instance of SQL Server that you are using for this sample.

   3. Copy the following script into the Query Editor window, and then click Execute.

   Each result set should contain six rows, including one with a value of 6 in the SampleID column.

   USE P2P_London
   GO
   SELECT * FROM SampleTable
   
   USE P2P_LosAngeles
   GO
   SELECT * FROM SampleTable
   
   USE P2P_LosAngeles
   GO
   SELECT * FROM SampleTable
   

2. Execute the following script to insert one row in the P2P_LosAngeles database and one row in the P2P_Taipei database.

   USE P2P_LosAngeles
   GO
   INSERT INTO SampleTable (SampleID,SampleData) VALUES (7,'Games')
   
   USE P2P_Taipei
   GO
   INSERT INTO SampleTable (SampleID,SampleData) VALUES (8,'Kitchen Ware')
   

3. Wait several seconds to let changes propagate to each database. Then execute the following script to verify that changes were replicated. Each result set should now contain eight rows.

   USE P2P_London
   GO
   SELECT * FROM SampleTable
   
   USE P2P_LosAngeles
   GO
   SELECT * FROM SampleTable
   
   USE P2P_LosAngeles
   GO
   SELECT * FROM SampleTable
   

Monitoring the Topology

You can now use Replication Monitor to monitor the publications and subscriptions you created. To start Replication Monitor:

1. In SQL Server Management Studio, right-click the Replication folder, and then click Launch Replication Monitor.

   

   Figure 32. Starting Replication Monitor

2. In Replication Monitor, expand the Publisher node (shown as MYSRVR in Figure 33).

3. Select one of the newly created publications.

   Subscription status is displayed on the All Subscriptions tab in the right pane. The time it took for data to replicate from one node to another is listed in the Latency column, and a relative rating of this performance is listed in the Performance column.

   

   Figure 33. Viewing subscription status in Replication Monitor

   To view the details of replication processing, you can double-click the subscription row in the right pane. The Distributor To Subscriber History tab shows the history for the selected Distribution Agent.

   

   Figure 34. Viewing Distribution Agent history

   The Publisher To Distributor History tab displays the history for the Log Reader Agent.

   

   Figure 35. Viewing Log Reader Agent history

   The Undistributed Commands tab displays information about the number of commands in the distribution database that have not been delivered to the selected node, and the estimated time to deliver those commands.

4. Close the dialog box and return to the main Replication Monitor interface.

   

   Figure 36. Viewing the number of commands in the distribution database

   Tracer tokens are used to diagnose the current performance of transactional replication. A token, which is a small amount of data, is written to the transaction log of the publication database, marked as if it were a typical replicated transaction, and then sent through the system. The elapsed time is then measured.

   Note

   Latency measurements can be misleading for peer-to-peer topologies, because data changes can be delivered through different routes. As an example, consider a three node topology in which:

   • The connection between node A and node B is fast

   • The connection between node A and node C is slow.

   • The connection between node B and node C is fast.

   A tracer token sent from node A to node C might take an unacceptably long time, but data changes sent from node A to node C might actually be replicated from node A to node B and then from node B to node C in an acceptable amount of time; in peer-to-peer replication changes are always delivered by the fastest route.

   A token will travel through the whole topology only if the topology is fully-connected. For example, if the topology included node D, which was connected only to node C, the token inserted at node A will not measure the latency from node A to node D.

5. In Replication Monitor, click the Tracer Tokens tab.

6. Click Insert Tracer.

   

   Figure 37. Adding a tracer token for a publication

   After a short time, Replication Monitor displays three numbers as indicators of transactional replication performance: the time to replicate the tracer token from the publication database to the distribution database; the time to replicate the tracer token from the distribution database to the subscription database; and the total elapsed time. If one of the agents is stopped and the tracer cannot be replicated, Replication Monitor displays Pending until the agent is restarted.

7. Verify that a value is displayed in the Total Latency column.

8. Close Replication Monitor.

   

   Figure 38. Viewing elapsed time for the tracer token

You have now completed the configuration and monitoring of a sample peer-to-peer topology. You should have a system in that lets you make changes in any of the participating databases and replicates those changes to the other two databases. If you encountered any issues during configuration, testing, or monitoring, see "Troubleshooting Replication" in SQL Server Books Online.

The next section of the paper addresses questions frequently asked by customers. We recommend reading through this information and then spending more hands-on time with peer-to-peer replication. Possible areas to focus on include (all references are to SQL Server Books Online):

• Reviewing administrative procedures. For more information, see "How to: Administer a Peer-to-Peer Topology (Replication Transact-SQL Programming)".

• Performing schema changes on one or more databases in the topology. For more information, see "Making Schema Changes on Publication Databases".

• Adding a node to the existing topology. For more information, see "How to: Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio)".

• Scripting out the whole topology and reviewing the scripts to understand the options that are set. For more information, see "Scripting Replication".

• Configuring a topology of three or more nodes on separate computers.

Frequently Asked Questions

Q: What tools are available to configure and administer a peer-to-peer topology?

A: You can configure peer-to-peer replication using SQL Server Management Studio (recommended), stored procedures, and Replication Management Objects (RMO). SQL Server provides the following tools to help administer a peer-to-peer topology:

• SQL Server Management Studio. Management Studio contains dialog boxes and property sheets related to replication. For more information, see "Replication Properties" in SQL Server Books Online.

• SQL Server Replication Monitor. Replication Monitor is a graphical tool that lets you monitor the overall health of a replication topology. For more information, see "Monitoring Replication with Replication Monitor" in SQL Server Books Online.

• The tablediff utility. This command line utility enables you to identify differences between databases. For more information, see "tablediff Utility" in SQL Server Books Online.

• Tracer tokens. Tracer tokens enable you to determine when a single database has received a change through a single route. For more information, see "Measuring Latency and Validating Connections for Transactional Replication" in SQL Server Books Online.

• Note: Tracer tokens also enable you to measure latency, but latency measurements can be misleading for peer-to-peer topologies. Data changes can flow throughout a peer-to-peer topology; some connections might show high latency although others are delivering the changes in an acceptable amount of time.

• The stored procedures sp_requestpeerresponse and sp_helppeerresponses. These procedures enable you to determine when all participating databases have received a change. For more information, see "sp_requestpeerresponse" and "sp_helppeerresponses" in SQL Server Books Online.

• The Distribution Agent parameters OutputVerboseLevel and HistoryVerboseLevel. Setting these parameters to a higher value provides more information about replication processing, which can be useful during testing and troubleshooting. We recommend default values for production systems because additional logging can affect performance.

Q: Which route will peer-to-peer replication use when replicating data? Will a single change be applied by multiple agents to the same database?

A: Peer-to-peer replication uses whichever route is available and fastest at the time that data is replicated. Rows are delivered to each node only one time. Consider a topology with nodes A, B, and C: a Distribution Agent delivers an update from node C to node A, but then there is a problem with the connection to node C. The update that originated at node C is delivered to node B from node A. When the connection to node C is restored, the Distribution Agent for node B recognizes that node B has already received the update and does not try to deliver it.

Q: Can a user specify which route a change should take when it is replicated?

A: No. As described in the previous answer, peer-to-peer replication uses whichever route is available and fastest at the time that data is replicated.

Q: How many routes should I create between nodes?

A: The number of routes that you create depends on the level of redundancy that the system requires. With a small number of nodes, it is reasonable for all nodes to be connected. For example, in a topology with nodes A, B, and C, you could create the following subscriptions: A to B, B to A, A to C, C to A, B to C, and C to B. However, do not create replication redundancy that you do not need. For example, to have ten nodes that are connected through every possible route requires ninety subscriptions.

Q: What is the maximum number of nodes in a peer-to-peer topology?

A: SQL Server does not enforce a maximum limit, but we recommend that topologies not exceed ten nodes for reasons of performance and manageability. Performance can decrease when many nodes are fully-connected, and manageability becomes an issue as the number of agents increases (a ten node fully-connected topology uses 100 agents).

Q: How many Distributors should I have? Which servers should act as Distributors?

A: You should typically use one Distributor for each node; if nodes share a Distributor, there is a single point of failure in the system. As in read-only transactional replication, remote Distributors can provide better performance.

Q: Why are snapshots not supported for initialization and reinitialization?

A: During initialization of subscriptions in a peer-to-peer topology, replication must track additional metadata. This metadata is not easily tracked when snapshots are used, but it is automatically tracked by the SQL Server backup and restore processes.

Q: Why are filters not supported?

A: Peer-to-peer replication is based on a multi-master topology. In such a scenario each node (which is also a master), is expected to have the same set of schema and data.

Q: Can I have identity columns in tables that are published using peer-to-peer replication?

A: You should avoid use of identity columns. When you use identity columns, you must manually manage the ranges that are assigned to the tables at each participating database. For more information, see the section "Replicating Identity Columns" in SQL Server Books Online.

Q: How do I add a database to an existing topology?

A: Follow these steps:

1. Restore a backup of an existing node to a new location.

2. Configure replication at the database that supplied the backup to the new location, using the Configure Peer-to-Peer Topology Wizard or stored procedures.

Using this method, you can establish a connection between the new database and the database from which the backup was taken. If you want to add connections between the new database and other databases in the topology, you must quiesce the topology before you add the new subscriptions (quiescing a topology involves stopping insert, update, and delete activity and letting all changes replicate. For more information, see "How to: Quiesce a Replication Topology" in SQL Server Books Online.).

For more information about adding nodes, see "How to: Configure Peer-to-Peer Transactional Replication" in SQL Server Books Online.

Q: How do I recover a failed node?

A: Use one of the following options:

• Restore another database node backup over the failed database and follow the instructions to configure it as a new peer-to-peer node. For more information, see "How to: Configure Peer-to-Peer Transactional Replication" in SQL Server Books Online.

• Use the standard backup and restore procedures, as long as you can restore completely without data loss. For more information, see "Strategies for Backing Up and Restoring Snapshot and Transactional Replication" in SQL Server Books Online.

Q: How do I add and drop columns and make other schema changes?

A: Follow these steps:

1. Quiesce the topology: stop insert, update, and delete activity and let all changes replicate. For more information, see "How to: Quiesce a Replication Topology" in SQL Server Books Online.

2. Issue the DDL statement(s) for the schema changes. Each set of schema changes should be issued from a single database in the topology.

3. After the schema changes has been replicated to all databases, resume activity on the other databases.

For more information, see "How to: Administer a Peer-to-Peer Topology (Replication Transact-SQL Programming)" in SQL Server Books Online.

Q: Does peer-to-peer replication handle data conflicts?

A: No. Peer-to-peer replication is optimized for throughput performance and does not incur the overhead of detecting and resolving conflicts. Custom applications that access and change data must ensure that inserts, updates, and deletes are partitioned, so that modifications to a given row originating at one node are synchronized with all other databases in the topology before the row is modified by a different node. If an application performs concurrent conflicting modifications to a given row at multiple nodes, you should use merge replication, which is designed for handling conflicts. For more information, see "Merge Replication Overview" in SQL Server Books Online.

Q: How can I discover and resolve data conflicts?

A: Follow these steps:

1. Quiesce the topology: stop insert, update, and delete activity and let all changes replicate. For more information, see "How to: Quiesce a Replication Topology" in SQL Server Books Online.

2. If agents have stopped with data errors, use sp_helpsubscriptionerrors to find rows with issues. Optionally, use sp_browsereplcommands to view commands in the distribution database and use sp_replshowcmds to view transaction log data.

3. Run validation on each node. For more information, see "Validating Replicated Data" in SQL Server Books Online.

4. Review the Distribution Agent history output from validation to see which tables do not match.

5. Use the tablediff utility to identify non-convergence, and optionally generate SQL statements to bring nodes back into convergence.

Q: Which features of transactional replication are not supported by peer-to-peer replication?

A: Peer-to-peer replication supports the core features of transactional replication. It does not support the following options:

• Initialization and reinitialization with a snapshot.

• Row and column filters.

• Timestamp columns.

• Non-SQL Server Publishers and Subscribers.

• Immediate updating and queued updating subscriptions.

• Anonymous subscriptions.

• Partial subscriptions.

• Attachable subscriptions and transformable subscriptions (both deprecated in SQL Server 2005).

• Shared Distribution Agents.

• The Distribution Agent parameter SubscriptionStreams and the Log Reader Agent parameter MaxCmdsInTran.

• The article properties @destination_owner and @destination_table.

The following properties have special considerations:

• The publication property @allow_initialize_from_backup requires a value of 'true'.

• The article property @replicate_ddl requires a value of 'true'; @identityrangemanagementoption requires a value of 'manual'; and @status requires that option 24 is set.

• The value for article properties @ins_cmd, @del_cmd, and @upd_cmd cannot be set to 'SQL'.

• The subscription property @sync_type requires a value of 'none' or 'automatic'.

Conclusion

Peer-to-peer replication is a robust technology built on the solid foundation of SQL Server transactional replication; it is ideally suited for applications that require read scalability and availability for maintenance or failure management. In this paper, we described the evolution of peer-to-peer transactional replication so that you could better understand why we developed this technology. We provided detailed configuration information so that you could set up and monitor a peer-to-peer topology on a single computer. Finally, we listed answers to customer questions, so that you can understand some of the design decisions we made in creating the technology. We hope that the paper has been instructive and that peer-to-peer transactional replication fits the needs of your applications.

For More Information

• https://www.microsoft.com/technet/prodtechnol/sql/default.mspx

Download

P2ptranrepl.doc
2.1 MB
Microsoft Word file
Get Office File Viewers