IPVでのMicrosoft SQL Serverテンプレートのご利用について

IPVにて提供する以下のMicrosoft SQL Server(以降、SQL Serverと表記)オフィシャルテンプレートを利用する際の留意事項および本テンプレートを利用する際のTipsを記載します。
IPV提供テンプレートのSQL Serverバージョンおよび同梱のSQL Server Management Studio(SSMS)、SQL Server Reporting Services(SSRS)のバージョンは以下となります。

SQL Server提供テンプレート
テンプレート名 Windows Server Version SQL Server Version
SQLServer-2017_Standard_64_English_include-license_WindowsServer-2016_Datacenter_64_include-license_hw17_IPV WindowsServer-2016_Datacenter SQLServer-2017_Standard(En)
SQLServer-2017_Standard_64_Japanese_include-license_WindowsServer-2016_Datacenter_64_include-license_hw17_IPV WindowsServer-2016_Datacenter SQLServer-2017_Standard(Ja)
SQLServer-2017_Standard_64_English_include-license_WindowsServer-2019_Datacenter_64_include-license_hw17_IPV WindowsServer-2019_Datacenter SQLServer-2017_Standard(En)
SQLServer-2017_Standard_64_Japanese_include-license_WindowsServer-2019_Datacenter_64_include-license_hw17_IPV WindowsServer-2019_Datacenter SQLServer-2017_Standard(Ja)
SQLServer-2019_Standard_64_English_include-license_WindowsServer-2019_Datacenter_64_include-license_hw17_IPV WindowsServer-2019_Datacenter SQLServer-2019_Standard(En)
SQLServer-2019_Standard_64_Japanese_include-license_WindowsServer-2019_Datacenter_64_include-license_hw17_IPV WindowsServer-2019_Datacenter SQLServer-2019_Standard(Ja)

同梱されているSSMS、SSRSのバージョンは以下となります。

SQL Server Management Studioバージョン
SQL Server Management Studio(SSMS)バージョン 18.11.1

注釈

Azure Data Studioは同梱されておりません。


SQL Server Reporting Services(SSRS)バージョン
SQL Server Reporting Services(SSRS) 15.0.1102.932

注釈

SQL ServerテンプレートではWindows Defenderがプリインストールされており、リアルタイム検索などの機能も有効な状態となっています。
SQL ServerはWindows Defenderの自動除外の対象とはされていません。
以下を参考に必要な除外設定をおこなって下さい。

SQL Serverを実行しているコンピューターで実行するウイルス対策ソフトウェアを選択する

目次


対象読者

本チュートリアルは、以下の方々を対象にしています。
  • SQL Server DBのシステム設計・構築者およびシステム管理者
なお、本チュートリアルは以下の知識があることを前提に説明しています。
  • SQLの基本的な知識
  • T-SQLプログラミングの基本的な知識

1. デプロイ後の初期設定

IPV提供のSQL Serverテンプレートではサーバーの管理ツールとしてSQL Server Management Studio (以降、SSMS)が同梱されています。
初期設定およびサーバー管理には本ツールをご利用下さい。

初回デプロイ時ではSQL Serverの認証モードは「混合モード(Windows認証とSQL Server認証)」となっています。
そのため、システム構築の前にセキュリティ向上のために"SQL Server管理者アカウント(saアカウント)"のパスワード変更もしくは無効化を行って下さい。

注釈

Windows認証用にActive Directoryを構築することを推奨します。
SQL Serverのローカルユーザーをご利用される場合、クライアント接続用のユーザーをSQL Server内に作成していただく必要がございます。

本項ではSQL Server認証の無効化、SQL Server管理者アカウント(saアカウント)のパスワード変更方法について記載します。

1.1. SQL Server管理者アカウント(saアカウント)パスワード変更方法

SQL Server認証を無効化せずに混合モードでご利用される場合はsaアカウントのパスワード変更を実施して下さい。
パスワード変更方法はSSMS起動後にGUIによる変更、Transact-SQL(以降T-SQL)による変更の2種類があります。

SSMSを使用したsaアカウントのパスワード変更方法

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントを使用してログインを行います。

[SQL Server Management Studio]-[サーバー接続]ダイアログ
項目 入力内容
サーバータイプ データベースエンジン
サーバー名 localhost
認証モード SQLサーバー認証
ログイン sa
パスワード saパスワード(初回：sqlserver)

../_images/guide_ssms-login_002.png

  • SSMSオブジェクトエクスプローラーにて[セキュリティ]->[ログイン]より、"sa"を選択し右クリックします。
    メニューより[プロパティ]を選択します。
  • saアカウントの[ログインプロパティ]ダイアログ内、[全般]の"パスワード"欄に新規パスワードを入力します。
    入力完了後、[OK]をクリックします。
  • SSMSオブジェクトエクスプローラーで対象サーバーを右クリックしメニューより[再起動]をクリックします。

T-SQLを使用したsaアカウントのパスワード変更方法

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントを使用してログインを行います。

[SQL Server Management Studio]-[サーバー接続]ダイアログ
項目 入力内容
サーバータイプ データベースエンジン
サーバー名 localhost
認証モード SQLサーバー認証
ログイン sa
パスワード saパスワード(初回：sqlserver)

../_images/guide_ssms-login_002.png

  • SSMSオブジェクトエクスプローラー上部メニューより[新しいクエリ]を選択します。

  • クエリウィンドウ内に以下のT-SQLを入力しコマンドを実行します。

### 構文
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'新しいパスワード' MUST_CHANGE
GO

  • 正常にクエリ処理が完了した後にsaアカウントのパスワードが設定されます。

1.2. SQL Server認証の無効化方法

SQL Server認証の無効化方法を記載します。
SQL Server認証の無効化方法はSSMS起動後にGUIによる無効化、Transact-SQL(以降T-SQL)による無効化の2種類があります。
本項ではSSMSを利用したSQL Server認証の無効化を記載します。

注釈

T-SQLによる認証モード変更はサーバーレジストリの書き換え処理となります。
レジストリの変更の方法を誤った場合、深刻な問題が発生することがあります。
これらの問題が起きた場合、オペレーティング システムを再インストールしなければならない場合があります。
そのため、本項ではT-SQLでの認証モード変更については記載を行いません。

無効化手順を記載します。

SSMSを使用したSQL Server認証の無効化方法


注釈

事前にSSMSにて"Windows管理者カウント(Administrator)"の登録を行ってください。
また、デプロイ後ユーザーには「SQLxxxxWINxxxxAdministrator」が存在していますが、こちらはご利用ができません。
不要な場合、削除を行っても問題がございません。

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントを使用してログインを行います。

[SQL Server Management Studio]-[サーバー接続]ダイアログ
項目 入力内容
サーバータイプ データベースエンジン
サーバー名 localhost
認証モード SQLサーバー認証
ログイン sa
パスワード saパスワード(初回：sqlserver)

../_images/guide_ssms-login_002.png

  • SSMSオブジェクトエクスプローラーにて対象サーバーを選択して右クリックを行います。
    メニューより[プロパティ] をクリックします。
  • [セキュリティ]ページへ遷移し[サーバー認証]欄より、"Windows認証モード"を選択し[OK]をクリックします。
  • SSMSオブジェクトエクスプローラーで対象サーバーを右クリックしメニューより[再起動]をクリックします。
    SQL Serverエージェントが実行されている場合、SQL Serverエージェントも再起動する必要があります。
以降、saアカウントは無効化されます。
次回のSSMSログインよりWindows認証のみとなります。

2. SQL ServerテンプレートTips

本項ではSQL Serverテンプレートをご利用いただく際のTipsを記載します。
記載内容をお役立てください。

2.1. ユーザーデータベース照合順序について

SQL Serverは、インスタンスおよびデータベースレベル、列レベルで照合順序をサポートしています。
サーバーレベル照合順序はインスタンスを作成するときに設定する項目となります。
IPV提供のSQL Serverテンプレートのサーバーレベル照合はサーバーの言語に依存されます。

提供テンプレート:サーバーレベル照合順序
テンプレート名 サーバーレベル照合順序
SQLServer-2017_Standard_64_English_include-license_WindowsServer-2016_Datacenter_64_include-license_hw17_IPV SQL_Latin1_General_CP1_CI_AS
SQLServer-2017_Standard_64_Japanese_include-license_WindowsServer-2016_Datacenter_64_include-license_hw17_IPV SQL_Latin1_General_CP1_CI_AS
SQLServer-2017_Standard_64_English_include-license_WindowsServer-2019_Datacenter_64_include-license_hw17_IPV SQL_Latin1_General_CP1_CI_AS
SQLServer-2017_Standard_64_Japanese_include-license_WindowsServer-2019_Datacenter_64_include-license_hw17_IPV SQL_Latin1_General_CP1_CI_AS
SQLServer-2019_Standard_64_English_include-license_WindowsServer-2019_Datacenter_64_include-license_hw17_IPV SQL_Latin1_General_CP1_CI_AS
SQLServer-2019_Standard_64_Japanese_include-license_WindowsServer-2019_Datacenter_64_include-license_hw17_IPV SQL_Latin1_General_CP1_CI_AS

データベースレベルの照合順序は、新しいデータベースまたはデータベースオブジェクトを作成する際に照合順序を設定(上書き)することにより、データベース、テーブル、または列レベルで変更できます。
例えば、デフォルトのサーバーレベル照合が"Japanese_CI_AS"の場合、データベース作成時に"SQL_Latin1_General_CP1_CI_AS "に変更することができます。

本項ではデータベースレベル照合順序の設定方法を記載します。
設定方法はSSMSにて実施する方法、T-SQLにて設定する方法があります。

注釈

