question

HafeezUddin-8965 avatar image
0 Votes"
HafeezUddin-8965 asked HafeezUddin-8965 answered

SQL Server 2017 Peer to Peer Transactional Replication

We are using Peer to Peer (P2P) replication on SQL 2012.
Setup is with 3 nodes NodeA, NodeB and NodeC ( on prem )

We isolation the users by geo location and we are using P2P replication as DR/HA and to improve the performance.

I am planning to upgrade them to SQL Server 2017 or SQL 2019 but planning to add one more node to expand the SQL Server to Azure.

Adding additional node will add extra replication activity on each node because each node have to talk to other three nodes in four node P2P and
I believe we can't control the flow of replication from one to another in P2P setup unless there is any change in SQL 2017 or SQL 2019.

Is it possible setup P2P replication between three nodes (NodeA, NodeB and NodeC ) and
create separate two node P2P replication between to nodes ( NodeC and NodeD (Azure) ) ?
In this model NodeA and NodeB will not get the data if something goes wrong to NodeC ( I am OK with it).

Second question is.
Is it possible to create each P2P Peer node on a cluster within each datacenter for local HA while using the P2P replication using multiple datacenters?

Thanks,


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @HafeezUddin-8965,

Welcome to Microsoft Q&A!

Is it possible setup P2P replication between three nodes (NodeA, NodeB and NodeC ) and create separate two node P2P replication between to nodes ( NodeC and NodeD (Azure) ) ?

Peer-to-peer replication propagates transactionally consistent changes in near real-time between multiple server instances. Please refer to Topologies That Have Three or More Participating Databases to get more information.
In Azure SQL, peer-to-peer transactional replication and merge replication are not supported, so you could not create separate two nodes between on-premise SQL Server and Azure SQL. Please refer to this link.


I

s it possible to create each P2P Peer node on a cluster within each datacenter for local HA while using the P2P replication using multiple datacenters?

Replication can be configured with Always On availability groups, but placing the distribution database into an availability group is supported with SQL 2016 and greater, except for distribution databases used in merge, bidirectional, or peer-to-peer replication topologies. Please refer to AlwaysOn Failover Cluster Instance to get more details.


Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HafeezUddin-8965 avatar image
0 Votes"
HafeezUddin-8965 answered HafeezUddin-8965 edited

Thanks @CarrinWu-MSFT for the response.

I have read the links for three and four node cluster and I am already using the P2P replication with three nodes.

Thanks @CarrinWu-MSFT ,
I am interested to use regular Enterprise Edition SQL in Azure ( using VM) not Azure SQL ( I know Azure SQL doesn't support P2P ).
So Is it possible to setup the Peer to Peer replication model similar to the following.

100953-image.png



image.png (37.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @HafeezUddin-8965, I edited my answer, please check it.


If the answer is helpful, please click "Accept Answer" and upvote it.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @HafeezUddin-8965,

Sorry for the late reply. As your illustration, I made a test for it.

  1. Created a peer to peer replication with Node1 & Node2 & Node3

  2. Then I try to create a new peer to peer replication with Node3 & Node4, I cannot replicate the tables because I selected before. In addition, I can selected the other objects that I didn't selected before:

101198-1.png
101255-2.png


Actually I didn't find any document about this before, so I made the test. I think using T-SQL and SSMS will get same result. And it cannot be a Publishers and Subscribers using same instance. Why do you try other ways? Such as log shipping, it allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



1.png (63.8 KiB)
2.png (24.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HafeezUddin-8965 avatar image
0 Votes"
HafeezUddin-8965 answered CarrinWu-MSFT commented

Thanks for trying and based on your test it is NOT possible.
I am doing my test as well and let you know but I think can be accomplished using TSQL code.
If it is doable using TSQL, I want to know if Microsoft supports this approach or NOT.
Thanks,

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @HafeezUddin-8965, I edited my anwser. You could check it. If the anwser is helpful, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
HafeezUddin-8965 avatar image
0 Votes"
HafeezUddin-8965 answered

In Peer to Peer replication, we can read/write from all nodes whereas if I use logshipping other options, I may be able to read but not write.
Btw, why I was thinking using TSQL over UI because when you use UI it will pull the nodes during setup ( if P2P already configured) whereas if you use TSQL you will have the control adding the subscriber/publisher.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.