INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Server mirroring issue

SQL Server mirroring issue

(OP)
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

RE: SQL Server mirroring issue

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.

RE: SQL Server mirroring issue

(OP)
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?

RE: SQL Server mirroring issue

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

CODE

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:

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close