【技術記事】SQL Serverの機能を使ってDBバックアップをAmazon S3に保管してみる

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

SQL Server 2022からは、SQL Server自身の機能でバックアップ先として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を使うので、サービスは起動させておきましょう。