Questions about Always On

• onlo

  SSCertifiable

  Points: 5080

  More actions

  April 10, 2021 at 9:07 am

  #3867533

  Dear all,

  I have the following questions about SQL Server Always On:

  1. How does SQL Server copy data changes from the primary node to the secondary nodes?

  Through log shipping (backup & restore transaction log)?

  Or bitwise copy (copy all the changes in bit level)?

  It seemed Microsoft hasn't tell us.

  Many people ask me it but I can't give them a clear answer.

  2. If I have 3 nodes: 1 primary node C01 and 2 secondary nodes: C02, C03, can I tell Always On : if C01 down, please fail over to C02 unless C02 is not available, can I?

  I can't find any parameter in Always On can do that.

  Any idea?

   

• e4d4

  SSCertifiable

  Points: 5777

  More actions

  April 10, 2021 at 10:36 am

  #3867556

  Hi,

  1. Log records are sent to other replicas and replicas are responsible for performing modification to data. https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15 "The primary replica sends transaction log records of each primary database to every secondary database."
  2. No you can't change you can only tell which replica will be the next primary in case of failover, in theory you could change order of prefered owners on cluster, but SQL will reset it after failover and it's not recomended https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-2016-alwayson-availability-group-enhancements/ba-p/318782

• onlo

  SSCertifiable

  Points: 5080

  More actions

  April 12, 2021 at 2:50 am

  #3867951

  Thanks a lot.

  The information you provided is very useful.