How to update a database remotely?

Question

I'm looking for a strategy to allow automatic updates for a number of databases at customer sites through a publish-subscribe kind of mechanism. Right now there is a datacenter which has all the master data that get fed through extractions from hundreds of databases out there. The problem is that, whenever I need to do create a new view in the remote customer databases, I have to manually roll out an installation patch and ask the users to run it (their sites are behind firewalls, so I can't remotely do that from my end). Ideally, I would like to have a "DDL image" of the customer database schema at the datacenter, and whenever any change happens to it, all the subscribing customer databases would update their table view codes. The target databases are mostly SQL Server 2005 and Oracle.

I heard the MS SQL replication services could do such a thing? What about Oracle? anybody had experience with such?

Thanks!

Answer 1

Not sure about existing solutions, but how about writing your own auto-update mechanism that would run on a timer on the client machines and pull the latest schemas and views from some service table in your master database? Your change wouldn't get propagated straight away to all sites and some sites would update before others, but they would all eventually see the changes.