Archive for the ‘Replicate Index on Partition Schemes at Subscriber’ Category

Replicate Index on Partition Schemes at Subscriber DB (Transactional Replication)

April 3, 2010 Leave a comment

Here we will go over how we can move the indexes of the Partitioned Publisher database (Data being partitioned on the basis of Clustered or non-clustered index) on the basis of Partition Function and Schemes or on different file groups to the subscriber database.

This is applicable to the environment which has their database partitioned using partitioned function & schema or their data is on different file groups other then Primary.

  1. Publisher database should be restored at the subscriber SQL Server instance with the same name or different.
  2. Subscriber database should have all the file groups, associated secondary data files, partition schema and partition function.
  3. On the step of setting article properties at publisher ,Set the value to TRUE of the below settings
    1. Copy Clustered Index = TRUE
    2. Copy Non-Clustered Index =TRUE
    3. Copy file group associations =TRUE
    4.  Copy partitioning schemes=TRUE
    5. Copy index partitioning schemes=TRUE



Once setting has been done, generate the snapshot by running the snapshot agent or my marking subscription for re-initialization.

After snapshot is generated we can check the generated scripts in the snapshot folder whether index script is properly generated or not with the filegroup or Partition scheme defined.

Now check if the snapshot is properly delivered or not.