AWSTemplateFormatVersion: 2010-09-09 Description: D2L Course Site Request (RDS SQL Server)

Parameters:

RestoreDBSnapshotARN: Description: ARN of DB snapshot to restore from (leave blank if undesired) Type: String

S3StackName: Description: Name of the stack that created the S3 buckets (template 1 of 4) Type: String Default: d2lcsr-prd-s3

VPCID: Description: Target VPC Type: AWS::EC2::VPC::Id Default: vpc-82ee9de5

PrivateSubnetA: Description: Private Subnet (us-west-2a) Type: AWS::EC2::Subnet::Id Default: subnet-fd862bb4

PrivateSubnetB: Description: Private Subnet (us-west-2b) Type: AWS::EC2::Subnet::Id Default: subnet-ca49c2ad

EmailForSNSSubscription: Description: Email address to use for the SNS subscription Type: String Default: d2lcsr-aws-prod@list.arizona.edu StorageAllocation: Description: Amount of storage (in GiB) to provison for the instance Type: String Default: 500

MasterUser: Description: Master username for the DB instance Type: String MinLength: 1 MaxLength: 16 AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*" ConstraintDescription: Username must begin with a letter and contain only alphanumeric characters Default: d2ladmin

MasterPassword: Description: Master password for the DB instsance Type: String NoEcho: true MinLength: 8 MaxLength: 41 AllowedPattern: "[a-zA-Z0-9]*" ConstraintDescription: Password must contain only alphanumeric characters DbEngineVersion: Description: DB engine version to use for the RDS SQL Server instance. If attempting a SQL 2014 to 2019 upgrade, be aware that the engine version and instance class cannot be changed with the same action. It is advised to manually change the instance class to one supported by SQL 2019 (the m4 family is NOT supported in 2019). Type: String Default: "15.00" AllowedValues: - "12.00" - "15.00" InstanceClass: Description: Instance class (db.r5.large for non-PRD and db.r5.xlarge for PRD). If attempting a SQL 2014 to 2019 upgrade, be aware that the engine version and instance class cannot be changed with the same action. It is advised to manually change the instance class to one supported by SQL 2019 (the m4 family is NOT supported in 2019). Type: String Default: db.r5.large LicenseModel: Description: License model. CloudFormation is stupid and thinks we're trying to revert back to bring-your-own because it's not recognizing the new template. Type: String Default: license-included AllowedValues: - license-included ### Tags TagService: Description: Name of the service associated with this resource (as listed in the service catalog) Type: String Default: D2L Course Site Request TagEnvironment: Description: Environment type of this resource (dev, tst, rpt, trn, prd) Type: String Default: prd AllowedValues: - dev - tst - rpt - trn - prd TagContactNetID: Description: NetID of the primary technical resource Type: String Default: dbaty TagTicketNumber: Description: Ticket number of the associated Type: String Default: CLOUD-76 TagAccountNumber: Description: Account number associated with the service Type: String Default: 2433643 # Learning Management Systems

Metadata: AWS::CloudFormation::Interface: ParameterGroups: - Label: default: Settings Parameters: - RestoreDBSnapshotARN - StorageAllocation - DbEngineVersion - InstanceClass - S3StackName - VPCID - PrivateSubnetA - PrivateSubnetB - EmailForSNSSubscription - MasterUser - MasterPassword - Label: default: Tags Parameters: - TagService - TagEnvironment - TagContactNetID - TagTicketNumber - TagAccountNumber ParameterLabels: {}

Conditions: IsPRD: !Equals [ !Ref TagEnvironment, prd ] IsNotPRD: !Not [!Equals [!Ref TagEnvironment, prd ]] IsSql2019: !Equals [ !Ref DbEngineVersion, "15.00" ]

RdsInstance: Type: AWS::RDS::DBInstance

