I have a situation where a web application needs to obtain hourly updates as
to inventory levels from second system. There is also a once-a-day update w
here new catalog products that have been initially entered into the second s
ystem need to be automatica
lly pulled and inserted into the web application where they must be massaged
and made ready for presentation on the web site. Both systems have MS SQL S
erver backends.
My question is ... what is the most common, simplest, most reliable, uses le
ast overhead ... (best) ... way to go about this?
Is this what Subscriptions are for? Stored Procedures?
Thanks for your advice,
Chip DukesChip,
you could use DTS or replication or linked servers.
If DTS, you would have 2 packages, each scheduled.
If replication, then the hourly movement would be transactional, and
once-a-day process would be transactional with a transformable subscription
(which amounts to using DTS behind the scenes anyway). Alternatively, the
second process could be plain transactional and (indexed) views used on the
subscriber to massage the output.
As a third posibility you could use linked servers and scheduled jobs.
My preference would be transactional replication. This has a series of
alerts to maintain notification of the process success/failure, and the
distribution database can easily be queried at any time to see which
transactions are waiting in the 'pipeline', ready to be delivered, so
troubleshooting is not difficult. The only stipulation is that you have
primary keys on your table articles, but I doubt that would be an issue.
HTH,
Paul Ibison
