Remove SQL Server

Now that you switched to Amazon Aurora PostgreSQL and confirmed that application is up and running, you can remove Amazon RDS SQL Server instance.

Open NorthwindCdk.sln in Visual Studio.

Open NorthwindCdkStack.cs.

Remove following 4 blocks of code:

  • Remove Ingress Rule for the security group for port 1433.
sg.AddIngressRule(Peer.Ipv4("54.240.197.232/32"), Port.Tcp(1433)); // SQL Server
  • Remove Amazon RDS SQL Server database instance
var sql = new DatabaseInstance(this, "NorthwindSQLServer", new DatabaseInstanceProps
{
    Vpc = vpc,

    InstanceIdentifier = "northwind-sqlserver",
    Engine = DatabaseInstanceEngine.SQL_SERVER_EX, // SQL Server Express
    MasterUsername = "adminuser",
    MasterUserPassword = new SecretValue("Admin12345?"),
    InstanceClass = InstanceType.Of(InstanceClass.BURSTABLE3, InstanceSize.SMALL), // t3.small
    SecurityGroups = new ISecurityGroup[] { sg },
    MultiAz = false,
    VpcPlacement = new SubnetSelection() { SubnetType = SubnetType.PUBLIC }, 

    DeletionProtection = false, // you need to be able to delete database
    DeleteAutomatedBackups = true,
    BackupRetention = Duration.Days(0),
    RemovalPolicy = RemovalPolicy.DESTROY // you need to be able to delete database
});
  • Remove Amazon RDS SQL Server endpoint output
new CfnOutput(this, "SQLServerEndpointAddress", new CfnOutputProps
{
    Value = sql.DbInstanceEndpointAddress
});
  • Remove parameter from the Systems Manager Parameter Store
new StringParameter(this, "NorthwindDatabaseConnectionString", new StringParameterProps {
    ParameterName = "/Northwind/ConnectionStrings/NorthwindDatabase", 
    Type = ParameterType.STRING,
    Description = "SQL Server connection string",
    StringValue = string.Format("Server={0},1433;Integrated Security=false;User ID=adminuser;Password=Admin12345?;Initial Catalog=NorthwindTraders;", sql.DbInstanceEndpointAddress)
});

Save changes and compile the project.

Run cdk diff to take a look at the changes that are going to be deployed.

cdk diff

The output should look like this:

Stack NorthwindCdkStack
Security Group Changes
┌───┬───────────────────────────────────────────┬─────┬──────────┬──────────────────┐
│   │ Group                                     │ Dir │ Protocol │ Peer             │
├───┼───────────────────────────────────────────┼─────┼──────────┼──────────────────┤
│ - │ ${NorthwindDatabaseSecurityGroup.GroupId} │ In  │ TCP 1433 │ 34.255.215.58/32 │
└───┴───────────────────────────────────────────┴─────┴──────────┴──────────────────┘
(NOTE: There may be security-related changes not in this list. See https://github.com/aws/aws-cdk/issues/1299)

Resources
[-] AWS::RDS::DBSubnetGroup NorthwindSQLServerSubnetGroup107ABEB4 destroy
[-] AWS::RDS::DBInstance NorthwindSQLServerB034B5AC destroy
[-] AWS::SSM::Parameter NorthwindDatabaseConnectionString2DB3D9C6 destroy
[~] AWS::EC2::SecurityGroup NorthwindDatabaseSecurityGroup NorthwindDatabaseSecurityGroup0267A879
 └─ [~] SecurityGroupIngress
     └─ @@ -1,13 +1,6 @@
        [ ] [
        [ ]   {
        [ ]     "CidrIp": "34.255.215.58/32",
        [-]     "Description": "from 34.255.215.58/32:1433",
        [-]     "FromPort": 1433,
        [-]     "IpProtocol": "tcp",
        [-]     "ToPort": 1433
        [-]   },
        [-]   {
        [-]     "CidrIp": "34.255.215.58/32",
        [ ]     "Description": "from 34.255.215.58/32:5432",
        [ ]     "FromPort": 5432,
        [ ]     "IpProtocol": "tcp",

Outputs
[-] Output SQLServerEndpointAddress: {"Value":{"Fn::GetAtt":["NorthwindSQLServerB034B5AC","Endpoint.Address"]}}

As you can see, security group is going to be modified and DBInstance and Parameter are going to be destroyed.

Now it’s time to deploy the updates using cdk deploy command.

cdk deploy

Once deployment is complete, you will se the following output:

NorthwindCdkStack: deploying...
NorthwindCdkStack: creating CloudFormation changeset...
 0/7 | 10:00:20 AM | UPDATE_IN_PROGRESS   | AWS::EC2::SecurityGroup               | NorthwindDatabaseSecurityGroup (NorthwindDatabaseSecurityGroup0267A879)
 1/7 | 10:00:36 AM | UPDATE_COMPLETE      | AWS::EC2::SecurityGroup               | NorthwindDatabaseSecurityGroup (NorthwindDatabaseSecurityGroup0267A879)
 1/7 | 10:00:40 AM | UPDATE_IN_PROGRESS   | AWS::RDS::DBInstance                  | NorthwindPostgreSQL/Instance1 (NorthwindPostgreSQLInstance15E57D78E)
 2/7 | 10:00:40 AM | UPDATE_COMPLETE      | AWS::RDS::DBInstance                  | NorthwindPostgreSQL/Instance1 (NorthwindPostgreSQLInstance15E57D78E)
 2/7 | 10:00:42 AM | UPDATE_COMPLETE_CLEA | AWS::CloudFormation::Stack            | NorthwindCdkStack
 2/7 | 10:00:43 AM | DELETE_IN_PROGRESS   | AWS::SSM::Parameter                   | NorthwindDatabaseConnectionString2DB3D9C6
 3/7 | 10:00:45 AM | DELETE_COMPLETE      | AWS::SSM::Parameter                   | NorthwindDatabaseConnectionString2DB3D9C6
 3/7 | 10:00:46 AM | DELETE_IN_PROGRESS   | AWS::RDS::DBInstance                  | NorthwindSQLServerB034B5AC
 3/7 Currently in progress: NorthwindCdkStack, NorthwindSQLServerB034B5AC
 4/7 | 10:04:52 AM | DELETE_COMPLETE      | AWS::RDS::DBInstance                  | NorthwindSQLServerB034B5AC
 4/7 | 10:04:53 AM | DELETE_IN_PROGRESS   | AWS::RDS::DBSubnetGroup               | NorthwindSQLServerSubnetGroup107ABEB4
 5/7 | 10:04:58 AM | DELETE_COMPLETE      | AWS::RDS::DBSubnetGroup               | NorthwindSQLServerSubnetGroup107ABEB4
 6/7 | 10:04:59 AM | UPDATE_COMPLETE      | AWS::CloudFormation::Stack            | NorthwindCdkStack

 ✅  NorthwindCdkStack

Outputs:
NorthwindCdkStack.PostgreSQLEndpointAddress = northwind-postgresql.cluster-cyhlwzws5aoz.eu-west-1.rds.amazonaws.com

Stack ARN:
arn:aws:cloudformation:eu-west-1:404486542784:stack/NorthwindCdkStack/f78fb1c0-8ed1-11ea-8a83-0a0af0d573f8

Congratulations! You have successfully migrated database from Amazon RDS SQL Server to Amazon Aurora PostgreSQL and removed unused Amazon RDS SQL Server instance.