The Problem

To migrate a SQL Server database to AWS or to implement a data migration/transfer strategy to AWS, a common tool we use is the Data Migration Service (DMS). This is a native AWS tool and supports many different database technologies, including Microsoft SQL Server. However, when connecting to SQL Server, one of the common errors raised is "The Distributor has not been installed correctly".

This problem is raised not only with DMS, but also with other tools such as Qlik. The text below shows detailed errors raised in two different scenarios.

=================================================================================

Stream component 'XXXXXXXX' terminated Cannot initialize subtask Failed while preparing stream component 'XXXXXXXXXX'. RetCode: SQL_ERROR SqlState: 42000 NativeError: 20028 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]The Distributor has not been installed correctly. Could not enable database for publishing. Line: 1 Column: -1

Failed (retcode -1) to execute statement: 'exec sp_replicationdboption @dbname = N'XXXXXXXXX', @optname = N'publish', @value = N'true''

=================================================================================

Last Error Fatal error has occurred Task error notification received from subtask 0, thread 0 [reptask/replicationtask.c:2858] [1022505] Failed (retcode -1) to execute statement; RetCode: SQL_ERROR SqlState: 42000 NativeError: 20028 Message:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The Distributor has not been installed correctly. Could not enable database for publishing. Line: 1 Column: -1; Failed while preparing stream component 'st_0_HLNYCZNDQHRBG57NDT2VEQTTIGCORSR66SZNZEI'.;

Cannot initialize subtask; Stream component 'st_0_HLNYCZNDQHRBG57NDT2VEQTTIGCORSR66SZNZEI' terminated [reptask/replicationtask.c:2866] [1022505] Stop Reason FATAL_ERROR Error Level FATAL

=================================================================================

The problem is, by default DMS and Qlik check for SQL Server replication even if the replication is not enabled in the SQL Server.

The Solution

The resolution for this is simple. We just need to add the below parameter to connection string.

ignoreMsReplicationEnablement=true

Once you have this in the connection string, the connection succeeds with no error being returned or logged.

I couldn't find a good reference documentation for this. However I just found a link in the Qlik documentation, which listed this parameter. But there is no clear explanation for this, either in the AWS documentation for DMS or elsewhere.