Monday, November 14, 2011

Configuring Peer to Peer Replication

1.       Connect to SSMS and take 2 instances.

2.       Make both the instances as publisher as well as distributor.
3.       Go to first instance create one transactional publication from required database.
4.       Right click on publication take properties

5.       Select “Subscription Options” and Allow Peer to peer subscriptions= True
6.       OK
7.       Go to Node1 i.e. first server take full backup of Galactic database. (We have to take full backup of database from which publication was created)
                use master
     BACKUP DATABASE Galactic TO DISK='c:\backups\Galactic.bak'
8.       Now restoring backup in Node2 (server2)
1.       Create a folder for data and transaction log files in server2 and grant read write permissions to server2 service account
2.       Restoring database
·         Right click on Databases in second server
·         Restore Database….

·         To database = Galactic
·         Select “From device” click on browse button à Add

·         Select backup file (c:\backups\Galactic.bak)
·         OK
·         OK
·         Select checkbox under Restore option
·         Click Options, under Restore As change the file paths as follows

·         Click Options, under Restore As change the file paths as follows
·         OK
9.       Right click on publication select configure peer to peer topology

10.   Click Next
11.   (Select publication )Next
12.   Right on the surface and select Add New peer node as follows

13.   Select second instance à Connect
14.   Select Database= Galactic and Peer Originator ID=2 as follows

15.   OK
16.   Right click on first node à Select Connect to all displayed node
17.   Next
18.   Configure Log Reader Agent Security à Next
19.   Configure Distribution Agent security à Next
20.   Select first option à Next
21.   Next à Finish
22.   Close
23.   Test by performing changes in both the servers.
24.   Observe that publication is created in second node.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

SQL Server Error Logs

* Error Logs maintains events raised by SQL Server database engine or Agent. * Error Logs are main source for troubleshooting SQL Server...

SQL Server DBA Training

SQL Server DBA Training
SQL Server DBA