BANGALORE, INDIA: 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.

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.