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.

How to get Publisher Details from Subscriber

Working with different versions of sql servers i came across with an issue,How to get Publisher information from Subscriber.

To get to know I ran sql profiler for couple of time and captured an undocumented stored procedure which helped in getting the information I needed about the publisher.

exec sp_MSenumsubscriptions ‘both’

The stored procedure can be found in subscribed database.

The same can be achieved from table MSreplication_subscriptions, can found in subscribed database.

SELECT * FROM MSreplication_subscriptions