[개요]

한 장비에서 SQL Agent를 통해 여러 SSIS 패키지를 수행하는 경우 각 SSIS 패키지별로 서로 다른 Windows 계정을 사용하여 SSIS 패키지를 수행해야 할 때가 있습니다. 예를 들어 [그림1]과 같이 각 SSIS 패키지 내에서 서로 다른 Windows 계정을 이용하여 대상 서버에 인증을 해야 하는 경우입니다. SSIS 패키지에서 이런 Windows 계정을 통한 인증은 SQL Server로 로그인, 특정 폴더나 파일 접근과 같은 작업에 필요합니다. 기본적으로 SQL Agent를 이용하여 배치 작업을 수행시 SQL Agent 시작 계정을 인증에 사용하므로 이런 경우 여러 계정을 지정할 수 없기 때문에 권한 문제가 발생하게 됩니다. 때문에 이런 경우에는 "자격 증명"과 "프록시"를 사용하여 SQL Agent에서 SSIS 패키지 작업별로 인증에 사용할 Windows 계정을 지정할 수 있습니다.

[그림 1 – 패키지별로 서로 다른 계정을 이용하여 인증]

 

 

[SQL Agent에서 특정 Windows 계정으로 SSIS 패키지 실행하기]

  1. 인증에 사용할 로컬 Windows 계정으로 Login1, Login2이 있다고 가정합니다.
  2. 먼저 자격증명을 생성하도록 하겠습니다. SSMS(SQL Server Management Studio)를 실행한 다음 "개체 탐색기 – 보안 – 자격증명 – 새 자격 증명"을 실행합니다.
  3. 다음과 같이 정보를 채워 넣습니다. 이 단계는 Login1이라는 Windows 계정을 가지고 myCredential1이라는 자격 증명을 생성하게 됩니다.
    - 자격 증명 이름 : myCredential1
    - ID : Login1
    - 암호 : Login1의 암호
  4. 확인을 눌러 자격증명 생성을 완료한 다음 동일한 방식으로 Login2를 이용하여 myCredential2를 생성합니다. 이제 SSIS 패키지 수행할 때 사용할 자격 증명 준비가 완료 되었습니다.
  5. 이번에는 프록시를 생성하도록 하겠습니다. SSMS(SQL Server Management Studio)를 실행한 다음 "개체 탐색기 – SQL Server 에이전트 – 프록시 – 새 프록시"를 실행합니다.
  6. 다음과 같이 정보를 채워 넣습니다. 이 단계는 myCredential1 자격 증명을 이용해 proxyLogin1이라는 프록시를 생성합니다.
    - 프록시 이름 : proxyLogin1
    - 자격 증명 이름 : myCredential1
    - SQL Server Integration Services 패키지 체크
  7. 생성된 프록시는 "SSIS 패키지 실행"에서 확인할 수 있으며, myCredential2 자격 증명을 이용하여 proxyLogin2를 생성합니다. 이제 작업에 사용할 프록시가 준비 되었습니다.
  8. "SQL Server작업 – 작업 – 새 작업"을 실행합니다.
  9. 새 작업 단계에서 유형을 "SQL Server Integration Services 패키지"를 선택한 다음 "다음 계정으로 실행" 영역을 보면 생성했던 proxyLogin1, proxyLogin2를 확인할 수 있습니다. 여기서 필요한 프록시를 선택하면 프록시와 연결된 자격 증명을 통해 해당 Windows 계정의 인증을 사용해 SSIS 패키지를 실행할 수 있습니다.
  10. 다음 계정으로 실행(R)에서 proxyLogin1을 선택 하여 작업을 생성했을 경우 작업 수행 시 작업의 기록 보기에서 다음과 같은 내용을 확인할 수 있습니다.

 

 

[참고자료]
자격 증명(데이터베이스 엔진)
http://msdn.microsoft.com/ko-kr/library/ms161950.aspx
SQL Server 에이전트 프록시 만들기
http://msdn.microsoft.com/ko-kr/library/ms189064.aspx
작업 구현
http://msdn.microsoft.com/ko-kr/library/ms187880.aspx

 

AND

1탄(Windows 2008 R2 Hyper-V에서 클러스터 테스트 환경 구성 1)에 이어 계속되는 글입니다.

 

단계 4. Cluster 구성2

  1. Node1로 가서 서버 관리자에서 기능 – 기능 추가를 선택합니다.
  2. 장애 조치(failover) 클러스터링을 체크 후 "다음"을 클릭해 설치합니다. 이 방식으로 Node2도 설치를 합니다.
  3. 다시 Node1으로 가서 장애 초치 클러스터를 생성합니다.
  4. 서버 이름을 입력 후 "추가" 버튼을 누르면 서버가 추가됩니다. Node1 , Node2를 추가한 후 다음 단계로 넘어갑니다.
  5. 다음 단계에서 모든 유효성 검사를 하도록 선택후 "모든 테스트 수행"을 합니다. 단계를 넘어가다 보면 아래처럼 각 노드에서 유효성 검사를 진행합니다. 디스크를 아직 구성하지 않아 몇개 경고가 나오지만 무시합니다.
  6. 클러스터 이름과 IP를 입력합니다. 저는 이름은 CWIN2K8R2-Node , IP는 192.168.1.10으로 하였습니다.
  7. 이제 클러스터 기능이 추가 되었습니다.
  8. 서버 관리자를 다시 실행한 다음 클러스터 관리로 들어가 봅니다. 네트워크 이름을 클릭해 오른쪽에 보이는 서브넷을 보고 public , private으로 이름을 조정해 줍니다. (안그러면 나중에 햇갈려요~)
  9. 이 네트워크 이름에서 마우스 오른쪽을 클릭해 속성을 보면, public 네트워크에서는 "클라이언트가 이 네트워크를 통해 연결할 수 있음"이 체크되어 있고, private 네트워크에서는 이 체크가 지워져 있어야 합니다.
  10. 이제 클러스터에서 사용한 디스크를 구성해야 합니다. 디스크는 SAN이 없기 때문에 Microsoft iSCSI Software Target을 이용해 구성하도록 하겠습니다. 저는 이 파일을 다음 윈도우 ISO 파일에서 찾았습니다. "mu_windows_storage_server_2008_r2_embed_stand_ent_work_iscsi_software_target_3.3 _x64_dvd_587719.iso"
  11. DISK를 구성할 장비에서 프로그램 설치 후 "관리도구 – Microsoft iSCSI Software Target"을 실행합니다. 저는 Win2K8R2-AD 장비에 설치 하였습니다.
  12. iSCSI 대상에서 마우스 오른쪽 클릭하여 iSCSI 대상 만들기를 실행 합니다.
  13. iSCSI 대상 이름을 정합니다. 저는 "iSCSIDISK"로 정했습니다.
  14. iSCSI 초기자 ID는 고급 버튼을 클릭해 IP를 입력하도록 합니다. 이 단계는 생성한 Disk에 아무 장비나 붙으면 안되기 때문에 붙을수 있는 장비를 지정해 주는 단계입니다.
  15. 추가 버튼을 누르면 새창이 뜨고, ID 유형은 "IP주소"로 값에는 Node1의 IP를 입력합니다.
  16. Node2번도 추가하여 다음과 같이 Node1, Node2의 IP가 모두 등록되도록 합니다.
  17. 확인하고 다음으로 넘어가면 아래처럼 iSCSIDISK가 생성된 것을확인할 수 있습니다.
  18. 이번에는 장치에서 마우스 오른쪽 버튼을 클릭해 "가상 디스크 만들기"를 합니다.
  19. DISK가 위치할 경로를 입력합니다. 먼저 쿼럼용 디스크를 생성해 보도록 하겠습니다.
  20. 사이즈는 1GB 정도로 잡아 봅니다.
  21. 디스크 설명은 대충 "Quorum"이라고 입력하고 넘깁니다.
  22. 다음 단계로 방금 만든 DISK는 우리가 전단계에서 생성한 iSCSIDISK에서 액세스 할 수 있도록 선택합니다.
  23. 이 장치 추가 단계를 통해 DTC와 SQL용 DISK 를 추가합니다. 잘 추가되면 iSCSIDISK를 클릭했을때 오른쪽에 아래와 같이 추가된 내용이 보이게 됩니다.
  24. 이제 디스크 준비는 완료 되었습니다. 다음은 Node1, Node2에서 방금 생성한 디스크를 연결하도록 하겠습니다.
  25. Node1로 가서 "관리 도구 – iSCSI 초기자"를 실행 합니다.
  26. 검색 탭에서 "포털 검색"을 클릭합니다.
  27. DISK를 생성한 AD장비의 IP를 입력한 다음 확인버튼을 누릅니다.
  28. 대상 탭에서 "연결"버튼을 누릅니다. 새창이 뜨고 확인 버튼을 누르면 상태 부분이 "연결됨"으로 변경됩니다.
  29. Node2로 가서 동일하게 iSCSI 초기자를 이용하여 디스크를 연결합니다.
  30. 다시 Node1의 서버 관리자로 돌아와서 "저장소-디스크 관리"를 보면 추가한 3개의 디스크가 보이는것을 확인할 수 있습니다.
  31. 각 디스크에서 마우스 오른쪽 버튼을 클릭해 온라인, 디스크 초기화, 새 단순 볼륨 과정을 거쳐 다음과 같이 구성 하였습니다.
  32. 이번에는 준비된 Disk를 클러스터 리소스로 추가해야 합니다. 클러스터 저장소에서 "디스크 추가"를 실행합니다.
  33. 클러스터 디스크 1~3까지 보이는것을 확인 후 "확인" 버튼을 누릅니다.
  34. 이제 클러스터 리소스에 디스크가 추가 되었습니다. 저는 클러스터 디스크 1,2,3이라고 등록된 이름을 좀 더 보기좋게 변경 하였습니다.
  35. 클러스터 이름에서 마우스 오른쪽 버튼을 클릭해 "기타작업 – 클러스터 쿼럼 설정 구성"을 실행합니다.
  36. 쿼럼 구성 선택. 저는 "노드 및 디스크 과반수"를 선택 하였습니다. 아래쪽의 "쿼럼 구성에 대한 자세한 정보" 링크를 통해 상세 내용을 확인할 수 있습니다.
  37. 쿼럼 디스크를 선택해 줍니다.
  38. 다음~ 다음~ 해 주시면 쿼럼 구성이 완료 됩니다.
  39. 다음은 DTC 구성을 하겠습니다. "서비스 및 응용 프로그램 – 서비스 또는 응용 프로그램 구성"을 실행합니다.
  40. DTC를 선택한 다음 다음 단계로 넘어갑니다.
  41. DTC이름과 IP를 입력합니다. 저는 이름을 CWIN2K8R2-DTC , IP를 192.168.1.11로 주었습니다.
  42. DTC 디스크를 선택 후 다음 단계로 진행합니다.
  43. 이제 DTC 구성이 완료 되었습니다.
  44. 서비스 및 응용 프로그램 – CWIN2K8R2-DTC 에서 마우스 오른쪽버튼을 클릭해 리소스가 잘 넘어가는지 한번 테스트 해 봅니다.
  45. 리소스가 잘 넘어가면 현재 소유자가 Node2가 된것을 확인할 수 있습니다.

 

 

단계 5. SQL 설치

  1. 먼저 설치할 SQL Server 이미지 또는 DVD가 필요합니다.
  2. 클러스터 구성 단계에서 IP를 설정하면서 WIN2K8R2-AD 노드의 Internal network를 192.168.0.50으로 설정해 두었습니다. Host 장비의 네트워크를 보면 internalNetwork라는게 있습니다. 여기와 IP 대역을 맞춰주면 HostPC와 guestPC간에 통신을 할 수 있게 됩니다.
  3. 이 네트워크의 IP를 192.168.0.253으로 맞춰 줍니다. 게이트웨이나 DNS는 필요 없습니다.
  4. 이제 host PC에서 guest PC로 연결할 수 있는 네트워크가 구성 되었습니다. Host PC에서 "\\192.168.0.50\c$" 이런식으로 WIN2K8R2-AD 장비로 네트워크 접근이 되는지 확인합니다. 물론 Node1 , Node2에 각각 Internal network를 구성하여 사용해도 상관없을듯 합니다~
  5. SQL Server를 설치하려면 .Net 3.5가 필요한데요. 기능추가를 하셔서 .Net Framework 3.5.1을 설치해 줍니다.
  6. 이제 SQL Server를 설치할 차례입니다. SQL Server 2008 부터는 클러스터 설치시 첫번째 Node를 설치 후 나머지 Node를 Add하는 방식으로 설치하게 됩니다. 먼저 Host PC에서 Node1의 설정으로 갑니다. (나중에 Node2는 Add하여 설치할 예정임)
  7. 저는 이미지를 준비해서 이미지 파일을 선택해 주었습니다. 이제 Node1 장비의 DVD-ROM에 SQL Server이미지 파일이 들어갔습니다.
  8. SQL Server 설치 전에 클러스터 저장소에서 SQL Disk가 설치하려는 장비인 Node1으로 가 있는지를 확인합니다.
  9. 만일 Node2번으로 가 있다면 Node2번의 클러스터 서비스를 중지해서 강제로 Node1로 Disk 리소스가 넘어가도록 합니다.
  10. 드디어 Setup을 시작하고.. "SQL Server 장애 조치 클러스터 새로 설치"를 선택합니다.
  11. 설치 지원 규칙 체크 완료
  12. 제품키 입력
  13. 사용권 계약서 동의
  14. 설치 지원파일 설치
  15. 설치 지원 규칠 체크 완료
  16. 기능 선택. 저는 그냥 엔진과 관리도구만 설치합니다.
  17. SQL 네트워크 이름을 지정해 줍니다. 저는 대충 CSQL이라고 지었습니다. 혹시 클러스터를 Active-Active로 하실 예정이라면 인스턴스명이 충돌되지 않도록 지정해 주는게 좋겠죠? ㅎㅎ
  18. 클러스터 리소스 그룹 이름 설정이 나옵니다. 저는 그냥 디폴트로 두었습니다.
  19. 클러스터 디스크로 SQL_Disk를 선택해 줍니다.
  20. 네트워크 구성에서는 DHCP체크를 지워주고 고정으로 192.168.1.12를 입력해 줍니다.
  21. 보안 정책은 도메인에서 SQLAdmins 그룹을 하나 생성해서 설정 하였습니다.
  22. 서비스 시작 계정은 귀찮으니 모든 SQL Server 서비스에 동일한 계정 사용으로 할당 했습니다. sqlStarter라는 계정은 이전 단계의 SQLAdmins 그룹에 들어있는 계정입니다. (꼭 SQLAdmins 그룹의 멤버여야 합니다.)
  23. 혼합모드 인증을 선택 후 암호를 입력해 줍니다. 또, "현재 사용자 추가"버튼을 눌러 관리자 지정을 해 줍니다. 데이터 디렉토리 설정이 필요한 경우 탭을 선택하셔서 설정해 줍니다. 저는 그냥 디폴트를 사용하도록 하겠습니다. (테스트 환경인데 귀찮습니다; ^^; )
  24. 이제 다음, 다음 하셔서 사뿐히 규칙 검사를 통과하시면 SQL 설치가 시작됩니다. 설치를 해보시면 stand alone으로 설치를 할때보다 시간이 좀 더 오래 걸리는듯 합니다.
  25. 설치가 완료되면 "서비스 및 응용 프로그램"에서 SQL Server를 확인할 수 있습니다. 현재 Node1에 와 있고 아래쪽에 리소스들이 "온라인" 상태인것을 확인할 수 있습니다.
  26. 이제 다른 노드를 설치할 차례입니다. Node2로 가서 클러스터 노드 추가를 선택해 설치합니다.
  27. 가볍게 "설치 지원 규칙"을 통과하고..
  28. 제품 키 입력
  29. 사용권 계약
  30. 설치 지원 파일 설치
  31. 설치 지원 규칙 통과
  32. 클러스터 노드 구성에서는 인스턴스 이름이 디폴트 인스턴스명이고, 현재 노드는 Node2번인 것을 확인합니다.
  33. 서비스 계정 설정에서 암호를 입력 후 다음 단계로 넘어갑니다.
  34. 다시 한번 규칙 검사를 마치고 나면 "장애 조치 클러스터 노드 추가" 작업이 진행됩니다.
  35. 노드 추가가 완료되고 나면 "서버 관리자"에서 클러스터 노드를 이동시켜 봅니다.
  36. 정상적으로 노드 이동이 완료되었는지 확인합니다. 저는 Node1 -> Node2로 잘 이동된 것을 확인 하였습니다

 

헥헥. 겨우 끝났다~~ 졸라 힘들어요~

 

하만철 / Ha Man cheol

AND

Windows 2008 R2 Hyper-V에서 클러스터 테스트 환경 구성

하만철 (feisia@naver.com)

 

0단계. 구성 계획
AD, Cluster Node 1, 2 이렇게 3개의 VM을 생성하여 테스트 환경 구성

VM Name

Hostname

IP

Win2K8R2-AD

Win2K8R2-AD

192.168.1.50

Win2K8R2-SQL2K8R2-Node1

Win2K8R2-Node1

192.168.1.1

Win2K8R2-SQL2K8R2-Node2

Win2K8R2-Node2

192.168.1.2

 

 

1단계. Hyper-V 준비

  1. 당근 Windows 2008 Server가 필요합니다. Hyper-V는 Windows 2008부터 사용 가능합니다.
  2. 관리도구 – 서버 관리 실행
  3. 서버 관리에서 역할 – 역할 추가 실행
  4. Hyper-V 역할에 체크한 다음 "설치"를 수행
  5. 설치가 정상적으로 완료 되시면 다음과 같이 역할에서 Hyper-V를 확인할 수 있습니다.
  6. 왼쪽의 역할 – Hyper-V – Hyper-V Manager – 장비명 – 마우스 우클릭 – New – Virtual Machine을 선택
  7. Virtual Machine 명을 입력. 저는 Win2K8R2-AD라고 입력 했습니다.
  8. 사용할 메모리는 디폴트인 512MB로 하였습니다.
  9. 네트워크은 일단 Not Connected로 선택합니다. 일단 윈도 설치 후 다시 조정 가능합니다.
  10. 이미지 파일의 이름과 위치, 용량을 정합니다. 용량의 경우 127GB로 지정 하더라도 처음부터 127GB를 할당하는게 아니라 VM이 데이터를 쓰는만큼 순차적으로 증가하게 되므로 넉넉하게 잡았습니다.
  11. OS 설치는 좀이따 하도록 하고 넘어갑니다.
  12. Finish~ 를 선택하면 VM이 하나 생성되게 됩니다. 현재는 꺼져 있는 상태이네요. ㅎㅎ~
  13. 이제 Hyper-V 이미지 준비는 끝이 났습니다. 앞으로 남은건 여기에 OS를 설치하고, 이걸 통해 나머지 2개의 VM을 생성해 클러스터를 구성하는 것입니다.

 

