March 18, 2009 at 6:25 am
HI ALL....
I want to implement database mirroring in ms sql server 2008.I am facing the issue related server network.
The steps I have performed after the database bckup and log backup is retored on the mirror server
->Created a endpoint on the primary server.
->Create a endpoint on the Mirror server (by the same syntax, with Port number 5023).
->Grant permission to endpoint (Both on primary and Mirror server) with
GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRRORING TO USER.
(message: Command(s) completed successfully.)
Now when I execute the following alter syntax on the mirror server.
ALTER DATABASE DB_NAME
SET PARTNER = ’TCP://Primary_server:5022’
it gives me the message. ”Command(s) completed successfully.”
But when I execute the syntax on the Primary server
ALTER DATABASE DB_NAME
SET PARTNER = ’TCP://MIRROR_server:5023’
it gives me the message
“Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://MIRROR_SERVER:5023" 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. “
When I ping It works fine…
I have set the Firewall OFF
When I write telnet command on the command prompt (C:\telnet Mirror_server:5023 )
Connecting To Mirror_server...Could not open connection to the host, on port 5023: Connect
Failed
Same message I got on the Mirror server when I try to telnet other server port.
I have Enable the Protocols on both the server
plz sugges some solution.....
thx in advance.....
March 18, 2009 at 8:08 am
What is the sys.tcp_endpoints saying regarding to the ports are they really there and the ones you use. Use netstat to see if the server is really listening on that port. And check if the endpoints are started in sys.database_mirroring_endpoints.
March 18, 2009 at 8:46 am
hi...
thx for the reply..
The sys.tcp_endpoints shows the ports both on primary and mirror server.
The endpoints are started in sys.database_mirroring_endpoints.
but when I use netstat on cmd prompt, server name displays same name in the local address and foreign address(both on primary and mirror server).
plz sugest some solution...
March 18, 2009 at 11:00 am
firewall turned on or something like that? is encryption enabled on the connection on any of the databases.. is the mirror db in recovery mode?
March 18, 2009 at 7:08 pm
Execute this to confirm the Endpoint are enabled/started state:
SELECT name, role_desc, state_desc
FROM SYS.database_mirroring_endpoints
If state is not started then execute ..
ALTER ENDPOINT [EndPointName]
STATE=STARTED
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 18, 2009 at 10:37 pm
the mirror db in recovery mode
March 18, 2009 at 11:55 pm
Hi..
WHEN i SEE THE ERRORLOG,the message on the primary server errorlog was
An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.
and On the mirror server errorlog, the message was
Database Mirroring login attempt by user 'MSSQL\ACUTE$.' failed with error: 'Connection handshake failed.
March 19, 2009 at 12:04 am
The account you gave access is it not Windows account?
If you can't use domain account then you need to use certificates to setup authentication between nodes.
Look at: http://technet.microsoft.com/en-us/library/ms191477.aspx
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 19, 2009 at 1:15 am
HI...
I HAVE TRIED MIRRORING BY CREATING THE CERTIFICATE
i HAVE RUN THE FOLLOWING CODE(BOTH ON PRIMARY AND MIRROR SERVER)
--use master
--go
--create master key encryption by password='PASSWORD';
--GO
--CREATE CERTIFICATE MSSQL_CERTIFICATE WITH SUBJECT='MSSQL certificate for database mirroring';
--go
--CREATE ENDPOINT ENDPOINT_MIRRORING
--STATE=STARTED
--AS TCP(LISTENER_PORT=5022,LISTENER_IP=ALL)
--FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE MSSQL_CERTIFICATE,
-- ENCRYPTION=REQUIRED ALGORITHM AES,
-- ROLE=ALL );
-- GO
--BACKUP CERTIFICATE MSSQL_CERTIFICATE TO FILE='C:\CERTI\MSSQL_19MARCH.CER';
--GO
----------------------------------------------------------------------
(ON MIRROR)
--CREATE LOGIN LOGIN_NAME WITH PASSWORD='PASSWORD'
--CREATE USER USER FOR LOG LOGIN_NAME
--GO
-----------------------------------------------------------------------
--ALTER CERTIFICATE MSSQL_CERTIFICATE SET AUTHORIZATION SUSHANT
--FROM FILE='C:\CERTI\MSSQL_19MARCH.CER';
--CREATE CERTIFICATE SUSHANT AUTHORIZATION SUSHANT
--FROM FILE='C:\CERTI\MSSQL_19MARCH.CER'
--GO
--GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRRORING TO USER
bUT STILL i GOT THE ERROR MSG "THE SERVER NETWORK ADDRESS DOES NOT REACH OR DOES NOT EXIST"
PLZ HELP ME...
March 19, 2009 at 5:23 am
hi....
WHEN i USE WINDOWS AUTHENTICATION FOR DATABASE MIRRORING iT GIVES ME ERROR MSG
"Cannot connect to MSSQL\MSSQL08.
------------------------------
ADDITIONAL INFORMATION:
Failed to connect to server MSSQL\MSSQL08. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Login failed for user 'MSSQL\Guest'. (Microsoft SQL Server, Error: 18456)"
PLZ HELP.
March 19, 2009 at 8:08 am
Please execute ...
SELECT *
FROM sys.database_mirroring_endpoints
On both your nodes and paste results.
The errors you are getting with Windows Authentication indicates that the account credintials are not being passed accoross servers because not a domain account. So a guest account or Network Services account is being used.
The certificate script looks correct...
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply