Sql-server – On which node first transaction will be executed in Always-on setup

availability-groupssql server

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.

Best Answer

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.

Related Question