Transactional replication without a primary key (unique index)

Question

I just met something disturbing, I tried to implement transactional replication from a database whose design is not under our control. This replication was about reporting without burdening the system too much. After the replication attempt, only some of the tables have passed.

In the investigation, the tables were not selected for replication, because they do not have a primary key, I thought that this could not even be shown as a primary key if I use ODBC and ms access, but not in the management studio. Also requests are not ridiculously slow.

I tried to insert a duplicate entry, and it did not report a unique index (and not a primary key). It seems that the tables were implemented using a unique index as against the primary key. Why I don’t know that I can scream.

In any case, to perform transactional replication or an alternative, it must be live (last minute or two). The main db server currently is sql 2000 sp3a and report server 2005.

The only thing I'm trying to try right now is to set up replication, as if it were a different type of database. I believe that replication, in order to say that the oracle is possible, will force it to use, say, the ODBC driver, as I assume that access uses, therefore, shows the primary key. I do not know how accurate it is from my depth.

Answer 1

As MSDN states, it is not possible to create transactional replication in tables without primary keys. You can use Merge replication (one way), which does not require a primary key, and it automatically creates a rowguid column if it doesn’t exist:

Merge replication uses a globally unique identifier (GUID) to identify each row during replication merge. If the published table does not have a unique column identifier with the ROWGUIDCOL property and a unique index, adding replication is one. Make sure that all SELECT and INSERT statements published by tables use column lists. If the table is no longer published or duplicated, a column is added, the column is deleted; if the column already exists, it is not deleted.

Unfortunately, when using merge replication, you get a performance penalty.

If you need to use replication for reporting only, and you don’t need the data in the same way as the publisher, then you can also consider snapshot replication