2단계. VM에 OS설치

  1. Virtual Machine 이름에서 마우스 오른쪽 버튼을 클릭해 "Settings"를 선택합니다.
  2. 왼쪽 메뉴에서 DVD Drive를 선택한 다음 "Image file"을 선택해 준비한 Windows 2008 R2 파일을 선택합니다. DVD 미디어로 사용할 경우 "Physical CS/DVD drive"를 선택해 host장비의 미디어를 geust장비로 맵핑하여 설치 할 수도 있습니다.
  3. 이제 Virtual Machines 목록에서 생성했던 virtual machine을 더블클릭한 다음 아래와 같은 화면에서 왼쪽 상단의 "start"버튼을 클릭해 guest OS를 부팅합니다. 이제 Windows 미디어를 인식해 OS 설치를 시작하게 됩니다.
  4. OS 설정은 별다른게 없으므로 넘어가도록 하겠습니다.
    OS 설치가 다 되었으면 이제 이걸 공장 출하 상태로 돌린 다음 2개의 VM을 더 만들건데요. 저는 보안패치 여러번 하기 귀찮아서 보안패치를 미리 한 다음에 진행 하였습니다.
  5. VM 목록에서 마우스 우클릭 후 "Settings"로 다시 들어갑니다.
  6. Network Adapter 선택 후 오른쪽의 Network에서 Not Connected -> Local Area Connection 으로 변경해 줍니다. 그러면 해당 guest OS의 네트워크가 host의 네트워크를 통해 인터넷을 할 수 있게 됩니다.
  7. "OK"를 누르고 빠져나와 다시 해당 VM으로 가서 인터넷이 되는지 확인해본 다음에 보안 업데이트를 합니다. 저는 한 40개 나오네요. -_-;
  8. 이제 모든 준비가 끝났습니다. 이 설치한 OS를 이제 리팩키징 할 단계입니다. C:\windows\system32\sysprep\sysprep.exe 를 실행합니다.
  9. 시스템 준비 도구에서 아래와 같이 선택 합니다.
  10. 이제 리팩키징 작업이 진행되고 작업이 완료되고 나면 자동으로 VM의 시스템이 종료됩니다.
  11. VM Image파일이 있는 폴더로 가서 기존의 vhd파일(빨간박스)을 복사해 Node1과 Node2의 퐁 파일을 생성합니다. 저는 만일의 사태에 사용하기 위해 clean_Win2KR2라는 폴더에도 하나 따로 복사해 두었습니다. ㅎㅎ
  12. 이제 단계1 의 virtual machine 추가에서 기존의 이미지 파일을 추가하여 다음과 같이 2개의 VM을 더 생성 하였습니다. 진행하면서 다른 부분은 아래와 같이 단계1-10에서 기존의 vhd 파일을 이용하는 부분입니다.
  13. 이제 클러스터에서 사용할 VM 3개가 준비 되었습니다.
  14. 먼저 Win2K8R2-AD를 시작 시킵니다. 아래와 같은 화면이 나오고 간단한 언어 선택, 암호 설정을 마치면 windows 시작이 됩니다.
  15. Windows의 hostname을 변경합니다. 저는 WIN2K8R2-AD로 하였습니다. 다른 2대의 VM도 모두 켜서 hostname을 설정합니다. (저는 WIN2K8R2-Node1 , WIN2K8R2-Node2)
  16. 여기 까지로 VM에 OS를 설치해 준비하는 단계가 완료 되었습니다.

 

단계 3. Cluster 구성1

  1. 왼쪽의 역할 – Hyper-V – Hyper-V Manager – 장비명 – 마우스 우클릭 – Virtual Network Manager 선택
  2. 왼쪽 메뉴에서 New virtual network를 선택하고, 오른쪽에서 Private를 선택한 다음 "Add" 버튼을 클릭해 새로운 네트워크를 생성합니다.
  3. 네트워크 이름을 적고, Private virtual machine network가 선택된 것을 확인한 다음 "OK"버튼을 누르시면 네트워크가 생성 됩니다.
  4. 이런식으로 Private Network 2개와 Internal Network 1개를 생성합니다. 저는 아래와 같이 생성 하였습니다. 이것은 클러스터에서 사용할 public network, private network그룹과 관리 목적으로 host PC와 guest PC간의 통신을 위한 네트워크가 될 예정입니다.
  5. 3개의 VM 설정에서 Network Adapter를 privateNetwork로 선택 합니다. 여러대를 설정 하실때는 왼쪽 위의 select box를 통해 좀 더 손쉽게 할 수 있습니다.
  6. 이제 3개의 VM에서 네트워크 어댑터 이름을 "public"으로 변경해 줍니다. 나중에 하나씩 더 추가할 것이기 때문에 햇갈리지 않게 하기 위해서 입니다.
  7. 각 장비의 IP를 설정해 줍니다. 저는 단계0의 구성대로 3개 VM의 IP를 설정했고, 게이트웨이는 192.168.1.254 , DNS는 192.168.1.50으로 하였습니다.
  8. 이제 3대의 VM을 모두 종료 후 네트워크 어댑터를 하나씩 더 추가를 합니다. VM의 설정에 가셔서 Add Hardware – Network Adapter – Add를 선택합니다.
  9. 새로 추가된 어댑터를 AD장비는 internalNetwork로 Node 1, Node2는 privateNetwork2로 지정합니다.
  10. 이제 다시 VM 3대를 켠 다음에 Windows에서 AD장비의 네트워크 어댑터명은 Management, Node의 네트워크 어댑터명은 private로 변경합니다. 또, AD장비의 IP는 192.168.0.50, Node1의 IP는 10.0.0.1, Node2의 IP는 10.0.0.2로 합니다. IP와 Subnetmask만 입력하면 됩니다.
  11. Node1, Node2에서 네트워크 연결의 고급 – 고급 설정으로 갑니다.
  12. 네트워크 순서는 public , private 순서로 맞추고, private 네트워크에서 파일 및 프린터 공유의 체크는 지웁니다.
  13. 이번에는 private IP설정의 고급 버튼을 클릭합니다.
  14. DNS 탭에서 "주 DNS 접미사의 부모 접미사 추가"와 "DNS에 이 연결의 주소를 등록" 체크를 지웁니다.
  15. WINS 탭에서 "LMHOSTS 조회 가능" 체크를 지우고, NetBIOS를 사용안함으로 선택합니다.
  16. 나머지 노드의 private network를 위와 같이 설정합니다.
  17. AD역할을 할 장비에서 AD를 설치할 차례입니다. 관리도구 – 서버 관리자 를 실행합니다.
  18. 왼쪽 상단의 역할 – 마우스 우클릭 – 역할 추가
  19. Active Directory 도메인 서비스를 체크하여 "다음" 버튼을 클릭해 설치합니다. 도중에 .Net관련된 서비스 추가에 대한 물음이 나오는데 설치함을 선택하시면 됩니다.
  20. 설치가 완료되면 역할에 "Active Directory 도메인 서비스"가 생기고, 이걸 선택하신 다음 오른쪽 화면의 "도메인 서비스 설치 마법사"를 클릭합니다.
  21. 별건 없을것 같은데 "고급 모드 설치 사용"을 체크하고 한번 넘어가 보겠습니다.
  22. 새 포리스트에 새 도메인을 만듭니다.
  23. 모메인을 정합니다. 저는 MANHA.COM입니다. 하하하. -_-;
  24. 도메인 NetBIOS는 저는 "MANHA"라고 나왔는데 그냥 두고 "다음"을 눌러 넘어갑니다.
  25. 포리스트 기능 수준 설정이 나오는데 하위 호환성이 제공되지 않는다고 합니다. 저는 Windows 2008 R2로만 구성할 것이기 때문에 "Windows Server 2008 R2"를 선택 했습니다.
  26. 원래 DNS를 설치 후 AD를 설치해야 하는데 DNS를 설치하지 않았기 때문에 DNS 설치 단계가 나타납니다. 당근 설치를 해야 하므로 "DNS 서버"에 체크를 하고 다음으로 넘어갑니다.
  27. 경고 메세지가 뜨는데 그냥 무시하고 다음, 다음을 해서 폴더설정, 암호 설정을 마치고 나면 AD설치가 완료되게 됩니다. 시간이 좀 걸리며 완료 후 리붓을 하게 됩니다.
  28. 리붓 후 관리도구 – Ac tive Directory 사용자 및 컴퓨터 를 실행합니다. 그리고 Users – 새로 만들기 – 사용자를 실행해 관리용 계정을 하나 따로 만듭니다.
  29. 저는 feisia라는 계정을 만들었고, 마우스 오른쪽을 클릭해 "그룹에 추가"를 선택 후 Domain Admins 그룹에 추가해 줍니다.
  30. 이제 Node1 , Node2를 방금 생성한 AD에 가입시킬 차례입니다. 먼저 Node1번 VM으로 가서 도메인에 가입시켜 줍니다. 저는 MANHA.COM으로 가입을 시킵니다. 가입시킬때 도메인 가입 권한이 있는 계정을 요청하는데 좀전에 만든 도메인 관리용 계정을 이용해 가입을 시켜줍니다.
  31. AD에 가입을 시키면 리붓을 하는데 리붓 후 로그인 할때는 이전 단계에서 추가한 관리자 계정으로 로그인 해 줍니다. 이제 클러스터 서비스를 추가할건데 이때 도메인 계정으로 로그인하는 작업이 필요합니다.




음.. 이번주에 다 적을수 있을줄 알았는데 캡춰해서 붙이는것만 해도 시간이 너무 오래 걸리네요 -_-;
다음주에는 나머지 클러스터 구성과 SQL 설치를 추가하도록 하겠습니다~

하만철 / Ha Man-cheol


AND


아.. 거의 3주동안 이걸로 삽질을 한거 같다.
Windows 2008 R2에서 Hyper-V로 guestOS 3개 만들어서 드디어 클러스터 구성을 완료!!!
마지막까지 iSCSI 초기자 때문에 삽질 하다가 드디어 디스크 구성 완료 하여서 모든 리소스 구성을 완료했다.

테스트 환경 구축하면서 방화벽, AD, 권한, iSCSI software Disk, windows sid 등 계속해서 문제가 생겨서 해결이 안되었는데... 우하하하하. 다끝났다.
이제 SQL만 올리면 되지만 이거야 뭐 맨날 하던거니 껌이겠지~
끝까지 다 구성해본 다음에 매뉴얼이라도 한번 만들던지 해야겠다.

오늘은 발뻗고 잘 수 있을듯!!! 아쏴~ +_+

AND


이것은 linked server 수행을 별도 프로세스로 띄우는 방식으로 OOP(Out-of-process)라고도 합니다.

이것은 다음 경우에 도움이 될 수 있을것 같습니다.
1. x86의 경우 프로세스당 메모리를 2GB까지밖에 사용할 수 없는데 linked server에서 많은 메모리가 필요할 때
2. Linked server가 문제를 발생시키는데 Inprocess라 SQL Service까지 영향을 받을 경우 linked server process만 죽일 수 있습니다.

하지만 별도 프로세스를 사용할 경우 MySQL과 연결할때는 text 컬럼에 대해 insert, delete, update시 문제가 발생할 수 있고,
oracle과 사용할 경우 oracle provider를 실행하기 위한 권한 설정이 추가로 필요할 수 있다고 합니다.
때문에 사용하기 전에 테스트 환경에서 문제가 없는지 확인이 필요해 보입니다.

@설정 방법
아래와 같이 SSMS에서 "서버 개체 – 연결된 서버 – 공급자 – MSDASQL"의 속성에서 "Inprocess 허용"을 체크 해제 합니다.



설정 후 연결된 서버가 사용될 때 마다 아래와 같이 "dllhost.exe" 프로세스가 별도로 수행되는것을 확인할 수 있습니다.
물론 연속적으로 연결된 서버를 사용하는 쿼리가 수행되면 하나의 프로세스를 사용하게 됩니다. (여러개 프로세스가 뜨지 않습니다! ^^; )

 

하만철 / Ha Man-cheol

AND

SQL 2005 이상에서 SP의 성능을 확인할 수 있는 모니터링 쿼리를 작성해 보았습니다.
이것은 SP가 성능 문제를 발생할 경우 어떤 SP인지 찾고 싶을 때 유용하게 사용할 수 있습니다.

-- 캐시 비우기
-- DBCC FREEPROCCACHE

-- 쿼리 통계 중 SP 관련 데이터 확인
-- SQL 2005에서는 SP 단위의 통계를 수집하는 DMV가 없기 때문에 statement의 정보를 GROUP BY하여 확인해야 함
SELECT QS.*
,         DB_NAME(ST.dbid) as dbName
,         OBJECT_NAME(ST.objectid, ST.dbid) as objectName
--,       ST.*
FROM (
          SELECT TOP 5 sql_handle
          ,         MAX(execution_count) as execution_count
          ,         SUM(last_logical_reads) as last_logical_reads
          ,         SUM(max_logical_reads) as max_logical_reads
          ,         SUM(total_logical_reads) as total_logical_reads
          FROM sys.dm_exec_query_stats
          GROUP BY sql_handle
          ORDER BY SUM(last_logical_reads) DESC
) AS QS
          CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE ST.objectid IS NOT NULL
ORDER BY QS.last_logical_reads DESC



-- SQL 2008에서 새로 추가된 DMV를 이용하여 확인
SELECT TOP
          DB_NAME(database_id) as dbName
,         OBJECT_NAME(object_id, database_id) as objectName
,         *
FROM sys.dm_exec_procedure_stats
ORDER BY last_logical_reads DESC
 

-- 확인된 쿼리의 text plan은 다음의 2 DMV에서 확인
sys.dm_exec_query_plan ( plan_handle )
sys.dm_exec_sql_text(sql_handle | plan_handle)
 

하만철 / Ha Man-cheol

AND

@@version

Microsoft SQL Server 2008 (SP1) - 10.0.2775.0 (X64)

       Apr 30 2010 14:31:04

       Copyright (c) 1988-2008 Microsoft Corporation

       Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

 

--DROP TABLE T1

--TRUNCATE TABLE T1

 

-- 테이블 생성

create table t1 (

  col1 int

, b1 bit

, col2 varchar(1000)

, b2 bit)

 

-- 데이터 입력

insert into t1 select 255, 1, 'abcde', 1

go 100

 

-- dbid objectid 확인

select db_id('dba'), object_id('t1')

 

-- 페이지 번호(PID) 확인

dbcc ind (5, 421576540, 0)

 

-- 페이지 내용 확인

dbcc traceon(3604)

dbcc page(5, 1, 174, 2)

 

 

-- 페이지 내용 일부

090CC0B0:   62636465 30000900 ff000000 eb0400f0 †bcde 0... .... ....        

090CC0C0:   01001500 61626364 65300009 00ff0000 †.... abcd e0.. ....        

 

30         0 -- statusA

00         0 -- statusB

0900       9 --고정길이 컬럼의 길이+4 (2 bit 컬럼이 합쳐져서 1byte 사용함)

ff000000   255 -- 고정길이컬럼데이터int 255

eb         하위 2bit 11 -- bit 컬럼의 데이터 (2 bit 컬럼이 합쳐져서 1byte 사용함)

0400       4 -- 컬럼수

f0         하위 4bit 0000-- null 허용컬럼 bitmap

0100       1 -- 가변길이컬럼수

1500       21 -- 가변길이컬럼오프셋배열

61         a -- 가변길이컬럼데이터1

62636465   bcde -- 가변길이컬럼데이터2

 

이 상태에서 bit컬럼을 추가할 경우를 포함하여 8bit 컬럼이 넘어서 합계 1byte를 넘기 전에는 1byte를 사용하게 됨.

 

 

하만철 / Ha Man-cheol

 

 

AND

데이터 사이즈가 커지면서 여러 가지 이유로 이중화 솔루션이 필요하게 됩니다. SQL Server에서 제공하는 이중화 솔루션은 어떤 것들이 있는지 알아 보고 향후 이중화 솔루션에 대해 고려해야 할 때 조금이나마 도움이 되었으면 좋겠습니다.

 

목차.

1.     복제(Replication)

2.    미러링(Mirroring)

3.    로그 전달(Log Shipping)

4.    MSCS(MS Cluster Service)

 

 

1. 복제(Replication)

복제는 한 데이터베이스에서 다른 데이터베이스로 데이터와 데이터베이스 개체를 복사 및 배포한 다음 데이터베이스 간에 동기화를 수행하여 일관성을 유지하는 기술입니다. 복제된 대상 DB는 온라인 상태를 유지하게 되므로 다른 일련의 역할을 수행할 수 있게 됩니다.

 

복제의 논리적 구조 (잡지의 개념으로 생각하면 이해가 쉽습니다.)

- 잡지사(게시자)에서는 하나 이상의 출판물(게시)을 생산합니다.

- 출판물(게시)에는 하나 이상의 기사(아티클)가 있습니다.

- 잡지를 배포하는 배급업자(배포자)가 있습니다.

- 구독자는 각자가 구독하는 출판물(게시)을 받아 봅니다.


 

SQL Server에서 제공하는 복제 솔루션

트랜잭션 복제

Transaction Replication

- 가장 많이 사용되는 유형으로 데이터 변경에 대한 로그를 읽어 비동기적으로 데이터를 대상DB에 반영.

- 동일한 데이터가 5회 변경될 경우 마지막 데이터가 아닌 5번의 변경 모두가 대상 DB에 각각 반영됨.

- 기본적으로 row 단위로 데이터가 반영.(한번에 5건을 업데이트 하는 쿼리 수행시 5번의 업데이트 단일 row 업데이트 쿼리로 변경되어 동기화가 진행)

- 다른 복제에 비해 짧은 동기화 시간을 가짐.

- 게시자 또는 구독자가 Oracle인 경우 사용 가능함.

스냅숏 복제

Snapshot Replication

- 지정한 개체의 모든 데이터를 대상DB에 반영.

- 동기화가 진행되는 동안 구독자의 데이터를 사용할 수 없음.

- 지정한 개체의 사이즈가 큰 경우 동기화에 시간이 오래 걸림.

- 데이터 사이즈가 작고, 많은 업데이트가 발생하는 경우 유용함.

- 사용예시 : 슈퍼마켓 지점과 본점의 데이터를 매일 새벽 동기화.

병합 복제

Merge Replication

- 게시자와 구독자 모두가 데이터 변경을 할 수 있음.

- 동일한 데이터가 5회 변경될 경우 변경 데이터의 마지막 값을 가지고 있다가 동기화시 마지막 값을 대상 DB에 반영함.

- 트리거를 통하여 변경되는 행의 대해 마지막 변경값을 시스템 테이블로 관리함.

- 충돌 발생시 지정한 우선순위에 따라 어떤 값을 반영할지 결정함.

- 사용예시 : PDA PC간의 데이터 동기화

P2P 복제

P2P Replication

- 모든 노드가 게시와 구독의 역할 모두를 수행함.

- 중간의 1개 노드가 장애 발생시에도 다른 노드를 통하여 데이터 동기화 하여 복제 가용성을 높일 수 있음.

- 양방향 트랜잭션 복제와 유사한 형태로 동작함.

 

 

2. 미러링(Mirroring)

- 미러링은 데이터베이스 가용성을 높이기 위한 소프트웨어 솔루션입니다.

- 미러링은 데이터베이스 단위로 구현되며 전체 복구 모델을 사용하는 데이터베이스에서만 작동합니다.

- 미러링은 데이터베이스를 제공하는 주서버(Principal Server)와 장애 조치를 위한 미러서버(Mirror Server)로 기본구성을 하며, 운영 모드에 따라 모니터 서버(Witness Server)를 추가하여 장애 발생시 자동 장애조치(Failover)를 수행할 수 있습니다.

 

미러링의 구성도

 

미러링의 운영 모드

SAFETY FULL

보호 우선 모드

- 모니터 서버를 두어 주 서버에서 장애 발생시 자동으로 미러 서버가 주 서버의 역할을 하도록 자동 장애조치를 지원함.

- 주 서버와 미러 서버의 트랜잭션이 동기적으로 수행되어 변경된 데이터가 미러 서버에도 반영 되어야 주 서버의 Commit이 완료됨.
-
트랜잭션이 두 파트너에서 모두 커밋되지만 트랜잭션 대기 시간이 길어짐

SAFETY OFF

보호 우선 모드

- “SAFETY FULL 보호 우선 모드에서 모니터 서버가 없는 운영모드.

성능 우선 모드

- 주 서버와 미러 서버의 트랜잭션이 비동기적으로 수행되어 미러 서버의 Commit을 기다리지 않고 주 서버의 Commit이 완료됨.

- 비동기 작업을 통해 주 서버는 최소 트랜잭션 대기 시간으로 실행될 수 있지만 데이터가 손실될 수 있는 위험이 있음.

 

미러링의 또다른 기능

- 스냅샷을 통한 조회 : 미러 서버의 데이터베이스는 일반적으로 오프라인 상태가 유지됩니다. 하지만 데이터베이스 스냅숏 기능을 이용하여 데이터베이스를 읽을 수 있는 시점을 지정해 읽기 전용으로 사용할 수 있습니다.

- 로그 스트림 압축 : SQL Server 2008 부터는 주 서버에서 미러 서버로 데이터를 보내기 전에 로그 스트림을 압축할 수 있습니다.

- 페이지복원 : 미러링에 참여중인 데이터베이스는 데이터 페이지를 읽지 못하게 하는 특정 오류 유형을 자동으로 해결하려고 시도합니다. 페이지를 읽지 못하는 파트너는 다른 파트너로부터 새 복사본을 요청합니다. 이 요청이 성공하면 읽을 수 없는 페이지는 새 복사본으로 대체되고 일반적으로 오류가 해결됩니다.

 

 

3. 로그 전달(Log Shipping)

로그 전달을 사용하면 주 서버 데이터베이스에서 별도의 보조 서버에 있는 하나 이상의 보조 데이터베이스로 트랜잭션 로그 백업을 자동으로 보낼 수 있습니다. 트랜잭션 로그 백업은 각 보조 데이터베이스에 개별적으로 적용됩니다. 모니터 서버라고 하는 선택적인 세 번째 서버는 백업과 복원 작업의 기록 및 상태를 기록하고 예약된 대로 작업이 실행되지 않으면 선택적으로 경고를 발생시킬 수 있습니다.

 

로그 전달의 구성도

로그 전달의 특징

- 로그 전달 구성은 자동으로 주 서버에서 보조 서버로 장애 조치(Failover)되지 않습니다. 주 데이터베이스를 사용할 수 없을 경우 수동으로 임의의 보조 데이터베이스를 온라인 상태로 전환할 수 있습니다.

- 보조 서버의 데이터베이스는 일반적으로 읽기 전용 상태를 유지하지만 복원 작업 진행시 모든 연결이 끊어지게 됩니다.

- SQL Server 2008 부터는 압축 백업을 이용하여 좀 더 빠르게 로그 전달을 수행할 수 있습니다.

 

 

4. MSCS(MS Cluster Service)

SQL Server 장애 조치 클러스터는 Windows Server 장애 조치 클러스터 위에 구축되어 전체 SQL Server 인스턴스에 고가용성을 제공합니다.

SQL Server 장애 조치 클러스터는 네트워크에서 한 대의 컴퓨터처럼 보이지만 현재 노드를 사용할 수 없을 때 노드 간 장애 조치(Failover) 기능을 제공합니다. 예를 들어 디스크 이외의 하드웨어 오류, 운영 체제 오류 또는 계획된 운영 체제 업그레이드 작업 중에 다른 노드에서 SQL Server 인스턴스를 구성하여 서비스를 유지할 수 있습니다. 그러나 장애 조치 클러스터는 디스크 오류에 대한 대비책은 아닙니다.

 

MSCS의 구성도

 

- Heartbeat 라인을 통하여 각 노드의 상태를 서로 체크하게 됩니다.

- 외부에서는 항상 동일한 Virtual IP를 바라보며, 어떤 노드가 Active한지를 알 필요가 없습니다.

- 공유 스토리지를 사용하기 때문에 데이터 동기화의 비용이 들지 않습니다.

- 구성 방식에 따라 각 노드를 Active-Passive , Active-Active로 구성할 수 있으며, 최대 16개 노드를 구성할 수 있습니다.

 

 

참고자료.

http://msdn.microsoft.com/ko-kr/library/bb500348.aspx

http://msdn.microsoft.com/ko-kr/library/bb522583.aspx

http://www.microsoft.com/korea/sqlserver/2008/whats-new.aspx

 


하만철 / Ha Man-cheol

AND


 

@ 상황

t1 테이블을 replA DB -> replB DB 복제하고 있습니다.

여기에 t2 테이블을 복제에 추가하려고 하는데 관계없는 t3 테이블의 대기 현상에 의해 t2 테이블을 아티클로 추가할 없는 현상이 발생합니다.

 

 

-- 1. 테스트를위해replA , replB DB초기값세팅

create database replA

create database replB

 

use replA

go

create table t1 (col1 int not null, col2 int)

create table t2 (col1 int not null, col2 int)

create table t3 (col1 int not null, col2 int)

alter table t1 add constraint pk_t1 primary key (col1)

alter table t2 add constraint pk_t2 primary key (col1)

alter table t3 add constraint pk_t3 primary key (col1)

GO

 

use replB

go

create table t1 (col1 int not null, col2 int)

create table t2 (col1 int not null, col2 int)

create table t3 (col1 int not null, col2 int)

alter table t1 add constraint pk_t1 primary key (col1)

alter table t2 add constraint pk_t2 primary key (col1)

alter table t3 add constraint pk_t3 primary key (col1)

 

 

-- 2. t1 테이블을replA DB -> replB DB로복제추가

한장비에서 테스트를 하며, 배포 구성은 되어있다고 가정합니다.

현재 테스트에 사용하는 SQL 2008 TTEMPDB09-TEST\SQL2008 라는 인스턴스 입니다. ^^

Use master

exec sp_replicationdboption @dbname = N'replA', @optname = N'publish', @value = N'true'

exec [replA].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1

exec [replA].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1

GO

 

-- 트랜잭션게시를추가하는중

use [replA]

exec sp_addpublication @publication = N'pubt1', @description = N'게시자''TTEMPDB09-TEST\SQL2K8''의데이터베이스''replA''에대한트랜잭션게시입니다.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

exec sp_addpublication_snapshot @publication = N'pubt1', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

exec sp_grant_publication_access @publication = N'pubt1', @login = N'sa'

exec sp_grant_publication_access @publication = N'pubt1', @login = N'NT AUTHORITY\SYSTEM'

exec sp_grant_publication_access @publication = N'pubt1', @login = N'TTEMPDB09-TEST\Dkfhskals!#'

exec sp_grant_publication_access @publication = N'pubt1', @login = N'distributor_admin'

-- 트랜잭션아티클을추가하는중

exec sp_addarticle @publication = N'pubt1', @article = N't1', @source_owner = N'dbo', @source_object = N't1', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N't1', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_dbot1]', @del_cmd = N'CALL [dbo].[sp_MSdel_dbot1]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dbot1]'

-- 트랜잭션구독을추가하는중

exec sp_addsubscription @publication = N'pubt1', @subscriber = N'TTEMPDB09-TEST\SQL2K8', @destination_db = N'replB', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'pubt1', @subscriber = N'TTEMPDB09-TEST\SQL2K8', @subscriber_db = N'replB', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'

GO

 

 

 

-- 3. 복제에사용되는SP 생성

-- 아래 쿼리의 결과 스크립트를 replB DB에서 수행하여 SP 생성해 준다.

use replA

go

sp_scriptpublicationcustomprocs @publication = 'pubt1'

 

 

-- 아래 쿼리를 통해 데이터가 넘어가는지 확인해 본다.

use replA

go

-- 데이터 입력

insert into t1 select 1,1

 

-- 복제 되었는지 확인

select * from replA..t1

select * from replB..t1

 

 

 

. 복제가 넘어간다면 이제 준비는 모두 되었습니다.

이제 t2 테이블을 복제에 추가할꺼고, t3 테이블에는 스키마 락을 있는 동작을 하나 보겠습니다.

저는 트랜잭션을 걸고 인덱스를 하나 생성 하였습니다.

물론 commit , rollback 명령은 수행하지 않고 계속 Lock 잡고 있도록 합니다.

 

begin tran

create index idx_t3 on t3 (col2)

 

-- rollback

 

 

 

다른 세션을 생성하여 복제에 아티클을 하나 추가해 보겠습니다.

 

-- 트랜잭션 아티클을 추가하는

use [replA]

exec sp_addarticle @publication = N'pubt1', @article = N't2', @source_owner = N'dbo', @source_object = N't2', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N't2', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_dbot2]', @del_cmd = N'CALL [dbo].[sp_MSdel_dbot2]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dbot2]'

GO

exec sp_addsubscription @publication = N'pubt1', @subscriber = N'TTEMPDB09-TEST\SQL2K8', @destination_db = N'replB', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

GO

 

-- use [replA]

-- exec sp_dropsubscription @publication = N'pubt1', @article = N't2', @subscriber = N'all', @destination_db = N'all'

