In this article, we explore the use of manual snapshots in AWS RDS SQL Server.
Introduction
AWS RDS is a cloud-based managed service from Amazon Web Services (AWS). Database backups are a critical requirement for ensuring database availability. Usually, in a traditional database, database professionals configure SQL agent jobs and schedule full, transaction log backups as per application recovery objective.
AWS automatically takes database backup and allows point-in-time recovery for your databases. It takes the backup during the specified backup window. RDS does not take database backup. Instead, it takes a storage volume snapshot. As soon as you create an RDS instance, it takes a full snapshot of the underlying storage. You can find the automated backup in the RDS dashboard, as shown below.
After a full snapshot, it takes incremental snapshots. Incremental snapshots take backup for the data changed after the last snapshot. We can configure the retention period for these automated RDS backups from 0 to 35 days. The default backup retention also depends upon the method you use for configuration.
- Default backup retention 7 days if you create DB from AWS web console
- Default backup retention 1 day if you use AWS command-line tools (CLI) or AWS API
- In case you specify 0 in backup retention, it disabled the automated snapshot
The requirements for the manual snapshots in AWS RDS SQL Server
Apart from the automated snapshots, we can also take manual snapshots in the RDS instance. Think of a few questions-
- Why do we require manual snapshots?
- What are the automated and manual snapshots?
In this article, we find out answers to these questions.
Backup Retention
Suppose you have a critical database that holds financial transactions in the AWS RDS SQL Server. Due to audit and compliance requirements, you need to hold at least 1-year old backups. As we know, automated backups’ maximum retention period is 35 days, so it does not satisfy our requirements. You can take bi-weekly or monthly manual snapshots for a more extended retention period. You can note here that we can have a maximum of 50 manual DB snapshots in an AWS account. For longer retention, you can either contact AWS support or design the backup policy accordingly.
Manual Snapshots before database deletion
We can stop an AWS RDS SQL Server instance if it is not in use to avoid the cost. If we stop the RDS instance, it can be in stopped state for a maximum of 7 days, and it comes automatically online after that period. Suppose you created an instance for development work. Once your development work is completed, you plan to terminate the instance. You might want the same instance for your future deployments.
Once we delete an instance, RDS gives you an option to take a manual snapshot. It helps you to avoid the cost of a running RDS instance, and you can restore the instance later from the manual snapshot whenever required.
Cross-Region backups
We can use the automated backups to restore a database in the same AWS region where your database belongs. For example, if your RDS database is in the Asia Pacific (Mumbai), you cannot restore the automated backup in the US-east (Ohio). You might create a disaster recovery environment in another AWS region. In this case, you can utilize manual snapshots by copying it to the required region and restore the RDS instance in that region.
Take manual snapshots for AWS RDS SQL Server
In this article, I have the following [SQLShackDemo] RDS instance.
- Instance name: SQLShackDemo
- DB engine: SQL server express edition
- Region: ap-south-1b
- Status: Available
- size: db.t2.micro
You can refer to AWS RDS articles and prepare an instance to proceed further in this article.
To take a manual snapshot, select the DB instance in the RDS dashboard and navigate to Actions-> Take snapshot.
Specify an appropriate name for the DB snapshot. You can put a familiar name so that you can identify the purpose of this manual snapshot later. You cannot use a unique character in the snapshot name.
It takes a manual snapshot. The snapshot time depends upon the instance size.
Once the snapshot gets completed, the snapshot is available. You can see the snapshot creation time, and the DB instance created time in the dashboard.
You can click on the snapshot name to get more details such as DB engine, storage, VPC, port, snapshot type.
By default, AWS takes an unencrypted manual snapshot. It is best to use encryption to encrypt your RDS snapshots.
Encrypt AWS RDS SQL Server manual snapshots
To convert your existing encrypted manual snapshots to encrypted snapshots, select the snapshot, and navigate to Actions -> Copy Snapshot.
In the Copy snapshot, specify a new snapshot identifier. We can copy this snapshot to a different region as well. By default, it shows the existing RDS instance region in the designated region.
Scroll down and enable the encryption. You also need to select the master key for encryption. We use the default AWS/rds encryption key in this article.
It encrypts the manual snapshot. You can distinguish an encrypted snapshot from using the Encrypted=Yes value from the manual snapshots.
Take manual snapshot while terminating RDS Instance
As highlighted earlier, AWS gives the option to take a manual backup before instance termination. Select the instance and go to Actions-> Delete.
It automatically selects the option – Create final snapshots? Specify a snapshot name and type delete me to terminate the instance.
It starts creating a final manual snapshot.
This final snapshot is also listed in the manual snapshots, as shown below.
Restoring manual snapshots
We deleted the AWS RDS SQL Server in the previous step and tool a manual snapshot. We have three snapshots available, as shown in the above image.
Suppose we want to restore the RDS instance from the first manual snapshot. In the RDS dashboard, select the snapshot from which we want to restore the instance.
In the restore snapshot, select the following options. By default, it shows the configuration options that we used in the SQL instance for the snapshot.
- SQL Server engine. We can change the SQL instance; however, we cannot downgrade a SQL instance. For example, if we had a snapshot for enterprise RDS instance, it cannot be restored as standard RDS
- License model (by default, it is license included)
- Verify manual snapshot name
- DB instance identifier (for this restore, I specified SQLShackDemoRestore)
You can verify the remaining configurations like security groups, option groups, VPC, public accessibility, DB instance, and storage size similar to a new RDS instance.
Once your configuration completes, click on Restore DB instance as shown below.
It starts new AWS RDS SQL Server instance creation using the manual snapshot specified.
Share the AWS RDS SQL Server manual snapshot with another AWS account
You can also share the manual snapshot with another authorized AWS account.
- If you share an unencrypted manual snapshot, authorized AWS users in that account can restore the snapshot directly. It does not require copying the snapshot first and then restore
- For an encrypted snapshot sharing, you cannot restore it directly because RDS does not allow a shared and encrypted snapshot restore. You can create a copy of a snapshot and restore it. You need to share the AWS Key Management Service (AWS KMS) encryption key that was used to encrypt the RDS. However, we cannot share the snapshot encrypted using the default KMS encryption, as we explored in this article. You can refer to AWS docs for detailed information on this
- We cannot share the snapshot as the public. We can only share a snapshot of a specific AWS account
- If we use Transparent Data Encryption (TDE) encryption, we cannot share the snapshot
To share the snapshot, select the snapshot and click on Share Snapshot.
Specify the AWS account id with whom you want to share this manual snapshot.
Deleting manual snapshots in AWS RDS SQL Server
You should remove the snapshots when you do not require them. It saves your storage cost in the AWS account. You can delete the snapshot without terminating the RDS instance. In the AWS web console, go to RDS-> Snapshots and select the snapshot to delete.
Confirm to proceed with deleting this manual snapshot.
You get a confirmation prompt after it deletes the specified snapshot.
Conclusion
In this article, we explored the requirement and importance of manual snapshots in the AWS RDS SQL Server instance. You should analyze your requirements, decide the snapshot frequency, and configure. You can take a manual snapshot before doing any significant activity on the database to recover your instance in case of any accidental damages.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023