SQL Server 2022: AlwaysOn AG databases can not sync after applying CU6 or higher
The setup is: OS - Win Server 2022 & MSSQL - SQL Server 2022 AlwaysOn Availability Groups are enabled on two (primary + secondary) replicas and use WSFC. Several availability groups are created and a database in each of them. AGs are configured with Sync Commit and Automatic failover mode, also they use dedicated separate VLAN for data sync. The issue we noticed is that after applying CU6 or higher ones , in case of we restart any of two replica hosts, on the secondary replica not all databases switch back to Synchronized state, part of them remain in Not Synchronizing state . Any ideas what could be the reason of that behavior change? Had someone similar issue ?Creating a SQL Login with SQL Server Availability Groups
The issue The logins used in the databases included in an AG must share the same SID if you have SQL Server AlwaysOn, availability groups. How come? Let's investigate further... A row is added to the [syslogins] table in the [master] system database each time a user is created. Now, when you map a user to a database and begin adding roles to that database for the specific user principal, a row with the pertinent data is added to the [sysusers] user database table. The [syslogins] and [sysusers] tables are connected based on SID information. Tada!. You can query sys.database_principals to get a list of database principals. As shown below, we have similar SID’s for a login [SQLAg2User] and database user [SQLAg2user]. Environment information The SQL Server Always On Availability Groups for these two nodes have the following characteristics for the purposes of this article: Primary Replica: SQLNode1\INST1 Secondary Replica: SQLNode2\INST1 Database: [MyNewDB] The Solution Enters T-SQL: You create the login on the primary replica node with the following script or something like that, that fits your needs: -- Connect to the PRIMARY REPLICA node of the AG USE [master] GO -- Create the login CREATE LOGIN user1 WITH PASSWORD = N'aStrongPasswordHere'; If everything runs successfully you run the next to get the SID of the user: USE [master] GO -- Get the SID of the user createdSELECT [sid], [name] FROM [syslogins] WHERE [name] = 'user1'; GO Now, that you have the SID, switch the connection the secondary replica and execute the following query: -- Connect to the **SECONDARY** REPLICA node of the AG USE [master] GO CREATE LOGIN user1 WITH PASSWORD = N'useTheSamePassword', sid=<copy-paste the SID from above>; Now, you are ready to go back to your Primary Replica that has the databases you want to grant the rights for the user and do so freely. The next time your database fails over to the other node it will work without a problem. Just a side note: why Windows Authentication mode does not have this SID problem? Well, if you think about it, the SID is governed by one source: the Windows Active Directory.
Secondary Databases Not Recovering
We had a fail over last night due to Windows patches being applied. Most databases were fine but we had a few that were stuck in Initializing / In Recovery. From the logs I see the following for the databases in question. -Availability database 'dbname', which is in the secondary role, is being restarted to resynchronize with the current primary database. This is an informational message only. No user action is required. -Nonqualified transactions are being rolled back in database Administrator for an Always On Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required. -State information for database 'dbname' - Hardened Lsn: '(20363:176333:1)' Commit LSN: '(20363:176332:1)' Commit Time: 'Apr 8 2020 7:42PM' -Always On Availability Groups connection with primary database established for secondary database 'dbname' on the availability replica 'nodename' with Replica ID: {...}. This is an informational message only. No user action is required. -6 transactions rolled forward in database 'dbname' (5:0). This is an informational message only. No user action is required. -Always On Availability Groups connection with primary database established for secondary database 'dbname' on the availability replica 'nodename' with Replica ID: {...}. This is an informational message only. No user action is required. Those logs lead me to believe the recovery process completed but the databases never actually recovered, syncing did not start back up. What else can I look at to troubleshoot this issue? Is this a bug? We would like to figure out the root cause so this does not occur again. SQL Server version 13.0.5598.27
