SQL Server replication for HA and LB
I'm currently trying to build a high-availability and load-balanced web application with SQL Server Replication Services technologies. Automatic fail-over is built into the application logic. Basically, there are two groups of application servers running the same, each with its own SQL Server instance. They are set to use the other instance in case of failure. Data is continously replicated between the two SQL Server instances via transactional replication. (A few seconds lag exists, that's okay.)
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? UPDATE to this first question: 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?
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-directional replication in documents or blogs, is this scenario what they mean or rather updateabl开发者_JS百科e 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.
精彩评论