October 11, 2010 at 8:16 am
Hello,
First of all i hope i'm not creating a double post.
I've a problem with my SQL 2008 R2 mirror configuration. This is a configuration with a witness server.
Server A = Principal database
Server B = Mirror database
Server C = Witness Server
When i initiate a failover from Server A to Server B the clients wil continue working and new connections wil be made normally.
When i initiate a failover by disabling the NIC's on Server A. The witness server set the mirror database on Server B as principal database. Now the clients cannot work anymore. New clients will get a network related-error.
When i let TPCView from Sysinternal run in the background i see that the application is trying to create a connection to Server A (who is now down) and won't even bother to try to find the mirror (now principal) database.
The application uses a UDL (Data Link) with the folowing configuration:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Database1;Data Source=Server A;Failover Partner=Server B
This UDL is using the SQL Server Native Client 10.0.
What am i doing wrong? I hope you guys/girls can help me out.
Thanks!
October 18, 2010 at 5:32 am
I'm currently experiencing exactly the same problem with a mirrored and witnessed SQL Server 2008 (non R2) mirror. As the principal disappears, either the box (via a network cable pull out) or the instance via a process kill, the failover occurs, but clients just wont try and use the FailoverPartner in the connection string. Any ideas on how to diagnose what is going on would be great, as we're pulling out hair out here!
Alex.
October 18, 2010 at 7:15 am
Hello Alex,
I did resolve the problem (in my case). What I did is changed the provider to OLEDB Provider for SQL Server and added the extended property "Network=dbmssocn"
The connection string now is:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Database1;Data Source=Server A;Extended Properties="Network=dbmssocn;Failover Partner=Server B".
It works like a charm now 🙂
Please let me now if this is the solution for your problem.
October 19, 2010 at 6:07 am
Hey Sjoerd,
Thanks for your reply. Unfortunately our applications are all heavily reliant on LINQ-To-SQL and thus on the SqlConnection .NET class which does not accept the Provider or Driver connection string parameters because it is only for SQL Server. As regards to specifying TCP/IP connection only (dbmssocn) this did not appear to help. Also if the principal in the connection string is offline no clients can connect to the server specified in FailoverPartner. This is really infuriating, as the underlying mirroring technology appears to work great but all the .NET client stuff really blows!
Alex.
October 19, 2010 at 7:22 am
Firewall issue! We found the problem/solution via this support document:
http://support.microsoft.com/kb/941526
Specifically the line which says:
Enable User Datagram Protocol (UDP) traffic to the mirror partner server over port number 1434.
We just weren't aware that port 1434 was used!
Hopefully this helps a fair few people.
Alex.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply