Sample Replication Peer-To-Peer SQL Server 2008 .

What is Peer-To-Peer Replication ?

By the declaration of MSDN for Peer-To-Peer Replication 'Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes.'

The benefits of the P2P Replication

1- Read Increase : any inserted data for any nodes will be spread to other nodes.
2- Maintains availability : if any node will be down then the application layer can redirect to a complete data for any other active node.
3- Online/Offline data availability : any node can work out side the replication (for administarative tasks or maintenance requirement) and then return back to P2P replication to get all  lost data form other nodes.

The Topologies of P2P Replication

the following topology dislpay the interaction between clients and SQL Server databases in the IT comany infrastructure.

Another topologies.

our example assume working with two instance (SERVER08) and (SERVER08\INST2) . we need to have a replicate between two instances.

before anything run the following script, it's about creating database with table and insertions for this table. this database is our target of working with peer to peer (p2p) between two instances.

create database MySampleDB

go
use MySampleDB
go
create table Emp_Tbl
(
id int primary key ,
nam nvarchar(max)
)
go
insert into Emp_Tbl values(1,'ahmed')
insert into Emp_Tbl values(2,'mohamed')
insert into Emp_Tbl values(3,'emad')
insert into Emp_Tbl values(4,'tareq')

1- the first step to configure p2p for two instance, is check the instances services and agents in the SQL Server Configuration Manager

the highlight services in the last pic must to be run before working with p2p replication .

2- after that goto SQLServer database that is found in (SERVER08) and make full backup to c:\mybackup.bak.

3- restore this backup to another instance (SERVER08\INST2).

4- now we need to configure Distribution server for both instances. each inctance will have a separate Distribution server .

5- Configure Distribution server for (SERVER08)

6- Configure Distribution server for (SERVER08\INST2)

it's the same with previous step, but with (SERVER08\INST2) instance.

7- Return again to (SERVER08) .

form the local publication folder in the Replication folder . right click and select New Publication.

 select your database from the list

for working with P2P replication you must select transactional replication from the list of publication type

 select your objects that you want to replicate

if you need any filters about your data.

you can select from one or two option in Snapshot Agent , select the first option to create snapshot immediately . if you need to make scheduale you can select the second option also.

your agent need to set security, click on the Security Settings button

set administration account here by 'yourdomain\youraccount' template and set administrator password.

type your publication name.

8- after finish the publication creation, right click on it and then select properties

from subscription options tab, select Allow peer-to-peer subscriptions option and change value from Flase to  True. and then click OK.

9- Return to publication item (MyPublication) again in the first server (Server08),  then right click and select

Configure Peer-To-Perr Topology

this option will let chance to relate multi-nodes together to distribute data between them .

select your publication to configure there topology

this screen will lead to draw all your nodes that wanted to be added and links together. if you select any node you will see ToolTip contain infomarion about this node.

Remember that node mean SQLServer Device or instance that will be found in your Topology .

To add new node in your topology, right click on any empty area inside your topology and then select Add a New Peer Node .

Select the second instance you want to add (or second server) .

after that select your database that will act a replication in the second server (in our example you will select thr recovered database that you do that in previous steps)

if you need SQL Server to link your new node directly with the exist nodes in the topology , check on the option Connect to ALL displayed nodes. and take your new node a new rank by the Peer Originator ID

after that your topology will display your new node with other nodes. you can repeat this step again with new nodes .

all your new nodes will appear in the Log Reader Agent Security window. your need here to set security setting about agent to let replicate use log reader with a wright context security.

Here, set security agent for replicate data distribution. if you need to one context security for all nodes , check on below option.

In this step you need to tell thew wizard how can get data for new peers. if you have a backup, wizard can restore it for you [the second selection] . or you can do that manually (that is our situation) [the first selection]

your can check your affect of your P2P replication by view Replication folder for each instance. you can summarize that each node will act (Publication and Subscriber) for others.

10 - Now you test your replication between your inctances by inserting new records in both tables for each database instance .


In server 'Server08'
insert into dbo.Emp_Tbl values (5,'taha')

In server 'Server08\Inst2'
select * from MySampleDB.dbo.emp_tbl -- your will see new record added

In server 'Server08'

insert into dbo.Emp_Tbl values (5,'taha')

In server 'Server08\Inst2'
select * from MySampleDB.dbo.emp_tbl -- your will see new record added