Database Mirroring shows error message in SQL Server

Question

I have two instance of SQL server 2014 Standard edition

1. MSSQLSERVER(PRIMARY)
2. 192.168.10.131/MIRROR(MIRROR)

All database on same machine of WINDOWS SERVER 2012 R2

First i backup AdventureWorks2014 from MSSQLSERVER(PRIMARY) and restore database in
192.168.10.131/MIRROR with RESTORE WITH NO RECOVERY

in Restore Database-> Option ->Recovery State ->RESTORE WITH NO RECOVERY.

Then i take backup of log MSSQLSERVER(PRIMARY)

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

then i restore the log to 192.168.10.131/MIRROR(MIRROR)

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

Ok fine .Then MSSQLSERVER(PRIMARY) in AdventureWorks2014

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

BUT WHEN i start Mirror it shows following windows

After clicking Yes the the error message

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

Answer 1

You May also want to check to see that port 5023 is open.

1. From command prompt - type the following.

Telnet Waltonserver 5023

2 . If you don't have telnet installed do the following first, here are the directions.

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

3. After installing telnet, Test your telnet on a server you know is running on port 1433

Telnet ServerName 1433

If this works, you'll get an open box pop up that you can type into. If it fails you will get a message....could not open connection to host on port 1433. If you can telnet to other servers on port 1433 but not to port 5023, then you know the port isn't open. then ask your admin to open port 5023.

Answer 2

Goto services.msc and check the sql server is running under which account. Make sure that sql server and sql server agent services should run with same credentials.

in the mirror database server you should do the same step as step 1. Give the same credentials as in principal server. If that user is not present create a new one on the both servers with same credentials.

Now got principal server and in sql server add the new login under Security-->Login. Give the server roles as Sysadmin and public. Add the same user on the mirror server also.

Now do the mirror in the principal database. You did not get any error there.

Answer 3

Here are a few things you may want to try:

1) Make sure firewall is not blocking your SQL ports. Go to Windows Firewall with Advanced Security -> Inbound\Outbound Rules -> New Rule -> Port -> Specific local ports : Set 5022,5023 -> Allow the connection

2) Check if the Login created on each instance has sysadmin role : In Management Studio connect to each instance -> Security -> "your_login" -> Properties -> Server Roles -> sysadmin. Also when you configure mirroring , in the Service accounts tab, you should put your account credential in the Principal and Mirror boxes.

3) Go to SQL Server Configuration Manager and make sure that both your instances and SQL agent run under the same account (preferably yours). Also check in SQL Server Network Configuration for each instance if TCP\IP protocol is enabled.

4) On each instance try to drop and recreate your mirror endpoint. Run the following script on 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 this suggestions help. Good luck!