how to findout the servers which are involved in database mirroring.

Question

how to findout the servers which are involved in database mirroring.

charipg

SSCrazy Eights

Points: 9888
More actions
August 11, 2010 at 1:51 am

#157142

how to findout the servers which are involved in database mirroring.?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

If you look at at servers databases you will be able to identify the mirrored pairs by the database names.

Mirrored db's have two roles, the Principal (the current active database) and the Mirror (currently inactive and in restor mode).

HTH

Adam Zacks-------------------------------------------Be Nice, Or Leave

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

T-SQL statement host_name() can be used to detemine the workstation name.

Raunak J

charipg SSCrazy Eights Points: 9888 More actions · Answer 3

ok thanks for the answers.

any table will store this information?

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 4

Database Mirroring (as name suggests) is database level, so if you are looking for a way to find out which servers are participating in Database Mirroring sessions; this is not possible.

However you can check endpoints on each SQL Server for database mirroring; this will let you know if server is at presently configured for database mirroring. Even if at present there are no databases being mirrored.

-- Please pay attention to the state_desc column, indicating if it is active or not.

SELECT * FROM sys.endpoints WHERE type_desc = 'DATABASE_MIRRORING'

-- Another view of endpoints, you are allowed only 1 endpoint for database mirror per server.

SELECT * FROM sys.database_mirroring_endpoints

You can check catalog views to see which databases are in mirror state by executing:

-- From Microsoft White paper on Database Mirroring.

SELECT

DB_NAME(database_id) AS 'DatabaseName'

, mirroring_role_desc

, mirroring_safety_level_desc

, mirroring_state_desc

, mirroring_safety_sequence

, mirroring_role_sequence

, mirroring_partner_instance

, mirroring_witness_name

, mirroring_witness_state_desc

, mirroring_failover_lsn

FROM sys.database_mirroring

WHERE mirroring_guid IS NOT NULL;

REF: http://technet.microsoft.com/en-us/library/cc917680.aspx

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].