October 19, 2012 at 8:12 am
I have setup mirroring on a pair of SQL 2008 R2 servers. This appears to be working ok when I run the monitoring software on the partner I can see both the principal and mirror instance. the state on both says Synchronised. If I try to run it on the Principal server it gives the miiror the wrong sql instance name and cant connect to it.
Lookng at the sys.database_mirroring table the mirroring_partner_name fiels is correctly named but the mirroring_partner_instance has the wrong name. I have tried deleting the server instance connections and re adding them but it just keeps putting the wrong instance name in. As it cant connect it throws an eror and fails to connect.
two questions spring to mind
1) Is mirroring working ? as the mirror monitor claims
2) How can I update the sys table to put the right instance name in to the monitor
October 19, 2012 at 1:13 pm
can you post the results of the following query please
selectd.name
, dm.mirroring_state_desc
, dm.mirroring_role_desc
, dm.mirroring_safety_level_desc
, dm.mirroring_partner_instance
, dm.mirroring_witness_name
, dm.mirroring_state_desc
, dm.mirroring_redo_queue
, dm.mirroring_redo_queue_type
from sys.databases d inner join sys.database_mirroring dm
on d.database_id = dm.database_id
where d.name = 'yourdb'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 22, 2012 at 2:50 am
attached csv dump of query results as txt file
The mirroring partner instance field contains the mis-spelt server name
November 1, 2012 at 5:42 am
Mirroring is now suspended and I can't resume it as it attempts to reconnect to the database server that doesnt exist. I can remove mirroring and re-create it. but although I put in the correct database server name it still uses the original incorrect entry
September 11, 2014 at 11:42 am
Hi I was wondering if you ever found out the solution to your issue. I have a very similar issue where I need to change the mirroring_partner_instance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply