Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.