August 16, 2011 at 7:40 am
Afternoon all,
I have a question regarding the application re-direct functionality behind database mirroring.
We have the high-safety (synchronous) mode of operation for database mirroring currently deployed on our main Production database server and the edition of the SQL Server is Standard SQL Server 2005 - 9.00.3077.00 (X64) running on a SQL 2008 server.
One of the applications that requires data from our production database (that is mirrored) was receiving the following error this weekend AFTER the failover had automatically occured: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
The connection string for the above application has the Failover Partner keyword property in the connection string: connectionString="Server=ServerA;Failover Partner=ServerB;Database=MyDB;User ID=AUser;Password=SomethingStrong#;Trusted_Connection=False"
Should applications automatically re-direct new database connections if the Principal server & Witness server are both online and have network comms but the Mirror server (what was previously the Principal server before automatic failover occured) is actually offline and has no network comms? I was under the impression that new connections should be automatically re-directed to the mirror server if the above keyword property was used? I have checked and both partners have named pipes/TCP/IP & allow remote connections.
Thanks!
August 16, 2011 at 9:45 pm
What I expect to happen is that when servera fails, your application will receive an error and it needs to deal with that error. What your application needs to do is to attempt to open the connection again.
Behind the scenes, the new connection request will attempt to connect to servera. It won't succeed and will automatically attempt to connect to serverb.
August 18, 2011 at 8:05 am
Any new connections should connect to whichever server is currently acting as the principal. As for existing connections, I believe they would need to be dropped and re-established to the principal server.
You are using the SQL Native Client driver, right? Looks like it from your connection string, just checking...
August 18, 2011 at 9:43 am
.NET connection string
"Data Source=ServerA;Failover Partner=ServerB;Initial
Catalog=AdventureWorks;Integrated Security=True;"
August 18, 2011 at 9:57 am
nellisjp (8/18/2011)
Any new connections should connect to whichever server is currently acting as the principal. As for existing connections, I believe they would need to be dropped and re-established to the principal server.You are using the SQL Native Client driver, right? Looks like it from your connection string, just checking...
Nope, ADO .NET (as Subjective has alread said) 🙂
Thanks for the info guys, can feed this back to our developers now!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply