locked
MSSQL peer-to-peer replication only sync 1 way RRS feed

  • Question

  • Hi,

    My name is Adi, i am trying to create peer to peer replication between 2 Microsoft SQL Server 2014 Enterprise edition on Windows server 2012 R2 VM. I follow this steps on 

    - http://www.databasejournal.com/features/mssql/article.php/3814591/Peer-to-Peer-Replication-in-SQL-Server-2008-150-Configure-a-two-node-topology.htm

    - http://www.sanssql.com/2013/11/sql-server-replication-configuring-peer.html

    But i only manage to make the replication 1 way only.  Do you have any advice how to make it replicate 2 way ?

    Regards,

    Adi

    Wednesday, July 27, 2016 2:53 AM

Answers

  • Hi,

    We manage to figure the problem.

    Troubleshooting Steps
    ====================
    1.       We checked the monitoring and found the following error message:
    The process could not execute 'sp_replcmds' on 'MYSERVER1'.
     

    2.       For this error message, it also means the files of the database don’t have an owner. So we assign an owner and the replication works fine.

     Regards,

    Adi

    • Marked as answer by adi321 Wednesday, August 3, 2016 8:23 AM
    Wednesday, August 3, 2016 8:22 AM

All replies

  • Hi Adi, 

    Could you please be more specific about “But I only manage to make the replication 1 way only.”?  By definition, in peer-to peer replication the data should replicate between nodes so I wonder maybe you are using transactional replication. Also, since you are using SQL Server 2014 I’m assuming you are using SSMS 2014, in this case, the first link in your post is outdated and you should choose peer-to peer replication when you create new publication, and the steps in second link seems correct.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Wednesday, July 27, 2016 10:03 AM
  • Hi Lin,


    Thank you for the reply.

    I have 2 SQL server, SQLPOC01 and SQLPOC02. And here the steps i have done to setup peer2peer replication

    - Setup Test Db ,

    CREATE DATABASE GlobalSales 
    GO
    
    USE GlobalSales
    
    CREATE TABLE Customers
    (ID nvarchar(10) CONSTRAINT [PK_Customers] PRIMARY KEY,
    FirstName nvarchar(100) NOT NULL,
    LastName nvarchar(100) NOT NULL,
    CountryCode nvarchar(3) NOT NULL,
    CreationDate datetimeoffset CONSTRAINT [DF_Customers_CreationDate] DEFAULT GETDATE()
    )
    GO

    - Setup distribution role on both SQL server

    - Create publication on first server(SQLPOC01) name it PubSales and set publication type to peer-to-peer

    - Choose GlobalSales DB table customer to publish

    - Set Log Reader Agent Security, Choose run under the sq; server agent service account and to connect to publisher i use SA SQL server login.

    -Go to MSSMS on Server1 > Replication > Local Publication > Pub Sales properties > Subscription properties, makesure peer to peer replication set to True

    - Back up GlobalSales database on SQLPOC01

    - On SQLPOC01, insert a row into the Customers table to test if the row will be created later on SQLPOC02 after the topology is set up.

    INSERT INTO Customers (ID, FirstName, LastName, CountryCode) VALUES ('USA-0001', 'Melissa', 'Clinton', 'USA')

    - Restore the GlobalSales DB backup on SQLPOC02

    - On SQLPOC01 set peer to peer topology by going to Replication > Local Publication > Pub Sales > Right click choose configure peer to peer topology, Add a New Peer Node, Choose SQLPOC02 and login using SA account.

    - In the Add New Peer Node dialog box, select the GlobalSales database and select Connect to ALL displayed nodes, assigned the peer ID to 2

    - On the New Peer Initialization page, select “I restored a backup of the original publication database, and the publication database was changed after the backup was taken” and select the backup on SQlpoc01 that was used to initialize the GlobalSales database on sqlpoc02 .

    and the problem is i can only replicate from SQLPOC01 to SQLPOC02 only i cannot replicate from SQLPOC02 to SQLPOC01. If i change the table content on SQLPOC02 the sync status will not show "No replicated transactions are available."

    But when i change the table on SQLPOC01 db the sync status will show "1 transaction(s) with 1 command(s) were delivered." and the replication works the data changes on SQLPOC02 db.

    Do you have any advice what i have overlook or missing ?

    Regards,

    Adi





    Thursday, July 28, 2016 1:52 AM
  • Hi,

    We manage to figure the problem.

    Troubleshooting Steps
    ====================
    1.       We checked the monitoring and found the following error message:
    The process could not execute 'sp_replcmds' on 'MYSERVER1'.
     

    2.       For this error message, it also means the files of the database don’t have an owner. So we assign an owner and the replication works fine.

     Regards,

    Adi

    • Marked as answer by adi321 Wednesday, August 3, 2016 8:23 AM
    Wednesday, August 3, 2016 8:22 AM