For testing an HA system, what is the "recommended" way to "crash" a server?

[former_SQLA_member1694877]

Hello,

I'd like to test my HA system. What is the "recommended" way to "crash" a server to do so? A dbstop is a "controlled shutdown" and might not be a "real" test. I suppose I can use (under Windows) Task Manager to kill the process but how would I identify which process is the Primary server (for a specific db)?

Thank you.

[Breck_Carter]

The High Availability feature only cares about "availability" which translates to connections between servers.

For example, if the primary server loses connectivity to secondary (mirror) and the arbiter, two things happen: (a) the primary drops all client connections because doesn't have a connection to either of the secondary or mirror, and (b) the arbiter and secondary agree (quorum) that the old primary is unavailable and therefore the secondary becomes the new primary (failover). If the old primary database is restarted, it will become the new secondary... unless CREATE MIRROR ... AS PARTNER ... preferred = 'YES' in which case a second failover will be forced and the old primary/new secondary immediately becomes the new primary again (thus annoying all the users a second time 🙂

The following are equivalent ways of making an HA server unavailable: kick the power cord out of the UPS, run dbstop, End Task in Task Manager, run taskkill, pull the ethernet cable, and so on.

I think Volker's suggestion mirror_role = 'primary' or 'mirror' and ProcessId is the way to identify which is which. Another way is to use Foxhound with connections to each partner directly, or to the primary and secondary, or all four 🙂

[VolkerBarth]

and therefore the secondary becomes the new primary (failover).

One might add that an automatic failover requires synchronous mode or the mirror option "auto_failover" = On.

[former_SQLA_member1694877]

Thanks to both Breck & Volker for helpful hints!

[VolkerBarth]

Are you running the 3 HA servers on different hardware? (Then dropping network cables might be quite realistic). Otherwise, besides knowing which server is which by using a fitting server name, you can query the relevant properties of a running server instance via, say

select property('ServerName'), property('TcpIpAddresses'), property('ProcessId'), db_property('MirrorRole')

The process id might be helpful to kill the process via OS tools.

Note, dbstop can be used with -y to drop current connections.

[former_SQLA_member1694877]

Hi Volker,

Thanks for the hint about select ... property('ProcessId') ... That's exactly what would be needed in a Ubuntu environment! That's the answer! (Once again, I'm missing something about this forum. I apparently can mark my (this) own comment as the answer but I can't do so with your comment which is the actual statement of the answer!)

[VolkerBarth]

Once again, I'm missing something about this forum...

No, you're not missing anything... - When I'm not sure whether I have a "real answer" or just some hints, I usually respond with a comment, as in this case. When you tell that you do consider that an answer, feel free to tell so (just as you have done), and others or me can easily turn the comment into an answer... - and you might be able to do as well once you got more reputation points...