Jump to content
A.M. Hoornweg

Ole DB (Ado) for MSSQL un-deprecated by Microsoft

Recommended Posts

Somehow I totally missed this, but in october 2017 Microsoft did a complete U-turn on deprecating OleDB database connectivity.  They  have even released new OleDB providers for MS Sql Server which will be maintained and updated with the most recent server features.

 

https://blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/announcing-the-new-release-of-ole-db-driver-for-sql-server/

https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-2017

 

 

  • Like 2
  • Thanks 2

Share this post


Link to post

Interesting. Has anyone done any benchmarking of the MSOLEDBSQL driver vs the SQLNCLI driver?

Share this post


Link to post

Oddly enough, FireDAC seems to prefer SQLNCLI over everything else, and I can't find a way to override that priority, apart from explicitly setting the ODBCDriver on Create, which makes it harder to handle it not being installed
 

procedure TFDPhysMSSQLDriver.InternalLoad;
begin
  inherited InternalLoad;
  if ODBCDriver = '' then
    ODBCDriver := FindBestDriver(
      {$IFDEF MSWINDOWS} [C_2012_NC, C_2016_ODBC, C_2012_ODBC, C_2017_ODBC, C_2008, C_2005, C_2000] {$ENDIF}
      {$IFDEF POSIX} [C_2016_ODBC, C_2012_ODBC, C_2017_ODBC, C_FreeTDS], C_FreeTDSLib {$ENDIF}
    );
end;

 

Share this post


Link to post

EMBT placed the above constants in the implementation section of the FireDAC.Phys.MSSQL, which I guess is good for hiding implementation details, but not so good for doing overrides... hello, new literal.

 

Initial benchmarks indicate that C_2016_ODBC = 'ODBC DRIVER 13 FOR SQL SERVER' is about the same speed as SQLNCLI 11, which is good news.

 

class function TPSDFireDatabasePoolMSSQL.FindBestDriver(const Link: TFDPhysMSSQLDriverLink): String;
const // Constants copied from implementation section of FireDAC.Phys.MSSQL
  C_2017_ODBC = 'ODBC DRIVER 17 FOR SQL SERVER';
  C_2016_ODBC = 'ODBC DRIVER 13 FOR SQL SERVER';
  C_2012_ODBC = 'ODBC DRIVER 11 FOR SQL SERVER';
{$IFDEF POSIX}
  C_FreeTDS = 'FreeTDS';
{$ENDIF}
{$IFDEF MSWINDOWS}
  C_2012_NC = 'SQL SERVER NATIVE CLIENT 11.0';
{$ENDIF}
var
  DriverList TStringList;
  WantedList : TArray<String>;
begin
  Result := '';
  WantedList := {$IFDEF MSWINDOWS} [C_2017_ODBC, C_2016_ODBC, C_2012_NC, C_2012_ODBC] {$ENDIF}
               {$IFDEF POSIX} [C_2017_ODBC, C_2016_ODBC, C_2012_ODBC, C_FreeTDS] {$ENDIF};

  DriverList := TStringList.Create;
  try
    Link.GetDrivers(DriverList);
    for var Wanted in WantedList
     do for var Driver in DriverList
      do if CompareText(Wanted , Driver) = 0
       then Exit(Wanted);
  finally
    DriverList.Free;
  end;
end;

class function TPSDFireDatabasePoolMSSQL.CreateDriverLink(const aOwner: TComponent): TFDPhysDriverLink;
var
  Res: TFDPhysMSSQLDriverLink;
begin
  Res := TFDPhysMSSQLDriverLink.Create(aOwner);
  Res.ODBCDriver := FindBestDriver(Res);
  Result := Res;
end;

 

Share this post


Link to post

Added updated code example for how to prioritize specific MSSQL connection types on a machine that may have multiple installed.

Share this post


Link to post

I've done some basic testing and for the newest drivers (e.g. ODBC 17 and the OLE DB (msoledbsql) "Driver" (not "Provider")), the speeds in decreasing order (fastest at the top):

 

OLE DB (msoledbsql)

SQL Server Native Client

ODBC 17

 

The native client drivers *are* deprecated by Microsoft, replaced by the OLE DB driver and ODBC 17 ones listed above. Since FireDAC cannot use the OLE DB driver, I'm forced to pick either the ODBC 17 or use the native client. This is frustrating because the native client has issues with SQL 2019, and the ODBC driver (while it can connect to any version of database) is slower.

Share this post


Link to post

@Dmitry Arefiev - is there something planned for mitigating this?

@BruceTTTT - Check the quality portal and add an issue if it is not already there.

Edit: Wait a sec - we are using the MSOLEDBSQL driver - why doesn't it work for you?

Share this post


Link to post

In the plan is to check latest MS SQL ODBC driver, and update FireDAC MSSQL driver if needed.

No plans for OLEDB support. Just no sense ...

Share this post


Link to post

There are no "problems" with the native client except for compatibility with versions of SQL Server. Depending on which version of the server you are running (including with Express), you have to pick the correct native client. There are times my software has had to support 5 different versions of the server (2008 all the way through 2019), and supporting a different driver each time gets annoying fast.

 

So far with my testing, the msoledbsql ("Driver", not "Provider") and the related ODBC 17 driver just released by Microsoft are compatible with all versions of SQL Server. This is really helpful when the app or client needs to access different versions. And as mentioned above, ODBC 17 is slower. Bottom line for me right now, dbGo using this new driver is still the fastest and most agile solution. Supporting ODBC 17 as a choice is great for options, but in all my testing, there is still overhead vs. using the OLE DB "Driver" directly.

 

https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-2017

 

In their words:

... SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use either for new development work.

  • Thanks 1

Share this post


Link to post

Is the ZeosLib for FireDAC? I.e. is it installed as ODBC so FireDAC can see it? Otherwise the the msoledbsql OLE DB driver works fine with dbGo...

 

Bruce

Share this post


Link to post

No Bruce,

ZeosLib as an OpenSource DAC(Database access component) and it's definitelly not for FireDac. We may call it "a source of inspiration for Dimitry's FireDac/AnyDac".

ZeosLib is much better maintained than dbGo. So don't hassitate to use OpenSource!

Regards, Michael

  • Like 2

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×