SQL Server 2005/2008에서 관리자 전용 연결(DAC) 사용

SQL Server에서 심각한 Blocking 현상이 발생하면 SQL Server에 접속할 수 없는 상태에 빠질 수 있습니다. 또한, 특정 세션에서 리소스(CPU, 메모리, 디스크)를 과도하게 사용하는 쿼리문이 실행되면 전체적인 응답속도가 심각하게 저하되는 현상이 발생할 수도 있습니다. SQL Server에서 이런 증상이 발생했을 때 관리자는 빠른 시간 안에 원인을 확인하고 문제를 유발하는 세션을 종료하는 등의 적절한 조치를 취해야 합니다. 하지만, SQL Server 2000까지는 관리자가 원인을 파악하려고 해도 SQL Server에 접속조차 하지 못하거나 접속을 해도 응답속도가 너무 느려서 적절한 조치를 취하기 어려운 상황에 빠지기 일쑤였으며 결국에는 최후의 수단으로 SQL Server를 재시작하는 방법으로 문제를 해결하곤 하였습니다.

 

SQL Server 2005/2008에서는 이런 문제점을 해결하기 위해 관리자 전용 연결(Dedicated Administrator Connection)이 제공됩니다. 관리자 전용 연결은 응급시 최소한 관리자가 접속할 수 있도록 하고 조치를 위한 쿼리문 실행을 보장하기 위한 것으로 별도의 SOS(SQL OS) Scheduler를 통해 지원됩니다. SQL Server는 기본적으로 CPU 갯수에 해당하는 만큼의 SOS Scheduler를 생성하여 여러 동시 접속 세션들에서 실행되는 작업(Task)들간에 CPU가 적절히 할당되도록 조절합니다. SOS Scheduler는 제한된 갯수의 작업자 스레드(Worker Thread)를 생성하여 비선점(Nonpreemptive) 방식으로 작업자 스레드간 CPU 스케줄링을 하게 되는데 사용 가능한 작업자 스레드가 고갈되거나 특정 작업(Task)이 오랫동안 실행되게 되면 SOS Scheduler에 문제가 발생하여 접속이 되지 않거나 응답속도가 저하되는 현상이 발생할 수 있습니다. 그런데 SQL Server 2005/2008에서는 CPU 갯수만큼의 SOS Scheduler 외에 추가적으로 SOS Scheduler를 생성하여 관리자 전용 연결용으로만 사용되도록 함으로써 응급시 조치가 가능하도록 한 것입니다. SQL Server에서 sys.dm_os_schedulers DMV에 대한 조회를 해 보면 다음과 같이 모든 SOS Scheduler에 대한 정보를 확인해 볼 수 있는데 다음의 예에서는 scheduler_id 255에 해당하는 SOS Scheduler가 관리자 전용 연결(DAC)용으로 생성되어 있음을 알 수 있습니다. 

 

SQL Server 2005/2008에서 관리자 전용 연결은 기본적으로 서버 로컬에서만 접속이 가능하도록 활성화되어 있으며 단 하나의 연결만이 가능합니다. 사용자 세션은 기본적으로 1433의 TCP 포트를 통해 접속하는 반면에 관리자 전용 연결은 1434의 TCP 포트를 사용합니다. 관리자 전용 연결에 사용되는 TCP 포트는 임의의 포트를 지정할 수는 없으며 SQL Server 시작시 동적으로 할당됩니다. SQL Server 시작시 1434의 TCP 포트를 사용할 수 있으면 이를 사용하고 만약 사용할 수 없으면 동적으로 할당된 포트가 사용됩니다. 관리자 전용 연결에 사용되는 TCP 포트에 대한 정보는 SQL Server의 오류 로그(ERRORLOG)와 레지스트리에 기록됩니다. 다음은 SQL Server의 오류 로그에 기록된 메시지의 예로써 Loopback 주소인 127.0.0.1의 TCP 포트 1434에서 관리자 전용 연결을 위해 리스닝을 하고 있음을 알 수 있습니다. 관리자 전용 연결이 기본적으로는 서버 로컬에서만 접속이 가능한 이유는 바로 Loopback 주소에 대해서만 리스닝을 하고 있기 때문입니다.

2010-08-19 15:06:17.62 서버          Server is listening on [ ::1 <ipv6> 1434].
2010-08-19 15:06:17.63 서버          Server is listening on [ 127.0.0.1 <ipv4> 1434].
2010-08-19 15:06:17.63 서버          Dedicated admin connection support was established for listening locally on port 1434.

 

관리자 전용 연결에 사용되는 TCP 포트에 대한 정보는 다음의 레지스트리 값을 통해 확인해 볼 수도 있습니다. 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\

MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp\TcpDynamicPorts

 

관리자 전용 연결은 기본적으로 서버 로컬에서만 접속이 가능하다고 했습니다. 원격의 클라이언트에서 관리자 전용 연결을 통해 접속하려면 추가적인 설정이 필요합니다. 다음과 같이 서버 구성 옵션인 'remote admin connections'를 1로 설정함으로써 SQL Server를 재시작하지 않고도 원격에서 관리자 전용 연결을 통한 접속이 가능하도록 할 수 있습니다. 

sp_configure 'remote admin connections', 1
reconfigure with override

 

