MS SQL Server レプリケーションの設定方法
Microsoft SQL Server は、Windows Server オペレーティングシステムにインストール可能なデータベース管理ソフトウェアです。データベースはあらゆる業界の企業で利用されており、多くのソフトウェアソリューションでは、集中型および分散型のデータベースが活用されています。データベースの可用性とデータの整合性はビジネスにおいて極めて重要であるため、データベースのバックアップとレプリケーションは不可欠です。
SQL Server のレプリケーションの種類、SQL Server におけるレプリケーションの仕組み、および SQL Server レプリケーションの実行方法について学びましょう。
SQL Serverのレプリケーションとは何ですか？
MS SQL Serverのレプリケーションとは、特定のデータベースオブジェクトを含め、あるデータベースから別のデータベースへデータをコピーし、ソースデータベースとターゲットデータベース間でこのデータの同期化されたコピーを維持するプロセスです。SQL Serverのレプリケーションを使用すると、プライマリデータベースと完全に同一のコピーを作成し、データの整合性と完全性を維持しながら、2つのデータベース間の変更を同期させることができます。
MS SQL Serverのレプリケーションで使用される用語
MS SQL Serverのレプリケーションの設定方法について詳しく説明する前に、まずは主な用語とレプリケーションモデルについて簡単に確認しておきましょう。
Articles テーブル、プロシージャ、関数、ビューなど、複製対象となる基本単位です。フィルターを使用することで、記事を垂直方向または水平方向に拡張できます。同じオブジェクトに対して複数の記事を作成することができます。
A publication これは、論理的にまとまった一連の記事です。これは、レプリケーションの対象として指定されたデータベースからの最終的なエンティティのセットです。
A filter これは、記事に対する一連の条件です。MS SQL Serverのレプリケーションでは、フィルターを使用してレプリケーション対象となるエンティティをカスタマイズすることができ、その結果、トラフィックや冗長性を低減し、データベースレプリカに保存されるデータ量を削減できます。たとえば、フィルターを使用して最も重要なテーブルやフィールドのみを選択し、そのデータのみをレプリケートすることができます。
Agents これらは、リレーショナルデータベース管理システムのバックグラウンドサービスとして機能するMS SQL Serverのコンポーネントであり、MS SQLデータベースのバックアップやレプリケーションなどのジョブの自動実行をスケジュールするために使用されます。エージェントには、スナップショット・エージェント、ログ・リーダー・エージェント、ディストリビューション・エージェント、マージ・エージェント、キュー・リーダー・エージェントの5種類があります。
Metadata これは、データベース内のエンティティを記述するために使用されるデータです。MS SQL Server インスタンス、データベース インスタンス、およびデータベース エンティティに関する情報を取得できる、豊富な組み込みのメタデータ関数が用意されています。
SQL Database レプリケーションにおける役割
MS SQLデータベースのレプリケーションには、主に3つの役割があります。それは、ディストリビューター、パブリッシャー、およびサブスクライバーです。
A Distributorこれは、パブリケーションからトランザクションを収集し、それらをサブスクライバーに配信するように構成されたMS SQLデータベースインスタンスです。ディストリビューターは、レプリケートされたトランザクションを格納するためのデータベースとして機能します。ディストリビューター・データベースは、パブリッシャーとディストリビューターの両方の役割を兼ねるものと見なすことができます。ローカル・ディストリビューター・モデルでは、単一の Microsoft SQL Server インスタンスがパブリッシャーとディストリビューターの両方を実行します。リモート・ディストリビューター・モデルは、サブスクライバーが単一の Microsoft SQL Server インスタンスを使用して異なるパブリケーションを取得するように構成したい場合(集中型ディストリビューション)に使用できます。このモデルでは、パブリッシャーとディストリビューターは別々のサーバー上で実行されます。
A Publisherこれは、パブリケーションが構成されているメインのデータベースのコピーであり、レプリケーションプロセスで使用されるように構成された他のMS SQLサーバーに対してデータを提供します。パブリッシャーは、複数のパブリケーションを持つことができます。A Subscriberこれは、パブリケーションからレプリケートされたデータを受信するデータベースです。1つのサブスクライバーが、複数のパブリッシャーやパブリケーションからデータを受信することができます。サブスクライバーが1つの場合は、シングルサブスクライバーモデルが使用されます。1つのパブリケーションに複数のサブスクライバーが接続されている場合は、マルチサブスクライバーモデルが使用されます。Subscriptionこれは、購読者に配信されるべき出版物のコピーを請求するものです。サブスクリプションは、受信すべき出版物のデータ、およびそのデータがどこでいつ受信されるかを定義するために使用されます。サブスクリプションには2つの種類があります:Push subscription: 変更されたデータは、ディストリビューターからサブスクライバーのデータベースへ強制的に送信されます。サブスクライバーからのリクエストは不要です。Pull subscription: パブリッシャー側で変更されたデータが、サブスクライバーから要求されます。エージェントはサブスクライバー側で実行されます。
サブスクリプション・データベースとは、MS SQLレプリケーション・モデルにおけるターゲット・データベースのことです。
その 複数の発行者－複数の購読者 このモデルでは、パブリッシャーがMS SQLサーバーの1台でサブスクライバーとして機能することができます。このMS SQL Serverレプリケーションモデルを使用する際は、更新の競合が発生しないよう十分注意してください。
MS SQL Server のレプリケーションの種類
MS SQL Serverのレプリケーションは、データベース間でデータを継続的または定期的に、スケジュールに従ってコピーおよび同期させるための技術です。レプリケーションの方向性については、MS SQL Serverのレプリケーションには、一方向、一対多、双方向、および多対一の形式があります。MS SQL Serverのレプリケーションには、スナップショット・レプリケーション、トランザクション・レプリケーション、ピア・ツー・ピア・レプリケーション、マージ・レプリケーションの4つの種類があります。
スナップショットレプリケーション
Snapshot replication データベースのスナップショットが作成された時点のデータをそのまま複製するために使用されます。このタイプのレプリケーションは、データの変更頻度が低い場合、マスターデータベースよりも古いレプリカが存在しても問題にならない場合、または短期間に大量の変更が行われる場合に適しています。 スナップショットレプリケーションでは変更追跡は使用されません。
たとえば、為替レートや価格表が1日1回更新され、メインサーバーから支店のサーバーへ配布する必要がある場合などに、スナップショットレプリケーションを使用できます。
トランザクションレプリケーション
Transactional replication これは、マスターデータベースからデータベースのレプリカへ、データをリアルタイム(またはニアリアルタイム)で配信する、定期的な自動レプリケーションです。 トランザクションレプリケーションは、スナップショットレプリケーションよりも複雑です。実行されたすべてのトランザクションとデータベースの最終状態がレプリケートされるため、レプリカ上でトランザクション履歴全体を監視することが可能になります。
トランザクションレプリケーションのプロセス開始時には、サブスクライバーにスナップショットが適用され、その後、データに変更が加えられるたびに、マスターデータベースからデータベースレプリカへデータが継続的に転送されます。トランザクションレプリケーションは、一方向レプリケーションとして広く利用されています。
トランザクションレプリケーションのユースケース:
- メインのデータベースサーバーに障害が発生した場合にフェイルオーバーに使用するための、データベースレプリカを備えたデータベースサーバーを作成する。
- 各支店に複数のパブリッシャーを配置し、本社に1つのサブスクライバーを設置して、支店で行われた業務に関するレポートを受信する。
- 変更が発生した直後にレプリケーションを行う。
- ソースデータベースのデータは頻繁に変更されます。
ピアツーピア複製
Peer-to-peer replication は、データベースのデータを複数のサブスクライバーに同時に複製するために使用されます。このMS SQL Serverのレプリケーション方式は、データベースサーバーが世界中に分散している場合に利用できます。変更はどのデータベースサーバーで行ってもよく、その変更はすべてのデータベースサーバーに反映されます。ピアツーピア・レプリケーションは、データベースを利用するアプリケーションのスケールアウトに役立ちます。その主な動作原理は、トランザクション・レプリケーションに基づいています。
以下では、世界中に分散しているデータベースサーバー間で、MS SQL Serverのピアツーピアレプリケーションをどのように活用できるかをご紹介します。
マージレプリケーション
Merge replication これは、データベースサーバー間で継続的な接続が確立できない場合に、サーバーとクライアントの環境においてデータを同期させるために通常使用される双方向レプリケーションの一種です。両方のデータベースサーバー間でネットワーク接続が確立されると、マージレプリケーションエージェントは両方のデータベースで行われた変更を検出し、データベースを修正して状態を同期・更新します。マージレプリケーションはトランザクションレプリケーションと似ていますが、データはパブリッシャーからサブスクライバーへ、そしてその逆の方向にもレプリケートされます。
この種のデータベースレプリケーションは、MS SQL Serverのレプリケーション方式の中で最も複雑であり、ほとんど使用されません。 例えば、マージレプリケーションは、共有倉庫と連携する複数のピアストアで使用できます。各ストアは倉庫データベース内の情報を変更することが許可されていますが、同時に、倉庫への商品出荷や資材の納入後には、すべてのストアがデータベースの最新の状態を保持している必要があります。マージレプリケーションは、更新された情報をメイン(または中央)データベースと支店データベースの両方で同時に利用可能にする必要がある場合に使用できます。
MS SQL Server レプリケーションの要件
インバウンドトラフィックを受け入れるには、以下のポートを開く必要があります:
- TCP 1433、1434、2383、2382、135、80、443
- UDP 1434
MS SQL Server をインストールする前に、各ホストで Windows ファイアウォールを設定し、着信トラフィック用に適切なポートを有効にしてください。MS SQL レプリケーションに参加するホストは、ホスト名を使用して相互に解決できる必要があります。
MS SQL Server レプリケーションを設定する前に、MS SQL Server 用に以下のソフトウェアをインストールする必要があります:
- .NET Framework – 一連のライブラリ
- MS SQL Server – データベースサーバーソフトウェア
- MS SQL Server Management Studio (SSMS) – GUI(グラフィカル・ユーザー・インターフェース)を備えた、MS SQLデータベースを管理するためのソフトウェア。
注: この記事では、設定にMS SQL Server 2016を使用しています。同じ原理を用いて、より新しいバージョンのSQL Serverでもレプリケーションを設定することができます。
ソースデータベースが配置されている最初のマシンに MS SQL Server 2016 をインストールする場合、データベースが正常に機能するためには、2 台目のマシンにも MS SQL Server 2016 をインストールする必要がある点に注意してください。
たとえば、MS SQL トランザクションレプリケーションを設定する場合、パブリッシャーが構成されているソースデータベースサーバーから 2 バージョン以内のバージョンの 2 台目のデータベースサーバー(サブスクライバーが構成されている場所)を使用できます。 MS SQL Server上のパブリッシャーのバージョンが2016の場合、ディストリビューターは2016、2017、2019、および2022のバージョンで構成でき、サブスクライバーはMS SQL Server 2012、2014、2016、2017、および2019で構成できます。 ディストリビューターのバージョンは、パブリッシャーのバージョンより低いことはできません。たとえば、2台目のマシンにMS SQL Server 2008をインストールした場合、レプリケーションは機能しません。
MS SQLデータベースのレプリケーションに関する基本的な推奨事項
MS SQL Server の環境設定を行う前に、以下の点を考慮してください:
- IDフィールドとトリガーには制限があります。
- パブリケーションには、主キーを持つテーブルのみを含めることができます。
- 大規模なデータベースでは、大量のコンピューティングリソースを消費することを避けるため、スナップショットの作成スケジュール機能を使用しないことをお勧めします。
- サブスクライバー上に存在するデータベースレプリカのデータを変更する際は注意が必要です。データを変更するトランザクションが送信される際に、そのデータが編集または削除されていると、問題が解決されるまでレプリケーションが停止する可能性があります。
環境の設定
MS SQLのレプリケーションを初めて設定する際は、まずテスト環境で行うことをお勧めします。例えば、仮想マシン上で動作するSQL Serverでレプリケーションを設定します。このチュートリアルでは、Windows Server 2016とMS SQL Server 2016を実行している2台のホストを使用して、MS SQL Serverのレプリケーションについて解説します。
このブログ記事の作成に使用したテスト環境の構成を確認し、MS SQL Serverのレプリケーション設定についてより深く理解しましょう。
Host 1
- IPアドレス:192.168.101.101
- ホスト名: MSSQL01
- MS SQL Server インスタンス ID: MSSQLSERVER1
Host 2
- IPアドレス:192.168.101.102
- ホスト名: MSSQL02
- MS SQL Server インスタンス ID: MSSQLSERVER2
両方のマシンには、ディスク構成としてディスク C: とディスク D: があります。
MS SQL Server のレプリケーション設定を練習する際は、インストール中に Windows ファイアウォールを一時的に無効にすることができます。
このチュートリアルは MS SQL Server のレプリケーション設定に焦点を当てているため、本ブログ記事では MS SQL Server のインストール方法については詳しく説明しません。この例では、両方の MS SQL Server は PolyBase なしでインストールされています。
MS SQL Server のインストールが完了したら、MS SQL Server のレプリケーションに必要な機能がインストールされていることを確認してください。 なお、SQL Server レプリケーションや R-Services などのデータベースエンジンサービスは、MS SQL Server のインストール時に選択する必要があります。この例では、デフォルトのインストールパス(C:Program FilesMicrosoft SQL Server)を使用しています。
その他の設定:
- 混合認証モード(Windows 認証と MS SQL Server 認証)
- データのルートディレクトリ:D:MSSQL_Server
- システムデータベースのディレクトリ:D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
- ユーザーデータベースのディレクトリ:D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
- ユーザーデータベースのログディレクトリ:D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
- バックアップディレクトリ:D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
各マシンにMS SQL Server 2016とSQL Server Management Studioをインストールしたら、データベースのレプリケーションに向けてMS SQL Serverの準備を行うことができます。
MS SQL Server レプリケーションの準備
データベースのレプリケーションを開始するには、事前にサーバーの設定を行う必要があります。この例では、MS SQL Serverのレプリケーション・エージェント用に1つのWindowsアカウントを使用します。
- 作成する mssql 両方のサーバーでユーザーを作成し、同じパスワードを設定します。
- その mssql この例では、ユーザーは以下のグループのメンバーです:
- 管理者(ローカル マシンのローカル管理者。ドメイン管理者は除く)
- SQLユーザーグループMSSQLSERVER1
- SQLServer2005SQLBrowserUser$MSSQL01
- [編集]ボタンを押すと、ユーザーやグループを編集できます
Win+R、開幕CMD、そしてlusrmgr.mscコマンド。
この例で使用している 2 台の Windows Server マシンは、Active Directory に所属していません。Active Directory を使用している場合は、 mssql ドメイン コントローラー上のユーザー。
MS SQL Serverへの接続
- SQL Server Management Studio を起動します。
- ログイン(スクリーンショット参照)
saSQL Server 認証を使用することで。- MSSQL01MSSQLSERVER1 は、最初のサーバーのホスト名および MS SQL インスタンス名です。
- MSSQL02MSSQLSERVER2 は、2台目のサーバーのホスト名およびMS SQLインスタンス名です。
同様に、2台目のサーバー(MSSQL02)から、2つ目のMS SQL Serverインスタンス(MSSQLSERVER2)に接続することもできます。 また、SQL Server Management Studioで認証情報を入力することで、最初のMS SQL Server(MSSQL01)から2番目のMS SQL Serverインスタンス(MSSQLSERVER2)に接続することもできます。1つのSQL Server Management Studioインスタンスで、両方のMS SQL Serverインスタンス(MSSQL01およびMSSQL02)に接続できます。
これを行うには、オブジェクトエクスプローラーで、[ Connect > Database engineこのチュートリアルでは、SQL Server Management Studio を使用して、MSSQL01 から MSSQLSERVER1 に、また MSSQL02 から MSSQLSERVER2 に接続し、MS SQL サーバーの設定を行います。
エージェントの起動
MS SQL Server インスタンスにログインすると、エージェントが実行されていないことがわかります。デフォルトでは、SQL Server エージェントは自動的に開始されません。このサービスを手動で開始することもできますが、Windows の起動後に自動的に開始されるように設定することをお勧めします。
エージェント・サービスを自動起動するように設定するには:
- 報道
Win+R、実行cmd,そして、services.mscコマンド。 - SQL Server Agent サービスのプロパティを開き、S を設定しますスタートアップの種類を
Automatic.
MS SQL Server のユーザー設定
SQL Server Management StudioでMSSQLSERVER1インスタンスに接続した後、ユーザーの設定を行う必要があります:
- 移動
Object Explorerそして開くSecurity>Logins. - 右クリック
Loginsそして選択してくださいNew Login. 選択Windows authentication. - ログイン名を入力してください MSSQL ～の中で 概要 セクション。
- クリック
Search、その後、Check names確認して、クリックしてくださいOK設定を保存するには、2回クリックしてください。
- さて、 MSSQL01mssql Windowsユーザーが、データベースにログインできるユーザーのリストに追加されます(同様に、 mssql ユーザーに ログイン (2台目のマシン"MSSQL02"上のSQL Server Management Studioで)。
- ""を追加する mssql ユーザーを
sysadminsのサーバーロール セキュリティ SQL Server Management Studio でのデータベースの設定。 - 移動
MSSQL01MSSQLSERVER1>Server roles、右クリックsysadmin、そして開くProperties. - その 会員 ページで、[クリック]
Add、ユーザー名を入力してください mssql, をクリックしてCheck names. - ユーザー名のチェックボックスを選択してください MSSQL01mssql をクリックして
OK.
- 2台目のマシン(この場合はMSSQL02)でも同じ設定を行ってください。
- 両方のマシンを再起動してください。
これで、両方のサーバーで Windows 認証を使用してログインできるようになりました。
バックアップからデータベースをインポートする
バックアップからサンプルデータベースをインポートし、そのデータベースを1台目のマシンから2台目のマシンへレプリケートしてみましょう。 AdventureWorks 2016 この例では、このデータベースがサンプルデータベースとして使用されています。
- をコピーして AdventureWorks2016.bak データベースのバックアップファイルをMSSQLのバックアップディレクトリに保存します。今回のケースでは、第1サーバー上のこのディレクトリは D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup です。
- サンプルデータベースをインポートします。最初のマシンで SQL Server Management Studio を起動し、[ MSSQL01MSSQLSERVER1、右クリック
Databases,そして選択してくださいRestore Databaseコンテキストメニューで。
- その データベースを復元する ウィンドウで、必要なパラメータを選択してください:
- ソース:
Device. - をクリックして
three dotsデータベースのバックアップファイルを閲覧するには。- その バックアップデバイスを選択 ウィンドウで、バックアップメディアの種類を選択してください:
file. - クリック
Add.
- その バックアップデバイスを選択 ウィンドウで、バックアップメディアの種類を選択してください:
- 必要なものを選択してください .bak ファイル – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
- ヒット
OK、そしてOKまたしても。
- ソース:
- その AdventureWorks 2016 データベースの復元が正常に完了しました。
データベースのレプリカが実行される2台目のマシン上のバックアップから、データベースをインポートすることができます。この方法では、空のデータベースにデータベースデータ全体をコピーすることなく、バックアップ作成以降の変更内容をコピーすることからレプリケーションが開始されるため、ネットワークトラフィックを削減できます。
2台目のサーバー上のバックアップからデータベースを復元し、データベースの名前を AdventureWorks 2016ここで、"r"は"レプリカ"を意味します。
最終的に、次のようになります:
| ホスト名:MSSQLインスタンス名 | データベース名 |
| MSSQL01MSSQLSERVER1 | AdventureWorks 2016 |
| MSSQL02MSSQLSERVER2 | AdventureWorks 2016 |
データベースをインポートした後、MS SQLサーバーの準備を整えるために、いくつかのチューニングを行う必要があります
- ～について MSSQL01 マシン、[ここ](https://example.com) へ移動
MSSQL01MSSQLSERVER1>Security>Logins、選択MSSQL01mssql. 右クリック(またはダブルクリック) mssql ユーザーを選択Properties. - で サーバーの役割、の横にあるチェックボックスを選択し、
dbcreator役割。
- ～について ユーザーマッピング ページで、このログインに紐付けられているユーザーを選択し、
AdventureWorks2016データベースのチェックボックス(選択) AdventureWorks 2016 (2台目のサーバーでも同様に)。 - その データベース・ロールのメンバーシップ セクションで、[チェック] を入れてください
db_ownerチェックボックス。
- クリック
OK設定を保存するには。
MSSQL02 マシンでも同様の設定を行ってください。その後、データベースのレプリケーションに必要な MS SQL Server コンポーネントの設定を行うことができます。
データベースのレプリケーションの設定
グラフィカルモードでのレプリケーション設定は、最も便利な方法です。次の設定は、SQL Server Management Studio で行います。 この例では、最もよく使用されるMS SQL Serverのレプリケーションタイプの一つであるトランザクション・データベース・レプリケーションについて説明します。
以下のスクリーンショットは、SQL Server Management Studioにおけるメインのデータベースサーバー(MSSQL01MSSQLSERVER1)のビューと、セカンダリサーバー(MSSQL02MSSQLSERVER2)のビューを示しています。
配布の設定
ディストリビューションは、複数のパブリッシャーやサブスクライバーで利用できます。この例では、ソースデータベースが格納されているメインサーバーでディストリビューションが構成されています。メインサーバー(MSSQL01MSSQLSERVER1)上で、右クリックして Replication そして、コンテキストメニューから、[選択] を選択します Configure Distribution.
その 配布設定ウィザード 開きます。
Distributor. この例では、メインサーバー(MSSQL01MSSQLSERVER1)上で実行中の現在のデータベースインスタンスをディストリビューターとして選択します。[クリック]Nextウィザードの次の手順に進むたびに。SQL Server Agent Start. 上記の手順に従ってMS SQL Server Agentを自動起動するように設定していない場合、次のメッセージが表示されます。[選択]Yes, configure the SQL Server Agent service to start automatically.
Snapshot Folder. ここではデフォルトのパスをそのまま使用できます。レプリケーションの初期化にはスナップショットが必要です。スナップショットディレクトリが配置されているディスクに十分な空き容量があることを確認してください。空き容量は、少なくともレプリケートされるデータベースのサイズと同等以上である必要があります。Distribution Database. ディストリビューション・データベース名を入力してください。デフォルト名をそのまま使用することもできます(流通) および配布用データベースファイルとログファイル用のフォルダ。
Publishers. ディストリビューターにアクセスできるMS SQL Serverレプリケーションのパブリッシャーを定義します。プライマリMS SQL Serverインスタンス(レプリケートされるソースデータベースをホストしているインスタンス)上で、ディストリビューションデータベース名の横にあるチェックボックスを選択します。この例では、インスタンス名はMSSQL01MSSQLSERVER1、ディストリビューションデータベース名は 流通.Wizard Actions. 選択Configure distributionウィザードの最終ステップで配布設定を行います。この例では、後で実行するためのスクリプトファイルは生成しません。
Complete the Wizard. "配布設定の概要"を確認し、[クリック] してくださいFinishディストリビューターを作成する。
- その 成功 ディストリビューターの作成と設定が正常に完了していれば、ステータスが表示されるはずです。
SQL Server Agent を自動起動するように設定する際にエラーが発生した場合は、サービスの設定を開き、SQL Server Agent の起動モードを確認してください(設定方法については、このブログ記事の上記"Agent の起動設定"を参照してください)。
また、SQL Server Management Studio で SQL Server Agent のプロパティを開き、サービスの状態や再起動オプションを確認することもできます。右クリックして SQL Server Agent リストの最後にある オブジェクト エクスプローラー そしてクリック Properties エージェントのプロパティを表示または編集するには。
パブリッシャーの設定
配布の設定が完了したら、パブリッシャーの設定を行うことができます。パブリッシャーは、レプリケート対象のマスターデータベースが格納されているメインサーバー(MSSQL01MSSQLSERVER1)上で設定する必要があります。選択してください Replication、右クリック Local Publications そして、コンテキストメニューから、[ New Publication.
その 新しい出版ウィザード 開きます。
Publication Database. レプリケートしたいデータベースを選択します(AdventureWorks 2016 (この場合)。クリックNextウィザードの各ステップで、次へ進むには
Publication Typeこの手順では、データベースに対してMS SQL Serverのレプリケーションの種類を選択できます。ここでは、広く利用されているレプリケーションの種類である"トランザクション・パブリケーション"を選択してみましょう。Articles. 記事として公開するテーブル、プロシージャ、ビュー、インデックス付きビュー、ユーザー定義関数など、必要なオブジェクトを選択します。必要に応じて、テーブル内のカスタムフィールドのレプリケーションを選択したり、記事のプロパティを設定したりすることも可能です。この例では、いくつかのテーブルが選択されています。
Filter Table Rowsこの例ではフィルタは追加されていません(これがフィルタのデフォルト設定です)。必要に応じてフィルタを追加することができます。Snapshot Agent. スナップショット・エージェントの実行タイミングを指定します。エージェントをすぐに実行するように設定しましょう。選択してくださいCreate a snapshot immediately and keep the snapshot available to initialize subscriptions.
Agent Security. 選択Use the security settings from the Snapshot AgentをクリックしてくださいSecurity Settingsボタンをクリックして、エージェントを実行するアカウントを選択します。その スナップショット・エージェントのセキュリティ 表示されるウィンドウに、の認証情報を入力してください mssql 以前に作成したWindowsユーザーです。"パブリッシャーに接続"を選択してください
By impersonating the process account"OK"をクリックして設定を保存し、ウィザードに戻ります。
必要なユーザーを定義すると、そのユーザーを スナップショット・エージェント そして ログリーダーエージェント セクション。
Wizard Actions. ウィザードの最終ステップで、上部のチェックボックスを選択してパブリケーションを作成します。Complete the Wizard. 公開設定を確認し、[クリック] してくださいFinish新しい出版物を作成する。
その 出版物の作成 ウィンドウでは、新しいパブリケーションの作成進捗状況を確認できます。しばらく待つと、すべてが正しく行われていれば、成功ステータスが表示されるはずです。
パブリケーションが作成されました。次の手順で"オブジェクト エクスプローラー"に表示されます。 Replication > Local Publications.
サブスクライバーの設定
ご存じのとおり、MS SQL Serverのレプリケーションには、プル型とプッシュ型の2種類があります。プッシュ型レプリケーションを設定する場合は、サブスクライバーがメインのデータベースサーバー(この場合はMSSQL01)上でエージェントを実行するように設定する必要があります。プル型レプリケーションを設定する場合は、サブスクライバーが2台目のマシン(MSSQL02)、つまりデータベースのレプリカが作成されるマシン上でエージェントを実行するように設定する必要があります。
プッシュレプリケーションを設定し、マスターデータベースが存在する最初のMS SQL Server(MSSQL01MSSQLSERVER1)で新しいサブスクリプションを作成しましょう。
オブジェクトエクスプローラーで、[ Replication、右クリック Local Subscriptions そして、コンテキストメニューから、[選択] を選択します New Subscriptions.
その 新しいサブスクリプションウィザード 開きます。
Publication. 新規サブスクリプションを作成するパブリケーションを選択します。この例では、パブリッシャーの名前は MSSQL01MSSQLSERVER1 で、パブリケーション名(先に作成されたもの)は AdvWorks_Pub. クリック 次へ ウィザードの各ステップで、続行するにはDistribution Agent Location. プッシュ・サブスクリプションまたはプル・サブスクリプションのいずれかを選択して、レプリケーションの種類を指定します。この例では、すべてのエージェントをソースサーバー側で実行したいので、最初のオプションを選択してプッシュ・サブスクリプションを作成します。これにより、MS SQL Serverのレプリケーションを一元的に管理できるようになります。
Subscribers. デフォルトでは、ウィザードを実行しているサーバー(この場合は MSSQL01MSSQLSERVER1)がサブスクライバーとして表示され、サブスクリプション データベースは定義されていません。新しいサブスクライバーを追加し、2 台目のデータベース サーバー(MSSQL01MSSQLSERVER2)にあるサブスクリプション データベースを選択しましょう。[クリック]Add Subscriberそして、コンテキストメニューから、[選択] を選択しますAdd SQL Server Subscriber.- ポップアップウィンドウで、2つ目のMSSQL Serverインスタンス(この例ではMSSQL01MSSQLSERVER2)の認証情報を入力し、[クリック]します
Connect.
- データベースのレプリカを保存する2台目のサーバー(MSSQL02MSSQLSERVER2)のチェックボックスを選択し、 定期購読データベース ドロップダウンメニューから、データベースレプリカとして使用する新しいデータベース、またはバックアップから復元した既存のデータベースを選択します。
この例では、 AdventureWorks 2016 メイン(ソース)を復元することで、2台目のサーバー上に作成されました AdventureWorks 2016 バックアップからデータベースを復元してレプリケーションを開始します。レプリケーションは、レプリケーションプロセスの開始後にデータベース全体をコピーするのではなく、新しいデータのみを複製することで開始されます。したがって、 AdventureWorks 2016 この例では、サブスクリプション・データベースとして選択されています。
- ポップアップウィンドウで、2つ目のMSSQL Serverインスタンス(この例ではMSSQL01MSSQLSERVER2)の認証情報を入力し、[クリック]します
Distribution Agent Security. 三つの点があるボタンをクリックしてください(…)、そしてディストリビューション・エージェントのユーザーおよびその他のセキュリティ設定を選択します。その 販売代理店のセキュリティ 表示されるウィンドウで、ディストリビューション・エージェントが MSSQL01 以下のホスト mssql ユーザーアカウント。のパスワードを入力してください。 mssql Windowsユーザー。選択
Connect to the Distributor by impersonating the process accountそして選択してくださいConnect to the Subscriber by impersonating the process account. ヒット 了解 設定を保存するには。
これで、サブスクリプションのプロパティの設定が完了しました。
Synchronization Scheduleディストリビュータ上に配置されているエージェントを選択して 連続運転 現在の加入者について。Initialize Subscriptions. を選択してくださいInitializeチェックボックスをオンにし、ドロップダウンメニューから選択してくださいImmediatelyサブスクリプションを初期化するタイミングについて。また、 メモリ最適化 必要に応じてこのオプションを選択してください。
Wizard Actions. ウィザードの最後にサブスクリプションを作成するには、上のチェックボックスを選択してください。Complete the Wizard. 購読設定を確認し、[クリック] してくださいFinishサブスクリプションを作成するには。
- サブスクリプションが作成されるまでお待ちください。もし 成功 ステータスがこの状態であれば、サブスクリプションが正常に作成されたことを意味します。
- SQL Server でレプリケーションを設定すると、オブジェクト エクスプローラーに 3 つのジョブが表示されます。これらは、次の場所から確認できます。
SQL Server Agent>Jobs.
レプリケーション設定の確定
ディストリビューター、パブリッシャー、およびサブスクライバーの設定が完了したら、MS SQL Serverのレプリケーションの状態を確認できます。
- 最初のサーバー(MSSQL01MSSQLSERVER1)で、レプリケーション・モニターを起動し、MS SQL Serverのレプリケーションの状態を確認します。SQL Server Management Studioで、MS SQL Serverインスタンス(MSSQLSERVER1)を選択し、[
Replication、右クリックLocal Publicationsそして、コンテキストメニューから、[選択] を選択しますLaunch Replication Monitor.
- ある ログリーダーエージェント このケースではエラーが発生しました。エラーの詳細を確認するには、左ペインでソースデータベース(パブリッシャー)を選択し、
Agents右側のペインにあるタブをクリックし、エラー名をダブルクリックします。
- 開いたウィンドウには、エージェントの履歴とエラーメッセージが表示されます。エラーメッセージは以下の通りです:
- MSSQL01MSSQLSERVER1 サーバーで sp_replcmds を実行できませんでした。原因: MSSQL_REPL。エラー番号: MSSQL_REPL20011。
- データベースのプリンシパルとして実行できません。プリンシパル"dbo"が存在しないか、この種類のプリンシパルを代行できないか、または必要な権限がありません。(ソース: MSSQLServer、エラー番号: 15517)。
2つ目のエラーメッセージは、何らかの権限が不足していることを示唆しています。このエラーを修正しましょう。
- MS SQL Management Studio で新しいクエリを作成し、このクエリを実行します。メインウィンドウで、
New Queryボタン。 - メインウィンドウの"SQLクエリ"セクションに、次のクエリを入力してください:
USE AdventureWorks2016GOEXEC sp_changedbowner 'sa'GOをクリックして
Executeボタン。
コマンドが正常に完了しました。
- 次に、次のページへ進んでください
MSSQL01MSSQLSERVER1>Replication>Local Publications>[AdventureWorks2016]: AdvWorks_Pub. パブリケーション名を右クリックし、コンテキストメニューから スナップショット・エージェントのステータスを表示。クリックできますAction>Refreshステータスを更新し、Reinitialize All Subscriptions各サブスクライバーにスナップショットを適用する。これで問題はすべて解決し、エラーも表示されなくなりました。MS SQL Serverのレプリケーションは正常に動作するはずです。
レプリケーションの仕組みを確認する
MS SQL Serverのレプリケーションが実際にどのように動作するか見てみましょう。テーブルの内容を表示して、 AdventureWorks 2016 最初のMS SQLサーバーに保存されているデータベース(MSSQL01MSQLSERVER1)。この例では、 人物.住所の種類 テーブル。これを行うには、次のクエリを実行します:
USE AdventureWorks2016;
GO
SELECT *
FROM Person.AddressType
;
クエリを実行した結果は、以下のスクリーンショットに表示されています:
2台目のサーバーでも同様のクエリを実行し、 人物.住所の種類 の AdventureWorks 2016 MSSQL02MSSQLSERVER2に保存されているデータベース。
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
上のスクリーンショットと下のスクリーンショットを比較すると、 人物.住所タイプ 両方のデータベース(1台目のサーバー上のソースデータベースと、2台目のサーバー上のデータベースレプリカであるターゲットデータベース)で同一です。
の表から1行削除しましょう 人物の住所タイプ 表から AdventureWorks 2016 最初のサーバー(MSSQL01MSSQLSERVER1)上のデータベース(ソース)。次のクエリを実行して、以下の条件に一致する行を削除します。 "請求" その後にテーブルの名前を指定し、その内容を表示するには:
DELETE FROM Person.AddressType WHERE Name='Billing';
SELECT * FROM Person.AddressType;
ご覧の通り、最初の行には アドレス種別ID 1 と名前 "請求" はから削除されました 人物.住所の種類 の表 AdventureWorks 2016 データベース上の MSSQL01 マシン。
トランザクションレプリケーションが実行されています。 人物.住所の種類 の表 AdventureWorks 2016 データベース上の MSSQL02 マシン。テーブルの内容を確認するために、上記と同様のクエリをもう一度実行してください:
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
複製が行われた結果、最初の行も 人物.住所タイプ データベースのレプリカとして機能するセカンダリデータベース内のテーブル(AdventureWorks 2016)。結果は以下のスクリーンショットでご確認いただけます。
SQL Server のデータベースレプリケーションは正常に動作しています。
結論
MS SQL Serverのレプリケーションには、スナップショット、トランザクション、ピアツーピア、マージの4種類があります。トランザクション・レプリケーションが広く利用されているため、本記事ではこのMS SQL Serverのレプリケーションタイプについて設定方法を解説します。 データベースのレプリケーションを機能させるには、ディストリビューター、パブリッシャー、およびサブスクライバーを設定する必要があります。サブスクライバーは、ソースサーバー(プッシュレプリケーション)およびターゲットサーバー(プルレプリケーション)に設定できます。
ただし、レプリケーションと MS SQLデータベースのバックアップ 成功の可能性を高めるために データベースのデータ復旧.
