Configure extra storage volumes with Amazon RDS for SQL Server

Amazon Relational Database Service (Amazon RDS) for SQL Server situations historically use a single Amazon Elastic Block Retailer (Amazon EBS) quantity to retailer database information. With the introduction of the extra storage quantity function, now you can connect as much as three extra storage volumes to your Amazon RDS for SQL Server situations. By utilizing this function, you possibly can distribute your knowledge and log information throughout a number of volumes. This enhancement gives extra granular management over storage configuration and efficiency optimization.

On this publish, you’ll find totally different situations for working with the extra storage quantity function.

Answer overview

The next structure diagram illustrates the extra storage quantity configuration, exhibiting the usage of main and secondary hosts, every with two volumes, in a multi-Availability Zone (multi-AZ) configuration.

On this publish, you’ll be taught in regards to the following situations:

• Including a brand new storage quantity
• Scaling an current storage quantity
• Restoring a database on a further storage quantity
• Deleting a storage quantity

This answer requires new AWS sources that may incur prices in your account. See AWS Pricing for extra info.

Conditions

We assume you will have familiarity with navigating the AWS Administration Console. For this publish, you additionally want the next sources and companies enabled in your AWS account:

Observe: For present limitations on the extra storage quantity function, see Working with SQL Server storage.

Including a brand new storage quantity

You possibly can add as much as three extra storage volumes both throughout occasion creation or after an occasion has been deployed. Extra storage volumes complement the default storage quantity. The default quantity makes use of D: as its drive letter. Extra storage volumes help two storage sorts: Basic Goal SSD (gp3) and Provisioned IOPS (io2), so you possibly can select the optimum storage efficiency in your database workloads. Quantity names are mapped to home windows disk letters as follows:

• rdsdbdata2 – H: drive
• rdsdbdata3 – I: drive
• rdsdbdata4 – J: drive

Add a storage quantity when creating an occasion

So as to add a further storage quantity throughout an occasion creation, full the next steps:

1. See Create and Hook up with a Microsoft SQL Server Database with Amazon RDS for detailed directions to create an RDS for SQL Server occasion.
2. For Storage, choose the suitable storage sort. That is your D: quantity.
3. For Extra storage volumes – non-obligatory, Select Add storage quantity.
   
   
4. Enter or choose applicable values for Quantity identify, Storage sort, Allotted storage, Provisioned IOPS, and Storage throughput.
   
   

Including a storage quantity to an current occasion

Full these steps so as to add storage volumes to an current RDS for SQL Server occasion:

1. On the Amazon RDS console, select Databases.
2. For DB identifier, choose your RDS for SQL Server occasion. On this instance, I selected rds-asv-demo.
3. Select Configuration.
4. Select Add storage quantity.
5. Choose applicable values for Quantity identify, Storage sort, Allotted storage, Provisioned IOPS, and Storage throughput.
6. For Scheduling, choose Apply instantly and select Submit.

Alternatively, use the next AWS Command Line Interface (AWS CLI) so as to add a brand new storage quantity to an current Amazon RDS for SQL Server occasion:

Substitute , , , , and with applicable values.

For this instance, for , use rdsdbdata2, rdsdbdata3, or rdsdbdata4. For use gp3 or io2.

aws rds-asv modify-db-instance 
  --db-instance-identifier  --region  
  --additional-storage-volumes '[{"VolumeName":"","StorageType":"","AllocatedStorage":}]' 
  --apply-immediately

Scaling a further storage quantity

To scale a further storage quantity on an current RDS for SQL Server occasion, full the next steps. Whereas this operation usually runs with out downtime, we suggest performing it throughout low-activity durations.

1. On the Amazon RDS console, select Databases.
2. For DB identifier, choose your database.
3. Select Configuration.
4. Beneath Extra storage volumes, choose the storage quantity to scale and select Modify.
5. For Modify storage quantity, select applicable values for Storage sort, Allotted storage, Provisioned IOPS, and Storage throughput.
6. For Scheduling, choose an applicable worth and select Submit.

To scale an current storage quantity, utilizing the AWS CLI:.

Substitute , and with applicable values.

Within the following instance, we scale the IOPS of rdsdbdata2 quantity to 4000.

aws rds-asv modify-db-instance 
  --db-instance-identifier  --region  
  --additional-storage-volumes '[{"VolumeName":"rdsdbdata2","IOPS": 4000}]' 
  --apply-immediately

Restoring a database on a further storage quantity

To revive a database on a further storage quantity, carry out the next steps.

1. Hook up with your RDS for SQL Server occasion in SQL Server Administration Studio.
2. Choose New Question.
3. Use the next instance command to revive a database named AdventureWorks2019 on extra storage volumes. You possibly can obtain a pattern backup from AdventureWorks2019. Substitute with an applicable worth.

   See Importing and exporting SQL Server databases utilizing native backup and restore for directions to carry out a database restore into an RDS for SQL Server occasion. The command assumes you will have configured extra storage volumes named H and I in your RDS for SQL Server occasion.

   exec msdb.dbo.rds_restore_database    
       @restore_db_name="AdventureWorks2019",
       @s3_arn_to_restore_from='arn:aws:s3:::/AdventureWorks2019.bak',
       @data_file_volume="H:",
       @log_file_volume="I:"

4. Test the standing of the restore utilizing the next command. Substitute with applicable values.

exec msdb.dbo.rds_task_status @task_id=;

5. Anticipate the restore to finish efficiently. Use the next command to test the situation of the bodily information.

   Use AdventureWorks2019
   GO
   choose * from sys.database_files

   

Deleting a further storage quantity

You possibly can solely delete a further storage quantity in your occasion when it’s not in use and you’ll’t delete the D: drive. To delete a further storage quantity on an current RDS for SQL Server occasion, full the next steps:

1. On the Amazon RDS console, select Databases.
2. For DB identifier, choose your database.
3. Select Configuration.
4. Beneath Extra storage volumes, choose the storage quantity to delete and select Delete.
5. On the Delete storage quantity pop up window, enter delete and select Delete.
   
   

Alternatively, use the next AWS CLI command to delete an current storage quantity on the RDS for SQL Server occasion:

Substitute , and with applicable values.

aws rds-asv modify-db-instance 
  --db-instance-identifier  --region   
  --additional-storage-volumes '[{"VolumeName":"", "SetForDelete": true}]' 
  --apply-immediately

Clear up

To keep away from incurring pointless prices, take away any volumes that you simply’ve created however not want by following the directions within the previous part.

Conclusion

On this publish, we launched extra storage quantity options for Amazon RDS for SQL Server situations and demonstrated sensible implementation situations. We walked via the method of provisioning, deleting and managing extra storage volumes, and confirmed you restore databases to those volumes to optimize your storage structure.Extra storage volumes present flexibility to prepare your knowledge by workload sort, enhance efficiency via devoted IOPS allocation, and scale your storage independently whereas sustaining excessive availability and sturdiness.

You probably have questions on implementing extra storage volumes in your atmosphere, go away a remark.

Concerning the authors