I've got two SQL 2005 servers, one OLTP [let's call it SQLA] and one used for reporting [SQLB] to reduce load on SQLA when people run crazy queries. Our main database is replicated from SQLA to SQLB with transactional replication.

The nature of the queries we run on SQLB are obviously very different, and therefore our index requirements are different too.

Are there any issues with creating more indexes on SQLB, given that the database is replicated from SQLA?

My main concern is that I may see a performance hit on SQLA as the data is replicated to SQLB and the extra indexes are updated- my lack of understanding about the Log Reader Agent is the problem here, apologies if this is a really basic question.



I can see why you'd have this concern but you shouldn't need to worry about it. The load on SQLA should not change in this scenario. The Log Reader Agent is running on a third machine correct? You have SQLA (Publisher) running, a (Distributor) running Log Reader Agent and the (Subscriber) SQLB receiving the updates. SQLA will still write the exact same transaction logs, the Distributor will still submit to SQLB the exact same insert, update, delete. The work of actually adding it to the various indexes will fall squarely on SQLB.

Here's a good diagram of the process: http://i.msdn.microsoft.com/dynimg/IC19107.gif

The only thing you should watch is that SQLB doesn't fall behind by having too many indexes to perform the inserts/updates/deletes in a timely manner, causing them to build up in the distribution database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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