AWSTemplateFormatVersion: 2010-09-09 Description: Sunapsis (RDS for SQL Server)

Parameters: EnvironmentType: Description: Environment type of this resource (dev, tst, rpt, trn, prd) Type: String Default: prd AllowedValues: - dev - tst - rpt - trn - prd RestoreDBSnapshotARN: Description: ARN of DB snapshot to restore from (leave blank if undesired) Type: String DbEngineVersion: Description: DB engine version to use for the RDS SQL Server instance Type: String Default: "15.00" AllowedValues: - "13.00" - "15.00" EncryptStorage: Description: Specify whether to encrypt the RDS storage (note that restoring from a snapshot is not possible when this is set to true as storage enryption is determined by the snapshot) Type: String Default: true AllowedValues: - true - false EmailForSNSSubscription: Description: Email address to use for the SNS subscription Type: String Default: sunapsis-aws@list.arizona.edu RdsMasterUsername: Description: Master username for the RDS 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: sunapsisadmin RdsMasterUserPassword: Description: Master user password for the RDS instsance Type: String NoEcho: true MinLength: 8 MaxLength: 41 AllowedPattern: "[a-zA-Z0-9]*" ConstraintDescription: Password must contain only alphanumeric characters InstanceClass: Description: Instance class (db.r5.large for non-PRD and db.r5.xlarge for PRD) Type: String Default: db.r5.large AllocatedStorage: Description: Storage (GiB) allocated for the instance (min 20, max 16384). Type: Number Default: 200 MinValue: 20 MaxValue: 16384 ConstraintDescription: Value must be a number between 20 and 16384. ### Tags TagService: Description: Name of the service associated with this resource (as listed in the service catalog) Type: String Default: Sunapsis TagContactNetID: Description: NetID of the primary technical resource Type: String Default: dbaty TagTicketNumber: Description: Ticket number for the CLOUD Jira project Type: String Default: CLOUD-85 TagAccountNumber: Description: Account number associated with the service Type: String Default: 2433643 TagSubAccount: Description: Sub account associated with the service Type: String Default: Sunapsis

Metadata: AWS::CloudFormation::Interface: ParameterGroups: - Label: default: Environment Parameters: - EnvironmentType - Label: default: Settings Parameters: - RestoreDBSnapshotARN - DbEngineVersion - EncryptStorage - EmailForSNSSubscription - RdsMasterUsername - RdsMasterUserPassword - Label: default: Tags Parameters: - TagService - TagContactNetID - TagTicketNumber - TagAccountNumber - TagSubAccount ParameterLabels: {}

Conditions: IsPRD: !Equals [ !Ref EnvironmentType, prd ] IsStorageEncrypted: !Equals [ !Ref EncryptStorage, true] IsSql2019: !Equals [ !Ref DbEngineVersion, "15.00" ]

RdsInstance: Type: AWS::RDS::DBInstance

DeletionPolicy: Delete Properties: AllocatedStorage: !Ref AllocatedStorage StorageType: io1 Iops: 1000 AllowMajorVersionUpgrade: true AutoMinorVersionUpgrade: true

BackupRetentionPeriod: !If [ IsPRD, 30, 7 ] CopyTagsToSnapshot: true DBInstanceClass: !Ref InstanceClass DBInstanceIdentifier: !Sub sunapsis-${EnvironmentType}-rds-sql DBParameterGroupName: !If [ IsSql2019, !Ref RdsParameterGroup2019, !Ref RdsParameterGroup ] DBSubnetGroupName: !Ref RdsSubnetGroup Engine: sqlserver-se EngineVersion: !Ref DbEngineVersion LicenseModel: license-included PreferredBackupWindow: 10:00-11:00 # UTC time, this is 3am-4am AZ (UTC-7) PreferredMaintenanceWindow: sat:08:00-sat:09:00 # UTC time, this is Sat 1am-2am AZ (UTC-7) MasterUsername: !Ref RdsMasterUsername MasterUserPassword: !Ref RdsMasterUserPassword

MultiAZ: !If [ IsPRD, true, false ] OptionGroupName: !If [ IsSql2019, !Ref RdsOptionGroup2019, !Ref RdsOptionGroup ] PubliclyAccessible: false DBSnapshotIdentifier: !If [ IsStorageEncrypted, !Ref "AWS::NoValue", !Ref RestoreDBSnapshotARN ] StorageEncrypted: !If [ IsStorageEncrypted, true, false ] Timezone: US Mountain Standard Time VPCSecurityGroups: - !Ref SecurityGroupForSQL Tags: - Key: Name Value: !Sub sunapsis-${EnvironmentType}-rds-og - Key: environment Value: !Ref EnvironmentType - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService - Key: subaccount Value: !Ref TagSubAccount

RdsSubnetGroup: Type: AWS::RDS::DBSubnetGroup Properties: DBSubnetGroupDescription: !Sub Sunapsis (${EnvironmentType}) - subnet group for mirroring (Multi-AZ) SubnetIds: - Fn::ImportValue: !Sub sunapsis-${EnvironmentType}-private-subnet-a - Fn::ImportValue: !Sub sunapsis-${EnvironmentType}-private-subnet-b Tags: - Key: Name Value: !Sub sunapsis-${EnvironmentType}-rds-subnetgroup - Key: environment Value: !Ref EnvironmentType - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService - Key: subaccount Value: !Ref TagSubAccount

