Automatic Failover At Always On High Avability Group SQL SERVER

Posted on

Question :

after i read on documentation
https://docs.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 ?

Answer :

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”.

Leave a Reply

Your email address will not be published.