AWS can not change DB Subnet Group for AWS RDS

I had this same question a few months back, and ended up contacting AWS (I have Enterprise support). This was the result:

Unfortunately, moving DB instance subnet group to another subnet group in the same VPC is not supported at this time. I realize our documentation says that it is supported, but that is an error. We are currently working on updating our documentation to reflect this and I apologize for the mis-communication.

However, I do have a workaround, you can create a new temporary VPC, update the subnet group to point to that temporary VPC, then once that process completes, change the subnet group to point back to your new subnet group.

Alternatively, another way to do it would be to create a database snapshot, and spin up a new instance from the snapshot.

You might want to look at both approaches. Both methods will probably cause you some downtime unless you are able to run your application in read only mode for a while, or have a method of replaying transactions on the restored snapshot.

The following steps can be performed to change the subnet group within VPC,

1. Convert the prod DB instance to a Single Availability Zone (if multi-AZ enabled)

2. Modify the subnet group by choosing any default/other VPC subnet groups and continue with Apply immediately

3. After, again modify the subnet group by choosing the new one that you want to

4. Also, choose relevant Security Groups and continue with Apply immediately

5. Check connectivity from servers

6. Then convert it back to a Multi-AZ

Note: Time to perform the above steps takes around 40 mins to 50 mins

What I did -> create a snapshot, restore snapshot and select your new subnet group that have only public or your desired subnets and ...voilá

the subnet group option for aurora can just set on instance build and you cannot change it after.

Seems that 2 and half years in and this problem still persists. The biggest problem is that I am updating my infrastructure through Cloudformation and from documentation the correct modifying strategy when changing DB subnet name is a resource replacement, which does not seem to be the case once I look at my stack changeset where DB subnet resource for replacement has flag "False" and afterward the stack update fails with DB subnet group having "Internal Error".

Now I essentially have to manually modify resources to have them update correctly even though that completely goes against usage of CloudFormation

I have an Amazon Relational Database Service (Amazon RDS) DB instance that is in a public subnet. I want to move my DB instance from a public to a private subnet within the same VPC, and make my DB instance completely private. How can I do this?

Short description

Amazon RDS does not provide an option to change the subnet group of your DB instance, with the same VPC. However, you can use the workaround method in this article to move your DB instance from a public subnet to a private subnet, and make your DB instance private.

The advantages of using this method include:

• Avoids the need to create a new DB instance
• Avoids using the snapshot-restore process
• Minimizes the downtime involved in creating a new instance and diverting traffic. The only downtime you see is the failover time.

Resolution

Disable Multi-AZ deployments and public accessibility on your DB instance

If your DB instance is already set to Single-AZ with the Public accessibility parameter set to No, you can skip this step and proceed to the next step to discover your IP address.

To modify your DB instance to disable Multi-AZ deployments, follow these steps:

1. Sign in to the AWS RDS console.
2. From the navigation pane, choose Databases, and then choose the DB instance you want to modify.
3. Choose Modify.
4. From the Modify DB Instance page, for Multi-AZ deployment and Public accessibility, choose No.
5. Choose Continue, and review the summary of modifications.
6. Choose Apply immediately to apply your changes.
7. Review your changes, and if correct, choose Modify DB Instance to save.

Discover the IP address of your DB instance

After your DB instance has returned to the Available state, run dig on the DB instance's endpoint to find its underlying IP address:

dig <rds-endpoint>

Output:

db-RDS-instance.xxxxxxxx.us-east-1.rds.amazonaws.com. 5 IN A 172.39.5.213

From the private IP, you can find which subnet it is using. This is the subnet that your primary instance uses.

In this example, the list of subnet CIDR is as follows:

• subnet1 -> 172.39.5.0/24
• subnet2 -> 172.39.4.0/24

Because the IP is falling under 179.39.5.0/24, you can conclude that the instance is placed in subnet1.

Remove the public subnets and add private subnets on your DB instance

Add all required private subnets in the subnet group. Also, delete all public subnets from the subnet group except for the one that is used by your primary. In the example above, you delete everything except subnet1 because it is being used by your DB instance.

Note: A private subnet is a subnet that is associated with a route table that has no route for an Internet gateway.

1. Sign in to the Amazon RDS console.
2. From the navigation pane, choose Subnet groups, and then choose the subnet group that is associated with your DB instance.
3. Choose Edit.
4. From the Add subnets section, choose the availability zone and private subnets you want to add.
5. Select the public subnets you want to delete, and then choose Remove.
6. Choose Save.

Enable Multi-AZ on your DB instance

Modify the DB instance to enable the Multi-AZ deployment. The new secondary launches in one of the remaining private subnets.

Reboot your DB instance with failover and disable Multi-AZ deployment

When your DB instance fails over, the secondary, which is using the private IP, becomes the primary and the public subnet becomes the secondary.

After you have rebooted your DB instance with failover, you need to remove the secondary which is now in the public subnet. To do this, modify your DB instance to disable Multi-AZ, again. You can do this by setting Multi-AZ deployment to No.

Remove the public subnet

1. Remove the remaining public subnet from the subnet group.

Note:Removing subnets from the subnet group is a configuration from the RDS side. It does not involve deleting any subnets from the VPC.

2. Check that there are only private subnets present in the subnet group.

3. If your DB instance was previously in Multi-AZ deployment, enable it again.

This solution involves failover and disabling/enabling Multi-AZ so there are few things to consider. For more information, see Modify a DB instance to be a Multi-AZ deployment.

Note: This method is very specific for RDS DB instances. If your DB instance is part of Aurora cluster, you can use the clone option. Or you can follow the steps in this article, but instead of disabling Multi-AZ, you should delete and recreate the readers.