세상을 가끔은 미쳐서 살아보자...

분산 쿼리 사용시 주의 사항 및 오류

다른 서버로의 연결이 자주 발생하지 않는 경우는 OPENRWOSET 함수나 OPENDATA SOURCE 함수 등 AD-HOC 쿼리를 사용하는 것이?? Linked Server를 구성하여 사용하는 것이 효율적입니다. 먼저 Linked Server를 구 성하였을 때 사용할 수 있는 OPENQUERY 함수와 Four-Part 이름을 사용한 쿼리의 성능상 문제를 비교하고 이와 관련해서 발생하는 오류를 점검합니다.

많은 사이트에서 개발자가 손쉽게 Query를 작성할 수 있다는 장점 때문에 Four-Part 이름을 사용하는 Query를 사용하지만 성능상 문제를 정확히 이해한 뒤 사용해야 합니다. Four-Part 이름을 사용한 Query는 로컬 서버의 리소스를 사용하고 OPENQUERY 함수는 Passthrough Query라고 해서 원격 서버의 리소스를 사용합니다. 즉 Pass-through Query는 Query자체를 원격 서버에 보내서 최적화 작업을 원격 서버가 실시하고 그에 따른 결과만을 Romote Scan 하여 반환 받지만, Four-Part 이름을 사용한 Query는 원격 서버가 동일한 SQL 서버인 경우에는 원격 서버에게 sp_tableinfo_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset 등의 시스템 저장 프로시저 실행을 요청해서 반환 받은 정보를 바탕으로 로컬 서버가 최적화를 한 뒤에 Remote Query를 실행하게 됩니다. 따라서, Four-Part Query는 Pass-through Query에 비해서 이론적으로 비효율적입니다. 그러나 모든 경우가 이와 같지는 않습니다. UPDATE 구문과 DELETE 구문을 예로 들면 Four-Part 이름을 사용한 Query가 오히려 좋은 성능을 냅니다. UPDATE와 DELETE를 실행하는 경우 OPENQUERY 함수는 OLEDB Provider 의 기능의 제한으로 인해서 UPDATE 나 DELETE 구문을 전송하지 못하므로 워크테이블을 생성하는 방법으로 처리하게 됩니다. 이와 같은 경우는 Four-Part 이름을 사용한 Query를 사용하는 것이 효율적입니다. 따라서, UPDATE나 DELETE 구문을 사용하거나 원격의 저장 프로시저를 사용하는 경우, 쿼리의 길이가 8000 바이트를 넘는 경우 등은 Four-Part 이름을 사용한 Query를 사용하고 나머지 경우에는 OPENQUERY 함수를 사용하는 것이 성능상 유리할 수 있습니다. 오라클과 같은 이기종 DBMS인 경우에는 SQL 서버와 구문이 다른 옵티마이저 힌트를 주거나 오라클 함수의 실행과 같은 OLEDB Provider가 이해하지 못하는 작업은 Four-Part 이름을 사용한 Query는 불가능하며 검색 조건을 제대로 지정하더라도 테이블 스캔을 해서 성능을 저해할 수 있습니다. 따라서, 이와 같이 원격 서버로 Query를 실행하는 작업은 충분한 테스트를 진행한 뒤 어떤 방법을 사용할 것인지 결정해야 합니다.

1) OPENROWSET 함수 사용 시 윈도우즈 인증

회사 내 보안을 위하여 계정 정보와 패스워드를 소스 내에 하드코딩하는 것은 바람직하지 않습니다. OPENROWSET 함수 사용시 윈도우즈 인증을 사용할 것을 권장합니다. 윈도우 즈 인증을 통한 OPENROWSET 함수를 사용하는 경우는 다음 항목을 지정해야 합니다.

Trusted_Connection=YES; Integrated Scurity=SSPI ;Data Source=[대상 인스턴스 이름]

2) Linked Server를 이용한 SELECT / INSERT

원격 서버에 SELECT 또는 INSERT 구문을 실행하는 경우 OPENQUERY 함수를 사용하는 것이 효율적입니다. 그러나 다음 예제 가운데 하나와 같이 집계연산을 포함하여 조인을 하는 등의 복잡한 쿼리는 검색조건이 효율적으로 지정되도록 주의하여야 합니다.

<검색조건을 효율적으로 지정한 동적 쿼리>

3) Linked Server를 이용한 UPDATE / DELETE

원격 서버에 UPDATE 또는 DELETE 구문을 실행하는 경우 Four-Part 이름을 사용한 Query가 효율적입니다. 다음과 같은 OPENQUERY 함수를 사용한 UPDATE와 DELETE의 사용을 자제합니다.

4) Linked Server를 사용한 함수 실행

원격 서버의 함수를 실행하고자 하는 경우에는 다음과 같이 OPENQUERY 함수를 사용합니다. Four-Part 이름을 사용해서 원격 서버에 대한 함수 호출은 지원되지 않습니다.

5) Linked Server를 사용한 저장 프로시저 실행

원격 서버의 저장 프로시저를 실행하고자 하는 경우 충분한 검증을 필요로 합니다. Four-Part 이름을 사용해서 원격의 저장 프로시저를 호출하는 것이 다소 효율적인 경우가 많습니다.

