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:
- are there better ways of achieving what we want
- 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.
Best Answer
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:
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).