AWSTemplateFormatVersion: '2010-09-09'
Description: AzureDataSync Landing SQLServer with Data Sync
Parameters:
  EnvironmentType:
    Description: Specify the Environment type of the stack.
    Type: String
    Default: Prod
    AllowedValues:
      - Prod
    ConstraintDescription: Specify the deployment environment either Dev, Test or
      Prod.
  AzureSyncStageDBName:
    Default: AzureSyncStageDB
    Type: String
    Description: Azure Sync Stage DB Name
  RdpPort:
    Default: 3389
    Type: String
    Description: Default RDP port
  MsSqlPort:
    Default: 1433
    Type: String
    Description: Default MSSQL port
  SslPort:
    Default: 443
    Type: String
    Description: Default SSL port
  PostgresPort:
    Default: 5432
    Type: String
    Description: Default PostgreSQL port
  KeyPairName:
    Description: Specify the key name to connect to instance.
    Type: AWS::EC2::KeyPair::KeyName
  AmiID:
    Type: AWS::SSM::Parameter::Value<AWS::EC2::Image::Id>
    Description: The ID of the AMI.
    Default: /aws/service/ami-windows-latest/Windows_Server-2019-English-Full-SQL_2017_Standard
  ReplicationInstanceAllocatedStorage:
    Description: "Storage (in gigabytes) to be allocated the DMS replication instance.\n"
    Type: Number
    Default: 100
  StagingSQLPassword:
    Description: Staging DB password
    Type: String
    NoEcho: true
  ReplicationInstanceClass:
    Description: "The compute and memory capacity of the replication instance Valid\
      \ Values: dms.t2.micro | dms.t2.small | dms.t2.medium | dms.t2.large | dms.c4.large\
      \ | dms.c4.xlarge | dms.c4.2xlarge | dms.c4.4xlarge\n"
    Type: String
    Default: dms.C5.large
  RdpAccessCIDR:
    Description: Access to the RDP staging environment agent
    Type: String
    Default: '0.0.0.0/0'
  AzureSQLDBCIDR:
    Description: The IP address of Azure SQL DB IP address or CIDR network.
    Type: String
    Default: '0.0.0.0/0'
Conditions: {}
Mappings:
  RegionalSettings:
    us-east-1:
      nodeType: db.r5.large
      name: N. Virginia
      az1: us-east-1a
      az2: us-east-1b
      az3: us-east-1c
    us-east-2:
      nodeType: db.r5.large
      name: Ohio
      az1: us-east-2c
      az2: us-east-2a
      az3: us-east-2b
    us-west-2:
      nodeType: db.r5.large
      name: Oregon
      az1: us-west-2b
      az2: us-west-2c
      az3: us-west-2d
    ca-central-1:
      nodeType: db.r5.large
      name: Montreal
      az1: ca-central-1c
      az2: ca-central-1a
      az3: ca-central-1b
    eu-central-1:
      nodeType: db.r5.large
      name: Frankfurt
      az1: eu-central-1b
      az2: eu-central-1a
      az3: eu-central-1c
    eu-west-1:
      nodeType: db.r5.large
      name: Ireland
      az1: eu-west-1a
      az2: eu-west-1b
      az3: eu-west-1c
    eu-west-2:
      nodeType: db.r5.large
      name: London
      az1: eu-west-2b
      az2: eu-west-2a
      az3: eu-west-2c
    eu-west-3:
      nodeType: db.r5.large
      name: Paris
      az1: eu-west-3c
      az2: eu-west-3b
      az3: eu-west-3a
    ap-southeast-1:
      nodeType: db.r5.large
      name: Singapore
      az1: ap-southeast-1c
      az2: ap-southeast-1b
      az3: ap-southeast-1a
    ap-southeast-2:
      nodeType: db.r5.large
      name: Sydney
      az1: ap-southeast-2a
      az2: ap-southeast-2b
      az3: ap-southeast-2c
    ap-south-1:
      nodeType: db.r5.large
      name: Mumbai
      az1: ap-south-1a
      az2: ap-south-1b
      az3: ap-south-1c
    ap-northeast-1:
      nodeType: db.r5.large
      name: Tokyo
      az1: ap-northeast-1d
      az2: ap-northeast-1a
      az3: ap-northeast-1c
    ap-northeast-2:
      nodeType: db.r5.large
      name: Seoul
      az1: ap-northeast-2a
      az2: ap-northeast-2b
      az3: ap-northeast-2c
  EnvironmentToInstanceType:
    Prod:
      InstanceType: m5.large
  NetworkSettings:
    global:
      vpcCidr: 172.31.0.0/16
      subPub1Cidr: 172.31.0.0/24
      subPub2Cidr: 172.31.1.0/24
      subPub3Cidr: 172.31.2.0/24
      subPrv1Cidr: 172.31.10.0/24
      subPrv2Cidr: 172.31.11.0/24
      subPrv3Cidr: 172.31.12.0/24
      sshSourceCidr: '0.0.0.0/0'
  ClusterSettings:
    global:
      dbSchema: dbo
      dbDriver: postgresql
      dbVersion: 12.4
      dbEngine: aurora-postgresql
      dbFamily: aurora-postgresql12
    scaling:
      maxCapacity: 2
      minCapacity: 1
      cpuLoadTarget: 20
Resources:
  SSMIAMRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - ec2.amazonaws.com
            Action:
              - sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
  SQLServerInstanceProfile:
    Type: AWS::IAM::InstanceProfile
    Properties:
      Path: /
      Roles:
        - !Ref 'SSMIAMRole'
  SQLServerEIP:
    Type: AWS::EC2::EIP
    Properties:
      Domain: vpc
      InstanceId: !Ref 'SQLServerInstance'
  IamRoleForFlowLogs:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Sid: vpcflowlogs
            Effect: Allow
            Principal:
              Service: vpc-flow-logs.amazonaws.com
            Action: sts:AssumeRole
      Policies:
        - PolicyName: allow-access-to-cw-logs
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Effect: Allow
                Action:
                  - logs:CreateLogGroup
                  - logs:CreateLogStream
                  - logs:PutLogEvents
                  - logs:DescribeLogGroups
                  - logs:DescribeLogStreams
                Resource:
                  - arn:aws:logs:*:*:*
  FlowLogsGroup:
    Type: AWS::Logs::LogGroup
  FlowLogs:
    Type: AWS::EC2::FlowLog
    Properties:
      ResourceType: VPC
      ResourceId: !Ref 'vpc'
      TrafficType: ALL
      LogDestinationType: cloud-watch-logs
      LogGroupName: FlowLogs
      DeliverLogsPermissionArn: !GetAtt 'IamRoleForFlowLogs.Arn'
  vpc:
    Type: AWS::EC2::VPC
    Properties:
      EnableDnsSupport: true
      EnableDnsHostnames: true
      InstanceTenancy: default
      CidrBlock: !FindInMap
        - NetworkSettings
        - global
        - vpcCidr
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-vpc'
  vpcIgw:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-igw'
  attachIgwVpc:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      VpcId: !Ref 'vpc'
      InternetGatewayId: !Ref 'vpcIgw'
  sub1Public:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref 'vpc'
      CidrBlock: !FindInMap
        - NetworkSettings
        - global
        - subPub1Cidr
      AvailabilityZone: !FindInMap
        - RegionalSettings
        - !Ref 'AWS::Region'
        - az1
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-pub-sub-1'
  sub2Public:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref 'vpc'
      CidrBlock: !FindInMap
        - NetworkSettings
        - global
        - subPub2Cidr
      AvailabilityZone: !FindInMap
        - RegionalSettings
        - !Ref 'AWS::Region'
        - az2
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-pub-sub-2'
  sub3Public:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref 'vpc'
      CidrBlock: !FindInMap
        - NetworkSettings
        - global
        - subPub3Cidr
      AvailabilityZone: !FindInMap
        - RegionalSettings
        - !Ref 'AWS::Region'
        - az3
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-pub-sub-3'
  rtbPublic:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref 'vpc'
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-public-rtb'
  rteToIgw:
    Type: AWS::EC2::Route
    DependsOn: attachIgwVpc
    Properties:
      RouteTableId: !Ref 'rtbPublic'
      DestinationCidrBlock: '0.0.0.0/0'
      GatewayId: !Ref 'vpcIgw'
  srta1Public:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref 'sub1Public'
      RouteTableId: !Ref 'rtbPublic'
  srta2Public:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref 'sub2Public'
      RouteTableId: !Ref 'rtbPublic'
  srta3Public:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref 'sub3Public'
      RouteTableId: !Ref 'rtbPublic'
  sub1Private:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref 'vpc'
      CidrBlock: !FindInMap
        - NetworkSettings
        - global
        - subPrv1Cidr
      AvailabilityZone: !FindInMap
        - RegionalSettings
        - !Ref 'AWS::Region'
        - az1
      MapPublicIpOnLaunch: false
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-prv-sub-1'
  sub2Private:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref 'vpc'
      CidrBlock: !FindInMap
        - NetworkSettings
        - global
        - subPrv2Cidr
      AvailabilityZone: !FindInMap
        - RegionalSettings
        - !Ref 'AWS::Region'
        - az2
      MapPublicIpOnLaunch: false
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-prv-sub-2'
  sub3Private:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref 'vpc'
      CidrBlock: !FindInMap
        - NetworkSettings
        - global
        - subPrv3Cidr
      AvailabilityZone: !FindInMap
        - RegionalSettings
        - !Ref 'AWS::Region'
        - az3
      MapPublicIpOnLaunch: false
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-prv-sub-3'
  natEip:
    Type: AWS::EC2::EIP
    Properties:
      Domain: vpc
  vpcNgw:
    Type: AWS::EC2::NatGateway
    DependsOn: attachIgwVpc
    Properties:
      AllocationId: !GetAtt 'natEip.AllocationId'
      SubnetId: !Ref 'sub2Public'
  rtbNat:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref 'vpc'
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-nat-rtb'
  rteToNgw:
    Type: AWS::EC2::Route
    Properties:
      RouteTableId: !Ref 'rtbNat'
      DestinationCidrBlock: '0.0.0.0/0'
      NatGatewayId: !Ref 'vpcNgw'
  srta1Ngw:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref 'sub1Private'
      RouteTableId: !Ref 'rtbNat'
  srta2Ngw:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref 'sub2Private'
      RouteTableId: !Ref 'rtbNat'
  srta3Ngw:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref 'sub3Private'
      RouteTableId: !Ref 'rtbNat'
  dbSubnets:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Aurora Lab subnets allowed for deploying DB instances
      SubnetIds:
        - !Ref 'sub1Private'
        - !Ref 'sub2Private'
        - !Ref 'sub3Private'
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-db-subnet-group'
  DMSInstanceSecuritygroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      VpcId: !Ref 'vpc'
      GroupDescription: DMS Instance security group (firewall)
      SecurityGroupIngress: []
      SecurityGroupEgress: []
  DMSInstanceSecuritygroupEgressMsSql:
    Type: AWS::EC2::SecurityGroupEgress
    Properties:
      DestinationSecurityGroupId: !Ref 'StagingSQLSecuritygroup'
      FromPort: !Ref 'MsSqlPort'
      GroupId: !Ref 'DMSInstanceSecuritygroup'
      IpProtocol: tcp
      ToPort: !Ref 'MsSqlPort'
  DMSInstanceSecuritygroupEgressPostgres:
    Type: AWS::EC2::SecurityGroupEgress
    Properties:
      DestinationSecurityGroupId: !Ref 'AuroraPostgresSecuritygroup'
      FromPort: !Ref 'PostgresPort'
      GroupId: !Ref 'DMSInstanceSecuritygroup'
      IpProtocol: tcp
      ToPort: !Ref 'PostgresPort'
  AuroraPostgresSecuritygroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      VpcId: !Ref 'vpc'
      GroupDescription: Aurora lab database security group (firewall)
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: !Ref 'PostgresPort'
          ToPort: !Ref 'PostgresPort'
          SourceSecurityGroupId: !Ref 'DMSInstanceSecuritygroup'
      SecurityGroupEgress:
        - DestinationSecurityGroupId: !Ref 'DMSInstanceSecuritygroup'
          FromPort: !Ref 'PostgresPort'
          IpProtocol: tcp
          ToPort: !Ref 'PostgresPort'
  StagingSQLSecuritygroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      VpcId: !Ref 'vpc'
      GroupDescription: Allows access from the staging instance security group
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: !Ref 'RdpPort'
          ToPort: !Ref 'RdpPort'
          CidrIp: !Ref 'RdpAccessCIDR'
        - IpProtocol: tcp
          FromPort: !Ref 'SslPort'
          ToPort: !Ref 'SslPort'
          CidrIp: !Ref 'AzureSQLDBCIDR'
        - IpProtocol: tcp
          FromPort: !Ref 'MsSqlPort'
          ToPort: !Ref 'MsSqlPort'
          CidrIp: !Ref 'AzureSQLDBCIDR'
        - IpProtocol: tcp
          FromPort: !Ref 'MsSqlPort'
          ToPort: !Ref 'MsSqlPort'
          SourceSecurityGroupId: !Ref 'DMSInstanceSecuritygroup'
      SecurityGroupEgress:
        - IpProtocol: tcp
          FromPort: !Ref 'MsSqlPort'
          ToPort: !Ref 'MsSqlPort'
          DestinationSecurityGroupId: !Ref 'DMSInstanceSecuritygroup'
        - IpProtocol: tcp
          FromPort: !Ref 'MsSqlPort'
          ToPort: !Ref 'MsSqlPort'
          CidrIp: !Ref 'AzureSQLDBCIDR'
        - IpProtocol: tcp
          FromPort: !Ref 'SslPort'
          ToPort: !Ref 'SslPort'
          CidrIp: !Ref 'AzureSQLDBCIDR'
  KmsKey:
    Type: AWS::KMS::Key
    Properties:
      Enabled: true
      KeyPolicy:
        Version: '2012-10-17'
        Statement:
          - Sid: allow key administration
            Effect: Allow
            Principal:
              AWS: !Sub 'arn:aws:iam::${AWS::AccountId}:root'
            Action:
              - kms:Create*
              - kms:Describe*
              - kms:Enable*
              - kms:List*
              - kms:Put*
              - kms:Update*
              - kms:Revoke*
              - kms:Disable*
              - kms:Get*
              - kms:Delete*
              - kms:ScheduleKeyDeletion
              - kms:CancelKeyDeletion
            Resource: '*'
          - Sid: allow key use
            Effect: Allow
            Principal:
              AWS: !Sub 'arn:aws:iam::${AWS::AccountId}:root'
            Action:
              - kms:Encrypt
              - kms:Decrypt
              - kms:ReEncrypt
              - kms:GenerateDataKey*
              - kms:DescribeKey
            Resource: '*'
      EnableKeyRotation: true
  secretClusterMasterUser:
    Type: AWS::SecretsManager::Secret
    Properties:
      Description: !Sub 'Master user credentials for DB cluster ''${AWS::StackName}-cluster'''
      KmsKeyId: !GetAtt 'KmsKey.KeyId'
      GenerateSecretString:
        SecretStringTemplate: '{"username": "masteruser"}'
        GenerateStringKey: password
        PasswordLength: 10
        ExcludePunctuation: true
        IncludeSpace: false
        ExcludeLowercase: false
        ExcludeNumbers: false
        ExcludeUppercase: false
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-cluster-secret'
  dbCluster:
    Type: AWS::RDS::DBCluster
    Properties:
      Engine: !FindInMap
        - ClusterSettings
        - global
        - dbEngine
      EngineVersion: !FindInMap
        - ClusterSettings
        - global
        - dbVersion
      Port: !Ref 'PostgresPort'
      DBSubnetGroupName: !Ref 'dbSubnets'
      DBClusterIdentifier: !Sub '${AWS::StackName}-aurora-cluster'
      BackupRetentionPeriod: 1
      MasterUsername: !Join
        - ''
        - - '{{resolve:secretsmanager:'
          - !Ref 'secretClusterMasterUser'
          - :SecretString:username}}
      MasterUserPassword: !Join
        - ''
        - - '{{resolve:secretsmanager:'
          - !Ref 'secretClusterMasterUser'
          - :SecretString:password}}
      DatabaseName: !FindInMap
        - ClusterSettings
        - global
        - dbSchema
      StorageEncrypted: true
      VpcSecurityGroupIds:
        - !Ref 'AuroraPostgresSecuritygroup'
        - !Ref 'DMSInstanceSecuritygroup'
      EnableIAMDatabaseAuthentication: true
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-pg-cluster'
  dbNode1:
    Type: AWS::RDS::DBInstance
    Properties:
      DBClusterIdentifier: !Ref 'dbCluster'
      CopyTagsToSnapshot: true
      DBInstanceClass: !FindInMap
        - RegionalSettings
        - !Ref 'AWS::Region'
        - nodeType
      Engine: !FindInMap
        - ClusterSettings
        - global
        - dbEngine
      PubliclyAccessible: false
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-node-1'
  SQLServerInstance:
    CreationPolicy:
      ResourceSignal:
        Count: 1
        Timeout: PT30M
    Type: AWS::EC2::Instance
    Metadata:
      AWS::CloudFormation::Init:
        config:
          files:
            /etc/cfn/cfn-hup.conf:
              content: !Sub "[main]\nstack=${AWS::StackId}\nregion=${AWS::Region}\n\
                interval=1\n"
              mode: 256
              owner: root
              group: root
            /etc/cfn/hooks.d/cfn-auto-reloader.conf:
              content: !Sub "[cfn-auto-reloader-hook]\ntriggers=post.update\npath=Resources.SQLServerInstance.Metadata.AWS::CloudFormation::Init\n\
                action=../../opt/aws/bin/cfn-init --stack ${AWS::StackName} --resource\
                \ SQLServerInstance --region ${AWS::Region}\nrunas=root"
            C:\\cfn\\scripts\\script.ps1:
              content: !Sub "<powershell>\nmkdir C:\\AzureDataSyncAgent\n$path = 'C:\\\
                AzureDataSyncAgent'\n            $acl = Get-Acl -Path $path\n    \
                \        $accessrule = New-Object System.Security.AccessControl.FileSystemAccessRule\
                \ ('Everyone', 'FullControl', 'ContainerInherit, ObjectInherit', 'InheritOnly',\
                \ 'Allow')\n            $acl.SetAccessRule($accessrule)\n        \
                \    Set-Acl -Path $path -AclObject $acl\nInvoke-WebRequest -Uri \"\
                https://download.microsoft.com/download/C/B/3/CB35F695-5A32-4458-ACDB-E701250CEA1E/SQLDataSyncAgent-2.0-x64-ENU.msi\"\
                \ -OutFile \"C:\\AzureDataSyncAgent\\SQLDataSyncAgent-2.0-x64-ENU.msi\"\
                \nStart-Process -FilePath \"C:\\AzureDataSyncAgent\\SQLDataSyncAgent-2.0-x64-ENU.msi\"\
                \ -ArgumentList \"TARGETDIR=`\"C:\\Program Files (x86)\\Microsoft\
                \ SQL Data Sync 2.0`\" SERVICEACCOUNT=`\"LocalSystem`\" /qn\"\n#load\
                \ assemblies\n[System.Reflection.Assembly]::LoadWithPartialName(\"\
                Microsoft.SqlServer.SMO\") | Out-Null\n#Need SmoExtended for backup\n\
                [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SmoExtended\"\
                ) | Out-Null\n [System.Reflection.Assembly]::LoadWithPartialName(\"\
                Microsoft.SqlServer.SMO\") | Out-Null\n#Need SmoExtended for backup\n\
                [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SmoExtended\"\
                ) | Out-Null\n$server = New-Object Microsoft.SqlServer.Management.Smo.Server(\"\
                (local)\")\n$dbname='AzureSyncStageDB'\n$db = New-Object Microsoft.SqlServer.Management.Smo.Database($server,$dbname)\n\
                $db.Create()\n$sqlquery='\nUSE [master]\nGO\nCREATE LOGIN [azuresqlagent]\
                \ WITH PASSWORD=N''${StagingSQLPassword}'', DEFAULT_DATABASE=[master],\
                \ CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF\nGO\nALTER SERVER ROLE [sysadmin]\
                \ ADD MEMBER [azuresqlagent]\nGO\nuse [AzureSyncStageDB]\nEXEC sys.sp_cdc_enable_db\n\
                GO'\n$SQLServer = \"(local)\"\n$dbname = \"AzureSyncStageDB\"\nInvoke-Sqlcmd\
                \ -ServerInstance $SQLServer -Database $dbname -Query $sqlquery\n\
                $sqlquery2='USE [master]\nGO\nEXEC xp_instance_regwrite N''HKEY_LOCAL_MACHINE'',\
                \ N''Software\\Microsoft\\MSSQLServer\\MSSQLServer'', N''LoginMode'',\
                \ REG_DWORD, 2\nGO'\nInvoke-Sqlcmd -ServerInstance $SQLServer -Database\
                \ $dbname -Query $sqlquery2\nRestart-Service -Force MSSQLSERVER\n\
                $SQLServer = \"(local)\"\n$dbname = \"AzureSyncStageDB\"\n$sqlquery3='Use\
                \ [AzureSyncStageDB]\nGO\nEXEC sys.sp_cdc_enable_db\nGO'\nInvoke-Sqlcmd\
                \ -ServerInstance $SQLServer -Database $dbname -Query $sqlquery3\n\
                $sqlquery4='\nuse [master]\nGO\n--Set the recovery model to full for\
                \ dms_sample - required for replication\nALTER DATABASE AzureSyncStageDB\
                \ SET RECOVERY FULL WITH NO_WAIT\nGO\n--Configure this SQL Server\
                \ as its own distributor\nexec sp_adddistributor @distributor = @@SERVERNAME\n\
                exec sp_adddistributiondb @database = N''distribution'', @data_folder\
                \ = N''C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\\
                MSSQL\\DATA'', @log_folder = N''C:\\Program Files\\Microsoft SQL Server\\\
                MSSQL14.MSSQLSERVER\\MSSQL\\Data'', @log_file_size = 2, @min_distretention\
                \ = 0, @max_distretention = 72, @history_retention = 48, @security_mode\
                \ = 1\nGO'\nInvoke-Sqlcmd -ServerInstance $SQLServer -Database $dbname\
                \ -Query $sqlquery4\n$sqlquery5='--Change context to the distribution\
                \ database\nuse [distribution] \nGO\n--Configure replication\nif (not\
                \ exists (select * from sysobjects where name = ''UIProperties'' and\
                \ type = ''U '')) \ncreate table UIProperties(id int) \nif (exists\
                \ (select * from ::fn_listextendedproperty(''SnapshotFolder'', ''user'',\
                \ ''dbo'', ''table'', ''UIProperties'', null, null))) \nEXEC sp_updateextendedproperty\
                \ N''SnapshotFolder'', N''C:\\Program Files\\Microsoft SQL Server\\\
                MSSQL14.MSSQLSERVER\\MSSQL\\ReplData'', ''user'', dbo, ''table'',\
                \ ''UIProperties'' \nelse \nEXEC sp_addextendedproperty N''SnapshotFolder'',\
                \ N''C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\\
                MSSQL\\ReplData'', ''user'', dbo, ''table'', ''UIProperties''\nGO\n\
                exec sp_adddistpublisher @publisher = @@SERVERNAME, @distribution_db\
                \ = N''distribution'', @security_mode = 1, @working_directory = N''C:\\\
                Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\MSSQL\\\
                ReplData'', @trusted = N''false'', @thirdparty_flag = 0, @publisher_type\
                \ = N''MSSQLSERVER''\nGO'\nInvoke-Sqlcmd -ServerInstance $SQLServer\
                \ -Database $dbname -Query $sqlquery5 \n</powershell>\n"
          commands:
            '01-run-script':
              command: PowerShell -Command "C:\\cfn\\scripts\\script.ps1" 1> NUL
    Properties:
      IamInstanceProfile: !Ref 'SQLServerInstanceProfile'
      ImageId: !Ref 'AmiID'
      KeyName: !Ref 'KeyPairName'
      SubnetId: !Ref 'sub1Public'
      InstanceType: !FindInMap
        - EnvironmentToInstanceType
        - !Ref 'EnvironmentType'
        - InstanceType
      SecurityGroupIds:
        - !Ref 'StagingSQLSecuritygroup'
      Tags:
        - Key: Name
          Value: !Join
            - '-'
            - - !Ref 'EnvironmentType'
              - SQLServer
      UserData: !Base64
        Fn::Sub: "<script>\ncfn-init.exe -v --stack ${AWS::StackName} --resource SQLServerInstance\
          \ --region ${AWS::Region}\ncfn-signal.exe -e %errorlevel% --stack ${AWS::StackName}\
          \ --resource SQLServerInstance --region ${AWS::Region}\n</script>\n"
  ReplicationInstanceSubnetGroup:
    Type: AWS::DMS::ReplicationSubnetGroup
    Properties:
      ReplicationSubnetGroupDescription: !Sub '${AWS::StackName} DMS Subnet Group'
      ReplicationSubnetGroupIdentifier: dms-replication-subnet-group
      SubnetIds:
        - !Ref 'sub1Private'
        - !Ref 'sub2Private'
        - !Ref 'sub3Private'
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-dms-replication-subnet-group'
  ReplicationInstance:
    DependsOn:
      - DbClusterWaitCondition
    Type: AWS::DMS::ReplicationInstance
    Properties:
      AllocatedStorage: !Ref 'ReplicationInstanceAllocatedStorage'
      AllowMajorVersionUpgrade: false
      AutoMinorVersionUpgrade: false
      MultiAZ: false
      PubliclyAccessible: false
      ReplicationInstanceClass: !Sub '${ReplicationInstanceClass}'
      ReplicationInstanceIdentifier: !Sub '${AWS::StackName}-replication-instance'
      ReplicationSubnetGroupIdentifier: !Ref 'ReplicationInstanceSubnetGroup'
      VpcSecurityGroupIds:
        - !Ref 'DMSInstanceSecuritygroup'
  DMSEndPointSource:
    DependsOn:
      - ReplicationInstance
    Type: AWS::DMS::Endpoint
    Properties:
      DatabaseName: !Ref 'AzureSyncStageDBName'
      EndpointType: source
      EngineName: sqlserver
      Password: !Ref 'StagingSQLPassword'
      Port: !Ref 'MsSqlPort'
      ServerName: !GetAtt 'SQLServerInstance.PrivateDnsName'
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-DMSEndpointSource'
      Username: azuresqlagent
  DMSEndPointTarget:
    DependsOn:
      - DMSEndPointSource
    Type: AWS::DMS::Endpoint
    Properties:
      DatabaseName: dbo
      EndpointType: target
      EngineName: aurora-postgresql
      Username: !Join
        - ''
        - - '{{resolve:secretsmanager:'
          - !Ref 'secretClusterMasterUser'
          - :SecretString:username}}
      Password: !Join
        - ''
        - - '{{resolve:secretsmanager:'
          - !Ref 'secretClusterMasterUser'
          - :SecretString:password}}
      Port: !Ref 'PostgresPort'
      ServerName: !GetAtt 'dbCluster.Endpoint.Address'
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-DMSEndpointTarget'
  DmsReplicationTask:
    Type: AWS::DMS::ReplicationTask
    Properties:
      MigrationType: full-load-and-cdc
      ReplicationInstanceArn: !Ref 'ReplicationInstance'
      SourceEndpointArn: !Ref 'DMSEndPointSource'
      TargetEndpointArn: !Ref 'DMSEndPointTarget'
      TableMappings: '{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name":
        "1", "object-locator": { "schema-name": "dbo", "table-name": "%" }, "rule-action":
        "include" } ] }'
      Tags:
        - Key: Name
          Value: !Sub '${AWS::StackName}-ReplicationTask'
  DbClusterWaitCondition:
    Type: AWS::CloudFormation::WaitCondition
    Properties:
      Count: 0
      Handle: !Ref 'DbClusterWaitHandle'
      Timeout: '1'
  DbClusterWaitHandle:
    DependsOn:
      - dbCluster
    Type: AWS::CloudFormation::WaitConditionHandle
    Properties: {}
Outputs:
  VpcId:
    Description: Environment VPC
    Value: !Ref 'vpc'
  ClusterName:
    Description: Cluster Name
    Value: !Ref 'dbCluster'
  ClusterEndpoint:
    Description: Aurora Cluster Endpoint
    Value: !GetAtt 'dbCluster.Endpoint.Address'
  SQLServerPublicDNS:
    Description: Public DNS of EC2 instance
    Value: !GetAtt 'SQLServerInstance.PublicDnsName'
  SQLServerPrivateDNS:
    Description: Private DNS of EC2 instance
    Value: !GetAtt 'SQLServerInstance.PrivateDnsName'
  SQLServerElasticIP:
    Description: Elastic IP assigned to EC2
    Value: !Ref 'SQLServerEIP'
  AuroraCluster:
    Description: Elastic IP assigned to EC2
    Value: !Ref 'dbCluster'
  SecretArn:
    Description: Database Credentials Secret ARN
    Value: !Ref 'secretClusterMasterUser'
  TargetEndPointArn:
    Description: Target Endpoint ARN
    Value: !Ref 'DMSEndPointTarget'
  SourceEndPointArn:
    Description: Source Endpoint ARN
    Value: !Ref 'DMSEndPointSource'