Let’s look again at callout C. At that point in the flowchart, no rows at the Publisher match the old Subscriber row in primary key and version GUID. Second, the transaction didn’t update the Subscriber primary key. And at the Publisher, no rows have the same primary key as the old Subscriber row. So SQL Server knows that the row with the given primary key was deleted at the Publisher and therefore now needs to be deleted from the Subscriber.
When you select the Publisher always wins option, SQL Server needs to return to the Subscriber table and delete the row that has the same primary key as the row that was modified at the Subscriber and deleted at the Publisher, so that neither the Publisher nor the Subscriber has a row that contains that primary key. SQL Server will synchronize the Publisher and Subscriber data later. In a Publisher always wins setting, no updating occurs at the Publisher, and the Queue Reader Agent doesn’t modify the Publisher row when a conflict occurs.
To delete the modified row at the Subscriber, SQL Server uses the primary key to generate a compensating DELETE command. Then, SQL Server puts the command into the distribution database so that the next time the Distribution Agent runs, it picks up the command to run the sp_MS_del_Authors stored procedure, which deletes the appropriate row at the Subscriber.
Now, let’s take another look at the step that callout D depicts. At that point in the flowchart, a row doesn’t exist at the Publisher that matches the old Subscriber row in primary key and version GUID. Also, the transaction didn’t update the primary key. Furthermore, at the Publisher, a row exists that has the same primary key as, but a different GUID than the old Subscriber row. Now you can tell that the row with the given primary key was updated at both the Publisher and the Subscriber.
When you’ve selected the Publisher always wins option, SQL Server needs to delete the Subscriber table row that has the same primary key as at the Publisher, then insert a row in the Subscriber table that exactly matches all the current column values in the Publisher table that have the same primary key. Again, no updating occurs at the Publisher.
SQL Server uses the primary key to generate a DELETE compensating command, then drops the command into the distribution database. The next time the Distribution Agent runs, it picks up the command to run the sp_MS_del_Authors stored procedure, which deletes the appropriate row at the Subscriber.
SQL Server uses the current values in the Publisher row that contains the primary key to generate an INSERT compensating command, then drops the command into the distribution database. The next time the Distribution Agent runs, it picks up the command to run the sp_MS_ins_Authors stored procedure, which inserts the appropriate row at the Subscriber.
Give TRQU a Spin
TRQU is well suited for systems that can tolerate some data loss (in case of a conflict) but where a large degree of synchronization is essential. For example, user or group customizations of favorite Web sites might store preferences in SQL Server tables and have two or more sites for redundancy. Conversely, financial transactions can’t tolerate even minimal loss of data and aren’t good candidates for TRQU. In the final article in this series, I’ll describe how to back up and restore replication implementations.