1

Just a preface that I'm working at the moment as an accidental DBA so sorry for any ignorance.

I need to completely rebuild one of our transactional replications. The indexing strategy is different on the subscriber database than it is on the publisher, the primary key is the same but the clustered index on NC indexes are different. I am aware that the tables by default are completely dropped on the subscriber. I believe I can use scripts pre and post snapshot application to drop and create indexes.

Both the clustered indexes on the subscriber and publisher are UniqueIdentifier but different columns. The table has nearly 1 billion rows and is sadly pretty wide. Just thought that I'd provide that information in case it makes a difference.

My questions are:

  • Is using the post execution scripts the best way to go about this?
  • If the clustered index is different on the subscriber than the publisher am I best to allow the bulk insert to happen with the publisher clustered index or would it be better to set the alternate clustered index before the bulk insert?

Thanks in advance for any help.

2 Answers 2

1

At my last job this is exactly what we did for a very similar scenario (also on SQL Server 2008 R2 - Standard Edition), and I was the one to build it out. We had an off the shelf vendor application we heavily used and needed to report out of. It was indexed differently than how we were reporting out of it.

We had one way transactional replication setup to sync from the vendor app to our report server. I setup pre and post scripts to drop and recreate the appropriate indexes (and some other relevant entities like indexed views), which of course checked for object existence first too (you can check this via the sys schema's tables and views) before executing.

The scripts themselves just pointed to a stored procedure where all the logic lived, on the subscriber server. This just felt more natural for maintaining the code than updating an external script file. The pre script to drop the custom indexes before the schema was synced was important because there was a bug we ran into where transactional replication would occasionally break and stop syncing otherwise. (If you need a code example of my pre and post scripts, let me know and I'll update this answer.)

In regards to your second question, in general it's faster to bulk insert your records into a table and then add your indexes after (this gives SQL server the full picture to arrange the data in the most efficient manner, at one time). To do this with transactional replication, you'll need to de-select "copy clustered indexes" (and non-clustered indexes for that matter) in the Publisher's Properties window under Article Properties for that table. (Letting it sync the data with the Publisher's clustered index is essentially just doubling the work your server will need to do when you re-index it on the Subscriber with your preferred clustered index.)

0

In my opinion, because of the table size the better way is use the strategy truncate table in the publication options and use pre script to create all index before to proceed with the load.

Two other question:

It's work? I mean this replication was synchronized with more the 1 bilion rows? At my work a have problems to replicated table with more the 300 milions rows.

Is this table was partitioned? With this size I really recommended that.

2
  • 1
    Yeah it works. It takes about 6 hours to process the snapshots and then another few hours to rebuild the indexes. Unfortunately we don't have enterprise so I can't partition it unless we did a partitioned view but we often query all over the range so from what I understand a partitioned view would be quite slow. So your suggestion would be to drop the nonclustered indexes, Truncate the table and let it insert with the current clustered index in place then add the nonclustered indexes again? Just making sure I'm clear. Thanks
    – Bee_Riii
    Jun 8, 2017 at 15:16
  • @Bee_Riii I corrected my answer.
    – Krismorte
    Jun 8, 2017 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.