データベースの照合順序を変更すると、次の変更が行われます。
1.システムテーブル内のchar型、varchar型、text型、nchar型、nvarchar型、またはntext型の列はすべて新しい照合順序に変更されます。
2.ストアドプロシージャおよびユーザー定義関数で使用されているchar型、varchar型、text型、nchar型、nvarchar型、またはntext型の既存のパラメーターおよびスカラー値の戻り値はすべて新しい照合順序に変更されます。
3.char型、varchar型、text型、nchar型、nvarchar型、またはntext型のシステムデータ型およびこれらを基にしたユーザー定義データ型はすべて新しい既定の照合順序に変更されます。

SSMSを使用したデータベースレベル照合順序の設定方法

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントを使用してログインを行います。

[SQL Server Management Studio]-[サーバー接続]ダイアログ
項目 入力内容
サーバータイプ データベースエンジン
サーバー名 localhost
認証モード SQLサーバー認証
ログイン sa
パスワード saパスワード(初回：sqlserver)
  • SSMSオブジェクトエクスプローラーにて[データベース]より対象データベースを選択し右クリックします。
    メニューより[オプション]を選択します。

注釈

新しいデータベースを作成する場合は[データベース]にて右クリックメニューより[新しいデータベース]を選択します。
  • [オプション]ページ上部の"照合順序"を変更します。
  • 変更後、[OK]をクリックします。

  • クエリウィンドウ内に以下のT-SQLを入力しコマンドを実行します。
    クエリ結果にて指定した照合順序になっていることを確認します。

### 構文
SELECT name, collation_name
FROM sys.databases
WHERE name = N'対象データベース名';
GO


T-SQLを使用したデータベースレベル照合順序の変更方法

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントを使用してログインを行います。

[SQL Server Management Studio]-[サーバー接続]ダイアログ
項目 入力内容
サーバータイプ データベースエンジン
サーバー名 localhost
認証モード SQLサーバー認証
ログイン sa
パスワード saパスワード(初回：sqlserver)
  • SSMSオブジェクトエクスプローラー上部メニューより[新しいクエリ]を選択します。

  • クエリウィンドウ内に以下のT-SQLを入力しコマンドを実行します。

### 構文
USE master;
GO

ALTER DATABASE 対象データベース名
    COLLATE 変更後照合順序;
GO

  • 正常にクエリ処理が完了した後に以下クエリにて指定した照合順序になっていることを確認します。

### 構文
SELECT name, collation_name
FROM sys.databases
WHERE name = N'対象データベース名';
GO


2.2. SQL Serverクライアント接続方法

SQL Serverクライアントがデータベースへ接続を行う際に既定の接続情報ではなくエイリアス(以降、別名)を用いた接続を行いたいケースがあります。
ここでは別名での接続を行うための設定を記載します。

別名接続を利用する場合

  • [サーバーマネージャー]->[ツール]->[コンピュータの管理]->[SQL Server構成マネージャー]を起動します。

  • [SQLserver構成マネージャー]より[SQL Server Native Clientの構成]を展開します。
    展開後、[別名]ボックスにクライアントが接続するための別名を入力します。
  • 右側ペインに各種接続プロトコルの一覧が表示されます。
    利用したいプロトコルを右クリックし有効、無効および接続設定を行います。
  • [サーバー]ボックスに、サーバーの名前またはIPアドレスを入力します。
  • [プロトコル]ボックスにて、この別名が使用するプロトコルを選択します。
    プロトコルを選択すると、オプションプロパティボックスのタイトルが[ポート番号]、[パイプ名]、または[接続文字列]に変わります。
    接続プロトコルについては後述参照

SQL Serverではデータベースへの接続プロトコルとして「TCP/IP」、「名前付きパイプ(Named Pipes)」、「共有メモリ(Shared Memory)」のいずれかのプロトコルが使用できます。
各接続プロトコルの概要を以下に示します。

接続プロトコル一覧概要
接続プロトコル 概要
TCP/IP 接続クライアントはIPアドレスとポート番号を指定して接続を行います
名前付きパイプ(Named Pipes)
サーバー名、サーバ内で定義したパイプ名を元にアプリケーションが接続する方式
例：\サーバ名パイプ名
共有メモリ(Shared Memory) サーバー内のローカル接続で利用される通信方式

注釈

SQL Serverの既定のインスタンスは、TCP/IPポート1433および名前付きパイプ"\.pipesqlquery."にて構成されています。
接続プロトコルを使用することでユーザーデータベースへの接続に標準以外のポート番号、別名パイプで接続する事が可能となります。
本項ではユーザーデータベースへの接続方式を設定する方法を記載します。

  • [サーバーマネージャー]->[ツール]->[コンピュータの管理]->[SQL Server構成マネージャー]を起動します。

  • [SQLserver構成マネージャー]より[SQL Serverネットワークの構成]を展開します。
    展開後、[インスタンス名のプロトコル]をクリックします。
  • 右側ペインに各種接続プロトコルの一覧が表示されます。
    利用したいプロトコルを右クリックし有効、無効および接続設定を行います。

TCP/IP接続を利用して標準ポートから異なるポートへ変更する場合

  • 接続プロトコルの一覧より[TCP/IP]を選択し右クリックします。
    メニューより[プロパティ]を選択します。
  • [IPアドレス]タブ内"IPALL"の「TCP動的ポート」に任意のポート番号を入力し[適用(A)]をクリックします。

SQL Serverサービスの再起動後、変更設定がサービスに適用されます。

名前付きパイプを利用する場合

  • 接続プロトコルの一覧より[名前付きパイプ]を選択し右クリックします。
    メニューより[プロパティ]を選択します。
  • [プロトコル]タブにて無効状態から有効へ状態を変更します。
  • [パイプ名]タブにてSQL Serverがリッスンする名前付きパイプ名(\.pipe"任意のパイプ名")を指定し[適用(A)]をクリックします。

SQL Serverサービスの再起動後、変更設定がサービスに適用されます。

2.3. 可用性構成の実現方法

2.3.1. データベースミラーリング

データベースミラーリングは、データベースの可用性を向上させるための機能となります。
ミラーリング処理はデータを保存する際に同じデータを同時に複数のストレージに書き込む機能となります。
本機能を活用することにより、ミラーリングをデータベースごとに実装することが可能です。

注釈

完全復旧モデルを使用するデータベースでのみ機能します。

データベースミラーリング構成は以下のメリットがあります。

- 1つの仮想マシンディスクに問題が発生しても他の仮想マシンディスク上に同じデータが残っているためデータ損失を防ぐことが可能
- 自動フェイルオーバーに対応させる場合にはミラーリング監視サーバを導入することで信頼性を向上することが可能

データベースミラーリング構成のデメリットは以下となります。

- 削除データの復旧措置が困難

警告

誤ってデータを削除してしまった場合、ミラーリングの特性上データ復旧が行えません。
そのため、定期的なデータバックアップの取得をお願いします。
SQL Serverデータバックアップに関しては後述を参照下さい。

データベースミラーリング自動フェイルオーバーに関して、プリンシパルサーバーとミラーリング監視サーバとの接続性が失われていなければ自動フェイルオーバーは行われません。
その間プリンシパルサーバが引き続きデータベースとして機能します。
※ミラーサーバから再接続された際にプリンシパルサーバにて蓄積されたログレコードを送信します。

注釈

ミラーリング監視サーバは複数セッションにも対応可能です。
また、自動フェイルオーバーが不要な場合はミラーリング監視サーバは不要となります。

2.3.1.1. 前提条件/制約事項

  • プリンシパルサーバとミラーサーバおよびミラーリング監視サーバのSQL Serverバージョンが同一であること
  • プリンシパルサーバにミラーリング対象データベースが完全復旧モデルにて作成されていること
  • ミラーサーバのディスク容量がミラーデータベースを保持するのに十分なサイズであること
  • 関連サーバ間にてリモート接続設定が完了しており、ホスト名解決も行えること
  • SQL Serverインスタンス接続アカウントは同一であること
  • ファイアウォール設定にて下表ポートがあいていること

ファイアウォール設定
No ポート番号
1 1433
2 1434
3 5022

制約事項は以下の通りとなります。

  • ミラー化できるのはユーザー データベースのみ
  • データベースミラーリングセッション中に、ミラーデータベースの名前を変更できません
  • FILESTREAMはサポートしていません
  • 複数のデータベースにまたがるトランザクションまたは分散トランザクションはサポートされません

注釈

ミラーリング通信にて暗号化を行う必要がある場合には各サーバーにてSSL証明書を作成して下さい。
SSL証明書作成後、以下のT-SQLを使用しミラーリングエンドポイントの作成およびミラーリング用証明書の鍵作成等を行います。

■ 暗号化通信にて用いるT-SQL
- CREATE MASTER KEY
- CREATE CERTIFICATE
- CREATE ENDPOINT

※ 本資料では暗号化は使用しません。

注釈

データベースミラーリング構成の設定に関しては Microsoft SQL Server データベースミラーリング をご参照下さい。


2.3.2. データベースミラーリング設定(ミラーリング監視サーバなし)

本手順ではワークグループ環境にて構築する手順を記載します。

注釈

テンプレートデプロイに関しては手順には記載しません。
本手順ではファイアウォール設定より記載します。

2.3.2.1 ファイアウォール/SQL Serverインスタンスログオンアカウント設定 [対象：プリンシパルサーバ、ミラーサーバ]


  • [サーバーマネージャー]->[ツール]->[セキュリティが強化された Windows Defender ファイアウォール]を起動します。

  • 左メニューペインより[受信の規則]を選択し、右ペイン内[新しい規則]をクリック。
    [新規の受信の規則ウィザード]で、以下設定にて規則を作成。

ファイアウォール設定:新規の受信の規則ウィザード
項目 設定
規則の種類 ポート
プロトコルおよびポート
プロトコル：TCP
ポート：1433,1434,5022
操作 接続を許可する
プロファイル プライベート
名前 {適切な規則名を付与}

