SugarCRM and Microsoft SQL Server / FreeTDS with a Linux Webserver

This is the first in a series of posts were we will discuss how best to configure SugarCRM in order to achieve best performance with Microsoft SQL Server.

Provident CRM are recognised by SugarCRM Inc as the leading partner Globally in support for running SugarCRM on the Microsoft Stack.

 

 

 

 

 

 

SugarCRM can be deployed in any number of combinations which involve various aspects of the Microsoft Stack, some of the most common deployments are:

 

  • Full Microsoft Stack
    • Windows Operating System
    • MSSQL Server
    • IIS Webserver
    • Microsoft MSSQL PHP Driver
  • WAMP Stack
    • Windows Operating System
    • MySQL
    • Apache Webserver
  • Hybrid
    • Apache Webserver (most commonly running on Linux for best performance)
    • MSSQL Server
    • FreeTDS  MSSQL PHP Driver

This Post in concentrating on the Hybrid solution, this is commonly deployed in Enterprise environments were MSSQL is the Database server of choice and enterprise DBA and backup skills/resources are best suited to supporting an MSSQL database.  It is lightly customers will also run Enterprise reporting / BI tools against the MSSQL database as integrations such as SSIS (SQL Server Integration Services).  Provident CRM regularly support customers on these platforms including developing & supporting such integrations.

The FreeTDS Driver supports numbers modes (for connecting to different Database types including MSSQL, Sybase, ODBC etc), below table describes how to configure using the Version number:

TDS Version Supported Products
4.2 Sybase SQL Server < 10 and Microsoft SQL Server 6.5
5.0 Sybase SQL Server >= 10
7.0 Microsoft SQL Server 7.0
7.1 Microsoft SQL Server 2000
7.2 Microsoft SQL Server 2005

Watch out for bogus references to other versions such as 8.0 and 9.0, at first there was One Version of TDS common to both vendors but, in keeping with the broad history of private ventures, they soon diverged. Each vendor has subsequently brought out different versions, and neither supports the other’s flavor. That is to say, each vendor’s client libraries use the latest version of TDS offered by that vendor. You can’t reliably use Microsoft’s libraries to connect to Sybase, or Sybase’s libraries to connect to Microsoft. In some cases you’ll get a connection, but pretty soon you’ll bump into some incompatibility.

 

Versions of the TDS protocol

TDS 4.2 Sybase and Microsoft
The version in use at the time of the Sybase/Microsoft split.
TDS 5.0 Sybase
Introduced for Sybase. Because TDS 5.0 includes negotiated capabilities through which protocol features can be expanded, we are unlikely to see a new TDS version from Sybase.
TDS 7.0 Microsoft
Introduced for SQL Server 7.0. Includes support for the extended datatypes in SQL Server 7.0 (such as char/varchar fields of more than 255 characters). It also includes support for Unicode.
TDS 7.1 Microsoft, was 8.0 [1]
Introduced for SQL Server 2000. Includes support for big integer (64-bit int) and “variant” datatypes.
TDS 7.2 Microsoft, was 9.0
Introduced for SQL Server 2005. Includes support for varchar(max), varbinary(max), xml datatypes and MARS.

Notes

[1] Earlier FreeTDS documentation referred to versions 7, 8 and 9. Microsoft subsequently published a protocol specification document denoting 7.1 and 7.2, and one finds scattered references using that scheme elsewhere, too. For that reason, FreeTDS switched to Microsoft’s nomenclature.

We have posted the full TDS protocol Documentation

Also a note on configuring FreeTDS to connect to MSSQL using SSL 

And finnaly a full FreeTDS Reference Manual

Also, here is an introduction and getting started guide to FreeTDS:

Introduction
————

This document is intended for people who want to help on the FreeTDS project and/or are interested in how the code works.

TDS is a fairly simple request/response protocol. It comes in three major
flavors: 4.2, 5.0 and 7.0. Microsoft SQL-Server versions through 6.5 still use
the 4.2 version as do Sybase servers before 10.0. MS SQL 7.0 introduced TDS 7.0
which so far is not implemented (but can still be accessed with 4.2). No version
of MS SQL uses 5.0 and Sybase does not use 7.0, but both still support 4.2.
TDS allows for negotiation of quite a few parameters at connection time, many of
these are documented in the cap.txt file in this directory.

+—————————————————————+
| | MS SQL | Sybase ASE | Sybase ASA |
+—————————————————————+
|ver |6.5 |7.0 |2000| <10.x | >11.0 |<5.5.03|5.5*|>6.0|
+—–+—-+—-+—-+———–+————+——-+—-+—-+
| 4.2 | X | X | X | X | X | | | |
| 5.0 | | | | | X | | X | X |
| 7.0 | | X | X | | | | | |
| 8.0 | | | X | | | | | |
+—–+—-+—-+—-+———–+————+——-+—-+—-+

* Sybase ASA 5.5 requires 5.5.03 or better with the OpenServer Gateway
(OSG).
Layers
——

The FreeTDS code is broken down into two layers.

The TDS layer handles the bulk of the processing of the protocol and dealing with the network, etc… Any code dealing with protocol issues should reside in this directory (src/tds). A related directory src/server has routines that are used
only for server side processes. These two directories may eventually be merged.

On top of this layer is a thin layer specific to the ‘Call Level Interface’ (CLI). A CLI is an API intended to be used by client programs. The core FreeTDS
has three CLIs: dblib, the original Sybase API; ctlib, the newer Sybase API; and
ODBC, the non-database specific API.

Currently dblib and ODBC are the most robust CLIs we have, followed by ctlib, which is
quite usable none-the-less. The ODBC CLI is built using a driver manager (either iODBC or
unixODBC), so you will need one of those to build it with.

Outside of these there is also a JDBC driver for Java and a DBI driver for
Perl which do not reside atop the TDS layer. (FreeTDS/JDBC is a pure Java
driver). This document does not currently cover these libraries. Those wishing
to use Perl are also encouraged to look at DBD::Sybase which uses ctlib and works well with the FreeTDS libraries.

The Nitty Gritty
—————-

The first thing a CLI does is allocate a login structure for TDS. Such as:

TDS_LOGIN *login = tds_alloc_login();

It can then call any of the tds_set_*() functions to initialize this TDS_LOGIN
structure. Please refer to src/tds/login.c for a complete listing of options.

Once username, password, server, and other options are set a call can be made to
tds_connect(). This will allocate a TDS_SOCKET structure and open a socket
connection to the specified server (or NULL on failure). The TDS_SOCKET is then
passed as an argument to all future TDS functions.

At this point the only really useful thing to do is to call tds_submit_query()
and then process the returning rows using tds_process_results() and company.

This should get us to the routines in src/tds/token.c which are responsible for
reading the results back from the server and doing something with them. The
logic in these routines should not be hard to follow. The return stream is
broken up into a series of ‘messages’ (not to be confused with actual messages
returned from the server). Each one starts with a marker (marker is my word for
it, not Sybase’s or anyone else’s). include/tds.h defines all the markers and in
general there will be one tds_process_*() function for each marker unless it is
not worth looking at, in which case tds_process_default_tokens() will read past
it.

Misc
—-

Please send fixes and updates to Brian Bruns (brian@bruns.org)

 

Capabilities
————

The TDS_CAP_TOKEN token (decimal 226) in TDS is sent to the server immediately
after the login sequence in TDS 5.0. It is sent back as part of the login
acknowledgement stream. The client requests which capabilities it wants and
the server sends back which ones it supports.

I’m not certain but I believe a full 5.0 login sequence could be sent to an
MS-SQL or Sybase 4.x server and if the 226 token is absent from the return
stream then the protocol could default back to 4.2. This would eliminate the
need for compiling two versions of the library, of course setting the proto
version manually would not send the capabilities. I need someone to try this
as I only have access to Sybase 11.x servers.

