Back

Topic

[KB1232]Replication of two SQL Data Base

Tags: HDS, Historic, Replication

Historical data
1 month ago
By HOTE
Options
Print
Applies to:
PcVue 16.2 onwards
Summary:
In this article, we’ll describe the procedure for setting up database replication using SSMS. We’ll assume the setup involves two machines, each configured with the following: – One PcVue instance set up as a historical data server. – One SQL Server instance.
Details:

The architecture :

PcVue Configuration:
You can configure two databases for each instance, assigning them different names, while ensuring that the table names remain identical across both databases.
Below is an example configuration:

Data Base NameDatabase 01Database 02
Trend table nameTrendTableTrendTable
Log table nameLogTableLogTable

Machine Configuration:
To enable communication between the two SQL Server instances and setup the replication, follow these steps on each machine:

  • Install the SQL Server Replication Features:
  • Activate the Sql Server Browsing Service:
  • Activate the TCP/IP on the adequate SQL Server instance

NB : Check that the port (by default 1433) is authorised

  • Start the SQL Server Agent.
  • Select the appropriate account to enable the logon (use the current user account).

1- Set up the replication
The configuration needs to be performed on a single machine.
Once you have selected the machine where the replication will be deployed, follow the steps below:

Open SSMS -> Replication -> Local Publications -> New Publication

  • Select the account (it is also possible to use an SQL Server user).
  • Select the database you want to replicate

Kind of replication:

  • Snapshot publication (not compatible with PcVue):

The Publisher sends a snapshot of the published data to Subscribers at scheduled intervals.

  • Transactional publication (not compatible with PcVue):

The Publisher streams transactions to the Subscribers after they receive an initial snapshot of the published data.

  • Peer-to-Peer publication (not include on SQL Server Standard edition license):

Peer-Peer publication enables multi-master replication. The publisher streams transactions to all the peers in the topology. All peer nodes can read and write changes, and the changes are propagated to all the nodes in the topology.

  • Merge publication (The most usable):

The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications.

-> About this article we’ll setup a Merge publication:

  • Select The table “LogTable” and “TrendTable”
  • Configure a schedule to execute the replication.

By default, the schedule is set to execute only once. You need to modify the schedule to occur at regular intervals, for example, every 1 hour.

Execution of the Agent Security:

• It is possible to connect using a Microsoft account, provided that the user has the necessary authorization.

Alternatively, it is possible to run with the SQL Server Agent (in this case, you should specify the appropriate user, as explained previously).

• Connect to the publisher: It is recommended to use a SQL Server login (administration account).

  • Provide a name for the publication:
  • Configure The subscriptions:

Right click on the replication -> New Subscriptions

  • Add the subscriber from the other machine to connect to the instance:

Then select the other database:

  • Configure the merge agent security:
  • Test the replication:
  • To change the schedule:

Created on: 18 Mar 2025