set-up-a-mirror-database-to-use-the-trustworthy-property-transact-sql.md

title	Enable trustworthy property for a mirrored database
description	Learn how to enable the TRUSTWORTHY database property on a newly mirrored database by using Transact-SQL in SQL Server.
ms.custom	seo-lt-2019
ms.date	03/09/2017
ms.prod	sql
ms.prod_service	high-availability
ms.reviewer
ms.technology	database-mirroring
ms.topic	conceptual
helpviewer_keywords	TRUSTWORTHY database option mirror database [SQL Server] database mirroring [SQL Server], security
ms.assetid	6993b076-78ef-453e-b0ea-e341b8e5ee3e
author	MikeRayMSFT
ms.author	mikeray

Set Up a Mirror Database to Use the Trustworthy Property (Transact-SQL)

[!INCLUDE SQL Server] 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:

   • Manually Fail Over a Database Mirroring Session (SQL Server Management Studio)

   • Manually Fail Over a Database Mirroring Session (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:

   • Change Transaction Safety in a Database Mirroring Session (Transact-SQL)

   • Remove the Witness from a Database Mirroring Session (SQL Server)

   In SQL Server Management Studio:

   • Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio)

See Also

TRUSTWORTHY Database Property
Set Up an Encrypted Mirror Database