Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@yuurin-Data-Engineer

SSMSからAzureStorageへアップロード自動化

Posted at

1.オンプレミス環境のSQL ServerのデータベースバックアップをAzure上へバックアップする為の手順

Azure Blob Storageはバックアップデータの格納に適したストレージサービス。
Azure Blob Storageの中で、よりセキュアで安価なShared Access Signature (SAS)とブロック BLOBを使用した手順です。

＜参考＞

####一部抜粋####
Microsoft Azure Blob Storage サービスに格納できる BLOB には、ブロックとページの 2 種類の BLOB があります。 SQL Server 2016 以降の場合は、ブロック BLOB を使用することをお勧めします。
資格情報内でストレージ キーが使用されている場合は、ページ BLOB が使用されます。Shared Access Signature が使用されている場合は、ブロック BLOB が使用されます。
・BLOB アクセスを承認する方法としては、ストレージ キーよりも Shared Access Signature の方が安全です。
・ブロック BLOB はページ BLOB よりも安価です。

①ストレージ アカウントの作成

ストレージ アカウントの作成はAzure Portal上で行うことが可能で、以下が手順です。

  1. Azure Portalにログインし、「ストレージアカウント」メニューを選択。
  2. 新しいストレージアカウントを作成するため、「作成」ボタンを押す。
  3. リソースグループ、ストレージアカウント名を指定し、「次：ネットワーク」ボタンを押す。
    ※一般的に、Standard を選択いただければ問題ございませんが、必要に応じてpremiumの選択も可能です。
    image001.png
    ストレージのパフォーマンスについての詳細は以下公開情報よりご確認いただけますと幸いです。

＜参考＞ストレージ アカウントの概要 - Azure Storage | Microsoft Docs

  1. 「次：データ保護」「次：タグ」と進み「確認および作成」を押す。
  2. 「作成」を押す。

②Azure BLOB コンテナーの作成

ストレージ アカウントを作成した後、コンテナーを作成。コンテナーを作成するには、次の手順を実行。
1.Azure portal を開く。
2.ストレージ アカウントに移動。
3.ストレージ アカウントを選択し、 [データストレージ][コンテナー] を押す。
4.[ + コンテナー ] を選択して新しいコンテナーを追加。
5.コンテナーの名前を入力し、指定したコンテナー名をメモ。 この情報は「 T-SQL ステートメントの URL (バックアップ ファイルのパス) 」内で使用される。

③Azure Storage の Shared Acciess Signeture (SAS) 作成

Azure Storageに制限付きのアクセス権を付与する為のShared Access Signatureを作成。

  1. Azure 管理ポータル - ストレージ アカウント - SAS を作成したい ストレージ アカウントを選択。

  2. Shared Access Signature を選択。

  3. 開始日時、終了日時を指定、また、ローカル時間 (JST) を指定したい場合は、UTC +9:00 を指定後、"SAS と接続文字列を生成する" ボタンを押す。
    image002.png

  4. SAS トークンが作成されたことを確認。
    image003.png

④データベースのバックアップを作成
Shared Access Signature を作成後、Azure Storage内にバックアップを取得。

  1. SQL Server Management Studio を起動し、SQL Server インスタンスに管理者権限が付与されたログイン (sa など) でログイン。

  2. 資格情報を作成。

CREATE CREDENTIAL [HTTP[s]://ACCOUNTNAME.blob.core.windows.net/< CONTAINER>]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET='sv=###########################################################'
go

※ CREATE CREDENTIAL コマンドで Shared Access Signature を指定する際、先頭の "?" は取り除く。

    1. で作成した資格情報を使用し、Azure Storage 上の Blob にバックアップ ファイルを作成。
BACKUP DATABASE db1 to URL='HTTP[s]://ACCOUNTNAME.blob.core.windows.net/< CONTAINER>/<FILENAME.bak>'
go

[補足]
Shared Access Signatureを使用し、Azure Storage 上の Blob にバックアップを作成した場合、ファイルは Block Blob としてバックアップが作成される。COMPRESSIONオプションにてバックアップファイルの圧縮を設定することも可能。
＜参考＞SQL Server Backup to URLにてサポートされている引数

2.オンプレミス環境のバックアップファイルの容量を削減できる手段を確認し回答

圧縮バックアップを作成することにより、バックアップをファイルの容量を削減することが可能。
以下、圧縮バックアップを取得する手順

1.バックアップを取得するデータベース右クリックし、[タスク]-[バックアップ]を押す。
image004.png

  1. [バックアップオプション]を押しバックアップの圧縮の設定(P): を”バックアップを圧縮する”に変更しOKを押しバックアップファイルが作成されていることを確認。
    image005.png

＜注意点＞
圧縮を指定せずバックアップファイルを取得されている場合、バックアップ ファイルを違うファイルにするか、既存のバックアップファイルを削除頂き、再度バックアップの圧縮を行って頂く必要がある。バックアップ アップファイルに対して圧縮する・しないの違いによって、復元する際にデータの不整合は発生しない。
＜参考＞バックアップ圧縮の制限について詳細

####抜粋####
圧縮されたバックアップと圧縮されていないバックアップを 1 つのメディア セット内に共存させることはできません。

アップロードできない例

ブロック BLOB を使ってサポートされる１ファイルの最大サイズは、約 200 GB (50,000 ブロック * 4MB MAXTRANSFERSIZE) となっている。ブロック BLOB はストライピングが可能なので、複数URLを追加することでデータを分割し分散してバックアップを取得することが可能。 ※複数URLを指定しても同様のエラーが出てしまう場合は、さらにURLを追加し再度バックアップの取得を試してみてください。

＜解決の例の補足＞
・WITH COMPRESSIONを指定することで、バックアップファイルの圧縮をすることが可能で、ストレージ コストとストレージのトランザクションコストを最小限に抑えることが可能。 バックアップ取得時間の短縮が可能
・ブロック BLOB で許可される 50,000 ブロックの使用を最適化する為、MAXTRANSFERSIZE と BLOCKSIZE を以下最大値に設定もおすすめ。

MAXTRANSFERSIZE=4194304、BLOCKSIZE=65536

＜参考＞
URL へのバックアップに関するベスト プラクティスとトラブルシューティング - SQL Server | Microsoft Docs

####抜粋####
I/O デバイス エラーのため、要求は実行されませんでした。
大規模なデータベースをバックアップする場合は、COMPRESSION、MAXTRANSFERSIZE、BLOCKSIZE、および複数の URL 引数を検討してください。 「Backing up a VLDB to Azure Blob Storage」(VLDB を Azure Blob Storage にバックアップする) を参照してください
エラー:
Msg 3202, Level 16, State 1, Line 1
Write on https://mystorage.blob.core.windows.net/mycontainer/TestDbBackupSetNumber2_0.bak failed:
1117(The request could not be performed because of an I/O device error.)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

＜解決の例＞

BACKUP DATABASE TestDb
TO URL = 'https://mystorage.blob.core.windows.net/mycontainer/TestDbBackupSetNumber2_0.bak',
URL = 'https://mystorage.blob.core.windows.net/mycontainer/TestDbBackupSetNumber2_1.bak',
URL = 'https://mystorage.blob.core.windows.net/mycontainer/TestDbBackupSetNumber2_2.bak'
WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536;

Azure Blob Storageの複数URLにバックアップを取得した際の復元手順

■Shared Access Signatureを使用しブロックBLOBにて取得したバックアップファイルの復元手順 
RESTORE DATABASE Databasename to
URL='HTTP[s]://ACCOUNTNAME.blob.core.windows.net/< CONTAINER>/<FILENAME.bak>' ,
URL ='HTTP[s]://ACCOUNTNAME.blob.core.windows.net/< CONTAINER>/<FILENAME.bak_1>' ,
'HTTP[s]://ACCOUNTNAME.blob.core.windows.net/< CONTAINER>/<FILENAME.bak_2>'
go

■アクセスキーを使用しページBLOBにて取得したバックアップファイルの復元手順
※WITH CREDENTIALにてアクセスキーを指定する（@@@＝アクセスキー）

RESTORE DATABASE Databasename to URL='HTTP[s]://ACCOUNTNAME.blob.core.windows.net/< CONTAINER>/<FILENAME.bak>' WITH CREDENTIAL = '@@@@'
go

Azure Blob Storageにバックアップの定期的なスケジューリング

メンテナンスプランのバックアップタスクでは WITH CREDENTIAL オプション付きでバックアップが実行される為、アクセスキーを使用したページBLOBにてバックアップを取得する必要がある。

①アクセス キーを使用した資格情報の作成手順

  1. Azure 管理ポータルから、バックアップを作成した Azure Storage アカウントの アクセス キーを確認。
    ATT22918 1.jpg

  2. SQL Server Management Studio を起動し、SQL Server インスタンスに管理者権限が付与されたログイン (sa など) でログイン。

  3. 資格情報を作成。
    CREDENTIALの＠＠＠＠には任意の値を入れる。例) testuser

use master
go
CREATE CREDENTIAL ＠＠＠＠ WITH IDENTITY='ACCOUNTNAME',
SECRET='<Access Key>'
go

②バックアップを手動で取得する手順

1)で作成した資格情報を使用し、Azure Storage 上の Blob にバックアップ ファイルを作成。

BACKUP DATABASE Databasename to URL='HTTP[s]://ACCOUNTNAME.blob.core.windows.net/< CONTAINER>/<FILENAME.bak>'
WITH CREDENTIAL = '@@@@'
go

③メンテナンスプランにてバックアップを取得する手順

実際のメンテナンスプラン作成時は、要件に合わせてスケジュールを選択する。

  1. メンテナンスプランを右クリックしメンテナンスプランウィザードを起動
    ATT69167 2.jpg

2)[プランプロパティを選択]画面のスケジュール配下の”変更”よりメンテナンスプランのスケジュールを設定可能。
ATT27071 3.jpg

  1. スケジュールを作成し”OK”を押す 。
    スケジュールの設定は、要件に合わせて任意で設定。
    例）「頻度を[毎月]に設定し、頻度を[間隔] 1 ヵ月に１度にに設定する」など
    ATT14063 4.jpg

4)[メンテナンスタスクの選択]画面にて、実行したいタスクを選択。
今回、データベースのバックアップ(完全)を選択している。
ATT21066 5.jpg

5)[データベースのバックアップ(完全)タスクの定義]画面にて取得するデータベースやバックアップ先を指定。
バックアップ先をURLに選択しタブの[バックアップ先]を押す。
ATT42192 6.jpg

6)先ほど①の手順にて作成したSQL 資格情報をプルダウンで指定すると自動的に URL が埋め込まれるので、コンテナ名のみ入力。
ATT87871 7.jpg

7)[オプション]タブを押し[バックアップを圧縮する]を選択し”次へ”を押す。
※ストレージ コストとストレージのトランザクションコストを最小限に抑えることが可能で、バックアップを圧縮を推奨されている。
ATT88853 8.jpg

8)ウィザードの完了まで進め”完了”を押しメンテナンスプランの作成は完了。
ATT31894 9.jpg

9)作成したメンテナンスプランを手動で実行する場合は作成したメンテナンスプランを右クリックし”実行”を押す
ATT45103 10.jpg
メンテナンスプランを作成する方法についての詳細は以下公開情報も合わせてご確認いただければと思います。
＜参考＞メンテナンス プラン ウィザードの使用- SQL Server | Microsoft Docs

T-SQLにてバックアップを取得した際に作成されるlogについて

バックアップを取得した際に、別途Logが生成されない。
メンテナンスプランを使用してバックアップを取得した際はLogの生成をすることが可能。

２）取得したバックアップファイルをPower Shellにて一括削除

以下コマンドにて、Azure ストレージのバックアップのクリーンアップを実施することが可能。
BLOBコンテナー上の「変更日時」が１日以上前のファイルはすべて削除するコマンドとなっているので、ご用件に沿って適宜変更が必要。

・認証キャッシュのクリア

Clear-AzContext -Force

・Azure へ接続

Connect-AzAccount -Identity
Write-Host "Login to Azure"

・最初にバックアップ先BLOBコンテナー、SQL DBのバックアップ先BLOBコンテナーのストレージ アカウント名、 ストレージ アカウントに紐づくリソース グループを引数に設定。

$container="<CONTAINER>" 
$StorageAccountName="< ACCOUNTNAME> " 
$resourceGroupName="<RESOURCEGROUPNAME>"
$accountKeys = Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -Name $StorageAccountName
$StorageAccountKey = $accountKeys[0].Value
Write-Host $StorageAccountKey
$context = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $StorageAccountKey
Write-Host $context 
$filelist = Get-AzStorageBlob -Container $container -Context $context

・本スクリプト実行時に取得される（Get-Date）日時と比べて、BLOBコンテナー上の「変更日時」が１日以上前のファイルはすべて削除するコマンド。AddMinutes、AddHoursで日時単位ではなく時間単位での削除も可能。
・Get-Dateにで取得される時刻はJST表記、BLOBコンテナー上での「LastModified.DateTime」はUTC表記であるため「ToUniversalTime()」にてUTC表記に平仄を揃える。

foreach ($file in $filelist | Where-Object {$_.LastModified.DateTime -lt ((Get-Date).ToUniversalTime().AddDays(-1))})  
{
    $removefile = $file.Name 
    if($removefile -ne $null) 
    {
         Write-Host "Removing file $removefile" 
         Remove-AzStorageBlob -Blob $removeFile -Container $container -Context $context 
    } 
}

AzureStorageExploreの指定のフォルダを指定

フォルダ構成は以下になります。
例えば「/202204XX/」配下のファイルを操作するようなコマンド

Remove-AzStorageBlobコマンドを実施する際に-blobにて該当のpathを指定することが可能。

Remove-AzStorageBlob -blob 202204XX/backupfile.bak -Container $container -Context $context

以上。

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?