0

after i read on documentation https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server?view=sql-server-ver15

i still confused about part protection level

  • if nodes within an FCI protection level = Instance & Failed-over resources = Server, instance, and database
  • if replica within an availability group protection level = Database & Failed-over resources=Database only

this mean if i choose or setup replica within an availability group if primary instance is down automatic failover is not running ?

Improve this question

1 Answer 1

Reset to default
1

That's not what it means.

With a FCI, the protection is done at the service level. It means that if the primary node have an issue, the service will restart on the other node. As all node share the same storage, you end up with the exact same "instance" after a failover (same jobs, same logins, same DBs, etc..) but running from a different server.

In AG, the services is up and running on both node. The protection is set on at the database level (you can have some DB on the primary node that are in AG and some other that aren't for example). Each nodes also have their own storage.

If you are in sync mode and have the failover set to "automatic", then if the primary node of you AG fail, all DBs that are in your AG will failover to the other node.

P.s. The system databases are not part of the AG so you need to be carefull with everything releated to those (ex: Jobs, logins, etc) as they won't "failover".

Improve this answer
3
  • thanks for advice, i still confused because on tutorial AG and listener setup on primary node also. my perception if primary node is fail (example i shutdown the server) listener and AG can't failover to secondary node
    – febry
    Aug 6, 2020 at 3:02
  • I'm not sure to understand what you said... The listener is attached to the AG. It's redirecting the SQL connection to the primary node (for this specific AG). Availability groups are based on the Windows Cluster feature. If you shut down your primary node (and if your secondary node get the Quorum majority - Meaning that with only 2 nodes, you will probably want to have a file share withness), the Cluster will "understand" that your primary node failed. SQL will then be able to failover the AG (which will cause the listener to redirect the connection to the new primary node)
    – Dominique Boucher
    Aug 6, 2020 at 12:20
  • ok i understand after i read some tutorial. on windows clustering must be have withness disk or file share. thanks
    – febry
    Aug 7, 2020 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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