Unanswered: Transactional replicaton without primary key (Peoplesfot database)
We are trying to implement transactional replication on our Peoplesoft database to create new reporting instance.
Unfortunately, Peoplesoft is enforcing referential integrity using unique indexes instead of explicitly defining primary key constraints.
This works in general, but Microsoft replication by design requires primary key constraint on every replicated table.
Any suggestions or ideas?
I have seen post on Oracle discussions where same question is asked, so they are in the same boath. However, answer was to use Data Mover which is really only a tool to move a data around and/or execute scripts (like Query Analyzer and OSQL/BCP), but is nothing to do with real time replication.
1) change a unique key on each replicated table, and change them to a primary key (unadvisable, due to upgrade concerns).
2) Implement log-shipping to a standby database in read-only mode. This will require Enterprise Edition of SQL, but a Peoplesoft database will probably already require that.
You are right, if we replace unique indexes with primary key constraints we will get into the support and upgrade issues.
Log shipping really does not provide viable solution (it's not almost real time like transactional replication and database is not available during log restore).
Is is possible at all to implment tran replication without having primary key constraints defined? I am not sure what are Microsoft's reasons to require primary key and how strict that requirement is (because it is to some extent making replication less usable).
Most of the big app vendors tend to be platform independent.
Transactional Replication with Indexed Views
An alternative to adding a row filter at the article level is to publish an indexed view based on the same WHERE clause that the filter would use. When you use an indexed view rather than a row filter, the Log Reader Agent does not need to evaluate statements against filters because the article—which is now the indexed view, rather than the base table—is already filtered. However, the overall performance of the Log Reader Agent is still slower than an article without filters, because every modification performed on a table with an indexed view is logged twice, once for the indexed view and again for the table itself. This doubles the number of log records the Log Reader Agent must traverse, which affects Log Reader Agent performance.
The cost of maintaining the indexed view at the Publisher can be high, so indexed views work best if the underlying data is infrequently updated. If the underlying data is frequently updated, the cost of maintaining the indexed view data may outweigh any performance benefits of using the indexed view. Indexed views usually do not improve performance under the following scenarios: OLTP systems with many writes; databases with many UPDATE operations; or using queries that do not involve aggregations or joins.
The following table shows the results of a test comparing execution times on identical tables with and without indexed views. No replication was involved in this test. The indexed view was: