Microsoft AlwaysOn Failover and Delphi Solution

Question

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

The application is compiled in Delphi XE3 and uses TADOConnection .

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

If I use Provider=SQLNCLI11 (I found on Google that OLEDB does not support the AlwaysOn solution, and I must use my own SQL client), I get an invalid attribute when I try to open a connection.

Connection string:

 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 need to update a new version on Delphi in order to use this solution for fault tolerance, or something that I am 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 persistence events will cause the database connection to fail, and you need to initiate a new one.

If the SqlClient application is connected to the AlwaysOn database, which is not running, the original connection is broken and the application must open a new connection to continue working after a failure.

I considered this with the simple possibility of overriding the TAdoQuery component with my own version, which retries the connection after receiving a connection failure. This may not be the right way to do this, but it certainly works. What it does is overrides the methods invoked to open (if the query returns a result set) or executes SQL (otherwise), and if it fails due to a connection loss error, try again (but only once). I tested it hard against AlwaysOn switches and it works reliably for our configuration. It will also respond to any other communication loss events and, therefore, deals with some other reasons for request failures. If you are using a component other than TAdoQuery, you will need to create similar overrides for this component.

Maybe this can be considered in other ways, but I stopped looking for alternatives as soon as I found something that worked. You might want to tidy up the usage instruction, as it explicitly contains some things that are not needed. (Just looking at this code, I want to leave and reorganize the code duplication)

 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.