You would be now thinking what is the relation with re-initialization and Non Clustered Indexes. Well, We all know that Indexes are super critical for faster retrieval of data and what would happen upon re-initialization of a subscription if all your Non Clustered indexes at your subscriber database goes off?
How many times did you have the need to do a re-initialization of a subscription? And how many times have you checked the performance of queries at the subscriber database? Well I had this problem many times now and my systems team starts shouting that your replication broke the website. It is a easy setting which gets overlooked.
By default, If you check an article property you can find that “Copy nonclustered Indexes” is set to false. You can also notice that “Copy clustered indexes” is set to true. What happens when you do a re-initialization is that when the snapshot is re applied at the subscriber database none of the non clustered indexes would get copied over. Now this is enough to cause you performance issues at the subscriber end.
What can be done now? It is easy for you that when ever you try to do a re-initialization just before that change this option back to “True”. This will make sure that the non clustered indexes are copied over.
Upon creation of the snapshot , If you check the table_name.idx file in the latest replication snapshot folder you will now see that it also contains the statement for the non clustered index. But if you didn’t change the property check the corresponding snapshot folder it would not have the create nonclustered index statement.
The NonClustered index creation step just right after the Clustered index script is now seen in the .idx file.
Now do we have to worry about anything here?
The answer is NO if you have only one subscription. And the answer is YES if you have multiple subscriptions and you only need to re-initialize one particular subscription. What happens is changing the property to true causes the snapshot to be invalid which causes it to re-initialize all the subscriptions that you have. You get a warning message when you change the property and then click Ok. Once you click Yes below, it causes all your subscriptions to be re-initialized.
In my environment, I have subscriptions to production databases as well as test databases from the same publication. Now If I do this change on the subscription to test database it will still effect my production as well which is not a wise thing to do especially during production hours.
So, what can we do is if we have multiple subscriptions then it is better to leave the option as default ie False. Once the snapshot is applied just script out all the non clustered indexes from the publisher database and then create it in the subscriber db. You don’t have to create the clustered indexes as it will be by default copied during snapshot. Now if you have indexes in the subscriber database which are different than the production , then you should script it before you start re-initialization.