Microsoft AlwaysOn failover solution and Delphi

Question

I'm trying to make a Delphi application to work with AlwaysOn solution. I found on Google that I have to use MultiSubnetFailover=True in the connection string.

Application is compiled in Delphi XE3 and uses TADOConnection.

If I use Provider=SQLOLEDB in the connection string, application starts but it looks like MultiSubnetFailover=True has no effect.

If I use Provider=SQLNCLI11 (I found on Google that OLEDB doesn't support AlwaysOn solution and I have to use SQL Native client) I get invalid attribute when trying to open the connection.

The connection string is:

Provider=SQLOLEDB.1;Password="password here";Persist Security Info=True;User ID=sa;Initial Catalog="DB here";Data Source="SQL Instance here";MultiSubnetFailover=True

Do I have to upgrade to a newer version on Delphi to use this failover solution or is something that I'm missing in the connection string?

Answer 1

I am currently using XE2 with SQL Server AlwaysOn. If you read the documentation you will see that AlwaysOn resilience events will cause your database connection to fail and you need to initiate a new one.

If a SqlClient application is connected to an AlwaysOn database that fails over, the original connection is broken and the application must open a new connection to continue work after the failover.

I've dealt with this via the simple expedient of overriding the TAdoQuery component with my own version which retries the connection after getting a connection failure. This may not be the proper way to do this but it certainly works. What it does is override the methods invoked for opening (if the query returns a result set) or executes the SQL (otherwise) and if there is a failure due to connection loss error tries again (but only once). I have heavily tested this against AlwaysOn switch overs and it works reliably for our configuration. It will also react to any other connection loss events and hence deals with some other causes of queries failing. If you are using a component other than TAdoQuery you would need to create similar overrides for that component.

It is possible this can be dealt with in other ways but I stopped looking for alternatives once I found something that worked. You may want to tidy up the uses statement as it clearly includes some stuff that isn't needed. (Just looking at this code makes me want to go away and refactor the code duplication as well)

unit sptADOQuery;

interface

uses
  Windows, Messages, SysUtils, Classes, Db, ADODB;

type
  TsptADOQuery = class(TADOQuery)
  protected
    procedure SetActive(Value: Boolean); override;
  public
    function ExecSQL: Integer;   // static override
  published
  end;

procedure Register;

implementation

uses ComObj;

procedure Register;
begin
  RegisterComponents('dbGo', [TsptADOQuery]);
end;

procedure TsptADOQuery.SetActive(Value: Boolean);
begin
  try
    inherited SetActive(Value);
  except
    on e: EOleException do
    begin
      if (EOleException(e).ErrorCode = HRESULT($80004005)) then
      begin
        if Assigned(Connection) then
        begin
          Connection.Close;
          Connection.Open;
        end;
        inherited SetActive(Value);   // try again
      end
      else raise;
    end
    else raise;
  end;
end;

function TsptADOQuery.ExecSQL: Integer;
begin
  try
    Result := inherited ExecSQL;
  except
    on e: EOleException do
    begin
      if (EOleException(e).ErrorCode = HRESULT($80004005)) then
      begin
        if Assigned(Connection) then
        begin
          Connection.Close;
          Connection.Open;
        end;
        Result := inherited ExecSQL;   // try again
      end
      else raise;
    end
    else raise;
  end;
end;

end.