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 XXX.XXX.XXX.XXX in the code snippet below with remote VM (CDK Workshop) public IP address, as you want to give access to the database from that instance. On the AWS Console page, click on the EC2 link under the Compute section (or search for EC2). Select CDK Workshop EC2 instance and copy the Public IPv4 address. Dev box public IP

Using your own computer

Replace XXX.XXX.XXX.XXX 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("XXX.XXX.XXX.XXX/32"), Port.Tcp(1433)); // SQL Server
            // !!!!!!!!!!

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

                InstanceIdentifier = "northwind-sqlserver",

                // SQL Server Express
                Engine = DatabaseInstanceEngine.SqlServerEx(new SqlServerExInstanceEngineProps { Version = SqlServerEngineVersion.VER_14 }), 
                
                Credentials = Credentials.FromPassword(
                    username: "adminuser", 
                    password: new SecretValue("Admin12345?")),
                
                // t3.small
                InstanceType = InstanceType.Of(InstanceClass.BURSTABLE3, InstanceSize.SMALL), 

                SecurityGroups = new ISecurityGroup[] { sg },
                MultiAz = false,

                // public subnet
                VpcSubnets = 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
            });

            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("XXX.XXX.XXX.XXX/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",
    // SQL Server Express
    Engine = DatabaseInstanceEngine.SqlServerEx(new SqlServerExInstanceEngineProps { Version = SqlServerEngineVersion.VER_14 }), 
    
    Credentials = Credentials.FromPassword(
        username: "adminuser", 
        password: new SecretValue("Admin12345?")),
    
    // t3.small
    InstanceType = InstanceType.Of(InstanceClass.BURSTABLE3, InstanceSize.SMALL), 
    SecurityGroups = new ISecurityGroup[] { sg },
    MultiAz = false,

    // public subnet
    VpcSubnets = 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
});

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.