Use SQL Server 2008 R2 client with Enterprise Library 4.1

Topics: Data Access Application Block
Aug 24, 2010 at 5:04 PM

All,

I am modifying an existing application that uses SQL Server 2008 as the database and Enterprise Library 4.1 for Data Access.

I can upgrade this to Ent Lib 5.0 if need be, but would rather not.

What I need to do is to make it use the SQL Server 2008 R2 client to take advantage of out of the box failover capabilities.

Any idea how I can accomplish this?

Thanks

 

Aug 25, 2010 at 2:14 AM
Edited Aug 25, 2010 at 2:15 AM

I haven't really tried it but based on the reading I've done, you can implement database mirroring with failover clustering.  And then specify the FailOver Partner in your connection string:

";Failover Partner=PartnerServerName"

For more info, please refer to these links:

http://technet.microsoft.com/en-us/library/ms191309.aspx

http://msdn.microsoft.com/en-us/library/5h52hef8.aspx

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com

Aug 25, 2010 at 5:30 AM
Edited Aug 25, 2010 at 5:31 AM

Sarah,

I have done that. So, here is what prompted the question:



I am using Enterprise Library 4.1 to connect to a SQL Server 2008 R2 database with a mirror.
My connection string itself is as below (with dummy values for all fields):


"Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=DBName;User ID=uid;Password=pwd;Connect Timeout=5"

 

Sample .NET code is as below (with modifications to instantiate all variables locally to provide context):

 

SqlDatabase db = new SqlDatabase(ConnectionString);
SqlConnection cn = (SqlConnection)db.CreateConnection();
cnInitTime = String.Format("{0:MMM dd, yyyy, HH:mm:ss.fff}", DateTime.Now); //To store the time when the connection was attempted
cn.Open();

//A dummy method to test whether connection has indeed been established. Added out of desperation :-)
if (cn != null && cn.State == ConnectionState.Open)
{ 
cmd = new SqlCommand("PRINT 'ASDF'", cn);      
cmd.ExecuteNonQuery();                    

}

 

And here is what I observed happening:
Initially when ServerA is active, everything works well.
When Server A fails and the requests get routed to ServerB, I get a network error. Which I can understand happening on the first request. The problem is this persists for approx two minutes or so before I can get access to the ServerB from within my code. While this is happening, I can fetch data from ServerB using the query analyzer.
After two minutes with ServerB acting as primary and Server A as mirror, life is good. And when ServerB goes down, the connection seamlessly switches to ServerA without any issues.
Digging into this further I noticed that after this line below:

 

SqlConnection cn = (SqlConnection)db.CreateConnection();

 

the datasource of the connection is always ServerA regardless of which server is active. However, when the database (on ServerA or ServerB) is serving requests normally, the datasource switchs on cn.Open(); But immediately after failing over to ServerB and for the next couple of minutes, this switch does not happen which means that the code is still attempting to connect to ServerA despite that failing.


Prior to posting this, I tried handling the exception raised and clearing the connection pool and reinitiating the connection hoping that this is something transient that will go away.
 catch (SqlException se)
            {
                try
                {
                    
                    if (cn != null)
                    {
                        SqlConnection.ClearPool(cn);
                        cn = new SqlConnection(connectionstring);
                        cn.Open();
                        cmd = new SqlCommand("PRINT 'ASDF'", cn);
                        cmd.ExecuteNonQuery();
                    }
                }

}
This still gives me a network error.
This is driving me nuts and I would really appreciate it if you could shed some light on what I am doing wrong here.


Thanks

Aug 25, 2010 at 5:50 AM

The behavior doesn't seem to be caused by DAAB since 90% of your code still looks like the regular ADO.NET code.  Try using pure ADO.NET code and see if you will still encounter the error.  If the problem still persists, I would suggest posting this question on other .NET/ADO.NET forums to get a better explanation.   If you don't encounter the error, kindly post the full error message of the network exception.

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com

Aug 25, 2010 at 7:43 PM

Sarah,

I agree that this does not look like something DAAB is messing up. I would put my money on the failover SQL Server not serving DB requests as soon as it comes online or the underlying SQLClient library not doing automatic failover for some reason.

FWIW, the error message that I am getting is:

"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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

 

I did read http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but since my problem is transient, troubleshooting steps 1-3 do nmot really apply and I could not get PortQry to resolve the IP even when the server was active and serving db requests without any issues.

I posted this issue here: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8057b7d8-802e-4ec2-b6d8-0448ee07d86e

Any other good forums that you know of?

Thanks

Aug 27, 2010 at 3:26 AM

Haven't really posted to any sql forums yet, but try here.

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com