I configured Always on basic availability groups on my SQL Server 2019 Standard instances. One as our production machine and the second as a DR machine.
I have primary set to synchronous commit and DR set to asynchronous commit. It seems to be working perfectly and failover works without any problems until I tested what would happen should our primary server completely fail and become unavailable.
To simulate this I turned off the SQL Server service on the primary and then did a forced manual failover to secondary. Secondary came online without any problems and all the recent changes were available (lucky for me - no data loss) but then when I started up the SQL Server service again on the original primary, the AG went into primary state while the secondary replica was also still in primary.
Now when I try doing a failover, it fails saying the database is already a primary on the other side. I tried this from both Prod and DR with no luck either side. Luckily for now I was just using a test database but we would like to implement Always on to replace the current log shipping we have in place but now with this happening I am scared of ending up in such a situation with a production DB.
Was I doing the failover incorrectly or is there some startup switches I am missing and how do I get the 2 replicas to act normal again (1 primary 1 secondary). I truly hope someone else has had this issue and managed to get it resolved as recovery from DR after failover with log shipping is a complete nightmare plus our data loss is always going to be high.
We have tried mirroring but ended up dropping it as the performance impact was very high plus we had a few instances where it did a DB failover without any problems on the primary database so mirroring is quite scary plus MS has deprecated it since SQL Server 2012.