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 /ALLTo 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?
