SQL Server 2019에서 트랜잭션 복제 갱신 구독 사용

페이지 생성 날짜 : 2022년 5월 29일 일요일

소개 및 주의 사항

이 문서는 SQL Server 2019의 복제를 위한 것입니다. 그 기능 중 하나는 "재생 가능한 구독"이 활성화되어 있다는 것입니다.

이 기능은 이전 버전의 SQL Server에서 기본적으로 사용하도록 설정되었지만 2017년 이후 (아마도) 사용하지 않도록 설정되었습니다.

이후 버전의 SQL Server에서 이 기능이 제거될 수 있습니다. 기존 환경을 업그레이드하는 것을 제외하고 병합 복제 등으로 새 구성으로 대체하는 것이 좋습니다.

참조

환경

두 개의 서버, 하나의 서버와 두 개의 SQL Server를 사용합니다. Windows Server는 설치 직후에 추가 설정이 수행되지 않은 상태입니다.

1단(출판측)

윈도우 서버 2019 스탠다드 에디션
SQL Server 2019 개발자 에디션(스탠다드 에디션 이상 필요)
SQL 서버 관리 스튜디오 18.9.1

두 번째 장치(구독자 측)

윈도우 서버 2019 스탠다드 에디션
SQL Server 2019 Express Edition
SQL 서버 관리 스튜디오 18.9.1

목적

첫 번째 또는 두 번째 서버 데이터베이스의 테이블 레코드가 업데이트되는 경우에도 서로의 서버에 있는 테이블 레코드가 동기화되었는지 확인하십시오.

그건 그렇고, 출판 측의 하나는 괜찮지 만 가입자 측은 임의의 수의 단위를 가질 수 있습니다. 또한 단일 서버에서 SQL Server의 여러 인스턴스에서도 작동합니다.

SQL 서버 설치

SQL Server 설치는 이러한 팁의 주요 부분이 아니므로 자세한 내용은 다루지 않겠습니다. 복제와 관련된 항목만 나열됩니다.

출판 쪽을 설치하려면 Standard Edition 이상이 필요합니다. 이 팁은 개발자 에디션을 테스트 환경으로 설치합니다.

가입자는 Express Edition에 적합합니다. 물론 DB 구성에 따라 다른 버전도 괜찮습니다.

기능 선택에서 데이터베이스 엔진 서비스 및 SQL Server 복제를 선택합니다. 이는 발행물 측과 구독자 측 모두에 대해 동일합니다.

인스턴스 구성은 기본적으로 게시 측과 구독자 측 모두로 설정됩니다. 인스턴스 이름을 변경하더라도 나중에 서버 사양에서 인스턴스 이름을 지정할지 여부가 다릅니다.

게시 쪽에서 SQL Server 에이전트가 자동으로 시작되도록 구성합니다. 이 설정은 나중에 변경할 수 있습니다.

이 팁은 SQL Server sa 계정을 사용하여 복제를 인증합니다. 따라서 SQL Server 인증을 사용하도록 설정합니다. 보안상의 이유로 sa 계정을 사용하지 않으려면 나중에 설명하는 SQL Server 인증 부분을 Windows 인증 또는 다른 계정으로 바꿉니다.

이 설정을 두 서버 모두에서 동일하게 만듭니다.

SQL 서버 관리 스튜디오 설치

다운로드한 SQL Server 관리 스튜디오를 설치합니다. 설치 화면은 설치에만 해당되므로 설명은 생략합니다.

설치 후 다시 시작하라는 메시지가 표시되면 Windows를 다시 시작합니다. 또한 Windows 업데이트에서 패치를 설치하십시오.

게시 쪽 데이터베이스 및 테이블 만들기

발행 측에서 테이블을 작성하는 경우 구독자 측은 자동으로 테이블 또는 레코드를 복제합니다.

생성 방법은 일반적인 절차와 동일하므로 세부 사항은 생략하겠습니다.

SQL Server Management Studio에서 게시 DB라는 데이터베이스를 만들고 있습니다.

"Users"라는 테이블을 만들고 다음과 같이 열을 등록합니다. 레코드의 동기화를 확인하기만 하면 테이블의 이름, 번호 및 열 내용이 무엇이든 될 수 있습니다. 그러나 기본 키만 필요합니다.

몇 개의 레코드를 추가하여 복제를 만든 후 레코드가 동기화되는지 확인합니다.

원격 연결 구성

서로의 서버 데이터베이스에 대한 원격 액세스를 허용하도록 구성합니다. 자세한 내용은 다음 팁에 요약되어 있습니다.

SQL Server 인증을 사용하여 SQL Server에 연결하기 위한 서버 쪽 구성

폴더 사용 권한 설정

게시 쪽의 서버에서 다음 폴더의 속성을 엽니다. 폴더 경로는 SQL Server 버전과 인스턴스 이름에 따라 다릅니다.

C:\Program Files\Microsoft SQL Server\MSSQL15. MSSQLSERVER\MSSQL\ReplData

보안 탭에서 "SERVICE" 계정을 추가하고 모든 권한으로 설정합니다.

배포 구성

이번에는 발행물과 동일한 서버에 배포가 작성됩니다.

SQL Server Management Studio를 시작하고 복제를 마우스 오른쪽 단추로 클릭한 다음 배포 구성을 선택합니다.

다음을 클릭합니다.

"자신의 서버를 자체 유통 업체> 만드십시오"<을 선택하십시오.

그런 다음 "다음"을 그대로 클릭하여 완료하십시오.

발행물 작성

