Problématique rencontrée chez un client
1. Expression du besoin
Un serveur SQL (Microsoft) « Serv.A » dispose d’une base de données dont certaines tables sont identiques à celles d’un serveur Informix (IBM) « Serv.B ».
Une mise à jour entre les deux bases (de Serv.B vers Serv.A) est nécessaire quotidiennement.
Les performances de l’application ne doivent pas être dégradées par la mise en place du serveur lié.
1.1 – Volumétrie des données
Les tables concernées par cette synchronisation sont au nombre de 15 et regroupent au total moins de 170 000 lignes de données.
1.2 – Matériel
Le serveur SQL est installé sur une machine virtuelle de type ESX 4 (VM Ware)
Le serveur Informix est installé sur un AIX.
Ces deux serveurs sont sur le même réseau (aucun pare feu, notamment).
1.3 – Versions des bases de données
Base Microsoft : Microsoft SQL Server 2008 R2 Standard Edition (64-bit)
Base Informix : IBM Informix Dynamic Server Version 11.50.FC6W1
2. Mise en place de la solution
2.1 – IBM Informix OLE DB Provider
Le fournisseur d’accès OLE DB Informix est un composant d’accès universel, il permet l’accès à un serveur de base de données IBM Informix.
Voici le nom et l’identifiant du fournisseur d’accès IBM Informix OLE DB :
Voici une liste de serveurs de bases de données Informix qui soutiennent le fournisseur d’accès (Version 3.50) :
2.2 – Installation de IBM Informix Client SDK
Le Client SDK Informix installe le fournisseur d’accès. Il est disponible à cette adresse: http://www14.software.ibm.com/webapp/download/search.jsp?rs=ifxdl
Assurez-vous de sélectionner la version appropriée à votre plateforme.
L’installation s’effectue dans le répertoire suivant :
« C:\Program Files\IBM\Informix\Client-SDK »
Assurez-vous que la variable d’environnement PATH contienne bien le répertoire « INFORMIXDIR $ / bin » en paramètre.
Le fournisseur « ifxoledbc.dll » se trouve dans le dossier %INFORMIXDIR%\bin.
Par défaut, il est enregistré au cours de l’installation du Client SDK. Vous pouvez aussi l’inscrire manuellement à l’aide de l’outil Regsvr32.exe en utilisant la commande suivante: regsvr32.exe% INFORMIXDIR% \ bin
Remarque: Sur un Windows x64 (64 bits) du système, il y a deux versions de l’outil Regsvr32.exe:
• La version 32-bit se trouve dans C:\WINDOWS\SysWOW64
• La version 64-bit se trouve dans C:\WINDOWS\System
Utilisez la version correcte lors de l’inscription du provider OLEDB Informix sinon, l’application ne pourra pas charger la librairie.
Configurer le composant Setnet32 :
Dans l’onglet « Serveur Information », renseignez les informations sur le serveur de base données IDS.
• IBM Informix Server: le nom du serveur IDS.
• HostName: l’hôte ou l’adresse de la machine où le serveur IDS est en cours d’exécution. Si vous utilisez un nom d’hôte, vous devez vous assurer que le nom est connu sur votre machine afin que l’adresse soit résolue.
• ProtocolName: le protocole Internet utilisé. Utilisez « onsoctcp » par défaut.
• Nom du service: il s’agit soit d’un nom de service ou de son numéro. Si vous utilisez un nom de service, vous devez avoir ce nom défini sur votre machine dans le fichier C: \ WINDOWS \ system32 \ etc \ services.
Nous allons utiliser le programme « ilogin » afin de vérifier la connectivité avec le serveur IDS :
Une fois les paramètres remplis, appuyez sur OK.
Une fenêtre affichant le contenu d’une table de test doit s’ouvrir.
2.3 – Validation de la connexion OLE DB avec Rowset Viewer
Vous pouvez également tester Informix OLE DB Provider en utilisant Rower Viewer.
Rower Viewer est inclus avec les composants Microsoft Data Access Components (MDAC)
2.8 (SDK), que vous pouvez télécharger ici : http://www.microsoft.com/download/en/details.aspx?id=21995
Accès au serveur IDS avec Rowset Viewer :
1. Démarrez Rowset Viewer
2. Cliquez sur File puis sur Full Connect
3. Dans la boîte de dialogue Connexion, choisissez « Ifxoledbc » dans le domaine de fournisseur.
4. Dans DataSource, entrez le nom du serveur de bases de données à laquelle vous voulez vous connecter : MaBaseDeDonnées@ServerInformix
5. Cliquez sur « ok »
Remarque : Vous pouvez également utiliser Rowset Viewer pour exécuter des instructions SQL sur la base de données du serveur Informix.
2.4 – Configuration de la base de données IBM Informix
Il est nécessaire d’exécuter le script « coledbp.sql » sur le serveur de base de données Informix afin d’ajouter les tables et les fonctions requises par le fournisseur d’accès.
Pour ce faire, exécuter la commande suivante à partir d’une fenêtre de commande DOS :
dbaccess sysmaster %INFORMIXDIR%\etc\coledbp.sql
Ce script doit être exécuté sur le serveur à partir du compte Informix.
2.5 – Configuration du fournisseur OLE DB « Ifxoledbc »
Les options du fournisseur d’accès OLE DB permettent de gérer les requêtes SQL distribuées. Dans l’Explorateur d’objets de SQL Server Management Studio, cliquez avec le bouton droit sur le nom du fournisseur, puis sélectionnez Propriétés.
Lorsque les options sont définies pour un fournisseur, les paramètres s’appliquent à toutes les définitions de serveur lié qui utilisent le même fournisseur OLE DB.
Voici le script SQL de configuration du fournisseur :
USE [master]
EXEC master.dbo.sp_MSset_oledb_prop N’Ifxoledbc’, N’AllowInProcess’, 1
EXEC master.dbo.sp_MSset_oledb_prop N’Ifxoledbc’, N’DynamicParameters’, 1
EXEC master.dbo.sp_MSset_oledb_prop N’Ifxoledbc’, N’IndexAsAccessPath’, 1
EXEC master.dbo.sp_MSset_oledb_prop N’Ifxoledbc’, N’NestedQueries’, 1
EXEC master.dbo.sp_MSset_oledb_prop N’Ifxoledbc’, N’SqlServerLIKE’, 1
2.6 – Configuration du serveur lié
Créez un nouveau serveur lié :
Définissez les champs suivants:
• Serveur lié: Indiquez le nom du serveur SQL pour lier.
• Prestataire: Choisissez IBM Informix OLE DB Provider de la liste déroulante.
• Nom du produit: Indiquez le nom du fournisseur de données : « ifxoledbc ».
• Source des données: Indiquez le nom de la source de données comme base de données @ serveur.
• Chaîne du fournisseur: Spécifiez les paramètres supplémentaires de la chaîne de connexion qui fournisseur utilise.
Remarque : Si l’option collation compatible prend la valeur TRUE, collation name prend automatiquement la valeur NULL. Si collation name a une valeur non NULL, collation compatible prend automatiquement la valeur FALSE.
Voici le script SQL pour la création du serveur lié:
USE [master]
EXEC master.dbo.sp_addlinkedserver @server = N’MY_SERVER_INFORMIX’, @srvproduct=N’Ifxoledbc’, @provider=N’Ifxoledbc’, @datasrc=N’test@ol_ids_1150_1′
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’collation compatible’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’data access’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’dist’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’pub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’rpc’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’rpc out’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’sub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’connect timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’collation name’, @optvalue=null
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’lazy schema validation’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’query timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’use remote collation’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server= N’MY_SERVER_INFORMIX’, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’MY_SERVER_INFORMIX’, @locallogin = NULL , @useself = N’False’
2.7 – Définitions de la sécurité
L’onglet sécurité vous permet de mapper les utilisateurs de la base de données SQL Server avec ceux de votre serveur Informix :
Not be made : Spécifie qu’une connexion ne sera pas établie pour les connexions non définies dans la liste.
Be made without using security context : Spécifie qu’une connexion sera établie sans utiliser de contexte de sécurité pour les connexions d’accès non définies dans la liste.
Be made using the login’s current security contexte : Spécifie qu’une connexion sera établie à l’aide du contexte de sécurité en cours de la connexion pour les connexions non définies dans la liste. Si vous vous connectez au serveur local en utilisant l’authentification Windows, vos informations d’identification Windows seront utilisées pour vos connexions au serveur distant. Si vous vous connectez au serveur local en utilisant l’authentification SQL Server, le nom et le mot de passe de connexion seront utilisés pour vos connexions au serveur distant. Dans ce cas, une connexion possédant exactement les mêmes noms et mot de passe doit exister sur le serveur distant.
Be made using the security context: Spécifie qu’une connexion sera établie en utilisant la connexion d’accès et le mot de passe définis dans les zones Ouverture de session à distance et Avec le mot de passe pour les connexions non définies dans la liste. L’ouverture de session à distance doit être une connexion utilisant l’authentification SQL Server sur le serveur distant.
Remarque : La fonction Sys.linked_logins renvoie des informations concernant les identifiants utilisés pour l’accès au serveur lié. La fonction Sys.servers renvoie une ligne par serveur lié ainsi qu’une ligne pour le serveur local dont l’argument server_id est égal à 0.
2.8 – Création des vues
Vous devez utiliser des noms en quatre parties pour faire référence à un objet sur le serveur lié.
Exemple : SELECT * FROM [ServeurLié].[BaseDeDonnées].[Schema].[Table]
Simplification des requêtes SQL :
CREATE SCHEMA [ServeurLié] AUTHORIZATION dbo;
CREATE SYNONYM [ServeurLié].[Table] FOR [ServeurLié].[BaseDeDonnées].[Schema].[Table];
SELECT * FROM [ServeurLié].[Table];
Création d’une vue :
CREATE VIEW [ViewName] AS SELECT * FROM [ServeurLié].[ BaseDeDonnées].[Schema].[Table];
Remarque: Il n’est pas possible de créer des Index sur un objet d’un serveur lié. Pour ce faire, vous devez répliquer les données de votre serveur lié dans une table sur votre serveur principal, puis créer une vue indexée sur la nouvelle table.
2.9 – Troubleshooting
Verify the status of “Distributed Transaction Coordinator” service :
Informix OLE DB provider « Ifxoledbc » reported an unexpected catastrophic failure
Problem : Informix OLE DB provider « Ifxoledbc » for an SQL Server linked server reported an unexpected catastrophic failure
Symptom : When using a Microsoft® SQL Server Linked Server to Informix server with Informix native OLE DB provider, you may get the following error if you log on to the Windows system as a domain user account that is not a member of local administrator group:
Error Message: “The OLE DB provider « Ifxoledbc » for linked server « IDS1150 » reported an error. The provider reported an unexpected catastrophic failure. “
The above error does not reproduce if the domain user account is added to the local administrator group.
Cause : Microsoft® SQL Server Linked Server may not have the permission needed to use the interface from Informix OLE DB provider. This can be caused by the provider option « Allow InProcess » not selected for the linked server.
Diagnosing the problem : If the linked server to Informix is created without the « Allow InProcess » provider options selected, Microsoft® SQL Server would be running in a different process space from the OLE DB Provider. In that case, SQL Server linked server may not allow it to have the access privileges to use certain required interface.
Resolving the problem : You need to check the provider options of the linked server to Informix and make sure the provider option « Allow InProcess » is selected.
3. Serveur lié avec le fournisseur MSDASQL
3.1 – Installation du fournisseur
Si le fournisseur n’est pas déjà présent dans la liste des fournisseurs ODBC, veuillez le télécharger à cette adresse : http://www.microsoft.com/downloads/fr-fr/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b
3.2 – Configuration du System DSN
3.3 – Test du fournisseur MSDASQL
Avec l’outil ODBC Test de Microsoft Data Access SDK 2.8 nous allons tester la connexion, dans « Fill Connect » choisissez votre connexion préalablement crée :
3. – Création du serveur lié
Dans SQL Server Management Studio vous devriez avoir le fournisseur « MSDASQL ».
Configurez les options suivantes dans ses propriétés :
• Dynamic Parameter
• Nested Queries
• Allow Inprocess
• Supports ‘Like’ Operator
Exécutez la commande suivante :
EXEC master.dbo.sp_addlinkedserver @server = N’MY_SERVER_INFORMIX’, @srvproduct=N’MSDASQL’, @provider=N’MSDASQL’, @datasrc=N’InformixDNS’, @provstr=N’DSN=Informix;Database=test;UID=informix;PWD=password’
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’MY_SERVER_INFORMIX’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’informix’,@rmtpassword=’########’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’collation compatible’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’data access’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’dist’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’pub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’rpc’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’rpc out’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’sub’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’connect timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’collation name’, @optvalue=null
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’lazy schema validation’, @optvalue=N’false’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’query timeout’, @optvalue=N’0′
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’use remote collation’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’MY_SERVER_INFORMIX’, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
4. Liens
– IIUG is a global organization dedicated to enabling the transfer of information between the community of Informix customers and Informix Software: http://www.iiug.org/
– Informix developer and user forum : https://www.ibm.com/developerworks/forums/forum.jspa?forumID=548&start=0
– DBforums is a discussion forum and helpdesk for database design, development and administration issues : http://www.dbforums.com/informix/
– Information about Linked Server with SQL Server: http://msdn.microsoft.com/en-us/library/ms188279.aspx