locked
unable to create mirror across domain, getting Error: 11001 RRS feed

  • Question

  • My coworker and I have inherited a network with 2 machines, both running Server 2008 Enterprise SP2 and SQL Server 2008 R2. They are in different domains physically separated on a secure connection between 2 ASA’s.

    1 week ago I came in to work to see their status showed (Mirror, disconnected). I attempted to restore the mirror using online help, but to no avail. I broke the mirror in an effort to recreate it. They are in different domains physically separated on a secure connection between 2 ASA’s.

    They don’t have certificates for the mirror and they don’t have a witness.

    When trying to create the mirror, whether we connect via FQDN or IP, we get this error:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)


    Troubleshooting efforts have included:
    -we were able to ping successfully between servers using both IP addresses and FQDN’s

    -we ensured 1433 and 5022 ports were listening on both servers
    -we initiated successful telnet sessions from both mirror computers to the other and verified they are accepting connections

    -verified correct firewall rules for both ASA’s
    -turned off windows firewalls for both machines and disabled McAfee
    -verified both devices have entries in their etc/hosts file
    -confirmed both machines have SQLServer and SQL Server Browser services were running
    -run ‘sqlcmd -L’ and didn’t return either mirror computer, but I believe this is because they are on different domains
    -enabled both Named Pipes and TCP/IP in the Protocols for MSSQLSERVER, making sure TCP/IP is set to 1433
    -checked to make sure SQL Server allows remote connections on both machines
    -added incoming/outgoing domain firewall rules to allow traffic on 1433 and 5022 on both machines. These rules were not originally in place, but we tried it for troubleshooting.

    Can anyone help with some ideas? We didn’t create this connection and aren’t SQL devs, so we are stuck.
    Thursday, January 3, 2013 8:22 PM

Answers

  • Hi,

    Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet FQDN 5022″.

    The accounts under which SQL Server is running must be correctly configured.

    1. Do the accounts have the correct permissions?

      1. If the accounts are running in the same domain accounts, the chances of misconfiguration are reduced.

      2. If the accounts are running in different domains or are not domain accounts, the login of one account must be created in master on the other computer, and that login must be granted CONNECT permissions on the endpoint. F This includes the Network Service account.

    2. If SQL Server is running as a service that is using the local system account, you must use certificates for authentication.

    3. Endpoints must be correctly configured.

      1. Make sure that each server instance (the principal server, mirror server, and witness, if any) has a database mirroring endpoint.

      2. Check that the port numbers are correct.

        To identify the port currently associated with database mirroring endpoint of a server instance.

      3. Make sure that the endpoints are started (STATE=STARTED). On each server instance, use the following Transact-SQL statement         " SELECT state_desc FROM sys.database_mirroring_endpoints"

      4. To start an endpoint, use the following Transact-SQL statement.

         ALTER ENDPOINT Endpoint_Mirroring
        STATE = STARTED
        AS TCP (LISTENER_PORT = <port_number>)
        FOR database_mirroring (ROLE = ALL);
        GO
      5.  Then check that the ROLE is correct. On each server instance use the following Transact-SQL statement.
      SELECT role FROM sys.database_mirroring_endpoints;
      GO
    4. Then login for the service account from the other server instance requires CONNECT permission. Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQL statement.

    SELECT 'Metadata Check';
    SELECT EP.name, SP.STATE,
       CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
          AS GRANTOR,
       SP.TYPE AS PERMISSION,
       CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
          AS GRANTEE
       FROM sys.server_permissions SP , sys.endpoints EP
       WHERE SP.major_id = EP.endpoint_id
       ORDER BY Permission,grantor, grantee;
    GO

    5. Following example shows the server network address for a server instance on a computer system named REMOTESYSTEM3 in another domain. The domain information is NORTHWEST.ADVENTURE-WORKS.COM, and the port of the database mirroring endpoint is 7025. Given these example components, the server network address is.

    TCP://REMOTESYSTEM3.NORTHWEST.ADVENTURE-WORKS.COM:7025

    6.  Also check In the server network address of a server instance, only the number of the port associated with its mirroring endpoint distinguishes that instance from any other instances on the computer. The following example illustrates the server network addresses of two server instances on a single computer. The default instance uses port 5022 and the named instance could use port 5033. The server network address for these two server instances are, respectively:

    TCP://MYSYSTEM.Adventure-works.MyDomain.com:5022

    and

    TCP://MYSYSTEM.Adventure-works.MyDomain.com:5033.



     



    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    Waqas

    MS(SPM), MS(E&F), MCP, MCT, MCTS, MCITP, MCSE, MCPD, MCSD, MCDBA , Author
    Twitter: @waqas8777
    Linked In: http://www.linkedin.com/in/waqasm1


    • Edited by Waqas M Monday, January 7, 2013 11:21 AM additional text
    • Proposed as answer by Waqas M Friday, January 11, 2013 2:18 PM
    • Marked as answer by Maggie Luo Monday, January 14, 2013 2:15 PM
    Monday, January 7, 2013 11:08 AM

