남정현의 Windows Azure 블로그

최근 Windows Azure 기반 Social App을 개발하면서 한 가지 문제에 봉착했었습니다. 인위적으로 생성해낸 수십만건의 레코드를 어떤 방법으로 SQL Azure에 Throttling 현상 없이 (SQL Azure 스스로가 성능 향상을 목적으로 과도한 부하를 일으키는 연결이나 불필요하게 장시간 연결되어있는 연결을 임의로 cut하는 동작) 안전하게 모든 데이터를 게시할 수 있는지에 관한 문제였습니다. 그러던 중 SQL Bulk Copy 클래스를 SQL Azure에서 사용하기 위하여 여러가지 시도를 해보던 끝에 아래와 같은 기본적인 지침을 얻을 수 있었습니다.

1. 최상의 결과를 내기 위해서는 SQL Azure 대상 테이블과 원본 테이블 사이의 정의를 가능한한 일치시켜야 한다.

저의 경우, 인위적으로 생성한 수십만건의 레코드를 SQL Azure의 테이블에 게시하기 위하여 여러가지 시도를 해보았지만 데이터 중복에 관한 처리를 정확히 할 수 없어 상당한 시행착오를 겪었습니다. 결국 찾게 된 방법은 ADO.NET이 기본으로 제공하는 In-Memory RDBMS 시스템을 활용하는 것으로, SQL Azure에 게시하면서 유효성 검사를 하지 않고 In-Memory RDBMS에서 미리 유효성 검사를 한 후 최종적으로 산출된 데이터만을 게시하도록 하는 것이었습니다.

아래의 코드는 history_id, template_id, parameter_id, parameter_value라는 4개의 column을 정의하는 테이블로, history_id, template_id, parameter_id column을 한 번에 primary key로 지정하는 방법을 C# 코드로 보여주고 있습니다.

DataTable table = new DataTable();
table.Columns.Add("history_id", typeof(int)).AllowDBNull = false;
table.Columns.Add("template_id", typeof(string)).AllowDBNull = false;
table.Columns.Add("parameter_id", typeof(string)).AllowDBNull = false;
table.Columns.Add("parameter_value", typeof(string)).AllowDBNull = false;
table.Constraints.Add("history_id_pk",
    new DataColumn[] { table.Columns["history_id"], table.Columns["template_id"], table.Columns["parameter_id"] },
    true);

SQL Azure에도 위와 비슷한 형태의 테이블이 이미 게시되어있는 상황에서, SQL Azure에 데이터를 삽입하거나 업데이트 - 또는 - 삭제하면서 유효성 검사가 일어나도록 기다리지 않고 로컬에서 간단한 유효성 검사를 미리 수행하여 네트워크 부하를 최소화하고 작업 속도를 개선할 수 있었습니다.

2. Windows Server 2003 SP2 사용자 필독

클라이언트 - 혹은 - 관리 도구 차원에서 SqlBulkCopy 클래스를 사용하려는 PC의 환경이 Windows Server 2003 SP2인 경우 SQL Azure로의 연결이 아무런 까닭없이 성립되지 않는 이상한 현상을 보일 수 있습니다. 이 경우, 시스템에 KB977291 Hotfix (http://support.microsoft.com/kb/977291/en-us)가 설치되어있는지 확인하시고, 설치되어있지 않은 경우 해당 핫 픽스를 설치한 후 다시 시도해야 합니다.

3. SqlBulkCopy 유틸리티를 사용할 때는 가능한한 명시적으로 사용할 것

애석하게도, 자동화 도구는 편의를 위하여 제공되는 것이지만 편의를 사용자에게 제공하기 위해서는 적정한 설정이 뒷받침되어야만 합니다. SqlBulkCopy 클래스가 그런 유형인데, ColumnMappings 속성, BatchSize 속성, BulkCopyTimeout 속성, DestinationTableName 속성을 정확히 이해하고, WriteToServer 메서드를 호출하기 전에 설정해야 합니다.

SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
bulkCopy.ColumnMappings.Add("history_id", "history_id");
bulkCopy.ColumnMappings.Add("template_id", "template_id");
bulkCopy.ColumnMappings.Add("parameter_id", "parameter_id");
bulkCopy.ColumnMappings.Add("parameter_value", "parameter_value");

bulkCopy.BatchSize = 100;
bulkCopy.BulkCopyTimeout = 100;
bulkCopy.DestinationTableName = "tb_design_image_history";

bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
bulkCopy.WriteToServer(table);

위 코드에서 특별히 중요한 것은 ColumnMappings, BatchSize, DestinationTableName 속성에 관한 부분입니다. ColumnMappings를 사용하여 원본 데이터 소스에서 대상 데이터 소스로 복사할 때의 위치를 결정해야 합니다. 그리고 BatchSize는 SQL Azure가 수용할 수 있는 범위의 값을 사용해야 하며 개인적인 테스트 결과로는 100이 안전한 값인듯합니다. 기본값을 사용하려고 하면 SQL Azure가 연결을 거부할 수 있습니다. 그리고 DestinationTableName 속성에서 대상 테이블 이름이 정확히 지정되어야 올바르게 작동합니다.

마지막으로 WriteToServer 메서드는 상당히 유연한 것입니다. 이미 로컬에 캐시된 데이터 테이블과 그 행의 배열은 물론, 아직 연결이 열린 상태인 다른 데이터 소스에 대한 IDataReader 객체의 사용을 허용합니다. 정의만 서로 정확히 일치하고 맞아떨어진다면 동기화를 비교적 손쉽고 간편하게 할 수 있습니다.

4. 결론

SqlBulkCopy는 개별적으로 INSERT 명령어를 SQL Azure에 실행하는 것보다 훨씬 빠르게 동작했습니다. 덕분에 40여만건 이상의 레코드를 무사히 모두 복제할 수 있었습니다. 하지만 한 가지 중요한 점이 있는데, 개별적으로 INSERT 명령어를 실행하는 것과 달리 SqlBulkCopy 클래스는 중간에 발생하는 데이터베이스 오류를 핸들링할 수 있는 capability가 없으며 문제가 발생하면 작업이 중간에 끊기게 되므로 이를 예방하려면 Transaction을 사용하여 처리 전후 과정을 보완하는 것이 필요합니다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.