Issue:-
Mirrored databases are disconnected after restart of SQL instance due to
in SQL Server 2005 and above.
Resolution 1:
In above image you can see DBA database is in Disconnected mode.
How to over come on this ???
T-sql commands.
First need to find endpoint name of mirrored server.
SELECT name,type_desc,state_desc,role_desc FROM sys.database_mirroring_endpoints;
On the principal database, execute the following SQL script to stop the endpoint:
ALTER ENDPOINT <Endpoint Name> STATE=STOPPED
eg: ALTER ENDPOINT Mirroring STATE=STOPPED
Execute the following SQL script to restart the endpoint:
ALTER ENDPOINT <Endpoint Name> STATE=STARTED
eg: ALTER ENDPOINT Mirroring STATE=STARTED
Resolution 2 :
Drop and re-create the database mirroring endpoints on both servers.
First need to find endpoint name of mirrored server.
SELECT name,type_desc,state_desc,role_desc FROM sys.database_mirroring_endpoints;
On the principal database, execute the following SQL script to drop the endpoint
drop endpoint <END POINTNAME>
go
On the principal database, execute the following SQL script to Create Endpoint :
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING
(ENCRYPTION = DISABLED,ROLE=ALL)
GO
Use correct Encryption , else DB would be in disconnected mode
Thats it :)
More :
Mirrored databases are disconnected after restart of SQL instance due to
- Server Hang ,
- You end the process of the SQL Server secondary instance by using Task Manager
- Network Issue
in SQL Server 2005 and above.
Resolution 1:
In above image you can see DBA database is in Disconnected mode.
How to over come on this ???
T-sql commands.
First need to find endpoint name of mirrored server.
SELECT name,type_desc,state_desc,role_desc FROM sys.database_mirroring_endpoints;
On the principal database, execute the following SQL script to stop the endpoint:
ALTER ENDPOINT <Endpoint Name> STATE=STOPPED
eg: ALTER ENDPOINT Mirroring STATE=STOPPED
Execute the following SQL script to restart the endpoint:
ALTER ENDPOINT <Endpoint Name> STATE=STARTED
eg: ALTER ENDPOINT Mirroring STATE=STARTED
Resolution 2 :
Drop and re-create the database mirroring endpoints on both servers.
First need to find endpoint name of mirrored server.
SELECT name,type_desc,state_desc,role_desc FROM sys.database_mirroring_endpoints;
On the principal database, execute the following SQL script to drop the endpoint
drop endpoint <END POINTNAME>
go
On the principal database, execute the following SQL script to Create Endpoint :
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING
(ENCRYPTION = DISABLED,ROLE=ALL)
GO
Use correct Encryption , else DB would be in disconnected mode
Thats it :)
More :
No comments:
Post a Comment