可用性グループの作成 (Transact-SQL)

[アーティクル]
06/25/2013

このトピックでは、AlwaysOn 可用性グループ機能を有効にする SQL Server 2012 のインスタンス上で可用性グループを作成および構成するために Transact-SQL を使用する方法について説明します。可用性グループは、1 つのまとまりとしてフェールオーバーする一連のユーザーデータベースと、フェールオーバーをサポートする一連のフェールオーバーパートナー (可用性レプリカ) を定義します。

注
可用性グループの概要については、「AlwaysOn 可用性グループの概要 (SQL Server)」を参照してください。

作業を開始する準備:

前提条件

セキュリティ

作業の概要および対応する Transact-SQL ステートメント
可用性グループを作成および構成する方法: Transact-SQL
例: Windows 認証を使用した可用性グループの構成
関連タスク
関連コンテンツ

注
Transact-SQL の代わりに、可用性グループの作成ウィザードまたは SQL Server PowerShell コマンドレットを使用する方法もあります。詳細については、「新しい可用性グループウィザードの使用 (SQL Server Management Studio)」、「[新しい可用性グループ] ダイアログボックスの使用 (SQL Server Management Studio)」、または「可用性グループの作成 (SQL Server PowerShell)」を参照してください。

Transact-SQL の代わりに、可用性グループの作成ウィザードまたは SQL Server PowerShell コマンドレットを使用する方法もあります。詳細については、「新しい可用性グループウィザードの使用 (SQL Server Management Studio)」、「[新しい可用性グループ] ダイアログボックスの使用 (SQL Server Management Studio)」、または「可用性グループの作成 (SQL Server PowerShell)」を参照してください。

作業を開始する準備

可用性グループを初めて作成する場合は、あらかじめこのセクションに目を通しておくことを強くお勧めします。

前提条件、制限事項、および推奨事項

可用性グループを作成する前に、可用性レプリカをホストする SQL Server のインスタンスが同じ Windows Server Failover Clustering (WSFC) フェールオーバークラスタリング内の別の WSFC ノードに存在していることを確認します。また、各サーバーインスタンスが AlwaysOn 可用性グループの他のすべての前提条件を満たしていることも確認します。詳細については、「AlwaysOn 可用性グループの前提条件、制限事項、および推奨事項 (SQL Server)」をお読みいただくよう強くお勧めします。

セキュリティ

権限

sysadmin 固定サーバーロールのメンバーシップと、CREATE AVAILABILITY GROUP サーバー権限、ALTER ANY AVAILABILITY GROUP 権限、CONTROL SERVER 権限のいずれかが必要です。

[トップに戻る] リンクで使用される矢印アイコン [先頭に戻る]

作業の概要および対応する Transact-SQL ステートメント

次の表は、可用性グループの作成と構成に伴う基本的な作業と、これらの作業に使用する Transact-SQL ステートメントの一覧です。 AlwaysOn 可用性グループに関連したこれらの作業は、この表に示されている順に実行する必要があります。

作業	Transact-SQL ステートメント	作業の実行場所*
データベースミラーリングエンドポイントを作成する (SQL Server インスタンスごとに 1 回)	CREATE ENDPOINT endpointName ... FOR DATABASE_MIRRORING	データベースミラーリングエンドポイントが欠落している各サーバーインスタンスで実行します。
可用性グループを作成する	CREATE AVAILABILITY GROUP	初期プライマリレプリカをホストするサーバーインスタンスで実行します。
セカンダリレプリカを可用性グループに参加させる	ALTER AVAILABILITY GROUP group_name JOIN	セカンダリレプリカをホストする各サーバーインスタンスで実行します。
セカンダリデータベースを準備する	BACKUP および RESTORE	プライマリレプリカをホストするサーバーインスタンスでバックアップを作成します。セカンダリレプリカをホストする各サーバーインスタンス上で、RESTORE WITH NORECOVERY を使用してバックアップを復元します。
各セカンダリデータベースを可用性グループに参加させてデータ同期を開始する	ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = group_name	セカンダリレプリカをホストする各サーバーインスタンスで実行します。

