December 6, 2018 at 4:30 am
Hi there,
Is there a script that will tell you how up to date (or not) a Mirror is with the Principal?
Also I've noticed that my Mirror is not encrypted (my mirror is our DR) for one of our DBs . The Principle is encrypted and the mirror is still showing as Synchronized / Restoring yet its not encrypted when the principle is.
I'm 99.9999% sure that the Mirror was encrypted recently I must have unencrypted it, but can't remember when.
I think I can simply run
ALTER DATABASE cheesetoastie SET ENCRYPTION ON;
But can I run that while the mirror is in place or will I have to break and re-establish the mirror?
I'm also a bit confused as to how the mirror is working when the Principle is Encrypted and the Mirror is not.
The Mirror Monitor is implying that they are up to date, but does someone a script to prove it?
Many thanks
December 6, 2018 at 8:50 am
query master.dbo.database_mirroring table to find out, or may be create a snapshot on sec mirror and compare ..
December 7, 2018 at 3:07 am
A weird one , I think I observed a bug.
when I looked at whether my Mirror DB was encrypted. It was is wasn't is_encrypted = 0
But when I broken the Mirror and restore the DB and tried to Encrypt it , sql said it was already encrypted and my db then had an is_encrypted = 1
Now I have the pain re-establishing the mirror.
Strange one that.
December 9, 2018 at 10:36 am
You should consider setting up policies to monitor the RTO/RPO in your availability groups. Review this document which outlines code that can be used to monitor - as well as the steps necessary to setup and create the RTO/RPO policies.
Once you setup the policies - the system will notify you through email when the secondary falls below the specified thresholds for either RTO or RPO.
Additionally - you should setup and configure alerts for AlwaysOn...here is a script I use to do that:
Set Nocount On;
Declare @alertName sysname
, @thisErrorNumber varchar(6)
, @sqlCommand nvarchar(max) = ''
, @operatorName sysname = '{replace with your operator here}';
Declare @errorNumbers Table (ErrorNumber varchar(6), AlertName varchar(50));
Insert Into @errorNumbers
Values ('1480' , 'AG Role Change (failover)')
, ('976' , 'Database Not Accessible')
, ('983' , 'Database Role Resolving')
, ('3402' , 'Database Restoring')
, ('19406', 'AG Replica Changed States')
, ('35206', 'Connection Timeout')
, ('35250', 'Connection to Primary Inactive')
, ('35264', 'Data Movement Suspended')
, ('35273', 'Database Inaccessible')
, ('35274', 'Database Recovery Pending')
, ('35275', 'Database in Suspect State')
, ('35276', 'Database Out of Sync')
, ('41091', 'Replica Going Offline')
, ('41131', 'Failed to Bring AG Online')
, ('41142', 'Replica Cannot Become Primary')
, ('41406', 'AG Not Ready for Auto Failover')
, ('41414', 'Secondary Not Connected');
Declare cur_ForEachErrorNumber Cursor Local fast_forward
For
Select *
From @errorNumbers;
Open cur_ForEachErrorNumber;
Fetch Next From cur_ForEachErrorNumber Into @thisErrorNumber, @alertName;
While @@fetch_status = 0
Begin
If Not Exists(Select *
From msdb.dbo.sysalerts s
Where s.message_id = @thisErrorNumber)
Begin
Execute msdb.dbo.sp_add_alert
@name = @alertName
, @message_id = @thisErrorNumber
, @severity = 0
, @enabled = 1
, @delay_between_responses = 0
, @include_event_description_in = 1
, @job_id = N'00000000-0000-0000-0000-000000000000';
Execute msdb.dbo.sp_add_notification
@alert_name = @alertName
, @operator_name = @operatorName
, @notification_method = 1;
Raiserror('Alert ''%s'' for error number %s created.', -1, -1, @alertName, @thisErrorNumber) With nowait;
End
Fetch Next From cur_ForEachErrorNumber Into @thisErrorNumber, @alertName;
End
--==== Close/Deallocate cursor
Close cur_ForEachErrorNumber;
Deallocate cur_ForEachErrorNumber;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 10, 2018 at 3:33 am
Thank you Jeffrey
December 18, 2018 at 2:44 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply