Usually setting up a mirroring pair is a breeze but today I found out some possible overlook that my fellow DBA’s could also encounter on their jobs. Here are the common steps that I usually take once restoration on the mirrored side is properly done till the most recent LSN.
Step 1: on mirrored partner, issue below t-sql script
CREATE ENDPOINT endpoint_mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING (ROLE=PARTNER); GO ALTER DATABASE db_name SET PARTNER = N'TCP://principalserver_address:5022'; GO
Step 2: on the principal server, issue below t-sql script
CREATE ENDPOINT endpoint_mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING (ROLE=PARTNER); GO ALTER DATABASE db_name SET PARTNER=N'TCP://mirroredpartner_address:5022' GO
Then I can easily switch the mirroring mode to ASYNC if I execute another command on the principal server:
ALTER DATABASE db_name SET SAFETY OFF
However today when I was setting up mirroring for two new boxes, I encountered the titled error message and the root cause turned out to be that the mirrrored partner is a named instance and there is another instance on the same box that is already listening on port 5022. And I had to modify the port number to 5023 on the mirrored partner to enable the mirroring. Hopefully, this will provide some guidance or tips if you ever google up this post.