SQL Mirroring Transactions Test

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: theprincipal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

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.

If transaction log records are not sent quickly by principal server or are not applied quickly by the mirror server, then the data in the principal and mirror databases will be out of sync; this will cause significant data loss during a failover. To avoid this, administrators must keep track of the log record traffic between the principal and mirror servers, proactively detect potential slowness in mirroring, figure out the probable source of the bottleneck, and clear it to ensure synchronization between the principal and mirror databases. This is where the SQL Mirroring Transactions test helps.

This test tracks the transactions started on a SQL server instance, measures the rate at which transaction log data is  sent to the mirror server for synchronization, and the time taken by the mirror server to apply the data. In the process, the test pinpoints bottlenecks in database mirroring and where exactly the bottlenecks lie.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for each database on the Microsoft SQL server instance being monitored.

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. ssl – If the Microsoft SQL server being monitored is an SSL-enabled server, then set the ssl flag to Yes. If not, then set the ssl flag to No.
  5. instance - In this text box, enter the name of a specific Microsoft SQL instance that is to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the INSTANCE parameter.

  6. USER –If a Microsoft SQL Server 7.0/2000 is monitored, then provide the name of a SQL user with the Sysadmin role in this text box. While monitoring a Microsoft SQL Server 2005 or above, provide the name of a SQL user with all of the privileges outlined in User Privileges Required for Monitoring Microsoft SQL server.

  7. password - The password of the specified user
  8. confirm password - Confirm the password by retyping it.
  9. domain - By default, none is displayed in the DOMAIN text box. If the ‘SQL server and Windows’ authentication has been enabled for the server being monitored, then the DOMAIN can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the DOMAIN text box, specify the Windows domain in which the managed Microsoft SQL server exists. Also, in such a case, the USER name and PASSWORD that you provide should be that of a user authorized to access the monitored SQL server.
  10. isntlmv2 - In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, the isntlmv2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL host. Set this flag to Yes if NTLMv2 is enabled on the target host.
  11. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
Measurements made by the test
Measurement Desription Measurement Unit Interpretation

Queued log size:

Indicates the total number of kilobytes of log that have not yet been sent to the mirror server.

KB

A high value for this measure could indicate a bottleneck on the principal server or a network congestion obstructing data flow to the mirror server. Ideally, the value of this measure should be low.

Log bytes sent:

Indicates the rate at which log data was sent.

Bytes/sec

A high value is desired for this measure. A consistent drop in this value could indicate a processing bottleneck on the principal server.

Log compressed bytes sent:

Indicates the rate at which compressed bytes of log data was sent.

Bytes/Sec

 

Log send flow control time:

Indicates the duration for which log stream messages waited for send flow control, in the last second.

Msecs

Sending log data and metadata to the mirroring partner is the most data-intensive operation in database mirroring and might monopolize the database mirroring and Service Broker send buffers. Use this counter to monitor the use of this buffer by the database mirroring session.

A high value of this measure indicates that the queue in the actual layer sending the messages on the network is full. Hence this would indicate a network issue.

Transactions:

Indicates the rate at which transactions were started for the database.

Transactions/Sec

 

Transaction delay:

Indicates delay in waiting for unterminated commit acknowledgment.

Msecs

High values in this counter can be a clear indicator of a bottleneck that is affecting performance and that end users are seeing a delay in their transactions.

Avg transaction delay:

Indicates ratio between transaction delay and transaction per sec.

Msecs/transaction

 

Log harden time:

Indicates the time for which log blocks waited to be hardened to disk, in the last second.

Msecs

If transactions are not hardened on the log drive on the mirror fast enough and you are using high safety, the principal might have to wait for the mirror to acknowledge hardening of log records before transactions can commit, resulting in degraded performance. A high value for this measure is therefore a cause for concern.

Log bytes received:

Indicates the rate at which log bytes were received.

Bytes/Sec

A high value is desired for this measure. A consistent drop in this value could indicate a processing bottleneck on the mirror server.

Log compressed bytes received:

Indicates the rate at which compressed log data was received.

Bytes/Sec

 

Redo bytes:

Indicates the number of bytes of log rolled forward on te mirror database per second.

Bytes/Sec

 

Redo queue:

Indicates the total number of kilobytes of hardened log that currently remain to be applied to the mirror database to roll it forward. This is sent to the Principal from the Mirror.

KB

A low value is ideal for this measure.

Send/Receive ack time:

Indicates the time for which messages waited for acknowledgment from the partner, in the last second.

Msecs

This counter is helpful in troubleshooting a problem that might be caused by a network bottleneck, such as unexplained failovers, a large send queue, or high transaction latency. In such cases, you can analyze the value of this counter to determine whether the network is causing the problem.

Mirrored write transactions:

Indicates the number of transactions that wrote to the mirrored database and waited for the log to be sent to the mirror in order to commit, in the last second. This counter is incremented only when the principal server is actively sending log records to the mirror server.

Transactions/Sec

This counter is incremented only when the principal server is actively sending log records to the mirror server.