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

適用対象 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 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 の使用

Note

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

  1. プライマリ レプリカをホストするサーバー インスタンスに接続します。

  2. 可用性グループは、CREATE AVAILABILITY GROUP という Transact-SQL ステートメントを使用して作成します。

  3. 新しいセカンダリ レプリカを可用性グループに参加させます。 詳細については、可用性グループへのセカンダリ レプリカの参加 (SQL Server) に関するページを参照してください。

  4. 可用性グループ内の各データベースについて、セカンダリ データベースを作成します。これは、プライマリ データベースの最新のバックアップを、RESTORE WITH NORECOVERY で復元することによって行います。 詳細については、「例:Windows 認証を使用した可用性グループの構成 (Transact-SQL)」で、データベース バックアップの復元手順をまず参照してください。

  5. 新しいセカンダリ データベースをすべて可用性グループに参加させます。 詳細については、可用性グループへのセカンダリ レプリカの参加 (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]

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

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

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

初期ロール システム ホスト SQL Server インスタンス
プライマリ COMPUTER01 AgHostInstance
セカンダリ COMPUTER02 既定のインスタンス
  1. 可用性グループの作成先となるサーバー インスタンス ( 上の という名前のインスタンス) 上に、 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  
    
  2. セカンダリ レプリカをホストするサーバー インスタンス ( 上の既定のサーバー インスタンス) 上にエンドポイント 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  
    
  3. 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  
    
  4. ユーザー データベースが存在するサーバー インスタンス上に、可用性グループを作成します。

    次のコード例では、サンプル データベースの MyDb1MyDb2 を作成したサーバー インスタンス上に、 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)」を参照してください。

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

    次のコード例では、 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  
    
  6. セカンダリ レプリカをホストするサーバー インスタンス上でセカンダリ データベースを作成します。

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

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

    重要

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

    次のコード例では、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 に変更したばかりの場合など、ログ バックアップが不要な場合もあります。

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

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

    重要

    実際のセカンダリ データベースを準備する際は、セカンダリ データベースの作成元となったデータベース バックアップの後に作成されたすべてのログ バックアップを適用する必要があります。その際には古いものから順に適用し、毎回 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  
    
  9. セカンダリ レプリカをホストするサーバー インスタンス上で、新しいセカンダリ データベースを可用性グループに参加させます。

    次のコード例では、 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 可用性グループを有効にするには

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

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

関連コンテンツ

参照

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