I had beforehand requested a query about Orphaned Customers right here and was directed to this hyperlink in an effort to repair it, however this did not appear to reply my questions, so I’m re-asking after some additional overview of my system.
We discovered that a number of database customers on our secondary AG node had been orphaned and set as
SQL Person With out Login, so we had been unable to carry out a profitable failover. The one repair was to run
ALTER USER 'consumer' with login = 'consumer'; after failover, which mapped the Database login with the SQL login on the 2nd node and allowed the app to attach. Sadly, this eliminated the configuration from the customers on the first node and set them to
SQL Person With out Login. We clearly don’t desire this, as a result of it means guide intervention and extra downtime in an automated failover.
My questions are:
- Why does an
ALTER USERassertion on one node have an effect on the opposite if invoked on that first node?
- Is there a setting inside the Availability Group properties that must be checked off/added that may very well be lacking in my setup?
- There was a suggestion in one other weblog that acknowledged to drop the consumer, and re-create with the SID of the SQL Person. How is that this completely different than working an
ALTER USERassertion, because the SID adjustments on the DB consumer to match with the SQL Person?