March 31, 2009 at 12:49 pm
I am tring to set up database mirroring accoss domain using certificate authentication without witness server. but I couldn't make it. I don't know if anyone had such experience.
Primary server:
Windows 2003 32bit, SQL 2005 32 bit Standard edition SP3; SQL service is running on domain account, say domain1\sqlservice
Seconday server: Windows XP; SQL 2005 32 bit Standard edition SP3; SQL service is running on domain account, say domain2\myaccount
On both servers, FQDN is set on Hosts file and port number is open (netstat -an)
When I tried to issue alter command, the error happened.
alter database Mydb set partner='tcp://secondaryserver.domain2.com:5022'
The server network address "tcp://secondaryserver.domain2.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.
Any ideas?
March 31, 2009 at 12:53 pm
One thing in Mirroring is u have to set up your SQL server Agent and your SQL server under same account. Do it same thing with your Mirror server too.
March 31, 2009 at 1:26 pm
Mayank Khatri (3/31/2009)
One thing in Mirroring is u have to set up your SQL server Agent and your SQL server under same account. Do it same thing with your Mirror server too.
thanks your reply.
Yes, they are on the same domain account.
primary server/agent is under domain1\sqlservice
mirroring server/agend: under domain2\muaccount
March 31, 2009 at 1:35 pm
Is it possible for u to use same account for your Mirror server and Mirror Agent too(the one used by your Primary server)?
March 31, 2009 at 1:37 pm
have u tried using Mirroring Wizard too?
March 31, 2009 at 1:42 pm
Mayank Khatri (3/31/2009)
Is it possible for u to use same account for your Mirror server and Mirror Agent too(the one used by your Primary server)?
the both server are in the two different domains. domains are not trusted. one is in production side. one is in developement side. this db mirroring is just for my testing.
March 31, 2009 at 1:43 pm
Mayank Khatri (3/31/2009)
have u tried using Mirroring Wizard too?
Yes, same error.
March 31, 2009 at 5:28 pm
Mayank Khatri (3/31/2009)
One thing in Mirroring is u have to set up your SQL server Agent and your SQL server under same account. Do it same thing with your Mirror server too.
Clarification :
Database Mirroring does not depends on SQL Server Agent in any sense, it really does not matter on which account SQL Server Agent is running, even if you stop SQL Server Agent service, Database Mirroring will still work fine.
Reason : Database Mirroring is done through TCp-IP, unlike Log Shipping. Log Shipping requires you have your SQL Server Agent up every time because of backup and Restore jobs.
I have not done Database Mirroring using certificate. (I do understand you are using Certificate for Authentication because you have your two instances on two different / Non-trusted Domains).
I have always used Windows Authentication to set up Database Mirroring. Here are few Tips that are purely based on my experience...
1. Always try to set up Database Mirroring from Mirror Server. (Log into Mirror Server, Using SSMS connect to Principal Server and then configure Mirroring).
2. Always take a Full backup and a fresh Log backup of your principal database, and Restore your backup with no recovery option and then restore your log backup with no recovery option on Mirror Server, keeping the Mirror database name same as Principal ( Main point, Apply Transactional Log backups on mirror database before configuring Mirroring).
3. Make sure, you are not using same endpoints across different SQL Server instances on the same Server.
It took me nearly a week to figure out this Problem,
Here is a best example.
I have DEV, TEST and STG on the same Server , say Server_A
I have PROD on some Server, say Server_B
I have DR on some other server, say Server_C
I had already configured Database Mirroring between DEV and PROD and Dev and DR and it was running fine with no issues. ( I did this like an year ago )
Now when I was try to configure Database Mirroring from TEST to PROD, it was giving me the same error message what you received. I tried configuring Database Mirroring from STG to PROD same issue.
Error Message, it says cannot connect because servername cannot be reached Tcp IP... all other crap...Trust me you get same error message for at least 3 - 4 types of different errors...
Problem Resolved After spending a week:
1. I Used Port: 5022 on my Server_A ( Dev) while creating an end point when I set up Database Mirroring_A between Server_A and Server_B (Prod). Meaning Port 5022 is already reserved for DEV Instance of Server_A.
2. When I was setting up Database Mirroring between TEST and PROD, I was using same port (5022). Because TEST Instance was on the same box as DEV instance and DEV Instance was using Port 5022 for its database Mirroring, TEST Instance was not able to use 5022 Port for its end point and that is why I was not able to configure database mirroring
How I resolved Issue :
1. very Simple : Use different port numbers (any number other than 5022) for Test Instance and then try to configure database Mirroring.
I wanted to be very sure that this particular problem was the issue, just to verify that, I removed Database Mirroring between DEV and PROD. Deleted End Points on Dev Instance (Using SSMS). And Used 5022 as port while configuring database Mirroring between TEST and PROD, It worked !!!
I configured database mirroring between DEV and PROD like months ago, so I forgot about DEV Instance being mirrored with Production. That is why all confusion....
Let us know, if you still getting error.
~IM.
April 2, 2009 at 12:56 pm
Thanks for all the replys.
Unfortunately, I didn't solve the problem, the same network error.
Principal server is domain1 which is located in production area. The production area has firewall.
Mirroring server is domain2 which is located in developement area. The production area has firewall.
They connect by VPN.
When I issued "alter database climara set partner='tcp://mirror.domain1.com:5022" from principal server, network error happened.
But "alter database climara set partner='tcp://principal.domain2.com:5022" from mirroring server, it succeeded.
I asked network administrator to open tcp port on developement firewall even though I saw the port open in mirroring server using "netstat -an".
I will let you know whether or not it is working.
April 3, 2009 at 9:24 am
Finally, I made it working after the new rule was added to firewall.
thanks for all your help.
April 3, 2009 at 12:33 pm
You can only have 'Full Safety DB Mirroring' in SQL Server 2005 SE, which means that you will need a Witness server if using standard edition.
April 3, 2009 at 1:58 pm
No. I have set up database mirroring on SQL server 2005 standard edition without witness server.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply