ToB企服应用市场:ToB评测及商务社交产业平台

标题: SQL Server实例间同步登录用户 [打印本页]

---

作者: 宝塔山    时间: 2023-9-12 20:46
标题: SQL Server实例间同步登录用户
SQL Server实例间同步登录用户

问题痛点：由于AlwaysOn和数据库镜像无法同步数据库外实例对象，例如 登录用户、作业、链接服务器等，导致主库切换之后，应用连接不上数据库或者作业不存在导致每晚跑批任务漏跑等
目前来看，作业等其他实例对象的同步还比较难实现，比如作业分为很多步骤，而且作业包含的命令也比较复杂，作业也支持调用其他子系统，比如 PowerShell ，ActiveX，CmdExec等数据库外部程序和命令，用动态SQL方式很难处理
本文主要介绍的是登录用户的同步，毕竟登录用户的重要性还是比较高的，应用需要先通过登录用户登录DB实例才能执行后续的操作
 
要在SQLServer实例间同步登录用户，主要有几种方法
1、创建操作系统域用户，然后创建基于这个域用户的登录用户，因为域用户在域里面是同步的，但是这种方法前提是需要有域环境，而且普通开发人员一般也没有域控机器权限创建域用户
2、使用外部第三方工具，比如 sqlcmd，PowerShell
3、使用链接服务器 和 动态拼接SQL方法
 
本文主要使用第三种方法，因为第三种方法本人认为有下面几种优势
1、保证最低维护成本，纯SQL实现，不需要借助第三方工具
2、通用性，几乎所有SQL Server版本都能用，也不需要像第三方工具例如 PowerShell那样有时候需要升级版本
3、兼容性，跨操作系统平台Linux、Windows
4、高可靠性，使用SQLServer自带原生工具，足够简单高效
 
 
这个工具脚本的主要流程如下
 
 
具体使用步骤
假设有三个AlwaysOn节点，分别是
node1 ip：192.168.10.10
node2 ip：192.168.10.11
node3 ip：192.168.10.12
step1： 创建链接服务器，在所有AlwaysOn节点上创建其他节点的链接服务器，比如在192.168.10.10上创建其他节点链接服务器，下面脚本在192.168.10.10服务器上执行，其他节点以此类推

1. --create  linkedserver
2. USE [master]
3. GO
5. DECLARE @IP NVARCHAR(MAX)
6. DECLARE @Login NVARCHAR(MAX)
7. DECLARE @PWD NVARCHAR(MAX)
9. SET @Login = N'sa' --★Do
10. SET @PWD = N'xxxxxx'  --★Do
11. SET  @IP ='192.168.10.11,1433'    --★Do
14. EXEC master.dbo.sp_addlinkedserver @server = @IP,@srvproduct = N'SQL Server'
16. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation compatible', @optvalue = N'false'
17. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'data access', @optvalue = N'true'
18. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'dist',@optvalue = N'false'
19. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'pub',@optvalue = N'false'
20. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc',@optvalue = N'true'
21. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc out',@optvalue = N'true'
22. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'sub',@optvalue = N'false'
23. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'0'
24. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation name', @optvalue = NULL
25. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'lazy schema validation', @optvalue = N'false'
26. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'0'
27. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'use remote collation', @optvalue = N'true'
28. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'120'
29. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'120'
30. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'remote proc transaction promotion',@optvalue = N'true'
32. USE [master]
33. EXEC master.dbo.sp_addlinkedsrvlogin
34. @rmtsrvname = @IP,
35. @locallogin = NULL,
36. @useself = N'False',
37. @rmtuser = @Login,
38. @rmtpassword = @PWD
42. ---------------------------------------------------------------------------------------------------------------------------
44. --create  linkedserver
45. USE [master]
46. GO
48. DECLARE @IP NVARCHAR(MAX)
49. DECLARE @Login NVARCHAR(MAX)
50. DECLARE @PWD NVARCHAR(MAX)
52. SET @Login = N'sa' --★Do
53. SET @PWD = N'xxxxxx'  --★Do
54. SET  @IP ='192.168.10.12,1433'    --★Do
57. EXEC master.dbo.sp_addlinkedserver @server = @IP,@srvproduct = N'SQL Server'
59. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation compatible', @optvalue = N'false'
60. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'data access', @optvalue = N'true'
61. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'dist',@optvalue = N'false'
62. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'pub',@optvalue = N'false'
63. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc',@optvalue = N'true'
64. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc out',@optvalue = N'true'
65. EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'sub',@optvalue = N'false'
66. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'0'
67. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation name', @optvalue = NULL
68. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'lazy schema validation', @optvalue = N'false'
69. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'0'
70. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'use remote collation', @optvalue = N'true'
71. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'120'
72. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'120'
73. EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'remote proc transaction promotion',@optvalue = N'true'
75. USE [master]
76. EXEC master.dbo.sp_addlinkedsrvlogin
77. @rmtsrvname = @IP,
78. @locallogin = NULL,
79. @useself = N'False',
80. @rmtuser = @Login,
81. @rmtpassword = @PWD

复制代码

 
step2： 创建存储过程，在所有AlwaysOn节点上创建存储过程，记住是所有AlwaysOn节点都要执行
[code]USE [master]GO-- =================================================================-- Author:        -- Create date: -- Description:    -- =================================================================create  PROCEDURE [dbo].[usp_SyncLoginUserRegularBetweenInstances]ASBEGIN      IF EXISTS(SELECT  1   FROM    sys.dm_hadr_availability_replica_states hars               INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id              INNER JOIN sys.availability_replicas ar ON ar.replica_id = hars.replica_id      WHERE   [hars].[is_local] = 1 AND [hars].[role_desc] = 'PRIMARY'AND [hars].[operational_state_desc] = 'ONLINE'              AND [hars].[synchronization_health_desc] = 'HEALTHY')      BEGIN               ----Check for prerequisite, if not present deploy it.               IF NOT EXISTS (SELECT  id  FROM  [master].[dbo].[sysobjects] where name='sp_hexadecimal' and xtype='P')                 BEGIN                     DECLARE @sp_hexadecimalcreatescript NVARCHAR(3000)                     SET @sp_hexadecimalcreatescript =  N'                  CREATE PROCEDURE [dbo].[sp_hexadecimal]                      @binvalue VARBINARY(256) ,                      @hexvalue VARCHAR(514) OUTPUT                  AS                      DECLARE @charvalue VARCHAR(514);                      DECLARE @i INT;                      DECLARE @length INT;                      DECLARE @hexstring CHAR(16);                      SELECT @charvalue = ''0x'';                      SELECT @i = 1;                      SELECT @length = DATALENGTH(@binvalue);                      SELECT @hexstring = ''0123456789ABCDEF'';                      WHILE ( @i

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4