원래 다음 그림과 같이 마법사에서 만들어집니다. SQL Server 2019에서 마법사에서 만드는 것은 "갱신 구독"을 사용하도록 설정하지 않으며 테이블 간의 레코드 동기화를 허용하지 않습니다.

따라서 T-SQL을 사용하여 여기에서 발행물을 만듭니다.

다음은 SQL Server 인증을 사용하여 발행물을 만들기 위한 SQL입니다. 설정에 따라 비밀번호를 변경하십시오.

-- トランザクションパブリケーションを追加するための宣言
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'パブリケーションDB'; 
SET @publication = N'パブリケーション'; 
SET @login = 'sa'; 
SET @password = 'saパスワード'; 

USE [パブリケーションDB]

-- パブリケーションデータベースでトランザクションレプリケーションを有効にする。
EXEC sp_replicationdboption 
    @dbname=@publicationDB, 
    @optname=N'publish',
    @value = N'true';

-- sp_addlogreader_agent を実行して、エージェントジョブを作成します。
EXEC sp_addlogreader_agent 
    @publisher_login = @login, 
    @publisher_password = @password,
    @publisher_security_mode = 0;

-- 即時更新、キュー更新、およびプルサブスクリプションをサポートするトランザクションパブリケーションを作成します。
EXEC sp_addpublication 
    @publication = @publication, 
    @status = N'active',
    @allow_sync_tran = N'true', 
    @allow_queued_tran = N'true',
    @allow_pull = N'true',
    @independent_agent = N'true',
    -- 関連するデフォルトのプロパティを明示的に宣言する
    @conflict_policy = N'pub wins';

-- デフォルトのスケジュールを使用して、パブリケーションの新しいスナップショットジョブを作成します。
EXEC sp_addpublication_snapshot 
    @publication = @publication, 
    @publisher_login = @login, 
    @publisher_password = @password,
    @publisher_security_mode = 0;
GO

Windows 인증을 사용하는 경우 다음과 같이 "sp_addlogreader_agent" 및 "sp_addpublication_snapshot" 부분을 변경합니다.

SQL 서버 인증

    @publisher_login = @login, 
    @publisher_password = @password,
    @publisher_security_mode = 0;

윈도우 인증

    @job_login = @login, 
    @job_password = @password,
    @publisher_security_mode = 1;

성공적으로 만들어지면 다음이 표시됩니다.

또한 만든 발행물의 속성을 열고 "갱신 구독"이 활성화되어 있는지 확인합니다.

아티클 지정

원래 마법사를 사용하여 만들면 동시에 문서를 지정합니다. 위의 스크립트에는 아티클 처리가 포함되어 있지 않으므로 속성에서 지정됩니다.

왼쪽 메뉴에서 "기사"를 선택하고 동기화하려는 테이블을 확인한 다음 확인 버튼을 클릭하십시오.

동기화할 테이블의 열에 msrepl_tran_version이 추가되고 테이블 트리거가 동시에 추가됩니다.

구독 만들기

방금 만든 발행물을 마우스 오른쪽 단추로 클릭하고 새 구독을 선택합니다.

다음을 클릭합니다.

만든 발행물을 선택합니다.

배포자에서 모든 에이전트 실행<서버 이름>을 선택합니다.

SQL Server 구독자 추가 단추를 클릭합니다.

구독자로 등록할 서버(+ 인스턴스 이름)를 지정합니다.

구독자 데이터베이스가 없는 경우 새 데이터베이스를 만듭니다. 이미 있는 경우 선택 항목이 표시되므로 선택합니다.

데이터베이스를 지정한 후 다음을 클릭합니다.

구독자가 두 명 이상인 경우 여기에서 필요한 만큼 등록할 수 있습니다.

"..." 버튼을 클릭합니다.

여기서 에이전트는 sa 계정으로 실행되지만 환경에 따라 설정하십시오.

연속 실행을 선택합니다.

"재생 가능 구독" 설정이 작동해야 합니다.

"커밋 변경 동시에 변경"의 경우 즉시 동기화되지만 모든 서버가 실행 중이어야 합니다.

"가능한 경우 큐 변경 및 커밋"은 큐에 변경 사항을 누적하므로 서버가 실행 중이 아니더라도 서버가 실행 중일 때 커밋됩니다. 그러나 동기화 타이밍이 느립니다.

미리 정의된 연결된 서버 또는 원격 서버 사용을 선택합니다.

지금을 선택합니다.

다음을 클릭합니다.

마침 단추를 클릭합니다.

오류가 없으면 닫습니다.

잠시 후 구독자 측에서 데이터베이스가 만들어졌으며 테이블도 작성되었음을 알 수 있습니다.

레코드도 동기화되었는지 확인할 수 있습니다.

잠시 후 동기화되지 않으면 다음을 수행하십시오.

생성된 구독을 마우스 오른쪽 단추로 클릭하고 다시 초기화를 선택합니다.

"새 스냅샷 사용"을 선택하고 "다시 초기화하도록 설정" 버튼을 클릭합니다.

오류가 발생하면 다시 시도하십시오.

작업 확인

게시 측에서 테이블을 추가하거나 수정해 보십시오.

구독자와 동기화되었는지 확인하기 위해 몇 초 정도 기다립니다.

이제 구독자 측에서 레코드를 수정해 보겠습니다.

게시 측과 동기화되었는지 확인할 수 있습니다.

등록된 구독자가 여러 명인 경우 구독자 간에 동기화되어 있는지 확인합니다.

관리 스튜디오에서 테이블 디자인 변경 사용

SSIS 빌드의 SQL Server 2014 CTP2 버전 설치