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
Pripomienky
https://aka.ms/ContentUserFeedback.
Pripravujeme: V priebehu roka 2024 postupne zrušíme službu Problémy v službe GitHub ako mechanizmus pripomienok týkajúcich sa obsahu a nahradíme ju novým systémom pripomienok. Ďalšie informácie nájdete na stránke:Odoslať a zobraziť pripomienky pre