Bora.Choi

1. Books Online: sp_addarticle (Transact-SQL)
2. How to: Specify Schema Options (Replication Transact-SQL Programming)

If you look at the schema option on sp_addarticle (Transact-SQL), you will see there are over 40 combinations of schema options and it can be hard to know which one to choose. The article, How to: Specify Schema Options (Replication Transact-SQL Programming) shows how to figure out and specify schema options.

Replication Requirements

My company has two different requirements for replication as outlined below. When using the GUI with different versions of SQL Server several different schema options are generated, but they do not always work the same across all versions of SQL Server.

Requirement 1

1. Replicate the Primary Key with index that is related to the primary key constraint
2. Convert User Defined Types (UDTs) to base data types at the Subscriber
3. Use SQL Stored Procedures to replicate (auto generate store procedures on subscribers for Insert, Update and Delete)

Based on the above, I have tested the below combinations and as you can see different version have different results for the 0xB3 option.

* PK - Primary Key
* FK - Foreign Key

So, based on the above, I have concluded that our company, "0x00000000000080A3" fits our needs.

Requirement 2

1. All of the Requirement 1 plus
2. Replicate all Indexes on Publisher

Based on the testing, we have decide to use "0x00000000000080F3".

Confirmation

Now, here is simple query that I use to confirm the options.

If you run this for 0x00000000000080A3:

declare @pubid int
declare @optionid bigint
declare @schema_option varbinary(2000)
set @optionid = 1
set @schema_option = 0x00000000000080A3
WHILE (@optionid <= 2147483648)
BEGIN
if (select @schema_option & @optionid) > 0 
PRINT cast(@optionid as varbinary)
SET @optionid = @optionid * 2
END

You will see this result:

0x0000000000000001
0x0000000000000002
0x0000000000000020
0x0000000000000080
0x0000000000008000

You can check Books Online: sp_addarticle (Transact-SQL) and you will see the below options. Note, in Books Online they shorten the notation, so 0x0000000000000001 = 0x01.

• 0x0000000000000001 - Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
• 0x0000000000000002 - Generates the stored procedures that propagate changes for the article, if defined.
• 0x0000000000000020 - Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.
• 0x0000000000000080 - Replicates primary key constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enable
• 0x0000000000008000 - This option is not valid for SQL Server 2005 Publishers.

If we do the same for 0x00000000000080F3, we get this output:

• 0x0000000000000001 - Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
• 0x0000000000000002 - Generates the stored procedures that propagate changes for the article, if defined.
• 0x0000000000000010 - Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.
• 0x0000000000000020 - Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.
• 0x0000000000000040 - Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.
• 0x0000000000000080 - Replicates primary key constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enable
• 0x0000000000008000 - This option is not valid for SQL Server 2005 Publishers.

• 복제 관련 시스템 저장프로시저 내용을 확인해 보고 싶을 경우가 종종 있다.
• 성능을 향상 시키고자 할 때나, 문제를 해결 하고자 할 때
• 그런데 시스템 저장 프로시저 내용을 확인하기는 쉽지 않다.
• 그런데 방법은 있다.

1. DAC 기능을 활성화 한다.  혹은 sqlcmd 명영 유틸을 사용시에는 -A 옵션을 사용한다.

exec sp_configure 'remote admin connections', 1
go
reconfigure
go

 2. 저장 프로시저 내역 확인

select object_definition (object_id('sys.sp_MSrepl_helparticlecolumns') )

3. 서비스에서 작업을 진행 했다면, DAC 연결을 반드시 종료한다.

복제에 문제가 생길 때 보실 수 있는 내역입니다. 어제 이 방법으로 확인했습니다.

평소 문제가 없을때는 MSrepl_commands 조회시 top 을 주시는 것이 좋습니다.

1.  해당 게시명은 아는데 agent를 모를 경우 쉽게 알 수 있는 법은 배포 장비의 job중에 해당 게시의 배포 agent 명을 보면 뒤에 붙어 있는 숫자가 Agent 아이디 입니다.

아니면  select id, name , publication from MSdistribution_agents 하면 게시에 대한 agent를 확인 할 수 있습니다.

 
2.  1번에서 실행된 agent에서 가장 마지막으로 실행된 xact_seqno 를 아셨으면 그 번호 이상인 명령이 무엇인지 확인하는 것입니다.

   이때 MSrepl_commands 테이블만으로 조해 해보면 모든 게시자 DB에 여러 개의 게시가 있을 경우 다른 게시에 대한 것도 보이니  아티클로 아셔서 보시거나 윗 처럼 조인해서 보시면 됩니다.

3.  최종적으로 비슷한 xact_seqno가 동일한게 여러 개 보이면 범위주어서 찾아보시면 됩니다. 

인자로 @agent_id 값을 넣으면 조회가 오래 걸리니 안 넣으시게 좋을 것 같습니다.

추가 )  지연된 명령어 수 확인

exec sp_replmonitorsubscriptionpendingcmds  

 @publisher ='Enter publisher server name',

 @publisher_db = 'Enter publisher database name',

 @publication ='Enter name of publication',

 @subscriber ='Enter subscriber server name',

 @subscriber_db ='Enter subscriber database name',

 @subscription_type ='0' --0 for push and 1 for pull 

복제 삭제

• 방법 1: http://msdn.microsoft.com/ko-kr/library/ms152757.aspx
• 방법 2: http://support.microsoft.com/kb/324401

• 이상하게 잘 못 삭제했을 경우

2. select replinfo,* from sysobjects where replinfo = 0 을 찾아서 입력합니다.

3.           sp_removedbreplication [ [ @dbname = ] 'dbname' ]
                 [ , [ @type = ] type ]
             

4. 1. [ @dbname=] 'dbname'

   1. 데이터베이스의 이름입니다. dbname은 sysname이며 기본값은 NULL입니다. NULL인 경우 현재 데이터베이스를 사용합니다.

   1. [ @type = ] type

   1. 데이터베이스 개체를 제거 중인 복제의 유형입니다. type은 nvarchar(5)이며 다음 값 중 하나일 수 있습니다.

   tran	트랜잭션 복제 게시 개체를 제거합니다.
   merge	병합 복제 게시 개체를 제거합니다.
   both(기본값)	모든 복제 게시 개체를 제거합니다.

ERROR CASE

게시& 배포자가 먼저 삭제되고 구독이 남아 있을 경우

작업으로 인해 배포자 agent를 중지하고 작업 처리후 시작하는 것을 잊어버리는 수가 있다. 혹은 다른 문제로 인해 배포 agent가 실행되고 있지 않을때,

오래 동안 실행되지 않으면 복제가 실패되어 다시 구축해야 하는 경우가 발생한다.

아래 쿼리는 배포 agent 실행여부를 확인하여 메일이나 SMS를 받을 수 있게 처리하는 것이다.

이또는 해당 job이 실행되고 있거나 오래 실행되는것도 찾을 수 있다.

[code sql]

DECLARE @is_sysadmin INT 
DECLARE @job_owner sysname 
DECLARE @job_id uniqueidentifier 
DECLARE @job_name sysname 
DECLARE @running int 
DECLARE @cnt int 
DECLARE @msg varchar(8000) 
DECLARE @msg_header varchar(4000) 
DECLARE @categoryid int 


SELECT @job_owner = SUSER_SNAME() 
,@is_sysadmin = 1 
,@running = 0 
,@categoryid = 10 -- Distributor jobs 


CREATE TABLE #jobStatus (job_id UNIQUEIDENTIFIER NOT NULL, 
last_run_date INT , 
last_run_time INT , 
next_run_date INT , 
next_run_time INT , 
next_run_schedule_id INT , 
requested_to_run INT , 
request_source INT , 
request_source_id sysname COLLATE database_default NULL, 
running int , 
current_step INT , 
current_retry_attempt INT , 
job_state INT) 


INSERT INTO #jobStatus 
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner--, @job_id 
--
select j.name, js.command, jss.running
from msdb.dbo.sysjobsteps js
join msdb.dbo.sysjobs j on js.job_id = j.job_id
join #jobStatus jss on js.job_id = jss.job_id
where step_id = 2 and subsystem = 'Distribution'
and command like '%-Continuous'
and jss.running <> 1 -- Not running



[/code]

트랜잭션 복제, 병합복제, 스냅숏 복제에 게시를 초기화 하는 방법 중에 스냅숏 DB를 사용한 게시 초기화 방법.

•  SQL Server 2005 Enterprise Edition 서비스 팩 2 이상 버전일 경우만 가능

현재 구독 초기화 방법중 알고 있는 방법은

방법

게시의 속성 중에  sync_method 옵션중에  database snapshot 가 추가 되었음 (database snaphort character : 문자모드, 사용하지 않은 것이 좋을 것 같음)
해당 옵션은 복제 마법사를 통해서 세팅이 불가능하며, 게시를 설정한 후 아래 스크립트로 변경해야 한다.

1. 1. 게시 DB에서 실행

   2. exec sp_helppublication;  

   3. exec sp_changepublication

          @publication = '게시 이름',

          @force_invalidate_snapshot = 1,

          @property  = 'sync_method',

          @value = 'database snapshot'  -- 5번

결과

•  이 방법으로 변경해서 게시를 초기화 하게 되면 게시이름_DB 이름으로 스탭숏 DB가 생성되며, 게시가 초기화 완료되면 삭제된다. 기존에 동일한 스탭숏 DB가 있다고 해도 항상 새로 생성한다. 
• 대용량 DB일 경우 스탭숏 DB를 생성할 공간만 있다면, 이 방법을 사용했을 때 스냅숏 초기화 하는 과정 동안 서비스 DB에  테이블 lock가 걸리지 않으므로 서비스에 지장이 덜 가게 된다. 
• 스냅숏 복제 작동 방법 : http://msdn.microsoft.com/ko-kr/library/ms151734(SQL.90).aspx

참고

• 대용량 DB일 경우 Arrary 스토리지를 사용해서 백업/복원하는 초기화 방법을 빠르게할 수도다.:  http://sqlcat.com/technicalnotes/archive/2009/05/04/initializing-a-transactional-replication-subscriber-from-an-array-based-snapshot.aspx
• sync_mode  사용 초기화 : http://blogs.msdn.com/mangeshd/archive/2008/05/30/how-to-use-database-snapshot-sync-mode-while-creating-a-new-tran-snapshot-publication-using-sp-addpublication.aspx
• http://sqlcat.com/whitepapers/archive/2008/02/11/database-snapshot-performance-considerations-under-i-o-intensive-workloads.aspx

복제를 구성한 후 article 속성을 셋팅해서 게시 한 경우 추후 Aritical 속성을 확인할때 UI를 통한 확인도 가능하지만 부하가 있는 서버의 복제를 확인할때 UI가 응답 없음으로 될 경우가 있다.

sysarticles 테이블의 schema_option 값에 바이너리 값으로 속성값이 저장되며, 도움말 확인하여 연산해서 속성 확인 가능하다.

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

(트랜잭션 복제 기준)