最近、AWS上にIaaSでSQL Server 2022を構築する機会があって、SQL ServerのデータベースのバックアップをAmazon S3に保管するという要件があって検証を行っていました。

SQL Server 2022からは、SQL Server自身の機能でバックアップ先としてS3を直接指定できるようになったので、その検証内容を私の情報整理も兼ねてストーリーとしてアウトプットしていこうと思います！

目次

• S3バケット上に直接バックアップする機能について
• 構成情報と試した内容
• 検証
• 追加）メンテナンスプランのバックアップジョブで実行する場合
• 最後に・・・

S3バケット上に直接バックアップする機能について

これまでは、IaaSでSQL ServerのバックアップをS3に保管しようと思うと、以下のいずれかでやりましょうねというのがAWSからのアナウンスでした。

• Storage Gatewayを使ってEC2上にS3バケットの領域をマウントし、マウントした領域を指定してバックアップする
• EC2にバックアップ保管用にマウントしたEBSの領域にバックアップし、EBSごとスナップショットを取ってバックアップとする
• FSx上に直接バックアップを配置し、手動でS3バケットにバックアップをコピーする

参考情報）データベースレベルのバックアップオプションhttps://docs.aws.amazon.com/ja_jp/prescriptive-guidance/latest/sql-server-managing-on-aws/database-level-backup.html

率直に感じたのは、「面倒やな・・・」でした。仕組みのためのリソース費用もかかるので、もう少しシンプルに出来ないかなと調べていたところ、SQL Serverの機能で実現出来るとのことでした！

参考情報）S3 互換オブジェクト ストレージでの URL への SQL Server のバックアップhttps://learn.microsoft.com/ja-jp/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage?view=sql-server-ver16

参考情報）Backup SQL Server databases to Amazon S3
https://aws.amazon.com/jp/blogs/modernizing-with-aws/backup-sql-server-to-amazon-s3/

要するに、S3を始めとした各社のクラウドサービスで提供されているオブジェクトストレージを対象に、SQL Serverのバックアップメディアとして使えるよ！ということだそうです。

SQL Server 2022しか対応していないので、2022より前のバージョンは変わらず上記の仕組みを使わないといけないですが、それでも構成がシンプルに、費用が押さえられるならありだなと思ったので試してみようと思いました。

ちなみに、無償のExpressエディション以外であればどのエディションでも使えると記載ありました。

※DeveloperエディションはEnterpriseエディションと使える範囲が一緒なので、Developerを使うならEnterpriseの列を見る必要があります。

参考情報）SQL Server 2022 の各エディションとサポートされている機能
https://learn.microsoft.com/ja-jp/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16#rdbms-high-availability

構成情報と試した内容

以下の構成で検証を行いました。ちなみに、アクセスキーを発行してSQL Server上に登録することになるのですが、本当はアクセスキーすら発行せずに、IAMロールでassumeRoleして、STSでトークン発行して・・・と言ったことがしたくなるかもしれませんが、なんとこの機能では対応していないのです。悲しい。
※一応、別の方法で出来なくもないのですが、SQL ServerのPolyBaseという外部データ連携機能、AWS側でIDプライバイダーの登録、ActiveDirectoryのFSを使ったフェデレーション機能・・・といった大掛かりな構成にすれば出来るそうですが・・・

ただ、登録後のパスワードは最低限マスクされて見えないようになっています。流石にAWS CLIと違って平文でパスワードが見れるなんてことはないのは救いですかね。

検証なので、バックアップ対象としてシステムデータベースを使います（どれでもいいので）。
ゴールは、作成したS3バケット目掛けて、データベースのバックアップができればOKです。

検証

基本的には参考情報のURLの内容を基に実施すれば出来ちゃうのでなんてことはないはず・・・！

S3バケットの準備

まず、バックアップの配置先となるS3バケットを用意しておきます。今回は、東京リージョン (ap-northeast-1) に作りました。
※作成手順は単純なので割愛します

# aws s3api list-buckets
{
    "Buckets": [
        {
            "Name": "<s3bucket-name＞",
            "CreationDate": "2024-02-18T01:39:07+00:00"
        }
    ],
    (略)
}
# 

このバケットの名称を資格情報の中で、以下の形式で登録することになるので、バケット名を控えておきます。

s3://<s3bucket-name>.<サービスエンドポイント>

バケット名が"s3-bucket"、東京リージョンに作成していた場合は、↓のようになります。

s3://s3-bucket.s3.ap-northeast-1.amazonaws.com

※リージョンに応じたサービスエンドポイントの調べ方は以下URLを参照してくださいー

参考情報）Amazon Simple Storage Service エンドポイントとクォータhttps://docs.aws.amazon.com/ja_jp/general/latest/gr/s3.html

バックアップ用IAMポリシーとユーザの準備

S3バケットにバックアップを配置するための許可を与えるポリシーを作成しておきます。
こちらのポリシーをバックアップ用のユーザに紐づける形で、ユーザを作成します。

# aws iam get-policy --policy-arn arn:aws:iam::XXXXXXXXXXXX:policy/Apply-Backup-Sqldb-001
{
    "Policy": {
        "PolicyName": "Apply-Backup-Sqldb-001",
        "PolicyId": "ANPAZSRAWYFI7DXKJL3BW",
        "Arn": "arn:aws:iam::XXXXXXXXXXXX:policy/Apply-Backup-Sqldb-001",
        "Path": "/",
        "DefaultVersionId": "v1",
        "AttachmentCount": 1,
        "PermissionsBoundaryUsageCount": 0,
        "IsAttachable": true,
        "CreateDate": "2024-02-18T02:17:51+00:00",
        "UpdateDate": "2024-02-18T02:17:51+00:00",
        "Tags": []
    }
}
#

ユーザを作成する際、コンソールアクセスは不要なので無効にしておき、作成したあとでアクセスキーを発行し、SQL Serverに登録するまで手元に控えておきます。
※Access Key IDとSecret Access Keyの管理は厳重に！！

# 作成したユーザを確認
# aws iam get-user --user-name sql-bkup-user-001
{
    "User": {
        "Path": "/",
        "UserName": "sql-bkup-user-001",
        "UserId": "AIDAZSRAWYFIZ5HDB5EF6",
        "Arn": "arn:aws:iam::XXXXXXXXXXXX:user/sql-bkup-user-001",
        "CreateDate": "2024-02-18T02:18:36+00:00"
    }
}
#

# ユーザにアタッチされたポリシーを確認
# aws iam list-attached-user-policies --user-name sql-bkup-user-001
{
    "AttachedPolicies": [
        {
            "PolicyName": "Apply-Backup-Sqldb-001",
            "PolicyArn": "arn:aws:iam::XXXXXXXXXXXX:policy/Apply-Backup-Sqldb-001"
        }
    ]
}
#

SQL Server 2022を導入されたEC2の作成

検証するサーバを用意しないといけないのですが、機能の検証が出来れば良いので、SQL Server 2022 Standard Edition同梱版のAMIを指定して作成しました。

この場合使用できるインスタンスタイプが制限されているので、AWS Pricing Calculatorで "SQL Server Standard を使用する Windows Server" を選択した上で、最低価格の "t3a.xlarge" を選びました。

参考情報）AWS Pricing Calculator
https://calculator.aws/#/

ログイン後、SSMSでDBインスタンスに接続できることを確認しておきます。

SQL Server資格情報の登録

資格情報を作成するため、ALTER ANY DATABASE権限を持ったユーザが必要になりますが、デフォルトでOS管理者ユーザにsysadminロールが付与されているので、今回はOS管理者ユーザを使用します。

ちなみに、参考情報のURLの内容では、T-SQL文で資格情報を作成する手順しか無い（少なくともこの記事を作成してる時点では見つけられませんでした・・・）のですが、GUI操作でも作れることが検証の中でわかったので、その手順で記載しておきます。

SSMSを起動してOS管理者ユーザでインスタンスに接続後、セキュリティ＞資格情報＞(右クリックして) 新しい資格情報 の順にクリックします。

項目に以下の通り入力しておきます。

• 資格情報名：s3://<s3bucket-name>.<サービスエンドポイント>　※バケット作成時に確認したもの
• ID　　　　：S3 Access Key　※資格情報の種類を指定するため、必ずこの通り指定すること
• パスワード：<Access Key ID>:<Secret Access Key ID>　※コロン（：）で繋げて指定する

ここまで出来ればようやくバックアップ実行出来ます！

バックアップ実施

バックアップ実行のために、BACKUP DATABASE権限を持ったユーザが必要になるので、資格情報作成と同じようにOS管理者ユーザでバックアップを行います。

masterデータベースのバックアップを取得するので、データベース＞システムデータベース＞master＞(右クリックして) 新しいクエリ の順にクリックします。

表示されたエディタ上で、バックアップのT-SQL文を実行します。
今回はmasterデータベースのバックアップを、S3バケット上のmasterフォルダ配下にバックアップを配置するように指定しています。

BACKUP DATABASE master
TO URL = 's3://s3-bucket.s3.ap-northeast-1.amazonaws.com/master/master_backup_00.bak'
WITH FORMAT, COMPRESSION;

"▶実行"をクリックして、T-SQLを実行します。
実行後に、以下のようにメッセージにエラーが無ければOKです！

S3バケットの指定したフォルダ上に、バックアップファイルが存在していればバックアップ完了です！

追加）メンテナンスプランのバックアップジョブで実行する場合

手動でバックアップを実行する場合の手順を記載しましたが、実際に運用に乗せる場合は、スケジュール設定による定期実行や、バックアップ以外のジョブの前後処理を気にする等してあげないといけないですね。

もちろん、SSMSのメンテナンスプランを使ってバックアップジョブを組み込むことも出来るので、軽くそちらにも触れておきます。
※先に触れておくと、現時点ではファイルのクリーンアップジョブを使って、S3バケット上のバックアップファイルの削除処理を行うことが出来ないので注意です。。（S3バケットを対象に指定できないため）
なので、ファイルのクリーンアップはバケットのライフサイクルポリシーを使うか、自前でPowershellスクリプトを組むといったことをしないといけないですね。

メンテナンスプランはSQL Server Agentを使うので、サービスは起動させておきましょう。

メンテナンスプランとサブプランを作成し、データベースのバックアップタスクを用意します。タスクの設定は↓のような感じです。

作成後はちゃんとメンテナンスプランの内容を保存しておきましょう（意外と忘れる・・・）

SQL Server エージェント＞ジョブ＞作成したサブプラン名＞(右クリックして)ステップでジョブを開始　の順にクリックします。

ジョブが正常に終了したらOKです！
あとは、バケット上にファイルが存在するか確認しておきましょう。

最後に・・・

無事、SQL Serverの機能でS3にバックアップを直接配置することが出来ました。

本当はリストア出来るところまでしっかり検証したいところですが、一先ずアウトプット出来そうなバックアップ部分だけでまずはご容赦を・・・

今後、こんな感じで他にも自分の経験のアウトプットや、興味あることを検証した結果など技術情報を共有していきますので、これが誰かの参考になればいいなと思っております！！

ではまた次のストーリーで！！