Hemant Borole is a big data consultant at Amazon Web Services.

AWS Database Migration Service (AWS DMS) helps you migrate databases to AWS easily and securely. AWS DMS supports homogenous migrations such as Oracle to Oracle, as well as heterogeneous migrations between different database platforms, such as Oracle to Amazon Aurora.

For customers who use multiple AWS accounts, the source and target databases may exist on different accounts. For AWS DMS to work with source and target databases in different accounts, it must be able to communicate across accounts.

The following are some of the options you can use to enable cross-account AWS DMS communication:

Making database endpoints accessible over the Internet

Virtual Private Cloud (VPC) peering

Whitelisting the AWS DMS public IP in the security group

Exposing database endpoints over the Internet is not a secure option because it makes the databases vulnerable to security attacks.

VPC peering is a commonly adopted practice. A VPC is a virtual network (block of CIDR) that is allocated to an AWS account. VPC peering allows two VPCs to connect as if they are on the same network. However, this means that both VPCs must have CIDR blocks that do not overlap. Customers might have compliance restrictions that prevent the VPCs from peering.

Solution overview

The use case discussed in this blog post requires the customer to continuously replicate data changes on Amazon Aurora to Amazon Redshift. We use AWS DMS to achieve this replication. AWS DMS uses replication endpoints to migrate data from a source endpoint to a target endpoint. The source and target endpoints can reside in the same Amazon account or in different Amazon accounts. (The setup of the databases is not in the scope of this post.)

AWS DMS uses a replication task to manage replication between the source and the target endpoint. You can configure the replication to be a one-time migration or an ongoing replication. The task also can restrict the replication to certain schemas and tables from the source database. With the AWS DMS task settings, you can configure schemas and tables to be included or excluded from replication.

The AWS DMS task uses rules mapping, which is a JSON definition that allows you to configure this mapping. Here is an example of the rules mapping.

{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-action": "include", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-name": "1" } ] }

The solution described here enables AWS DMS to communicate across accounts without VPC peering. The customer’s environment consists of two accounts. Account A hosts the Amazon Redshift database cluster as a target for AWS DMS. Account B hosts the Amazon Aurora database cluster as the source.

You can find information about setting up these databases in Getting Started with Amazon Redshift and Getting Started with Amazon Aurora. With Amazon Aurora, ensure that you configure the cluster parameter group to allow for replication. This is a prerequisite for Amazon Aurora to work for replication.

Currently, AWS DMS requires that the Amazon Redshift cluster run in the same account as itself. We enable AWS DMS in account A to communicate with Amazon RDS in account B by whitelisting the public IP address of AWS DMS in the Amazon Aurora inbound security group rules.

It’s possible that AWS DMS is running in a private subnet configured with a network address translation (NAT) gateway. So, we have to whitelist the public IP of the NAT gateway in the inbound rules of the Amazon Aurora security group in account B.

If AWS DMS is publicly accessible, we have to whitelist the public IP of AWS DMS in the inbound rules of the Amazon Aurora security group in account B. If there is no NAT gateway set up on the subnet, the public IP of AWS DMS can be whitelisted on the inbound security group rules of the Amazon Aurora (account B) security group.

AWS DMS communicates via TCP. By whitelisting all TCP rules on the Amazon Aurora inbound security group for Amazon Aurora clusters port (default 3306), we enable AWS DMS to communicate with Amazon Aurora.

The next section describes in detail the components of the architecture in both accounts. This post focuses more on the cross-account setup than the AWS DMS setup itself.

Architecture

The high-level architecture is captured in the following diagram.

When you have this set up, you can run your DMS task and see the replication working.

Account A setup

Account A consists of AWS DMS, Amazon Redshift, and Amazon SNS. AWS DMS mainly needs a replication instance, replication endpoints, and a replication task. The replication instance is an Amazon EC2 instance that performs the actual data migration between source and target database.

To automate the whitelisting of the AWS DMS (or NAT gateway) public IP address in the inbound security group for the Amazon Aurora cluster in account B, we can use the replication instance creation event as a trigger. For more details about this automation, see the Lambda function information for account B later in this section.

After the whitelisting is done, the DMS task in account A can be initiated by another Lambda function (not discussed here) that is triggered when the Lambda function completes the update to the security group in account B.

We enable SNS notification on the replication instance creation event to notify account B that it can trigger a Lambda function to update the security groups on the Amazon Aurora inbound connections in account B. We use Boto for this process. Boto is a Python library that provides you with an easy way to interact with and automate using Amazon Web Services.

The following sample Boto code creates an SNS topic in account A with cross-account permissions to account B. This code snippet shows that the SNS topic allows Subscribe, ‘ListSubscriptionsByTopic, and Receive permissions for account B.

topic = sns_client.create_topic(Name = topic_name) #try: sns_client.add_permission( TopicArn=topic['TopicArn'], Label='lambda-access-' + account_b, AWSAccountId=[ account_b ], ActionName=[ 'Subscribe', 'ListSubscriptionsByTopic', 'Receive' ] )

