JDBC to SQL Azure Best Practices?

Question

We're having problems using JDBC to access a SQL Azure database - the connections keep timing out.

2010-08-12 09:59:42,113 WARNING [org.hibernate.jdbc.AbstractBatcher] (main) exception clearing maxRows/queryTimeout
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:319)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:956)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMaxRows(SQLServerStatement.java:989)
 at org.jboss.resource.adapter.jdbc.WrappedStatement.getMaxRows(WrappedStatement.java:378)

We're using sqljdbc4.jar from Microsoft SQL Server JDBC Driver 3.0.

The Azure documentation says:

“Maximum allowable durations are subject to change depending on the resource usage. Long running queries or transactions may be closed after five minutes. A logged-in session that has been idle for 30 minutes will be terminated automatically. We strongly recommend that you use the connection pooling and always close the connection when you are finished using it so that the unused connection will be returned to the pool.”

Clearly we've got to think harder about connection pooling and connection management.....

Is anybody using JDBC to SQL Azure in production? If so do you have any best practices or approaches you would be willing to share please?

Answer 1

Hi Rob,

Could you please elaborate a bit? Did you get the time out error with a newly opened connection or an existing connection?

Because the connection to SQL Azure may be closed for certain reasons, we need to implement the retry logic in our applications. We need to capture the connection failure, re-establish the connection and re-execute the queries.

For example, we can wrap the database operation in a loop and a TRY/CATCH block. If there is a connection exception thrown, then re-execute the operation till success.

For more information, please see:

General Guidelines and Limitations (SQL Azure Database)
http://msdn.microsoft.com/en-us/library/ee336245.aspx

SQL Azure Connection Retry
http://blogs.msdn.com/b/bartr/archive/2010/06/18/sql-azure-connection-retry.aspx

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

The following Java code sample isolates and demonstrates the problems we’re having with JDBC connections being closed when running against SQL Azure.

Basically the code performs one query, sleeps for 70 Seconds and then performs another query.

With an on premises SQL Server and other JDBC compliant databases, this works fine.

With SQL Azure, the connection gets closed during the sleep and we get:

com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error

Contrary to the documentation, it seems that connections don't stay open beyond about 1 minute.

Any help or advice gratefully received.

/**
 * JDBC against SQL Azure Problem Repro
 *
 * When run against SQL Azure, the second query fails with
 * com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error
 *
 * It works against on premises SQL Server
 *
 */
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestAzureConnection {
 
  public static void main(String[] args) {

 try {
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   String username = "USER@SERVER";
   String password = "PASSWORD";
   Connection conn = DriverManager.getConnection("jdbc:sqlserver://SERVER.database.windows.net;DatabaseName=master", username, password);
   //String username = "sa";
   //String password = "password";
   //Connection conn = DriverManager.getConnection("jdbc:sqlserver://localhost;DatabaseName=master", username, password);
   System.out.println("First Query");
   PreparedStatement pstmt = conn.prepareStatement("Select count(*) from sys.Tables");
   ResultSet rs = pstmt.executeQuery();
   if (rs.next()) {
 System.out.println(rs.getInt(1)); 
   }
   long millis = 70000;
   System.out.println("Sleeping for " + millis );  
   Thread.sleep(millis);
   System.out.println("Second Query");
   rs = pstmt.executeQuery();
   if (rs.next()) {
 System.out.println(rs.getInt(1)); 
   }
 }
 catch (Exception e) {
   e.printStackTrace();
 }
  }
}

 

Answer 3

 

Rob,

Where is you client code running?  SQL Azure will not terminate a session unless it has been idle for 1800 seconds and your 70-second is well below that.

We have found that many people who are going through proxy servers to get to SQL Azure run into similar problems because their proxy servers are configured to terminiate idle sessions after 60 seconds.

---

Tonyp

Answer 4

Thanks, but I dont think its a proxy server problem. The same behaviour has been observed at three separate locations.

I rewrote the Java program in C Sharp, and predictably that works fine and DOES NOT exhibit the connection drop problems.

I used Wireshark to compare the Java and .NET implementations at the TCP level and the problem becomes clear:

TCP Keep-Alive messages are sent to SQL Azure every 30 seconds from the .NET code, but there are no such messages from JDBC, hence the JDBC connection times out at between about 61 to 63 seconds.

I've been in touch with Microsoft, and I understand that the problem has been forwarded to he JDBC team........

Here is the C Sharp code for info:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading;

namespace SqlAzureTest
{
  class Program
  {
    static void Main(string[] args)
    {
      string conectionString = "Server=tcp:[serverName].database.windows.net;Database=myDataBase;User ID=[LoginForDb]@[serverName];Password=myPassword;Trusted_Connection=False;Encrypt=True;"
      
      SqlConnection sqlConnection = new SqlConnection(conectionString);

      sqlConnection.Open();

      SqlCommand command = new SqlCommand("SELECT count(*) from sys.tables", sqlConnection);

      Console.WriteLine("First Query");
      int i = (int)command.ExecuteScalar();

      Console.WriteLine("Result {0}", i);

      int millis = 35 * 60 * 1000;
      Console.WriteLine("Sleeping for {0}", millis);
      Thread.Sleep(millis);

      Console.WriteLine("Second Query");
      i = (int)command.ExecuteScalar();

      Console.WriteLine("Result {0}", i);

      Console.ReadLine();
    }
  }
}

Answer 5

There is now a workaround for this problem which I've blogged  http://bit.ly/gF7Kzh