Setting up Ongoing Replication using AWS DMS and RDS

Question

I'm trying to use AWS Database Migration Service (DMS) to setup "ongoing replication" from an AWS RDS hosted SQL Server 2016 database to an EC2 hosted SQL Server 2016 database. When I create a DMS task to do this, I get an error:

2018-01-17T12:13:24 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 21089 Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Only members of the sysadmin fixed server role can perform this operation. Line: 1 Column: -1 [1022502] (ar_odbc_stmt.c:4406)

According to the documentation, both users used to connect need to be sysadmins. However, it's not possible to do this with AWS RDS SQL Server databases but the documentation suggests that this is a supported scenario. How can I get this to work?

Answer 1

You can use Microsoft SQL Server as a replication source to RDS, but you cannot use SQL Server as the destination.

To setup replication, one side is the distributor and the other the subscriber. For RDS you do not have sysadmin rights, therefore you cannot setup the distribution.

Security Requirements for Replication

Answer 2

You can achieve this with Cloudbasic's RDS SQL Server HA/DR tool available on the AWS Marketplace: https://aws.amazon.com/marketplace/pp/B00OU0PE5M


Launch it in the same AWS VPC as your RDS SQL Server source instance. In the New Replication setup section, ensure to select SQL Server-to-SQL Server replication (as the tool also streams data from SQL Server to Redshift and S3 data lakes).