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

[A.M. Hoornweg 144]

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

 

 

[Lars Fosdal 1792]

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

[Lars Fosdal 1792]

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;

 

[Lars Fosdal 1792]

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;

 

[Lars Fosdal 1792]

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

[BruceTTTT 7]

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.

[Lars Fosdal 1792]

@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?

[A.M. Hoornweg 144]

17 hours ago, BruceTTTT said:

native client has issues with SQL 2019

Could you please elaborate on that? Anything serious happening?

[Dmitry Arefiev 101]

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 ...

[BruceTTTT 7]

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.

[EgonHugeist 3]

@BruceTTTT

ZeosLib has an direct OleDB driver for SQL Server. See: OleDB-Forum. I added the driver 5years ago IIRC. I can confirm the direct Ole-Drivers are somewhat faster than SQLNCLI. Feel free to test it: Click download Snapshot. and report your findings. Have fun, Michael

[BruceTTTT 7]

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

[EgonHugeist 3]

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