SQL Server: レプリケーションにおけるアーティクル追加時に気を付けること

SQL Serverのレプリケーションにおいて、既存のパブリケーションにアーティクルを追加する際、気を付けることをまとめたいと思います。
注意を払うべきなのは「スナップショットエージェントの実行タイミング」です。

1.追加するパブリケーションに入っている全アーティクルに一時的にSch-Mロックがかかる

パブリッシャ側のDBにおいてアーティクル追加後、スナップショットエージェントの実行時に対象パブリケーションに入っている全アーティクルに対して一時的にSch-Mロックが取得されます。

また、ブロッキングが発生したからといって、スナップショットエージェントを停止することは、レプリケーションの設定が中途半端な状態となる危険性があるため避けた方が良いです。したがって、一度スナップショットエージェントを実行した後は基本的に見守ることしかできません。

そのため、メンテ期間を設けずにオンラインで実施する際は、少なくとも「スナップショットエージェント実行時に、パブリケーションの全アーティクルのいずれかを参照しているクエリが存在しないか」をチェックしてから実行するべきです。
これにより、ブロッキング発生のリスクを可能な限り減らすことができます。

「パブリケーションの全アーティクルのいずれかを参照しているクエリが存在しないか」については、下記の手順で確認が可能です。

1-1. チェッククエリを生成するクエリを実行

該当のパブリケーションを指定して実行することで、テーブルおよび、テーブルを参照しているVIEWのいずれかにアクセスしているかどうかチェックするクエリを生成できます。

USE <db名> --パブリッシャのDB名
GO
set nocount on
declare @publicationame varchar(255) = 'publication名' --パブリケーション名
 
IF (OBJECT_ID('tempdb..#article_objects') IS NOT NULL)
BEGIN
    DROP TABLE #article_objects
END
 
-- パブリケーションで使用しているアーティクルのオブジェクト情報を一時テーブルに格納
select
    a.name,
    a.objid
INTO #article_objects
from
    syspublications AS p
    left join sysarticles AS a
        on p.pubid = a.pubid
where
    p.name = @publicationame
 
-- アーティクルのオブジェクト情報を元にオブジェクトの依存関係のリストを生成
DECLARE @parameter nvarchar(max)
;WITH objectList AS(
-- 該当オブジェクトを参照しているテーブル
select
    1 as level,
    d.referenced_id AS parent_objecT_id,
    d.referenced_entity_name AS parent_object_name,
    d.referencing_id AS child_object_id,
    object_name(d.referencing_id) AS child_object_name,
    o.type
from
    sys.sql_expression_dependencies AS d WITH(NOLOCK)
    INNER JOIN sys.objects as o WITH(NOLOCK)
        ON  d.referencing_id = o.object_id
        AND o.type IN('V')
    INNER JOIN #article_objects AS a
        ON a.objid = d.referenced_id
union all
select
    level + 1,
    c.referenced_id,
    c.referenced_entity_name,
    c.referencing_id,
    object_name(c.referencing_id) AS object_name,
    o.type
from
    sys.sql_expression_dependencies as c WITH(NOLOCK)
    INNER JOIN sys.objects as o WITH(NOLOCK)
        ON  c.referencing_id = o.object_id
        AND o.type IN('V')
    inner join objectList
        on objectList.child_object_id = c.referenced_id
)
 
-- オブジェクトの依存関係をもとに、実行中のクエリに該当のオブジェクト名が含まれているクエリが存在するかをチェック
SELECT
    @parameter = name
FROM
(
    SELECT
         ' OR text LIKE ''%' + object_name + '[^0-9|a-Z|_-]%'''
    FROM (
        SELECT DISTINCT
            parent_object_name AS object_name
        FROM objectList
        UNION
        SELECT DISTINCT
            child_object_name
        FROM objectList
    ) AS BaseTbl
    WHERE
    object_name not like '%syncobj%'
    FOR XML PATH('')
) AS T(name)
   
