Home > Replicate Index on Partition Schemes at Subscriber, SQL Server Replication > Replicate Index on Partition Schemes at Subscriber DB (Transactional Replication)

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

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.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: