質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

Transact-SQL

Transact-SQLはSybase ASEとMIcrosoft SQLサーバで対応されているSQLの機能拡張版です。

Q&A

解決済

3回答

5957閲覧

【SQLServer】ミラーリング構成をスクリプトで実現したい

Utagoe33

総合スコア5

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

Transact-SQL

Transact-SQLはSybase ASEとMIcrosoft SQLサーバで対応されているSQLの機能拡張版です。

0グッド

0クリップ

投稿2017/08/05 09:24

###前提・実現したいこと
SQLServerのデータベースミラーリング構成をコマンドベースで実現しようとしています。
(最終的には順番に実行すればミラーリングが構成できるクエリ群を作成するのが目的です。)
その際、プリンシパル側からフェールオーバーパートナーを指定するコマンドで想定しないエラーが発生し
Web検索を利用して対策を調査しているものの、有力な情報が得られず詰まっております。
何卒お知恵をお貸し頂きたく質問いたします。

環境としては以下の通りです。

■共通
仮想化:VMware
ドメイン参加:有
FW:すべて無効
リモート接続:許可

■サーバ1(プリンシパル)
OS:WindowsServer2012R2
CPU:2*2
メモリ:4GB
ドライブ構成:C(OS領域)40GB、D(データ保存)40GB
アプリケーション:SQLServer2016SE、SSMS2016
SQLServerについて:Windows認証、DBバックアップ済み、エンドポイント作成済み

■サーバ2(ミラーリング)
OS:WindowsServer2012R2
CPU:2*2
メモリ:4GB
ドライブ構成:C(OS領域)40GB、D(データ保存)40GB
アプリケーション:SQLServer2016SE、SSMS2016
SQLServerについて:Windows認証、DBをNORECOVERで復元済み、エンドポイント作成済み、プリンシパルをフェールオーバーパートナーに指定済み

■サーバ3(ウィットネス)
OS:WindowsServer2012R2
CPU:2*2
メモリ:4GB
ドライブ構成:C(OS領域)40GB
アプリケーション:SQLServer2016EXP、SSMS2016
SQLServerについて:Windows認証、エンドポイント作成済み

###発生している問題・エラーメッセージ

■前提
Windows認証のため、ログオンや証明書の作成手順は必要ないものと認識しています。
1.すべてのSQLServerでエンドポイントの作成が成功しています。
portはすべてLithningで、状態はSTARTEDです。
2.ミラーリングサーバからプリンシパルサーバへのSET PARTNERは成功しています。
3.プリンシパルサーバからミラーリングサーバへのSET PARTNERが失敗します。

##該当のソースコード(個別の名称は仮名に置き換えております)

TransactSQL

1USE master; 2GO 3ALTER DATABASE "DB名" 4 SET PARTNER = 'TCP://ミラーリングサーバアドレス:ポート番号'; 5GO

##エラーメッセージ

メッセージ1407、レベル16、状態1、行3 データベース"DB名"のリモートコピーは、データベースのローカルコピーに関連していません。

##SQLServerログ

ソース Server Database mirroring connection error 4 'データ'10054(既存の接続はリモートホストによって強制的に切断されました。)'を受信中にエラーが発生しました。'for'TCP://ミラーリングサーバアドレス:ポート番号'

###試したこと
GUIベース(ミラーリング構成ウィザードから実行)では?
→同様のエラーメッセージが発生し、ミラーリングが構成できませんでした。
SSMSのバージョンによってはミラーリングが失敗することがある?
→SSMSのverを17.1、16.5と試すも解消されませんでした
接続文字列の設定の問題?
→関連する情報を調べていますがまだ理解に至れていません
データベースの作成からやり直してみる
→試みましたが解決しませんでした。

###補足情報(言語/FW/ツール等のバージョンなど)
言語:TransactSQL
FW:すべて無効
SSMSのバージョン:16.5

以上となります。不足している情報などあればご指摘ください。
よろしくお願いいたします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

roleの状態はどうなっていますか?

こちらに、「それぞれのパートナー サーバー インスタンスのデータベース ミラーリング エンドポイントの ROLE は、PARTNER または ALL のいずれかに設定されている必要があります。」とあります。
以下で確認できるようです。
SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

また、バージョンは古いですけど、こちらがスクリプトのサンプルになれば。
※記事はODBC接続する為のものですが、前段として環境構築のスクリプトが記載されています。

投稿2017/08/05 18:45

編集2017/08/05 19:09
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Utagoe33

2017/08/06 01:32

>>sazi 様 ご回答いただきありがとうございます。 ROLEについて、記載を失念しておりました。 ROLEの設定は下記の通りとなります。 サーバ1(プリンシパル) ROLE=ALL サーバ2(ミラーリング) ROLE=ALL サーバ3(ウィットネス) ROLE=WITNESS ROLEの設定は条件を満たしているものと考えております。 また、サンプルスクリプトの情報をご案内頂きありがとうございます。 現在使用しているスクリプトや実行結果と比較して、 解決の糸口を探していきたいと思っています。
guest

0

解決して何よりです。

後続する人達の為に、是非結果の報告をお願いします。

なにより、私も結果を知りたいのでw

あ、間違えて回答で投稿してしまいましたm(__)m

投稿2017/08/09 03:37

編集2017/08/09 04:55
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Utagoe33

2017/08/18 22:27

遅くなりましたが、本件についてご報告します。 データベースミラーリング構成のための前提条件は以下の通りです。 ▼SQLServer構成マネージャー SQLServerのネットワーク構成⇒TCP/IPを有効にする。 ▼Windowsファイアウォール 有効にする場合は、受信の規則で下記通信を許可する。 1.SQLServerのTCP/IPの受信ポート番号 2.ミラーリングエンドポイントのポート番号 ▼SQLServer 1.エンドポイントを作成する。 CREATE ENDPOINT Mirroring STATE = STARTED AS TCP(LISTENER_PORT = ポート番号) FOR database_mirroring (ROLE = ロールタイプ); 2.ログインを作成する。(アドミン権限とエンドポイントへのアクセス権を付与) USE [master] GO CREATE LOGIN [ログイン名] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[日本語] GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [ログイン名] GRANT CONNECT on ENDPOINT::Mirroring TO [ログイン名]; GO 3.プリンシパルデータベースを復旧モデル完全で作成する。 SSMSで手動 4.プリンシパルデータベースの完全バックアップを取得する。 SSMSで手動 5.バックアップファイルをミラーデータベースとしてNORECOVERYでリストアする。 SSMSで手動 6.フェールオーバーパートナーの指定(ミラー⇒プリンシパルが先) ALTER DATABASE データベース名 SET PARTNER='tcp://サーバ名:エンドポイントのポート番号)'; **プリンシパル⇒ミラーのパートナー指定でエラーが出ることがある メッセージ1407、レベル16、状態1、行3 データベース"DB名"のリモートコピーは、データベースのローカルコピーに関連していません。 **原因はよくわかっていませんが、下記の手順で解消しました。 A.ミラー側のパートナー指定をOFFにする。 B.ミラー側のDBをRECOVERYでリストアする。 C.ミラー側のDBを削除する。 D.プリンシパル側のDBの復旧モデルを完全⇒単純にする。 E.プリンシパル側のDBの復旧モデルを単純⇒完全にする。 F.プリンシパル側のDBの完全バックアップを取得する。 G.ミラー側でバックアップをNORECOVERYでリストアする。 H.再度フェールオーバーパートナー指定を実行する。 **エラー対応ここまで 7.監視サーバーの指定 ALTER DATABASE データベース名 SET WITNESS='tcp://サーバ名:エンドポイントのポート番号)'; 問題解消後、これらのスクリプトをバッチから実行するよう作業しておりました。 以上です。ありがとうございました。
sazi

2017/08/19 00:27

報告お疲れ様です。 なにやら、プリンシパルとミラーとのデータに違いがあった場合に起きるエラーのような感じですね。 SQL-Serverのトランザクションログはバックアップを取った段階で消去されるようなので、プリンシバル側での完全バックアップにより、バックアップデータをリストアしたミラー側との差異が無くなることでエラーが解消したのかも。あくまで、推測ですが。
guest

0

本件解決いたしました。

詳細は時間のあるときに記載したいと思います。

ご協力いただきありがとうございました。

投稿2017/08/09 03:21

Utagoe33

総合スコア5

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問