Home Library Learn Samples Downloads Support Community Forums
MSDN Library
Servers and Enterprise Development
SQL Server
SQL Server 2005
Product Documentation
SQL Server 2005 Documentation
SQL Server 2005 Books Online
SQL Server Database Engine
Database Engine How-to Topics
Administration How-to Topics
Configuring Availability How-to Topics
Database Mirroring How-to Topics
How to: Create a Mirroring Endpoint for Windows Authentication (Transact-SQL)
How to: Allow Database Mirroring Network Access Using Windows Authentication (Transact-SQL)
How to: Allow Database Mirroring to Use Certificates for Outbound Connections (Transact-SQL)
How to: Allow Database Mirroring to Use Certificates for Inbound Connections (Transact-SQL)
How to: Prepare a Mirror Database for Mirroring (Transact-SQL)
How to: Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL)
How to: Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)
How to: Set Up a Mirror Database to Use the Trustworthy Property
How to: Change Transaction Safety in a Database Mirroring Session (Transact-SQL)
How to: Manually Fail Over a Database Mirroring Session (Transact-SQL)
How to: Force Service in a Database Mirroring Session (Transact-SQL)
How to: Pause a Database Mirroring Session (Transact-SQL)
How to: Resume a Database Mirroring Session (Transact-SQL)
How to: Remove the Witness from a Database Mirroring Session (Transact-SQL)
How to: Remove Database Mirroring (Transact-SQL)
How to: Configure a Database Mirroring Session (SQL Server Management Studio)
How to: Launch the Configuring Database Mirroring Security Wizard (SQL Server Management Studio)
How to: Add or Replace a Database Mirroring Witness (SQL Server Management Studio)
How to: Launch Database Mirroring Monitor
How to: View the State of a Mirrored Database (SQL Server Management Studio)
How to: Manually Fail Over a Database Mirroring Session (SQL Server Management Studio)
How to: Pause or Resume a Database Mirroring Session (SQL Server Management Studio)
How to: Remove the Witness from a Database Mirroring Session (SQL Server Management Studio)
How to: Remove Database Mirroring (SQL Server Management Studio)
How to: Install a Service Pack on a System with Minimal Downtime for Mirrored Databases
Expand Minimize
This topic has not yet been rated - Rate this topic

How to: Change Transaction Safety in a Database Mirroring Session (Transact-SQL)

SQL Server 2005

Transaction safety is the attribute that controls the operating mode of the session. At any time, however, the database owner can change the transaction safety. By default, the level of transaction safety is set to FULL (synchronous operating mode).

Turning off transaction safety shifts the session into asynchronous operating mode, which maximizes performance. If the principal becomes unavailable, the mirror stops but is available as a warm standby (failover requires forcing service with possible data loss).

ms189061.note(en-US,SQL.90).gifNote:
For more information about operating with safety on, see Synchronous Database Mirroring (High-Safety Mode). For more information about operating with safety off, see Asynchronous Database Mirroring (High-Performance Mode).

  1. Connect to the principal server.

  2. Issue the following Transact-SQL statement:

    Copy 
    ALTER DATABASE <database> SET PARTNER SAFETY FULL

    where <database> is the name of the mirrored database.

  1. Connect to the principal server.

  2. Issue the following statement:

    Copy 
    ALTER DATABASE <database> SET PARTNER SAFETY OFF

    where <database> is the mirrored database.

Did you find this helpful?
(1500 characters remaining)

Community Additions

ADD
© 2013 Microsoft. All rights reserved.