-- GO

-- use [replA]

-- exec sp_droparticle @publication = N'pubt1', @article = N't2', @force_invalidate_snapshot = 1

-- GO

 

 

sp_addarticle 수행시 추가하려는 t2 아닌 t3 테이블의 대기에 의해 t2 테이블이 아티클로 추가되지 않습니다. (제거는 됩니다. ^^;)

혹시나 이때 대기가 발생한 것을 알지 못하고 시간이 오래 걸린다고 sp_addarticle 수행중이던 세션에서 중지 버튼을 누르셨다면..

t3 테이블에 대기 걸고 있던 작업은 끝난 상태에서 다시 복제를 추가하려고 한다면 아래 오류를 만나실 있습니다.

메시지14030, 수준16, 상태1, 프로시저sp_MSrepl_addarticle, 1263

아티클't2'() 게시'pubt1'에있습니다.

메시지15002, 수준16, 상태1, 프로시저sp_MSrepl_addsubscription, 388

트랜잭션내에서프로시저'sp_addsubscription'() 실행할수없습니다.

이때는 당황하지 마시고 rollback 수행 주시면 됩니다.

아티클을 추가하면서 트랜잭션이 시작되고 중지 버튼에 의해 자동으로 rollback 하지 못하여 발생하는 문제 입니다.

rollback 다시 아티클을 추가하면 추가되는 것을 확인할 있습니다.

 

이 현상은 SQL 2000에서는 발생하지 않는 것을 확인 하였습니다.

아마도 SQL 2008에서는 복제 추가시 달라진 부분이 있는 것 같습니다.

복제 작업을 다른 작업과 병렬로 수행해야 할 때 조심해야 할 것 같습니다.

 


== 추가 내용 ==
트레이스 걸어보면 아래와 같이 쿼리를 조회 하면서 대기가 발생 하는것을 확인할 수 있습니다.
시스템 테이블에 인덱스가 필요해 보입니다.

IF EXISTS (SELECT so1.name
                    FROM sys.objects as so1
                        join sys.objects as so2
                            on so2.object_id = so1.parent_object_id
                   WHERE so1.parent_object_id = @tabid
                   AND so1.type = 'PK'
                   AND so2.type = 'U')

 하만철 / Ha Man-cheol

 

AND

안녕하세요. 하만철입니다.

SQL 2008부터 힙 테이블을 관리하는 방식이 변경된 것으로 보입니다.
데이터 삭제와 관련된 내용인데요. SQL 2000, 2005와 같이 이전 버전에서는 힙 테이블에 데이터를 입력한 다음 DELETE로 삭제하면 삭제된 공간을 재사용 할 수 있도록 관리 하였는데 SQL 2008의 힙 테이블에서는 DELETE로 삭제한 공간을 재사용할 수 없습니다.
때문에 INSERT , DELETE가 빈번한 힙 테이블은 실제 데이터가 몇건 없더라도 큰 공간을 차지할 수 있습니다.
또, 이 테이블을 조회하는 쿼리가 테이블 스캔을 하는 경우 힙 테이블 구조상 더블링크드 리스트가 없기 때문에 IAM페이지를 통하여 할당된 공간 전체를 스캔하여 소량의 데이터가 있더라도 테이블 스캔에 많은 IO가 발생할 수 있습니다.
이때는 클러스터드 인덱스를 생성, 사용 쿼리에 인덱스 힌트 추가 또는 주기적으로 TABLE REBUILD를 하여 이런 문제를 해결할 수 있습니다.

select @@VERSION

Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (Intel X86)

       Nov  9 2009 16:59:31

       Copyright (c) 1988-2008 Microsoft Corporation

       Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

 

USE [master]

GO

 

-- DB생성

CREATE DATABASE [TESTDB] ON  PRIMARY

( NAME = N'TESTDB_Data', FILENAME = N'D:\MSSQL\DATA\TESTDB_Data.MDF' , SIZE = 7000 , MAXSIZE = UNLIMITED, FILEGROWTH = 500 )

 LOG ON

( NAME = N'TESTDB_Log', FILENAME = N'D:\MSSQL\DATA\TESTDB_Log.LDF' , SIZE = 100 , MAXSIZE = UNLIMITED, FILEGROWTH = 500 )

GO

 

ALTER DATABASE [TESTDB] SET RECOVERY SIMPLE

GO

 

 

-- 테이블생성

use TESTDB

GO

 

CREATE TABLE [dbo].[tA](

 [col1] [int] NOT NULL,

 [col2] [smallint] NOT NULL,

 [col3] [varchar](20) NOT NULL,

 [col4] [varchar](400) NOT NULL,

 [col5] [int] NULL

) ON [PRIMARY]

GO

 

 

-- 테이블 데이터 입력

insert into tA with(TABLOCK)

select top 100000 ROW_NUMBER() over(order by (select 1)) r

,      ROW_NUMBER() over(order by (select 1)) % 32000 r

,      left(isnull(A.name, 'aaa') , 20)

,      isnull(A.name, 'bbb')

,      ROW_NUMBER() over(order by (select 1)) r

from sysindexes A

       , sysindexes B

       , sysindexes C

 

--넌클러스터드인덱스 생성(넌클인덱스가 있는 경우 인덱스로 할당된 공간은 정상적으로 반환함)

--alter table tA add CONSTRAINT [PK_tA] PRIMARY KEY NONCLUSTERED ([col1] ASC,[col2] DESC,[col3] ASC)

--CREATE NONCLUSTERED INDEX [IX_tA] ON [dbo].[tA] ([col1] ASC,[col3] ASC,[col2] ASC)

 

-- 데이터 모두 삭제

delete top (1000)

from tA

go 100

 

 

데이터 입력과 삭제를 반복 할때마다 계속해서 할당 공간이 증가하는것을 확인할 수 있습니다.

저는 테스트를 위해 300회 정도를 수행해 보았습니다. 

   

 

-- 할당된 공간 확인(건수가 0건인데 약 1.7GB정도 할당됨)

sp_spaceused tA

tA     0     1773456 KB   1773448 KB   8 KB   0 KB

 

 

-- 쿼리조회시IO 확인 (건수는 0인데 논리적 읽기수가 약 22만 페이지로 나옴)

set statistics io on

select * from tA

set statistics io off

 

(0 행이 영향을 받음)

테이블 'tA'. 검색 1, 논리적 읽기 221681, 물리적 읽기 2558, 미리 읽기 162886, LOB 논리적 읽기 0, LOB 물리적 읽기 0, LOB 미리 읽기 0.

 

 

-- 해결방법

1. alter table tA rebuild

   테이블 리빌드를 통하여 사용하지 않는 공간을 반환함.

 

2. create clustered index CL_tA on tA (col1)

   힙 테이블에 클러스터드 인덱스를 생성함.

 

3. select top 1 * from tA with(index(인덱스명))

   조회 쿼리에서 테이블 스캔을 하지 않도록 인덱스 힌트를 추가함. 



하만철 / Ha Man-cheol

AND