Is there a way to replicate a SQL Server database real time without primary keys defined?

I understand we can't use the SQL transactional replication because of lacking primary keys. Is there any 3rd party tool that can do that?

  • do You have any single reason - do not have PK? any 3rd party tools need way for identify the row. just add auto increment columns and make them PK
    – a_vlad
    Dec 5 '16 at 13:57
  • You can use triggers (after insert,delete,update) ,but why not use primary keys and replication Dec 5 '16 at 14:18

It is unlikely that any replication solution you can import would work without a PK (or at least a unique index) otherwise it can't tell the difference between an update and a delete+insert (amongst other problems) which could be quite a significant issue depending on what keys and triggers exist on or refer to the affected table(s).

If you can't add a PK or other unique index then you will need to try write your own synchronisation solution for the situation.

Is there any reason why the table(s) can't have a primary key?


There are 3rd party replication programs that allow you to not use a 'real' PK, but instead let you create one within the application. It will use that as rows flow but, without it being enforced on both systems by the source and destination DBMS, you run the risk of missing rows, performing DUI ops on incorrect rows, not being in sync and possibly taking all of your replications down.

DBMoto by HiT Software will let you create a 'soft' PK. As will Starquest Data Repliator (SQDR) by Starquest. They are both not free and are enterprise-level replication solutions.

I do not work for either company but have used both in my environment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy