none
SQL Server 2019 でレプリケーション設定をしてもテーブルレコード更新でレコードが同期されない RRS feed

  • 質問

  • 手順や設定が間違っているかもしれませんのでご指摘願えればと思います。

     

    【やりたいこと】

     

    2サーバーにインストールした SQL Server 同士でレプリケーションを組み、片方のテーブルのレコードが更新されたらもう片方のサーバーのテーブルのレコードに同じ更新をかけたい。逆方向への更新もあり

    構築手順は後半に記載しますが、SQL Server 2012 R2 では同期ができましたが SQL Server 2019 でうまくいってません

     

    【環境】

     
    • 2つの Windows Server 2019 と3つの SQL Server 2019 を使用します。すべて最新のパッチ適用済みです。
    • Windows Server 2019 はインストール直後の状態を使用します。
    • Aサーバー:Windows Server 2019 Standard, SQL Server 2019 Standard, SQL Server 2019 Express
    • Bサーバー:Windows Server 2019 Standard, SQL Server 2019 Express
    • SQL Server 2019 Standard : パブリッシャー
    • SQL Server 2019 Express : サブスクリプション
    • SQL Server のインスタンスはすべてデフォルトです。
    • 3つの SQL Server はいずれも「データベースエンジンサービス」「SQL Server レプリケーション」の機能をインストールしています。
    • エージェントは自動起動です。
    • 2つのサーバー間は Windows 認証でお互いに接続可能です。sa による認証も可。
     

    【現象・原因】

     

    レプリケーション設定をした後にテーブルトリガーが各テーブルにセットされない? レプリケーション用のストアドはサブスクリプション側に自動追加されている。

    2012 R2 ではサブスクリプション側のテーブルに ins upd del のテーブルトリガーが自動で追加されます。

     

    【構築手順】

     

    ■パブリッシャー側

     
    1. Aサーバー Standard にデータベース「レプリケーションDB」を作成
    2. Aサーバー Standard「レプリケーションDB」にいくつかテーブル作成。各テーブルは1つ以上のキーを持つ。mstran_repl_version 列はなし。
    3. Aサーバー Standard でパプリケーション新規作成
    4. ディストリビューターを同サーバーに作成
    5. スナップショットフォルダーのパスはデフォルト。フォルダ権限に「Service」追加
    6. パブリケーションの種類は「トランザクション パブリケーション
    7. アーティクルはすべてのテーブル
    8. セキュリティは SQL Server の sa アカウント
     

    ■サブスクリプション側 (Express 2台とも)

     
    1. ローカルパプリケーションの下にサブスクリプションを新規作成
    2. ディストリビューターですべてのエージェントを実行する
    3. サブスクライバーで対象の Express サーバーに DB を新規追加。(Windows 認証)
    4. セキュリティは SQL Server の sa アカウント
    5. 同期スケジュールは「連続実行」
    6. サブスクリプションの初期化は「今すぐ」
     

    【結果】

     
    • 2つの Express Edition 側の SQL Server にはデータベースが作成され、Standard Edition 側と同じテーブルも作成される
    • テーブルのレコードも Express 側に同じレコードが入る
    • アーティクルで「ユーザートリガーのコピー」を true にすればパブリッシャーのテーブルにつけたテーブルトリガーはサブスクリプション側にもコピーされる
    • パブリッシャーのテーブルレコードを更新すると2台のサブスクリプションのテーブルレコードにも反映される
    • サブスクリプション側のテーブルレコードを更新しても反対側のサブスクリプション、パブリッシャーには同期はされない
    • 各テーブルには mstran_repl_version 列は追加されない

     

    SQL Server 2012 R2 (またはそれ以前のバージョン)では同じ手順でできていたのですが、SQL Server 2019 で同じ設定をしても同期されませんでした。ちなみにこれを2つの環境 (合計4サーバー)で実施したのですがどちらもダメでした。

    Express Edition 側でデータが更新されたときにもう片方のサーバーのテーブルに同期させる方法についてわかる方がいればご教示願います。


    • 編集済み おのでら 2021年5月6日 13:18 HTML崩れ修正
    2021年5月6日 13:04

