SQL Server Mirroring – Data Passed Between Principal and Secondary

mirroringsql server

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.

Best Answer

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.

Related Question