2

I have a transactional replication from SQL Server 2008 to RDS SQL Server 2016 in 2014 compatibility mode. This works okay. I now want to replicate this RDS database via DMS to Postgresql. This errors prematurely, the Cloudwatch entries:

Database instance is not enabled for REPLICATION: Applying enablement... (sqlserver_log_queries.c:2752)
2018-08-02T13:09:29 [SOURCE_CAPTURE ]E: Failed to execute statement: '' [1022502] (ar_odbc_stmt.c:4422)
2018-08-02T13:09:29 [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:4428)

The user used for replication has all the possible rights on the database and RDSinstance. Any idea What to try next?

Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
Willem
  • 66
  • 3

1 Answers1

0

Solution: When I first create the replication via the DMS, and only then create the replication to the RDS, it works. BEWARE: in the errorsituation, the database CANNOT BE DELETED. There's a warning that you need sysadmin rights when you try. I had to drop and recreate the instance before i could implement the solution.

Willem
  • 66
  • 3