MS SQL Server et les serveurs liés (linked servers)

Introduction

Ce document est une étude sur l’accès aux données distantes Sybase depuis un serveur MS SQL 2000, en particulier d’un point de vue performances.

ODBC vs OLE DB

ODBC

ODBC est l’acronyme de Open DataBase Connectivity. Il s’agit d’une méthode de connexion à des bases de données hétérogènes- développée par Microsoft - permettant l’accès aux informations d’un SGBD distant.

Les éditeurs comme Oracle, Sybase, DB2, Microsoft fournissent tous les pilotes permettant l’accès à leur environnement respectif.

Ainsi, il est possible depuis une application Access d’interroger voire de modifier des données provenant de bases Sybase ou Oracle.

Son usage permet une grande flexibilité de développement et de très bonnes performances.

La limite la plus importante dans l’usage de l’ODBC dans les environnements SQL SERVER concerne les filtres ou jointures : Ils sont réalisés par recopie de toutes les données de la table cible en local, puis application du filtre. Il s’agit d’un problème majeur concernant les performances non seulement sur le client, mais également sur le serveur sollicité et le transport réseau. Il existe heureusement quelques méthodes pour contourner cette caractéristique.

A noter que depuis un frontal Access, les moteurs DAO et Jet ont été optimisés pour intégrer cette limite : une des techniques est le parcours ligne à ligne des objets distants pour profiter de leurs indexes éventuels.

OLEDB

OLE signifie Object Linking and Embedding. Il s’agit d’une version évoluée de l’ODBC, fournissant des méthodes d’accès à des informations en provenance d’applications tiers, en particulier les bases de données. Microsoft tente d’imposer ce nouveau standard d’accès aux données, en remplacement de l’ODBC en particulier.

De même que pour l’ODBC, les éditeurs de base de données fournissent les pilotes nécessaires à l’utilisation de ce connecteur.

Le jeu de données retourné n’est pas un simple tableau résultant d’une requête mais un objet COM manipulable (parcours, filtres, …).

Les limites connues de cette interface aujourd’hui sont liées à sa relative jeunesse. Bien que censément plus performant, il est parfois relevé, selon les pilotes utilisés, quelques soucis de stabilité et de performance.

Définition de l’environnement

ODBC

Créer une entrée DSN

Ouvrir le gestionnaire de sources ODBC

Dans l’onglet « Sources de données système », ajouter une entrée utilisant le pilote Sybase ASE ODBC Driver

Renseigner les 4 informations indispensables dans l’onglet « General »

Le champ « Server Name » doit correspondre à une entrée du fichier sql.ini du client Sybase.

La connexion peut être testée au préalable depuis la commande « Test Connect ».

Ajout du lien ODBC vers un serveur Sybase dans le moteur MS SQL Server

Pour ajouter le serveur distant lié défini par ODBC dans le moteur SQL Server :

exec sp_addlinkedserver   @server         = 'SERVEUR_ODBC',
                            @srvproduct   = 'sybase',
                            @provider     = 'MSDASQL',
                            @datasrc      = 'SERVEUR'

exec sp_addlinkedsrvlogin @rmtsrvname     = 'SERVEUR_ODBC',
                            @locallogin   = 'login',
                            @useself      = false,
                            @rmtuser      = 'login_linked',
                            @rmtpassword  = 'login_linked_motdepasse'

exec sp_serveroption 'SERVEUR_ODBC', 'Data Access', 'true'
exec sp_serveroption 'SERVEUR_ODBC', 'collation compatible', 'true'

sp_addlinkedserver intègre dans l’environnement sql server le serveur distant et ses caractéristiques principales. La paramètre @datasrc doit correspondre à une entrée DSN existante.

sp_addlinkedsrvlogin définit les paramètres de connexion au serveur distant, et ainsi faire correspondre un login local à un login distant. Le paramètre @useself positionné à false indique que les valeurs de @rmtuser et @rmtpassword doivent être utilisées. Si l’option est à true, l’authentification est réalisée à l’aide de la sécurité Windows.

Enfin, sp_serveroption définit quelques options liées au serveur.

'Data Access' est indispensable.

'collation compatible' indique que le jeu de caractères du serveur distant est compatible avec MS SQL, cela réduit les contrôles lors d’échange de données.

2 autres options de timeout peuvent être utilisées pour réduire la portée de requêtes trop longues ou de défauts de connexion.

OLE-DB

Précautions préalables pour OLE-DB et Sybase

