• 아티클

일반 T-SQL 쿼리 수집기 유형

일반 T-SQL 쿼리 수집기 유형은 사용자 제공 Transact-SQL 문을 입력 매개 변수로 실행하고 쿼리의 출력을 저장한 다음 출력을 관리 데이터 웨어하우스에 업로드합니다. 이 수집기 유형은 관리 데이터 웨어하우스의 core.supported_collector_types 뷰에 등록됩니다.

이 수집기에는 다음과 같은 입력 매개 변수가 있습니다.

  • Value - Transact-SQL 쿼리입니다. 두 개 이상의 쿼리를 입력으로 제공할 수 있습니다.

  • OutputTable - 쿼리 결과를 관리 데이터 웨어하우스에 업로드하기 전에 해당 결과를 저장할 테이블의 이름을 제공합니다.

  • Database - 쿼리를 실행할 대상 데이터베이스를 지정합니다. 특정 데이터베이스 이름을 지정하거나, * 와일드카드 문자를 사용하여 서버의 모든 데이터베이스를 지정할 수 있습니다. Database의 값을 제공하지 않으면 모든 시스템 데이터베이스에 대해 쿼리가 실행됩니다.

일반 T-SQL 쿼리 입력 스키마

일반 T-SQL 쿼리 수집기 입력에 대한 스키마는 다음과 같습니다.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="DataCollectorType">
  <xs:element name="TSQLQueryCollector">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Query" minOccurs="1" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Value" type="xs:string" />
              <xs:element name="OutputTable" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Databases" minOccurs="0" maxOccurs="1">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Database" minOccurs="0" maxOccurs="unbounded" type="xs:string" />
            </xs:sequence>
            <xs:attribute name="UseSystemDatabases" type="xs:boolean" use="optional" />
            <xs:attribute name="UseUserDatabases" type="xs:boolean" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

다음 코드 예제에서는 스키마의 사용 방법을 보여 줍니다. 전체 코드 샘플은 방법: 일반 T-SQL 쿼리 수집기 유형을 사용하는 사용자 지정 컬렉션 집합 만들기를 참조하십시오.

예 1

다음 예제에서는 시스템 및 사용자 데이터베이스를 쿼리한 다음 그 결과를 custom_snapshots.VerifyDbName 테이블에 저장합니다.

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT DB_NAME() as db_name</Value>
    <OutputTable>VerifyDbName</OutputTable>
  </Query>
  <Databases UseSystemDatabases="true" UseUserDatabases="true" /> 
</ns:TSQLQueryCollector>

예 2

다음 예제에서는 시스템 데이터베이스 3개를 쿼리한 다음 그 결과를 custom_snapshots.MyOutputTable1 테이블에 저장합니다.

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM sys.objects</Value>
    <OutputTable>MyOutputTable1</OutputTable>
  </Query>
  <Databases>
    <Database>model</Database>
    <Database>tempdb</Database>
    <Database>master</Database>
  </Databases>
</ns:TSQLQueryCollector>

예 3

다음 예제에서는 사용자 데이터베이스만 쿼리한 다음 그 결과를 custom_snapshots.MyOutputTable2 및 custom_snapshots.MyOutputTable3 테이블에 저장합니다.

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM sys.dm_tran_database_transactions</Value>
    <OutputTable>MyOutputTable2</OutputTable>
  </Query>
  <Query>
    <Value>SELECT * FROM sys.dm_db_file_space_usage</Value>
    <OutputTable>MyOutputTable3</OutputTable>
  </Query>
  <Databases UseSystemDatabases="false" UseUserDatabases="true" />
</ns:TSQLQueryCollector>

별도의 컬렉션 항목을 사용하는 경우

위 예에서는 모든 쿼리가 같은 데이터베이스 집합에 대해 실행됩니다. 여러 쿼리를 서로 다른 데이터베이스에 대해 실행해야 하는 경우에는 각 쿼리-데이터베이스 조합에 대해 별도의 컬렉션 항목을 만들어야 합니다.

테이블 이름은 같지만 스키마가 다른 두 개의 데이터베이스가 있는 시나리오에서도 서로 다른 컬렉션 항목이 필요합니다. 예 4에서는 이 시나리오를 처리하기 위한 컬렉션 항목을 보여 줍니다.

예 4

첫 번째 데이터베이스 db1에 대한 컬렉션 항목을 만듭니다.

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM mytable</Value> -- Query mytable
    <OutputTable>MyOutputTable1</OutputTable>
  </Query>
  <Databases>
    <Database>db1</Database>
  </Databases>
</ns:TSQLQueryCollector>

두 번째 데이터베이스 db2에 대한 컬렉션 항목을 만듭니다.

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
  <Query>
    <Value>SELECT * FROM mytable</Value> -- Query mytable
    <OutputTable>MyOutputTable2</OutputTable>
  </Query>
  <Databases>
    <Database>db2</Database>
  </Databases>
</ns:TSQLQueryCollector>

처리 및 출력

