In this article, we will be learning to make a backup on the Amazon AWS S3 with SQL Server 2022.
SQL Server 2022 is the most up -to -date version of SQL Server, especially cloud integrations. The AWS S3 backup comes with this version.
For now, it is important to remember that the SQL Server 2022 Public Preview is also.
Many institutions / organizations / enterprises use cloud infrastructure preferences on many issues such as growing data dimensions and costs.
In fact, they are now using multi -cloud structure and multiple cloud infrastructure. The reason for this is the benefits and costs of each cloud provider.
In this respect, we could only make a backup process to Azure Blob using SQL Server 2016 or SQL Server 2019, so these versions do not support AWS S3 backup.
We need an AWS S3 account where we will do backup and restore.
On the AWS S3 Bucket, we need to define IAM (Identity and Access Management – AWS services and resources under which conditions).
We need to create a CRENTENTİAL over SQL Server 2022.
The first requirement of the AWS S3 Bucket; We are logging out on the AWS console and go to the Amazon S3 service and form a new “bucket”.
After saying “Create Bucket”, the screen that will come across is as follows. Here, the bucket name is the bucket name we will use, and Region is the region in which Bucket will be located.
In our example, I create Bucket in the Us East-Virginia region called co-sqlserver2022.
In order to create a simple bucket on the AWS S3 side and not to have authorization problems, I remove the “Block All Public Access” sign and give confirmation information as shown on the screen below.
With this process, I declare that I accept all kinds of access to Bucket. Do not do this in live environments! You can configure your S3 access using ACL.
Without changing other adjustments in the page, I create Bucket by saying “Create Bucket”.
After the bucket creation process is completed, you can see it in the Bucket list. Generally, it allows you to create cloud providers by making the nearest location proposal when creating such services.
I created the Bucket called co-sqlserver2022 in the US-EAST area.
Bucket has been formed, now next time it creates a folder in the bukeket. These folders may include naming of the structure you will use in your organization.
I will be giving the name sqlbackup as a prefix. Because I will create and use full, DIFF, log folders with different folders.
We have realized the Bucket definitions that we will need for our demo environment.
Again, let’s get the S3 URI and Copy Url information, which will be useful in the demo, as in the picture below.
S3 URL : s3://co-sqlserver2022/sqlbackup/FULL/
URL : https://co-sqlserver2022.s3.amazonaws.com/sqlbackup/FULL/
Now it’s time for AWS IAM configuration. We set up the bucket side, we will do the IAM configuration.
The SQL Server 2022 requires Listbucket and Putobject permits to make the database backup directly on S3.
To set the IAM principle;
We’re going to IAM Policy from AWS Portal.
We create a new policy for SQLServer 2022 from the Create Policy. After saying Create Policy here, I select S3 from the Service.
In the Access Level section, I select the listbucket, Putobject and Putbucketrequestpayment in Write, getbucketrequestpayment.
In the Resources section, I select “All Resources” and continue. Here, you can restrict access from the Request Conditions section.
The last configuration on the IAM side is as follows.
You can enter Tag information for the Policy on the next Tag screen. It is not compulsory, but in the policy or in pricing processes, Tag usage will be beneficial to distinguish your transactions.
For this reason, I suggest you fill. We have created a policy, now it has come to the creation of a user to access with the policy we have created.
Again, we add the user from the Users section in the Policy screen.
I create a user named co-sqlbackupuser.
When we get to the authorization process, we have just created the policy we have created.
After completing the user creation and policy appointment, there is an Access Key and Secret Access Key information that we will use for access.
We have completed our Bucket transactions on the AWS S3 side. At the rest of the SQL Server, we will be creating the Credential using the information we obtain here.
The code block that we need for the Credential is as follows.
CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>]
IDENTITY = ‘S3 Access Key’
,SECRET = ‘<AccessKeyID>:<SecretKeyID>’;
Let us define the code block above with the information we obtain on the AWS side. Our S3 information in the definition is also as follows.
Bucket Name: co-sqlserver2022
Secret Key: AKIAXQYUDTPT6QHRJCI6
Access Key: gR5agEB0gP/OV69PUC0hkiwBiGBjJgfZat2/5nOD
S3 URL: s3://co-sqlserver2022/sqlbackup/Full/
Credential tanımlamak için güncel bilgiler ile aşağıdaki sql kodunu çalıştırıyoruz.
CREATE CREDENTIAL [s3://co-sqlserver2022.s3.us-east-1.amazonaws.com/sqlbackup/FULL]
IDENTITY = ‘S3 Access Key’
,SECRET = ‘AKIAXQYUDTPT6QHRJCI6:gR5agEB0gP/OV69PUC0hkiwBiGBjJgfZat2/5nOD’;
We will use the following SQL code block to get backup of DMC database.
BACKUP DATABASE DMC
TO URL = ‘s3://co-sqlserver2022.s3.us-east-1.amazonaws.com/sqlbackup/FULL/DMC.bak’
,STATS = 10
As can be seen from the picture above, we have taken the backup of the DMC database to the AWS S3 Bucket, where we created a backup.
The following screenshot shows the S3 content via the AWS portal.