* ここに記載されたサーバーインスタンスに接続して作業を実行します。

[トップに戻る] リンクで使用される矢印アイコン [先頭に戻る]

Transact-SQL を使用した可用性グループの作成と構成

注
「例: Windows 認証を使用した可用性グループの構成」では、以上に示した各 Transact-SQL ステートメントのコード例を交えながらサンプル構成プロシージャを紹介しています。

プライマリレプリカをホストするサーバーインスタンスに接続します。
可用性グループは、CREATE AVAILABILITY GROUP という Transact-SQL ステートメントを使用して作成します。
新しいセカンダリレプリカを可用性グループに参加させます。詳細については、「可用性グループへのセカンダリレプリカの参加 (SQL Server)」を参照してください。
可用性グループ内の各データベースについて、セカンダリデータベースを作成します。これは、プライマリデータベースの最新のバックアップを、RESTORE WITH NORECOVERY で復元することによって行います。詳細については、「可用性グループの作成 (Transact-SQL)」で、データベースバックアップの復元手順をまず参照してください。
新しいセカンダリデータベースをすべて可用性グループに参加させます。詳細については、「可用性グループへのセカンダリレプリカの参加 (SQL Server)」を参照してください。

[トップに戻る] リンクで使用される矢印アイコン [先頭に戻る]

例: Windows 認証を使用した可用性グループの構成

この例では、AlwaysOn 可用性グループ構成プロシージャのサンプルを作成します。サンプルでは、Windows 認証を使用するデータベースミラーリングエンドポイントのセットアップ、さらには、可用性グループとそのセカンダリデータベースの作成と構成を Transact-SQL を使用して行います。

この例の内容は次のとおりです。

サンプル構成プロシージャを使用するうえでの前提条件
サンプル構成プロシージャ
サンプル構成プロシージャの完全なコード例

サンプル構成プロシージャを使用するうえでの前提条件

このサンプルプロシージャには、次の要件があります。

サーバーインスタンスは AlwaysOn 可用性グループをサポートしている必要があります。詳細については、「AlwaysOn 可用性グループの前提条件、制限事項、および推奨事項 (SQL Server)」を参照してください。
2 つのサンプルデータベース (MyDb1 および MyDb2) が、プライマリレプリカをホストするサーバーインスタンス上に存在する必要があります。次のコード例では、これらの 2 つのデータベースを作成、構成し、それぞれの完全バックアップを作成します。これらのコード例は、サンプルの可用性グループの作成先となるサーバーインスタンス上で実行します。サンプル可用性グループの初期プライマリレプリカは、このサーバーインスタンスでホストされます。
1. 次の例の Transact-SQL では、これらのデータベースを作成し、完全復旧モデルを使用するように変更を加えています。
```
-- Create sample databases:
CREATE DATABASE MyDb1;
GO
ALTER DATABASE MyDb1 SET RECOVERY FULL;
GO

CREATE DATABASE MyDb2;
GO
ALTER DATABASE MyDb2 SET RECOVERY FULL;
GO
```
2. 次のコード例では、MyDb1 および MyDb2 データベースの完全バックアップを作成します。このコード例では、架空のバックアップ共有 \\FILESERVER\SQLbackups を使用します。
```
-- Backup sample databases:
BACKUP DATABASE MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FORMAT
GO

BACKUP DATABASE MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FORMAT
GO
```

[例の先頭に戻る]

サンプル構成プロシージャ

このサンプル構成では、信頼関係のある異なるドメイン (DOMAIN1 と DOMAIN2) の下でサービスアカウントが実行される 2 つのスタンドアロンサーバーインスタンスに可用性レプリカを作成します。

次の表は、このサンプル構成で使用する値をまとめたものです。

初期ロール	システム	ホスト SQL Server インスタンス
プライマリ	COMPUTER01	AgHostInstance
セカンダリ	COMPUTER02	既定のインスタンス

