sql server replication: Hello folks,

I currently have a "live" DB and a "reporting" DB, both SQL 2005. Each
night a full backup of "live" is restored into "reporting" which gives my
users what they need - access to a copy of "live" with no danger of screwing
up the production service. We do however add a few indexes and views to
"reporting" to better meet the data extraction requirements and, at the
moment, these are built each night after the restore has completed.

It seems to me that publishing "live" and subscribing to it from "reporting"
has to be the way to go. However, I am finding a bewildering amount of
reading material, so would appreciate a pointer from the experts on such
things as:

- Can I have extra views and indexes in "reporting" (if not, it's a dead
end).
- I have many triggers in "live" which, presumably need disabling in
"reporting".
- Which replication model transactional/snapshot?

Then, add to the list all of the things I have not yet thought of!

Advice and/or pointers to useful reading material all welcome.

Thanks, Nick

Hi Nick,
usually transactional involves far less data flow and there won't be any
blocking issues that you might get with snapshot replication, so I'd
recommend this as the way to go for a reporting database. Extra indexes,
views etc can be simply added manually after the initialization but if the
process ever needs to be reinitialized they might get forgotten, so setting
up and maintaining a post-snapshot script is time well spent. There are 2
books which can help on replication (shown on www.replicationanswers.com) and
books online is also a good info source.
HTH,
Paul Ibison

Thanks Paul. Book now ordered and thanks for the pointers.

[quoted text, click to view]