以上でファイアウォール設定は完了となります。
次にSQL Serverインスタンスログオンアカウントの設定を行います。

注釈

本手順はワークグループ環境での構築のみ必要となります。

  • [サーバーマネージャー]->[ツール]->[コンピュータの管理]->[SQL Server構成マネージャー]を起動します。

  • [SQLserver構成マネージャー]より[SQL Serverのサービス]を展開します。
    展開後、[SQL Server(MSSQLSERVER)]を選択し右クリックします。

  • メニューより[プロパティ]を展開します。
    [ログオン]内、"このアカウント"が「NT Service¥MSSQL~」となっていることを確認します。

  • [ログオン]内、"このアカウント"をSQL Serverミラーリング用ローカルアカウント(例：Administrator)に変更し[適用]をクリックします。

以上でミラーリング構成手順を行うための事前準備は全て完了となります。

2.3.2.2 ミラーリング用ユーザーデータベース作成 [対象：プリンシパルサーバ]

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントを使用してログインを行います。

  • [オブジェクトエクスプローラー]->[データベース]を右クリックします。
    メニューより[新しいデータベース]を選択します。
    [新しいデータベース]ウィザードが起動しますので適切な設定を行い[OK]をクリックします。

  • [オブジェクトエクスプローラー]->[データベース]->[新規作成データベース]を右クリックします。
    メニューより[プロパティ]を選択します。
    [プロパティ]->[オプション]へ移動し"復旧モデル：完全"となっていることを確認します。
    なっていない場合は「完全」に変更します。

2.3.2.3 ユーザーデータベースバックアップ実施 [対象：プリンシパルサーバ]


注釈

saアカウントを使用して作業を行います。
  • [オブジェクトエクスプローラー]->[データベース]->[新規作成データベース]を右クリックします。
    メニューより[タスク]->[バックアップ]を選択します。

  • [データベースのバックアップ]を右クリックします。
    全般内、"復旧モデル"、"バックアップの種類"が「完全」となっていることを確認します。

  • [データベースのバックアップ]内"バックアップ先"にて[追加]をクリックします。
    保存先を指定し[OK]をクリックします。
    "バックアップ先"項目に指定したバックアップパスが表示されることを確認し[OK]をクリックします。
    バックアップ成功ダイアログが表示されますので[OK]をクリックしダイアログを閉じます。

  • 再度[タスク]->[バックアップ]-> [データベースのバックアップ]を起動します。
    全般内、"バックアップの種類"を「トランザクションログ」へ変更し[OK]をクリックします。
    バックアップ成功ダイアログが表示されますので[OK]をクリックしダイアログを閉じます。

注釈

トランザクションログは事前に取得したデータベースバックアップに追記でバックアップを行います。
そのため、バックアップ先の変更はしていません。

  • バックアップファイルをミラーサーバへ転送します。

注釈

RDP等でミラーサーバー側へバックアップファイルを転送して下さい。


2.3.2.4 ミラーサーバにてバックアップ復元 [対象：ミラーサーバ]

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントを使用してログインを行います。

  • [オブジェクトエクスプローラー]->[データベース]を右クリックします。
    メニューより[データベースの復元]を選択します。

  • [データベースの復元]->[全般]内"ソース"にて"デバイス"を選択します。
    "デバイス"欄右端の「…」をクリックします。

  • [バックアップデバイスの選択]ダイアログが起動します。
    "バックアップメディアの種類"が「ファイル」となっていることを確認し[追加]をクリックします。
    [バックアップの選択]ダイアログにてプリンシパルサーバよりミラーサーバへ転送したバックアップファイルを選択します。

  • 復元対象バックアップの解析が行われ「完全」バックアップと「トランザクション ログ」バックアップが展開されることを確認します。

  • 左ペイン内[オプション]を選択します。
    "復旧状態"項目にて『 RESTORE WITH NORECOVERY 』を選択します。
    [OK]をクリックしデータベースの復元を行います。

  • 復元完了ダイアログが表示されることを確認し[OK]をクリックします。

  • [オブジェクトエクスプローラー]->[データベース]内に復元データベースが表示されることを確認します。
    この際、"データベース名(復元しています...)"と表示されることを確認します。
以上でミラーリング準備は完了となります。

2.3.2.5 データベースミラーリング構成設定 [対象：プリンシパルサーバ]

注釈

事前にファイアウォール構成、hostsファイル等を用いた疎通性の確立を実施しておく必要があります。
※saアカウントを使用して作業を行います。

  • [オブジェクトエクスプローラー]->[データベース]->[新規作成データベース]を右クリックします。
    メニューより[タスク]->[ミラー]を選択します。

  • [データベースのプロパティ]->[ミラーリング]が起動します。
    ミラーリングページ内、"セキュリティの構成"をクリックします。

  • [データベースミラーリング セキュリティ構成ウィザード]が起動します。
    [次へ]をクリックします。
  • [ミラーリング監視サーバを含める]ページにて"いいえ"にチェックを入れ[次へ]をクリックします。
    "ミラーリング監視サーバー"を含めた構成を行う場合には 2.3.3. データベースミラーリング設定(ミラーリング監視サーバあり) を参照下さい。
  • [プリンシパル サーバ インスタンス]ページに表示されている内容を確認します。
    "ミラーサーバーインスタンスおよびミラーリング監視サーバーインスタンスからの接続を受け入れるときにプリンシパルサーバーインスタンスが使用する、エンドポイントのプロパティを指定して下さい。"欄「エンドポイントから送られるデータを暗号化する」のチェックが外れていることを確認し[次へ]をクリックします。

注釈

本手順ではデータベースミラーリング通信の暗号化を行わない設定を記載しています。

  • [ミラー サーバ インスタンス]ページ内[接続]をクリックします。
    [サーバへの接続]にて"サーバ名"に対向となるミラーサーバを指定し、接続認証を指定し[接続]をクリックします。
    "ミラーサーバーインスタンスおよびミラーリング監視サーバーインスタンスからの接続を受け入れるときにプリンシパルサーバーインスタンスが使用する、エンドポイントのプロパティを指定して下さい。"欄「エンドポイントから送られるデータを暗号化する」のチェックを外します。
    接続完了後、ミラーサーバに関する情報が設定されることを確認し[次へ]をクリックします。
    ※ リスナーポート：5022等表示されることを確認して下さい。
  • [サービスアカウント]ページではワークグループ環境構成の設定は不要なため[次へ]をクリックします。
  • [ウィザードの完了]ページで設定内容を確認し[完了]をクリックします。
    エンドポイント構成が実施されます。
    "成功"となることを確認し[閉じる]をクリックします。
  • ウィザードを閉じた後に[ミラーリング]ページで設定内容を確認し[完了]をクリックします。
    エンドポイント構成が実施されます。
    エンドポイント構成が"成功"となることを確認し[閉じる]をクリックします。
  • [データベースのプロパティ]ダイアログにてデータベースミラーリングの構成内容が表示されます。
    構成内容を確認しすぐにミラーリングを確認する場合には[ミラーリングの開始]をクリックします。
  • ワークグループ環境の場合、コンピューター名が完全修飾ドメイン名ではない旨の確認メッセージが表示されます。
    こちらのメッセージについては問題がないため[はい]をクリックします。
  • [データベースのプロパティ]->[ミラーリング]に戻ります。
    [ミラーリング]内下部の"状態"に『同期完了：データベースは完全に同期されています』と表示されていることを確認します。

注釈

エラー：1418が発生した場合、ミラーサーバの名前解決が行えることを確認下さい。
また、ミラーリング構成にて暗号化通信を設定されている場合はエンドポイントおよび証明書情報が適切かを確認下さい。

エラー：927が発生した場合、ミラーリングエンドポイントへのアクセス権限が、SQL Serverサービス起動アカウントに付与されているかを確認下さい。

問題の切り分けとして、以下を参考に、T-SQLからミラーリング構築を試行して下さい。
T-SQLでのミラーリング構築が正常にできない場合、ミラーリングエンドポイントへのアクセス権限設定およびプリンシパル-ミラー間の通信でミラーリング使用ポートが空いているかを確認して下さい。
ミラーリングのためのミラー データベースの準備 (SQL Server)
データベース ミラーリングの構成


以上でデータベースミラーリングの構成は完了となります。

2.3.3. データベースミラーリング設定(ミラーリング監視サーバあり)

注釈

※saアカウントを使用して作業を行います。

  • [ミラーリング監視サーバを含める]ページにて"はい"にチェックをいれ[次へ]をクリックします。
  • [構成するサーバーを選択する]ページにて"ミラーリング監視サーバ"にチェックをいれ[次へ]をクリックします。
  • [プリンシパル サーバ インスタンス]ページに表示されている内容を確認します。
    "ミラーサーバーインスタンスおよびミラーリング監視サーバーインスタンスからの接続を受け入れるときにプリンシパルサーバーインスタンスが使用する、エンドポイントのプロパティを指定して下さい。"欄「エンドポイントから送られるデータを暗号化する」のチェックが外れていることを確認し[次へ]をクリックします。

注釈

本手順ではデータベースミラーリング通信の暗号化を行わない設定を記載しています。

  • [ミラー サーバ インスタンス]ページ内[接続]をクリックします。
    [サーバへの接続]にて"サーバ名"に対向となるミラーサーバを指定し、接続認証を指定し[接続]をクリックします。
    "ミラーサーバーインスタンスおよびミラーリング監視サーバーインスタンスからの接続を受け入れるときにプリンシパルサーバーインスタンスが使用する、エンドポイントのプロパティを指定して下さい。"欄「エンドポイントから送られるデータを暗号化する」のチェックを外します。
    接続完了後、ミラーサーバに関する情報が設定されることを確認し[次へ]をクリックします。
    ※ リスナーポート：5022等表示されることを確認して下さい。
  • [ミラーリング 監視サーバ インスタンス]ページ内[接続]をクリックします。
    [サーバへの接続]にて"サーバ名"に対向となるミラーリング監視サーバを指定し、接続認証を指定し[接続]をクリックします。
    "ミラーサーバーインスタンスおよびミラーリング監視サーバーインスタンスからの接続を受け入れるときにプリンシパルサーバーインスタンスが使用する、エンドポイントのプロパティを指定して下さい。"欄「エンドポイントから送られるデータを暗号化する」のチェックを外します。
    接続完了後、ミラーサーバに関する情報が設定されることを確認し[次へ]をクリックします。
    ※ リスナーポート：5022等表示されることを確認して下さい。
  • [サービスアカウント]ページではワークグループ環境構成の設定は不要なため[次へ]をクリックします。
  • [ウィザードの完了]ページで設定内容を確認し[完了]をクリックします。
    エンドポイント構成が実施されます。
    "成功"となることを確認し[閉じる]をクリックします。
  • ウィザードを閉じた後に[ミラーリング]ページで設定内容を確認し[完了]をクリックします。
    エンドポイント構成が実施されます。
    エンドポイント構成が"成功"となることを確認し[閉じる]をクリックします。
  • [データベースのプロパティ]ダイアログにてデータベースミラーリングの構成内容が表示されます。
    構成内容を確認しすぐにミラーリングを確認する場合には[ミラーリングの開始]をクリックします。
  • ワークグループ環境の場合、コンピューター名が完全修飾ドメイン名ではない旨の確認メッセージが表示されます。
    こちらのメッセージについては問題がないため[はい]をクリックします。
  • [データベースのプロパティ]->[ミラーリング]に戻ります。
    [ミラーリング]内下部の"状態"に『同期完了：データベースは完全に同期されています』と表示されていることを確認します。

注釈

エラー：1418が発生した場合、ミラーサーバの名前解決が行えることを確認下さい。
また、ミラーリング構成にて暗号化通信を設定されている場合はエンドポイントおよび証明書情報が適切かを確認下さい。

エラー：927が発生した場合、ミラーリングエンドポイントへのアクセス権限が、SQL Serverサービス起動アカウントに付与されているかを確認下さい。

問題の切り分けとして、以下を参考に、T-SQLからミラーリング構築を試行して下さい。
T-SQLでのミラーリング構築が正常にできない場合、ミラーリングエンドポイントへのアクセス権限設定およびプリンシパル-ミラー間の通信でミラーリング使用ポートが空いているかを確認して下さい。
ミラーリングのためのミラー データベースの準備 (SQL Server)
データベース ミラーリングの構成


以上でデータベースミラーリングの構成は完了となります。

2.4. データベースのバックアップ方法

2.4.1. データベースバックアップ

データベースミラーリングは、データベースの可用性を向上させるための機能となります。
ミラーリングはデータベースごとに実装することが可能です。
本項ではSSMS、Transact-SQL、または PowerShell を用いたSQL Serverデータベースの完全バックアップを作成する方法について記載します。

2.4.1.1 SSMSを使用したSQL Serverデータベースの完全バックアップ方法

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントもしくはActive Directoryユーザーを使用してログインを行います。

  • [オブジェクトエクスプローラー]ツリーより[データベース]を展開します。
    バックアップ取得対象の"データベース名"を選択し右クリックメニューより[タスク]->[バックアップ]を選択します。

  • [データベースのバックアップ]ダイアログボックスが表示されます。
    [データベースのバックアップ]ダイアログボックスの設定項目は以下となります。

データベースのバックアップ設定項目
項目 設定
データベース データベース名が表示
復旧モデル 完全 ※復旧時の項目
バックアップの種類 完全 ※ドロップダウンリストよりバックアップの種類を選択可能
バックアップ コンポーネント データベース
バックアップ先 バックアップ ファイルの保存先を指定 ※規定保存先：../mssql/data フォルダー内

注釈

ダイアログボックス上部の[スクリプト]よりT-SQLスクリプトが生成されます。

  • "バックアップの種類"にて「完全(※既定値)」を選択します。

  • "バックアップ コンポーネント"にて「データベース」を選択します。

  • "バックアップ先"にてバックアップファイルの格納場所を指定します。
    下部の[追加]をクリックすることでファイル格納場所を指定できます。

注釈

[メディアオプション]、[バックアップオプション]を設定することでより詳細なバックアップ設定を行うことが可能です。

  • ダイアログ下部の[OK]をクリックしバックアップを実行します。
    完全バックアップ作成後は差分バックアップ、トランザクションログバックアップを行うことが可能となります。

2.4.1.2 T-SQLを使用したSQL Serverデータベースの完全バックアップ方法

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントもしくはActive Directoryユーザーを使用してログインを行います。

  • SSMSオブジェクトエクスプローラー上部メニューより[新しいクエリ]を選択します。

  • クエリウィンドウ内に以下のT-SQLを入力しコマンドを実行します。
    以下の例では[master]データベースをバックアップする方法を記載します。

### 構文
BACKUP DATABASE データベース名
TO {DISK | TAPE } = 'バックアップ先' [ WITH オプション ]


### 構文例
USE [master]
GO

BACKUP DATABASE [master]
TO DISK = 'c:\tmp\master.bak'
   WITH NOFORMAT,
      NOINIT,
      NAME = 'N'master-Full Database Backup',
      SKIP,
      NOREWIND,
      NOUNLOAD,
      STATS = 10
GO

2.4.1.3 Powershellを使用したSQL Serverデータベースの完全バックアップ方法

Powershellを用いたバックアップでは「Backup-SqlDatabase」コマンドレットを使用します。
SSMS内のPowershellウィンドウから実施することが可能です。
※SSMS-Powershellウィンドウより実行する場合は資格情報の指定を省略することができます。

  • [スタート]->[すべてのプログラム]->[Windows PowerShell]を起動します。

  • 「Backup-SqlDatabase」コマンドレットを使用して対象データベースのバックアップを行います。
    以下の例では"sa"アカウントを使用してインスタンスに接続し「master」データベースのバックアップを取得します。
    このコマンド実行後にログイン認証を完了するためのパスワード入力が求められます。

### 構文例
Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "master" -Credential (Get-Credential "sa")

2.4.2. データベースリストア

データベースミラーリングは、データベースの可用性を向上させるための機能となります。
ミラーリングはデータベースごとに実装することが可能です。
本項ではSSMS、Transact-SQLを用いたSQL Serverデータベースのリストアを実施する方法について記載します。

2.4.2.1. SSMSを使用したSQL Serverデータベースのリストア方法

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントもしくはActive Directoryユーザーを使用してログインを行います。

  • [オブジェクトエクスプローラー]ツリーより[データベース]を右クリックします。
    メニューより[データベースの復元]を選択します。

  • [データベースの復元]ダイアログボックスが表示されます。
    [データベースの復元]ダイアログボックスの設定項目は以下となります。

データベースの復元設定項目
項目 設定
ソース
・データベース
・デバイス
転送先
・データベース
・復元先 ※オプションにてタイムラインより復元が可能
復元オプション
・既存のデータベースを上書きする(WITH REPLACE)
・レプリケーションの設定を保存する(WITH KEEP_REPLICATION)
・復元するデータベースへのアクセスを制限する(WITH RESTRICTED_USER)
復旧状態 RESTORE WITH RECOVERY
ログ末尾のバックアップ 復元の前にログ末尾のバックアップを実行する
サーバー接続 接続先データベースへの既存の接続を閉じる ※チェックすることでデータベースセッションをクローズ
プロンプト 各バックアップを復元する前に確認する ※チェックすることでリストア時に確認プロンプトが実行

注釈

ダイアログボックス上部の[スクリプト]よりT-SQLスクリプトが生成されます。

  • "復元するバックアップセットの選択"にて完全バックアップが選択されているのを確認します。
    確認後、[オプション]へ遷移します。

  • "復元オプション"にて以下のチェックを外します。
    - 既存のデータベースを上書きする

  • "ログ末尾のバックアップ"にて以下のチェックを外します。
    - 復元の前にログ末尾のバックアップを実行する

注釈

オンライン状態のデータベースに対してリストア処理を行う場合には"ログ末尾のバックアップ"にチェックを入れて下さい。
※データベースがオフラインで起動できずにデータベースを復元する必要がある場合にも有用です。
　このとき、トランザクションは発生しないので、[WITH NORECOVERY]の指定が省略できます。
  • ダイアログ下部の[OK]をクリックしリストアを実行します。

2.4.2.2 T-SQLを使用したSQL Serverデータベースの完全リストア方法

  • [スタート]->[すべてのプログラム]->[Microsoft SQL Server Tools XX]->[Microsoft SQL Server Management Studio XX]を起動します。

  • [サーバー接続]ダイアログにてsaアカウントもしくはActive Directoryユーザーを使用してログインを行います。

  • SSMSオブジェクトエクスプローラー上部メニューより[新しいクエリ]を選択します。

  • クエリウィンドウ内に以下のT-SQLを入力しコマンドを実行します。
    以下の例では[master]データベースをリストアする方法を記載します。

### 構文
RESTORE DATABASE データベース名
FROM {DISK | TAPE } = 'バックアップ元' [ WITH オプション ]


### 構文例
USE [master]
GO

RESTORE DATABASE [master]
FROM DISK = 'c:\tmp\master.bak'
   WITH FILE =1,
   MOVE N'master' TO N'c:\tmp\master.mdf',
   MOVE N'mastlog' TO N'c:\tmp\master.ldf',
   NOUNLOAD,
   STATS = 10
GO

IPV Windows Serverテンプレートにおける日本語表記への変更について
ストレージパフォーマンスの計測方法