可用性グループの作成先となるサーバーインスタンス (COMPUTER01 上の AgHostInstance という名前のインスタンス) 上に、dbm_endpoint という名前のデータベースミラーリングエンドポイントを作成します。このエンドポイントはポート 7022 を使用します。可用性グループの作成先となるサーバーインスタンスには、プライマリレプリカがホストされることに注意してください。
```
-- Create endpoint on server instance that hosts the primary replica:
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO
```
セカンダリレプリカをホストするサーバーインスタンス (COMPUTER02 上の既定のサーバーインスタンス) 上にエンドポイント dbm_endpoint を作成します。このエンドポイントはポート 5022 を使用します。
```
-- Create endpoint on server instance that hosts the secondary replica: 
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO
```

注
可用性レプリカをホストするサーバーインスタンスのサービスアカウントが同じドメインアカウントで実行されている場合、この手順は不要です。省略して次の手順に進んでください。

2 つのサーバーインスタンスのサービスアカウントが、互いに異なるドメインユーザーで実行されている場合、それぞれのサーバーインスタンス上に、相手のサーバーインスタンス用のログインを作成し、このログイン権限に、ローカルのデータベースミラーリングエンドポイントのアクセス権を付与します。

ログインを作成し、エンドポイントの権限を付与するための Transact-SQL ステートメントのコード例を次に示します。ここでは、リモートサーバーインスタンスのドメインアカウントを domain_name\user_name としています。

  -- If necessary, create a login for the service account, domain_name\user_name
  -- of the server instance that will host the other replica:
  USE master;
  GO
  CREATE LOGIN [domain_name\user_name] FROM WINDOWS;
  GO
  -- And Grant this login connect permissions on the endpoint:
  GRANT CONNECT ON ENDPOINT::dbm_endpoint 
     TO [domain_name\user_name];
  GO

ユーザーデータベースが存在するサーバーインスタンス上に、可用性グループを作成します。

次のコード例では、サンプルデータベースの MyDb1 と MyDb2 を作成したサーバーインスタンス上に、MyAG という名前の可用性グループを作成しています。最初に、COMPUTER01 上のローカルサーバーインスタンス (AgHostInstance) が指定されています。初期プライマリレプリカは、このインスタンスによってホストされます。リモートサーバーインスタンス (COMPUTER02 上の既定のサーバーインスタンス) は、セカンダリレプリカをホストするように指定されています。どちらの可用性レプリカも、非同期コミットモードと手動フェールオーバーを使用するように構成します (非同期コミットのレプリカでは、手動フェールオーバーは、データ損失の可能性を伴う強制フェールオーバーを意味します)。
```
-- Create the availability group, MyAG: 
CREATE AVAILABILITY GROUP MyAG 
   FOR 
      DATABASE MyDB1, MyDB2 
   REPLICA ON 
      'COMPUTER01\AgHostInstance' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022', 
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL
         ),
      'COMPUTER02' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL
         ); 
GO
```
可用性グループを作成するための他の Transact-SQL コード例については、「CREATE AVAILABILITY GROUP (Transact-SQL)」を参照してください。
セカンダリレプリカをホストするサーバーインスタンス上で、セカンダリレプリカを可用性グループに参加させます。

次のコード例では、COMPUTER02 上のセカンダリレプリカを MyAG 可用性グループに参加させています。
```
-- On the server instance that hosts the secondary replica, 
-- join the secondary replica to the availability group:
ALTER AVAILABILITY GROUP MyAG JOIN;
GO
```
セカンダリレプリカをホストするサーバーインスタンス上でセカンダリデータベースを作成します。

次のコード例では、RESTORE WITH NORECOVERY でデータベースバックアップを復元することによって、MyDb1 と MyDb2 のセカンダリデータベースを作成しています。
```
-- On the server instance that hosts the secondary replica, 
-- Restore database backups using the WITH NORECOVERY option:
RESTORE DATABASE MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NORECOVERY
GO

RESTORE DATABASE MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH NORECOVERY
GO
```

プライマリレプリカをホストするサーバーインスタンス上で、各プライマリデータベースのトランザクションログをバックアップします。

重要
実際に可用性グループを構成する際は、このログのバックアップを作成する前に、まず対応するセカンダリデータベースを可用性グループに参加させ、それが済んでからプライマリデータベースのログバックアップ作業を行うことをお勧めします。

