분산 트랜잭션을 시작할 수 없는 경우
SET XACT_ABORT 옵션을 활성화하지 않은 경우
SQL Server에서 분산 트랜잭션을 시작하기 위해서는 XACT_ABORT 세션 옵션을 반드시 ON으로 설정해야 합니다. XACT_ABORT 옵션을 ON으로 설정하면 T-SQL 문에서 런타임 오류가 발생할 경우 전체 트랜잭션이 종료된 후 롤백됩니다. OFF로 설정하면 오류를 발생시킨 T-SQL 문만 롤백되고 작업이 계속 진행됩니다. 구문 오류와 같은 컴파일 오류는 XACT_ABORT 옵션 설정으로 영향을 받지 않습니다.
XACT_ABORT 옵션을 다음과 같이 활성화합니다.
윈도우즈 2003 서버에서 DTC 사용 시 오류
윈도우즈 2003 서버에서 DTC를 사용하는 분산 트랜잭션을 실시하는 경우에는 다음과 같은 오류 메시지가 발생합니다.
이것은 윈도우즈 2003 서버가 기본적으로는 로컬 호스트 내에서의 분산 트랜잭션만을 허용하고 다중의 호스트가 참여하는 분산 트랜잭션에 대해서는 구성되지 않았기 때문입니다. 이 경우 문제를 해결하기 위해서는 먼저 현재의 분산 트랜잭션과 관련된 구성 정보를 확인 합니다. 구성 요소 서비스의 내 컴퓨터의 속성을 선택하고 MSDTC 탭을 선택하면 [로컬 코 디네이터 사용]에 체크가 되어 있음을 확인합니다. 이어서 하단에 있는 [트랜잭션 구성의 보안구성] 버튼을 눌러 [네트워크 DTC 액세스]의 체크박스 상태를 확인합니다. 만약 이 체크 박스가 선택되어 있지 않다면 다중 호스트의 분산 트랜잭션이 구성되어 있지 않다는 것을 의미합니다.
다른 호스트와의 분산 트랜잭션을 처리하기 위해서는 다음의 단계를 진행해야 합니다.
- 네트워크 DTC 액세스 설정
- RPC Security 구성 (트랜잭션 관리자 인증)
- TCP/IP 이름 분해 구성 (Hosts/DNS)
먼저, 네트워크 DTC 액세스 설정입니다.
관리 도구에서 구성 요소 서비스를 실행하거나 시작에서 실행을 선택한 후 Dcomcnfg를 입력한 후 엔터 키를 누릅니다. 내 컴퓨터의 속성을 선택합니다. 다음과 같은 창이 실행되면 화면 하단의 트랜잭션 구성의 [보안구성] 버튼을 누릅니다.
다음과 같이 보안 구성 창에서 ①번으로 표시된 [네트워크 DTC 액세스]의 체크박스를 선택 합니다. 창을 닫기 위해 확인 버튼을 누르면 MSDTC 서비스를 재시작합니다. 또는 제어판의 [프로그램 추가/삭제]에서 [윈도우즈 구성 요소 추가/삭제] 버튼을 누릅니다. 윈도우즈 구성 요소 중에서 응용 프로그램 서버를 선택하고 [자세히] 버튼을 누릅니다. 두 번째의 [네트 워크 DTC 액세스 사용] 체크박스를 선택합니다. [확인], [다음], [마침] 버튼을 순서대로 눌러 설치를 마칩니다. 이제 1단계가 완료되었습니다.
두 번째 단계는 네트워크 DTC의 보안 구성 단계입니다. 이 단계는 서비스 팩의 설치 여부에 따라서 다릅니다.
- ① 서비스 팩1 이 설치된 경우 위 그림의 보안 구성 창에서 ②번 부분의 트랜잭션 관리자 통신 영역의 [인증 필요 없음]을 선택합니다.
- ② 서비스 팩이 설치되지 않은 경우는 레지스트리 편집기를 실행해서 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft \MSDTC로 이동합니다. 편집 메뉴를 누르고 새로 만들기 → DWORD 값을 선택합니다. TurnOffRpcSecurity 라고 입력하고 값을 1로 할당합니다.
- ③ 두 경우 모두 보안 구성 창에서 ③번 부분의 DTC 로그온 계정이 NT Authority\Network Service 임을 확인합니다.
세 번째 단계에서는 분산 트랜잭션에 참여하는 호스트간에 호스트 이름으로 통신이 가능하도록 구성합니다. 회사 내에서 DNS 서버를 사용하는 경우에는 모두 DNS에 이름을 등록합니다. DNS 서버가 구성되지 않은 경우에는 %SystemRoot%\system32\Drivers\Etc 폴더의 hosts 파일에 상대방 서버의 호스트이름과 IP 주소를 등록합니다.
이와 같은 작업을 분산 트랜잭션을 사용할 모든 호스트에서 실시합니다. 작업이 완료되면, 다시 쿼리 분석기의 분산 트랜잭션 테스트를 진행하여 분산 트랜잭션이 정상적으로 진행되는지 확인합니다.
DTC의 통신 여부와 구성 상태는 마이크로소프트가 제공하는 DTCPing.exe 라는 유틸리티를 통해서 점검할 수 있습니다. DTCPing.exe를 다운로드 받아 테스트하려는 호스트에 복사합니다.
DTCPing.exe를 각각의 호스트에서 실행하고 상대방 호스트 이름을 입력한 후 PING 버튼을 누르면 통신 여부를 보여주며 자세한 사항은 지정된 폴더에 생성되는 로그 파일을 통해 확인할 수 있습니다.
DTCPing 유틸리티의 로그는 다음 사항을 포함합니다.
① 대상 DTC 의 구성 정보
② 대상 호스트의 IP 구성
③ 대상 호스트의 NIC 구성
④ 대상 호스트의 host 파일과 lmhost 파일 구성 정보
⑤ DTC 통신 결과
분산 쿼리 사용시 주의 사항 및 오류
다른 서버로의 연결이 자주 발생하지 않는 경우는 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자 이상의 쿼리를 전송하지 못합니다.
Linked Server를 사용하여 OPENQUERY를 실행하는 중 오류가 발생하는 경우 DBCC TRACEON (7300, 3604) 구문을 실행하면 보다 자세한 오류 정보를 반환 받을 수 있습니다