Transact-SQL (T-SQL) を使用した Always On 可用性グループの作成

[アーティクル]
04/26/2023

適用対象:SQL Server

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

注意

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

Note

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

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

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

アクセス許可

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

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

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

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

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

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

Transact-SQL の使用

Note

これらの各 Transact-SQL ステートメントのコード例が使用された構成手順の例については、「例:Windows 認証を使用した可用性グループの構成」を参照してください。

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

」では、以上に示した各

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

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

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

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

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

サーバーインスタンスは Always On 可用性グループをサポートしている必要があります。詳細については、「Always On 可用性グループの前提条件、制限事項、および推奨事項 (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  
```

[TopOfExample]

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

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

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

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

可用性グループの作成先となるサーバーインスタンス ( 上のという名前のインスタンス) 上に、 AgHostInstance dbm_endpoint COMPUTER01という名前のデータベースミラーリングエンドポイントを作成します。このエンドポイントはポート 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  
```
セカンダリレプリカをホストするサーバーインスタンス ( 上の既定のサーバーインスタンス) 上にエンドポイント dbm_endpoint COMPUTER02を作成します。このエンドポイントはポート 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  
```
Note

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

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という名前の可用性グループを作成しています。最初に、 AgHostInstanceCOMPUTER01 上のローカルサーバーインスタンス ( ) が指定されています。初期プライマリレプリカは、このインスタンスによってホストされます。リモートサーバーインスタンス ( 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'   
    WITH NOFORMAT;  
GO
```
ヒント

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

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

重要

実際のセカンダリデータベースを準備する際は、セカンダリデータベースの作成元となったデータベースバックアップの後に作成されたすべてのログバックアップを適用する必要があります。その際には古いものから順に適用し、毎回 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`上の既定のサーバーインスタンスです。
上の	各データベースミラーリングエンドポイントに指定した名前。
MyDb1	サンプルの可用性グループの名前。
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。

Note

可用性グループを作成するための他の 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'   
    WITH NOFORMAT  
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

Related Tasks

可用性グループおよびレプリカのプロパティを構成するには

可用性グループの構成を完了するには

別の方法で可用性グループを作成する

Always On 可用性グループを有効にするには

AlwaysOn 可用性グループの有効化と無効化 (SQL Server)

データベースミラーリングエンドポイントを構成するには

Always On 可用性グループの構成のトラブルシューティング方法

参照

データベースミラーリングエンドポイント (SQL Server)
Always On 可用性グループの概要 (SQL Server)
可用性グループリスナー、クライアント接続、およびアプリケーションのフェールオーバー (SQL Server)
Always On 可用性グループの前提条件、制限事項、推奨事項 (SQL Server)

Transact-SQL (T-SQL) を使用した Always On 可用性グループの作成

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

アクセス許可

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

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

Transact-SQL の使用

」では、以上に示した各

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

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

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

Related Tasks

関連コンテンツ

参照

フィードバック

フィードバック

その他のリソース