Microsoft SQL Server의 데이터 동기화 가이드

고립된 시스템 간에 관련 정보를 공유하는 것은 조직에서 데이터의 품질과 가용성을 향상시킬 수 있기 때문에 점점 더 중요해지고 있습니다. 둘 이상의 디렉토리 서버에서 사용 가능하고 일관된 데이터 세트를 갖는 것이 유용한 상황이 많이 있습니다. 그렇기 때문에 SQL Server 데이터 동기화를 수행하는 일반적인 방법을 아는 것이 중요합니다.

데이터 가용성 및 일관성은 데이터 복제 및 데이터 동기화 프로세스를 통해 달성할 수 있습니다. 데이터 복제는 내결함성 또는 접근성 향상을 위해 데이터베이스의 하나 이상의 중복 복사본을 만드는 프로세스입니다. 데이터 동기화는 둘 이상의 데이터베이스 간에 데이터 일관성을 설정하고 해당 일관성을 유지하기 위해 후속적인 지속적인 업데이트를 수행하는 프로세스입니다.

많은 조직에서 다양한 시스템 간에 데이터 동기화를 수행하는 것은 바람직하면서도 어려운 일입니다. 데이터 동기화를 수행해야 하는 많은 사용 사례를 찾을 수 있습니다.

• 데이터베이스 마이그레이션
• 정보 시스템 간의 정기적인 동기화
• 한 정보 시스템에서 다른 정보 시스템으로 데이터 가져오기
• 다른 단계 또는 환경 간에 데이터 세트 이동
• 데이터베이스가 아닌 소스에서 데이터 가져오기

데이터 동기화를 위한 고유한 방법이나 만장일치로 합의된 방법은 없습니다. 이 작업은 경우에 따라 다르며, 데이터 구조의 복잡성으로 인해 언뜻 보기에는 단순해야 할 데이터 동기화도 복잡해질 수 있습니다. 실제 시나리오에서 데이터 동기화는 수행하는 데 오랜 시간이 걸릴 수 있는 많은 복잡한 작업으로 구성됩니다. 새로운 요구 사항이 나타나면 데이터베이스 전문가는 일반적으로 전체 동기화 프로세스를 다시 구현해야 합니다. 복제 외에 이를 수행하는 표준 방법이 없기 때문에 데이터 동기화 구현이 최적인 경우는 드뭅니다. 이로 인해 유지 관리가 어렵고 비용이 증가합니다. 데이터 동기화의 구현 및 유지 관리는 시간이 많이 소요되는 프로세스이며 그 자체로 풀타임 작업이 될 수 있습니다.

Microsoft Sync Framework를 사용하여 데이터 동기화 작업을 위한 아키텍처를 수동으로 구현하거나 Microsoft SQL Server 관리 도구 내에서 이미 생성된 솔루션의 이점을 누릴 수 있습니다. Microsoft SQL Server 데이터베이스에서 데이터 동기화를 해결하는 데 사용할 수 있는 가장 일반적인 방법과 도구를 설명하고 몇 가지 권장 사항을 제공하려고 합니다.

소스 및 대상(예: 데이터베이스, 테이블)의 구조를 기반으로 구조가 유사하거나 다른 경우 사용 사례를 구별할 수 있습니다.

소스와 목적지는 매우 유사한 구조를 가지고 있습니다.

이것은 소프트웨어 개발 수명 주기의 다양한 단계에서 데이터를 사용할 때 매우 자주 발생합니다. 예를 들어 테스트 및 프로덕션 환경의 데이터 구조는 매우 유사합니다. 일반적인 요구 사항은 테스트 데이터베이스와 프로덕션 데이터베이스 간의 데이터를 비교하고 프로덕션 데이터베이스에서 테스트 데이터베이스로 데이터를 가져오는 것입니다.

소스와 대상의 구조가 다름

구조가 다르면 동기화가 더 복잡합니다. 이것은 또한 더 자주 반복되는 작업입니다. 일반적인 경우는 한 데이터베이스에서 다른 데이터베이스로 가져오는 것입니다. 가장 일반적인 경우는 소프트웨어가 다른 회사에서 유지 관리하는 다른 소프트웨어에서 데이터를 가져와야 하는 경우입니다. 일반적으로 가져오기는 일정에 따라 자동으로 실행되어야 합니다.

