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.
- MS SQL 2008 – Transactional Publication failure – The process could not execute ‘sp_replcmds’ on ‘SERVERNAME’
- Replication doesn’t start after reinitializing in MS SQL 2008 R2
- SQL Server 2008 – Performance impact of transactional replication?
- SqlServer2005 Enterprise Fast Recovery, SqlAgent Availability, and Replication
- How To Simulate Publication Replication In SQL 2005 Express?