次のコード例では、MyDb1 および MyDb2 のトランザクションログのバックアップを作成します。

-- On the server instance that hosts the primary replica, 
-- Backup the transaction log on each primary database:
BACKUP LOG MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NOFORMAT
GO

BACKUP LOG MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITHNOFORMAT
GO

ヒント
通常、ログバックアップは各プライマリデータベースで作成した後、対応するセカンダリデータベースで (WITH NORECOVERY を使用して) 復元する必要があります。ただし、データベースを作成したばかりでこのログバックアップがまだ作成されていない場合や、復旧モデルを SIMPLE から FULL に変更したばかりの場合など、ログバックアップが不要な場合もあります。

通常、ログバックアップは各プライマリデータベースで作成した後、対応するセカンダリデータベースで (WITH NORECOVERY を使用して) 復元する必要があります。ただし、データベースを作成したばかりでこのログバックアップがまだ作成されていない場合や、復旧モデルを SIMPLE から FULL に変更したばかりの場合など、ログバックアップが不要な場合もあります。

セカンダリレプリカをホストするサーバーインスタンス上で、セカンダリデータベースにログバックアップを適用します。

次のコード例では、RESTORE WITH NORECOVERY でデータベースバックアップを復元することによって、MyDb1 と MyDb2 のセカンダリデータベースにバックアップを適用しています。

重要
実際のセカンダリデータベースを準備する際は、セカンダリデータベースの作成元となったデータベースバックアップの後に作成されたすべてのログバックアップを適用する必要があります。その際には古いものから順に適用し、毎回 WITH NORECOVERY を使用します。当然、完全と差分の両方のデータベースバックアップを復元する場合は、差分バックアップ以降に作成されたログバックアップを適用するだけでかまいません。

実際のセカンダリデータベースを準備する際は、セカンダリデータベースの作成元となったデータベースバックアップの後に作成されたすべてのログバックアップを適用する必要があります。その際には古いものから順に適用し、毎回 WITH NORECOVERY を使用します。当然、完全と差分の両方のデータベースバックアップを復元する場合は、差分バックアップ以降に作成されたログバックアップを適用するだけでかまいません。

-- Restore the transaction log on each secondary database,
-- using the WITH NORECOVERY option:
RESTORE LOG MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FILE=1, NORECOVERY
GO
RESTORE LOG MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FILE=1, NORECOVERY
GO

セカンダリレプリカをホストするサーバーインスタンス上で、新しいセカンダリデータベースを可用性グループに参加させます。

次のコード例では、MyDb1 のセカンダリデータベースと MyDb2 のセカンダリデータベースを順に MyAG 可用性グループに参加させています。
```
-- On the server instance that hosts the secondary replica, 
-- join each secondary database to the availability group:
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;
GO

ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;
GO
```

[例の先頭に戻る]

サンプル構成プロシージャの完全なコード例

以下のコードは、すべての手順のコード例を総合したサンプル構成プロシージャの全体像です。このコード例で使用されているプレースホルダーの値については次の表にまとめました。このコード例の手順の詳細については、このトピックの「サンプル構成プロシージャを使用するうえでの前提条件」および「サンプル構成プロシージャ」を参照してください。

プレースホルダー	説明
\\FILESERVER\SQLbackups	架空のバックアップ共有。
\\FILESERVER\SQLbackups\MyDb1.bak	MyDb1 のバックアップファイル。
\\FILESERVER\SQLbackups\MyDb2.bak	MyDb2 のバックアップファイル。
7022	各データベースミラーリングエンドポイントに割り当てられたポート番号。
COMPUTER01\AgHostInstance	初期プライマリレプリカをホストするサーバーインスタンス。
COMPUTER02	初期セカンダリレプリカをホストするサーバーインスタンス。これは、COMPUTER02 上の既定のサーバーインスタンスです。
dbm_endpoint	各データベースミラーリングエンドポイントに指定した名前。
MyAG	サンプルの可用性グループの名前。
MyDb1	1 つ目のサンプルデータベースの名前。
MyDb2	2 つ目のサンプルデータベースの名前。
DOMAIN1\user1	初期プライマリレプリカをホストするサーバーインスタンスのサービスアカウント。
DOMAIN2\user2	初期セカンダリレプリカをホストするサーバーインスタンスのサービスアカウント。
TCP://COMPUTER01.Adventure-Works.com:7022	COMPUTER01 上の SQL Server の AgHostInstance インスタンスのエンドポイント URL。
TCP://COMPUTER02.Adventure-Works.com:5022	COMPUTER02 上の SQL Server の既定のインスタンスのエンドポイント URL。

