# Working with storage in RDS for SQL Server
<a name="Appendix.SQLServer.CommonDBATasks.DatabaseStorage"></a>

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:
+ **Flexible file distribution** – Distribute database data files and log files across multiple volumes for improved I/O performance.
+ **Storage optimization** – Use different storage types and configurations for different workload requirements.
+ **Scalability** – Add storage capacity without modifying existing volumes.
+ **Automatic capacity management** – Enable storage autoscaling on each additional storage volume so that Amazon RDS increases the volume's storage automatically when it runs low on space. Each volume has its own maximum storage threshold and scales independently. For more information, see [Storage autoscaling for additional storage volumes](USER_PIOPS.Autoscaling.md#USER_PIOPS.Autoscaling.AdditionalVolumes).

**Topics**
+ [Considerations for using additional storage volumes with RDS for SQL Server](#SQLServer.ASV.Considerations)
+ [Add, remove, or modify storage volumes with RDS for SQL Server](#SQLServer.ASV.Management)
+ [Restore operations for additional storage volumes with RDS for SQL Server](#SQLServer.ASV.Restore)
+ [Use cases for additional storage volumes with RDS for SQL Server](#SQLServer.ASV.UseCases)

## Considerations for using additional storage volumes with RDS for SQL Server
<a name="SQLServer.ASV.Considerations"></a>

Take note of the following features and limitations when using additional storage volumes with RDS for SQL Server:
+ You can only add storage volumes on SQL Server Standard Edition (SE), Enterprise Edition (EE), and Developer Edition (DEV-EE).
+ You can add up to 3 additional storage volumes per instance.
+ Volume names are automatically mapped to Windows drive letters as follows:
  + `rdsdbdata2` – `H:` drive
  + `rdsdbdata3` – `I:` drive
  + `rdsdbdata4` – `J:` drive
+ TempDB files continue to use the `T:` drive when using NVMe instance storage. SQL Server Audit files and Microsoft Business Intelligence (MSBI) files remain on the `D:` drive.
+ You can enable storage autoscaling for each additional storage volume independently by setting a maximum storage threshold for the volume. For more information, see [Storage autoscaling for additional storage volumes](USER_PIOPS.Autoscaling.md#USER_PIOPS.Autoscaling.AdditionalVolumes).
+ You can only add General Purpose SSD (gp3) and Provisioned IOPS SSD (io2) storage types.
+ The minimum storage size of additional storage volumes is the same as the limit set for the default `D:` drive. The maximum storage size for your DB instance is 256 TiB total across all volumes.
+ Adding storage volumes to instances with read replicas or to read replica instances isn't supported.
+ Adding storage volumes to instances enabled for cross-region automated backup isn't supported.
+ Moving files between volumes after creation isn't supported.
+ You can't delete the `D:` volume, but you can delete other storage volumes as long as they're empty.
+ Modifying the size of existing volumes during snapshot restore or point-in-time recovery (PITR) isn't supported. However, you can add new storage volumes during restore operations.

## Add, remove, or modify storage volumes with RDS for SQL Server
<a name="SQLServer.ASV.Management"></a>

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.

**Important**  
Adding or removing additional storage volumes creates a backup pending action and a point-in-time restore blackout window. This window closes when the backup workflow completes.

**Topics**
+ [Adding storage volumes](#SQLServer.ASV.Adding)
+ [Scaling additional storage volumes](#SQLServer.ASV.Scaling)
+ [Removing additional storage volumes](#SQLServer.ASV.Removing)

### Adding storage volumes
<a name="SQLServer.ASV.Adding"></a>

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
<a name="SQLServer.ASV.Scaling"></a>

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
```

After you modify an additional storage volume, that specific volume enters the storage-optimization phase. To monitor optimization progress on the modified volume, inspect the corresponding entry in the `AdditionalStorageVolumes` array in the [DescribeDBInstances](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeDBInstances.html) response. During optimization, that entry's `StorageOperationStatus` is set to `Optimizing` and `StorageOperationPercentProgress` reports 0–100. For more information, see [Monitoring storage operations per volume](CHAP_Storage.md#Welcome.AdditionalStorageVolumes.Monitoring).

### Removing additional storage volumes
<a name="SQLServer.ASV.Removing"></a>

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

**Warning**  
Before you remove an additional storage volume, make sure that no database files are stored on the volume.

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
<a name="SQLServer.ASV.Restore"></a>

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

**Topics**
+ [Snapshot restore](#SQLServer.ASV.SnapshotRestore)
+ [Point-in-time recovery](#SQLServer.ASV.PITR)
+ [Native database restore](#SQLServer.ASV.NativeRestore)

### Snapshot restore
<a name="SQLServer.ASV.SnapshotRestore"></a>

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
<a name="SQLServer.ASV.PITR"></a>

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
<a name="SQLServer.ASV.NativeRestore"></a>

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

**`data_file_volume`**  
Specifies the drive letter for database data files

**`log_file_volume`**  
Specifies the drive letter for database log files

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
<a name="SQLServer.ASV.UseCases"></a>

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

**Topics**
+ [Creating databases on additional storage volumes](#SQLServer.ASV.NewDatabase)
+ [Extending storage capacity](#SQLServer.ASV.ExtendStorage)
+ [Moving databases between volumes](#SQLServer.ASV.MoveDatabase)
+ [Archiving data to cost-effective storage](#SQLServer.ASV.ArchiveData)

### Creating databases on additional storage volumes
<a name="SQLServer.ASV.NewDatabase"></a>

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:\rdsdbdata\data\{{MyDatabase_Data.mdf}}',
    SIZE = 100MB,
    FILEGROWTH = 10MB
)
LOG ON (
    NAME = '{{MyDatabase_Log}}',
    FILENAME = 'I:\rdsdbdata\data\{{MyDatabase_Log.ldf}}',
    SIZE = 10MB,
    FILEGROWTH = 10%
);
```

### Extending storage capacity
<a name="SQLServer.ASV.ExtendStorage"></a>

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.

**To extend storage capacity**

1. Add a storage volume to your instance using the `modify-db-instance` command.

1. Add a new data file to the additional storage volume:

   ```
   ALTER DATABASE {{MyDatabase}}
   ADD FILE (
       NAME = '{{MyDatabase_Data2}}',
       FILENAME = 'H:\rdsdbdata\data\{{MyDatabase_Data2.ndf}}',
       SIZE = 500MB,
       FILEGROWTH = 50MB
   );
   ```

### Moving databases between volumes
<a name="SQLServer.ASV.MoveDatabase"></a>

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](SQLServer.Procedural.Importing.Native.Using.md).

**To move a database to a different volume**

1. Back up the database using `rds_backup_database`:

   ```
   EXEC msdb.dbo.rds_backup_database 
       @source_db_name='{{MyDatabase}}',
       @s3_arn_to_backup_to='{{arn:aws:s3:::my-bucket/database-backup.bak}}';
   ```

1. Restore the database to the target volume:

   ```
   EXEC msdb.dbo.rds_restore_database    
       @restore_db_name='{{MyDatabase_New}}',
       @s3_arn_to_restore_from='{{arn:aws:s3:::my-bucket/database-backup.bak}}',
       @data_file_volume='H:',
       @log_file_volume='I:';
   ```

1. Drop the database from your old drive to release the space. For more information, see [Dropping a database in an Amazon RDS for Microsoft SQL Server DB instance](Appendix.SQLServer.CommonDBATasks.DropMirrorDB.md).

### Archiving data to cost-effective storage
<a name="SQLServer.ASV.ArchiveData"></a>

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

**To archive partitioned data**

1. Add a storage volume with appropriate storage type and capacity.

1. Create a new filegroup on the additional storage volume:

   ```
   ALTER DATABASE {{MyDatabase}}
   ADD FILEGROUP {{ArchiveFileGroup}};
   
   ALTER DATABASE {{MyDatabase}}
   ADD FILE (
       NAME = '{{Archive_Data}}',
       FILENAME = 'H:\rdsdbdata\data\{{Archive_Data.ndf}}',
       SIZE = 1GB,
       FILEGROWTH = 100MB
   ) TO FILEGROUP {{ArchiveFileGroup}};
   ```

1. Move partitions to the new filegroup using SQL Server partition management commands.