April 21, 2009 at 6:08 am
We have a 2 node clustered SQL 2005 STD server on our live site that employees DB mirroring to a single node 2005 STD server on the DR site with no witness server.
When we try to fail between the nodes on the cluster all the databases that are mirrored display the (principal, disconnected / In Recovery) and are not accessible. If we then fail back to node one the mirrored DB's return to (Principal / Syncronized).
Does anyone have any ideas? I've tried googling the error and looking on SQL forums but i'm not really getting anywhere? Only ideas i have is that its caused by 2005 STD only allowing synchronous mirroring and the two phase commit causing issues with the clustering.
April 21, 2009 at 8:07 am
Question, do you have your mirroring looking at the literal server name for the source? Or are you using the virtual cluster name for your mirrored source?
I'd say, at a guess, your Mirroring breaks every time you fail the cluster over because it's looking at a specific server name that is no longer available. Using the cluster's virtual name should fix this.
On the other hand, Mirroring is general used in place of clustering. So when the primary server fails, the mirrored DB is supposed to take the place of the primary. Doing it the way you have is a little odd, which is probably why you can't find the articles on it.
April 21, 2009 at 8:33 am
Ahh good idea I didnt think of that! I've just checked and it is set to use the SQL network name cluster resource rather than the individual node name. Is that what you mean?
The thinking behind the cluster and mirroring was to provide a highly available SQL server at the live site and then use mirroring to replicate the DB's to the disaster recovery site.
April 21, 2009 at 8:54 am
Mackempete (4/21/2009)
Ahh good idea I didnt think of that! I've just checked and it is set to use the SQL network name cluster resource rather than the individual node name. Is that what you mean?
That's exactly what I meant.
Honestly, if you've already got that set up properly then, I'm not sure what the entire problem is other than you're using 2 disaster recovery tools for one purpose.
You might be better off using replication or log shipping for your single node server.
April 21, 2009 at 9:23 am
Is the endpoint set up on both clustered instances? I could see that as an issue.
April 21, 2009 at 10:34 am
If it's an Active / Passive cluster, Steve, there won't be necessarily be multiple instances. One instance that swaps back and forth between the cluster as it fails is the way I'm reading it.
And even on an Active/Active cluster, I've never seen a setup where a database can be on both clustered instances at the same time. Doesn't mean it can't be done, but I haven't figured out how to do it.
However, if the ports aren't set up on the other server (passive node), then you're absolutely correct that it could be the issue.
April 21, 2009 at 10:40 am
Sorry, my bad verbiage. I meant node.
The endpoint is a server setting, and depending on how the cluster was set up, I was thinking that perhaps the endpoint didn't get created on one node.
April 21, 2009 at 10:46 am
Ah, yes. You're absolutely correct on that. @=)
April 21, 2009 at 10:52 am
Not sure I'm correct. Kind of throwing things out in the dark. You covered a lot of things, that was my small guess as to the issue.
April 21, 2009 at 11:09 am
Just went back to my old "Upgrading DBA skills to SQL 2005" book.
Before establishing a mirroring session, you must configure the communication mechanism through which mirroring will occur. This involves ensuring that the appropriate endpoints are created on each server instance that will participate in the mirroring operations...
Endpoints are instance level, according to this. So if they're already configured, then they should fail over with the instance. If, however, the proper ports on the passive node aren't opened correctly, that could cause a problem.
April 22, 2009 at 4:18 am
Thanks for your help guys. I dont think it will be a port problem as both nodes of the cluster are built exactly the same (or atleast they should be!). We dont have any port blocking configured between our sites but I'll check to see if there is anything blocking the ports on node 2. I take it i should be checking if 5022 is open and can connect to the target server for the mirroring at our DR site?
Thanks
Pete
April 23, 2009 at 4:14 am
Check whichever port your endpoint uses. If it's the default (5022), check that. But there is usually 2 ports involved (ISTR) in the creation of endpoints, so make sure they are both open.
Other than that, are you using synchronous or asynchronous mirroring?
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply