SQL Server mirroring issue

[niall5098]

Hi There,

I am working on an issue whereby 2 servers linked through mirroring have been renamed and now the mirroring appears to be broke.

Previously the servers were called:

SQL1 and SQL2 with all databases on SQL1 mirrored to SQL2.

The client wanted the server renamed to SQLA and SQLB and followed through with the request.

I made the necessary change at the backend by dropping the server and re-adding it again with SQL.

Now the mirroring appears to be broke with the following error:

Bypassing recovery for database 'abcd' because it is marked as an inaccessible database mirroring database. A problem exists with the mirroring session. The session either lacks a quorum or the communications links are broken because of problems with links, endpoint configuration, or permissions (for the server account or security certificate). To gain access to the database, figure out what has changed in the session configuration and undo the change.

If i try to reconfigure the security within DB mirroring i see:

TCP://SQLA.domain.local:5022
TCP://SQL2.domain.local:5022

If i go through the security wizard and connect to SQLB the change saves successfully:

Configuring Endpoints

- Configuring endpoint on principal server(SQLA) (Success)

- Configuring endpoint on mirror server(SQLB) (Success)

However the mirroring status has the following status:

No connection: cannot connect to the mirror server instance

Anyone have any ideas?

Thanks in advance,

Niall

 

[yelworcm]

The error messages given for mirroring are notorious for their vagueness.

If the databases are small, I would just rebuild all of the required mirrors with the new name.

If they are a bit big for that, you can check in the event logs of each server to see if you can get some better error messages.

 

[niall5098]

Issue is the client wants me to fix the problem rather than rebuild the DR even though it takes less time. I have tried to update the mirroring column to see if that would work with a dummy db so i run the following:

update sys.database_mirroring
set mirroring_partner_name = 'TCP://SQLB.domain.local:5022'
where database_id = 27

but receieve an error:

Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

I have tried allowing updates etc:

sp_configure 'Allow Updates', 0;
reconfigure with override;
go

-- and just in case:
sp_configure 'Show Advanced Options', 1;
reconfigure with override;
go

No joy.

i am assuming its the fact that an update cannot be added to the sys.database_mirroring catalog thats causing my issue?

 

[yelworcm]

System tables should not be updated directly. They are used for determining what is going on in the system. Half of the system tables are not even really tables, they are memory structures that appear as views or tables when queried.

You might try this (I don't have a system I can rename terribly freely, so this may not even be possible on your end, either). Disable mirroring for a database

alter database [dbname] set partner off

, then without going through all of the backup/restore, transaction log steps, just go right to the enabling of the mirror:

alter database[dbname] set partner = 'tcp://sql2.domain.local;5022'

In theory, the copy of the database on the mirror server is up to date enough that you can get away with this, but it depends on how finicky SQL Server is in checking to see if all of the proper backups have been done before enabling mirroring, or if it just checks the respective LSN's to make sure the primary is ahead of the mirror, and not too far out of date. This is a risky test, as it will break that mirror, but I figure you are going to end up having to go that route, anyway.