Alwayson Automatic synchronization Login(Alwayson 로그인 자동 동기화) ②

안녕하세요. MSSQL 2016 AlwaysON 운영을 하면서 계속해서 운영이슈가 생기고 있습니다.

이번에는 지난 번에 없는 계정을 만드는 스크립트에 이어서, 2번째로 Standby에만 존재하는 로그인 삭제와 Active 서버를 기준으로 패스워드가 

다른 로그인에 대해서 만들어봤습니다.

이전의 포스트( http://burning-dba.tistory.com/80 )에서 없는 계정을 만드는 부분을 했었는데, 먼저 읽어보시고 이것을 진행해야 합니다.

이번 포스트에서는 위에서 언급한데로, 2가지 기능을 추가하였습니다. 삭제하는 로그까지 추가하면 3가지가 되겠네요.

1. 삭제 대상 로그인 기록 

2. Active서버에 없고, Standby 서버에 없는 로그인 삭제

3. Active서버와 Standby 서버의 패스워드가 다를 경우 Standby 서버의 로그인 삭제

여기에 기능을 보시면 모두 삭제 밖에 없는데, 이렇게 해도 무방 한 이유는 제가 이전에 만든 내용이 로그인이 없을 경우 생성이기 때문입니다.

그렇기에 패스워드가 다르거나, 한쪽에만 존재하면 삭제해버리고 바로 만들어주면 아무 문제가 되지 않습니다. 

1. 삭제 대상 로그인 기록 

이 쿼리는 별개 없습니다. 삭제 하고자 하는 대상이되는 로그인을 테이블에 기록하기 위함입니다. 이것을 기록하는 이유는 로그성과 함께

만약에 상황에 대해서 즉시 계정을 확인하고 생성하고자 하기 위함 입니다.

기본적으로 ##MS가 들어가는 계정과 NT로 들어가는 시스템적인 로그인은 대상에서 제외하였습니다. 

또한 사용자에 편의에 따라서 서비스용(개발자,DBA계정이 아닌 계정) 으로 사용하는 앞단 계정들은 NOT IN 조건으로 체크하여 제외할수 있습니다. (3개 공통)

USE master
GO


-- 삭제되는 로그인 기록용 로그 테이블
CREATE TABLE Login_DROP_History
(
 Drop_Login	VARCHAR(100)
,Drop_PW		sql_variant
,Drop_date		DATETIME
)

CREATE NONCLUSTERED INDEX NC_1 ON Login_DROP_History(Drop_date)





USE master
GO


-- 삭제 전에 로그인 내역기록 
CREATE PROC [dbo].[Login_DROP_History_INS]
AS

	SET NOCOUNT ON

	BEGIN

		INSERT INTO Login_DROP_History
		SELECT A.name,A.PW,'삭제',GETDATE()
		FROM
			(
			SELECT name
					, LOGINPROPERTY(name, 'PasswordHash' ) PW
			FROM SYS.syslogins with(nolock)

			)AS A
		LEFT JOIN
			(
			SELECT name,PW FROM Openquery([ACTIVE],
						'SELECT CAST ( LOGINPROPERTY ( name , ''PasswordHash'' ) AS varbinary ( 514) )as [PW] ,name 
							FROM sys.syslogins with(nolock)')
			)AS b
		ON A.name = B.name 
		WHERE A.name <> 'sa' and A.name not like '##MS%' and A.name not like 'NT%' AND B.name IS NULL


		INSERT INTO Login_DROP_History
		SELECT A.name,A.PW,'동기화',GETDATE()
		FROM
			(
			SELECT name,PW FROM Openquery([ACTIVE],
						'SELECT CAST ( LOGINPROPERTY ( name , ''PasswordHash'' ) AS varbinary ( 514) )as [PW] ,name 
						 FROM sys.syslogins with(nolock)
						 WHERE password IS NOT NULL')
			)AS A
		INNER JOIN
			(
			SELECT name
				 , LOGINPROPERTY(name, 'PasswordHash' ) PW
			FROM SYS.syslogins with(nolock)
			WHERE password IS NOT NULL
			)AS b
		ON A.name = B.name AND A.PW <> B.PW
		WHERE A.name <> 'sa' and A.name not like '##MS%' and A.name not like 'NT%' 

	END

GO

2. Active서버에 없고, Standby 서버에 없는 로그인 삭제

Active 서버에는 없으나, Standby에 있는 경우는 극히 드물것으로 보입니다. 

하지만 이것도 상황에 따라서 있을 수 있으니, 적절히 NOT IN 조건으로 계정을 빼주셔야 합니다.

이것의 원리는 지난번에 포스트와 비슷 합니다. 대신 기준이 Standby 입니다. 기준을 Standby로 하여, 존재할 경우 삭제해주는 것 입니다.

USE master
GO

CREATE PROC sp_login_Drop
AS 

	SET NOCOUNT ON

	DECLARE @Login_name VARCHAR(100)
	DECLARE @SQL VARCHAR(150)

	DECLARE Login_cur CURSOR GLOBAL FOR 
	SELECT A.name
	FROM
		(
		SELECT name
			 , LOGINPROPERTY(name, 'PasswordHash' ) PW
		FROM SYS.syslogins with(nolock)

		)AS A
	LEFT JOIN
		(
		SELECT name,PW FROM Openquery([ACTIVE],
					'SELECT CAST ( LOGINPROPERTY ( name , ''PasswordHash'' ) AS varbinary ( 514) )as [PW] ,name 
					 FROM sys.syslogins with(nolock)')
		)AS b
	ON A.name = B.name 
	WHERE A.name <> 'sa' and A.name not like '##MS%' and A.name not like 'NT%' AND B.name IS NULL

	OPEN Login_cur

	FETCH NEXT FROM Login_cur INTO @Login_Name
	WHILE @@FETCH_STATUS = 0

	BEGIN 

		SET @SQL = 'DROP LOGIN ' + @Login_Name
		EXEC (@SQL)

		FETCH NEXT FROM Login_cur INTO @Login_Name
	END 

	CLOSE Login_cur
	DEALLOCATE Login_cur

GO

3. Active서버와 Standby 서버의 패스워드가 다를 경우 Standby 서버의 로그인 삭제

이것 또한 기본적으로 Active 기준입니다. Active에서 패스워드를 변경 하고 Standby에서 일일이 패스워드를 변경하는 것이 귀찮기에  만들었습니다.

원리는 동일 합니다. 다만, Password라는 Varbinary 값을 서로 비교하여, 조인하여 이것이 서로 다를 경우 삭제 합니다.

물론 위에서 기록하는 곳에서 이 내용도 기록 합니다. 저는 이내용은 동기화로 하고, 2번 내용은 삭제로 하였습니다.

USE master
GO


CREATE PROC [dbo].[sp_login_synchronization]
AS

	SET NOCOUNT ON

	DECLARE @Login_name VARCHAR(100)
	DECLARE @SQL VARCHAR(150)

	DECLARE Login_cur CURSOR GLOBAL FOR 
	SELECT B.name
	FROM
		(
		SELECT name,PW FROM Openquery([ACTIVE],
					'SELECT CAST ( LOGINPROPERTY ( name , ''PasswordHash'' ) AS varbinary ( 514) )as [PW] ,name 
					 FROM sys.syslogins with(nolock)
					 WHERE password IS NOT NULL')
		)AS A
	INNER JOIN
		(
		SELECT name
			 , LOGINPROPERTY(name, 'PasswordHash' ) PW
		FROM SYS.syslogins with(nolock)
		WHERE password IS NOT NULL
		)AS b
	ON A.name = B.name AND A.PW <> B.PW
	WHERE A.name <> 'sa' and A.name not like '##MS%' and A.name not like 'NT%' 

	OPEN Login_cur

	FETCH NEXT FROM Login_cur INTO @Login_Name
	WHILE @@FETCH_STATUS = 0

	BEGIN 

		SET @SQL = 'DROP LOGIN ' + @Login_Name
		EXEC (@SQL)
		--PRINT @SQL

		FETCH NEXT FROM Login_cur INTO @Login_Name
	END 

	CLOSE Login_cur
	DEALLOCATE Login_cur

GO

최종적 결론

이 모든 것을 만들고, 순서에 맡게 만들면 다음과 같이 만들수 있습니다. 순서는 기록이 제일 처음가고, 나머지 삭제의 순서는 상관 없습니다.

그리고 마지막으로 계정만 재생성하게 해주면 문제없이 됩니다.

그리고 여담으로 만약에 Standby서버로 넘어 갈 경우 기준이 바뀌어서 전부 삭제되는거 아니야? 라는 생각을 했는데 AlwayON은 링크드 서버는 동기화 시키지 않습니다.

그래서 비교할때 링크드 서버를 기반으로 하기 때문에 쿼리가 오류나면서 실패가 납니다. 아직 그런 경우는 없지만 이론상으로는 별다른 큰 문제는 없다고 생각 됩니다.

또한 Standby 서버로 넘어갔을때 서로 바꿔서 다시 기준이 Standby로 해서 해야 하는거 아니야? 

생각도 해봤는데 보통 그경우는 장애이므로, 다시 Active로 넘기실 거라고 생각 됩니다. 그래서 이 또한 별다른 문제 없이 사용 가능 하다고 생각됩니다.

저 같은 경우 실무에서 1시간 단위 동기화를 선택하였습니다. 

이는 사용자 계정의 패스워드를 빈번하게 변경하거나 삭제 또는 생성하지 않기 때문에 이는 차후의 편의를 위함이지 실시간의 동기화 개념으로 사용하기에는 약간의 

무리가 있다고 생각됩니다. 뭐 10분 단위 정도는 나쁘지 않다고 생각됩니다.

감사합니다.