February 4, 2010 at 5:34 am
Hi,
I have one db server which has two instances. One is A for principal and the other one B is for mirror. TCP/IP port for A is 1433 and for B is 1434.
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022 )
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022 )
FOR database_mirroring (ROLE = PARTNER);
GO
The above queries are for principal. For mirror 5023 has been given.
the above mentioned ports 5022 and 5023 are pinging.
we have verified with the commands in cmd promt. telnet localhost 5022 and telnet localhost 5023.
ALTER DATABASE DBM
SET PARTNER ='TCP://xxx-test1:5023'
Executing the above statment in Principal, getting error.
"The server network address "TCP://xxx-test1: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.
(Microsoft SQL Server, Error: 1418)
Please help me to come out of this error.
February 4, 2010 at 2:22 pm
Good for telnet'ing to the ports to see if they are available.
Questions:
- Check to make sure you are not using the local service account for either of the instances (Otherwise, you'll need to use certificates).
If you're on a domain, drop these endpoints and configure them using their FQDN. Otherwise, you'll need to use certs if you don't have a domain, or use local (see article below).
- If not using certs, the MSSQLSERVER service account -- SQLServerMSSQLUser contains the same user account, right? Also, you have logged into the machine with that user account, right?
For more information on this topic, read here:
And here:
http://blogs.msdn.com/grahamk/archive/2008/12/08/database-mirroring-error-1418-troubleshooter.aspx
If all those are not an issue:
- Have you shut down both instances of SQL and attempted to telnet to those ports on localhost?
- Checked to make sure there is no firewall in preventing access? (shouldn't be an issue if you can telnet, but it's easy to check)
- Taken both a full & transactional backup of DBM(5022) and restored them to DBM(5023) in the no_recovery state?
Just some leads. Hope they help.
-Ken
February 4, 2010 at 8:58 pm
Use BDCC TRACEON (1400) and alter the Startup parameter in configuration manager of SQL SERVER ;-T1400
· RUN --> Type sqlservermanager.msc
· Right click on SQL Server(instancename) service and click on properties
· Click on Advanced tab
· In the startup parameters enter this ;-T1400 and click on OK
· Restart SQLservices and then try configuring db mirroring
Or apply SQL Server 2005 Service Pack 2 for both the instances.
Just ADD on to this... Hope you have already tried. But still a thought..
Cheers,
- Win.
" Have a great day "
February 4, 2010 at 11:23 pm
i have tried with FQDN and
SP2 also has been installed and tried.
getting the same error.
both instances are having same account - local system.
February 5, 2010 at 1:33 am
If thats a test server or yur local machine.
Change the SQL Server service accounts to domain account or Network Service and restart the services.
Start configuring mirroring. And the database needs to be restored with Full backup and Trans log backup with NO RECOVERY and then start.
Just a comeback: Of course yu might ahve done .... but please cross check the things and the changes you have done once again from root level.
What is the SQL Server EDITION you are using ?
- Win.
Cheers,
- Win.
" Have a great day "
February 5, 2010 at 1:50 am
Thanks for the reply.
It is a Test server.
M using Local System account in both instances (SQL Server services) .
My System Account is Administrator.
i connected both instances with windows login.
Standard Edition with SP2 has been installed.
February 5, 2010 at 2:02 am
Update the services with domain account / network service and restart the services.
Delete the endpoints and retry creating the endpoints again.
Follow: http://www.sql-server-performance.com/articles/clustering/mirroring_2005_p3.aspx
Try configuring.
Nice times...
- Win.
Cheers,
- Win.
" Have a great day "
February 5, 2010 at 2:15 am
SELECT * FROM sys.endpoints
sys.endpointsAll endpoints and all generic properties
sys.database_mirroring_endpoints The Database Mirroring endpoints
----------------------------------------------------------------------------------------------
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED AS TCP ( LISTENER_PORT = 7022 )
FOR DATABASE_MIRRORING
( AUTHENTICATION = WINDOWS KERBEROS,
ENCRYPTION = SUPPORTED,
ROLE=ALL);
GO
--------------------------------------------------------------------------------------------
GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser]
--------------------------------------------------------------------------------------------
From one of the URLs - not remembered.
In this example we are inside a DOMAIN. We have a domain user called MRRUSR
1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.
2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.
3) MRRUSR is the MSSQLSERVER service account in both machines
4) Machine A is gonna be the PRINCIPAL, B the MIRROR.
5) Log on in my machine as MRRUSR
6) Open Sql server Studio. Connect to both machines using windows authenticacion.
7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data
8) Backup the new DB (Full backup) with .bak extension
9) Backup the new DB (Transaction log) with .trn extension
10) Copy the both files to a location in B machine.
11) Restore .bak into a new DB using NORECOVERY option
12) Restore .trn into the previous DB using NORECOVERY option
13) Go to Machine A, open a new query
14) Take a look to the endpoints
SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;
15) Delete existing ones (DROP ENDPOINT [NAME])
16) Create a new endopoint
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
17) Start it
ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO
18) Do steps 13 to 17 in machine B.
19) Return to machine A. Query.
20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.
ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'
21) Repeat step 20 from MACHINE B pointing to MACHINE A
22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.
23) Lets test them: Add some new data in Machine A database.
Good Luck..!!
Cheers,
- Win.
" Have a great day "
February 5, 2010 at 2:18 am
I think you can try this and test.
http://wiki.cdyne.com/wiki/index.php?title=Mirroring_SQL_Server_2005
Cheers,
- Win.
" Have a great day "
February 5, 2010 at 4:47 am
thanks winslet.
let me try those options.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply