October 9, 2007 at 5:11 am
Well I have recently submitted my first support request to Microsoft the details are:
Setup
Principal server
Mirror Server
Witness Sever
Scenario
We require to be able to monitor the status of the mirroring, safety level, witness connection, mirroring role on the servers using a third party product to query the databases (HP Openview). This product uses a SQL Server account with the minimum permissions required to interogatethe database.
The following Document section :Viewing Mirroring Information http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx#ELMAE details the information I am trying to Query from the Mirror and witness and states
You can execute this query on either partner (the principal or the mirror), and you will get the same output.
If anyone tries this they will get the following
Principal Server
name= AdventureWorks
database_id = 7
mirroring_role_desc = PRINCIPAL
mirroring_state_desc = SYNCHRONIZED
mirroring_safety_level_desc =FULL
mirroring_partner_name = TCP://SERVER_B.uk.abc.com:5022
mirroring_partner_instance = SERVER_B
mirroring_witness_name = TCP://SERVER_C.uk.abc.com:5023
mirroring_witness_state_desc =CONNECTED
Mirror Server
name = NULL
database_id =NULL
mirroring_role_desc =NULL
mirroring_state_desc =NULL
mirroring_safety_level_desc =NULL
mirroring_partner_name =NULL
mirroring_partner_instance =NULL
mirroring_witness_name =NULL
mirroring_witness_state_desc =NULL
Books on line http://msdn2.microsoft.com/en-us/library/ms178655.aspx"> http://msdn2.microsoft.com/en-us/library/ms178655.aspx states
To see the row for a database other than master or tempdb, you must either be the database owner or have at least ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission or CREATE DATABASE permission in the master database.
Ok so you do the following .. grant VIEW ANY DATABASE to the SQL Account used to monitor the Mirroring.
The result of this is that the Sys,Database_Mirroring Catalog View on the mirror still returns NULL. However if try Granting permissions to the account you find that when you Grant 'SYSADMIN' to SQL Account you get the following on the mirror.
Mirror Server
name = AdventureWorks
database_id =7
mirroring_role_desc =MIRROR
mirroring_state_desc =SYNCHRONIZED
mirroring_safety_level_desc =FULL
mirroring_partner_name =TCP://SERVER_A.uk.abc.com:5022
mirroring_partner_instance =SERVER_A
mirroring_witness_name =TCP://SERVER_C.uk.abc.com:5023
mirroring_witness_state_desc =CONNECTED
However this is not what is reflected in the Documentation referenced above.
I have raised this with Microsoft and they are going to update the documentation and escalate the call so that they can supply a method of retrieving the Data without requiring elevated rights.
I will post updates as I recieve them
November 15, 2007 at 8:05 am
UPDATE:
responded with the following
I have just taken the escalation about the case in the subject
--In summary I can write that the issue is the following
Executing the below query to retrieve database mirroring information from Principal and Mirror Server.
SELECT d.name, d.database_id, m.mirroring_role_desc,
m.mirroring_state_desc, m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL
It retrieves records successfully for Mirroring from Principal server with no major privileges.
Returns blank records when executed on Mirror server.
However when assigned the SQL account sysadmin on mirror server returns records.
--This behavior is documented internally
On the mirror, database is in a state that is not accessible to normal use, The database is not "Online", this is the reason why the query from sys.database_mirroring returns NULLs for low privileged user on the mirror
When the datatabase is not “Online”, only for sysadmin, database is opened with flags set for reading DB Mirror configuration.
This is the design behavior and it is not considered a bug
I’m double-cheking this information with Sql developers but I do not want to create false expectations .
Since we have internal documentation that this was the design, there will be few chance to obtain any modification to this design
November 15, 2007 at 8:13 am
Mirroring Monitor Job Failure
Steps to recreate
ServerDatabaseRole
SERVER_1AdventureWorksPrincipal
SERVER_2AdventureWorksMirror
SERVER_3N/A Witness
1)Create the Database Mirroring Job
Exec sp_dbmmonitoraddmonitoring
Set the Log files to be created for the Job
2)Stop SQL Server on the Principal (SERVER_1)
Mirroring Fails over to Mirror Becoming Principal (SERVER_2 becomes Principal)
3)Pause Mirroring on AdventureWorks (SERVER_2)
Mirroring enters state (Principal/Suspended)
4)Restart SQL Server on the other Server (SERVER_1)
5)Wait for SQL Server Agent to restart
6)Mirroring Enters state (Mirror, Suspended / Restoring)
7)Check the status of the Mirroring Job on the Server
8)The Job has Failed – The execution context of the job is the SQL Server Service Account(sysadmin user)
9)Exec sp_dbmmonitorresults AdventureWorks, 0,0
Returns
Database Name Adventureworks
Role 1
Mirroring_state 4
Showing that the Role is currently 1 = Principal
And the State is currently 4 = Synchronized
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply