4

We are trying to figure out which of the below statements is correct when talking about SQL Server mirroring:

  1. The master database server executes the query, and takes the resulting transactions and ships them over to the secondary server, which "rolls in" the transactions into its database.

  2. The master database server executes the query and simultaneously writes the query to the log. The log contents are sent to the slave and the queries are executed as they were on the primary.

Update: I unfortunately couldn't modify the two statements to avoid tampering. I think because the word "simultaneously" is used in the second statement it leads people away from the focus of question.

The main difference between the two statements is that in Statement 1, the transaction log that is sent to the secondary includes every row change action (insert/update/delete) that RESULTED from the query. In statement 2, the transaction log is actually only passing the query, and then executing it on the secondary. The key difference is, if Statement 2 is correct and you're updating millions of rows, it has little impact on how much data is passed (only the original query). However, if Statement 1 is correct, then updating millions of rows will require millions of rows of data to be sent to the secondary.

  • How is mirroring configured is it synchronous or Asynchrounous – Shanky Dec 9 '14 at 22:20
  • It will depend on how its setup. @Eugene are you looking on general or specific scenario? – Im88 Dec 9 '14 at 22:24
  • Shanky: It's configured Async, but I can't figure out how that effects the answer. The difference between async and sync would only affect when a commit is recorded (as soon as the principle commits for async, or as soon as the mirror confirms commit if synchronous), right? JigPatel: I'm looking for the general scenario. Just trying to figure out what MS SQL is doing in the background. – Eugene K Dec 9 '14 at 22:33
  • @EugeneK It does have a difference please read Table 9: A Safety FULL (Synchronous transfer) example sequence of events and Table 10: A Safety OFF (Asynchronous transfer) example sequence of events from technet.microsoft.com/en-gb/library/cc917680.aspx to find what actually happens – Shanky Dec 9 '14 at 22:46
  • @Shanky Nice find on that article, something I was looking for as well. Not sure, however, on how the operating modes influence what data is copied. Following the steps, I see differences in when things are committed and a few control signals, but not in the actual transaction log content that is being sent across (which is the difference between the two statements). – Eugene K Dec 9 '14 at 23:44
3

As noted it is based on the mode mirroring is configured to, but overall the concept of mirroring you are probably closer with statement one.

However, as noted in the documentation for mirroring:

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

So I would necessarily say it rolls in the transactions to the database on the secondary but it applies them to the secondary. Then depending on which mode you are in would be when the transaction is committed to the database on the primary (synchronous or asynchronous).

So I would probably state it as:

The primary server reads the transaction log of the mirrored database for a insert/update/delete occurs, that transaction is sent to the secondary and applied to the mirroring database. Based on synchronous or asynchronous the transaction on the primary (or mirrored database) is then committed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.