Working with storage in RDS for SQL Server

With RDS for SQL Server, you can attach up to three additional volumes to your RDS for SQL Server instance, each mapped to a unique Windows drive letter. This allows you to distribute database files across multiple volumes beyond the default D: drive. When you add a storage volume, you get enhanced flexibility for database file management and storage optimization.

Benefits include:

Considerations for using additional storage volumes with RDS for SQL Server

Take note of the following features and limitations when using additional storage volumes with RDS for SQL Server:

Add, remove, or modify storage volumes with RDS for SQL Server

You can add, modify, and remove additional storage volumes using the AWS CLI or AWS Management Console. All operations use the modify-db-instance API operation with the additional-storage-volumes parameter.

Adding storage volumes

You can add up to three storage volumes beyond the default D: drive. To add a new storage volume to your RDS for SQL Server instance, use the modify-db-instance command with the additional-storage-volumes parameter.

The following example adds a new 4,000 GiB General Purpose SSD (gp3) volume named rdsdbdata4.

aws rds modify-db-instance \
  --db-instance-identifier my-sql-server-instance \
  --region us-east-1 \
  --additional-storage-volumes '[{"VolumeName":"rdsdbdata4","StorageType":"gp3","AllocatedStorage":4000}]' \
  --apply-immediately

Scaling additional storage volumes

You can modify any storage setting for your additional volumes except for storage size. The following example modifies the IOPS setting for the rdsdbdata2 volume.

aws rds modify-db-instance \
  --db-instance-identifier my-sql-server-instance \
  --region us-east-1 \
  --additional-storage-volumes '[{"VolumeName":"rdsdbdata2","IOPS":4000}]' \
  --apply-immediately

Removing additional storage volumes

You can't delete the D: volume, but you can delete other storage volumes when they're empty.

The following example removes the rdsdbdata4 volume.

aws rds modify-db-instance \
  --db-instance-identifier my-sql-server-instance \
  --region us-east-1 \
  --additional-storage-volumes '[{"VolumeName":"rdsdbdata4","SetForDelete":true}]' \
  --apply-immediately

Restore operations for additional storage volumes with RDS for SQL Server

When you restore your database, you can add storage volumes. You can also modify the storage settings of existing volumes.

Snapshot restore

When restoring from a snapshot, you can add new additional storage volumes or modify the IOPS, throughput, and storage type settings of existing volumes.

The following example restores a DB instance from a snapshot and modifies the IOPS setting for the rdsdbdata2 volume:

aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier my-restored-instance \
  --db-snapshot-identifier my-snapshot \
  --region us-east-1 \
  --additional-storage-volumes '[{"VolumeName":"rdsdbdata2","IOPS":5000}]'

Point-in-time recovery

During point-in-time recovery (PITR), you can add new additional storage volumes with custom configurations.

The following example performs PITR and adds a new 5,000 GiB General Purpose SSD (gp3) volume:

aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier my-source-instance \
  --target-db-instance my-pitr-instance \
  --use-latest-restorable-time \
  --region us-east-1 \
  --additional-storage-volumes '[{"VolumeName":"rdsdbdata4","StorageType":"gp3","AllocatedStorage":5000,"IOPS":5000,"StorageThroughput":200}]'

Native database restore

You can use the rds_restore_database stored procedure to restore databases to specific additional storage volumes. Two new parameters support volume selection:

The following example restores a database with data files on the H: drive and log files on the I: drive:

EXEC msdb.dbo.rds_restore_database    
    @restore_db_name='my_database',
    @s3_arn_to_restore_from='arn:aws:s3:::my-bucket/backup-file.bak',
    @data_file_volume='H:',
    @log_file_volume='I:';

If you don't specify volume parameters, or if you specify the D: drive for both parameters, the database files are restored to the default D: drive:

EXEC msdb.dbo.rds_restore_database    
    @restore_db_name='my_database',
    @s3_arn_to_restore_from='arn:aws:s3:::my-bucket/backup-file.bak';

Use cases for additional storage volumes with RDS for SQL Server

Additional storage volumes support various database management scenarios. The following sections describe common use cases and implementation approaches.

Creating databases on additional storage volumes

You can create new databases directly on additional storage volumes using standard SQL Server CREATE DATABASE statements.

The following example creates a database with data files on the H: drive and log files on the I: drive:

CREATE DATABASE MyDatabase
ON (
    NAME = 'MyDatabase_Data',
    FILENAME = 'H:\MyDatabase_Data.mdf',
    SIZE = 100MB,
    FILEGROWTH = 10MB
)
LOG ON (
    NAME = 'MyDatabase_Log',
    FILENAME = 'I:\MyDatabase_Log.ldf',
    SIZE = 10MB,
    FILEGROWTH = 10%
);

Extending storage capacity

When the default D: drive reaches its maximum capacity, you can add additional storage volumes, scale existing volumes, and create new data files or log files on the new volumes.

Moving databases between volumes

To move a database to a different volume, use the backup and restore approach with the rds_backup_database and rds_restore_database stored procedures. For more information, see Using native backup and restore.

Archiving data to cost-effective storage

For partitioned tables, you can archive older data to additional storage volumes with different performance characteristics.