デベロッパー

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

2021年7月28日

Google Cloud Japan Team

※この投稿は米国時間 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] を選択します。
[ゾーンの可用性] で [シングルゾーン] を選択します。

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/image1-create-primary-instance.max-1600x1600.jpg

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

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

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/select-cloud-sql-replicas-from-left-menu.max-900x900.jpg

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/click-cloud-sql-create-read-replica.max-900x900.jpg

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

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/cloud-sql-instance-list-with-read-replica-vi.max-700x700.jpg

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

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

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

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

例: sql-analytics

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/image2-create-windows-vm-select-boot-disk.max-900x900.jpg

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/image3-create-windows-vm.max-1000x1000.jpg

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

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

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

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

[Windows パスワードを設定] ボタンをクリックして、ログインと VM インスタンスの管理に使用するパスワードを設定します。今後使用できるよう、パスワードをコピーしてメモします。
次に、[リモートデスクトッププロトコル]（RDP）ボタンをクリックし、VM にログインしてリモートデスクトップ接続を確立します。

https://storage.googleapis.com/gweb-cloudblog-publish/images/image4-sql-analytics-vm-details.max-800x800.jpg

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

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

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/image5-turn-off-ie-security-settings.max-1500x1500.jpg

次に、次のソフトウェアをインストールして 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」と検索して選択して開きます。

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

読み込んでいます...

サービスアカウントとして認証するか、ユーザーアカウントで認証するかを尋ねるコマンドプロンプトウィンドウが表示されます。[ユーザーアカウント] を選択して 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 を有効化します。

読み込んでいます...

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

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

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

読み込んでいます...

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

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/list-of-cloud-sql-instances-with-primary-ins.max-600x600.jpg

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

読み込んでいます...

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/image8-powershell-run-cloud-sql-auth-proxy.max-600x600.jpg

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）にアクセスします。

https://storage.googleapis.com/gweb-cloudblog-publish/images/image6-download-ssms.max-700x700.jpg

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/image6-run-ssms.max-400x400.jpg

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/ssms-install-dialog-window-install-complet.max-1300x1300.jpg

[閉じる] ボタンをクリックしてプロセスを完了します。

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

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/search-for-ssms-and-open-it.max-1100x1100.jpg

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

[サーバーの種類] に「データベースエンジン」と入力します。
[サーバー名] に、SQL Server インスタンスの IP アドレスとして「127.0.0.1」と入力します。
[認証] に [SQL Server 認証] を選択します。
[ログイン] に「sqlserver」と入力します。
[パスワード] に Cloud SQL インスタンスの作成時に使用したパスワードを入力します。

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/ssms-connector-to-server-dialog-window.max-1900x1900.jpg

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

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

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/select-database-in-ssms-to-start-new-query.max-800x800.jpg

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/select-database-in-ssms-and-choose-new-query.max-800x800.jpg

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

読み込んでいます...

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/ssms-execute-database-and-table-create-sql.max-1300x1300.jpg

今度は、今作成したテーブルにいくつかのデータを入力しましょう。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

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

https://storage.googleapis.com/gweb-cloudblog-publish/images/ssms-execute-insert-into-transactions-tabl.max-1600x1600.jpg

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 インスタンスリストで確認できます。