Distribution database role | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Distribution database role

Hi I have some doubt about the role of distribution data base that whether distribution database hold the new transaction or just pointing it to log file? In the transactional replication scenario once the changes made on publication the log reader read the changes and put the transaction to the distribution database. I would like to know the log reader will copy the transaction from transaction log and put it into distribution table and mark the transaction log entry marked as done or once the transaction replicated to the subscriber then only it will mark. Thanks
Hi,<br />BOL Says :<br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Transactional Replication <br />With transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers. <br /><br />Transactional replication is helpful when: <br /><br />You want incremental changes to be propagated to Subscribers as they occur. <br /><br /><br />You need transactions to adhere to ACID properties.<br /><br /><br />Subscribers are reliably and/or frequently connected to the Publisher. <br /><b>Transactional replication uses the transaction log to capture incremental changes that were made to data in a published table. Microsoft® SQL Serverâ„¢ 2000 monitors INSERT, UPDATE, and DELETE statements, or other modifications made to the data, and stores those changes in the distribution database, which acts as a reliable queue. Changes are then propagated to Subscribers and applied in the same order as they occurred.</b><br /><br />With transactional replication, incremental changes made at the Publisher flow according to the Distribution Agent schedule. This schedule can be set to continuously for minimal latency, or set at scheduled intervals to Subscribers. Because changes to the data must be made at the Publisher (when transactional replication is used without immediate updating or queued updating options), update conflicts are avoided. This guarantees ACID properties of transactions will be maintained. Ultimately, all Subscribers will achieve the same values as the Publisher. If immediate updating or queued updating options are used with transactional replication, updates can be made at the Subscriber, and with queued updating, conflicts might occur.<br /><br />If Subscribers need to receive data changes in near real-time, they need a network connection to the Publisher. Transactional replication can provide very low latency to Subscribers. Subscribers receiving data using a push subscription usually receive changes from the Publisher within one minute or sooner, provided that the network link and adequate processing resources are available (latency of a few seconds can often be achieved).<br /><br />However, Subscribers can also pull changes down as needed. A traveling sales representative can be a Subscriber and request incremental changes to a price list, which is only modified at the corporate office, once each evening. The use of transactional replication for disconnected users can be very effective for read-only data. <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />HTH<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />