This blog I am going to help you on moving your SQL backups to AWS S3.
Below are the steps we are going through in this blog.
- Creating S3 bucket in your Aws account.
- Creating IAM accounts in AWS security.
- Installing AWS CLI in your database server.
- Moving backups from On-Premise to S3 using PowerShell.
- Moving backups from AWS EC2 to S3 using PowerShell.
Creating S3 bucket in
your Aws account
- · Login to AWS console.
- · Click on storage service S3.
- · Click on Create bucket.
- · Fill bucket name, region where you want to place this storage and click next.
- · Set properties of the bucket. Enable versioning property and click on next.
- · Give read and write permissions to the user or owner of the bucket and click next.
- · Review the bucket details and click on create bucket.
- · New S3 bucket has been created.
Creating IAM accounts
in AWS security
- · Click on IAM service in security.
- · Select Users on left pane of IAM dashboard.
- · Click add user.
- · Provide username, enable programmatic access and click on next.
- · Add user to Admin group and click on next.
- · Review user details and click on create user.
- · This will create user and provide Access Key ID and Secret Access Key. Make sure you make a note of them for future use on AWS CLI access.
Installing AWS CLI in
your database server
Download and install AWS CLI on database server where backup
files are located.
Moving backups from
On-Premise to S3 using PowerShell
Step 1:
Configure AWS CLI in database backup server.
Open command prompt and configure AWS CLI as below
Provide
Access Key ID and Secret Access Key which you saved while creating IAM user.
Step 2:
Save the below PowerShell script at C:/Scripts/S3_copy.ps1
Provide bucket name and Backup location in the script.
import-Module -name AWSPowerShell
$BucketName = "aws-s3-sql-backup"
$s3Directory = "
C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup"
$concurrentLimit = 50
$inProgressFiles = @()
foreach ($i
in Get-ChildItem
$s3Directory)
{
# Write the
file to S3 and add the filename to a collection.
# Write-S3Object
-BucketName $BucketName -Key $i.Name -File $i.FullName
if (!(Get-S3Object -BucketName
$BucketName -Key
$i.Name))
{
Write-S3Object
-BucketName $BucketName
-Key $i.Name -File $i.FullName
}
$inProgressFiles
+= $i.Name
# Wait to
continue iterating through files if there are too many concurrent uploads
while($inProgressFiles.Count
-gt $concurrentLimit)
{
Write-Host "Before: "$($inProgressFiles.Count)
# Reassign
the array by excluding files that have completed the upload to S3.
$inProgressFiles
= @($inProgressFiles
| ? { @(get-s3object -BucketName
$BucketName -Key
$_).Count
-eq 0 })
Write-Host "After: "$($inProgressFiles.Count)
Start-Sleep
-s 1
}
Start-Sleep -s 1
}
Step 3:
- Open your SSMS and create a backup job with below two steps.
- Job step 1 - Takes backups of user DB’s.
- Job step 2 - Copy recent backups from database server backup location to AWS S3 bucket.
Properties of Step 2:
This will kick the PowerShell script placed in C drive.
Please check your S3 bucket once job gets
completed.
Note: Before running
the PowerShell script make sure you are remotely signed using below execution
policy in your database server.
Moving backups from
AWS EC2 to S3
You can use the same script in AWS EC2 machine
of SQL server and copy backup files to S3.
It was a nice article on AWS backup S3 and clearly shows the importance of backup in cloud computing. Thanks for sharing very useful content.
ReplyDeleteIt’s really interesting content and nice post AWS online Course
ReplyDeleteIt is really a great work and the way in which you are sharing the knowledge is excellent. aws training in omr | aws training in velachery | best aws training center in chennai
ReplyDeleteIf you are running SQL server on EC2 and looking to automate backup, PIT recovery, Clone, DB refresh, table recovery from EBS snapshots, try Nimesa.
ReplyDelete#aws, #sqldeveloper, #sqldba, #nimesa
http://nimesa.io/blogs/protecting-the-sql-d
Very informative blog and useful article thank you for sharing with us , keep posting learn more about aws with cloud computing
ReplyDeleteAWS Course
AWS Online Training
Very informative blog and useful article thank you for sharing with us , keep posting learn more about AWS with cloud computing.
ReplyDeleteCreate and Configure Amazon S3 Bucket
https://gigaren.com/how-to-fix-when-the-windows-10-stops-working-after-update/
5 Best Apps to Watch Soccer on Android
Customize Lid Close Action and Settings on Windows 10
GIGAREN