0

At my workplace we have a requirement to have extra indexes on the replication server (we replicate data only). This is because we a bunch of reporting that runs off the replication server so some more indexes will help this run faster. These indexes would slow down actual transactions for our customers so we don't want them on the live DB.

I'm not an expert with databases and it's my first job, and so I'm turning here for a bit of advice, since I don't know whether my workplace is headed down the right path.

I know this is a somewhat broad question, but what I would like to know is:

  1. are there better ways of achieving what we want
  2. is what we want a really bad idea and we should avoid it

I am happy to implement what my workplace has asked me to do, but thought it might be better to seek some wisdom from people who have a lot more experience.

1
  • 1
    Have you defined a good set of indexes that will help the reporting queries? Have you measured how much they slow down the transaction workload? Tough to know if this is a bad idea or if there are better ways if we have no way to know if what you've been told to do will be disastrous, good enough, or minimal impact. Feb 3 '15 at 3:01
3

This is a common requirement. Transactional Replication is typically used to off-load reporting to another server/instance, which can be near real-time in a best case scenario. One of the benefits of Transactional Replication is you can place different indexes on the subscriber(s) to optimize reporting. Another benefit is that you can choose to replicate only a portion of the data if only a subset is needed for reporting.

So to answer both of your questions:

  1. You are taking the correct approach.
  2. This is a common requirement and is not a bad idea at all.

Just keep in mind that the indexes that you place on the subscriber(s) will need to scripted out and included in a post-snapshot script in case you ever reinitialize the subscriber(s).

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.