Add SQL Server to CDK project

Next you are going to create Amazon RDS SQL Server instance that will be used to run initial version of the application.

Open NorthwindCdk.sln in Visual Studio.

Add Amazon.CDK.AWS.RDS Nuget package to the project:

RDS Nuget

Open NorthwindCdkStack.cs.

Add following import statement to the NorthwindCdkStack.cs.

using Amazon.CDK.AWS.RDS;

Please add the following changes at the end of NorthwindCdkStack constructor:

Using provided remote VM

Replace 54.240.197.232 in the code snippet below with remote VM (AWS Lab Win Dev Box) public IP address, as you want to give access to the database from that instance. Dev box public IP

Using your own computer

Replace 54.240.197.232 with your IP address, as you are going to open security group for yourself only.

namespace NorthwindCdk
{
    public class NorthwindCdkStack : Stack
    {
        internal NorthwindCdkStack(Construct scope, string id, IStackProps props = null) : base(scope, id, props)
        {
            .........

            // SQL Server

            var sg = new SecurityGroup(this, "NorthwindDatabaseSecurityGroup", new SecurityGroupProps
            {
                Vpc = vpc,

                SecurityGroupName = "Northwind-DB-SG",
                AllowAllOutbound = false
            });

            // !!!!!!!!!! replace IP according to the instructions above
            sg.AddIngressRule(Peer.Ipv4("54.240.197.232/32"), Port.Tcp(1433)); // SQL Server
            // !!!!!!!!!!

            var sql = new DatabaseInstance(this, "NorthwindSQLServer", new DatabaseInstanceProps
            {
                Vpc = vpc,

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

                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
            });

            new CfnOutput(this, "SQLServerEndpointAddress", new CfnOutputProps
            {
                Value = sql.DbInstanceEndpointAddress
            });
        }
    }
}

The code above does the following:

Creates new security group and allows traffic from your IP address to SQL Server port 1433.

var sg = new SecurityGroup(this, "NorthwindDatabaseSecurityGroup", new SecurityGroupProps
{
    Vpc = vpc,

    SecurityGroupName = "Northwind-DB-SG",
    AllowAllOutbound = false
});

sg.AddIngressRule(Peer.Ipv4("54.240.197.232/32"), Port.Tcp(1433)); // SQL Server

Creates new Amazon RDS SQL Server Express instance:

  • Using t3.small instance
  • Using adminuser as administrator user name
  • Using Admin12345? as administrator password
  • Using security group created above
  • Using public subnet from the VPC created above (to be able to connect to SQL Server from your developer PC)
  • Without any backups or delete protection (so you can easily destroy stack)
var sql = new DatabaseInstance(this, "NorthwindSQLServer", new DatabaseInstanceProps
{
    Vpc = vpc,

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

    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
});

Outputs Amazon RDS SQL Server endpoint address once it’s created (as you need to modify connection string to connect to the database):

new CfnOutput(this, "SQLServerEndpointAddress", new CfnOutputProps
{
    Value = sql.DbInstanceEndpointAddress
});

Save changes and compile the project.