	Product(s):	Bentley Raceway & Cable Management
	Version(s):	CONNECT Edition (10.04.00.31)
	Environment:	N/A
	Area:	Database Replication
	Subarea:	N/A

Overview

As many organizations have Bentley Raceway & Cable Management users working at remote locations and need to manage the requirement for users to connect from/to any location. perform different tasks(insert, update, delete), and the database should be kept synchronized across multiple sites.

Below are the terms used in this article:

Publisher: It is the source database, which contains data to replicate.
Subscriber: This is the destination database, there may be ma y subscribes for single publisher.
Distributor: It is used to distribute the transactions to the subscriber database.

Pre-requisites: In order to configure Peer-To-Peer publication, you need to have SQL Server 2012 developer edition or higher.

Method

Microsoft Distributed Transaction Coordinator (MS DTC)

The following steps must be done at both the Master server and Remote server.

Launch Control Panel > Administrative Tools > Component Services.
From the Component Services dialog:
a. Expand Component Services > Computers > My Computer > Distributed Transaction Coordinator.

b. Right-Click on Local DTC.
c. Select Properties.
From the Local DTC Properties dialog:
a. Select the Security tab.
b. Enable Network DTC Access.
c. Enable Allow Inbound.
d. Enable Allow Outbound.
e. Click Ok.
From the MSDTC Service dialog:
a. Click Yes.
From the MSDTC Service dialog:
a. Click
Close the Component Services dialog.

Microsoft Distributed Transaction coordinator Firewall Settings:

The following steps must be done at both the Master server and Remote server.

Launch Control Panel > Windows Firewall.
From the Windows Firewall dialog:
a. Click Allow an app or feature through Windows Firewall.
From the Allowed Programs dialog:
a. Enable Distributed Transaction Coordinator.
b. Click Ok.
Close the Windows Firewall dialog.

SQL Server Peer-To-Peer Replication:

Configuring Distribution on Master Server

Launch SQL Server Management Studio.
From the Connect to Server dialog:
a. Server name - Select the Master server.
b. Login - Use an account with SYSADMIN privileges.
c. Click Connect.
From the Object Explorer pane:
a. Right - click on Replication.
b. Select Configure Distribution.
From the Configure Distribution Wizard dialog:
a. Click Next.
From the Distributor dialog:
a. Click Next.
From the SQL Server Agent Start dialog:
a. Select Yes, configure the SQL Server Agent service to start automatically.
b. Click Next.

Note: This dialog will not appear, if the SQL Server Agent is already set to start automatically at the time of installation.
From the Snapshot Folder dialog:
a. Snapshot folder - Enter the path to a shared folder that can be accessed by the subscribers.
b. Click Next.
From the Distribution Database dialog:
a. Enter the name and location of the Distribution database.
b. Click Next.
From the Publishers dialog:
a. Click Next.
From the Wizard Actions dialog:
a. Click Next.
From the Complete the Wizard dialog:
a. Click Finish.
From the Configuring dialog:
a. Click Close.

Configuring distribution on Remote Server:

You need to follow the same process as we did for the Master server.

From the Connect to Server dialog:
Server name – Select the server
Login – Use an account with SYSADMIN privileges.
Click Connect.

Please follow the steps given in Configuring Distribution on Master Server.

Configure the Publication on Master Server:

From the Object Explorer pane:
a. Expand Replication.
b. Right-click on Local Publications.
c. Select New Publication.
From the New Publication Wizard dialog:
a. Click Next.
From the Publication Database dialog:
a. Select the database to publish.
b. Click Next.
From the Publication Type dialog:
a. Select Peer-to-Peer publication.
b. Click Next.
From the Articles dialog:
a. Enable Tables.
b. Expand Tables.
c. Select all tables.
d. Click Next.
From the Agent Security dialog:
a. Log Reader Agent – Click Security Setting.
From the Log Reader Agent Security dialog:
a. Specify the accounts to run the Log Reader Agent.
b. Click OK.
From the Agent Security dialog:
a. Click Next.
From the Wizard Actions dialog:
a. Enable Create the publication.
b. Click Finish.
From the Complete the Wizard dialog:
a. Publication name – Enter a name for the publication.
b. Click Finish.
From the Creating Publication dialog:
a. Click Close.

Configure Peer-To-Peer Topology:

From the Object Explorer pane:
a. Expand Replication > Local Publications.
b. Right-click on the Publication created in the previous section.
c. Select Configure Peer-To-Peer Topology.
From the Configure Peer-To-Peer Topology Wizard dialog:
a. Click Next.
From the Publication dialog:
a. Select the Publisher.
b. Select the Publication.
c. Click Next.
From the Configure Topology dialog:|
a. Right-click in the dialog.
b. Select Add a New Peer Node.
From the Connect to Server dialog:
a. Server name – Select the Remote server.
b. Login – Use an account with SYSADMIN privileges.
c. Click Connect.
From the Add a New Peer Node dialog:
a. Select Database – Select the restored database.
b. Peer Originator ID – Enter a unique ID for the node.
c. Enable Connect to ALL displayed nodes.
d. Select Use Push subscription.
e. click Next.
From the Configure Topology dialog:
a. Right-click in the dialog.
b. Select Redraw Graph.
c. Click Next.
From the Log Reader Agent Security dialog:
a. Click the Browse button.
From the Log Reader Agent Security dialog:
a. Specify the accounts to run the Log Reader Agent.
b. Click OK.
From the Log Reader Agent Security dialog:
a. Click Next.
From the Distribution Agent Security dialog:
a. Click the Browse button for the Master Server.
From the Distribution Agent Security dialog:
a. Specify the accounts to run the Distribution Agent.
b. Click OK.
From the Distribution Agent Security dialog:
a. Click the Browse button for the Remote Server.
From the Distribution Agent Security dialog:
a. Specify the accounts to run the Distribution Agent.
b. Click OK.
From the Distribution Agent Security dialog:
a. Click Next.
From the New Peer Initialization dialog:
a. Select I created the peer database manually.
b. Click Next.
From the Complete the Wizard dialog:
a. Click Finish.
From the Building the Peer-To-Peer Topology dialog:
a. Click Close.
After all the configuration in SQL server, the replication in SQL server will get displayed like shown in the image below.

Configure Publication Properties for both Servers:

From the Object Explorer pane:
a. Expand Replication > Local Publications.
b. Right-click on the Publication.
c. Select Properties.
From the Publication Properties dialog:
a. Click Subscription Options.
b. Set the value of Peer-to-Peer Replication > Continue replication after conflict detection to True.

Database Replication for Bentley Raceway & Cable Management

Overview

Method