"On demand" push subscription

Hi everyone,

Here is my problem: I need to implement an "on demand" push subscription. I am currently using a transactional replication with subscription agents running continiously. The connection between the publisher and the subscribers is pretty flaky, plus having the agents run continiously utilizes an ennormous amount of network bandwidth. At the same time the data need to be pushed to the subscribers as soon as they get into the publisher, so scheduling would not work either.

The only choice I have is to implement a solution where the distribution agents would be generally disconnected (i.e. disabled) from the subscribers, and would be enabled only when a data change is processd by the publisher.

Where do I start? Should I use triggers, DTS, or anything else? If yes, how? I am open to any suggestions.

Thank you very, very, very much!For the consistency between database why don't you use LOG SHIPPING which works in similar fashion of replication.|||Thank you for your input, Satya! I've investigated log shipping as a possible alternative, but found that it requires to be scheduled at a cerain time. I need a solution that allows an immediate update of the records.

Thank you, any additional ideas are greatly appreciated!

Originally posted by Satya
For the consistency between database why don't you use LOG SHIPPING which works in similar fashion of replication.|||True, its debatable topic between replication and log shipping.

What I mean is if you log shipping and enable Log transfer every 15 minutes between Primary and secondary server which will have easy effect of backup of live database. LS proved and saved a lot of time of mine than replication.

If you're insist to use immediate updations then better to opt for replication and make sure to suffice all the terms before using.

Refer books online for more information on REPLICATION topic.|||To Satya:

Woohoo! I resolved the problem! I have implemented the "on demand" programmatically using C#. The user decides when to actually replicate by simply pressing a button. The command initializes, runs, and terminates all of the necessary distribution agents. This way the agents are deactivated most of the time, and are activated only when the user actually puts in a batch of records and gives a command to replicate. Since the records are put in only once or twice daily, but need to be available everywhere immediately, it is a perfect solution for us. Of course, if the data were put in more often, it would make more sense to leave the agents run continuously or to use a frequently scheduled log shipping.

P.S. I used a Microsoft Distribution Control 8.0 (SQLDISTXLib)