With enterprises growing larger each day, it has become customary for most of
them to distribute their databases across locations. For instance, a national
level company might wish to separate their sales database by state so that each
regional sales office can manage their transactions and updates. However, one of
the problems that occur in such a scenario is when all other locations need to
see some of these changes and be able to send changes they themselves make
across. Till now, replication used to solve some of these issues by using a
Publisher-Subscriber model to allow a particular server to be a publisher of
some data and other nodes to be subscribers to this data and be able to pull it
in. For instance, master table data could be managed at the central location and
all other locations would be able to see changes that occurred there. There were
ways to send data 'back' to the publisher as well-but were prone with
difficulties such as conflicts arising from same data updates etc.
Direct Hit! |
Applies To: Database admins USP: Manage data changes across multiple locations Primary Link: msdn.microsoft.com Keywords: P2P replication in SQL Server 2008 ON DVD: PCQ_PROFESSIONAL /labs |
SQL Server 2008 introduces a new type of data replication called Peer-to-Peer
replication. This is actually a subset of Transactional replication, but takes
care of the issues that this faced in two-way data updates. P2P replication can
work over many nodes on the network and pass data from any node to any other
nodes as well. Refer to the architecture diagram to see a 4-way P2P replication
topology. You can scale up the topology (dependent of course on network latency
and availability amongst other things) to 15--20 nodes per replication group.
Scaling out further can involve creating mesh-like environment of many P2P
setups connected to each other.
So how do you setup P2P replication in SQL 2008? For this, you need to have
the appropriate access rights to each of the servers that make up the P2P set.
You also need the replication component setup in all the nodes. Once that is in
place, you need to open up SQL Server Mgmt Studio (SSMS) and connect to each of
the database engine servers. Since this is P2P replication, you can start the
configuration from any of the nodes unlike in any of the other replication
types. Select a node and expand the tree in Object Explorer to replication.
Right click this and select Configure Distribution. A wizard pops up that walks
you through the initial instantiation of the replication engine. For a simple
setup, the defaults will be fine. Perform these steps in each node you want to
add to the Peer list.
Once this is complete, open Replication | Local Publications. Right click
this and select New Publication and follow the wizard. Select a database that
you wish to replicate first. In the next step you need to select Transactional
replication. This is because P2P replication is a special case of Transactional
replication and we will configure this later on. Select the tables you wish to
replicate to all nodes in the next step and setup the correct SQL Server or
Windows Domain account that has the appropriate permissions to talk to the
database. Give the publication a name like MyP2PReplication and save it. To
configure this replication to be a P2P one, open the Properties dialog for the
replication and go to the Subscription Options screen. Select True for the Allow
P2P Subscriptions value and save the settings. Now, when you right-click the
replication name you'll see a new option-Configure P2P Topology. Select this to
bring up P2P configuration wizard.
Continue in the wizard till you reach a topology configuration screen. You
can start adding nodes in this screen for P2P. Right click an empty part of the
screen and select Add a new Peer Node. Select a database server (with right
permissions) and then select the database name to use for replication. Make sure
that you increment the Peer Originator ID for each node. Once all the nodes are
in place, right click any node and select Add a new Peer Connection. This brings
up a rubber-banding arrow that moves with your mouse and attaches to other
nodes. Click on a node to create a P2P replication between that and the origin.
Perform this for each node so that they talk to each other. You can also speed
this up by selecting Connect to all displayed nodes which creates the topology
for a server to all others. To create a mesh, perform this step with all nodes.
Continue on and enter the appropriate security information for access to each
node's database. Once done, complete the wizard and wait till the topology is
actually setup. Now right click Replication in any server and bring up
Replication Monitor. This will show you whether the replication is correctly
setup or not. To see the effect of replication, make some changes (insert,
updates, deletes) in any table that you selected for replication in any server.
Very soon you'll see the same change occuring in all other nodes as well.
Some very important things to keep in mind while setting up P2P replication.
1. P2P does not use Snapshots to create the 'base' image for databases across
nodes. It is up to you to ensure that the databases start off with identical
schemas and data before you setup the replication.
2. Primary conflicts can occur here. You need to ensure that if a table has
data getting inserted at any node, primary keys are unique across nodes. This
can be done by:
a. Using GUIDs as PKs
b. Using composite keys with one part of the key being unique to the server
c. Using Identity columns with start and step values to ensure no conflicts. For
instance, one on server, the identity field can be 1+2 (so that it uses 1, 3,
5...) and on another it uses 2+2 (so that it uses 2, 4, 6 ...).
3. P2P is available only in the Enterprise Edition of SQL Server 2008.
Once you setup P2P it becomes very easy to manage data changes across
multiple locations. This is a great new feature of SQL Server 2008 and can make
the life of a database admin much better.