Jaa

How to: Set Up a Mirror Database to Use the Trustworthy Property

  • Artikkeli

When a database is backed up, the TRUSTWORTHY database property is set to OFF. Therefore, on a new mirror database TRUSTWORTHY is always OFF. If the database needs to be trustworthy after a failover, extra setup steps are necessary after mirroring begins.

Note

For information about this database property, see TRUSTWORTHY Database Property.

Procedure

To setup a mirror database to use the Trustworthy Property

  1. On the principal server instance, verify that the principal database has the Trustworthy property turned on.

    SELECT name, database_id, is_trustworthy_on FROM sys.databases

    For more information, see sys.databases (Transact-SQL).

  2. After starting mirroring, verify that the database is currently the principal database, the session is using a synchronous operating mode, and the session is already synchronized.

    SELECT database_id, mirroring_role, mirroring_safety_level_desc, mirroring_state_desc FROM sys.database_mirroring

    For more information, see sys.database_mirroring (Transact-SQL).

  3. Once the mirroring session is synchronized, manually fail over to the mirror database.

    This can be done in either SQL Server Management Studio or using Transact-SQL:

  4. Turn on the trustworthy database property using the following ALTER DATABASE command:

    ALTER DATABASE <database_name> SET TRUSTWORTHY ON

    For more information, seeALTER DATABASE (Transact-SQL).

  5. Optionally, manually failover again to return to the original principal.

  6. Optionally, switch to asynchronous, high-performance mode by setting SAFETY to OFF and ensuring that WITNESS is also set to OFF.

    In Transact-SQL:

    In SQL Server Management Studio:

See Also

Concepts

TRUSTWORTHY Database Property
Setting Up an Encrypted Mirror Database

Help and Information

Getting SQL Server 2005 Assistance