사용되는 방법은 개인의 선호도와 해결해야 하는 문제의 복잡성에 따라 다릅니다.

구조가 얼마나 유사한지에 관계없이 데이터 동기화를 해결하기 위해 네 가지 다른 방법을 선택할 수 있습니다.

• 수동으로 생성된 SQL 스크립트를 사용한 동기화
• 데이터 비교 방식을 이용한 동기화(소스와 타겟의 구조가 유사한 경우에만 사용 가능)
• 자동 생성된 SQL 스크립트를 사용한 동기화 - 상용 제품 필요

소스와 대상이 동일하거나 매우 유사한 구조를 가짐

수동으로 생성된 SQL 스크립트 사용

가장 간단하고 지루한 솔루션은 동기화를 위한 SQL 스크립트를 수동으로 작성하는 것입니다.

장점

• 무료 및 오픈 소스(FOSS) 도구로 수행할 수 있습니다.
• 테이블에 인덱스가 있으면 매우 빠릅니다.
• SQL 스크립트는 저장 프로시저에 저장하거나 SQL Server에 대한 작업으로 주기적으로 실행할 수 있습니다.
• 지속적으로 변경되는 데이터에 대해서도 자동 가져오기로 사용할 수 있습니다.

단점

• 이러한 SQL 스크립트를 작성하는 것은 일반적으로 각 테이블에 대해 INSERT , UPDATE 및 DELETE 의 세 가지 스크립트가 필요하기 때문에 매우 지루합니다.
• SQL 쿼리를 통해 사용 가능한 데이터만 동기화할 수 있으므로 CSV 및 XML 파일과 같은 소스에서 가져올 수 없습니다.
• 유지 관리가 어렵습니다. 데이터베이스 구조가 변경되면 두세 개의 스크립트( INSERT , UPDATE 및 때로는 DELETE 도 수정)를 수정해야 합니다.

예시

ID 및 Value 열이 있는 Source 테이블과 동일한 열이 있는 Target 테이블 간에 동기화를 수행합니다.

테이블에 동일한 기본 키가 있고 대상 테이블에 자동 증가(ID) 기본 키가 없는 경우 다음 동기화 스크립트를 실행할 수 있습니다.

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

데이터 비교 방법 사용

이 방법에서는 도구를 사용하여 소스 데이터와 대상 데이터를 비교할 수 있습니다. 비교 프로세스는 원본 데이터베이스의 차이점을 대상 데이터베이스에 적용하는 SQL 스크립트를 생성합니다.

데이터 비교 및 ​​동기화를 위한 여러 프로그램이 있습니다. 이러한 프로그램은 대부분 동일한 접근 방식을 사용합니다. 사용자는 소스와 대상 데이터베이스를 선택하지만 다른 대안은 DB 백업, SQL 스크립트가 있는 폴더 또는 소스 제어 시스템에 대한 연결일 수 있습니다.

다음은 데이터 비교 접근 방식을 사용하는 가장 널리 사용되는 도구입니다.

• SQL Server용 dbForge 데이터 비교
• RedGate SQL 데이터 비교
• Apex SQL 데이터 차이

첫 번째 단계에서는 데이터를 읽거나 원본과 대상에서 더 큰 데이터의 체크섬만 읽습니다. 그런 다음 비교 프로세스가 실행됩니다.

이러한 도구는 동기화를 위한 추가 설정도 제공합니다.

데이터 동기화에 필요한 다음 구성 옵션을 설정해야 합니다.

동기화 키

기본적으로 기본 키 또는 UNIQUE 제약 조건이 사용됩니다. 기본 키가 없는 경우 열 조합을 선택할 수 있습니다. 동기화 키는 소스의 행을 대상의 행과 쌍으로 만드는 데 사용됩니다.

테이블 페어링

기본적으로 테이블은 이름으로 쌍을 이룹니다. 이것을 변경하고 필요에 따라 페어링할 수 있습니다. dbForge 데이터 비교 소프트웨어에서 SQL 쿼리를 소스 또는 대상으로 선택할 수 있습니다.

