Linked Server: ASE DBA Survival Guide for the Dead Protocol

[Former Member]

Linked server is a well know protocol available in MSSQL server that allows, inter alia, making remote servers (SAP ASE among them) visible within MSSQL so that the remote objects may be accessed locally from withing MSSQL.  It achieves this visibility by bridging MSSQL and ASE over either ODBC or OLEDB connectivity drivers.

Unfortunately, it is a dead protocol.   It has a lot of disadvantages.  It has a lot of connectivity and performance issues.  Even more sadly, there is no straightforward alternative to the ease of operation it once allowed (other than shift to SSIS).

I have been struggling with this defunct protocol for quite some time for a recent project.  Below is a brief mapping of issues related to Linked Server connectivity between MSSQL and SYBASE ASE – over either OLEDB or ODBC connection libraries - I have faced throughout this project and some alternatives I have had to implement to overcome these.

Since Linked Server allows creating a bridge between distinct servers, one of the use-cases for Linked Server is aimed at joining together local and remote tables.  Under this use case, there are several alternatives available.  The most straightforward and convenient is defining a Linked Server and than simply addressing the remote tables with a syntax of REMOTE_SERVER. REMOTE_DB. remote_schema.REMOTE_TABLE.  On the face of it, this allows joining together remote and local objects as if they were both available locally.  On the face of it.

However, joining distant objects with the LOCAL_DB.local_schema.LOCAL_TABLE = LINKOLE.REMOTE_DB. remote_schema. REMOTE_TABLE syntax has serious performance issues, which cannot and will not be solved by either provider.  As it turns out, Microsoft have stopped looking into the Linked Server connectivity issues long ago and SAP/Sybase stopped waiting for the issues to be resolved on Microsoft side.  So most of the OLEDB/ODBC protocol connectivity CRs at SAP side simply age out without any work being done on them.  As it looks, there is no foreseeable change to be expected here.

One of the reasons why this connectivity is badly misfunctional is that MSSQL fails to make intelligent choices on the basis of the data available to is about remote table indices.  Although MSSQL is aware of the remote table DDL details and in theory MSSQL must support filtering remote data by sending the SARGs to the remote server to process them there based on the available remote indices, it fails to do so in 99% of the cases.

The somewhat surprising option "Index as access path" (why should it be optional at all?) is not implemented properly.  MSSQL generates an error if the option is turned on in an attempt to access remote ASE tables.  On another hand, another seemingly unrelated option "Allow inprocess" must be turned on for the connectivity to work at all.  In theory (http://technet.microsoft.com/en-us/library/ms188095(v=sql.105).aspx), with or without this option the connectivity must work.  In practice – it works consistently only when the option is turned on.

When MSSQL starts to process a query, it begins by "handshaking" with ASE and pulling in a lot of metadata information about a table:  all available columns and their types (will be relevant later), all available indices, & a bunch of other metadata on the ASE server level.  Incidentally, what MSSQL never questions is the size of a table on the remote side or its column level statistics available through ASE metadata as well (not implemented in either protocol – OLEDB or ODBC).  As a result, for MSSQL executing a query over the Linked Server the ultimate alternative for resolving a query is to pull the remote table locally and only then to apply the where clause search arguments on the stream of data it receives.  I.e., instead of taking a risk and sending SARGs to the remote server for execution, MSSQL sends ASE a request to get full table data and then filters it in memory.

Needless to say, when you want to query a remote table holding over a million rows with an equijoin like REMOTE_COLUMN = @VALUE there is a huge performance difference between whether you execute equijoin on the remote side and retrieving back 1<=N<=1000000 rows (potentially only a single row) or pull the whole table locally (always N=1000000 rows) and then filter the rows "on the fly."  Strange but true - it seems that no one really thought over this while writing the protocol.

The only two datatypes that more or less consistently result in SARGS being propagated to the remote side to be executed there are integer and datetime.  Most of the others (e.g., all the character types and smalldatetime) are never passed to the remote ASE side at all.  ASE instead receives a query of the type "select Tbl1002.COL1,… Tbl1002.COLN from TABLE Tbl1002" with the SARGs completely stripped off.

You have to really think twice if you want to execute joins (or even single queries) against a remote table available through the Linked Server using the convenient LOCAL_DB. local_schema. LOCAL_TABLE = LINKED_SERVER. REMOTE_DB. remote_schema. REMOTE_TABLE syntax.  Unless you use very specific data types and unless you work with really small tables so that pulling them locally in the worst case is not an issue, the protocol will be very inconvenient to work with.  It will work, but potentially have a huge performance impact (not only for the query you execute but also for the server as a whole and other queries running on it concurrently.  Think on the impact on the MSSQL cache or the OS IO subsystem when instead of getting a single row, each query running against large remote table pulls gigabytes of data locally over the network to filter them in MSSQL memory space in order to produce a single row result set.  Likewise, there is no way MSSQL may interact with the remote server optimizer to make intelligent choices for complex queries executed against remote tables.  The query will be either "passed-through" to the remote server and will be evaluated by the optimizer there or MSSQL will simply pull all the data from all columns for all the tables involved in the query locally and filter this huge data stream.

A bit more manageable alternative to query remote table through the Linked Server protocol is to use the "pass-through" capabilities of MSSQL.  MSSQL allows to use the "execute ('QUERY') at LINKED_SERVER" or select * from (LINKED_SERVER,'query') syntax to pass the query as it is to the remote server instead of executing it locally using LINKED_SERVER. REMOTE_DB. remote_schema. REMOTE_TABLE syntax.  On the face of it, it looks like a way out from this mess.  However, there are hidden issues with this alternative as well.

The most obvious aspect of switching from LINKED_SERVER... syntax to execute/openquery syntax is that the query in the latter case is passed as is to the remote server side and the joins within the query are executed on the remote server side alone.   For "execute … at" additional limitation is that all of the query will run on the remote side and the result set cannot be used locally in joins.  With openquery there is a possibility for a narrow maneuver, but it is also pretty limited.

In order to join remote table to local table with openquery syntax, one may do the following:

select b.REMOTE_COLUMN, a.LOCAL_COLUMN

from LOCAL_TABLE a,

(

select * from openquery

(SYBASE_DB,'select REMOTE_COLUMN,… from REMOTE_TABLE where  …')

) as b

where a.LOCAL_COLUMN = b.REMOTE_COLUMN

This type of syntax will allow joining locally result sets from local and remote tables.  However, unlike regular join, the query submitted to openquery "window" must be a standalone query.  I.e., there is obviously no possibility to pass a local column as a participant in a join with a remote column.  One must rather identify how to narrow in the best possible way the remote result set and write the query using the openquery syntax with as many SARGs as possible (using the syntax of the remote – ASE – server, not local – MSSQL – server).  This is the only possible way I found to overcome the protocol behavior of pulling remote tables entirely and filtering them in the MSSQL memory.  Even this narrow workaround has additional pitfalls – with remote datatype conversion in MSSQL.

Besides the problem the Linked Server protocol has working with certain datatypes for executing joins (or processing SARGS remotely), there are also issues retrieving remote datatype locally.  To recall, in an initial handshake between MSSQL and remote ASE server, MSSQL queries remote datatypes for all the columns in a table it has to query.  Besides datatypes, MSSQL also receives information about column nullability.  This seems to be innocent – but it is not.  If the remote column is defined as NOT NULL and the local join uses the OUTER join semantics (i.e. allows possibility that the column defined as not null on the remote server will return NULL values generated by the outer join), Linked Server may abort the query with error:

{COLUMN} was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.

Actually, there is no way to bypass this error other than changing the source table definition for the column to allow null values.  This is pretty grave – but the only alternative is to retrieve the full remote table values locally and to make the outer join locally.

Another issue that may arise is retrieving smalldatetime values.  Each time the remote column is defined as smalldatetime, an attempt to retrieve it either from openquery or any other method will result in:

Error converting data type DBTYPE_DBTIMESTAMP to datetime2

For this error, there are two workarounds:  either to pull the values through a view defined on the remote server which will translate smalldatetime to datetime on the remote view level (when MSSQL will query column datatypes in the initial handshake it will get the datatype defined on the view level not on the table level).  The other simpler alternative is to use the openquery syntax only and to use cast (not convert) to translate the smalldatetime value to datetime value.  I.e. select * from openquery(LINKOLE, 'select top 1 smalldatetime_column from REMOTE_TABLE') or  select top 1 smalldatetime_column from LINKOLE.REMOTE_DB.remote_schema.REMOTE_TABLE or  select top 1 cast (smalldatetime_column as datetime ) from LINKOLE.REMOTE_DB.remote_schema.REMOTE_TABLE will fail with error, while  select * from openquery(LINKOLE, 'select top 1 cast (smalldatetime_column as datetime) from REMOTE_TABLE ') will execute correctly.

Lastly, MSSQL using the Linked Server protocol has over years become incapable of making RPC calls to the remote procedures using the exec LINKED_SERVER. REMOTE_DB. remote_schema. REMOTE_PROCEDURE.  It either fails with error or even generate stack trace and crashes MSSQL Server:

SqlDumpExceptionHandler:

Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.

SQL Server is terminating this process.

Message

*   LINKODBC...sp_who / LINKOLE...sp_who

*   Access Violation occurred writing address 0000000000000011

*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

*   Exception Address = 00000000104E72C3 Module(sybdrvodb64+00000000001172C3)

Here too, the alternative is to use either openquery (LNK,'sp_'…) or execute ('sp_...') at LNK syntax.

To sum things up, it is quite a pity but it seems to be true that Linked Server protocol seems to be on the bridge of death.  Although even the proxy access across distinct ASE servers too have its pitfalls and performance issues (e.g., proxy table definitions were misbehaving when tables / indices for the remote objects crossed the 30 character limit).  For those working within ASE milieu there is always a hope that the CRs raised by the customers will be eventually resolved (they in fact are).  For those working on the edge of ASE/MSSQL there seems to be no hope.  No one seems to care, and in this case Microsoft is the major culprit.

If anyone has an input on this matter or knows it to be otherwise, I would be glad to learn.

ATM.