Reconnect mirroring server SQL Server 2008 R2

Question

The failover server has status (Mirror, Disconnected, In recovery) and principal server has status (Principal, Disconnected).

What is the process to reconnect these servers?

Answer 1

Sometimes restarting the mirroring endpoint on the principal server will resolve this.

To do this use the following T-SQL.

ALTER ENDPOINT Endpoint_Name STATE = STOPPED

ALTER ENDPOINT Endpoint_Name STATE = STARTED

To get the name of the endpoint.

SELECT * FROM sys.endpoints
WHERE type = 4

See KB 2490051 for more details.

Answer 2

First, try to get them to reconnect with this T-SQL command on the principal:

ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;

If that fails, you've got a communications issue between the principal and the mirror, and that's where the fun begins. It can be anything from IP connectivity to security on the mirroring endpoints to different encryption methods. I'd recommend Robert Davis's book Pro SQL Server 2008 Mirroring if you need to get into deep troubleshooting.

Answer 3

In our case we're using certificate authentication for connecting mirroring endpoints, and the issue was because one of the certificates that the mirroring endpoints were using had expired.

You can check the expiration date of the certificate involved in endpoint authentication by doing a

SELECT * FROM sys.certificates  

If this is the case, you'll need to create new certificates for whatever expired, then do ALTER ENDPOINT after they have been been imported.

Answer 4

I fixed this issue by next steps, restarting endpoint just on PRIMARY didn't help

1. On PRIMARY:

   SELECT * FROM sys.endpoints WHERE type = 4
   
   ALTER ENDPOINT Mirroring STATE = STOPPED
   
   ALTER ENDPOINT Mirroring STATE = STARTED
   

2. On MIRROR:

   SELECT * FROM sys.endpoints WHERE type = 4
   
   ALTER ENDPOINT Mirroring STATE = STOPPED
   
   ALTER ENDPOINT Mirroring STATE = STARTED
   

3. Back on PRIMARY:

   ALTER DATABASE PACXDSite SET PARTNER RESUME;
   

Answer 5

Case:

When you run below command on Principal server:

alter database  'Database_Name' set partner off

After running above command your DB goes in [mirror, disconnected] State on mirror server.

Resolution:

Step 1:Run below query on mirror

exec sp_resetstatus 'Database_Name'

Step 2: After running below command your DB will be available.

restore database 'Database_Name' with recovery

Note: Step 2 will take some time to recover.

Answer 6

If "RESUME PARTNER" doesn't work

ALTER DATABASE AdventureWorks2016 SET PARTNER RESUME;

I would break the mirror for that database(s) and reconfigure it.

--STEP 1 ON PRINCIPAL, break the mirror for a database

ALTER DATABASE AdventureWorks2016 SET PARTNER OFF;

--STEP 2 ON MIRROR, break the mirror for a database

ALTER DATABASE AdventureWorks2016 SET PARTNER OFF;

--STEP 3 ON MIRROR

RESTORE LAST FULL BACKUP DATABASE WITH NO RECOVERY 
RESTORE TRAN BACKUP DATABASE WITH NO RECOVERY

--STEP 4 ON MIRROR

ALTER DATABASE AdventureWorks2016 SET PARTNER = 'TCP://PRINCIPAL.MyDomain.COM:5022';

--STEP 5 ON PRINCIPAL

ALTER DATABASE AdventureWorks2016 SET PARTNER = 'TCP://MIRROR.MyDomain.COM:5022';

--STEP 6 ON PRINCIPAL, if a WITNESS was already configured

ALTER DATABASE AdventureWorks2016 SET WITNESS = 'TCP://WITNESS.MyDomain.COM:5022';

It is worth to mention that all of the above can be done during working hours as the principal database will always be online and available.