回答

  • トランザクション レプリケーション 更新可能なサブスクリプション機能を以前に使用されていたのでしょうか。 少なからず、SQL Server 2019 では Express Edition ではこの機能を使用できないようです。

    なお、マージ レプリケーションを構築することで、サブスクライバ側の更新を反映させることは可能です。 しかしながら、競合の優先度など、色々と考慮しなければいけない点や、テーブルに rowguid 列が追加される点などに注意が必要になるかと思います。

    トランザクション レプリケーションの更新可能なサブスクリプション
    Editions and supported features of SQL Server 2019 (15.x)

    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月6日 17:57
  • NOBTA さん、回答ありがとうございます。

    まず、私の記載箇所に訂正箇所がありました。(あまり影響はありませんが…)

    • SQL Server 2012 R2 → SQL Server 2014    (Windows Server 2012 R2 と混同してました)
    • テスト環境では SQL Server Standard ではなく Developer でテストしています

     

    教えていただいた情報をもとに少し調べてみました。

     

    更新可能なサブスクリプション機能 について

    • 2014 ではサブスクリプションを右クリックすると「更新方法の設定」というメニューがありました。2019 にはありません。
    • 2014 のパブリケーションのプロパティを開き、「サブスクリプション オプション」の「更新可能なサブスクリプション」を見るとすべて True になっていました。2019 は False でした。
    • 2019 Standard (Developer) をパブリッシャーとサブスクリプション両方にしてみましたが「更新可能なサブスクリプション」は True になりませんでした

    以下の説明ページではパブリケーション作成 (Developer Edition) で「更新可能なサブスクリプションを含むトランザクションパブリケーション」が選択できるようなのですが、その画面を見ても上記の選択項目はなく「トランザクションパブリケーション」他全4つしかありませんでした。何か条件があるのでしょうか。

    トランザクション パブリケーションの更新可能なサブスクリプションの有効化 - SQL Server | Microsoft Docs
    ttps://docs.microsoft.com/ja-jp/sql/relational-databases/replication/publish/enable-updating-subscriptions-for-transactional-publications?view=sql-server-ver15

    と、よく見てみたら

    この機能は、Microsoft SQL Server の将来のバージョンで削除されます。
    新規の開発作業ではこの機能を使用しないようにし、
    現在この機能を使用しているアプリケーションは修正することを検討してください。

    とあったのでやっぱりマージレプリケーションにする感じでしょうか。

    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月7日 3:59
  • 以下の記載がありましたが、SQL Server 2019 上で コマンドで更新可能なサブスクリプション用のレプリケーションを構築したら、MSからのサポートはないが、この機能は使用できるかな、と考えていましたが、出来なかったのですかね。

    仮に上記ができていたとしても、MSからのサポートがない機能を使用すべきではないと思いますので、マージレプリケーションなどの他の機能を使用したほうが良いかと思います。

    マージ レプリケーションを使用する場合、可能な限り、競合を減らすため、Insert 処理で競合を発生させないよう、パブリッシャ、各サブスクライバ側で挿入するデータの主キーの範囲を決めて、アプリケーション側で制御するなどの工夫を実施されると良いかもしれません。


    この機能は、 SQL Server 2012 から 2016 のバージョンでサポートされています。

    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月7日 4:56
  • MSへのサポートパスをお持ちの場合は、SQL Server 2019 で 更新可能なサブスクリプション 機能を有効化することができるか、念のため、確認されてみてはいかがでしょう? 

    個人的には、SQL Server 2019 バージョン別機能では、EE, STD エディションで 更新可能なサブスクリプションに 〇 が付いていますが、公開情報が間違っているのではないかと推測しています。

    仮に公開情報が正しい場合、EE, STD で 更新可能なサブスクリプション 機能を有効化する方法について確認されると良いかと思います。 (SSMSから作成できないのであれば、T-SQL から 更新可能なサブスクリプションを構築する形になるのではないかと思います。)


    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月7日 5:02
  • NOBTA さん、回答ありがとうございます。

    「更新可能なサブスクリプション」について、ウィザードからは設定できなかったため提示いただいた T-SQL からパブリケーションを作成してみました。

    Microsoft Docs にあったサンプル T-SQL を以下のように少し修正して SQL Server 認証に変更して実行したところ「更新可能なサブスクリプション」が有効な状態でパブリケーションを作成することができました。

    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]
    
    -- Enable transactional replication on the publication database.
    EXEC sp_replicationdboption 
        @dbname=@publicationDB, 
        @optname=N'publish',
        @value = N'true';
    
    -- Execute sp_addlogreader_agent to create the agent job. 
    EXEC sp_addlogreader_agent 
        @publisher_login = @login, 
        @publisher_password = @password,
        @publisher_security_mode = 0;
    
    -- Create a transactional publication that supports immediate updating, 
    -- queued updating, and pull subscriptions. 
    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';
    
    -- Create a new snapshot job for the publication, using a default schedule.
    EXEC sp_addpublication_snapshot 
        @publication = @publication, 
        @publisher_login = @login, 
        @publisher_password = @password,
        @publisher_security_mode = 0;
    GO

    パプリケーションを作成した後はウィザードでサブスクリプションを作成し、各サブスクライバの DB テーブルを更新したところ他 DB のテーブルにもレコードが同期されることを確認できました。

    テーブルトリガーも追加され「msrepl_tran_version」列がテーブルに追加されることも確認できました。

    本来はマージレプリケーションなどに移行すべきかとは思いますが、とりあえずは旧環境をバージョンアップすることが目的でしたので今回はこれで動作確認を行い問題ないようであればこのまま使いたいと思います。

     

    対応ありがとうございました。

    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月7日 5:52

