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

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 ### 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 TagSubAccount: Description: Sub account associated with the service Type: String Default: D2L

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

Conditions: IsPRD: !Equals [ !Ref TagEnvironment, prd ] IsNotPRD: !Not [!Equals [!Ref TagEnvironment, prd ]]

RdsInstance: Type: AWS::RDS::DBInstance

Properties: AllocatedStorage: 500 AllowMajorVersionUpgrade: false AutoMinorVersionUpgrade: true BackupRetentionPeriod: !If [ IsPRD, 30, 7 ] CopyTagsToSnapshot: true

DBInstanceClass: !If [ IsPRD, db.m4.xlarge, db.m4.large ] DBInstanceIdentifier: !Sub d2lcsr-${TagEnvironment}-rds-sql DBParameterGroupName: !Ref RdsParameterGroup DBSubnetGroupName: !Ref RdsSubnetGroup Engine: sqlserver-se EngineVersion: 12.00.4422.0.v1 LicenseModel: bring-your-own-license PreferredBackupWindow: 10:00-11:00 PreferredMaintenanceWindow: sat:08:00-sat:09:00 MasterUsername: !Ref MasterUser MasterUserPassword: !Ref MasterPassword

MultiAZ: !If [ IsPRD, true, false ] OptionGroupName: !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: subaccount Value: !Ref TagSubAccount - 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: subaccount Value: !Ref TagSubAccount - 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: subaccount Value: !Ref TagSubAccount - 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: subaccount Value: !Ref TagSubAccount - 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.140.15.15/32 Description: Data Stage 1 (uaz-ep-e27.mosaic.arizona.edu) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.140.24.19/32 Description: Data Stage 2 (uaz-ep-e52.mosaic.arizona.edu) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.183.143/32 Description: AWS Data Stage PRD (aws-ds-prd.bi.arizona.edu) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 10.220.161.88/32 Description: AWS Data Stage DEV (aws-ds-dev.bi.arizona.edu) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 128.196.136.143/32 Description: Idera SQL Diagnostic Manager (rouge.catnet) - 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: subaccount Value: !Ref TagSubAccount - 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: 300 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