The layout of the capabilities packet is:
1 byte 2 bytes n bytes
+———+—————+———————+
| 226 | size of mask | capabilities field |
+———+—————+———————+

The capabilities field looks like:
1 byte 1 byte n bytes
+——–+——–+—————+
| id | size | bitmask |
+——–+——–+—————+

1) For id 1 seems to signify request capabilities and 2 is response capabilities.
2) The size represents the size of the bitmask (7 for all I’ve seen).
3) The bitmask is is a bitset mask denoting values set by ct_capability()
or similar call. It is packed from the low order bit to high order bit.
The first bit (low order bit…bit 0 of byte 6) is not used for a capability.
It seems to be used by the protocol but its function is presently unknown.

A listing of all known values for the bitmasks follows with its ctlib
constant. Refer to the ctlib documentation for descriptions of these.

REQUEST CAPABILITIES
——————–
byte 0:
default ctlib value: 3
bit 0 (0x01): CS_PROTO_DYNPROC
bit 1 (0x02): CS_DATA_FLTN
bit 2 (0x04): CS_DATA_BITN
bit 3 (0x08): CS_DATA_INT8
bit 4 (0x10): CS_DATA_VOID
bit 5 (0x20): unknown
bit 6 (0x40): unknown
bit 7 (0x80): unknown

byte 1:
default ctlib value: 109
bit 0 (0x01): CS_CON_INBAND
bit 1 (0x02): CS_CON_LOGICAL
bit 2 (0x04): CS_PROTO_TEXT
bit 3 (0x08): CS_PROTO_BULK
bit 4 (0x10): CS_REQ_URGNOTIF
bit 5 (0x20): CS_DATA_SENSITIVITY
bit 6 (0x40): CS_DATA_BOUNDARY
bit 7 (0x80): CS_PROTO_DYNAMIC

byte 2:
default ctlib value: 127
bit 0 (0x01): CS_DATA_MONEYN
bit 1 (0x02): CS_CSR_PREV
bit 2 (0x04): CS_CSR_FIRST
bit 3 (0x08): CS_CSR_LAST
bit 4 (0x10): CS_CSR_ABS
bit 5 (0x20): CS_CSR_REL
bit 6 (0x40): CS_CSR_MULTI
bit 7 (0x80): CS_CON_OOB

byte 3:
default ctlib value: 255
bit 0 (0x01): CS_DATA_NUM
bit 1 (0x02): CS_DATA_TEXT
bit 2 (0x04): CS_DATA_IMAGE
bit 3 (0x08): CS_DATA_DEC
bit 4 (0x10): CS_DATA_LCHAR
bit 5 (0x20): CS_DATA_LBIN
bit 6 (0x40): CS_DATA_INTN
bit 7 (0x80): CS_DATA_DATETIMEN

byte 4:
default ctlib value: 255
bit 0 (0x01): CS_DATA_BIN
bit 1 (0x02): CS_DATA_VBIN
bit 2 (0x04): CS_DATA_MNY8
bit 3 (0x08): CS_DATA_MNY4
bit 4 (0x10): CS_DATA_DATE8
bit 5 (0x20): CS_DATA_DATE4
bit 6 (0x40): CS_DATA_FLT4
bit 7 (0x80): CS_DATA_FLT8

byte 5:
default ctlib value: 255
bit 0 (0x01): CS_REQ_MSG
bit 1 (0x02): CS_REQ_PARAM
bit 2 (0x04): CS_DATA_INT1
bit 3 (0x08): CS_DATA_INT2
bit 4 (0x10): CS_DATA_INT4
bit 5 (0x20): CS_DATA_BIT
bit 6 (0x40): CS_DATA_CHAR
bit 7 (0x80): CS_DATA_VCHAR

byte 6:
default ctlib value: 254
bit 0 (0x01): unknown
bit 1 (0x02): CS_REQ_LANG
bit 2 (0x04): CS_REQ_RPC
bit 3 (0x08): CS_REQ_NOTIF
bit 4 (0x10): CS_REQ_MSTMT
bit 5 (0x20): CS_REQ_BCP
bit 6 (0x40): CS_REQ_CURSOR
bit 7 (0x80): CS_REQ_DYN

RESPONSE CAPABILITIES
———————
byte 0:
default ctlib value: 0
bit 0 (0x01): unknown
bit 1 (0x02): unknown
bit 2 (0x04): unknown
bit 3 (0x08): unknown
bit 4 (0x10): unknown
bit 5 (0x20): unknown
bit 6 (0x40): unknown
bit 7 (0x80): unknown

byte 1:
default ctlib value: 0
bit 0 (0x01): unknown
bit 1 (0x02): unknown
bit 2 (0x04): unknown
bit 3 (0x08): unknown
bit 4 (0x10): unknown
bit 5 (0x20): unknown
bit 6 (0x40): unknown
bit 7 (0x80): unknown

byte 2: (88)
default ctlib value: 10
bit 0 (0x01): CS_DATA_NOBOUNDARY
bit 1 (0x02): CS_DATA_NOTDSDEBUG
bit 2 (0x04): CS_RES_NOSTRIPBLANKS
bit 3 (0x08): CS_DATA_NOINT8
bit 4 (0x10): unknown
bit 5 (0x20): unknown
bit 6 (0x40): unknown
bit 7 (0x80): unknown

byte 3: (89)
default ctlib value: 120
bit 0 (0x01): CS_DATA_NOINTN
bit 1 (0x02): CS_DATA_NODATETIMEN
bit 2 (0x04): CS_DATA_MONEYN
bit 3 (0x08): CS_CON_NOOOB
bit 4 (0x10): CS_CON_NOINBAND
bit 5 (0x20): CS_PROTO_NOTEXT
bit 6 (0x40): CS_PROTO_NOBULK
bit 7 (0x80): CS_DATA_NOSENSITIVITY

byte 4: (90)
default ctlib value: 0
bit 0 (0x01): CS_DATA_NOFLT4
bit 1 (0x02): CS_DATA_NOFLT8
bit 2 (0x04): CS_DATA_NONUM
bit 3 (0x08): CS_DATA_NOTEXT
bit 4 (0x10): CS_DATA_IMAGE
bit 5 (0x20): CS_DATA_NODEC
bit 6 (0x40): CS_DATA_NOLCHAR
bit 7 (0x80): CS_DATA_NOLBIN

byte 5: (91)
default ctlib value: 0
bit 0 (0x01): CS_DATA_NOCHAR
bit 1 (0x02): CS_DATA_NOVCHAR
bit 2 (0x04): CS_DATA_NOBIN
bit 3 (0x08): CS_DATA_NOVBIN
bit 4 (0x10): CS_DATA_NOMNY8
bit 5 (0x20): CS_DATA_NOMNY4
bit 6 (0x40): CS_DATA_NODATE8
bit 7 (0x80): CS_DATA_NODATE4

byte 6: (92)
default ctlib value: 0
bit 0 (0x01): unknown
bit 1 (0x02): CS_RES_NOMSG
bit 2 (0x04): CS_RES_NOEED
bit 3 (0x08): CS_RES_NOPARAM
bit 4 (0x10): CS_DATA_NOINT1
bit 5 (0x20): CS_DATA_NOINT2
bit 6 (0x40): CS_DATA_NOINT4
bit 7 (0x80): CS_DATA_NOBIT

Bulk Copy
———

 

BCP Data Format

+———+—–+—–+—————+———-+————-+————+
| | non | row | | | | size of |
| size | var | num | fixed columns | row size | var columns | adjustment |
| | cols| | | | | table |
+———+—–+—–+—————+———-+————-+————+
+———+————+
| end of | offsets |
| data | to varlen |
| pointer | data |
+———+————+