デベロッパー

Cloud SQL リードレプリカを使用した分析と本番環境のワークロード分離

※この投稿は米国時間 2021 年 7 月 19 日に、Google Cloud blog に投稿されたものの抄訳です。

Cloud SQL for SQL Server に、プレビュー リリースの機能としてリードレプリカが追加されました。Cloud SQL for MySQL および Cloud SQL for PostgreSQL の両方に、一般提供(GA)機能としてリードレプリカが追加されています。

このブログ投稿では、分析クエリを始めとしたデータの後処理操作などの、読み取り専用のワークロード向けに使用可能な、Cloud SQL リードレプリカの作成手順を紹介します。このブログ投稿の目的として、SQL Server Management Studio(SSMS)に接続された SQL Server インスタンスを使用します。このブログ投稿における、リードレプリカを使用して読み取り専用のデータベースのワークロードを本番環境のデータベースのワークロードから分離する、全体的なアプローチは、MySQL および PostgreSQL Cloud SQL インスタンスでも機能します。

Cloud SQL for SQL Server インスタンスの作成

まず SQL Server インスタンスを作成して、リードレプリカの機能を実演します。

Google Cloud Console の Cloud SQL のセレクションで、[インスタンスを作成] ボタンをクリックして [SQL Server を選択] オプションを選択します。この投稿の目的では、インスタンスの作成設定はほとんどデフォルトのままで問題ありません。変更が必要なのは、[データベースのバージョン] と [リージョンとゾーンの可用性の選択] という 2 つの設定です。[データベースのバージョン] を [SQL Server 2017 Enterprise] に変更します。これは、現在リードレプリカ機能が含まれる唯一のバージョンです。これはデモなので、インスタンスのリージョンとゾーンの可用性の設定では、インスタンスの費用を削減することを目的として、[シングルゾーン] に設定します。これが実際の本番環境のインスタンスである場合は、[複数のゾーン(高可用性)] に設定します。また、[設定オプションを表示] > [接続] セクションで、[プライベート IP] を有効にしたうえで、[ネットワーク] には [default] を選択する必要があります。

以下の値を入力して [プライマリ] インスタンスを作成します

  • インスタンス ID:

    • 「sql-server-primary」と入力

  • パスワードを入力するか、[パスワード] 欄の [生成] ボタンをクリックします。今後のために、パスワードをメモします。

  • [データベースのバージョン] として [SQL Server 2017 Enterprise] を選択します。

  • [ゾーンの可用性] で [シングルゾーン] を選択します。

インスタンスの作成に必要なすべての値を設定したら、次のようになるはずです。

Create primary Cloud SQL instance

[インスタンスを作成] ボタンをクリックしてインスタンスを作成します。インスタンスの作成には約 3 分間かかります。

リードレプリカを作成する

[プライマリ] インスタンスの作成が完了したら、Cloud SQL インスタンスのリストから選択し、詳細ページを表示します。そこから、左のサイドメニューの [レプリカ] タブをクリックします。

Select Cloud SQL Replicas from left menu

[レプリカ] ページで [リードレプリカを作成] ボタンをクリックします。

Create Cloud SQL Read Replica

レプリカ インスタンスの情報フォームで、[レプリカを作成] ボタンをクリックします。このように 1 度クリックするだけで、Cloud SQL が自動的に「可用性グループ」の起動プロセス全体を処理し、そのグループ内にリードレプリカを作成します。リードレプリカはプライマリ インスタンスの読み取り専用のコピーであり、プライマリへの変更はほぼリアルタイムで反映されます。

数分後に、Cloud SQL インスタンス ページにリードレプリカが表示されるはずです。

Cloud SQL Instance list with Read Replica visible

GCE の Windows VM インスタンスの作成

実行中のリードレプリカが用意できたので、使用してみましょう。Google Compute Engine の Windows VM インスタンスを作成し、作成したばかりの Cloud SQL インスタンスに SSMS を使用して接続できるようにします。

Compute Engine Console で [インスタンスを作成] ボタンをクリックします。ほとんどはデフォルト設定で十分ですが、[ブートディスク] を変更します。

  • インスタンスの [名前] を入力します。

    • 例: sql-analytics

  • [インスタンスを作成] フォームの [ブートディスク] セクションで、[変更] ボタンをクリックして [オペレーティング システム] として [Windows Server] を選択します。[バージョン] で [Windows Server 2019 Datacenter] を選択します。[選択] ボタンをクリックしてブートディスクを設定します。

Create Windows VM instance select boot disk

完了した Compute Engine の [インスタンスを作成] フォームは次のようになっているはずです。