RdsOptionGroup: Type: AWS::RDS::OptionGroup DependsOn: IAMPolicyRdsSql Properties: EngineName: sqlserver-se MajorEngineVersion: "13.00" OptionGroupDescription: !Sub Sunapsis (${EnvironmentType}) - 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 sunapsis-${EnvironmentType}-rds-optiongroup - Key: environment Value: !Ref EnvironmentType - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService - Key: subaccount Value: !Ref TagSubAccount RdsOptionGroup2019: Type: AWS::RDS::OptionGroup DependsOn: IAMPolicyRdsSql Properties: EngineName: sqlserver-se MajorEngineVersion: "15.00" OptionGroupDescription: !Sub Sunapsis (${EnvironmentType}) - 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 sunapsis-${EnvironmentType}-rds-2019-optiongroup - Key: environment Value: !Ref EnvironmentType - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService - Key: subaccount Value: !Ref TagSubAccount

RdsParameterGroup: Type: AWS::RDS::DBParameterGroup Properties: Description: !Sub Sunapsis (${EnvironmentType}) RDS SQL Server Parameter Group Family: sqlserver-se-13.0 Parameters: cost threshold for parallelism: 100

max server memory (mb): "{DBInstanceClassMemory/1310720}" Tags: - Key: Name Value: !Sub sunapsis-${EnvironmentType}-rds-parametergroup - Key: environment Value: !Ref EnvironmentType - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService - Key: subaccount Value: !Ref TagSubAccount RdsParameterGroup2019: Type: AWS::RDS::DBParameterGroup Properties: Description: !Sub Sunapsis (${EnvironmentType}) 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 sunapsis-${EnvironmentType}-rds-2019-parametergroup - Key: environment Value: !Ref EnvironmentType - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService - Key: subaccount Value: !Ref TagSubAccount

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 Sunapsis - RDS SQL Server (${EnvironmentType}) VpcId: Fn::ImportValue: !Sub sunapsis-${EnvironmentType}-vpc 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.136.143/32 Description: Idera SQL Diagnostic Manager (rouge.catnet) - IpProtocol: tcp FromPort: 1433 ToPort: 1433 CidrIp: 150.135.49.146/32 Description: OGI SQL Server (via linked server) Tags: - Key: Name Value: !Sub sunapsis-${EnvironmentType}-sg-sql - Key: environment Value: !Ref EnvironmentType - Key: contactnetid Value: !Ref TagContactNetID - Key: ticketnumber Value: !Ref TagTicketNumber - Key: accountnumber Value: !Ref TagAccountNumber - Key: service Value: !Ref TagService - Key: subaccount Value: !Ref TagSubAccount

AlarmRdsHighCPUUtilization: Type: AWS::CloudWatch::Alarm Condition: IsPRD Properties: ActionsEnabled: true AlarmActions: - !Ref SNSTopic AlarmDescription: RDS CPU Utilization ComparisonOperator: GreaterThanThreshold Dimensions: - Name: DBInstanceIdentifier Value: !Ref RdsInstance EvaluationPeriods: 3 InsufficientDataActions: - !Ref SNSTopic MetricName: CPUUtilization Namespace: AWS/RDS Statistic: Average Period: 300 Threshold: 80 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 sunapsis-${EnvironmentType}-hostedzone-name Comment: !Sub Sunapsis (${EnvironmentType}) - alias for RDS SQL Server RecordSets: - Name: Fn::ImportValue: !Sub sunapsis-${EnvironmentType}-fqdn-for-sql Type: CNAME TTL: 300 ResourceRecords: - !GetAtt RdsInstance.Endpoint.Address

SNSTopic: Type: AWS::SNS::Topic Properties: TopicName: !Sub sunapsis-${EnvironmentType} DisplayName: !Sub AWS Sunapsis (${EnvironmentType}) Subscription: - Endpoint: !Ref EmailForSNSSubscription Protocol: email

IAMRoleRdsSql: Type: AWS::IAM::Role Properties: RoleName: !Sub sunapsis-${EnvironmentType}-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 sunapsis-${EnvironmentType}-policy-rds-sql PolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: - s3:ListBucket - s3:GetBucketLocation Resource: !Sub - "arn:aws:s3:::${S3BucketDb}" - S3BucketDb: Fn::ImportValue: !Sub sunapsis-${EnvironmentType}-bucket-db - Effect: Allow Action: - s3:GetObjectMetaData - s3:GetObject - s3:PutObject Resource: !Sub - "arn:aws:s3:::${S3BucketDb}/*" - S3BucketDb: Fn::ImportValue: !Sub sunapsis-${EnvironmentType}-bucket-db Roles: - !Ref IAMRoleRdsSql

Outputs: RdsSecurityGroupId: Description: GroupId of the RDS SQL Security Group Value: !GetAtt SecurityGroupForSQL.GroupId Export: Name: !Sub sunapsis-${EnvironmentType}-RdsSecurityGroupId SnsTopicArn: Description: SNS Topic ARN Value: !Ref SNSTopic Export: Name: !Sub sunapsis-${EnvironmentType}-SnsTopicArn