1
/
5
Wantedly
About 株式会社AgilityShare
株式会社AgilityShare
https://agility-share.co.jp

宮城県

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

菅原 將来さんのプロフィール
菅原 將来
取締役
on 2024/02/20

最近、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にバックアップを直接配置することが出来ました。

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

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

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



株式会社AgilityShareでは一緒に働く仲間を募集しています
AWS インフラ クラウド データベース SQL Server
3 いいね！
3 いいね！
株式会社AgilityShareの会社情報

株式会社AgilityShare

私たち合同会社AgilityShareは、培ったITスキルと経験でお客様に必要とされる技術力を提供するSES事業、そして、オンライン受講可能な教育スクール「OnlineNetworks」を始めたとしたエンジニア教育事業を展開し、ITエンジニアの新たな可能性を広げていこうと活動を行っています！ 2022年3月にスタートしたばかりの会社ですが、フリーランスエンジニアとして経験豊富な代表が、エンジニアが幸せな人生を送れることを第一に考え設立しました。 働きやすさと努力する人への投資を惜しまない企業として、大きな成長を目指します。 ■主な事業内容■ ・SES事業 創業2年目ですが、プライム案件を多数保有しております。 特にインフラ系の案件に強みがあり、オンプレ・クラウド問わず開発から保守運用まで対応可能です。 ・エンジニア教育事業 ITエンジニア向け教育事業を展開しており、現在大きく分けて以下の通り業務を行っております。 ①実務特化型インフラエンジニアスキルアップスクール「OnlineNetworks」の運営 ②パートナー企業の教育サービス再販 セキュリティ資格講座（SecuriST, EC-Council）※提供元：グローバルセキュリティエキスパート株式会社 GCPオフィシャルトレーニング　※提供元：クラウドエース株式会社 ③教育機関の技術講師 ④新規教育サービスの開発 ⑤社内メンバー向け教育（新入社員研修など） ■エンジニアの可能性を広げる会社■ AgilityShareが目指すのは、「どこにいても自分の才能を発揮し、思い通りの働き方ができる会社」。本社は仙台ですが、エンジニアが地方にいながら仕事が続けられる環境を創出します。 さまざまな業界でIT人材が不足する現代だからこそ、エンジニアにとってどんな働き方が適切かしっかりと考え、個々人の努力が実る働き方を実現していきます。 ■今後の展望■ SES事業をメインに展開していますが、これは足がかりに過ぎません。メンバーの技術力や経験、そして企業としての信用力を積み上げたのちは、私たちの得意領域が存分に生かせる開発に専念していきたいと思います。インフラ系を中心とした受託開発や、セキュリティ関連の自社サービスを展開する予定です。 そのため、これから入社するエンジニアの皆さんにはキャリアを形成するにあたって多種多様な選択肢を提供できると考えています。SESで現場にいたい方はもちろん、さまざまな開発に携わりたい方、セキュリティエンジニアを目指したい方、オンラインスクールの講師をやってみたい方など、理想のエンジニア像を目指すことが可能です。 AgilityShareは、皆様が夢を叶え、生き生きと人生を歩むサポートをしていきます。

もっと見る

同じタグの記事

#クラウド
もっと見る

今週のランキング

ランキングをみる
菅原 將来さんにいいねを伝えよう
菅原 將来さんや会社があなたに興味を持つかも