Interroger MySQL à partir de SQL Server à l’aide de linked server

SQL Server a des fonctions intéressantes appelées Linked Servers. Il s’agit de lier d’autres bases de données à SQL Server et d’utiliser leurs données comme si elles étaient locales. Il existe de nombreux systèmes open-source puissants écrits sur PHP et ils utilisent principalement MySQL comme base de données. Cet article de blog montre comment lier la base de données MySQL à SQL Server et comment utiliser le serveur lié dans les requêtes SQL.

qu’est-ce que le serveur lié?,

le serveur lié dans MSSQL est un autre serveur de base de données connecté à un serveur donné, ce qui permet d’interroger et de manipuler des données dans d’autres bases de données. Par exemple, nous pouvons lier une base de données MySQL à MSSQL et l’utiliser presque comme n’importe quelle autre base de données sur MSSQL.

L’Image est tirée de la page de documentation MSSQL 2019
serveurs liés (moteur de base de données)

bien que la communication avec les serveurs liés passe par les fournisseurs OLE DB, il existe également un fournisseur OLE DB pour ODBC et nous pouvons l’utiliser si notre base de données externe n’a pas de fournisseur

NB!, Le serveur lié est disponible pour l’ensemble de L’instance SQL Server. Cela signifie que toutes les bases de données SQL Server peuvent utiliser linked server pour récupérer des données.

lier MySQL à SQL Server

Ajouter un serveur lié et configurer les paramètres de connexion n’est pas toujours facile et simple.

pour que MySQL soit lié à SQL Server, j’avais besoin de créer ODBC DSN pour MySQL (je l’ai nommé MySQLCrm). Avant de passer aux étapes suivantes, assurez-vous que la source de données ODBC fonctionne.,

suivez ces étapes pour lier MySQL à SQL Server:

1. exécutez SQL Server Management Studio (SSMS)
2. connectez-vous à votre serveur
3. développez le nœud Server Objects à partir de l’arborescence à gauche
4. cliquez avec le bouton droit sur Linked Servers
5. sélectionnez New Linked Server…

vous devriez voir la boîte de dialogue suivante (ou légèrement différente mais l’idée reste la même).

NB! Portez une attention particulière à ce que vous insérez dans cette boîte de dialogue. Avec cet ensemble de données, j’ai fait fonctionner link. J’ai essayé différentes valeurs et si quelque chose ne va pas d’un millimètre, la connexion échoue., C’est un dialogue sacrément sensible.

la chaîne de connexion à la base de données MySQL devrait être comme montrée ici:

faites également attention à OPTION=3 – sans cela, je n’ai eu que des erreurs lors de la connexion au serveur lié.

essayez d’enregistrer en cliquant sur OK et voyez si vous pouvez naviguer vers le serveur lié. Si vous obtenez des erreurs, faites un clic droit sur le serveur et sélectionnez Propriétés. En gardant la boîte de dialogue ouverte, passez à la page Options du serveur. Définissez les paramètres RPC et RPC Out sur True.,

Je ne suis toujours pas très sûr de ce que font ces options, mais certains de ceux qui ont eu des problèmes avec link to MySQL l’ont fait fonctionner après avoir défini RPC-s sur true.

pour que l’interrogation fonctionne réellement, nous avons besoin d’un petit changement de plus qui affecte tout le fournisseur OLE DB et donc toutes les connexions qui l’utilisent. Ouvrez le nœud fournisseurs sous serveurs liés, cliquez avec le bouton droit sur MSDASQL (il s’agit du fournisseur OLE DB pour les sources de données ODBC) et sélectionnez Propriétés.

cochez la case avant le niveau zéro uniquement et cliquez sur OK pour enregistrer les modifications.,

interroger les données du serveur lié

interroger les bases de données liées est en fait facile. Voici la table Clients de la base de données crmlinked dans MySQL. Cette base de données est liée à mon serveur SQL.

la syntaxe pour interroger le serveur lié est un peu différente de ce que nous écrivons habituellement sur SQL Server. Nous devons utiliser des noms en quatre parties: serveur.la base de données.schéma.table. Comme il n’y a pas de schémas sur MySQL et que la chaîne de connexion spécifie le nom de la base de données, nous pouvons les laisser de côté comme indiqué ici.

select * from MYSQLCRM...customers

L’exécution de cette requête à partir de SSMS donne la sortie suivante., Ce sont les mêmes données que dans la table Clients MySQL.

bien sûr, nous pouvons également écrire des requêtes plus complexes. Tout ce QU’ODBC peut gérer est correct.

mélanger les données du serveur local et du serveur lié

Les Tables du serveur lié ne sont pas totalement isolées des tables et des vues de base de données locales. Nous pouvons également mélanger les données du serveur local et lié.

pour démontrer une requête mixte sur des tables locales et liées, écrivons une requête simple pour obtenir tous les clients de la table locale et leurs cotes de crédit de la table liée.

l’Exécution de cette requête nous donne la sortie suivante.,

comme Mark n’est pas présent dans la base de données MySQL (supposons qu’il soit nouveau client dans e-shop et que le service commercial ne l’ait pas encore dans leur système CRM), il n’a pas de cote de crédit disponible. Les cotes de crédit pour John et Mary proviennent de MySQL dans le cas actuel.

utiliser OPENQUERY() pour exécuter une requête dans un serveur lié

Les exemples ci-dessus font tous les traitements de données sur SQL Server. Cela peut être très peu optimal s’il y a beaucoup de données dans les tables du serveur lié. Nous pouvons vouloir – ou généralement vouloir-traiter certaines données dans linked server avant que SQL Server ne démarre le traitement local., Pour cela, nous avons OPENQUERY ().

Voici l’exemple d’utilisation de la fonction OPENQUERY() dans une requête mixte. Nous devons spécifier le nom du serveur lié et la requête SQL à exécuter dans le serveur lié lors de L’appel D’OPENQUERY(). La requête en rouge est exécutée dans MySQL server et les résultats sont lus dans SQL Server pour un traitement ultérieur.

SELECT
c.FirstName,
c.LastName,
crm_c.credit_rating as CreditRating
FROM 
Customers c
LEFT JOIN OPENQUERY(MYSQLCRM, '
SELECT 
c.credit_rating
FROM 
customers p
left join loyalty_points lp on
c.customer_id = lp.customer_id
WHERE
lp.points > 1000
') crm_c ON
c.ssn = crm_c.ssn
ORDER BY
crm_c.credit_rating,
c.LastName,
c.FirstName 

OPENQUERY() est un excellent moyen d’optimiser et d’accélérer les requêtes mixtes en exécutant des requêtes plus complexes sur les données du serveur lié dans le serveur lié.,

encapsuler

Les serveurs liés sont une fonctionnalité puissante de SQL Server, ce qui nous permet d’utiliser facilement les données de serveurs externes. Il existe deux façons d’écrire des requêtes à l’aide de données provenant de serveurs liés: les requêtes directes qui effectuent tout le traitement sur SQL Server et OPENQUERY() qui nous permet d’effectuer un traitement sur un serveur distant. Le serveur lié est l’intégration et son utilisation nécessite donc des soins supplémentaires. La planification et la mesure des performances sont des activités indispensables lors de la planification de l’utilisation du serveur lié.