1

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.

enter image description here

Current ver. SQL Server 2012 SP3-CU4

Thanks

  • There is small confusion, "There is a mechanism that will allow an app to be redirected instantly from 1 server to the other" means both databases are completely synchronized and you need high availability of data at server 2 in case of failure of server1. Am I right?? – Rajesh Ranjan Sep 2 '16 at 8:21
  • Thanks for letting me know, edited the question for clarity. – Mike Zalansky Sep 2 '16 at 14:59
1

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

  • Also remember that AlwaysON has just one primary node. – Danilo Braga Sep 2 '16 at 16:02
  • Both technologies have the different purposes to serve. We should choose which fits best in our scenario. – Rajesh Ranjan Sep 2 '16 at 16:12
  • Sure. I actually prefer AG. Last year I configured one with 4 nodes in 2 different data centers. It works smoothly :) – Danilo Braga Sep 2 '16 at 16:18
1

Have you considered Peer-to-peer transactional replication?
You can find out more comparison here.

Peer-to-peer transactional replication: Use When...

  • Replication is used to improve scalability and availability.
  • Minimal latency is required.
  • Data is not partitioned among Subscribers.
  • Conflicts typically do not occur, but they must be detected if they do.

Merge replication: Use When...

  • There are a large number of Subscribers.
  • Data is replicated to mobile users.
  • Replicated data is frequently updated at the Subscriber.
  • Data filtering is needed so that Subscribers receive different partitions of data.
  • 1
    Also remember that P2P replication is Enterprise only, while Merge works in lower editions. – spaghettidba Sep 1 '16 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.