Un bug lié à l’installation du composant empêche le bon fonctionnement de l’outil de configuration OLE sybase (Case # 10881882) : trois librairies peuvent ne pas avoir été bien prises en compte. Il faut donc passer les 3 commandes suivantes :

regsvr32 sydaase.dll
regsvr32 sydaases.dll
regsvr32 sydacvt.dll

Ces trois dll sont dans le répertoire %SYBASE%/OLEDB-2_1 où %SYBASE% est le répertoire d’installation du client Sybase sur la machine hébergeant le serveur MS SQL Server.

Créer une entrée OLE-DB Sybase

Exécuter le binaire sydaadm.exe se trouvant dans l’arborescence du client Sybase. Cet exécutable sydaadm est dans le répertoire %SYBASE%/OLEDB-2_1 où %SYBASE% est le répertoire d’installation du client Sybase sur la machine hébergeant le serveur MS SQL Server.

À partir du menu Edit , choisir le menu 'New sybase OLE Datasource' et renseigner le nom de la source

Cliquer sur Set Up Data source et renseigner toutes les valeurs de l’onglet « General » :

Le champ « Server Name » doit correspondre à une entrée du fichier sql.ini du client Sybase.

La connexion peut être testée au préalable depuis la commande « Test Connect ».

Ajout du lien OLE DB vers un serveur Sybase dans le moteur MS SQL Server

Pour ajouter le serveur distant lié défini par OLE DB dans le moteur SQL Server :

exec sp_addlinkedserver           @server       = 'SERVEUR_OLE',
                                  @srvproduct   = 'sybase',
                                  @provider     = 'Sybase.ASEOLEDBProvider',
                                  @datasrc      = 'SERVEUR'

exec sp_addlinkedsrvlogin         @rmtsrvname   = 'SERVEUR_OLE',
                                  @locallogin   = 'login',
                                  @useself      = false,
                                  @rmtuser      = 'login_linked',
                                  @rmtpassword  = 'login_linked_motdepasse'

exec sp_serveroption 'SERVEUR_OLE', 'Data Access', 'true'
exec sp_serveroption 'SERVEUR_OLE', 'Collation Compatible', 'true'

Le contenu des paramètres est identique à ceux vus dans la section ODBC, excepté la variable @provider

En mode graphique, il est possible de définir des options importantes d’une connexion OLE DB. Attention, elles s’appliquent à tous les serveurs utilisant le même pilote.

Il est indispensable de définir l’option 'Dynamic parameters'. Cette option va permettre à MS SQL Server de transmettre au serveur Sybase cible les paramètres et filtres permettant l’usage d’indexes.

Depuis le menu de MS SQL Server Enterprise Manager, Security/Linked servers, ajouter un nouveau serveur, sélectionner le fournisseur voulu (Provider name : Sybase ASE OLE DB Provider )

Sélectionner le bouton 'Provider Options' et valider l’option 'Dynamic parameters'.

Interrogation d’une table distante

4 syntaxes sont alors possibles pour interroger une table distante Sybase grâce à un serveur lié.

select from linkedserver…

select * from LINKED_SERVER.DATABASE.OWNER.TABLE where COL=VALUE

Il s’agit de la manière la plus standard d’adresser une table distante. Elle a pour avantage d’être une manière classique de rédaction de requête sql. L’inconvénient est que le filtre éventuellement fourni ne sera traité qu’en local et pas transmis au moteur distant dans le cas d’un lien ODBC

select from openquery

select * from openquery(LINKED_SERVER, 'select * from TABLE where COL=VALUE')

Syntaxe particulière au moteur MS SQL Server, la requête passée en paramètre est intégralement transmise au serveur distant qui l’exécute dans son propre contexte. Ainsi, l’optimiseur local peut être pleinement utilisé et les indexes utilisés.

Il est possible d’appliquer un filtre au bloc openquery(). Dans ce cas, les limites indiquées au point précédent s’appliquent : les clauses where sont executées une fois le jeu d’enregistrement distant ramené en local.

L’inconvénient majeur de cette méthode est qu’il n’est pas possible de passer autre chose qu’une chaine fixe au 2ème paramètre openquery.

Ainsi, on ne peut pas construire de requêtes dynamiques sous la forme openquery(SERVEUR, 'select … where col=' + @variable)

exec (select from openquery)

exec ('select * from openquery(LINKED_SERVER, ' + '''' + 'select * from TABLE where COL=VALUE' + '''' + ')')

Cette syntaxe 'barbare' permet de contourner la limitation du point précèdent. Puisque openquery ne peut être dynamique, on l’incorpore dans une commande exec qui elle l’est.

La chaîne particulière '''' indique une quote.

exec (select from openquery) par curseur

declare @ID   varchar(30)
declare @ID_LIST     varchar(1000)
declare @SQLTEXT     varchar(2000)

select @CPT=0, @ID_LIST='''' + ''''

DECLARE c1 CURSOR FOR SELECT CUR_COD FROM #tmp_cur

OPEN c1

FETCH NEXT FROM c1 into @ID

WHILE @@FETCH_STATUS = 0
BEGIN
       select @CPT=@CPT+1
       select @ID_LIST = @ID_LIST + ',' + '''' + @ID + ''''
       if @CPT = 10
       begin
              select @SQLTEXT='select * from CURRENCY WHERE CUR_COD in (' + @ID_LIST + ')'
              exec ( 'select * from openquery(DEC_T3_ASE_ODBC,' + ''''+ @SQLTEXT + '''' + ')')
              select @CPT=0, @ID_LIST='''' + ''''
       end
       FETCH NEXT FROM c1 into @ID
END
CLOSE c1
DEALLOCATE c1

select @SQLTEXT='select * from CURRENCY WHERE CUR_COD in (' + @ID_LIST + ')'
exec ( 'select * from openquery(DEC_T3_ASE_ODBC,' + ''''+ @SQLTEXT + '''' + ')')  

Cette syntaxe permet dans le cadre d’une interrogation via un lien ODBC, de parcourir l’objet cible de manière indexée.

La méthode permet de regrouper plusieurs appels openquery paramétrés, afin d’en limiter le nombre, pénalisant en terme de performance.

Le test 'if @CPT = 10' indique que les interrogations seront faites 10 par 10.

Quelques commandes utiles pour trapper les performances vers un serveur lié

Lister tous les 'linked servers' : référencés

exec sp_linkedservers

Consulter les statistiques d’un objet distant :

exec sp_statistics 'DEC_T3_ASE_ODBC.idee.dbo.OMS_COMMENT'

Consulter les indexes d’un objet distant :

exec sp_indexes 'DEC_T3_ASE_ODBC','OMS_COMMENT'

Obtenir des traces plus complètes en cas d’erreur :

DBCC TRACEON(3604, 7300)

Vider le cache de procédure ( supprime en période de test les plans d’exécution éventuellement en mémoire) :

DBCC FREEPROCCACHE

Compter les IO, la durée d’exécution :

set statistics io on
set statistics time on

Mesurer le temps écoulé :

declare @a datetime
select @a=getdate()
select * from DEC_T3_ASE_ODBC.idee.dbo.CURRENCY
select datediff(ms,@a,getdate())

Visualiser le plan d’exécution :

set showplan all on

Mesures de performances

Voici quelques résultats de performances. Dans le tableau qui suit :

• FS = Full Scan sur serveur distant
• IDX = Index utilise sur serveur distant
• ms = milliseconds
• Table CURRENCY : 200 lignes, 32 k
• Table OMS_COMMENT : 40 000 lignes, 3M
• Table ID_COMMENT, 1000000 lignes, 60 M

select * from
  DEC_T3_ASE.idee.dbo.CURRENCY
WHERE CUR_COD='ADP'

select * from
openquery(DEC_T3_ASE,
'select * from CURRENCY
  where CUR_COD="ADP"')

select * from
openquery(DEC_T3_ASE,
'select * from CURRENCY') a
where a.CUR_COD='ADP'

select * from
openquery(DEC_T3_ASE,
'select * from CURRENCY') a,
cur_ref b
where a.CUR_COD=b.CUR_COD

select * from
DEC_T3_ASE.idee.dbo.CURRENCY a,
cur_ref b
where a.CUR_COD=b.CUR_COD 

select count(1)
from DEC_T3_ASE.idee.dbo.OMS_COMMENT 

select * from
DEC_T3_ASE.idee.dbo.CURRENCY 

select * from
DEC_T3_ASE.idee.dbo.OMS_COMMENT

select * from
DEC_T3_ASE.idee.dbo.ID_COMMENT 

select * from
DEC_T3_ASE.idee.dbo.ID_COMMENT

select * from
DEC_T3_ASE.idee.dbo.ID_COMMENT

select * from
DEC_T3_ASE.idee.dbo.ID_COMMENT

select * from
DEC_T3_ASE.idee.dbo.ID_COMMENT

Les tests menés n’ont pas montré de comportements radicalement différents dépendant du paramétrage des pilotes. Les variations des paramètres Fetch Array et Packet Size n’ont pas accru ou dégradé les durées d’exécution de manière significative. On constate toutefois un léger mieux en faisant varier le paramètre Packet size (dépendant du serveur source).

On remarquera cependant deux tendances : pour les interrogations concernant des tables de petites taille, l’appel au pilote ODBC peut s’avérer plus judicieux. Pour les plus gros objets, la différence est systématiquement en faveur du lien ODBC.

Conclusion

La mise à jour d’un pilote OLE DB peut s’avérer intéressant pour des jointures multiserveurs impliquant un petit nombre de lignes.

Le pilote ODBC parait lui plus performant, plus simple à mettre en œuvre, mais nécessite quelques acrobaties de programmation pour réaliser des jointures multiserveurs efficaces.