By: Rajendra Gupta | Updated: 2022-06-30 | Comments (1) | Related: 1 | 2 | 3 | > SQL Server 2022
Problem
Historically, SQL Server database administrators create databases backup on disk and tape destinations. There was no direct support for backing up and restoring databases into the AWS S3 bucket. SQL Server 2012 introduced backup to Azure storage account, which improved in SQL Server 2016 with the added support for block blobs and page blobs.
Microsoft recently announced the public preview of SQL Server 2022, and it supports backup and restores of a SQL Server database using AWS S3 bucket storage. How do we configure and utilize the AWS S3 bucket for backup and restore into SQL Server 2022? Let's check it out in this tip.
Solution
SQL Server 2022 will be a significant upgrade to SQL Server, especially its cloud integration. It comes with the support of native backup to the AWS S3 bucket.
Many organizations prefer Amazon Web Services as their cloud infrastructure solution. However, SQL Server 2019 and below does not support storing backup to AWS S3 bucket directly using the BACKUP TO URL feature.
This is a multi-series article and Part 1 of the backup and restore series covers the following topics:
- Create AWS S3 bucket
- Create an IAM user-defined policy for the S3 bucket relevant permissions for creating a SQL database backup
- Create an IAM user and apply a custom policy for database backups
Requirements
It would help if you satisfied the below requirements to proceed with this tip.
- SQL Server 2022 Preview: Download the SQL Server 2022 CTP 2.0 preview from https://aka.ms/getsqlserver2022 and install the database engine. This article uses the SQL Server 2022 CTP 2.0 – 16.0.600.9 version below.
- AWS subscription: You need an AWS subscription for storing backups into an S3 bucket. You can leverage AWS's free tier account for learning purposes.
Create and Configure the AWS S3 bucket
You need to create and configure an AWS S3 bucket for storing the database backups. Login to the AWS console and navigate to the S3 service, as shown below.
Click on Create bucket. It launches the bucket configuration page that requires the following inputs.
- Bucket name: Specify a unique bucket name without spaces or uppercase letters. For this tip, I specify the bucket name as sqlserver2022.
- AWS region: Choose an AWS region from the drop-down regions list.
For this tip, I unchecked the option - Block all public access for simplified configuration of S3 bucket and accept the acknowledgment as shown below. Note: You should configure access for the S3 bucket using access control lists (ACLs) to avoid unauthorized access to the bucket and its content.
We can use the default values for the remaining configurations and click on Create bucket.
It creates the s3 bucket named sqlserver2022 in the Asia Pacific (Mumbai) ap-south-1 region. Usually, it would help if you chose the AWS region close to your data center.
Open the S3 bucket and create a new folder. Click on create folder and specify the folder name.
Click on create folder, and it shows the folder inside the s3 bucket.
We see that a few options are disabled. Put a check on the folder name, and it enables an option to copy S3 URI and URL.
It has the following values for the S3 bucket in my demo environment. We require the following information while performing the database backup and restore with the AWS S3 bucket.
- Copy S3 URI: s3://sqlserver2022/fullbackups/
- Copy URL: https://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/
AWS Policy for accessing the S3 bucket
IAM policies in the AWS IAM (Identity access management) define the user's permissions, or role can perform for the specific service. SQL Server 2022 requires ListBucket and PutObject permissions to take database backup directly into the S3 bucket.
- ListBucket: The ListBucket access returns a list of all buckets owned by the authenticated sender of the request.
- PutObject: The PutObject access allows you to write and adds objects into the bucket.
Navigate to IAM in AWS services and click on Policies. The policies tab shows you existing AWS policies and user-defined policies.
To create a new IAM policy, click on create policy. In the visual editor, choose the followings:
- Service: Choose the S3 service.
- Actions: Choose the ListBucket and PutObject as shown below.
- Resources: In the resources section, we can choose a specific or all resource for providing access. It is recommended to restrict access to a particular resource or S3 bucket. To limit the access for our S3 bucket, specify the bucket name in the amazon resource name(ARN) format – arn:aws:s3:::bucketname.
On the next page, review the policy and specify a policy name.
We need an AWS IAM user that has access to the S3 bucket for writing the backup copies into it. Navigate to IAM in AWS Services and click on Add users.
Specify an IAM user name and select AWS access type – Access key Programmatic access.
Click on Next: Permissions. In the set permissions, click on Attach existing policies directly.
We can choose an existing AWS or custom policy for the user. We already defined an IAM policy – sql2022backuppolicy. Therefore, filter the policy and decide to apply it to IAM users.
In the review section, we can see the [sqlbackup] user has assigned [sql2022backuppolicy].
Click on create user, and it gives the access key id and secret access. You need to save these credentials since it is the last time they are available to the user.
Next Steps
- Stay tuned for the next article on creating a database backup to the AWS S3 bucket in SQL Server 2022.
- Read SQL Server documentation on Microsoft docs.
- Follow up for existing tips on SQL Server 2022.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-06-30
