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(0003)
Metadata:
  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
          - VolumeType
          - VolumeIops
      - Label:
          default: AWS Quick Start configuration
        Parameters:
          - QSS3BucketName
          - QSS3KeyPrefix
    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
      PrivateSubnet3ID:
        default: Private subnet 3 ID
      QSS3BucketName:
        default: Quick Start S3 bucket name
      QSS3KeyPrefix:
        default: Quick Start S3 key prefix
      VolumeIops:
        default: Data volume IOPS
      VolumeSize:
        default: Data volume size
      VolumeType:
        default: Data volume type
      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
  QSS3BucketName:
    AllowedPattern: ^[0-9a-zA-Z]+([0-9a-zA-Z-]*[0-9a-zA-Z])*$
    ConstraintDescription: Quick Start bucket name can include numbers, lowercase
      letters, uppercase letters, and hyphens (-). It cannot start or end with a hyphen
      (-).
    Default: aws-quickstart
    Description: S3 bucket name for the Quick Start assets. Quick Start bucket name
      can include numbers, lowercase letters, uppercase letters, and hyphens (-).
      It cannot start or end with a hyphen (-).
    Type: String
  QSS3KeyPrefix:
    AllowedPattern: ^[0-9a-zA-Z-/]*$
    ConstraintDescription: Quick Start key prefix can include numbers, lowercase letters,
      uppercase letters, hyphens (-), and forward slash (/).
    Default: quickstart-microsoft-sql/
    Description: S3 key prefix for the Quick Start assets. Quick Start key prefix
      can include numbers, lowercase letters, uppercase letters, hyphens (-), and
      forward slash (/).
    Type: String
  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
  VolumeType:
    AllowedValues:
      - gp2
      - io1
    Default: gp2
    Description: Volume type for the SQL Data, Logs and TempDb volumes
    Type: String
  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
Conditions:
  GovCloudCondition: !Equals
    - !Ref 'AWS::Region'
    - us-gov-west-1
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: !Ref PubliclyAccessible
  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}"