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

다른 서버로의 연결이 자주 발생하지 않는 경우는 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=[대상 인스턴스 이름]

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

2) Linked Server를 이용한 SELECT / INSERT

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

OPENQUERY 함수 사용

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

OPENQUERY 함수 사용

OPENQUERY 함수 사용

3) Linked Server를 이용한 UPDATE / DELETE

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

UPDATE 구문 사용

DELETE 구문 사용

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

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

OPENQUERY 함수 사용

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

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

Four-Part 이름을 사용해서 원격의 저장 프로시저 호출

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

오류메시지

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

OPENQUERY 함수

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

OPENQUERY 함수

[참고]

Linked Server를 사용하여 OPENQUERY를 실행하는 중 오류가 발생하는 경우 DBCC TRACEON (7300, 3604) 구문을 실행하면 보다 자세한 오류 정보를 반환 받을 수 있습니다

출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=13771&boardConfigUid=9&categoryUid=216&boardIdx=66&boardStep=1

'SQL' 카테고리의 다른 글

트랜잭션 정리  (0) 2011.11.23
SQL Server Stress Test  (0) 2011.10.31
확장 저장 프로시저 내리기  (0) 2010.04.28
SQL Server 확장 저장프로시저  (0) 2010.04.28
문서화 되지 않은 시스템 저장 프로시저  (0) 2010.04.28

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

 

위험한 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의 경우 아예 내려버리고 지울 수도 있다.

 

 

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

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


SQL Server 확장 스토어드 프로시저

스토어드 프로시져는 SQL Server의 메모리 영역에서 직접 실행되는 DLL(Dynamic Link Library)로 SQL Server의 Open Data Services API를 이용해 개발된 것이다.

C와 같은 프로그래밍 랭귀지를 통해 본인이 원하는 확장 스토어드 프로시져를 만들 수 있다.

보통 스토어드 프로시저처럼 QA(Query Analyzer)에서 이 확장 스토어드 프로시져를 실행시켜 볼 수 있다.

확장 스토어드 프로시져는 SQL Server의 능력을 확장시키기 위해 보통 사용되는 것이다.

 

여기서는 문서화되어 있지는 않지만 사용하면 유용한 확장 스토어드 프로시져를 소개하고자 한다.

SQL Server 7.0 및 SQL Server 2000 모두에서 잘 동작하는 것들이다.

 

sp_MSgetversion

       이 확장 스토어드 프로시져는 SQL Server의 현재 버전을 알기 위해 사용된다.

SQL Server의 현재 버전을 알려면 다음과 같이 실행하면 된다.

EXEC master..sp_MSgetversion

<주의> SQL Server의 현재 버전을 알기 위한 더 좋은 방법은 Select문을 사용하는 것이다.

Select @@version

 

xp_dirtree

이 확장 스토어드 프로시져는 전달된 디렉토리의 모든 서브디렉토리 리스를 얻기 위해 사용된다.

가령, C:\MSSQL 7 디렉토리의 모든 서브디렉토리 리스트를 얻으려면 다음과 같이 하면 된다.

EXEC master..xp_dirtree 'C:\MSSQL 7'

 

xp_enum_oledb_providers

이 확장 스토어드 프로시져는 모든 OLE DB provider를 얻기 위해 사용된다.

xp_enum_oledb_providers는 Provider Name, Parse Name 및 Provider Description을 결과로 돌려준다.

모든 OLE DB Provider를 얻으려면 다음과 같이 하면 된다.

EXEC master..xp_enum_oledb_providers

 

xp_enumcodepages

이 확장 스토어드 프로시져는 모든 코드 페이지, 문자 집합 및 그 설명를 얻기 위해 사용된다.

EXEC master..xp_enumcodepages

 

xp_enumdsn

이 확장 스토어드 프로시져는 모든 시스템 DSN 리스트 및 그 설명을 얻기 위해 사용된다

EXEC master..xp_enumdsn

 

xp_enumerrorlogs

이 확장 스토어드 프로시져는 마지막 변경일과 함께 모든 에러 로그 리스트를 얻기 위해 사용된다.

EXEC master..xp_enumerrorlogs

 

xp_enumgroups

이 확장 스토어드 프로시져는 윈도 NT 그룹과 그 설명을 얻기 위해 사용된다.

EXEC master..xp_enumgroups

 

xp_fileexist

이 확장 스토어드 프로시져는 디스크 상에 특정 파일이 존재하는지 알려 준다.

EXEC master..xp_fileexist 'C:\boot.ini'

 

xp_fixeddrives

이 확장 스토어드 프로시져는 로컬 PC에 붙어 있는 모든 디스크 리스트와 각 하드디스크의 남은 공간을 알려준다.

EXEC master..xp_fixeddrives

 

xp_getnetname

이 확장 스토어드 프로시져는 현재 연결되어 있는 SQL Server의 WINS 네임을 알려준다

EXEC master..xp_getnetname

 

xp_readerrorlog

이 확장 스토어드 프로시져는 에러 로그 파일 내용을 알려준다

에러 로그 파일 위치는 디폴트로 "C:\MSSQL\LOG" 디렉토리이다.

EXEC master..xp_readerrorlog

 

xp_subdirs

이 확장 스토어드 프로시져는 전달된 디렉토리의 서브디렉토리 리스트를 돌려준다.

xp_dirtree와는 달리 detph=1인 디렉토리들만 반환한다

EXEC master..xp_subdirs 'C:\MSSQL 7'

+ Recent posts