SQL Availability Groups, when move database aplication stop working

Question

Hello,

We have a cluster SQL 2017 always on, node1 and node2. We have a database for lansweeper aplication. I put the password for the user with this SQL script:

https://www.lansweeper.com/knowledgebase/login-failed-for-user-lansweeperuser/

When we execute failover, database moves correctly but aplication fails with login error. If i set the password with the same script in the node2, works again.

But if we move again to node1, fail again.

Thanks,
Regards

Answer 1

In addition to the other posts: Microsoft are addressing this issue in SQL 2022 where they have "contained availability groups", so that you can let master and msdb follow the rest of the AG. Certainly an improvement!

Answer 2

I have some doubts.

1 - When i run this query:

select name as [logins], sid, type_desc from sys.server_principals where name='website_user'
select name as [users], sid, type_desc from sys.database_principals where name='website_user'

Sometimes return only login information, no info about user

why does this happen?

2 - I understand that Login SID must the same in both sql servers, but Login SID and User SID must be the same in each server ?

Regards,