-
Title
Indexes rebuild operations are not properly replicated -
Resolution
Applies to:
Brief description:
Replicating “Rebuild index” DDL operation (creating a redo script) with ApexSQL Log will:
- Create phantom insert statement in transaction log auditing results and apply them to replica
- Report job failure when tables with no primary keys are affected
Summary:
When ApexSQL Log is used for transactional replication of a SQL Server database to another “Subscriber” database, 1:1 Replication cannot be achieved and “phantom” insert statements are shown and replicated to a Subscriber database which can make imperfect replication of a transaction log database.
ApexSQL Log does not differentiate between these phantom insert operations and true inserts and it is not possible to exclude them from the replication job, not even in cases when replication is handled manually.
Furthermore, if database tables included in the transactional replication do not have set primary key on any columns, rebuild index operation will show as failed in the replication summary.
Resolution:
Due to the insufficient information in the transaction log files and ApexSQL Log limitations to identify phantom insert operations accompanying “Rebuild index” operations during the replication jobs, it is strongly suggested to exclude Rebuild index operations from any transactional replication jobs to avoid any potential de-sync issues.
Nevertheless, if “Rebuild index” operations are replicated in some cases, the following limitations/issues should be kept in mind:
- Phantom insert operations which come from the rebuild index task will be included in the replication
- Job will report failed processing if replicated tables are missing primary keys
- Performing rebuild index task will not yield exact same result on the production and subscriber tables due to the fact that the fragmentation can never be the same between 2 SQL Server database topographies