注
可用性グループを作成するための他の Transact-SQL コード例については、「CREATE AVAILABILITY GROUP (Transact-SQL)」を参照してください。

-- on the server instance that will host the primary replica, 
-- create sample databases:
CREATE DATABASE MyDb1;
GO
ALTER DATABASE MyDb1 SET RECOVERY FULL;
GO

CREATE DATABASE MyDb2;
GO
ALTER DATABASE MyDb2 SET RECOVERY FULL;
GO

-- Backup sample databases:
BACKUP DATABASE MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FORMAT
GO

BACKUP DATABASE MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FORMAT
GO

-- Create the endpoint on the server instance that will host the primary replica:
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

-- Create the endpoint on the server instance that will host the secondary replica: 
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

-- If both service accounts run under the same domain account, skip this step. Otherwise, 
-- On the server instance that will host the primary replica, 
-- create a login for the service account 
-- of the server instance that will host the secondary replica, DOMAIN2\user2, 
-- and grant this login connect permissions on the endpoint:
USE master;
GO
CREATE LOGIN [DOMAIN2\user2] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::dbm_endpoint 
   TO [DOMAIN2\user2];
GO

-- If both service accounts run under the same domain account, skip this step. Otherwise, 
-- On the server instance that will host the secondary replica,
-- create a login for the service account 
-- of the server instance that will host the primary replica, DOMAIN1\user1, 
-- and grant this login connect permissions on the endpoint:
USE master;
GO

CREATE LOGIN [DOMAIN1\user1] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::dbm_endpoint 
   TO [DOMAIN1\user1];
GO

-- On the server instance that will host the primary replica, 
-- create the availability group, MyAG: 
CREATE AVAILABILITY GROUP MyAG 
   FOR 
      DATABASE MyDB1, MyDB2 
   REPLICA ON 
      'COMPUTER01\AgHostInstance' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         FAILOVER_MODE = AUTOMATIC
         ),
      'COMPUTER02' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:7022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         FAILOVER_MODE = AUTOMATIC
         ); 
GO

-- On the server instance that hosts the secondary replica, 
-- join the secondary replica to the availability group:
ALTER AVAILABILITY GROUP MyAG JOIN;
GO

-- Restore database backups onto this server instance, using RESTORE WITH NORECOVERY:
RESTORE DATABASE MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NORECOVERY
GO

RESTORE DATABASE MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH NORECOVERY
GO

-- Back up the transaction log on each primary database:
BACKUP LOG MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NOFORMAT
GO

BACKUP LOG MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITHNOFORMAT
GO

-- Restore the transaction log on each secondary database,
-- using the WITH NORECOVERY option:
RESTORE LOG MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FILE=1, NORECOVERY
GO
RESTORE LOG MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FILE=1, NORECOVERY
GO

-- On the server instance that hosts the secondary replica, 
-- join each secondary database to the availability group:
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;
GO

ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;
GO

[トップに戻る] リンクで使用される矢印アイコン [例の先頭に戻る]

Share via

可用性グループの作成 (Transact-SQL)

作業を開始する準備

前提条件、制限事項、および推奨事項

セキュリティ

権限

作業の概要および対応する Transact-SQL ステートメント

Transact-SQL を使用した可用性グループの作成と構成

例: Windows 認証を使用した可用性グループの構成

サンプル構成プロシージャを使用するうえでの前提条件

サンプル構成プロシージャ

サンプル構成プロシージャの完全なコード例

関連タスク

関連コンテンツ

関連項目

概念

その他のリソース