쿼리 출력의 메타데이터는 사용자가 지정하는 Transact-SQL 문에 따라 달라지지만 데이터 흐름 원본 또는 대상에서의 열 매핑은 디자인 타임에 생성됩니다. 즉, 이 수집기 유형을 사용하는 모든 컬렉션 항목에 대해 런타임에 사용자 지정 패키지를 생성하고 이를 해당 스키마의 형식에 맞게 조정해야 합니다.

새 컬렉션 항목을 받거나 기존 컬렉션 항목에 대해 입력 매개 변수가 업데이트되는 경우 데이터 수집기는 사용자 지정 태스크를 사용하여 컬렉션 패키지와 업로드 패키지를 만듭니다. 이러한 패키지는 다시 사용할 수 있도록 msdb에 저장됩니다.

데이터 컬렉션 및 업로드 동작은 데이터 수집기 아키텍처 및 처리에 설명된 동작과 유사하지만 일반 T-SQL 쿼리 수집기 유형의 경우 약간 다르게 구현됩니다.

일반 T-SQL 쿼리 수집기 유형에서 수집한 데이터를 저장하는 데에는 다음 관리 데이터 웨어하우스 테이블이 사용됩니다.

  • core.snapshots - 이 테이블은 새로운 각 스냅숏을 식별합니다.

  • core.snapshot_timetable - 많은 스냅숏이 거의 동시에 발생할 수 있으므로 스냅숏 시간은 별도의 테이블에 저장됩니다.

  • core.source.info - 이 테이블은 데이터 원본과 데이터에 대한 정보를 저장합니다.

제한 사항

일반 T-SQL 쿼리 수집기 유형을 사용할 때 유의해야 할 제한 사항은 다음과 같습니다.

  • 열 이름 snapshot_time, snapshot_id 및 database_name은 데이터 수집기에 대해 예약되어 있습니다. 사용자 지정 컬렉션 집합에서 만든 테이블에는 이러한 이름을 사용할 수 없습니다. 이러한 이름을 사용하려고 하면 오류가 반환됩니다.

  • sysname 형식의 열은 관리 데이터 웨어하우스로 복사될 경우 nvarchar(128)로 변환됩니다. 데이터 컬렉션 중 SQL Server 2008 Integration Services(SSIS)는 데이터베이스 데이터 형식을 SSIS 데이터 형식으로 변환합니다. 예를 들어 sysname은 DT_WSTR이 되고 nvarchar(len)도 DT_WSTR이 됩니다. 이러한 변환은 OLE DB 원본 데이터 흐름 태스크 내에서 발생합니다. 데이터 업로드 중 데이터 수집기는 캐시에서 데이터를 SSIS 데이터 형식으로 읽습니다. 이 데이터는 기능상 sysname과 동일한 nvarchar(128)로 처리됩니다.

  • char(N) 형식의 열은 관리 데이터 웨어하우스로 복사될 경우 varchar(N)이 됩니다 char(N)은 varchar(N)에 맞춰집니다. varchar 저장소 크기가 가변적인 반면 char 저장소 크기는 고정적이라는 사실을 제외하면 이러한 유형은 기능상 동일하게 처리됩니다.

  • varbinary 형식의 열은 관리 데이터 웨어하우스로 복사될 경우 binary가 됩니다.

  • decimal 형식의 열은 관리 데이터 웨어하우스로 복사될 경우 numeric이 됩니다.

  • nchar 형식의 열은 관리 데이터 웨어하우스로 복사될 경우 nvarchar이 됩니다.

  • sqlvariant 형식은 SSIS가 직접 처리하지 않는 열에 대한 기본 처리에 의해 처리됩니다. 즉, 데이터 공급자에 대한 변환을 수행하지 않고 해당 열이 nvarchar(255)로 처리됩니다.

    [!참고]

    이 경우 열은 기본적으로 255자 길이로 생성되지만 4000자로 변경할 수 있습니다.

  • Transact-SQL 쿼리에서 반환한 모든 열에는 이름이 있어야 합니다. 예를 들어 select 1은 적합하지 않지만 select 1 as one은 적합합니다.

  • 다음 데이터 형식은 SSIS에서 지원되지 않으므로 일반 T-SQL 쿼리 수집기 유형을 사용하는 컬렉션 집합에서 생성한 모든 출력 테이블에 열로 포함될 수 없습니다.

    • image

    • text

    • ntext

    • XML

  • 일반 T-SQL 쿼리 수집기 유형이 실행하는 모든 쿼리는 단일 결과 집합을 반환해야 합니다.

  • 로컬 임시 테이블 쿼리는 처음에 동일한 일괄 처리의 일부로 선언된 경우에만 지원됩니다. 전역 임시 테이블 쿼리는 완벽하게 지원됩니다.

  • 인덱스, 개인 키, 외래 키 또는 기타 다른 제약 조건은 관리 데이터 웨어하우스의 대상 테이블로 전달되지 않습니다. 이는 같은 데이터가 여러 번 쿼리되고 데이터가 여러 컴퓨터에서 단일 테이블로 전달될 수 있기 때문입니다.

  • 지원되는 쿼리 유형에 관한 다른 모든 제한 사항 중 SSIS의 OLE DB 원본 데이터 흐름 태스크에 적용되는 사항은 일반 T-SQL 쿼리 수집기 유형에도 적용됩니다.