I'm currently trying to build a High-Availability and load-balanced web application with Sql Server Replication technologies. Automatic fail-over is built into the application logic. Basically, there are two groups of app servers running the same, each with its own Sql instance. They are set to use the other instance in case of failure. Data is continously replicated between the two sql instances via transactional replication. (A few seconds lag exists, that's okay.)
The first question is: I set up both servers in a way that Distribution agents run on the Distributor (= Publisher). My idea is, that as long as Server A (publisher) is working, it 'collects' the transactions and forwards them to Server B (subscriber) as soon as it's available. The same for Server B. The default option (distributor on the subscriber) would 'lose' changes while the subscriber is offline. Am I right with that?
The second question: The two databases have the same schema (except identity seed and increment). Each read-write table is cross-replicated. Why don't I see a loop, when I insert a row into a table? When I read about bi-di(rectional) replication in documents or blogs, is this scenario what they mean or rather updateable transactional replication?
What do you think about this scenario in general? This is my first time with replication and I fear the risks. Therefore any comments are very welcome.
UPDATE to question 1: transactions (waiting to be delivered) are stored in the distribution database, so they are "safe" anyway. This leads be to another question: if the Distributor Agent is on the subscriber, how will it know about new transactions to be delivered? Via frequent polling?