Create Windows VM instance

[作成] ボタンをクリックしてインスタンスを作成します。起動するまで数分間待ち、新たに作成した VM インスタンス上でリモート デスクトップ(RDP)接続を起動する次の手順に進みます。

  • 注: 業務のスケジュールに合わせて起動および停止するようにスケジュール設定すると、この Compute Engine のインスタンスの費用を削減できます。

Google Cloud Console を介した Windows VM へのリモート デスクトップ(RDP)

Cloud Console の Compute Engine インスタンスのリストで、新たに作成されたインスタンスを見つけて名前をクリックし、VM インスタンスの詳細ページを表示します。

  • [Windows パスワードを設定] ボタンをクリックして、ログインと VM インスタンスの管理に使用するパスワードを設定します。今後使用できるよう、パスワードをコピーしてメモします。

  • 次に、[リモート デスクトップ プロトコル](RDP)ボタンをクリックし、VM にログインしてリモート デスクトップ接続を確立します。

SQL Analytics VM details

Windows VM インスタンスを構成し、Cloud SQL 接続用のソフトウェアをインストールする

次の手順では、Cloud SQL インスタンスに SQL Server Management Studio(SSMS)とともに接続するために必要なソフトウェアをダウンロード、インストール、初期化します。新たに作成された Windows VM インスタンスでデフォルトのウェブブラウザを使用するため、[IE セキュリティ強化の構成] を無効化して、ブラウザを介してソフトウェアをダウンロードできるようにする必要があります。

  • リモート デスクトップ セッションで、[サーバー マネージャー] ウィンドウがすでに開いているはずです。開いていない場合は、リモート デスクトップ画面の左下にある [Windows] アイコンボタンをクリックし、[サーバー マネージャー] を検索して開きます。[サーバー マネージャー] が開いたら、[ローカル サーバー] タブをクリックして [IE セキュリティ強化の構成] 項目をクリックして [オフ] に設定します。

Turn off IE security settings on Windows VM instance

次に、次のソフトウェアをインストールして SQL Server Management Studio を Cloud SQL に接続するタスクを完了します。

  • Google Cloud SDK

  • Cloud SQL Auth Proxy

  • SQL Server Management Studio

Google Cloud SDK を介して Google Cloud API アクセスを認証する

この手順の最初のタスクは、Cloud SDK を介してアクセスを認証することです。これにより、Cloud SQL API への認証済みアクセスを有効化するために使用する、gcloud ツールが提供されます。これを Compute Engine VM インスタンスで行えるよう、すでに Cloud SDK がインストールされています。自分のマシンでこの設定を行っている場合は、Cloud SDK ページにアクセスしてダウンロードします。

Windows VM インスタンスのリモート デスクトップ セッション内で、画面左下の Windows アイコンをクリックして Powershell ターミナル ウィンドウを開きます。「Powershell」と検索して選択して開きます。

Windows VM instance search for Powershell

次に、以下の Powershell コマンドを実行して、Cloud SDK を介してユーザー アカウントを認証します。

  gcloud auth login

サービス アカウントとして認証するか、ユーザー アカウントで認証するかを尋ねるコマンド プロンプト ウィンドウが表示されます。[ユーザー アカウント] を選択して Enter キーを押します(サービス アカウント キー付きのサービス アカウントも使用できます)。これで認証プロセスが開始されます。コマンド プロンプト ウィンドウにコピーして貼り付けるためのトークン文字列を取得して、アカウントのアクセス権を確認する必要があります。

このプロセスが中断した場合や、今後 Cloud SDK を再認証する必要がある場合は、Windows VM インスタンスの Powershell ウィンドウで「gcloud auth login」コマンドを再実行できます。

Console SDK を介して認証したので、これを使用して Cloud SQL で認証できます。Console SDK の「gcloud ツール」は、Cloud SQL Admin API を使用して Cloud SQL とやり取りするので、次にこの API を有効化します。前述のコマンドの実行に使用したのと同じ Powershell ウィンドウで、gcloud ツールを使用して次のコマンドを入力して Cloud SQL Admin API を有効化します。

  gcloud services enable sqladmin.googleapis.com

Cloud SQL Auth Proxy のインストールと実行

次にインストールが必要なのは、Cloud SQL Auth Proxyです。これは、Windows VM インスタンスと Cloud SQL の間の安全な通信チャネルを提供します。

Windows VM インスタンスのリモート デスクトップ セッションで開いている Powershell ウィンドウで、次のコマンドを入力して、Cloud SQL Auth Proxy をダウンロードおよびインストールします。

  Invoke-WebRequest -Uri https://dl.google.com/cloudsql/cloud_sql_proxy_x64.exe -OutFile cloud_sql_proxy.exe

