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?
Best Answer
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.