동기화 프로세스

확인 후 도구는 소스 데이터와 대상 데이터를 비교합니다. 전체 프로세스는 모든 소스 및 대상 데이터를 다운로드하고 지정된 기준에 따라 비교하는 것으로 구성됩니다. 기본적으로 이름이 같은 테이블과 열의 값이 비교됩니다. 모든 도구는 열 및 테이블 이름 매핑을 지원합니다. 또한 IDENTITY (자동 증가) 열을 제외하거나 값을 비교하기 전에 일부 변환을 수행할 가능성이 있습니다(float 유형 반올림, 대소문자 무시, NULL 을 빈 문자열로 처리 등). 데이터 다운로드가 최적화되었습니다. 데이터 볼륨이 크면 체크섬만 다운로드됩니다. 이 최적화는 대부분의 경우에 유용하지만 데이터 볼륨에 따라 작업을 수행하는 데 필요한 시간이 늘어납니다.

다음 단계에는 마이그레이션이 생성된 SQL 스크립트가 있습니다. 이 스크립트는 저장하거나 직접 실행할 수 있습니다. 안전을 위해 이 스크립트를 실행하기 전에 데이터베이스 백업을 만들 수도 있습니다. ApexSQL Data Diff 도구는 선택한 데이터베이스에서 스크립트를 실행하는 실행 프로그램을 만들 수 있습니다. 이 스크립트에는 변경 방법에 대한 논리가 아니라 변경해야 하는 데이터가 포함되어 있습니다. 즉, 반복 가져오기를 제공하기 위해 스크립트를 자동으로 실행할 수 없습니다. 이것이 이 접근 방식의 가장 큰 단점입니다.

장점

• SQL에 대한 고급 지식이 필요하지 않으며 GUI를 통해 수행할 수 있습니다.
• 동기화하기 전에 데이터베이스 간의 차이점을 시각적으로 확인할 수 있습니다.

단점

• 상용 제품의 고급 기능입니다.
• 엄청난 양의 데이터를 전송할 때 성능이 저하됩니다.
• 생성된 SQL 스크립트에는 차이점만 포함되어 있으므로 향후 데이터를 자동으로 동기화하는 데 재사용할 수 없습니다.

아래에서 이러한 도구의 일반적인 UI를 볼 수 있습니다.

자동으로 생성된 SQL과 동기화

이 방법은 데이터 비교 방법과 매우 유사합니다. 이전 방식과의 유일한 차이점은 데이터 비교가 없고 생성된 SQL 스크립트에 데이터 차이가 포함되지 않고 동기화 논리가 포함된다는 점입니다. 생성된 스크립트는 저장 프로시저에 쉽게 저장할 수 있으며 주기적으로(예: 매일 밤) 실행할 수 있습니다. 이 방법은 데이터베이스 간의 자동 가져오기에 유용합니다. 이 방법의 성능은 데이터 비교 방법보다 훨씬 좋습니다.

자동 생성된 SQL에 의한 동기화는 SQL Database Studio에서만 제공됩니다.

SQL Database Studio는 데이터 비교 방법과 유사한 인터페이스를 제공합니다. 소스와 대상(데이터베이스 또는 테이블)을 선택해야 합니다. 그런 다음 옵션(동기화 키, 페어링 및 매핑)을 설정해야 합니다. 모든 매개변수를 설정하기 위한 그래픽 쿼리 작성기 기능이 있습니다.

장점

• SQL에 대한 고급 지식은 필요하지 않습니다.
• GUI에서 모든 것을 매우 빠르게 설정할 수 있습니다.
• 결과 SQL 스크립트는 저장 프로시저에 저장할 수 있습니다.
• 자동 가져오기 - SQL Server 작업으로 사용할 수 있습니다.

단점

• 상용 제품의 고급 기능입니다.
• 전체 프로세스가 한 단계에서 실행되기 때문에 동기화 전에 수동으로 차이점을 확인할 수 없습니다.

성능 벤치마크

테스트 케이스

