AWSTemplateFormatVersion: 2010-09-09
 
Description: >-
  This CloudFormation creates an Amazon Aurora PostgreSQL, RDS SQL Server Standard Edition server, DMS instance and endpoints for the purpose of testing a
  simple Full Load migration.

Parameters:
  MasterPassword:
    Description: Please enter the password for both SQL Server and Aurora master users.
    Type: String
    Default: password

  MasterUsername:
    Description: Please enter the admin username for both SQL Server and Aurora clusters.
    Type: String
    Default: adminuser

  VpcCIDR:
    Description: Please enter the IP range (CIDR notation) for this VPC
    Type: String
    Default: 10.192.0.0/16

  SubnetCIDR1:
    Description: Please enter the IP range (CIDR notation) for the first subnet
    Type: String
    Default: 10.192.10.0/24

  SubnetCIDR2:
    Description: Please enter the IP range (CIDR notation) for the second subnet
    Type: String
    Default: 10.192.11.0/24

  YourIPAddress:
    Description: Please enter your IP Address
    Type: String
    Default: 68.147.49.110/32

Resources:

  VPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: !Ref VpcCIDR
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: !Join ["_", [!Ref AWS::StackName,vpc]]

  InternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: !Join ["_", [!Ref AWS::StackName,igw]]

  InternetGatewayAttachment:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      InternetGatewayId: !Ref InternetGateway
      VpcId: !Ref VPC

  Subnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select [ 0, !GetAZs '' ]
      CidrBlock: !Ref SubnetCIDR1
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: !Join ["_", [!Ref AWS::StackName,subnet1]]

  Subnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      AvailabilityZone: !Select [ 1, !GetAZs '' ]
      CidrBlock: !Ref SubnetCIDR2
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: !Join ["_", [!Ref AWS::StackName,subnet2]]

  PublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: !Join ["_", [!Ref AWS::StackName,routetable]]

  DefaultPublicRoute:
    Type: AWS::EC2::Route
    DependsOn: InternetGatewayAttachment
    Properties:
      RouteTableId: !Ref PublicRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway

  PublicSubnet1RouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PublicRouteTable
      SubnetId: !GetAtt [Subnet1, SubnetId]

  PublicSubnet2RouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PublicRouteTable
      SubnetId: !GetAtt [Subnet2, SubnetId]

  SecurityGroupDatabase:
    Type: 'AWS::EC2::SecurityGroup'
    Properties:
      GroupDescription: !Sub '${AWS::StackName}-sg'
      GroupName: !Sub '${AWS::StackName}-sg'
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          CidrIp: !Ref YourIPAddress
          FromPort: '3306'
          ToPort: '3306'
        - IpProtocol: tcp
          CidrIp: '0.0.0.0/0'
          FromPort: '1433'
          ToPort: '1433'
      SecurityGroupEgress:
        - IpProtocol: '-1'
          CidrIp: '0.0.0.0/0'

  SelfReferencingIngress:
    Type: AWS::EC2::SecurityGroupIngress
    DependsOn: SecurityGroupDatabase
    Properties:
      GroupId:
        Ref: SecurityGroupDatabase
      IpProtocol: tcp
      FromPort: '0'
      ToPort: '65535'
      SourceSecurityGroupId:
        Ref: SecurityGroupDatabase

  DBSubnetGroup:
    Type: 'AWS::RDS::DBSubnetGroup'
    Properties:
      DBSubnetGroupDescription: !Join ["_", [!Ref AWS::StackName,subnet_group]]
      DBSubnetGroupName: !Join ["_", [!Ref AWS::StackName,subnet_group]]
      SubnetIds: 
        - !Ref Subnet1
        - !Ref Subnet2

  AuroraDBCluster:
    Type: 'AWS::RDS::DBCluster'
    Properties:
      DBClusterIdentifier: !Sub '${AWS::StackName}-aurora-cluster'
      DBSubnetGroupName: !Ref DBSubnetGroup
      DeletionProtection: false
      DBClusterParameterGroupName: !Ref RDSDBClusterParameterGroup
      Engine: aurora-postgresql
      MasterUsername: !Ref MasterUsername
      MasterUserPassword: !Ref MasterPassword 
      StorageEncrypted: false

      VpcSecurityGroupIds: 
      - !Ref SecurityGroupDatabase
  
  AuroraWriterInstance:
    Type: 'AWS::RDS::DBInstance'
    Properties:
      Engine: aurora-postgresql
      DBClusterIdentifier: !Ref AuroraDBCluster
      DBParameterGroupName: !Ref DBParameterGroup
      DBInstanceClass: db.t3.medium
      PubliclyAccessible: true

  SQLServerWriterInstance:
    Type: 'AWS::RDS::DBInstance'
    Properties:
      Engine: 'sqlserver-se'
      LicenseModel: 'license-included'
      EngineVersion: 15.00.4316.3.v1
      DBInstanceClass: db.t3.xlarge
      PubliclyAccessible: true
      MasterUsername: !Ref MasterUsername
      MasterUserPassword: !Ref MasterPassword
      AllocatedStorage: 20
      VPCSecurityGroups: 
      - !Ref SecurityGroupDatabase
      DBSubnetGroupName: !Ref DBSubnetGroup
      DeletionProtection: false

  #https://github.com/hashicorp/terraform-provider-aws/issues/11025
  #Need to attach polcy to role explictly and then add depends on

  #Gotcha, stand up a DMS instance first to generate the IAM Role.

  DMSReplicationSubnetGroup:
    Type: AWS::DMS::ReplicationSubnetGroup
    Properties: 
      ReplicationSubnetGroupDescription: !Join ["_", [!Ref AWS::StackName, replicationsubnetgroup]]
      ReplicationSubnetGroupIdentifier: !Join ["_", [!Ref AWS::StackName, replicationsubnetgroup]]
      SubnetIds: 
        - !Ref Subnet1
        - !Ref Subnet2
      Tags: 
        - Key: Name
          Value: !Join ["_", [!Ref AWS::StackName, replicationsubnetgroup]]

  DMSInstance:
    Type: AWS::DMS::ReplicationInstance
    Properties: 
      ReplicationInstanceClass: dms.t3.micro
      ReplicationInstanceIdentifier: !Join ["-", [!Ref AWS::StackName, replicationinstance]]
      ReplicationSubnetGroupIdentifier: !Ref DMSReplicationSubnetGroup
      ResourceIdentifier: String
      Tags: 
        - Key: Name
          Value: !Join ["_", [!Ref AWS::StackName, dms, instance]]
      VpcSecurityGroupIds: 
      - !Ref SecurityGroupDatabase

  DMSSourceMSSQLEndpoint:
    Type: AWS::DMS::Endpoint
    Properties: 
      DatabaseName: migrationdemo
      EndpointIdentifier: !Join ["-", [!Ref AWS::StackName, sourcemssqlendpoint]]
      EndpointType: source
      EngineName: sqlserver
      Password: !Ref MasterPassword 
      Port: 1433
      ServerName: !GetAtt [SQLServerWriterInstance, Endpoint.Address]
      Tags: 
        - Key: Name
          Value: !Join ["-", [!Ref AWS::StackName, sourcemssqlendpoint]]
      Username: !Ref MasterUsername 

  DMSTargetPOSTGRESEndpoint:
    Type: AWS::DMS::Endpoint
    Properties: 
      DatabaseName: postgres
      EndpointIdentifier: !Join ["-", [!Ref AWS::StackName, targetpostgresendpoint]]
      EndpointType: target
      EngineName: aurora-postgresql
      Password: !Ref MasterPassword 
      Port: 3306
      ServerName: !GetAtt [AuroraWriterInstance, Endpoint.Address]
      Tags: 
        - Key: Name
          Value: !Join ["-", [!Ref AWS::StackName, targetpostgresendpoint]]
      Username: !Ref MasterUsername

  DMSSourcePOSTGRESEndpoint:
    Type: AWS::DMS::Endpoint
    Properties: 
      DatabaseName: postgres
      EndpointIdentifier: !Join ["-", [!Ref AWS::StackName, sourcepostgresendpoint]]
      EndpointType: source
      EngineName: aurora-postgresql
      Password: !Ref MasterPassword 
      Port: 3306
      ServerName: !GetAtt [AuroraWriterInstance, Endpoint.Address]
      Tags: 
        - Key: Name
          Value: !Join ["-", [!Ref AWS::StackName, sourcepostgresendpoint]]
      Username: !Ref MasterUsername

  DMSTaskFirst:
    Type: AWS::DMS::ReplicationTask
    Properties: 
      MigrationType: cdc
      ReplicationInstanceArn: !Ref DMSInstance
      ReplicationTaskIdentifier: !Join ["-", [!Ref AWS::StackName, sourcetostaging]]
      SourceEndpointArn: !Ref DMSSourceMSSQLEndpoint
      TableMappings: "{\"rules\":[{\"rule-type\":\"transformation\",\"rule-id\":\"2\",\"rule-name\":\"tablerename\",\"rule-target\":\"table\",\"object-locator\":{\"schema-name\":\"%dbo\",\"table-name\":\"%earth\"},\"rule-action\":\"rename\",\"value\":\"earth_staging\",\"old-value\":null},{\"rule-type\":\"selection\",\"rule-id\":\"1\",\"rule-name\":\"tableselect\",\"object-locator\":{\"schema-name\":\"%dbo\",\"table-name\":\"%earth\"},\"rule-action\":\"include\",\"filters\":[]}]}"
      Tags: 
        - Key: Name
          Value: !Join ["-", [!Ref AWS::StackName, sourcetostaging]]
      TargetEndpointArn: !Ref DMSTargetPOSTGRESEndpoint

  DMSTaskSecond:
    Type: AWS::DMS::ReplicationTask
    Properties: 
      MigrationType: cdc
      ReplicationInstanceArn: !Ref DMSInstance
      ReplicationTaskIdentifier: !Join ["-", [!Ref AWS::StackName, stagingtodest]]
      SourceEndpointArn: !Ref DMSSourcePOSTGRESEndpoint
      TableMappings: "{\"rules\":[{\"rule-type\":\"transformation\",\"rule-id\":\"2\",\"rule-name\":\"tablerename\",\"rule-target\":\"table\",\"object-locator\":{\"schema-name\":\"%dbo\",\"table-name\":\"%earth_staging\"},\"rule-action\":\"rename\",\"value\":\"earth\",\"old-value\":null},{\"rule-type\":\"selection\",\"rule-id\":\"1\",\"rule-name\":\"tableselect\",\"object-locator\":{\"schema-name\":\"%dbo\",\"table-name\":\"%earth_staging\"},\"rule-action\":\"include\",\"filters\":[]}]}"
      Tags: 
        - Key: Name
          Value: !Join ["-", [!Ref AWS::StackName, stagingtodest]]
      TargetEndpointArn: !Ref DMSTargetPOSTGRESEndpoint

  RDSDBClusterParameterGroup:
    Type: 'AWS::RDS::DBClusterParameterGroup'
    Properties:
      Description: Custom Parameter Group for Aurora
      Family: aurora-postgresql14
      Parameters:
        rds.logical_replication: 1

  DBParameterGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties: 
      Description: Parameter group for Aurora writer
      Family: aurora-postgresql14
      Parameters:
        shared_preload_libraries: pglogical
      Tags: 
        - Key: Name
          Value: !Join ["-", [!Ref AWS::StackName, dbparametergroup]]