March 4, 2014 at 4:25 am
P2P replication provides the ability to automatically store details of conflicts. It will also automatically tie-break a conflict based in the internal Id number of the servers involved with the conflict.
My understanding of this Id number is the first server to be defined as a P2P node has Id 1, the second has Id 2, etc. Therefore the Id number is not based on server name.
One way around the problem of conflicts is to designate one node as a 'master write' server and the remaining nodes as read-only. In this situation all writes take place on a single server and conflicts are designed out of the system. The downside is that your master write server needs to be capable of handling your total write workload.
We do this at my place, and use DNS vanity names to identify the master and passive servers. We regularly rotate the DNS names around the servers as part of our DR process, so we have the assurance that we can continue operating if a given P2P node fails.
Ultimately all transactional-type replication is on the way out in SQL Server. All the recent investment has been in improvements to DB mirroring, and SQL2014 allows up to 7 read-capable secondary copies of your database. We are aiming to replace our P2P process with mirroring later this year.
Always-On mirroring can be a good alternative to P2P, providing the source server can handle the write workload and the mirroring can handle the distribution workload. However, if you have transactional replication from an Enterprise Edition publisher to a Standard Edition subscriber, database mirroring is only useful if you can cope with the costs of upgrading everything to EE.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply