This is something that I never thought to blog about as I figured it was a common practice. I’ve no idea where I got that idea from as I’d never spoken to anyone about it, just used it within a couple of environments.
Anyway, having spoken to a couple of MCMs about this it turns out my “common practice” is actually incredibly rare and not many people know about it. Therefore, presuming this to be true, I figured I should blog about it.
In a nutshell, this is the best method I can think of to ensure you run the best indexing strategy for your transactionally replicated environment.
Basically the setup I’m considering here is as follows:
This is a heavy workhorse of a machine. It receives all your data from numerous sources, crunches it and inserts the output into two or three transactionally published databases. The inserts need to be a quick as possible and therefore indexes are kept tuned to aid inserts but to a minimum to reduce overhead.
Houses the replicated databases and uses them for reporting purposes. These deliver content directly to webservers and therefore require heavy and complex indexing strategies with more focus on output rather than insert and update overhead.
Note that the role of the Distributor is irrelevant for this action and therefore I’ve not included it.
Therefore, as you can see, there is a vast difference in the indexing strategies required by the two ends of the replication set.
Prior to having any DBA on site, the company I was dealing simply placed ALL indexes on the Publisher.
This was a simple solution which meant that all indexes were present on the Subscriber and therefore clients were happy… however, this was definitely hindering the productivity of the Publisher as it dealt with the overhead of numerous indexes on each table into which it was trying to load data.
After employing a DBA on site this improved somewhat as they used the built in replication facility to have replication automatically fire off a SQL Script after initialisation.
This solved the problem as it meant that they no longer needed the excessive indexes on the Publisher and got the correct ones on the Subscriber.
The only downside to this was maintenance. Any changes made to indexes, tables, columns etc. all meant that the script needed to be edited accordingly and there were times this was being missed and causing replication failures on re-initialisation. Not ideal.
Also this file needs to be in a secure and backed up location otherwise you run the risk of losing the whole thing in the event of a disaster on the Subscriber site.
As an alternative, I’ve always dealt with this by utilising Disabled Indexes.
There aren’t many uses for a disabled index and I’ve heard people saying that they’re pretty much a useless feature as disabling an index effectively drops it and only maintains the definition. Nothing more.
However, in a replication environment I love them.
With transactional replication, if you create an index on the Publisher, disable it, and then replicate the table… SQL Server creates the table on the subscriber and then creates the index… enabled.
Therefore what I tend to do is to wait until there’s a quiet time on the Publisher and then create the indexes required by the Subscriber, then disable them.
This means that whenever I add a new subscriber or re-initialise an existing one, the indexes are automatically dealt with by SQL Server. And with the publisher being backed up regularly there is a much less likelihood of index definition loss in the case of an emergency. Also, because SQL uses bulk operations to transmit data, the indexes are also created considerably faster than in a separate script.
All in all, it’s a fantastic use for the lesser appreciated Disabled Index.