Edited entire question for clarity.
Scenario: Have 2 identical SQL Servers on separate servers, processing transactions from different applications simultaneously while being completely synchronized (example mobile apps server 1 and web apps server 2).
We are going to use Always On to fail over one server to the other for maintenance and vice versa however, when maintenance is complete, both servers will be once again online processing transactions from our various applications and synchronized.
Trying to solve a request from a client to be online, even during maintenance, with 2 synchronized servers, each processing transactions from different applications.
What is the best approach to keep them synchronized? Was leaning towards merge replication.
Current ver. SQL Server 2012 SP3-CU4
Thanks
Best Answer
According to given scenario if both applications use the same set of data (database), AlwaysOn is the best solution.
It gives you fast & almost real-time synchronization at mirror node with instant fail-over in case of any mishappening.
To make a connection with the database you have common IP Address/Listener Name, so you don't need to change Ip Address in case failover happens.
As it allows to use mirror database to perform read operation, you can use that for reporting by redirecting read (SELECT) request to it and take Full & Differential of Mirror database which reduces read overhead from Principal node.
Either your application is OLAP or OLTP, don't worry unlike replication always on doesn't records transaction of articles and perform on partner database. Instead in synchronize complete transaction from buffer of principal database to mirror database.
For periodic maintenance, it's the best solution. Perform your server maintenance activity at mirror node and when it will join cluster after completion, mirror database will get synchronized with the principal.
Merge Replication is useful when two different application manipulates few common tables available in different databases at different servers. For example, in an organization HR and Account both department need Employee table to work on, but both don't want to share their record with each other. So if there is two database HR_DB and Account_DB, we can setup merge replication between both database for Employee table.
Replication records DML operations performed on of every articles which you include in it. it's like trigger on a object for UPDATE, INSERT and DELETE which is created to record each DML request, so if your application is write intensive you may face performance and transaction conflict issues.
You are on the right path if all applications use the same set of data (database), leaning towards Merge Replication is not a good Idea in this Case.
Thanks