StarQuest Technical Documents

Preserving Non-Unique Indexes for SQL Server Destinations

Last Update: 12 July 2016
Product: StarQuest Data Replicator
Version: SQDR 4.6 or later
Article ID: SQV00DR035

Abstract

This tech note describes stored procedures which can be installed on a SQL Server destination database to preserve existing non-unique or nonclustered indexes for replicated tables. The procedures are installed by executing SQL scripts and may be modified to change the schema associated with each procedure. After the procedures are created, any subscription may be configured to use them. The result is that all non-unique indexes on the destination tables are rebuilt at the conclusion of baselines.

There are three stored procedures included in this package:

SQDR.DISABLEINDEXES - Disables all NONCLUSTERED indexes for a table
SQDR.REBUILDINDEXES - Rebuild all NONCLUSTERED indexes for a table
SQDR.STATUSINDEXES - Check the status of indexes for a table

Solution

1. Add destination schema "SQDR" using SQL Server Management Studio or execute the following dynamic SQL:
   create schema SQDR
   If you prefer to use a different schema for the procedures, you can modify the installation scripts:
   from: CREATE PROCEDURE SQDR.DISABLEINDEXES
   to: CREATE PROCEDURE MY_SCHEMA.DISABLEINDEXES
2. Using SQL Server Management Studio or dynamic SQL, create the stored procedures by executing the scripts disableidx.sql,  rebuildidx.sql and statusidx.sql (optional).
3. Configure the subscription to “Use existing table for baseline”:

4. Add Destination Before and After Processing statements for the subscription:

5. Save the subscription and run the baseline.

Syntax for executing the stored procedures:

EXEC SQDR.<stored_procedure_name> "<schema>", "<table_name>"

Examples:

• To Disable Indexes:
  EXEC SQDR.DISABLEINDEXES "dbo", "mytable"
  
• To Rebuild Indexes:
  EXEC SQDR.REBUILDINDEXES "dbo", "mytable"
• To Check Status of Indexes:
  EXEC SQDR.STATUSINDEXES "dbo", "mytable"
  This returns the unqualified name of the index(s) and 0=false or 1=true to indicate if the index(s) is disabled.