Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Modifying a DB Instance Running the Microsoft SQL Server Database Engine

You can change the settings of a DB instance to accomplish tasks such as changing the instance class or renaming the instance. This topic guides you through modifying an Amazon RDS DB instance running Microsoft SQL Server, and describes the settings for SQL Server DB instances.

We recommend that you test any changes on a test instance before modifying a production instance, so that you fully understand the impact of each change. This is especially important when upgrading database versions.

After you modify your DB instance settings, you can apply the changes immediately, or apply them during the next maintenance window for the DB instance. Some modifications cause an interruption by restarting the DB instance.

Note

You can't modify an existing SQL Server DB instance to change the storage type or storage allocation. Instead, you can create a backup of the database, and restore it to a new DB instance with the new storage type or storage allocation. For more information, see Importing and Exporting SQL Server Databases.

Settings for Microsoft SQL Server DB Instances

The following table contains details about which settings you can modify, which settings you can't modify, when the changes can be applied, and whether the changes cause downtime for the DB instance.

Setting Setting Description When the Change Occurs Downtime Notes

Allocated Storage

You cannot change the allocated storage for a SQL Server DB instance.

You can create a backup of the database, and restore it to a new DB instance with a storage allocation. For more information, see Importing and Exporting SQL Server Databases.

Auto Minor Version Upgrade

Yes if you want your DB instance to receive minor engine version upgrades automatically when they become available. Upgrades are installed only during your scheduled maintenance window.

Backup Retention Period

The number of days that automatic backups are retained. To disable automatic backups, set the backup retention period to 0.

For more information, see Working With Backups.

If Apply Immediately is set to true, the change occurs immediately.

If Apply Immediately is set to false and you change the setting from a nonzero value to another nonzero value, the change is applied asynchronously, as soon as possible. Otherwise, the change occurs during the next maintenance window.

An outage occurs if you change from 0 to a nonzero value, or from a nonzero value to 0.

Backup Window

The time range during which automated backups of your databases occur. The backup window is a start time in Universal Coordinated Time (UTC), and a duration in hours.

For more information, see Working With Backups.

The change is applied asynchronously, as soon as possible.

Certificate Authority

The certificate that you want to use.

Copy Tags to Snapshots

If you have any DB instance tags, this option copies them when you create a DB snapshot.

For more information, see Tagging Amazon RDS Resources.

Database Port

The port that you want to use to access the database.

The port value must not match any of the port values specified for options in the option group for the DB instance.

The change occurs immediately. This setting ignores the Apply Immediately setting.

The DB instance is rebooted immediately.

DB Engine Version

The version of the SQL Server database engine that you want to use. Before you upgrade your production DB instances, we recommend that you test the upgrade process on a test instance to verify its duration and to validate your applications.

For more information, see Upgrading the Microsoft SQL Server DB Engine.

If Apply Immediately is set to true, the change occurs immediately.

If Apply Immediately is set to false, the change occurs during the next maintenance window.

An outage occurs during this change.

DB Instance Class

The DB instance class that you want to use.

For more information, see DB Instance Class and DB Instance Class Support for Microsoft SQL Server.

If Apply Immediately is set to true, the change occurs immediately.

If Apply Immediately is set to false, the change occurs during the next maintenance window.

An outage occurs during this change.

DB Instance Identifier

The DB instance identifier.

For more information about the effects of renaming a DB instance, see Renaming a DB Instance.

If Apply Immediately is set to true, the change occurs immediately.

If Apply Immediately is set to false, the change occurs during the next maintenance window.

An outage occurs during this change. The DB instance is rebooted.

DB Parameter Group

The parameter group that you want associated with the DB instance.

For more information, see Working with DB Parameter Groups.

The parameter group change occurs immediately. However, parameter changes only occur when you reboot the DB instance manually without failover.

For more information, see Rebooting a DB Instance.

An outage doesn't occur during this change. However, parameter changes only occur when you reboot the DB instance manually without failover.

Enable Enhanced Monitoring

Yes to enable gathering metrics in real time for the operating system that your DB instance runs on.

For more information, see Enhanced Monitoring.

License Model

license-included to use the general license agreement for Microsoft SQL Server. bring-your-own-license to use your existing license.

To use the Bring Your Own License model, you must provide your Microsoft License Mobility Agreement information in the External Licenses section of the Amazon RDS console.

For more information, see Providing External License Information.

If Apply Immediately is set to true, the change occurs immediately.

If Apply Immediately is set to false, the change occurs during the next maintenance window.

An outage occurs during this change.

Maintenance Window

The time range during which system maintenance occurs. System maintenance includes upgrades, if applicable. The maintenance window is a start time in Universal Coordinated Time (UTC), and a duration in hours.

If you set the window to the current time, there must be at least 30 minutes between the current time and end of the window to ensure any pending changes are applied.

For more information, see Amazon RDS Maintenance Window.

The change occurs immediately. This setting ignores the Apply Immediately setting.

If there are one or more pending actions that cause a outage, and the maintenance window is changed to include the current time, then those pending actions are applied immediately, and an outage occurs.

Multi-AZ Deployment

Yes to have a standby mirror of your DB instance created in another Availability Zone for failover support. We recommend Multi-AZ for production workloads to maintain high availability. No for development and testing.

If your DB instance is running SQL Server 2014 or 2016 Enterprise Edition, and has in-memory optimization enabled, you can't add Multi-AZ. To add Multi-AZ, first disable in-memory optimization.

For more information, see Multi-AZ Deployments for Microsoft SQL Server with Database Mirroring.

