0

I have a scenario where SQL Server 2012 - Always-on feature configured.The primary and secondary are synchronous for failover purposes. I understand that we will have node 1 (PRIMARY) and node 2 . Also I know during fail over node 2 becomes PRIMARY.

My question is, (synchronous commit setup)

  1. When application send INSERT or any SQL transactions, which node first the execution happens? Is it on PRIMARY ?
  2. When node 1 fails then node 2 becomes PRIMARY, but who will make node 1 up? do I need to setup alerts? if node 1 dies then how HA and DR works?
  3. What is difference between active-passive or active-active?

Where I can find detailed information on always-on architecture? I have read many article but none explains the internals.

0

1) From Overview of AlwaysOn Availability Groups (SQL Server)

The primary replica sends transaction log records of each primary database to every secondary database. Every secondary replica caches the transaction log records (hardens the log) and then applies them to its corresponding secondary database.

So, primary first, then secondary.

2) Are you asking how node 1 is brought back on line? That's what your operations people and DBAs are paid their huge salaries to do.

Alerts would be a good idea. Maybe even a very good idea.

If node 1 dies AlwaysOn will re-direct connects to node 2, transparently to the application. In-flight work may receive an error. New connections will be routed to node 2. This is the HA and DR in action. When node 1 comes back you can continue running on node 2 as primary and have node 1 as secondary, or organise a managed failback at a time of your choice. If node 2 dies while node 1 is still down, then you have a problem.

3) Active-passive has the secondary as "warm": it is ready to become the primary when required but cannot do any application work until then. Active-active has a "hot" secondary: it can process read-only transaction from the application, and be used for backups and such like. IIRC one license is needed for active-passive and two for active-active.

  • perfect explanation. Thanks. "If node 2 dies while node 1 is still down, then you have a problem." - can we automate it through script to solve this problem or Is it DBA's job? – kudlatiger Mar 11 '16 at 11:34
  • If depends on why node 1 died. If it was a glitch in the OS then an automated reboot would be worth a try. If that data centre is on fire, then, no, I don't think a script would help. – Michael Green Mar 11 '16 at 11:37
  • Michael, can you help here too? dba.stackexchange.com/questions/131953/… – kudlatiger Mar 11 '16 at 11:51

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.