6) DML 문장을 직접 OPENQUERY 함수 안에 작성하는 것않고 다음과 같이 오류를 발생하게 됩니다.

7) OPENQUERY 함수를 사용해서 원격 서버의 확장 저장 프로시저를 실행할 수 없습니다.

8) OPENQUERY 함수를 사용해서 8000자 이상의 쿼리를 전송하지 못합니다.

보안상 위협이 될 수 있는 개체들에 대하여 일반 사용자 그룹의 사용권한을 제한한다.

위험한 sp들을 등록 해제한다.

1. use master
   exec sp_dropextendedproc 'xp_cmdshell'
   exec sp_dropextendedproc 'xp_dirtree'
   exec sp_dropextendedproc 'xp_enumgroups'
   exec sp_dropextendedproc 'xp_fixeddrives'
   exec sp_dropextendedproc 'xp_loginconfig'
   exec sp_dropextendedproc 'xp_enumerrorlogs'
   exec sp_dropextendedproc 'xp_getfiledetails'
   exec sp_dropextendedproc 'Sp_OACreate'
   exec sp_dropextendedproc 'Sp_OADestroy'
   exec sp_dropextendedproc 'Sp_OAGetErrorInfo'
   exec sp_dropextendedproc 'Sp_OAGetProperty'
   exec sp_dropextendedproc 'Sp_OAMethod'
   exec sp_dropextendedproc 'Sp_OASetProperty'
   exec sp_dropextendedproc 'Sp_OAStop'
   exec sp_dropextendedproc 'Xp_regaddmultistring'
   exec sp_dropextendedproc 'Xp_regdeletekey'
   exec sp_dropextendedproc 'Xp_regdeletevalue'
   exec sp_dropextendedproc 'Xp_regenumvalues'
   exec sp_dropextendedproc 'Xp_regread'
   exec sp_dropextendedproc 'Xp_regremovemultistring'
   exec sp_dropextendedproc 'Xp_regwrite'
   drop procedure sp_makewebtask
   go

주의: 위의 SP들을 다 제거할 경우 SP4 및 핫픽스 등의 인스톨이 실패할 수 있다. 그 경우 복구를 위해서는 (잘 보니 sp_oa시리즈가 없네 ㅡ_ㅡ)

1. use master
   exec sp_addextendedproc 'xp_cmdshell', 'xplog70.dll'
   exec sp_addextendedproc 'xp_dirtree', 'xpstar.dll'
   exec sp_addextendedproc 'xp_enumgroups', 'xplog70.dll'
   exec sp_addextendedproc 'xp_fixeddrives', 'xpstar.dll'
   exec sp_addextendedproc 'xp_loginconfig', 'xplog70.dll'
   exec sp_addextendedproc 'xp_regaddmultistring', 'xpstar.dll'
   exec sp_addextendedproc 'xp_regdeletekey', 'xpstar.dll'
   exec sp_addextendedproc 'xp_regdeletevalue', 'xpstar.dll'
   exec sp_addextendedproc 'xp_regread', 'xpstar.dll'
   exec sp_addextendedproc 'xp_regremovemultistring', 'xpstar.dll'
   exec sp_addextendedproc 'xp_regwrite', 'xpstar.dll'
   exec sp_addextendedproc 'xp_enumerrorlogs', 'xpstar.dll'
   exec sp_addextendedproc 'xp_getfiledetails', 'xpstar.dll'
   exec sp_addextendedproc 'xp_regenumvalues', 'xpstar.dll'
   go

메모리에서 위험한 sp들을 내린다.

1. dbcc xp_cmdshell(free)
   dbcc xp_dirtree(free)
   dbcc xp_regdeletekey(free)
   dbcc xp_regenumvalues(free)
   dbcc xp_regread(free)
   dbcc xp_regwrite(free)
   dbcc sp_makewebtask(free)

필요하지 않은 dll의 경우 아예 내려버리고 지울 수도 있다.

• 주의: 부작용 ( Implications of removing the xp_cmdshell stored procedure from SQL Server 2000 )
  • xp_dirtree, xp_regread를 없앨 경우 로그 읽기 등에서 문제 발생.
  • xp_regread를 없앨 경우 백업 / 리스토어 또는 SQL Server Agent 작업에서 문제 발생.
  • 서비스 팩 및 누적 패치 설치 전에는 위의 프로시저가 원래대로 복구되어 있어야 함.
• 참조글: Moving to SQL Server 2005 at Microsoft -> SQL Server 2005 전개 및 기능 구현 백서
• 참조글: Lockdown Script -> 주의: 바로 적용하지 말 것!!!!!!! 보조 서비스 다 내려버리는 등의 부작용이 있으므로 코드 부분참조.
• 참조글: SQL Server Checklist (6.5용이지만 확장 프로시저 리스트 등은 유심히 볼 것)

** 일부 보안 모델, 확장 프로시저 보안 설정, DBCC 명령 등이 2005에서 많이 바뀌었으므로 유의.

출처:http://blahblah.springnote.com/pages/351077