6 improved grammar
source | link

Is this of concern? Will a failoverfail-over work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern with fail-over as the fail-over basically targets user database based on database mirroring endpoint, and CONNECT permissions on the endpoint requireddue to user defined LOGIN or SERVICE ACCOUNT (not for server principals named like ##%). following facts:

  • The fail-over basically targets user databases based on database mirroring endpoint
  • CONNECT permissions on the ENDPOINT required to user defined LOGIN or SERVICE ACCOUNT (not for server principals named like ##%).

As you mentioned, fundamentally, the SID is the correlation between Login in server level and User in a database level, since. Since the server level metadata not included partsync is out of database-mirroring (Availability Group) scope in availability groups, we must maintain Server Level login's SID same in all the replicas (SQL Instances), so that it would match with User SID in replicatedsecondary copy of the database in secondary replica.

When fail-over happens there is no failure of application connectivity as the login SID = user SID, eventually application gets the same permissions on the database. Otherwise users in database become Orphaned

Following are some points that indicates why we do not have to concern on server principals that are named '##%':

  1. These are created during SQL Server installation, we do not have control over these to manage SID
  2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
  3. There is no issue with user DB connection, since any application usually doesn't have any relation with the principals that are named like '##%', and the principals (##%) not existed in user database.
  4. Finally, our aim is to match Server principal's SID = database principal's SID within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:
select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly stated here.., in this case contained database feature would be a solution to persists the authentication with database itself.

Not only logins, but also there are server level metadata aspects that need to be manged outside the availability group as required.

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern with fail-over as the fail-over basically targets user database based on database mirroring endpoint, and CONNECT permissions on the endpoint required to user defined LOGIN or SERVICE ACCOUNT (not for server principals named like ##%).

As you mentioned, fundamentally, the SID is the correlation between Login in server level and User in a database level, since the server level metadata not included part of database-mirroring (Availability Group) scope, we must maintain Server Level login's SID same in all the replicas, so that it would match with User SID in replicated copy of the database in secondary replica.

When fail-over happens there is no failure of application connectivity as the login SID = user SID, eventually application gets the same permissions on the database. Otherwise users in database become Orphaned

Following are some points that indicates why we do not have to concern on server principals that are named '##%':

  1. These are created during SQL Server installation, we do not have control over these to manage SID
  2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
  3. There is no issue with user DB connection, since any application usually doesn't have any relation with the principals that are named like '##%', and the principals (##%) not existed in user database.
  4. Finally, our aim is to match Server principal's SID = database principal's SID within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:
select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly stated here.., in this case contained database feature would be a solution to persists the authentication with database itself.

Not only logins, but also there are server level metadata aspects that need to be manged outside the availability group as required.

Is this of concern? Will a fail-over work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern with fail-over due to following facts:

  • The fail-over basically targets user databases based on database mirroring endpoint
  • CONNECT permissions on the ENDPOINT required to user defined LOGIN or SERVICE ACCOUNT (not for server principals named like ##%).

As you mentioned, fundamentally, the SID is the correlation between Login and User in a database level. Since the server level metadata sync is out of scope in availability groups, we must maintain Server Level login's SID same in all the replicas (SQL Instances), so that it would match with User SID in secondary copy of the database.

When fail-over happens there is no failure of application connectivity as the login SID = user SID, eventually application gets the same permissions on the database. Otherwise users in database become Orphaned

Following are some points that indicates why we do not have to concern on server principals that are named '##%':

  1. These are created during SQL Server installation, we do not have control over these to manage SID
  2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
  3. There is no issue with user DB connection, since any application usually doesn't have any relation with the principals that are named like '##%', and the principals (##%) not existed in user database.
  4. Finally, our aim is to match Server principal's SID = database principal's SID within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:
select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly stated here.., in this case contained database feature would be a solution to persists the authentication with database itself.

Not only logins, but also there are server level metadata aspects that need to be manged outside the availability group as required.

5 Improved formatting
source | link

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern whilewith fail-over happens withas the fail-over basically targets user database based on database mirroring endpoint, and CONNECT permissions on the endpoint required to user defined LOGIN or SERVICE ACCOUNT (not for server principals named like ##%).   

As you mentioned, fundamentally, the SID is the correlation between Login in server level and User in a database level, whilesince the server level metadata not included part of database-mirroring (Availability Group) scope is not server level (it's limitation in AG), we must maintain Server Level login's SID same in all the replicas, so that it would match with User SID in replicated copy of the database in secondary replica. 

When fail-over happens there is no failure of application connectivity as the login sid SID = user sid SID, eventually application gets the same permissions on the database. otherwiseOtherwise users in database become Orphaned

WhyFollowing are some points that indicates why we do not have to concern on server principals that are named '##%':

  1. These are created during SQL Server installation, we do not have control over these to manage SID
  2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
  3. There is no issue inwith user DB connection, since any application usually required user database connectivity and user databases doesn't have any relation with the principals that are named like '##%'  , and the principals (##%) not existed in user database.
  4. Finally, our aim is to match Server principal's sidSID = database principal's sidSID within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:
select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'
 

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly statesstated here.., in this case you may utilize contained databasecontained database feature which is an abilitywould be a solution to allow connections onpersists the authentication with database user without having associated loginitself.

Not only logins, but also there are thingsserver level metadata aspects that need to be manged outside the availability group upon the requirementas required.

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern while fail-over happens with user database.  

As you mentioned, fundamentally, the SID is the correlation between Login and User in a database, while the database-mirroring (Availability Group) scope is not server level (it's limitation in AG), we must maintain Server Level login's SID same in all the replicas, so that it would match with User SID in replicated copy of the database in secondary replica. When fail-over happens there is no failure of application connectivity as the login sid = user sid, eventually application gets the same permissions on the database. otherwise users in database become Orphaned

Why we do not have to concern on server principals that are named '##%':

  1. These are created during SQL Server installation, we do not have control over these to manage SID
  2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
  3. There is no issue in user DB connection, since any application usually required user database connectivity and user databases doesn't have any relation with the principals that are named like '##%'  
  4. Finally, our aim is to match Server principal's sid = database principal's sid within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:
select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly states, in this case you may utilize contained database feature which is an ability to allow connections on database user without having associated login

Not only logins, but also there are things that need to be manged outside the availability group upon the requirement.

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern with fail-over as the fail-over basically targets user database based on database mirroring endpoint, and CONNECT permissions on the endpoint required to user defined LOGIN or SERVICE ACCOUNT (not for server principals named like ##%).  

As you mentioned, fundamentally, the SID is the correlation between Login in server level and User in a database level, since the server level metadata not included part of database-mirroring (Availability Group) scope, we must maintain Server Level login's SID same in all the replicas, so that it would match with User SID in replicated copy of the database in secondary replica. 

When fail-over happens there is no failure of application connectivity as the login SID = user SID, eventually application gets the same permissions on the database. Otherwise users in database become Orphaned

Following are some points that indicates why we do not have to concern on server principals that are named '##%':

  1. These are created during SQL Server installation, we do not have control over these to manage SID
  2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
  3. There is no issue with user DB connection, since any application usually doesn't have any relation with the principals that are named like '##%', and the principals (##%) not existed in user database.
  4. Finally, our aim is to match Server principal's SID = database principal's SID within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:
select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'
 

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly stated here.., in this case contained database feature would be a solution to persists the authentication with database itself.

Not only logins, but also there are server level metadata aspects that need to be manged outside the availability group as required.

4 additional reference added
source | link

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern while fail-over happens with user database.

As you mentioned, fundamentally, the SID is the correlation between Login and User in a database, while the database-mirroring (Availability Group) scope is not server level (it's limitation in AG), we must maintain Server Level login's SID same in all the replicas, so that it would match with User SID in replicated copy of the database in secondary replica. When fail-over happens there is no failure of application connectivity as the login sid = user sid, eventually application gets the same permissions on the database. otherwise users in database become Orphaned

Why we do not have to concern on server principals that are named '##%':

  1. These are created during SQL Server installation, we do not have control over these to manage SID
  2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
  3. There is no issue in user DB connection, since any application usually required user database connectivity and user databases doesn't have any relation with the principals that are named like '##%'
  4. Finally, our aim is to match Server principal's sid = database principal's sid within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:
select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly statesstates, in this case you may utilize contained database feature which is an ability to allow connections on database user without having associated login

Not only logins, but also there are things that need to be manged outside the availability group upon the requirement.

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern while fail-over happens with user database.

As you mentioned, fundamentally, the SID is the correlation between Login and User in a database, while the database-mirroring (Availability Group) scope is not server level (it's limitation in AG), we must maintain Server Level login's SID same in all the replicas, so that it would match with User SID in replicated copy of the database in secondary replica. When fail-over happens there is no failure of application connectivity as the login sid = user sid, eventually application gets the same permissions on the database. otherwise users in database become Orphaned

Why we do not have to concern on server principals that are named '##%':

  1. These are created during SQL Server installation, we do not have control over these to manage SID
  2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
  3. There is no issue in user DB connection, since any application usually required user database connectivity and user databases doesn't have any relation with the principals that are named like '##%'
  4. Finally, our aim is to match Server principal's sid = database principal's sid within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:
select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly states, in this case you may utilize contained database feature which is an ability to allow connections on database user without having associated login

Not only logins, but also there are things that need to be manged outside the availability group upon the requirement.

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern while fail-over happens with user database.

As you mentioned, fundamentally, the SID is the correlation between Login and User in a database, while the database-mirroring (Availability Group) scope is not server level (it's limitation in AG), we must maintain Server Level login's SID same in all the replicas, so that it would match with User SID in replicated copy of the database in secondary replica. When fail-over happens there is no failure of application connectivity as the login sid = user sid, eventually application gets the same permissions on the database. otherwise users in database become Orphaned

Why we do not have to concern on server principals that are named '##%':

  1. These are created during SQL Server installation, we do not have control over these to manage SID
  2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
  3. There is no issue in user DB connection, since any application usually required user database connectivity and user databases doesn't have any relation with the principals that are named like '##%'
  4. Finally, our aim is to match Server principal's sid = database principal's sid within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:
select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly states, in this case you may utilize contained database feature which is an ability to allow connections on database user without having associated login

Not only logins, but also there are things that need to be manged outside the availability group upon the requirement.

3 additional reference added
source | link
2 grammar correction
source | link
1
source | link