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服务器上执行，其他节点以此类推- --create linkedserver
- USE [master]
- GO
- DECLARE @IP NVARCHAR(MAX)
- DECLARE @Login NVARCHAR(MAX)
- DECLARE @PWD NVARCHAR(MAX)
- SET @Login = N'sa' --★Do
- SET @PWD = N'xxxxxx' --★Do
- SET @IP ='192.168.10.11,1433' --★Do
- EXEC master.dbo.sp_addlinkedserver @server = @IP,@srvproduct = N'SQL Server'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation compatible', @optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'data access', @optvalue = N'true'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'dist',@optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'pub',@optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc',@optvalue = N'true'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc out',@optvalue = N'true'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'sub',@optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'0'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation name', @optvalue = NULL
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'lazy schema validation', @optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'0'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'use remote collation', @optvalue = N'true'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'120'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'120'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'remote proc transaction promotion',@optvalue = N'true'
- USE [master]
- EXEC master.dbo.sp_addlinkedsrvlogin
- @rmtsrvname = @IP,
- @locallogin = NULL,
- @useself = N'False',
- @rmtuser = @Login,
- @rmtpassword = @PWD
- ---------------------------------------------------------------------------------------------------------------------------
- --create linkedserver
- USE [master]
- GO
- DECLARE @IP NVARCHAR(MAX)
- DECLARE @Login NVARCHAR(MAX)
- DECLARE @PWD NVARCHAR(MAX)
- SET @Login = N'sa' --★Do
- SET @PWD = N'xxxxxx' --★Do
- SET @IP ='192.168.10.12,1433' --★Do
- EXEC master.dbo.sp_addlinkedserver @server = @IP,@srvproduct = N'SQL Server'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation compatible', @optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'data access', @optvalue = N'true'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'dist',@optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'pub',@optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc',@optvalue = N'true'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc out',@optvalue = N'true'
- EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'sub',@optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'0'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation name', @optvalue = NULL
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'lazy schema validation', @optvalue = N'false'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'0'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'use remote collation', @optvalue = N'true'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'120'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'120'
- EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'remote proc transaction promotion',@optvalue = N'true'
- USE [master]
- EXEC master.dbo.sp_addlinkedsrvlogin
- @rmtsrvname = @IP,
- @locallogin = NULL,
- @useself = N'False',
- @rmtuser = @Login,
- @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 |
