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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.