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

Applies to: SQL Server

Caution

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. For high availability, use Always On availability groups instead.

Important

Database Mirroring in SQL Server is a distinct technology from Microsoft Fabric Database Mirroring. Mirroring to Fabric provides better analytical performance, the ability to unify your data estate with OneLake in Fabric, and open access to your data in Delta Parquet format.

With Mirroring to Microsoft Fabric, you can continuously replicate your existing data estate directly into OneLake in Fabric, including data from SQL Server 2016+, Azure SQL Database, Azure SQL Managed Instance, Cosmos DB, Oracle, Snowflake, and more.

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.

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, see ALTER 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