May 21, 2004 at 2:33 pm
We have two Web apps (one for Order entry and one for Reporting) that both point to the same database. I would like to split these into two databases on different servers (each app pointing to it's respective DB).
The company wants real time (or damn near) reporting. How can I setup so that the servers synch or replicate without locking out users from the Order Entry system?
May 21, 2004 at 3:37 pm
I am facing a similar situation.
I have proposed three options : Log shipping, DTS and Transactional replication.
Log shipping is a batch update as frequent as required, however, the standby won't load logs whilst being connected to by reports ( or any process) so a period of non activity is required for queued logs to be loaded ie. for the latency to catch up. If you are already dumping logs then there is little overhead in this deployment apart from copying over the network to alternative server.
DTS could be deployed to extract data regularly to populate the reporting database. This would give you control over the format of the reporting database should you require some de-normalisation to aid report performance. This will add an overhead to your source database as the DTS package will be interrogating the database at data page level.
Transactional rep will keep your reporting database very close to the source even while reports are occuring, ie no quite time required for catchup. The downside is the source schema can't be altered if there are publications configured against tables. If database doesn't change that often structurally then this could be a suitable option depending on volume on transactions, availabilty of primary keys and knowledge of replication as things like autogenerated numbers require extra scripting.
I believe my users will opt for a log shipped database as they can take the hit on latency and will make available som time for the logs to catch up. This is simplest setup.
Hope this is of some help.
Rick.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply