I have setup a database mirroring session with witness - MachineA is the principal, MachineB is the mirror, and MachineC is the witness. Each SQL Server instance is hosted on its own machine. The mirroring is working correctly. If I submit data to the database on MachineA, and then unplug the network cable on MachineA, MachineB automatically becomes the principal, and I can see the data that I originally submitted to MachineA on MachineB. All the settings are showing correctly in Management Studio.
My issue is with the SQL Native Client and a front-end application that needs to make use of this database. I have setup my front-end application to use the ODBC client and specified the failover server in both the ODBC setup and the connection string. Here is the connection string that I am using :
Provider=ODBC;Database=master;DSN=MachineA;Failover Partner=MachineB;
Everything works perfectly on my front-end application when MachineA is the principal. If I unplug the network cable on MachineA, MachineB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on MachineB.
BUT - if I plug the network cable back in on MachineA (making the database on MachineA the mirror), the front-end application now works and can access the principal database on MachineB. I wrote a quick tester application to verify what I am seeing, and I am convinced that this is what is happening. The mirroring is working perfectly, and everything is setup correctly. The SQL Native Client is setup correctly. The problem is that the automatic failover to MachineB that is built into the SQL Native Client only works if both servers are plugged in.
In this scenario, when I plug both servers in, I know that the front-end app is definitely pulling from MachineB (since the mirror database on MachineA is in recovery mode, it's unavailable, and the front-end app displays the server that it is pulling data from).
Am I using an out-dated SQL Native Client The version number displayed in the ODBC configuration page is 2005.90.2047.00, and is dated 4/14/2006. Has anyone experienced this issue I'm guessing that it's a problem in the SQL Native client, since the mirroring really seems to be working correctly.
Thanks,
Bill
SQL Native Client automatic failover only works when both servers are plugged in
kastanienreis
Hi Steve,
Another quick update.. The application thats using persistent kind of connection to the database is actually using a windows authentication. So it seems like the applications on the front end which dont use the windows authentication fail to get automatically redirected when the primary server is not in network. My guess is that the webserver connections dont get authenticated at all at the very first step when they try to connect to the primary becuase the primary server is not in network.
I'm missing anything or is my guess wrong Could you please help me more on this
thks,
Manikanth.S
MCDBA.
Amadrias
These are my connectionstrings
Data Source=Data1;Failover Partner=Data2;Initial Catalog=webPortal;User ID=id;Password=password;Connection Timeout=5;
Data Source=Data2;Initial Catalog=webPortal;User ID=id;Password=password;Connection Timeout=5
cdolor
All
Looking at the first post I see your connection string contains the database MASTER. In order for Automatic Client Redirect to work, the Database parameter must be a Mirrored database. When I tried it myself using ADO.NET I successfully redirected but when I tried to connect to master i Had the message "Database master is not configured for database mirroring".
Hopefully this helps you all.
Steve
flarmon
Here a possible solution:
You need to use NativeClient >= 2005.90.2047.00 (from SP1).
The Problem is, that the NativeClient have no GUI to set up the used protocols and if NamedPipes is activated the described problem happens.
To deactivate NamedPipes you must change the following Registry-Entry:
HKLM/Software/Microsoft/MSSQLServer/Client/SNI9.0 ProtocolOrder sm tcp
If there is an Entry like np you should delete this.
An other way is you have install the SQL-Server Configurationtool, then you can set it up via GUI. Change the Entry SQL Native Client-Configuration / Clientprotocols / Named Pipes to deactivated.
Best regards,
Ralf
P.S.: A lot of Thanks to our Coach to find this solution!
SB1
I'm also experiencing a similar issue.
I'm looking into high-availability on a budget and database mirroring under 2005 looked ideal. I have a combination of applications running in the environment - some web, using connection strings (should be no problem) and some using ODBC data sources.
For the ODBC sources I configured a connection on a machine using the SQL native client to a 2005 SQL database that was being mirrored and included the mirror name in the client configuration.
However, I ran an append query, then failed the principal server (allowing the mirror to take over as the principal) and then ran the append again only to see the following error:
"[Microsoft][SQL Native Client]TCP Provider: The specified network name is no longer available. (#64) [Microsoft][SQL Native Client]Communication Link Failure (#64)"
Shouldn't the failover to mirror be automatic I know that the client is bound to the ODBC datasource but I thought from my reading that the SQL Native Client supported this.
Ceds
BIll
What was the error you are gettinng when connecting the application to B server
avinash kundal
Hi,
Is there any solution found out for this.. I see the same problem with one of my applications that use ADO.NET and ADO connection strings. However i had another application that uses some kind of persistent connection to the database. I mean if the connection to the database fails, application will keep retrying till it makes a connection. So i dint had any problem with the later application. I'm waiting to know should i change the application code for the earlier application i mentioned , or is there any solution
Could someone please do reply
thks,
Manikanth S
MCDBA
zand108
Bill, thank heavens some one out there is having the same problem. It was my understanding that when using the SQL Native client, automatic failover is supposed to happen even if the former principal can not be contacted
My scenario:
I have a SQL database mirrored with witness server and scripted/manual failover works fine. The client app that connects to this database has the SQL Native client installed and the mirror server was specified in the DSN. Durring our DR test the link to where the Principal server is was shutdown, so therefore the Witness server brought the Mirror server instance online in a Principal, Disconnected state which makes sense becuase the mirror had been broken. Well my client app using the Native client started throwing errors and I had to go in and manually change the ODBC entry to the mirror server.
Questions to ask Microsoft:
Why did I have to go in and edit the ODBC entry since I was led to beleive the SQL Native client would do auto failover even if the former principal is offline
Why in Bill's testing does the former principal server have to be on for the autofailover to work on the client app....whats the point of mirroring and having the claim of auto failover if manual intervention is required on the ODBC entry on servers
What consitutes a Witness to initiate a failover...server down...sql service down...what...evidently in Bill's testing the former principal down does not work with auto failover...the db fails over fine, but no the clients
Wrap up:
So I really like the mirroring aspect of SQL 2005...the witness server did exactly what it was supposed to do in our DR testing taking out the link to the data center where the principal server was at. It brought the mirror online proplery. I like the fact that I have an instant up to date database waiting there for applications to use it. No need to wait on other means to bring it online.
What I don't like is the fact the application using the database did not auto failover. My definition of auto failover is no manual intervention. Why does the former principal have to be on for auto failover of the client to work is the main question here i guess
rskorski
Dear all,
Even if Microsoft tells that this issue is still present only with ODBC connection, I've still this problem with my application working with an ADODB Connection
The:
Data Source=Data1;Failover Partner=Data2;Initial Catalog=webPortal;User ID=id;Password=password;Connection Timeout=5;
Data Source=Data2;Initial Catalog=webPortal;User ID=id;Password=password;Connection Timeout=5
seems to be the only solution that works so far.
Speaking with some Microsoft DBA specialist, it looks like the problem is gonna be solved with Service Pack 2 of SQL2005.
Does anyone have a better solution
Regards
Marco
Frederik Vanderhaegen
Hi Steve, thanks for replying.
I'm trying to mirror a non-system database, ie one I've created for testing. I'm struggling to get the client to detach from the failed data source and attach to the newly-promoted principal.
We are on a migration path from Access to SQL 2000/2005 and we have SQL databases running in the background with Access databases locally updating to the SQL databases using linked tables.
Therefore, during testing we open an Access database locally, run an append query to a linked table which correctly updates the SQL database table content, then fail the principal database and let the mirror promote itself to be the new principal and run the append query in Access again - which then fails as described.
The ODBC data source is configured on the client using the SQL native client with the mirror specified and testing the source it can connect to both databases.
Am I missing something or is this not designed to work this way
Thanks