If Apply Immediately is set to true, the change occurs immediately.

If Apply Immediately is set to false, the change occurs during the next maintenance window.

New Master Password

The password for your master user. The password must contain from 8 to 128 printable ASCII characters (excluding /,", a space, and @). By resetting the master password, you also reset permissions for the DB instance.

For more information, see Resetting the DB Instance Owner Role Password.

The change is applied asynchronously, as soon as possible. This setting ignores the Apply Immediately setting.

Option Group

The option group that you want associated with the DB instance.

For more information, see Working with Option Groups.

If Apply Immediately is set to true, the change occurs immediately.

If Apply Immediately is set to false, the change occurs during the next maintenance window.

Publicly Accessible

Yes to give the DB instance a public IP address, meaning that it will be accessible outside the VPC. To be publicly accessible, the DB instance also has to be in a public subnet in the VPC. No to make the DB instance accessible only from inside the VPC.

For more information, see Hiding a DB Instance in a VPC from the Internet.

The change occurs immediately. This setting ignores the Apply Immediately setting.

Security Group

The security group you want associated with the DB instance.

For more information, see Working with DB Security Groups.

The change is applied asynchronously, as soon as possible. This setting ignores the Apply Immediately setting.

Storage Type

The storage type that you want to use.

You cannot change the Provisioned IOPS setting for a SQL Server DB instance.

For more information, see Amazon RDS Storage Types.

If Apply Immediately is set to true, the change occurs immediately.

If Apply Immediately is set to false, the change occurs during the next maintenance window.

The following changes all result in a brief outage while the process starts. After that, you can use your database normally while the change takes place.

  • From General Purpose (SSD) to Magnetic.

  • From General Purpose (SSD) to Provisioned IOPS (SSD), if you are using a custom parameter group.

  • From Magnetic to General Purpose (SSD).

  • From Magnetic to Provisioned IOPS (SSD).

  • From Provisioned IOPS (SSD) to Magnetic.

  • From Provisioned IOPS (SSD) to General Purpose (SSD), if you are using a custom parameter group.

Subnet Group

The subnet group for the DB instance. You can use this setting to move your DB instance to a different VPC. If your DB instance is not in a VPC, you can use this setting to move your DB instance into a VPC.

For more information, see Moving a DB Instance Not in a VPC into a VPC.

AWS Management Console

To modify an SQL Server DB Instance

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the navigation pane, choose DB Instances, and then select the DB instance that you want to modify.

  3. Choose Instance Actions, and then choose Modify. The Modify DB Instance page appears.

  4. Change any of the settings that you want. For information about each setting, see Settings for Microsoft SQL Server DB Instances.

  5. To apply the changes immediately, select Apply Immediately. Selecting this option can cause an outage in some cases. For more information, see The Impact of Apply Immediately.

  6. When all the changes are as you want them, choose Continue.

  7. On the confirmation page, review your changes. If they are correct, choose Modify DB Instance to save your changes.

    Alternatively, choose Back to edit your changes, or choose Cancel to cancel your changes.

CLI

To modify a Microsoft SQL Server DB instance, use the AWS CLI command modify-db-instance.

Example

The following code modifies mydbinstance by setting the backup retention period to 1 week (7 days) and disabling automatic minor version upgrades. These changes are applied during the next maintenance window.

Parameters

  • --db-instance-identifier—the name of the db instance

  • --backup-retention-period—the number of days to retain automatic backups.

  • --no-auto-minor-version-upgrade—disallow automatic minor version upgrades. To allow automatic minor version upgrades, use --auto-minor-version-upgrade.

  • --no-apply-immediately—apply changes during the next maintenance window. To apply changes immediately, use --apply-immediately. For more information, see The Impact of Apply Immediately.

For Linux, OS X, or Unix:

Copy
aws rds modify-db-instance \ --db-instance-identifier mydbinstance \ --backup-retention-period 7 \ --no-auto-minor-version-upgrade \ --no-apply-immediately

For Windows:

Copy
aws rds modify-db-instance ^ --db-instance-identifier mydbinstance ^ --backup-retention-period 7 ^ --no-auto-minor-version-upgrade ^ --no-apply-immediately

API

To modify a Microsoft SQL Server DB instance, use the Amazon RDS API ModifyDBInstance action.

Example

The following code modifies mydbinstance by setting the backup retention period to 1 week (7 days) and disabling automatic minor version upgrades. These changes are applied during the next maintenance window.

Parameters

  • DBInstanceIdentifier—the name of the db instance

  • BackupRetentionPeriod—the number of days to retain automatic backups.

  • AutoMinorVersionUpgrade=false—disallow automatic minor version upgrades. To allow automatic minor version upgrades, set the value to true.

  • ApplyImmediately=false—apply changes during the next maintenance window. To apply changes immediately, set the value to true. For more information, see The Impact of Apply Immediately.

Copy
https://rds.us-east-1.amazonaws.com/ ?Action=ModifyDBInstance &ApplyImmediately=false &AutoMinorVersionUpgrade=false &BackupRetentionPeriod=7 &DBInstanceIdentifier=mydbinstance &SignatureMethod=HmacSHA256 &SignatureVersion=4 &Version=2013-09-09 &X-Amz-Algorithm=AWS4-HMAC-SHA256 &X-Amz-Credential=AKIADQKE4SARGYLE/20131016/us-east-1/rds/aws4_request &X-Amz-Date=20131016T233051Z &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date &X-Amz-Signature=087a8eb41cb1ab0fc9ec1575f23e73757ffc6a1e42d7d2b30b9cc0be988cff97

Related Topics