0

I am trying to mirror a database from my local SQL server instance to another SQL server instance found on the network using SQL Server management studio.

I am having troubles providing the TCP:// Server Network Addresses.

I did go through this documentation to better understand the fully qualified TCP addresses.

I am using the mirroring wizard, not transact-sql.

The local SQL server is correctly connected, my problem lies down with the TCP address of the PC (which hosts the other SQL server instance) on the network. Note that I can connect to the server from my local machine using the management studio with no problems at all.

I used these two queries to make sure that I've specified the exact port and that there's no problems:

SELECT * FROM sys.database_mirroring_endpoints;
SELECT * FROM sys.tcp_endpoints;

Result:

name                        endpoint_id principal_id protocol protocol_desc  type type_desc            state state_desc  is_admin_endpoint role role_desc  is_encryption_enabled connection_auth connection_auth_desc  certificate_id encryption_algorithm encryption_algorithm_desc
--------------------------------------- ------------ -------- -------------- ---- -------------------- ----- ----------- ----------------- ---- ---------- --------------------- --------------- --------------------- -------------- -------------------- -------------------------
Mirroring                   65537       1            2        TCP            4    DATABASE_MIRRORING   0     STARTED     0                 1    PARTNER    1                     3               NEGOTIATE             0              1                    RC4

(1 row(s) affected)

name                        endpoint_id principal_id protocol protocol_desc  type type_desc            state state_desc  is_admin_endpoint port        is_dynamic_port ip_address
--------------------------------------- ------------ -------- -------------- ---- -------------------- ----- ----------- ----------------- ----------- --------------- -----------
Mirroring                   65537       1            2        TCP            4    DATABASE_MIRRORING   0     STARTED     0                 5022        0               NULL

To make sure that I am entering the fully qualified TCP address in the wizard, I did follow the instructions from the documentatiom mentioned above, and I quote:

To find the fully qualified domain name of a system, at the Windows command prompt on that system, enter:
IPCONFIG /ALL

To form the fully qualified domain name, concatenate the values of <host_name> and <Primary_Dns_Suffix> as follows:

<host_name> . <Primary_Dns_Suffix>

For example, the IP configuration Host Name . . . . . . : MYSERVER Primary Dns Suffix . . . : mydomain.Adventure-Works.com equates to the following fully qualified domain name:

MYSERVER.mydomain.Adventure-Works.com

So what I am entering in the Mirror Server Network Address is what I found by using ipconfig /all which is:

TCP://CIC-SERVER2008.X.com:5022

Sadly, whenever I try to click on Start Mirroring, I am getting the following error/exception:

TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'TestMirroring'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address "TCP://CIC-SERVER2008.X.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

Am I doing anything wrong? Or am I missing something obvious?

Edit:

I tried to run netstat -an on my network PC, and port 5022 is listening.
I also tried to telnet using the fully qualified name, and it worked locally (from the PC itself). However, when I tried to telnet through my own PC, I got the following error:

C:\Users\User>telnet CIC-SERVER2008.X.com 5022
Connecting To CIC-SERVER2008.X.com...Could not open connection to the host, 
    on port 5022: Connect failed

But If I telnet CIC-SERVER2008 5022, it works.

I was also going through this answer here and I tried to run the following query:

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; 

The result I got was the next:

name                STATE GRANTOR PERMISSION GRANTEE
------------------- ----- ------- ---------- -------
TSQL Local Machine  G     sa      CO         public
TSQL Named Pipes    G     sa      CO         public
TSQL Default VIA    G     sa      CO         public

There's no TSQL Default TCP like found in that answer, does this have anything to do with my problem?

UPDATE:

After a discussion with @hunterjrj, we figured out that it would be better to use mirroring with certificate login, since my PC and the network PC are not on the same domain.

So I followed the example setting up database mirroring using certificates transact sql.

It all works well until the last query I should run which is:

ALTER DATABASE AdventureWorks   
    SET PARTNER = 'TCP://HOST_B.Mydomain.Corp.Adventure-Works.com:7024';

What is confusing and seems weird for me is these two scenarios:

First:

  • I set up my PC as the mirror for a test.
  • I was able to run the ALTER DATABASE DbName Set Partner = 'TCP://CIC-SERVER2008.X.com:5022' on the mirror instance.
  • I wasn't able to run the ALTER DATABASE DbName Set Partner = 'TCP://x.x.x.x:5022' on the principal instance.

Second:

  • I set up my PC as the principal for a test.
  • I was able to run the ALTER DATABASE DbName Set Partner = 'TCP://x.x.x.x:5022' on the mirror instance.
  • I wasn't able to run the ALTER DATABASE DbName Set Partner = 'TCP://CIC-SERVER2008.X.com:5022' on the principal instance.

Why would it always fail when trying to set the partner from the principal to the mirror eventhough when it's the other way round it works with the same TCP Address?

0

I was finally able to solve this problem thanks to @hunterjrj's help.

The first steps I had to take was setting up the mirroring using certificate because my two PCs were not on the same domain, yet same network. (Curtosy of hunterjrj).

After going through all the steps in example setting up database mirroring using certificates transact sql, I still had the same problem.

What took my attention was that the problem was reproduced even if I reverse the roles of Principal and Mirror, I.E: If my CIC-SERVER2008 was principal, I would have been able to set it's TCP address from the Mirror instance, but if CIC-SERVER2008 was mirror, I wouldn't have been able to set it's TCP address from the principal instance. It was always the mirror not being able to be set from the principal.

After lots of guesses, I came down to my last try, which is to change the port.

Instead of using the same port for both of the instances, I used 5022 for the principal, and 5023 for the mirror. This fixed my issue.

The problem might be due to both PCs being on same network. Hopefully this can help someone else having the same problem.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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