コマンドを実行すると、Cloud SQL Auth Proxy がダウンロードされ、コマンドの実行元の Powershell ディレクトリに「cloud_sql_proxy.exe」という名前のファイルとして保存されます。

次に Cloud SQL Auth Proxy を実行します。まず、「プライマリ」インスタンスに接続し、サンプルデータが入力されたデータベースとテーブルを作成する、いくつかの SQL ステートメントを実行します。Cloud Console の Cloud SQL インスタンス リストから「インスタンス接続名」をコピーして手順を始めます。

List of Cloud SQL instances with primary instance connection name highlighted

次に、Windows VM インスタンスのリモート デスクトップ セッションに戻り、Powershell で次のコマンドを実行します。[Instance-connection-name] は、コピーした値に置き換えます。

  ./cloud_sql_proxy -instances=[Instance-connection-name]=tcp:1433

次のような Powershell ウィンドウのレスポンスとともに、プロキシが実行されているのがわかるはずです。

Powershell run Cloud SQL Auth proxy

SQL Server Management Studio(SSMS)のインストール

最後に、このブログ投稿でのすべての設定作業の利点を体験してみましょう。Cloud SQL の「プライマリ」およびリードレプリカ インスタンスに対して SQL ステートメントとクエリを実行するために使用する、SQL Server Management Studio をインストールします。

  • Windows Server VM インスタンスのリモート デスクトップ セッションでウェブブラウザ ウィンドウを開き、SQL Server Management Studio のダウンロード ページ(https://docs.microsoft.com/ja-jp/sql/ssms)にアクセスします。

Download SSMS

  • [SQL Server Management Studio のダウンロード] リンクをクリックし、表示されたダウンロードのダイアログ ウィンドウで [実行] をクリックします。

Run SSMS

  • [インストール] ボタンをクリックし、SQL Server Management Studio のインストール ウィザードでインストールが行われるまで待ちます。

SSMS install complete dialog window
  • [閉じる] ボタンをクリックしてプロセスを完了します。

Cloud SQL Auth Proxy を介して SSMS を使用し、Cloud SQL に接続する

Windows VM インスタンスのリモート デスクトップ セッションの左下の Windows アイコンをクリックして「ssms」と入力し、SSMS を開きます。オプションの「Microsoft SQL Server Management Studio」が表示されるはずです。続行し、クリックしてプログラムを開きます。

Search for SSMS and run it

SSMS の [オブジェクト エクスプローラを接続] ダイアログ ウィンドウが開き、Cloud SQL の「プライマリ」インスタンスに接続できるようになります。次の値を入力します。

  • [サーバーの種類] に「データベース エンジン」と入力します。

  • [サーバー名] に、SQL Server インスタンスの IP アドレスとして「127.0.0.1」と入力します。

  • [認証] に [SQL Server 認証] を選択します。

  • [ログイン] に「sqlserver」と入力します。

  • [パスワード] に Cloud SQL インスタンスの作成時に使用したパスワードを入力します。

すべての値を入力すると、[サーバーへの接続] ダイアログが次のように表示されるはずです。

SSMS Connect to server dialog window

[接続] ボタンを押して、前の手順で実行した Powershell コマンドからすでに実行中の Cloud SQL Auth Proxy を介して、Cloud SQL インスタンスに接続します。

SSMS を使用して Cloud SQL にアクセスしてクエリを実行する

Cloud SQL の「プライマリ」インスタンスに接続したため、データベースとテーブルを作成する SQL ステートメントをいくつか実行しましょう。[127.0.0.1] SQL Server オブジェクトを右クリックします。

Select database in SSMS to start new query

次に、[新しいクエリ] を選択します。

Select database in SSMS and choose new query

[新しいクエリ] ウィンドウに、次の SQL ステートメントを入力します。

  CREATE DATABASE demo;
GO
USE demo;
GO

CREATE TABLE TRANSACTIONS (
    TRANSACTION_ID BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FROM_ACCT CHAR(10) NOT NULL,
    TO_ACCT CHAR(10) NOT NULL,
    FROM_ROUTE CHAR(9) NOT NULL,
    TO_ROUTE CHAR(9) NOT NULL,
    AMOUNT DECIMAL(10,4) NOT NULL,
    TIMESTAMP DATETIME2 NOT NULL
);
GO

[実行] ボタンをクリックします。

Execute database and table create SQL

今度は、今作成したテーブルにいくつかのデータを入力しましょう。SQL WHILE ループを使用する次の SQL ステートメントを入力して、「TRANSACTIONS」テーブルに 15,000 個のレコードの例を挿入します。

  DECLARE @RandomDate DATETIME 
DECLARE @start AS INT = 1
DECLARE @end AS INT = 15000

WHILE(@start <= @end)
BEGIN
    /* Get random date b/t today and the last seven days */
    SELECT @RandomDate = DATEADD(DAY,-ABS(CHECKSUM(NEWID()) % 8), 
      CONVERT (DATE, SYSDATETIME()))
    /* Insert random values with random date */
    INSERT INTO TRANSACTIONS
      (FROM_ACCT, TO_ACCT, FROM_ROUTE, TO_ROUTE, AMOUNT, [TIMESTAMP])
    VALUES (
     CAST(RIGHT(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT), 10) AS CHAR(10)),/*FROM_ACCT */
     CAST(RIGHT(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT), 10) AS CHAR(10)),/*TO_ACCT */
     CAST(RIGHT(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT), 9) AS CHAR(9)),  /*FROM_ROUTE */
     CAST(RIGHT(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT), 9) AS CHAR(9)),  /*TO_ROUTE */
	  ROUND(RAND(CHECKSUM(NEWID())) * (20000), 2), /* AMOUNT */
	  DATEADD(DAY, 
          ROUND(DATEDIFF(DAY, @RandomDate, @RandomDate) * 
            RAND(CHECKSUM(NEWID())), 5), 
	       DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % 86400,
               @RandomDate)) /* TIMESTAMP */
  )
  SET @start += 1
