AWSTemplateFormatVersion: '2010-09-09'
Description: >-
  This template deploys two Windows Server Failover Clustering (WSFC) and SQL Server
  AlwaysOn Availability Group nodes. This template is intended to be installed into
  an existing VPC that was built using the sample reference architecture titled: "Implementing
  Active Directory Domain Services in the AWS Cloud" **WARNING** This template creates
  Amazon EC2 Windows instance and related resources. You will be billed for the AWS
  resources used if you create a stack from this template. QS(qs-1tsaami29)
Metadata:
  cfn-lint:
    config:
      ignore_checks:
        - W4001
        - W9002
        - W9003
        - W9006
        - E9007
        - E9101
  AWS::CloudFormation::Interface:
    ParameterGroups:
      - Label:
          default: Network configuration
        Parameters:
          - VPCCIDR
          - VPCID
          - PrivateSubnet1ID
          - PrivateSubnet2ID
      - Label:
          default: SQL Server configuration
        Parameters:
          - DBAutoMinorVersionUpgrade
          - SQLEngineEdition
          - SQLEngineVersion
          - SQLMasterUserName
          - SQLMasterUserPassword
          - VolumeSize
          - VolumeIops
    ParameterLabels:
      DBAutoMinorVersionUpgrade:
        default: Database auto minor version upgrade
      SQLEngineEdition:
        default: MSSQL database engine edition
      SQLEngineVersion:
        default: MSSQL database engine version
      SQLMasterUserName:
        default: SQL Server master user name
      SQLMasterUserPassword:
        default: SQL Server master user password
      PrivateSubnet1ID:
        default: Private subnet 1 ID
      PrivateSubnet2ID:
        default: Private subnet 2 ID
      VolumeIops:
        default: Data volume IOPS
      VolumeSize:
        default: Data volume size
      VPCID:
        default: VPC ID
      VPCCIDR:
        default: VPC CIDR
Parameters:
  DBAutoMinorVersionUpgrade: 
    AllowedValues: 
      - "true"
      - "false"
    Default: "false"
    Description: "Select true to set up auto minor version upgrade."
    Type: String
  PrivateSubnet1ID:
    Description: ID of the private subnet 1 in Availability Zone 1 (e.g., subnet-a0246dcd)
    Type: AWS::EC2::Subnet::Id
  PrivateSubnet2ID:
    Description: ID of the private subnet 2 in Availability Zone 2 (e.g., subnet-a0246dcd)
    Type: AWS::EC2::Subnet::Id
#  PubliclyAccessible: 
#    AllowedValues: 
#      - "true"
#      - "false"
#    Default: "false"
#    Description: "Select whether DB instance is internet-facing."
#    Type: String
  DBInstanceClass:
    AllowedValues:
      - db.m5.large
      - db.m5.xlarge
      - db.m5.2xlarge
      - db.m5.4xlarge
      - db.m5.12xlarge
      - db.m5.24xlarge
      - db.m4.large
      - db.m4.xlarge
      - db.m4.2xlarge
      - db.m4.4xlarge
      - db.m4.10xlarge
      - db.m4.16xlarge
      - db.r4.large
      - db.r4.xlarge
      - db.r4.2xlarge
      - db.r4.4xlarge
      - db.r4.8xlarge
      - db.r4.16xlarge
    ConstraintDescription: "Must select a valid database instance type."
    Default: db.r4.2xlarge
    Description: "The name of the compute and memory capacity class of the database instance."
    Type: String
  SQLEngineEdition:
    AllowedValues:
      - sqlserver-ee
      - sqlserver-se
      - sqlserver-ex
      - sqlserver-web
    ConstraintDescription: "Must select a MSSQL Database Engine Edition."
    Default: sqlserver-ee
    Description: "MSSQL Database Engine Edition."
    Type: String
  SQLEngineVersion:
    AllowedValues:
      - 14.00.3049.1.v1
      - 14.00.3035.2.v1
      - 14.00.3015.40.v1
      - 14.00.1000.169.v1
      - 13.00.5216.0.v1
      - 13.00.4522.0.v1
      - 13.00.4466.4.v1
      - 13.00.4451.0.v1
      - 13.00.4422.0.v1
      - 13.00.2164.0.v1
      - 12.00.5571.0.v1
      - 12.00.5546.0.v1
      - 12.00.5000.0.v1
      - 11.00.7462.6.v1
      - 11.00.6594.0.v1
      - 11.00.6020.0.v1
      - 11.00.5058.0.v1
    ConstraintDescription: "Must select a MSSQL Database Engine Version."
    Default: 14.00.3049.1.v1
    Description: "MSSQL Database Engine Version."
    Type: String
  BackupRetentionPeriod:
    Default: 7
    Description: The number of days for which automated backups are retained. Setting this parameter to a positive number enables backups. Setting this parameter to 0 disables automated backups. 
    Type: Number
  SQLMasterUserName:
    AllowedPattern: '[a-zA-Z0-9]*'
    Default: sqlsa
    Description: SQL Server master user name.
    MaxLength: '25'
    MinLength: '5'
    Type: String
  SQLMasterUserPassword:
    AllowedPattern: (?=^.{6,255}$)((?=.*\d)(?=.*[A-Z])(?=.*[a-z])|(?=.*\d)(?=.*[^A-Za-z0-9])(?=.*[a-z])|(?=.*[^A-Za-z0-9])(?=.*[A-Z])(?=.*[a-z])|(?=.*\d)(?=.*[A-Z])(?=.*[^A-Za-z0-9]))^.*
    Description: Password for the SQL master user. Must be at least 8 characters containing letters, numbers and symbols
    MaxLength: '32'
    MinLength: '8'
    NoEcho: 'true'
    Type: String
  VolumeIops:
    Default: '1000'
    Description: Provisioned IOPS for the SQL Data, Logs and TempDb volumes. This
      parameter is only applicable when SQL Server Volume Type is set to "io1"
    MaxValue: '20000'
    MinValue: '100'
    Type: Number
  VolumeSize:
    Default: '500'
    Description: Volume size for the SQL Data, Logs and TempDb volumes, in GiB
    MaxValue: '16000'
    MinValue: '100'
    Type: Number
  VPCID:
    Description: ID of the VPC (e.g., vpc-0343606e)
    Type: AWS::EC2::VPC::Id
  VPCCIDR:
    AllowedPattern: ^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])(\/(1[6-9]|2[0-8]))$
    ConstraintDescription: CIDR block parameter must be in the form x.x.x.x/16-28
    Default: 10.0.0.0/16
    Description: CIDR Block for the VPC
    Type: String
Rules:
  SubnetsInVPC:
    Assertions:
      - Assert: !EachMemberIn
          - !ValueOfAll
            - AWS::EC2::Subnet::Id
            - VpcId
          - !RefAll 'AWS::EC2::VPC::Id'
        AssertDescription: All subnets must in the VPC
Resources:
  SQLSecrets:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: !Sub 'AWSQuickStart/${AWS::StackName}/SQLSecrets'
      Description: MS SQL Credentials for Quick Start
      SecretString: !Sub '{ "username" : "${SQLMasterUserName}", "password" : "${SQLMasterUserPassword}" }'
  RDSSQLDBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties: 
      DBSubnetGroupDescription: !Sub 'RDS Subnet Group for ${AWS::StackName} RDS SQL'
      DBSubnetGroupName: !Sub ${AWS::StackName}-DBSubnetGroup
      SubnetIds: 
        - !Ref PrivateSubnet1ID
        - !Ref PrivateSubnet2ID
  RDSSQL:
    Type: "AWS::RDS::DBInstance" 
    Properties: 
      AutoMinorVersionUpgrade: !Ref DBAutoMinorVersionUpgrade
      AllocatedStorage: !Ref VolumeSize
      BackupRetentionPeriod: !Ref BackupRetentionPeriod
      DBInstanceClass: !Ref DBInstanceClass
      DBSubnetGroupName: !Ref RDSSQLDBSubnetGroup
      Engine: !Ref SQLEngineEdition
      EngineVersion: !Ref SQLEngineVersion
      Iops: !Ref VolumeIops
      LicenseModel: license-included
      MasterUserPassword: !Ref SQLMasterUserPassword
      MasterUsername: !Ref SQLMasterUserName
      MultiAZ: True
      VPCSecurityGroups:
        - Fn::GetAtt:
          - WSFCClientSecurityGroup
          - GroupId
      PubliclyAccessible: false
  WSFCClientSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: SQL Client access ports
      VpcId: !Ref 'VPCID'
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 1433
          ToPort: 1433
          CidrIp: !Ref 'VPCCIDR'
Outputs:
   RDSSQLEndpoint:
     Description: Database endpoint
     Value: !Sub "${RDSSQL.Endpoint.Address}:${RDSSQL.Endpoint.Port}"