Sql-server – SQL Server AlwaysOn FCI vs AG Doubts

availability-groupsclusteringfailoverhigh-availabilitysql server

I have a big confusion between Always On FCI/AG. I know that for FCI you need to have shared disks and in-case of Hardware failure or OS failure or System reboot the instance with db, logins etc will fail-over to another node. Plus instance is constant through the setup. For AG i am not clear when db fail-over occur ? what if hardware fails or OS fails or System reboots ? What instance names i must configure for all nodes ? What about logins we need to take care about ?

Best Answer

I have a big confusion between Always On FCI/AG. I know that for FCI you need to have shared disks and in-case of Hardware failure or OS failure or System reboot the instance with db, logins etc will fail-over to another node.

Shared or replicated disks, yes. This is because the Disks "move" between servers. Each potential failover target has SQL Server installed locally and the service is started when the local node owns the resources and is given the online command. The local service starts and mounts the database files which are on the shared or replicated disks. This is why you don't need to change or replicate any SQL Server based items (logins, jobs, etc.).

For AG i am not clear when db fail-over occur ?

Failover of the Availability Group (not an individual database like Database Mirroring) will happen when the health checks either timeout, fail, or the lease fails to be obtained or renewed. When and under what circumstances is entirely dependent upon the flexible failover policy chosen and the type of issue the instance encounters.

what if hardware fails or OS fails or System reboots ?

In all of those cases it is entirely dependent upon the configuration of the availability group. If you're set for automatic failover (synchronous commit + synchronized databases + automatic failover set) then in all of those situation the availability group should fail over.

What instance names i must configure for all nodes ?

Entirely your choice. To make administration easier, I'd just use default instances.

What about logins we need to take care about ?

Only objects at the database level will be replicated as part of the log stream. System databases cannot currently be put in availability groups. Thus, any server level object or objects not contained in the user databases will need to be replicated. Examples include: Logins, Jobs, Server Level Certificates, Proxies, Server Level Configurations, Linked Server Definitions, Security Audits, etc.