각각 2,000,000행이 있는 하나의 테이블을 포함하는 두 개의 데이터베이스(A 및 B). 테이블은 동일한 SQL Server의 서로 다른 두 데이터베이스에 있습니다. 이 테스트는 두 가지 극단적인 경우를 다룹니다. 1) 원본 테이블에 2,000,000개의 행이 모두 포함되어 있고 대상 테이블이 비어 있습니다. 동기화는 많은 INSERTS 를 제공해야 합니다. 2) 원본 및 대상 테이블에는 2,000,000개의 행이 있습니다. 차이점은 한 행에만 있습니다. 동기화는 하나의 UPDATE 만 제공하면 됩니다.

RedGate 데이터 비교에는 3단계가 필요합니다.

• 비교하다
• 스크립트 생성
• 대상 데이터베이스에서 스크립트 실행

ApexSQL 데이터 차이에는 2단계가 필요합니다.

• 비교하다
• 한 번에 스크립트 생성 및 실행

SQL Database Studio는 전체 동기화를 한 단계로 수행합니다. 다음은 동기화 시간(초)입니다. "개별 단계"라고 표시된 열에는 위에 나열된 동기화 단계의 기간이 있습니다.

낮을수록 좋습니다.

동일한 테스트이지만 데이터베이스는 연결된 서버를 통해 연결되지 않은 다른 SQL 서버에 있습니다.

낮을수록 좋습니다.

요약

결과에서 동기화 알고리즘이 SQL Server에 종속되지 않기 때문에 RedGate와 Apex는 데이터베이스가 동일한 SQL 서버에 있는지 여부를 신경 쓰지 않는다는 것이 분명합니다. SQL Database Studio는 SQL Server의 기본 기능을 사용합니다. 따라서 데이터베이스가 동일한 서버에 있을 때 결과가 더 좋습니다.

출발지와 목적지가 다른 구조

하나의 넓은 테이블을 여러 개의 작은 관련 테이블로 동기화해야 하는 상황도 있습니다.

이 예제는 Continent , Country 및 City 작은 테이블로 동기화해야 하는 하나의 넓은 테이블 SourceData로 구성됩니다. 계획은 아래에 나와 있습니다.

SourceData의 데이터는 아래 이미지와 같을 수 있습니다.

수동으로 생성된 SQL 스크립트 사용

스크립트 동기화 대륙 테이블

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

스크립트 동기화 도시 테이블

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

이 스크립트는 더 복잡합니다. Country 및 Continent 테이블의 레코드를 찾아야 하기 때문입니다. 이 스크립트는 누락된 레코드를 City 에 삽입하고 ContryId 를 올바르게 채웁니다.

필요한 경우 UPDATE 및 DELETE 스크립트도 같은 방식으로 작성할 수 있습니다.

장점

• 상용 제품이 필요하지 않습니다.
• SQL 스크립트는 저장 프로시저에 저장하거나 SQL Server에 대한 작업으로 주기적으로 실행할 수 있습니다.

단점

• 이러한 SQL 스크립트를 만드는 것은 어렵고 복잡합니다(각 테이블에 대해 일반적으로 INSERT , UPDATE 및 DELETE 의 세 가지 스크립트가 필요합니다).
• 유지하기가 매우 어렵습니다.

외부 도구 사용

이러한 종류의 동기화(넓은 테이블에서 많은 관련 테이블로)는 데이터 비교 방법으로 수행할 수 없습니다. 다른 사용 사례에 초점을 맞추기 때문입니다. 데이터 비교 방법은 삽입할 데이터가 있는 SQL 스크립트를 생성하기 때문에 관련 테이블에서 참조를 조회하는 직접적인 기능이 없습니다. 이러한 이유로 이 방법을 사용하는 응용 프로그램(SQL Server용 dbForge Data Compare, RedGate SQL Data Compare, Apex SQL Data Diff용)을 사용할 수 없습니다.

그러나 SQL Database Studio를 사용하면 동기화 스크립트를 자동으로 생성할 수 있습니다. 아래 그림에는 SQL Database Studio에 Editor for Data Synchronization이라는 요소가 있습니다.