END

[実行] ボタンをクリックします。

SSMS execute insert into Transactions table SQL

Cloud SQL の「プライマリ」インスタンスでこれらの SQL オペレーションを実行したので、リードレプリカには新しいデータベース、テーブル、データが含まれているはずです。リードレプリカに接続してこれを確かめましょう。

SSMS を使用してリードレプリカ インスタンスの分析を実行する

Cloud SQL の「プライマリ」インスタンスからリードレプリカ インスタンスに SSMS の接続を切り替えるために必要なのは、GCE の Windows VM インスタンスの開いている Powershell ウィンドウで実行中の、Cloud SQL Auth Proxy を停止して再起動することだけです。

Cloud SQL Auth Proxy が実行中の Powershell ウィンドウで、Ctrl + c キーを押してプロキシを停止します。上矢印キーを押して、プロキシの実行に使用した前のコマンドを表示します。リードレプリカ インスタンスの [インスタンス接続名] でコマンドを更新します。これは、Cloud Console のCloud SQL インスタンス リストで確認できます。

Cloud SQL Instance list with read replica connection name highlighted

次に、更新されたコマンドを実行し、Cloud SQL Auth Proxy を再起動して、リードレプリカ インスタンスへの新たな接続を行います。

Connect Cloud SQL Auth Proxy to read replica Cloud SQL instance

GCE の Windows VM インスタンスですでに開いてある SSMS ウィンドウを使用して、[オブジェクト エクスプローラ] ウィンドウの接続済みサーバーのリストの上にある [更新] アイコンをクリックします。

SSMS refresh connection

これにより、SSMS がリードレプリカ インスタンスに接続されます。接続したら、127.0.0.1 SQL Server オブジェクトを右クリックして [新しいクエリ] を選択します。

基本的な分析クエリを実行して、すべてが正常に動作していることを確認します。以前に作成した TRANSACTIONS テーブルに対して、過去 7 日間の 1 日の平均トランザクションと 1 日のトランザクションの合計をクエリする、次の SQL クエリを入力します。

  SELECT FORMAT(AVG(AMOUNT),'#,0.00') AS DAILY_AVG, 
    FORMAT(SUM(AMOUNT),'#,0.00') AS DAILY_TOTAL,
	CAST([TIMESTAMP] AS DATE) as [DATE] FROM TRANSACTIONS
WHERE 
  CAST([TIMESTAMP] AS DATE) > DATEADD(DAY, -7, GETDATE())
GROUP BY CAST([TIMESTAMP] AS DATE)
ORDER BY [DATE]

[実行] ボタンをクリックします。

SSMS query Transactions table

これで、本番環境のワークロードの重要な処理を行いながら、プライマリ データベース サーバーにパフォーマンス上の影響を及ぼすことなく、分析クエリを柔軟に実行できるようになりました。お疲れ様でした。

ご精読ありがとうございました。Cloud SQL とリードレプリカを使用して、次のプロジェクトを開始しましょう。

 

-Developer Relations Jonathan Simon