データベースミラーリング の証明書を更新する

 
文書番号：10131


ワークグループ環境で証明書を使用してデータベースミラーリングを構築した環境で、証明書の有効期限が迫ってきたとき、切れたときに証明書を更新をする手順です。

有効期限が切れると SQL Server 上で定期的にエラーログが出力されますが、現在構成されているミラーリングは解除されず、そのまま稼動し続けます。

本手順では証明書を新しく作成し、その証明書を既存のエンドポイントとログインに割り当てます。

事前準備

  • 証明書ファイルを書き出すための一時フォルダーを準備します。
    本手順ではCドライブ直下に「temp」というフォルダーを作成しました。

    C:\temp　　となります。


    クリックで拡大

    別の場所に作成しても問題ありませんが以降のコマンドで指定している部分は置き換えて実行してください。


ミラーリングを削除する

  1. 現在ミラー構成中のデータベースは一旦、ミラーを削除します。
    データベースを右クリックして[タスク] － [ミラー]をクリックします。

    クリックで拡大
  2. 現在のミラーリングの状態が表示されています。
    「ミラーリングの削除」をクリックします。

    クリックで拡大
  3. データベースミラーリングを削除する確認メッセージが表示されます。
    「はい」をクリックします。

    クリックで拡大
  4. データベースミラーリングが削除されました。
    「OK」をクリックして画面を終了します。

    クリックで拡大

新しい証明書を作成し、エンドポイントを変更する


各サーバーで新しい証明書を作成し、その証明書をエンドポイントへ割り付けます。
その後、作成した証明書をバックアップし、他のサーバーへコピーします。


※既存の証明書はエンドポイントに割りついているので同じ名前で証明書を作成することはできません。
　別の名前で証明書を作成し、エンドポイントに割り付けてから既存の証明書を削除することができます。

  1. 最初に、プリンシパルサーバーで SQL Server Management Studio を起動します。
    ログインでは Windows 認証でプリンシパルサーバーへ接続します。
    ※Windows と SQL Server の両方に管理者権限を持ったアカウントで接続します。

    クリックで拡大
  2. 画面左上の「新しいクエリ」ボタンをクリックするか、Citrl キーを押下しながら「N」キーを押下します。

    クリックで拡大
  3. 新しいクエリ画面が開きますので以下のSQLを順に実行していきます。

    クリックで拡大
  4. 最初にプリンシパルサーバーで実行します。

    コマンド内の赤字の部分は適宜変更してください。
    変更した部分については以降のコマンドでも同様に置き換えて実行してください。
    
-- プリンシパルサーバー（SQL01）で実行

use [master]
GO

-- 証明書を作成します。
-- SUBJECT は説明なので何でもOKです。
-- EXPIRY_DATE （有効期限）はオプションです。
-- 　　指定しない場合は作成した日から1年後になります。
CREATE CERTIFICATE SQL01_CERT_NEW
  WITH SUBJECT = 'SQL01_CERT certificate for database mirroring'
  ,EXPIRY_DATE = '2020/12/31';

-- エンドポイントを変更します。
ALTER ENDPOINT Endpoint_Mirroring
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5022 , LISTENER_IP = ALL)
  FOR DATABASE_MIRRORING(
     AUTHENTICATION = CERTIFICATE SQL01_CERT_NEW
	,ENCRYPTION = REQUIRED ALGORITHM AES
	,ROLE = ALL
	);

-- 証明書をファイルにバックアップします。
BACKUP CERTIFICATE SQL01_CERT_NEW TO FILE = 'C:\temp\SQL01_CERT_NEW.cer';

-- 既存の証明書を削除します。
DROP CERTIFICATE SQL01_CERT
  5. 次はミラーサーバーで実行します。
    ミラーサーバーで SQL Server Management Studio を起動し、Windows 認証で接続します。

    サーバー名を元にしている部分が「SQL02」になります。
    
-- ミラーサーバー（SQL02）で実行

use [master]
GO

-- 証明書を作成します。
-- SUBJECT は説明なので何でもOKです。
-- EXPIRY_DATE （有効期限）はオプションです。
-- 　　指定しない場合は作成した日から1年後になります。
CREATE CERTIFICATE SQL02_CERT_NEW
  WITH SUBJECT = 'SQL02_CERT certificate for database mirroring'
  ,EXPIRY_DATE = '2020/12/31';

-- エンドポイントを変更します。
ALTER ENDPOINT Endpoint_Mirroring
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5022 , LISTENER_IP = ALL)
  FOR DATABASE_MIRRORING(
     AUTHENTICATION = CERTIFICATE SQL02_CERT_NEW
	,ENCRYPTION = REQUIRED ALGORITHM AES
	,ROLE = ALL
	);

-- 証明書をファイルにバックアップします。
BACKUP CERTIFICATE SQL02_CERT_NEW TO FILE = 'C:\temp\SQL02_CERT_NEW.cer';

-- 既存の証明書を削除します。
DROP CERTIFICATE SQL02_CERT
  6. 最後にミラーリング監視サーバーで実行します。
    ミラーリング監視サーバーで SQL Server Management Studio を起動し、Windows 認証で接続します。

    サーバー名を元にしている部分が「SQL03」になります。
    
-- ミラーリング監視サーバー（SQL03）で実行

use [master]
GO

-- 証明書を作成します。
-- SUBJECT は説明なので何でもOKです。
-- EXPIRY_DATE （有効期限）はオプションです。
-- 　　指定しない場合は作成した日から1年後になります。
CREATE CERTIFICATE SQL03_CERT_NEW
  WITH SUBJECT = 'SQL03_CERT certificate for database mirroring'
  ,EXPIRY_DATE = '2020/12/31';

-- エンドポイントを変更します。
ALTER ENDPOINT Endpoint_Mirroring
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5022 , LISTENER_IP = ALL)
  FOR DATABASE_MIRRORING(
     AUTHENTICATION = CERTIFICATE SQL03_CERT_NEW
	,ENCRYPTION = REQUIRED ALGORITHM AES
	,ROLE = ALL
	);

-- 証明書をファイルにバックアップします。
BACKUP CERTIFICATE SQL03_CERT_NEW TO FILE = 'C:\temp\SQL03_CERT_NEW.cer';

-- 既存の証明書を削除します。
DROP CERTIFICATE SQL03_CERT
  7. プリンシパルでバックアップした「SQL01_CERT_NEW.cer」ファイルをミラーとミラー監視へコピーします。

    同様にミラーでバックアップした「SQL02_CERT_NEW.cer」ファイルをプリンシパルとミラー監視へコピーします。

    さらにミラー監視でバックアップした「SQL03_CERT_NEW.cer」ファイルをプリンシパルとミラーへコピーします。


    ここまでで、3台のサーバーそれぞれに3台分の証明書が揃った状態になります。

    クリックで拡大


他のサーバーの証明書バックアップを使用して証明書を作成する


各サーバーで現在の証明書オブジェクトを一旦削除します。

その後、自サーバー以外の証明書のバックアップを使用して証明書オブジェクトを作成します。

※この手順では証明書を一旦削除してから再度作成しますので既存の証明書と同じ名前で作り直すことができます。

  1. 最初に、プリンシパルサーバーで次のコマンドを実行します。

    
--プリンシパルサーバー（SQL01）で実行

use [master]
GO


--既存の証明書を一旦削除します。
DROP CERTIFICATE SQL02_CERT

--ミラーで作成した証明書バックアップを元に証明書を作成し、元のユーザーへ割り付けます。
CREATE CERTIFICATE SQL02_CERT
  AUTHORIZATION SQL02_User
  FROM FILE = 'C:\temp\SQL02_CERT_NEW.cer'


--既存の証明書を一旦削除します。
DROP CERTIFICATE SQL03_CERT

--ミラーリング監視で作成した証明書バックアップを元に証明書を作成し、元のユーザーへ割り付けます。
CREATE CERTIFICATE SQL03_CERT
  AUTHORIZATION SQL03_User
  FROM FILE = 'C:\temp\SQL03_CERT_NEW.cer'
  2. 次にミラーサーバーで以下のコマンドを実行します。

    
--ミラーサーバーで実行（SQL02）

use [master]
GO


--既存の証明書を一旦削除します。
DROP CERTIFICATE SQL01_CERT

--プリンシパルで作成した証明書バックアップを元に証明書を作成し、元のユーザーへ割り付けます。
CREATE CERTIFICATE SQL01_CERT
  AUTHORIZATION SQL01_User
  FROM FILE = 'C:\temp\SQL01_CERT_NEW.cer'


--既存の証明書を一旦削除します。
DROP CERTIFICATE SQL03_CERT

--ミラーリング監視で作成した証明書バックアップを元に証明書を作成し、元のユーザーへ割り付けます。
CREATE CERTIFICATE SQL03_CERT
  AUTHORIZATION SQL03_User
  FROM FILE = 'C:\temp\SQL03_CERT_NEW.cer'
  3. 最後にミラーリング監視サーバーで以下のコマンドを実行します。

    
--ミラーリング監視サーバーで（SQL03）実行

use [master]
GO


--既存の証明書を一旦削除します。
DROP CERTIFICATE SQL01_CERT

--プリンシパルで作成した証明書バックアップを元に証明書を作成し、元のユーザーへ割り付けます。
CREATE CERTIFICATE SQL01_CERT
  AUTHORIZATION SQL01_User
  FROM FILE = 'C:\temp\SQL01_CERT_NEW.cer'

--既存の証明書を一旦削除します。
DROP CERTIFICATE SQL02_CERT

--ミラーで作成した証明書バックアップを元に証明書を作成し、元のユーザーへ割り付けます。
CREATE CERTIFICATE SQL02_CERT
  AUTHORIZATION SQL02_User
  FROM FILE = 'C:\temp\SQL02_CERT_NEW.cer'

    4. これで証明書は更新できました。

    次の手順でデータベースミラーリングの構成を行います

    ワークグループ環境で データベースミラーリングを構築する ～ 手順 4 ～

【参考情報】

[MSDN] CREATE CERTIFICATE (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms187798.aspx

[MSDN] ALTER CERTIFICATE (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms189511.aspx

[MSDN] ALTER ENDPOINT (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms186332.aspx