AWS RDS – How to Increase Storage for a SQL Server Instance

amazon-rdsawsstorage

The Amazon docs state that you cannot increase the storage allocated to a SQL Server RDS instance:

Note that you cannot change the storage capacity nor the type of storage for a SQL Server DB instance due to limitations of striped storage attached to a Windows Server environment.

Oh well, so I guess you take a snapshot and restore it to a new instance with more storage… except that seems to be impossible too:

Amazon RDS does not support changing the storage configuration for a Microsoft SQL Server DB instance when restoring from a DB snapshot.

That quote may refer to changing between general SSD and provisioned I/O, but there's no --allocated-storage parameter for restore-db-instance-from-db-snapshot, so it does seem to be impossible when restoring a snapshot. For that matter, simply specifying a new number of IOPS gives an error:

Cannot restore this instance based in Windows OS because the request has a different iops than the backup.

What is the most convenient way to increase the storage space allocated to a SQL Server instance? Is there nothing better than Database Migration Services in this situation?

Amazon support confirmed that S3 and DMS are the only options for SQL Server.

This question has been raised on SE. I've opened this question provisionally; if there's a consensus in Meta that we should not duplicate DBA questions which exist elsewhere on SO, I'll delete this one.

2019: Some time since I raised this question, it seems that Amazon lifted some restrictions. I was able to resize a SQL Server instance without taking any special steps: just edit the config and allow it to reboot.

Best Answer

What is the most convenient way to increase the storage space allocated to a SQL Server instance? Is there nothing better than Database Migration Services in this situation?

Native backup and restore through S3 might be an alternative to DMS. However it also has it's cons:

  • Requires you to stop writing to the existing database while the backup/restore is performed in order to ensure the latest data is included.
  • Won't work for databases larger than 1TB in size (limit on backup size).
  • If you've already run out of space on RDS, the backup stored procedure might not work.

AWS explains the procedure in their Import/Export documentation for SQL Server, here are the headlines:

  1. Spin up a new RDS instance with the desired disk capacity
  2. Ensure both source and target databases have an option group that includes SQLSERVER_BACKUP_RESTORE
  3. Stop writes to the source database
  4. Use the stored procedure rds_backup_database on the source instance to backup your database(s) to an S3 bucket you have permissions to write to
  5. Use the stored procedure rds_restore_database on the destination database to restore the backup
  6. Point your clients to the new RDS instance
Related Question