All replies

  • you mean it was working earlier for the same setup but not working? did i understand your post correctly?
    Friday, January 4, 2013 4:36 AM
  • Hi,

    Following few steps you can follow

    1) Check connectivity through SQL Management Studio Connectivity. From Principal to Mirror and Mirror To Principal

    2) Check The decrectory stucture (any chnages in directory stucture can cause Mirror to be broken)

    3) Check Steps for establishing Mirror on following links

    http://www.sqlserver-training.com/how-to-set-up-database-mirroring-in-sql-server-2008-video/-

    http://msdn.microsoft.com/en-us/library/ms189852.aspx

    http://www.sqlserver-training.com/how-to-setup-mirroring-in-sql-server-screen-shots/-



    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    Waqas

    MS(SPM), MS(E&F), MCP, MCT, MCTS, MCITP, MCSE, MCPD, MCSD, MCDBA , Author
    Twitter: @waqas8777
    Linked In: http://www.linkedin.com/in/waqasm1

    Friday, January 4, 2013 5:27 AM
  • Yes, it worked before.
    Friday, January 4, 2013 2:39 PM
  • Thank you for your reply. I checked and the file structure has not changed. Looking through one of the links, I think I may be using the wrong address when pointing to the backup.

    Principal

    FQDN: prod-sql01.prod.local

    Instance name: prod-sql01

    Mirror

    FQDN: dr-sql.dr.prod.local

    Instance name: dr-sql

    From the principal, I originally tried to connect to the server with: TCP://dr-sql.dr.prod.local:5022 and got the Error 11001. One of the sites mentioned using <servername>/instance so I tried TCP://dr-sql.dr.prod.local/dr-sql:5022 and got the same error. I figured it is possible I am just not entering the correct address of the mirror server. What should I type for the correct address?

    Friday, January 4, 2013 6:22 PM
  • Hi,

    Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet FQDN 5022″.

    The accounts under which SQL Server is running must be correctly configured.

    1. Do the accounts have the correct permissions?

      1. If the accounts are running in the same domain accounts, the chances of misconfiguration are reduced.

      2. If the accounts are running in different domains or are not domain accounts, the login of one account must be created in master on the other computer, and that login must be granted CONNECT permissions on the endpoint. F This includes the Network Service account.

    2. If SQL Server is running as a service that is using the local system account, you must use certificates for authentication.

    3. Endpoints must be correctly configured.

      1. Make sure that each server instance (the principal server, mirror server, and witness, if any) has a database mirroring endpoint.

      2. Check that the port numbers are correct.

        To identify the port currently associated with database mirroring endpoint of a server instance.

      3. Make sure that the endpoints are started (STATE=STARTED). On each server instance, use the following Transact-SQL statement         " SELECT state_desc FROM sys.database_mirroring_endpoints"

      4. To start an endpoint, use the following Transact-SQL statement.

         ALTER ENDPOINT Endpoint_Mirroring
        STATE = STARTED
        AS TCP (LISTENER_PORT = <port_number>)
        FOR database_mirroring (ROLE = ALL);
        GO
      5.  Then check that the ROLE is correct. On each server instance use the following Transact-SQL statement.
      SELECT role FROM sys.database_mirroring_endpoints;
      GO
    4. Then login for the service account from the other server instance requires CONNECT permission. Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQL statement.

    SELECT 'Metadata Check';
    SELECT EP.name, SP.STATE,
       CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
          AS GRANTOR,
       SP.TYPE AS PERMISSION,
       CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
          AS GRANTEE
       FROM sys.server_permissions SP , sys.endpoints EP
       WHERE SP.major_id = EP.endpoint_id
       ORDER BY Permission,grantor, grantee;
    GO

    5. Following example shows the server network address for a server instance on a computer system named REMOTESYSTEM3 in another domain. The domain information is NORTHWEST.ADVENTURE-WORKS.COM, and the port of the database mirroring endpoint is 7025. Given these example components, the server network address is.

    TCP://REMOTESYSTEM3.NORTHWEST.ADVENTURE-WORKS.COM:7025

    6.  Also check In the server network address of a server instance, only the number of the port associated with its mirroring endpoint distinguishes that instance from any other instances on the computer. The following example illustrates the server network addresses of two server instances on a single computer. The default instance uses port 5022 and the named instance could use port 5033. The server network address for these two server instances are, respectively:

    TCP://MYSYSTEM.Adventure-works.MyDomain.com:5022

    and

    TCP://MYSYSTEM.Adventure-works.MyDomain.com:5033.



     



    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    Waqas

    MS(SPM), MS(E&F), MCP, MCT, MCTS, MCITP, MCSE, MCPD, MCSD, MCDBA , Author
    Twitter: @waqas8777
    Linked In: http://www.linkedin.com/in/waqasm1


    • Edited by Waqas M Monday, January 7, 2013 11:21 AM additional text
    • Proposed as answer by Waqas M Friday, January 11, 2013 2:18 PM
    • Marked as answer by Maggie Luo Monday, January 14, 2013 2:15 PM
    Monday, January 7, 2013 11:08 AM
  • http://oracle-mssql-dba.blogspot.in/p/database-mirroringcreate-and-manage.html

    Use this.

    Create Login of domain\username for windows.

    Assign role of sysadmin.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Wednesday, January 9, 2013 5:51 AM