4

I am trying to set up SQL Server 2008 R2 mirroring but I am getting stuck with this error:

An error occurred while starting mirroring.

Additional Information:
Alter failed for Database 'MyDB'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

The server network address "TCP://10.39.113.135:5022" can not be reached or does not exist. Check the network address name and that the ports for the local an remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

  • I can ping 10.39.113.135 (the mirror) from the principal server.
  • I can connect with telnet to 10.39.113.135:5022 from the principal server
  • netstat -n shows that there are no connections on 5022, which means it's not being used by anything else
  • The endpoints configuration step executes properly, and I confirmed both ends are configured correctly by following these steps: http://msdn.microsoft.com/en-us/library/ms189127.aspx
  • I did prepare the backup DB which I recovered onto the mirror server with the NO RECOVERY clause

So any ideas why I'm getting error 1418?

Here's the info requested by Mark Storey-Smith:

Principal:

name                                                                                                                             endpoint_id principal_id protocol protocol_desc                                                local_net_address                                local_tcp_port type type_desc                                                    state state_desc                                                   is_admin_endpoint
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ -------- ------------------------------------------------------------ ------------------------------------------------ -------------- ---- ------------------------------------------------------------ ----- ------------------------------------------------------------ -----------------
Dedicated Admin Connection                                                                                                       1           1            2        TCP                                                          NULL                                             NULL           2    TSQL                                                         0     STARTED                                                      1
Mirroring                                                                                                                        65536       1            2        TCP                                                          NULL                                             NULL           4    DATABASE_MIRRORING                                           0     STARTED                                                      0
TSQL Default TCP                                                                                                                 4           1            2        TCP                                                          10.55.31.223                                     1433           2    TSQL                                                         0     STARTED                                                      0
TSQL Default VIA                                                                                                                 5           1            5        VIA                                                          NULL                                             NULL           2    TSQL                                                         0     STARTED                                                      0
TSQL Local Machine                                                                                                               2           1            4        SHARED_MEMORY                                                NULL                                             NULL           2    TSQL                                                         0     STARTED                                                      0
TSQL Named Pipes                                                                                                                 3           1            3        NAMED_PIPES                                                  NULL                                             NULL           2    TSQL                                                         0     STARTED                                                      0

(6 row(s) affected)

Mirror:

name                                                                                                                             endpoint_id principal_id protocol protocol_desc                                                local_net_address                                local_tcp_port type type_desc                                                    state state_desc                                                   is_admin_endpoint
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ -------- ------------------------------------------------------------ ------------------------------------------------ -------------- ---- ------------------------------------------------------------ ----- ------------------------------------------------------------ -----------------
Dedicated Admin Connection                                                                                                       1           1            2        TCP                                                          NULL                                             NULL           2    TSQL                                                         0     STARTED                                                      1
Mirroring                                                                                                                        65536       1            2        TCP                                                          NULL                                             NULL           4    DATABASE_MIRRORING                                           0     STARTED                                                      0
TSQL Default TCP                                                                                                                 4           1            2        TCP                                                          10.39.113.135                                    1433           2    TSQL                                                         0     STARTED                                                      0
TSQL Default VIA                                                                                                                 5           1            5        VIA                                                          NULL                                             NULL           2    TSQL                                                         0     STARTED                                                      0
TSQL Local Machine                                                                                                               2           1            4        SHARED_MEMORY                                                NULL                                             NULL           2    TSQL                                                         0     STARTED                                                      0
TSQL Named Pipes                                                                                                                 3           1            3        NAMED_PIPES                                                  NULL                                             NULL           2    TSQL                                                         0     STARTED                                                      0

(6 row(s) affected)
Improve this question

3 Answers 3

Reset to default
2

Have you checked the connection from mirror to principal with telnet? Have you restored a log backup at the mirror?

I posted a couple of diagnostic queries in an earlier q/a on mirroing. Run those and update your answer with the output.

Improve this answer
3
  • Yes, the mirror can connect to the principal with telnet. I can't restore the log backup, apparently because I used the WITH NORECOVERY clause: "The selected database, MyDB, cannot be restored in its current state. (SqlManagerUI)"
    – David
    Oct 14, 2011 at 8:37
  • If you used NORECOVERY, the database would be accept further restores. RECOVERY brings it online. If you are unable to restore a log, that's likely to be the reason you can't initiate the mirror.
    – Mark Storey-Smith
    Oct 14, 2011 at 9:03
  • I used NORECOVERY but it's not accepting further restores, since I can't restore the log. So why would that be?
    – David
    Nov 1, 2011 at 6:43
0

On each instance check the Mirroring endpoint and make sure both are running on 5022 (script it out to see easily, don't know the DMV to use) and check for an inbound and outbound host based firewall rule (win2k8 turns this on by default) on both principal and mirror. If the 2 computers are in different domains then you will need to use certificate based authentication for mirroring. Make sure you are taking a full backup and a log backup to restore to the mirror, both with NORECOVERY option.

Improve this answer
2
  • They are both using 5022. The firewall rules are also set properly since I can telnet 5022 both ways. The 2 computers are in different domains but I am using SQL authentication so it shouldn't matter, should it? I am taking a full backup and full log backup to restore, both with NORECOVERY. Anything else?
    – David
    Nov 1, 2011 at 6:45
  • If you're trying to connect two different domains then you needs to set up certificates on all the servers involved and then map those certificates to logins. Refer to msdn.microsoft.com/en-us/library/ms186384.aspx for setting that up.
    – Jason Cumberland
    Nov 7, 2011 at 14:41
0

It turns out that I was trying to do Asynchronous Database Mirroring on Microsoft SQL Server 2008 R2 Standard Edition, which does not support this feature.

To use asynchronous mirroring, I would have to use the Enterprise Edition.

I wish the database would have given me a more pertinent error like "Please upgrade to the Enterprise Edition". Oh well.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.