DECLARE @baseSql nvarchar(max) = '
SELECT
    datediff(s, er.start_time, GETDATE()) as time_sec,
    er.session_id,
    es.host_name,
    es.program_name,
    er.status,
    er.blocking_session_id,
    er.start_time,
    er.wait_type,
    er.wait_time,
    er.last_wait_type,
    er.wait_resource,
    er.command,
    st.text     
FROM
    sys.dm_exec_requests  AS er
    OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS st
    LEFT JOIN sys.dm_exec_sessions AS es
        ON er.session_id = es.session_id
WHERE
    st.text is not NULL
    AND
    er.session_id <> @@SPID
    AND datediff(s, er.start_time, GETDATE()) >= 1
    AND
    ({0})
'
DECLARE @sql nvarchar(max) = REPLACE(@baseSql, '{0}', STUFF(@parameter, 1,4,''))
select @sql
--EXECUTE (@sql + N' order by datediff(s, er.start_time, GETDATE()) desc ')

1-2. 生成したクエリを実行しながら、レコードが0になったタイミングでスナップショットエージェントを実行

0レコードになったタイミングでスナップショットエージェントを実行することでブロッキングリスクを最小限に抑えることができます。
かなり職人技になってしまいますが、トランザクションレプリケーションの場合、ノーメンテでレプリ追加を実施する際は最も安全な方法と考えています。

2. スナップショットエージェント実行中の状況を見守る

「現在実行中のクエリリストを取得するクエリ」を実行しながら、スナップショットエージェントがブロックされ続けていないかを見守ります。
数秒程度ブロックされてしまうことはオンラインの環境だと仕方がないですが、長時間特定のクエリにブロックされてエージェント実行が進まない場合はブロッカーをKILLすることも検討します。

「現在実行中のクエリリストを取得するクエリ」

set transaction isolation level read uncommitted
SELECT TOP 100
     der.session_id as spid
    ,der.blocking_session_id as blk_spid
    ,datediff(s, der.start_time, GETDATE()) as elapsed_sec
    ,DB_NAME(der.database_id) AS db_name
    ,des.host_name
    ,des.program_name
    ,der.status -- Status of the request. (background / running / runnable / sleeping / suspended)
    ,dest.text as command_text
    ,REPLACE(REPLACE(REPLACE(SUBSTRING(dest.text, 
    (der.statement_start_offset / 2) + 1, 
    ((CASE der.statement_end_offset
    WHEN -1 THEN DATALENGTH(dest.text)
    ELSE der.statement_end_offset
    END - der.statement_start_offset) / 2) + 1),CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' ') AS current_running_stmt
    ,datediff(s, der.start_time, GETDATE()) as time_sec
    ,wait_resource --ロックされているリソース名
    ,wait_type
    ,last_wait_type --最後または現在の待機の種類の名前
    ,der.wait_time  as wait_time_ms
    ,der.open_transaction_count
    ,der.command
    ,der.cpu_time
    ,(case der.transaction_isolation_level
      when 0 then 'Unspecified'
      when 1 then 'ReadUncomitted'
      when 2 then 'ReadCommitted'
      when 3 then 'Repeatable'
      when 4 then 'Serializable'
      when 5 then 'Snapshot'
    else cast(der.transaction_isolation_level as varchar) end) as transaction_isolation_level
    ,der.granted_query_memory * 8 as granted_query_memory_kb --キロバイト単位
FROM
    sys.dm_exec_requests der
JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS dest
WHERE
    des.is_user_process = 1
and datediff(s, der.start_time, GETDATE()) >= 1
--AND dest.text like '%%' -- クエリの中身でlike検索したいときはここを編集
ORDER BY
    datediff(s, der.start_time, GETDATE()) DESC

まとめ

以上、「なぜこうするか」といった理由の詳細な説明は割愛しましたが、SQL Serverのレプリケーションにおいて、既存のパブリケーションにアーティクルを追加する際に気を付けると良い点についてご紹介しました。