Properties: AllocatedStorage: !Ref StorageAllocation AllowMajorVersionUpgrade: true AutoMinorVersionUpgrade: true BackupRetentionPeriod: !If [ IsPRD, 30, 7 ] CACertificateIdentifier: rds-ca-2019 CopyTagsToSnapshot: true

DBInstanceClass: !Ref InstanceClass DBInstanceIdentifier: !Sub d2lcsr-${TagEnvironment}-rds-sql DBParameterGroupName: !If [ IsSql2019, !Ref RdsParameterGroup2019, !Ref RdsParameterGroup ] DBSubnetGroupName: !Ref RdsSubnetGroup Engine: sqlserver-se EngineVersion: !Ref DbEngineVersion LicenseModel: !Ref LicenseModel PreferredBackupWindow: 10:00-11:00 PreferredMaintenanceWindow: sat:08:00-sat:09:00 MasterUsername: !Ref MasterUser MasterUserPassword: !Ref MasterPassword

MultiAZ: !If [ IsPRD, true, false ] OptionGroupName: !If [ IsSql2019, !Ref RdsOptionGroup2019, !Ref RdsOptionGroup ] PubliclyAccessible: false DBSnapshotIdentifier: !Ref RestoreDBSnapshotARN StorageEncrypted: false StorageType: gp2 VPCSecurityGroups: - !Ref SecurityGroupForSQL Tags: - Key: Name Value: !Sub d2lcsr-${TagEnvironment}-rds-og - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService

RdsSubnetGroup: Type: AWS::RDS::DBSubnetGroup Properties: DBSubnetGroupDescription: !Sub D2L CSR (${TagEnvironment}) - subnet group for mirroring (Multi-AZ) SubnetIds: - !Ref PrivateSubnetA - !Ref PrivateSubnetB Tags: - Key: Name Value: !Sub d2lcsr-${TagEnvironment}-rds-subnetgroup - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService

RdsOptionGroup: Type: AWS::RDS::OptionGroup DependsOn: IAMPolicyRdsSql Properties: EngineName: sqlserver-se MajorEngineVersion: "12.00" OptionGroupDescription: !Sub D2L CSR (${TagEnvironment}) - enable native backup/restore to/from S3 & mirroring (Multi-AZ) OptionConfigurations: - OptionName: SQLSERVER_BACKUP_RESTORE OptionSettings: - Name: IAM_ROLE_ARN Value: !GetAtt IAMRoleRdsSql.Arn Tags: - Key: Name Value: !Sub d2lcsr-${TagEnvironment}-rds-optiongroup - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService RdsOptionGroup2019: Type: AWS::RDS::OptionGroup DependsOn: IAMPolicyRdsSql Properties: EngineName: sqlserver-se MajorEngineVersion: "15.00" OptionGroupDescription: !Sub D2L CSR (${TagEnvironment}) - enable native backup/restore to/from S3 & mirroring (Multi-AZ) OptionConfigurations: - OptionName: SQLSERVER_BACKUP_RESTORE OptionSettings: - Name: IAM_ROLE_ARN Value: !GetAtt IAMRoleRdsSql.Arn Tags: - Key: Name Value: !Sub d2lcsr-${TagEnvironment}-rds-optiongroup - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService

RdsParameterGroup: Type: AWS::RDS::DBParameterGroup Properties: Description: !Sub D2L CSR (${TagEnvironment}) RDS SQL Server Parameter Group Family: sqlserver-se-12.0 Parameters: cost threshold for parallelism: "100"

max server memory (mb): "{DBInstanceClassMemory/1310720}" Tags: - Key: Name Value: !Sub d2lcsr-${TagEnvironment}-rds-parametergroup - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService RdsParameterGroup2019: Type: AWS::RDS::DBParameterGroup Properties: Description: !Sub D2L CSR (${TagEnvironment}) RDS SQL Server Parameter Group Family: sqlserver-se-15.0 Parameters: cost threshold for parallelism: "100"

max server memory (mb): "{DBInstanceClassMemory/1310720}" Tags: - Key: Name Value: !Sub d2lcsr-${TagEnvironment}-rds-parametergroup - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService

RDSEventSubscription: Type: AWS::RDS::EventSubscription Properties: Enabled: true EventCategories: - availability - configuration change - deletion - failover - failure - low storage - maintenance - notification - recovery - restoration SnsTopicArn: !Ref SNSTopic SourceIds: - !Ref RdsInstance SourceType: db-instance

SecurityGroupForSQL: Type: AWS::EC2::SecurityGroup Properties: GroupDescription: !Sub D2L Course Site Request - RDS SQL Server (${TagEnvironment}) VpcId: !Ref VPCID SecurityGroupIngress: - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 150.135.112.64/27 Description: InfraDev VPN - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 150.135.112.96/27 Description: EntApp VPN - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 128.196.216.0/24 Description: DTOL-885, UA BookStores - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 150.135.85.21/32 Description: CLOUD-366, UA BookStores - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.161.64/26 Description: Data Stage TST (aws-ds-tst.bi.arizona.edu) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.161.128/26 Description: Data Stage TST (aws-ds-tst.bi.arizona.edu) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.161.192/27 Description: Data Stage TST (aws-ds-tst.bi.arizona.edu) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.161.224/27 Description: Data Stage TST (aws-ds-tst.bi.arizona.edu) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.183.128/26 Description: Data Stage PRD (aws-ds-prod.bi.arizona.edu) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.183.192/26 Description: Data Stage PRD (aws-ds-prod.bi.arizona.edu) - IpProtocol: tcp FromPort: 143 ToPort: 1433 CidrIp: 10.138.129.128/26 Description: UITS-Oracle VPN - Fn::If: - IsNotPRD - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 128.196.216.0/24 Description: UA BookStores (development, non-PRD only) - !Ref AWS::NoValue - Fn::If: - IsNotPRD - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.192.248.0/23 Description: UA BookStores (development, non-PRD only) - !Ref AWS::NoValue - Fn::If: - IsPRD - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 150.135.85.21/32 Description: UA BookStores (production) - !Ref AWS::NoValue - Fn::If: - IsPRD - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.140.95/32 Description: UA BookStores (production) - !Ref AWS::NoValue - Fn::If: - IsPRD - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.140.48/32 Description: UA BookStores (production) - !Ref AWS::NoValue - Fn::If: - IsPRD - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.138.24/32 Description: UA BookStores (production) - !Ref AWS::NoValue - Fn::If: - IsPRD - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 35.163.117.44/32 Description: UA BookStores (production) - !Ref AWS::NoValue Tags: - Key: Name Value: !Sub d2lcsr-${TagEnvironment}-sg-sql - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService

AlarmRdsFreeStorageSpace: Type: AWS::CloudWatch::Alarm Properties: ActionsEnabled: true AlarmActions: - !Ref SNSTopic AlarmDescription: RDS Free Storage Space ComparisonOperator: LessThanThreshold Dimensions: - Name: DBInstanceIdentifier Value: !Ref RdsInstance EvaluationPeriods: 1 InsufficientDataActions: - !Ref SNSTopic MetricName: FreeStorageSpace Namespace: AWS/RDS Statistic: Average Period: 300 Threshold: 20000000000 AlarmRdsFreeableMemory: Type: AWS::CloudWatch::Alarm Condition: IsPRD Properties: ActionsEnabled: true AlarmActions: - !Ref SNSTopic AlarmDescription: RDS Freeable Memory ComparisonOperator: LessThanThreshold Dimensions: - Name: DBInstanceIdentifier Value: !Ref RdsInstance EvaluationPeriods: 1 InsufficientDataActions: - !Ref SNSTopic MetricName: FreeableMemory Namespace: AWS/RDS Statistic: Average Period: 300 Threshold: 314572800 AlarmRdsReadLatency: Type: AWS::CloudWatch::Alarm Condition: IsPRD Properties: ActionsEnabled: true AlarmActions: - !Ref SNSTopic AlarmDescription: RDS Read Latency ComparisonOperator: GreaterThanThreshold Dimensions: - Name: DBInstanceIdentifier Value: !Ref RdsInstance EvaluationPeriods: 1 InsufficientDataActions: - !Ref SNSTopic MetricName: ReadLatency Namespace: AWS/RDS Statistic: Average Period: 300 Threshold: 0.1 AlarmRdsWriteLatency: Type: AWS::CloudWatch::Alarm Condition: IsPRD Properties: ActionsEnabled: true AlarmActions: - !Ref SNSTopic AlarmDescription: RDS Write Latency ComparisonOperator: GreaterThanThreshold Dimensions: - Name: DBInstanceIdentifier Value: !Ref RdsInstance EvaluationPeriods: 1 InsufficientDataActions: - !Ref SNSTopic MetricName: WriteLatency Namespace: AWS/RDS Statistic: Average Period: 300 Threshold: 0.1 AlarmRdsDatabaseConnections: Type: AWS::CloudWatch::Alarm Condition: IsPRD Properties: ActionsEnabled: true AlarmActions: - !Ref SNSTopic AlarmDescription: RDS Database Connections ComparisonOperator: GreaterThanThreshold Dimensions: - Name: DBInstanceIdentifier Value: !Ref RdsInstance EvaluationPeriods: 1 InsufficientDataActions: - !Ref SNSTopic MetricName: DatabaseConnections Namespace: AWS/RDS Statistic: Average Period: 300 Threshold: 150

Route53RecordSetGroup: Type: AWS::Route53::RecordSetGroup Properties: HostedZoneName: Fn::ImportValue: !Sub ${S3StackName}-hostedzone-name Comment: !Sub D2L Course Site Request (${TagEnvironment}) - alias for RDS SQL Server RecordSets: - Name: Fn::ImportValue: !Sub ${S3StackName}-fqdn-for-sql Type: CNAME TTL: "60" ResourceRecords: - !GetAtt RdsInstance.Endpoint.Address

SNSTopic: Type: AWS::SNS::Topic Properties: TopicName: !Sub d2lcsr-sql-${TagEnvironment} DisplayName: !Sub AWS D2L CSR SQL (${TagEnvironment}) Subscription: - Endpoint: !Ref EmailForSNSSubscription Protocol: email

IAMRoleRdsSql: Type: AWS::IAM::Role Properties: RoleName: !Sub d2lcsr-${TagEnvironment}-role-rds-sql AssumeRolePolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Principal: Service: - rds.amazonaws.com Action: - sts:AssumeRole Path: /

IAMPolicyRdsSql: Type: AWS::IAM::Policy Properties: PolicyName: !Sub d2lcsr-${TagEnvironment}-policy-rds-sql PolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: - s3:ListBucket - s3:GetBucketLocation Resource: - Fn::ImportValue: !Sub ${S3StackName}-bucket-tableexport-arn - Effect: Allow Action: - s3:GetObjectMetaData - s3:GetObject - s3:PutObject Resource: - Fn::ImportValue: !Sub ${S3StackName}-bucket-tableexport-arn-wildcard Roles: - !Ref IAMRoleRdsSql

Outputs: RdsSecurityGroupId: Description: GroupId of the RDS SQL Security Group Value: !GetAtt SecurityGroupForSQL.GroupId Export: Name: !Sub ${AWS::StackName}-RdsSecurityGroupId RdsInstanceEndpointAddress: Description: FQDN of the RDS SQL instance Value: !GetAtt RdsInstance.Endpoint.Address RdsInstanceEndpointPort: Description: Port of the RDS SQL instance Value: !GetAtt RdsInstance.Endpoint.Port FQDNforValenceTest: Description: FQDN for the RDS SQL instance alias Value: Fn::ImportValue: !Sub ${S3StackName}-fqdn-for-sql Export: Name: !Sub ${AWS::StackName}-fqdn-for-sql