I have requirement in my company that previously we are using HQ database to access application in another Main Branch.Now Requirement is that
1- HQ Server 1 HQ---> Separate SQL Database
2- MAIN BRANCH SERVER 2 ----> Separate SQL Database
Due to performance we are doing distributed database but we need to synced together if any user did change then no conflict will occur in both SQL database . If any of the server will down then all user will routed to Available SQL Server instance.
--> Current Environment SQL Server 2008 R2
--> Expected we Can also move to 2012 or 2016 if it is not possible in SQL 2008 R2 or if it is easy in 2016.
Server 1 -Mirroring(Server 2)
Server 2--Mirroring (Server 4) and in both Server 1 and Server 2 we will do Peer-2-Peer replication.
Please advise best solution with low effort and maintenance.