介绍
SQL Server Always On 可用性组为 SQL 数据库提供高可用性和灾难恢复解决方案。如果主副本出现任何问题，它会自动对辅助副本上的 AG 数据库进行故障转移。只要我们利用侦听器，您的应用程序就不需要对连接字符串进行任何更改。
但是，在 SQL Always On 中，同步可用性组的各个数据库部分。它不同步登录、代理作业、SSIS 包、链接服务器和服务器配置。在本文的这一部分中，我们将探讨主副本与辅助副本之间的登录同步。
登录和用户的快速回顾
- SQL Server 有两个登录名和用户作为安全主体
- 服务器级主体：登录名 – master 数据库有一个登录名并包含一个 SID。您可以查询sys.server_principals来检查登录名及其 SID
它返回 Windows、SQL 登录、服务器角色和证书。每个登录都有一个SID，如下图：
1select name, sid, type_desc from sys.server_principals
-
数据库主体：用户 – 我们将这些登录映射到数据库。数据库用户使用其 SID 与主数据库登录名进行映射
您可以查询sys.database_principals以获取数据库主体列表。如下所示，我们的登录名 [SQLAg2User] 和数据库用户 [SQLAg2user] 具有类似的 SID。
- 服务器级主体：登录名 – master 数据库有一个登录名并包含一个 SID。您可以查询sys.server_principals来检查登录名及其 SID
- Windows 和 SQL 登录
我们可以在 SQL Server 中使用 Windows 或 SQL 身份验证。在 Windows 身份验证中，您从 Windows 活动目录创建用户。它使用活动目录 SID 进行身份验证。这意味着如果您在多个 SQL 实例上创建了 Windows 登录名，这些登录名将具有相同的 SID。
在 SQL 身份验证中，您创建一个带有密码的登录名。它为这些 SQL 登录生成一个 SID。如果您使用相似的名称和密码创建登录，则它具有不同的 SID。您需要在 SQL Server Always On 可用性组的不同实例之间手动同步 SID。您可以使用相同的密码哈希和 SID 在辅助副本上创建登录名。
例如，以下脚本生成一个 CREATE LOGIN 脚本，其中包含登录名 [SQLAG2Use] 的密码哈希和 SID。
|
1
2
3
4
5
6
|
SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+
CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, '+
N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';'
FROM master.sys.server_principals AS sp
INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.name='SQLAG2User'
|
您需要复制此脚本并在辅助副本上执行它，以创建具有与主副本类似的 SID 的登录名。它将 SID 与辅助副本数据库相匹配，并且您的应用程序不会因 AG 故障转移而面临登录问题。
因此，您需要在 SQL Server Always On 可用性组的所有副本中手动同步以下对象。
- 服务器主体（登录）
- 服务器角色、成员身份和对象权限
环境详情
在本文中，我们考虑两个节点 SQL Server Always On 可用性组，其详细信息如下：
- 主副本：SQLNode1\INST1
- 次要副本：SQLNode2\INST1
- 数据库：[MyNewDB]
在主副本上，您创建登录名并分配 [MyNewDB] 可用性组数据库的 db_datareader 权限。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
USE [master]
GO
CREATE LOGIN [Demologin] WITH PASSWORD=N'India@123', DEFAULT_DATABASE=[MyNewDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [MyNewDB]
GO
CREATE USER [Demologin] FOR LOGIN [Demologin]
GO
USE [MyNewDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Demologin]
GO
|
现在，连接到您的辅助副本并查看登录是否有效。它不起作用，因为辅助副本上不存在相应的登录名。
您可以使用以下方法来解决此问题。
手动在辅助副本上创建登录名
在此方法中，您可以在辅助副本上手动创建登录名。
|
1
2
3
4
|
USE [master]
GO
CREATE LOGIN [Demologin] WITH PASSWORD=N'India@123', DEFAULT_DATABASE=[MyNewDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
|
如果我们查询辅助副本的 SQL 登录名和数据库用户，我们会注意到不同的 SID。这是因为数据库 SID 与主副本 SID 相似。但是，我们在生成新 SID 的辅助副本上手动创建了登录名。
由于登录名和用户SID不同，它是一个孤立用户。您可以使用 sp_change_users_login 存储过程来获取孤立用户的列表。
|
1
|
sp_change_users_login 'report'
|
您可以按照如何在 SQL Server 中发现和处理孤立数据库用户 一文来修复数据库中的孤立用户。
或者，您可以在主副本上运行脚本来生成脚本，在辅助副本上执行以避免 SID 差异。
|
1
2
3
4
|
CREATE LOGIN [Demologin]
WITH PASSWORD=0x0200B9AD82CA73B5065CDCA01F3C5B225CA9D0B9D5DE7394F4701575E25CC343D045
45FB8086EC8B356E57385CA9A505C45A2EF903B316544B90459FB7FA24392E5E04023226 HASHED,
SID=0x04162837B648EC4AA174954E9C0960AF;
|
使用存储过程sp_help_revlogin和sp_hexadecimal
Microsoft 提供了存储过程 sp_help_revlogin 将登录名传输到不同的实例。您创建存储过程，在主副本上执行，在辅助副本上复制脚本输出，然后执行它。
- 打开Microsoft 文档并复制脚本以在主副本上创建存储过程 sp_help_revlogin 和 sp_hexadecimal
-
执行存储过程 sp_help_revlogin。它返回带有原始 SID 和密码的登录脚本
- 连接到辅助副本，执行 sp_help_revlogin 的输出（相关登录）。它在辅助副本实例上创建 SQL 登录名
SSIS传输登录任务
您可以在集成服务 (SSIS) 中配置传输登录任务，并将所需的登录名从主副本传输到辅助副本。
您可以参考文章《将 SQL 登录名传输到 AG SSIS 传输登录任务的辅助副本》进行配置。
使用DBATools同步副本之间的登录
DBATools 提供了一组有用的函数和 cmdlet，用于执行 SQL Server 数据库管理任务。您可以浏览DBATools类别来尝试这些有用的 cmdlet。
我们可以利用 DBATools 命令来自动同步登录。它需要以下命令。
- Get-DbaAgReplica：它返回 SQL Server Always On 可用性组副本信息。例如我们可以用它来返回主备副本实例信息
- Get-DbaLogin：我们可以使用 Get-DbaLogin 返回登录名（Windows 和 SQL）、凭据、证书对象。我们将其与Copy-DbaLogin命令结合使用，将登录从源（主）副本迁移到目标（辅助）副本
在下面的 PowerShell 脚本中，我们执行以下任务：
- 使用 Get-DbaAgReplica 命令收集有关 SQL Server Always On 可用性组中的主副本和辅助副本的信息
- $primaryReplica 存储主副本信息
- $secondaryReplicas 存储次要副本信息
- 该脚本在主副本上运行 Get-DbaLogin 命令并将其存储在 $LoginsOnPrimary 变量中
- 对于辅助副本，它执行以下任务
- 首先，它从辅助副本生成登录名并将其存储到 $LoginsOnSecondary 中
- 它比较 $LoginsOnPrimary 和 $LoginsOnSecondary
- 如果发现任何差异，它将特定登录复制到辅助副本上
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
$AGLSN = 'AGUsingPowerShell'
$primaryReplica = Get-DbaAgReplica -SqlInstance $AGLSN | Where Role -eq Primary
$secondaryReplicas = Get-DbaAgReplica -SqlInstance $AGLSN | Where Role -eq Secondary
$LoginsOnPrimary = (Get-DbaLogin -SqlInstance $primaryReplica.Name)
$secondaryReplicas | ForEach-Object {
$LoginsOnSecondary = (Get-DbaLogin -SqlInstance $_.Name)
$diff = $LoginsOnPrimary | Where-Object Name -notin ($LoginsOnSecondary.Name)
if($diff) {
Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Name -Login $diff.Nane
}
}
|
如果主副本和辅助副本上都存在登录名，则会跳过该登录名并在“状态”列中提供信息。
对于此演示，我们创建两个登录名：
-
具有 [dbcreator] 服务器角色和 [db_datareader] 、[dba_datawriter] AG 数据库级别角色的 SQL 登录 [DBALogins1]。它使用以下脚本来创建登录名并分配权限
123456789101112131415161718192021USE [master]GOCREATE LOGIN [DBALogins1] WITH PASSWORD=N'India@123' MUST_CHANGE, DEFAULT_DATABASE=[MyNewDB], CHECK_EXPIRATION=ON, CHECK_POLICY=ONGOALTER SERVER ROLE [dbcreator] ADD MEMBER [DBALogins1]GOuse [MyNewDB];GOuse [master];GOUSE [MyNewDB]GOCREATE USER [DBALogins1] FOR LOGIN [DBALogins1]GOUSE [MyNewDB]GOALTER ROLE [db_datareader] ADD MEMBER [DBALogins1]GOUSE [MyNewDB]GOALTER ROLE [db_datawriter] ADD MEMBER [DBALogins1]GO -
AD 用户 [aduser] 在可用性组数据库上具有服务器角色 [processadmin] 和 [db_owner] 角色
1234567891011121314USE [master]GOCREATE LOGIN [MYDEMOSQL\aduser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]GOALTER SERVER ROLE [processadmin] ADD MEMBER [MYDEMOSQL\aduser]GOUSE [MyNewDB]GOCREATE USER [MYDEMOSQL\aduser] FOR LOGIN [MYDEMOSQL\aduser]GOUSE [MyNewDB]GOALTER ROLE [db_owner] ADD MEMBER [MYDEMOSQL\aduser]GO
现在，使用 DBATools 命令运行 Windows PowerShell 脚本。
如上面突出显示的，它显示最近创建的登录的成功状态，并跳过剩余的登录，因为它们已经存在于辅助副本上。您可以比较两个用户的 SID、登录名及其对主副本和辅助副本的权限。
- 注意：如果您更改主副本上的 SQL 登录密码，则还应在辅助副本上手动修改该密码。
结论
在本文中，我们学习了几种在 SQL Server Always On 可用性组中同步主副本及其相应辅助副本之间登录的方法。您必须保持登录同步，否则您可能会在 AG 故障转移后开始收到用户的投诉。您还可以使用 DBATools PowerShell 命令来自动同步丢失的登录名。