すべての返信

  • トランザクション レプリケーション 更新可能なサブスクリプション機能を以前に使用されていたのでしょうか。 少なからず、SQL Server 2019 では Express Edition ではこの機能を使用できないようです。

    なお、マージ レプリケーションを構築することで、サブスクライバ側の更新を反映させることは可能です。 しかしながら、競合の優先度など、色々と考慮しなければいけない点や、テーブルに rowguid 列が追加される点などに注意が必要になるかと思います。

    トランザクション レプリケーションの更新可能なサブスクリプション
    Editions and supported features of SQL Server 2019 (15.x)

    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月6日 17:57
  • NOBTA さん、回答ありがとうございます。

    まず、私の記載箇所に訂正箇所がありました。(あまり影響はありませんが…)

    • SQL Server 2012 R2 → SQL Server 2014    (Windows Server 2012 R2 と混同してました)
    • テスト環境では SQL Server Standard ではなく Developer でテストしています

     

    教えていただいた情報をもとに少し調べてみました。

     

    更新可能なサブスクリプション機能 について

    • 2014 ではサブスクリプションを右クリックすると「更新方法の設定」というメニューがありました。2019 にはありません。
    • 2014 のパブリケーションのプロパティを開き、「サブスクリプション オプション」の「更新可能なサブスクリプション」を見るとすべて True になっていました。2019 は False でした。
    • 2019 Standard (Developer) をパブリッシャーとサブスクリプション両方にしてみましたが「更新可能なサブスクリプション」は True になりませんでした

    以下の説明ページではパブリケーション作成 (Developer Edition) で「更新可能なサブスクリプションを含むトランザクションパブリケーション」が選択できるようなのですが、その画面を見ても上記の選択項目はなく「トランザクションパブリケーション」他全4つしかありませんでした。何か条件があるのでしょうか。

    トランザクション パブリケーションの更新可能なサブスクリプションの有効化 - SQL Server | Microsoft Docs
    ttps://docs.microsoft.com/ja-jp/sql/relational-databases/replication/publish/enable-updating-subscriptions-for-transactional-publications?view=sql-server-ver15

    と、よく見てみたら

    この機能は、Microsoft SQL Server の将来のバージョンで削除されます。
    新規の開発作業ではこの機能を使用しないようにし、
    現在この機能を使用しているアプリケーションは修正することを検討してください。

    とあったのでやっぱりマージレプリケーションにする感じでしょうか。

    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月7日 3:59
  • 以下の記載がありましたが、SQL Server 2019 上で コマンドで更新可能なサブスクリプション用のレプリケーションを構築したら、MSからのサポートはないが、この機能は使用できるかな、と考えていましたが、出来なかったのですかね。

    仮に上記ができていたとしても、MSからのサポートがない機能を使用すべきではないと思いますので、マージレプリケーションなどの他の機能を使用したほうが良いかと思います。

    マージ レプリケーションを使用する場合、可能な限り、競合を減らすため、Insert 処理で競合を発生させないよう、パブリッシャ、各サブスクライバ側で挿入するデータの主キーの範囲を決めて、アプリケーション側で制御するなどの工夫を実施されると良いかもしれません。


    この機能は、 SQL Server 2012 から 2016 のバージョンでサポートされています。

    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月7日 4:56
  • MSへのサポートパスをお持ちの場合は、SQL Server 2019 で 更新可能なサブスクリプション 機能を有効化することができるか、念のため、確認されてみてはいかがでしょう? 

    個人的には、SQL Server 2019 バージョン別機能では、EE, STD エディションで 更新可能なサブスクリプションに 〇 が付いていますが、公開情報が間違っているのではないかと推測しています。

    仮に公開情報が正しい場合、EE, STD で 更新可能なサブスクリプション 機能を有効化する方法について確認されると良いかと思います。 (SSMSから作成できないのであれば、T-SQL から 更新可能なサブスクリプションを構築する形になるのではないかと思います。)


    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月7日 5:02
  • NOBTA さん、回答ありがとうございます。

    「更新可能なサブスクリプション」について、ウィザードからは設定できなかったため提示いただいた T-SQL からパブリケーションを作成してみました。

    Microsoft Docs にあったサンプル T-SQL を以下のように少し修正して SQL Server 認証に変更して実行したところ「更新可能なサブスクリプション」が有効な状態でパブリケーションを作成することができました。

    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]
    
    -- Enable transactional replication on the publication database.
    EXEC sp_replicationdboption 
        @dbname=@publicationDB, 
        @optname=N'publish',
        @value = N'true';
    
    -- Execute sp_addlogreader_agent to create the agent job. 
    EXEC sp_addlogreader_agent 
        @publisher_login = @login, 
        @publisher_password = @password,
        @publisher_security_mode = 0;
    
    -- Create a transactional publication that supports immediate updating, 
    -- queued updating, and pull subscriptions. 
    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';
    
    -- Create a new snapshot job for the publication, using a default schedule.
    EXEC sp_addpublication_snapshot 
        @publication = @publication, 
        @publisher_login = @login, 
        @publisher_password = @password,
        @publisher_security_mode = 0;
    GO

    パプリケーションを作成した後はウィザードでサブスクリプションを作成し、各サブスクライバの DB テーブルを更新したところ他 DB のテーブルにもレコードが同期されることを確認できました。

    テーブルトリガーも追加され「msrepl_tran_version」列がテーブルに追加されることも確認できました。

    本来はマージレプリケーションなどに移行すべきかとは思いますが、とりあえずは旧環境をバージョンアップすることが目的でしたので今回はこれで動作確認を行い問題ないようであればこのまま使いたいと思います。

     

    対応ありがとうございました。

    • 回答としてマーク おのでら 2021年5月7日 5:52
    2021年5月7日 5:52
  • T-SQL からであれば、SQL Server 2019 でも 更新可能なサブスクリプションが作成できたのですね。

    情報を共有していただき、ありがとうございます。

    なお、一時的に使用するのであれば良いかもしれませんが、今後 数年以上使用し続ける環境の場合は、念のため、MS サポートに対して 本機能を SQL Server 2019 でも使用可能となるかを確認されたほうが良いかもしれません。

    なお、更新可能なサブスクリプションで 「キュー更新」 を使用されているのですね。 オフライン更新が必須でない構成が多かったため、トラブルシューティングが楽な 「即時更新」をよく使用していました。

     
    2021年5月7日 9:22
  • NOBTA さん、回答ありがとうございます。

     

    なお、一時的に使用するのであれば良いかもしれませんが、今後 数年以上使用し続ける環境の場合は、

    念のため、MS サポートに対して 本機能を SQL Server 2019 でも使用可能となるかを確認されたほうが良いかもしれません。


    ありがとうございます、時間があるときに確認したいと思います。

     

    更新可能なサブスクリプションで 「キュー更新」 を使用されているのですね。 
    オフライン更新が必須でない構成が多かったため、
    トラブルシューティングが楽な 「即時更新」をよく使用していました。

    2台のサーバーは負荷分散&冗長化で使用しているので、どちらかのサーバーがなくなっても動くようにキュー処理しています。(とはいっても addpublication の引数はそこまで意識していませんでしたが…)

    2021年5月8日 3:21