Database mirroring shows error message in SQL Server

Question

I have two instances of SQL Server 2014 Standard Edition

• MSSQLSERVER (PRIMARY)
• 192.168.10.131/MIRROR (MIRROR)

The entire database on a single computer WINDOWS SERVER 2012 R2

First I backup AdventureWorks2014 from MSSQLSERVER(PRIMARY) and restore the database to
192.168.10.131/MIRROR with RESTORE WITH NO RECOVERY

in Restore Database-> Option → Restore Status → RESTORE WITHOUT RESTORE .

Then I take a backup copy of the MSSQLSERVER log (PRIMARY)

 backup log [AdventureWorks2014] to disk ='c:\LOGBACKUP\AdventureWorks2014.trn' 

then I restored the log to 192.168.10.131/MIRROR (MIRROR)

 restore log [AdventureWorks2014] from disk ='c:\LOGBACKUP\AdventureWorks2014.trn' with norecovery 

Good. Then MSSQLSERVER (PRIMARY) in AdventureWorks2014

 RIGHT CLICK->TASK->MIRROR and CONFIGURE SECURITY option 

BUT WHEN I started Mirror, it shows the following windows

After clicking Yes, an error message appears

What is wrong in my process ??? What configuration should I do?

Answer 1

You can also check if port 5023 is open.

• At the command prompt, enter the following.

Telnet Waltonserver 5023

2. If you do not have telnet installed, follow these steps:

https://technet.microsoft.com/en-us/library/cc771275(v=ws.10).aspx

3. After installing telnet, check your telnet on a server that, as you know, works on port 1433

Telnet ServerName 1433

If this works, you will get a popup with an open window that you can enter. If this fails, you will receive a message .... Could not open a connection to the host on port 1433. If you can connect to other servers on port 1433, but not to port 5023, then you know that the port is not open. then ask the administrator to open port 5023.

Answer 2

Go to services.msc and check if the SQL server is running under this account. Verify that the SQL Server and SQL Server services are running with the same credentials.

on the mirror database server, you must take the same step as in step 1. Give the same credentials as on the main server. If this user is missing, create a new one on both servers with the same credentials.

Now the main server and sql server have appeared, add a new login in the Security → Login section. Provide server roles like Sysadmin and public. Add the same user to the mirror server.

Now a mirror in the main database. You have not received any errors.

Answer 3

Here are a few things you can try:

1) Make sure the firewall is not blocking your SQL ports. Go to Windows Firewall with Advanced Security → Inbox \ Outbound Rules → New Rule → Port → Specific Local Ports: Install 5022.5023 → Allow Connection

2) Check if Login has the sysadmin role created on each instance: In Management Studio, connect to each instance → Security → "your_login" → Properties → Server Roles → sysadmin. Also, when setting up mirroring on the Service Accounts tab, you must put the account credentials in the Principal and Mirror fields.

3) Go to SQL Server Configuration Manager and make sure that both instances and SQL Agent are running under the same account (preferably yours). Also check the SQL Server network configuration for each instance if TCP \ IP is enabled.

4) In each instance, try to reset and recreate the endpoint of the mirror. Run the following script in both instances with the appropriate port number:

 DROP ENDPOINT Mirroring GO CREATE ENDPOINT Mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING (ENCRYPTION = DISABLED,ROLE=ALL) GO 

Hope one of these tips helps. Good luck