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 ?
1 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.
answered Jun 30, 2017 at 20:43
-
Hey Sean, in the first part of your answer you mention that logins don't need to be replicated between the nodes. Then in your last part you mention that logins do need to be replicated. This is likely going to be confusing for others reading your answer. HTH. Jun 30, 2017 at 20:55
-
@SQLRockstar Open to suggestions (feel free to edit my answer) as the quoted first part asks about FCIs and the second part about AGs. Specifically denote that for each? Jun 30, 2017 at 21:26
-
Hi Sean, thanks for the reply. In case of AG what if my application was using specific Instance name ? Do i have to name the AG Listener same as that instance name in this case ?And I am still not clear about that last part of question which is, what we need to take care of regarding logins and other objects ? Can you elaborate little more please.– DanishJun 30, 2017 at 21:52
-
@Danish You almost always want to use the listener name, this abstracts the server and instance names of the real targets. Since AGs operate at the databases level, they won't replicate anything at the instance level. Logins live in the master database which cannot be added to an AG... thus anything server level needs to be replicated manually. Jul 1, 2017 at 1:07
-
@Sean Thanks for the response, so in this case shall i consider that i have to create/import exactly same logins in all the systems, either SQL or Domain. Correct ? Any other specific objects you would recommend to manually replicated ?– DanishJul 1, 2017 at 9:00