Editor는 잘 알려진 쿼리 빌더처럼 보이며 매우 유사한 방식으로 작동합니다. 각 테이블에는 정의된 동기화 키가 있어야 하지만 테이블 간에도 정의된 관계가 있습니다. 위 그림에는 동기화를 위한 매핑도 있습니다. 열 목록(이미지 하단)에는 City 테이블의 열이 있습니다(다른 테이블의 경우 유사).

열

• Id — 이 열은 기본 키(자동으로 생성됨)이므로 매핑되지 않습니다.
• CountryId — 이 열은 테이블에 대한 참조로 정의됩니다.
• 이름 — 이 열은 소스 테이블(와이드 테이블)의 City 열에서 채워집니다.

열 CountryId 및 Name 이 동기화 키로 선택됩니다. 동기화 키는 원본 및 대상 테이블의 행을 고유하게 식별하는 열 집합입니다. 기본 키 Id 는 원본 테이블에 없기 때문에 동기화 키로 사용할 수 없습니다.

동기화 후 테이블은 다음과 같습니다.

위의 예에서는 하나의 넓은 테이블을 소스로 사용했습니다. 소스 데이터가 여러 관련 테이블에 저장되는 일반적인 시나리오도 있습니다. SQL Database Studio의 관계는 외래 키를 사용하여 정의되지 않고 열 이름을 사용하여 정의됩니다. 이렇게 하면 CSV 또는 Excel 파일에서 가져올 수도 있습니다(파일이 임시 테이블에 로드되고 해당 테이블에서 동기화가 실행됨). 고유한 열 이름을 갖는 것이 좋습니다. 이것이 가능하지 않은 경우 해당 열에 별칭을 정의할 수 있습니다.

장점

• 쉽고 빠르게 생성
• 유지보수 용이
• 저장 프로시저에 저장할 수 있음(저장 프로시저는 나중에 편집기에서 동기화를 여는 데 필요한 데이터와 함께 저장됨)

단점

• 상용 솔루션

솔루션 비교

데이터 동기화는 INSERT , UPDATE 또는 DELETE 명령 시퀀스로 구성됩니다. 이러한 명령의 시퀀스를 만드는 방법에는 여러 가지가 있습니다. 이 기사에서는 동기화 SQL 스크립트를 생성하기 위한 세 가지 옵션을 살펴보았습니다. 첫 번째 옵션은 모든 것을 수동으로 만드는 것입니다. 가능하지만(시간이 너무 많이 소요됨) SQL에 대한 복잡한 이해가 필요하며 생성 및 유지 관리가 어렵습니다. 두 번째 옵션은 상용 도구를 사용하는 것입니다. 다음 도구를 살펴보았습니다.

• SQL Server용 dbForge 데이터 비교
• RedGate SQL 데이터 비교
• Apex SQL 데이터 차이
• SQL 데이터베이스 스튜디오

처음 세 가지 도구는 매우 유사하게 작동합니다. 데이터를 비교하고, 사용자가 차이점을 분석하고, 선택한 차이점을 동기화할 수 있습니다(자동으로 또는 명령줄에서). 다음 사용 시나리오에 유용합니다.

• 다양한 오류로 인해 데이터베이스가 동기화되지 않았습니다.
• 환경 간에 데이터를 전송하는 동안 복제를 방지해야 합니다.
• Excel 또는 HTML 형식의 데이터 비교 보고서가 필요합니다.

각 도구는 이런저런 이유로 사랑받고 있습니다. dbForge에는 훌륭한 UI와 많은 옵션이 있으며, ApexSQL은 나머지 도구보다 성능이 더 좋으며 RedGate는 가장 인기 있는 도구입니다.

네 번째 도구인 SQL Database Studio는 약간 다르게 작동합니다. 변경 사항이 아닌 동기화 논리를 포함하는 SQL 스크립트를 생성합니다. 모든 작업이 데이터베이스 서버에서 직접 수행되므로 데이터베이스 서버와 동기화 도구 간에 데이터를 전송할 필요가 없기 때문에 성능도 뛰어납니다. 이 도구는 다음 사용 사례에 유용합니다.

• 데이터베이스 구조가 다른 자동 데이터베이스 마이그레이션
• 여러 관련 테이블로 가져오기
• 외부 소스 XML, CSV, MS Excel에서 가져오기