Work at Computer LOU:
USE MASTER
CREATE DATABASE [aPeer]
GO
ALTER DATABASE [aPeer] SET RECOVERY FULL
GO
CREATE LOGIN [VANSTUDENTS\agent] FROM WINDOWS
GO
USE aPeer
GO
CREATE USER [VANSTUDENTS\agent] FOR LOGIN [VANSTUDENTS\agent]
GO
EXEC sp_addrolemember N'db_owner', N'VANSTUDENTS\agent'
GO
CREATE TABLE [dbo].[Contact](
[Name] [nchar](10) NULL ,
[Address] [nvarchar](50) NULL,
[ID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ([ID] ASC)
)
--You must have a primary key for transactional replication.
--Don't use the IDENTITY type. If used, problem will occur when you insert records.
Configuring Local Distributor
USE distribution
GO
CREATE USER [VANSTUDENTS\agent] FOR LOGIN [VANSTUDENTS\agent]
GO
EXEC sp_addrolemember N'db_owner', N'VANSTUDENTS\agent'
GO
Backup the aPeer database
Copy the backup to the computer IMAGE21.
Work at Computer IMAGE21:
Restore database from the backup to create aPeer database.
GO
CREATE LOGIN [VANSTUDENTS\agent] FROM WINDOWS
GO
USE aPeer
GO
CREATE USER [VANSTUDENTS\agent] FOR LOGIN [VANSTUDENTS\agent]
GO
EXEC sp_addrolemember N'db_owner', N'VANSTUDENTS\agent'
GO
Configuring Local Distributor
USE distribution
GO
CREATE USER [VANSTUDENTS\agent] FOR LOGIN [VANSTUDENTS\agent]
GO
EXEC sp_addrolemember N'db_owner', N'VANSTUDENTS\agent'
GO
Create a publication: contact
Contact publication--Properties
Subscription Options: Peer-to-Peer replication --true
Publication Access List: Vanstudents\agent
--OK
Right Click on-Contact --Configuring Peer-to-Peer replication
Add the LOU as the peer.
Work at Computer LOU:
Contact Publication Properties--Publication Access List--Vanstudents\Agent.
Test the replication.
LOU:
INSERT Contact(Name,Address,ID) VALUES('Loudone','burnaby',NEWID());
Image21:
--you must wait for completion of the replication from LOU, and then issue the following statement
INSERT Contact(Name,Address) VALUES('Imagedone','Vancouver',NEWID());
================
You can check the Contact table for the new record.
================
If you want to update the Contact simultaneously, make sure you don't update the same row at same time in the two participating computers.
NOTES:
Pay attention to the following:
Both participating databases should contain identical schema. Object names, object schema, and publication names should be identical among the participating databases. When you create publications, no row and column filtering are used.
To be simplified, I use one security principal VANSTUDENTS\AGENT. SET it as a member of db_owner role of distribution, publisher, and subscriber databases.
If you follow the least privilege guidance and the replication does not happen, set the Distribution Agent and Log Reader Agent to use SQL Server Agent service account. It will work most of the time.
Peer to Peer replication --transactional-- use two agents: distribution and log reader. SNAPSHOT agent is not used.
You can check which agent(s) start from TASK MANAGER --Processes tab.
AS you will see, I did not change the default snapshot folder. The same schema and other objects are warrantied through backup and restore.
NOTE:
I create a scenario: one DC and a member server, both have the SQL Server installed.
I have to configure the Distribution Agent with SQL Server service account in order to replication transactional from Member to DC.
It is possible that DC is Windows Server 2003 with SP2, however Member is Windows Server 2003 with SP1.
