When Does Trustworthy Get Reset on a Mirror?
When I’ve done sessions on automating database mirroring processes and in my book, I talk about incorporating steps to set properties that get reset during the restore process in the mirroring setup such as database owner and the Trustworthy property. When you perform the initial restores of the database to set up mirroring, the database owner is set to the account of the person performing the restore, and if Trustworthy is enabled, it is reset to off as a safety mechanism. This way nobody can slip a database marked Trustworthy in on you without you taking steps to enable it.I make two recommendations for how to handle this. You can perform an initial failover test to the mirror and set the database owner and Trustworthy property and then fail back. It is a very good idea to test this, but will require some momentary outages. It may not always be possible to perform this test, and if mirroring ever has to be rebuilt by someone else, there is no way to ensure that someone else will have the same dilligence. The other way is to set up a process to detect when a failover occurs to check these settings to ensure they are correctly set upon failover.
The question
The question of when the Trustworthy property is reset came up today. The following email was sent to a distribution list asking about the Trustworthy property on a database mirror.
The Test
My immediate thought upon reading the email was that Trustworthy gets reset during the restore. However, if that was true, it would not account for their claim that the property had been enabled on the snapshot in the beginning. I recalled an earlier post I had done where I demonstrated that the cache for the database doesn’t get cleared when it transitions from principal to mirror, it gets cleared when it transitions from mirror to principal. It’s the act of running crash recovery that flushes the memory. This made me wonder if the same was true for the Trustworthy property.
I set up a little experiment. I created a new database with the Trustworthy property enabled. Then I mirrored it to another instance, created snapshots, and validated if and when the snapshots showed the Trustworthy property set.
Use master; -- Create a database Create Database TestTrustworthy; Go -- Set recovery full and Trustworthy on Alter Database TestTrustworthy Set Recovery Full; Alter Database TestTrustworthy Set Trustworthy On; Go Backup Database TestTrustworthy To Disk = 'c:bakTestTrustworthy.bak' With Init Go Backup Log TestTrustworthy To Disk = 'c:bakTestTrustworthy.trn' With Init Go
On the would-be mirror server, I restored the backups, set up mirroring, and created a snapshot. I tested the Trustworthy setting every step of the way.
Restore Database TestTrustworthy From Disk = 'c:bakTestTrustworthy.bak' With NoRecovery, Move 'TestTrustworthy' To 'c:bakTestTrustworthy.mdf', move 'TestTrustworthy_log' To 'c:bakTestTrustworthy_log.ldf' Go Select name, is_trustworthy_on From sys.databases Where name = 'TestTrustworthy' name is_trustworthy_on --------------------- ----------------- TestTrustworthy 0 (1 row(s) affected) Go Restore Log TestTrustworthy From Disk = 'c:bakTestTrustworthy.trn' With NoRecovery Go Select name, is_trustworthy_on From sys.databases Where name = 'TestTrustworthy' name is_trustworthy_on --------------------- ----------------- TestTrustworthy 0 (1 row(s) affected) Go Create Database TestTrustworthy_snap On (Name = 'TestTrustworthy', FileName = 'c:bakTestTrustworthy_snap.ndf') As Snapshot of TestTrustworthy Go Select name, is_trustworthy_on From sys.databases Where name like 'TestTrustworthy%' name is_trustworthy_on --------------------- ----------------- TestTrustworthy 0 TestTrustworthy_snap 1 (2 row(s) affected)
Eureeka!! So the snapshot did indeed inherit the Trustworthy property. How could it inherit Trustworthy enabled when it clearly shows in sys.databases that it’s not enabled? The answer is that sys.databases is less than honest with us. The Trustworthy property has not yet been disabled in the database. I failed over to the mirrored database, failed back to the original principal, dropped the snapshot, and created a new snapshot. The database and the snapshot then showed the expected setting for Trustworthy, off on both the database and the snapshot.
Kendra Little
Very cool and informative post— and extremely useful information.
Your book is right here on the desk next to me, by the way. It’s on top of “The Database Relational Model by CJ Date. 🙂 Guess which one is more readable?
SQLSoldier
Thanks!! I hope you’re finding it helpful. The compliments I’ve gotten are hearing from people that have been helped out by the book!!
Meher
Good post Robert. Thank you.
SQLSoldier
Thanks!!
Something for the Weekend – SQL Server Links 08/04/11 | John Sansom - SQL Server DBA in the UK
[…] When Does Trustworthy Get Reset on a Mirror? – Some excellent DBA advice from Database Mirring expert Robert L. Davis(Blog|Twitter). […]