Example Analysis of Sqlserver Mirror switching Operation and checking Mirror synchronization
I would like to share with you the example analysis of Sqlserver image switching operation and checking image synchronization. I believe most people don't know much about it, so share this article for your reference. I hope you will learn a lot after reading this article. Let's take a look at it!
On the primary node:
ALTER DATABASE [dbname] SET SAFETY FULL
ALTER DATABASE [dbname] SET PARTNER FAILOVER
After switching to the slave node, the new master node:
ALTER DATABASE [dbname] SET SAFETY OFF
Check the mirror synchronization:
SET NOCOUNT ON
DECLARE @ db_name varchar
DECLARE @ dblist TABLE ([db_name] varchar)
DECLARE @ a TABLE (
Database_name SYSNAME
, [role] INT
, mirroring_state INT
, witness_status INT
, log_generation_rate INT
, unsent_log INT
, send_rate INT
, unrestored_log INT
, recovery_rate INT
, transaction_delay INT
, transactions_per_sec INT
, average_delay INT
, time_recorded DATETIME
, time_behind DATETIME
, local_time DATETIME
)
-- Export a list of mirror database names
Insert into @ dblist
Select a.name
From sys.sysdatabases a (nolock)
Join sys.database_mirroring b (nolock) on a.dbid=b.database_id
Where mirroring_guid is not null
EXEC msdb.dbo.sp_dbmmonitorupdate
EXEC msdb.dbo.sp_dbmmonitorupdate-if server executes this sp for the first time, it may not generate data, so you need to execute it again
-- all the relevant records of each mirror database are imported into table variables through a loop.
While exists (select * from @ dblist)
Begin
Set @ db_name= (select top 1 [db_name] from @ dblist)
Delete @ dblist where [db_name] = @ db_name
INSERT INTO @ a
EXEC msdb.dbo.sp_dbmmonitorresults @ db_name
, 0-- number of rows returned:-1 row;-previous bar;-number of rows in the last two hours
0-- 0 means not calling sp_dbmmonitorupdate, which means calling
End
SELECT database_name
, CASE [role] WHEN 1 THEN 'principal database'
WHEN 2 THEN 'Mirror Database'
ELSE NULL END AS 'db_role'
, CASE mirroring_state WHEN 0 THEN 'suspended'
WHEN 1 THEN 'disconnected'
WHEN 2 THEN 'synchronizing'
WHEN 3 THEN 'suspend failover'
WHEN 4 THEN 'synchronized'
ELSE NULL END AS mirroring_state
, CASE witness_status WHEN 0 THEN 'unknown'
WHEN 1 THEN 'connected'
WHEN 2 THEN 'disconnected'
ELSE NULL END AS witness_status
, unsent_log AS 'unsent_ log [kb]'
, unrestored_log AS 'unrestored_ log [kb]'
, send_rate AS 'send_ Rate [kb / s]'
, recovery_rate AS 'recovery_ Rate [kb / s]'
, transaction_delay AS 'transaction_delay/ms'
, transactions_per_sec
, average_delay
, time_recorded
, time_behind
, local_time
FROM @ a
The above is all the contents of the article "sample Analysis of Sqlserver Mirror switching Operation and checking Mirror synchronization". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!