ALTER DATABASE ERROR IN MIRRORING.

We got the below error in Mirroring when we are trying to add WITNESS server to the existing asynchronous operating mode.



1) In my environment PRINCIPAL and MIRROR servers are SQL Server 2014 Enterprise edition, but WITNESS server was SQL Server 2014 Express edition.
2) SQL Service accounts are running with NT Service\MSSQLSERVER account where as WITNESS server is running with another account(NT Service\MSSQL$VM) .
-->
3) But all these accounts  are communicating with  one another successfully. But if we are using the same DOMAIN account for all the instances(PRINCIPAL,MIRROR and WITNESS) there would be very less chance that we are going to get error.

Work Around:

The issue resolved when we STOP and RESTART the endpoint of  WITNESS server. Though it is a express edition this works fine for us. No need to hesitate even though you dont find end point  in server objects of express edition.


SELECT * FROM sys.database_mirroring_endpoints
GO
ALTER ENDPOINT Mirroring STATE = STOPPED
GO
ALTER ENDPOINT Mirroring STATE = STARTED


Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server