We can now identify the IP addresses of our NAT gateways using the following Boto code:

nat_ips = [] nats = ec2_client.describe_nat_gateways() while True: gws = nats['NatGateways'] for gw in gws: if gw['State'] == 'available': for nat in gw['NatGatewayAddresses']: nat_ips.append(nat['PublicIp']) if 'NextToken' not in nats: break else: nats = ec2_client.describe_nat_gateways(NextToken = nats['NextToken'])

Next, we can send a message with a list of updated IPs to the Amazon SNS topic in account A. Then the Lambda function in account B can extract these IPs from the event object to update the Amazon Aurora inbound security group rules. The following code shows how you can create an SNS publish message with the list of IP addresses in the message.

msg = {} nats = get_nats() nats.append(instance_public_ip) msg["nats"] = nats msg["cluster_id"] = cluster_id sns_client.publish( TopicArn=topic_arn, Message = json.dumps(msg), Subject='DMSInstanceCreation', MessageStructure='raw' )

Account B setup

Account B consists of an AWS IAM role, an AWS Lambda function, and the Amazon Aurora cluster. IAM enables you to securely control access to AWS services and resources for your users. We need an IAM role for our Lambda function so that the function can assume an Amazon RDS role to access its security group. This allows the Lambda function to identify the Amazon RDS cluster whose security group needs to be updated.

The following code example shows the AWS Lambda assume role.

trust_relations = { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "lambda.amazonaws.com" }, "Action": "sts:AssumeRole" }, { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole" } ] }

The Lambda function also requires authorization to update the security group for Amazon Aurora cluster inbound rules, to add the public IP of AWS DMS’s replication instance (or NAT gateway). So we must add a policy allowing the ec2:AuthorizeSecurityGroupIngress action to this role. The AWS EC2 authorize policy is as shown here.

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "logs:CreateLogGroup", "logs:CreateLogStream", "logs:PutLogEvents", "ec2:AuthorizeSecurityGroupIngress", "iam:Get*", "iam:CreatePolicy", "rds:DescribeDBClusters" ], "Resource": "*" } ] }

The following code snippet from the Lambda function shows how to locate the Amazon Aurora cluster’s security group.

clusters = rds_client.describe_db_clusters() while True: for cluster in clusters['DBClusters']: if cluster_identifier == cluster['DBClusterIdentifier'] \ and 'available' == cluster['Status'] : sgs = [] for vpcs in cluster['VpcSecurityGroups']: if 'active' == vpcs['Status']: sgs.append(vpcs['VpcSecurityGroupId']) return sgs ## Found the required security group. if 'Marker' not in clusters: return None else: clusters = rds_client.describe_db_clusters(Marker = clusters['Marker'])

After we identify the security group for the Amazon Aurora cluster, we can update its inbound rule to allow traffic that the IP address received via the Amazon SNS event that is passed in the Lambda function handler. The following code demonstrates the update to the inbound rules of the Amazon Aurora cluster’s security group.

sg = ec2_client.SecurityGroup(sg_id) sg.authorize_ingress( IpProtocol = 'tcp', FromPort = 0, ToPort = 65535, CidrIp = '' + nat + '/32' )

Verify replication

After you have set this up, you can run your DMS task and see the replication working. To validate this, connect to your Amazon Redshift instance and execute the following command to test the absence of a schema—let’s call it “test_schema.”

# \d test_schema.* Did not find any relation named "test_schema.*".lation named "test_schema.*".

Now let’s connect to the Amazon Aurora instance and create the test schema with a sample table containing data.

mysql> create schema test_schema Query OK, 1 row affected (0.05 sec) mysql> use test_schema Database changed mysql> create table test_table (test_column varchar(32)) Query OK, 0 rows affected (0.11 sec) mysql> insert into test_table values ( "test_data") Query OK, 1 row affected (0.05 sec)

We can set the AWS DMS task to replicate everything, as shown in the rules mapping example earlier. AWS DMS should now have replicated all the data, including the schema and table creation. We can verify this by connecting to the Amazon Redshift instance and querying the test_table.

# set search_path to '$user', public, test_schema SET # select * from test_schema.test_table test_column ------------- test_data (1 row)

The output from the queries should not reflect changes made on the Aurora database as shown in the example.

Conclusion

As I discussed at the beginning of this post, a more appropriate approach is to set up a VPC peering between the two accounts and let AWS DMS handle the replication seamlessly. The approach described here is for scenarios where VPC peering is not an option. AWS DMS makes it easy to migrate and replicate data between various data sources and targets. You can use this approach with other databases that are across accounts—it’s not just limited to Amazon Redshift or Amazon Aurora.

For additional background on this topic, see Set Up Cross-Account Permissions (AWS Lambda Developer Guide).