상기의 명령을 실행하면 SQL Server의 오류 로그(ERRORLOG)에 다음과 같은 메시지가 기록되며 곧바로 원격에서 관리자 전용 연결을 사용할 수 있는 상태가 됩니다. 다음의 메시지로부터 SQL Server 머신의 모든 IP Address('any')에 대해서 리스닝이 시작됨을 알 수 있습니다.

2010-08-19 16:23:02.83 서버          Server is listening on [ 'any' <ipv6> 1434].
2010-08-19 16:23:02.83 서버          Server is listening on [ 'any' <ipv4> 1434].
2010-08-19 16:23:02.84 서버          Dedicated admin connection support was established for listening remotely on port 1434.

 

클러스터 환경에서는 'remote admin connections' 서버 구성 옵션을 반드시 설정해야만 관리자 전용 연결을 사용할 수 있습니다. 그 이유는 클러스터 환경에서는 SQL Server에서 Loopback 주소를 사용하지 않기 때문(SQL Server에 대해 지정된 가상 IP Address만 사용)이며 'remote admin connections'를 설정하지 않으면 서버 로컬에서도 관리자 전용 연결을 사용할 수 없습니다. 따라서, SQL Server 클러스터를 설치한 다음에는 'remote admin connections' 서버 구성 옵션을 설정해 주는 것이 좋습니다.

 

관리자 전용 연결을 통해 SQL Server 2005/2008에 접속할 때 SQL Server Management Studio는 물론이고 sqlcmd나 osql 등의 명령행 유틸리티를 사용할 수 있습니다. 우선, SQL Server Management Studio를 사용하여 접속하는 방법은 파일 메뉴에서 '새로 만들기 - 데이터베이스 엔진 쿼리'를 실행하고 다음의 화면에서 서버 이름에 'admin:서버명'의 형태로 지정해 주는 것입니다(서버명으로 IP Address를 지정할 수도 있습니다). 

 

sqlcmd와 osql 명령행 유틸리티에서도 동일한 방식으로 서버명을 지정함으로써 관리자 전용 연결을 통해 접속할 수 있습니다. 다음과 같이 sqlcmd와 osql의 '-S' 옵션 값으로 'admin:서버명'을 지정해 주면 됩니다. 

C:\>sqlcmd -S admin:sql2008node1 -U sa -P ******

C:\>osql -S admin:sql2008node1 -U sa -P ******

 

sqlcmd 명령행 유틸리티는 SQL Server 2005부터 osql을 대체하기 위해 제공되는 유틸리티로 관리자 전용 연결을 위해 별도의 옵션 '-A'를 추가적으로 제공합니다. sqlcmd 실행시 '-A' 옵션을 지정하면 해당 접속이 관리자 전용 연결을 사용하도록 합니다. 다음과 같은 형태로 '-A' 옵션을 지정하여 sqlcmd를 실행함으로써 관리자 전용 연결을 통해 접속할 수 있습니다. 

C:\>sqlcmd -S sql2008node1 -A -U sa -P ******

C:\>sqlcmd -A   (서버 로컬에서 접속할 때에는 -A 옵션만을 지정하여 간단하게 접속 가능함) 

 

앞에서 관리자 전용 연결에 사용되는 TCP 포트는 SQL Server 시작시 동적으로 할당된다고 했습니다. 그렇다면 여러 개의 인스턴스가 설치되어 있거나 1434 포트가 다른 프로세스에 의해 이미 사용되고 있을 경우에는 1434 이외의 임의의 포트가 할당되어 사용되는 상황이 발생할 수 있습니다. 이럴 경우 클라이언트에서 관리자 전용 연결을 통해 SQL Server에 접속할 수 있는 방법은 무엇일까요? SQL Server Browser 서비스가 시작되어 있고 방화벽 등에 의해서 SQL Server Browser 서비스와 클라이언트간의 네트워크 패킷이 차단되지 않는다면 클라이언트에서 관리자 전용 연결을 위해 할당된 TCP 포트 정보를 SQL Server Browser 서비스를 통해 알아내어 자동으로 접속이 됩니다.

 

SQL Server Browser 서비스가 보안상의 이유로 중지되어 있는 경우(SQL Server 설치시 SQL Server Browser 서비스는 기본적으로 중지되어 있음)에는 SQL Server의 오류 로그(ERRORLOG)나 레지스트리를 통해 SQL Server에서 관리자 전용 연결을 위해 할당된 TCP 포트를 확인하고 이 TCP 포트 정보를 명시적으로 접속 정보로 지정해 주어야 합니다. SQL Server Management Studio를 이용하여 접속할 때에는 다음과 같이 서버 이름에 '서버명,포트번호'의 형태로 지정해 주면 됩니다. 

 

sqlcmd와 osql 명령행 유틸리티를 이용하여 접속할 때에는 다음과 같이 '-S' 옵션 값으로 '서버명,포트번호'를 지정해 주면 됩니다. sqlcmd 유틸리티의 경우 다음과 같이 포트번호를 함께 지정하는 방식으로 실행할 경우 '-A' 옵션은 지정하지 않습니다.

C:\>sqlcmd -S sql2008node1,1434 -U sa -P ******

C:\>osql -S sql2008node1,1434 -U sa -P ******