Set Up a Mirror Database to Use the Trustworthy Property (Transact-SQL)
Applies to: 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
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).
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).
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:
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).
Optionally, manually failover again to return to the original principal.
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:
See Also
TRUSTWORTHY Database Property
Set Up an Encrypted Mirror Database
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho