une activité courante lors de l’écriture de requêtes T-SQL consiste à se connecter à des bases de données locales et à traiter directement les données. Mais il y aura des situations dans lesquelles vous devrez vous connecter à une base de données distante située dans une instance différente du même serveur ou dans un serveur physique différent, et traiter ses données en parallèle avec le traitement des données locales.,
SQL Server nous fournit quatre méthodes utiles pour se connecter aux serveurs de base de données distants, même à d’autres types de serveurs de base de données, et interroger ses données dans votre instruction T-SQL. Dans cet article, nous allons discuter de ces quatre méthodes et comment l’utiliser pour interroger des bases de données SQL Server distantes.
OPENDATASOURCE
La première méthode pour interroger une base de données SQL Server distante est la fonction T-SQL OPENDATASOURCE ci-dessous:
OPENDATASOURCE ( provider_name as char, init_string)
où le provider_name est le fournisseur OLE DB utilisé pour accéder à la source de données., Et le init_string est la chaîne de connexion du serveur distant.
pour pouvoir utiliser L’instruction OPENDATASOURCE, vous devez vous assurer que la clé de registre DisallowAdhocAccess est définie sur 0 pour le fournisseur auquel vous souhaitez vous connecter autre que le serveur SQL, qui se trouve dans le chemin ci-dessous des clés de Registre :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLSERVER\Providers\<ProviderName>
Vous devez également activer l’option de configuration avancée des requêtes distribuées ad hoc qui est désactivée par défaut dans SQL Server., Si vous essayez d’exécuter la requête simple ci-dessous qui utilise L’instruction OPENDATASOURCE T-SQL, vous obtiendrez l’erreur:
Msg 15281, niveau 16, état 1, Ligne 1
SQL Server a bloqué l’accès à L’instruction ‘OpenRowset/OpenDatasource’ du composant ‘ad Hoc Distributed Queries’ car ce composant est désactivé dans le cadre de la configuration de sécurité de ce serveur. Un administrateur système peut activer l’utilisation de « requêtes distribuées Ad Hoc » en utilisant sp_configure., Pour plus d’informations sur l’activation des « requêtes distribuées Ad Hoc », recherchez « requêtes distribuées Ad Hoc » dans SQL Server Books Online.
comme vous pouvez le voir dans le message d’erreur, L’option de configuration avancée des requêtes distribuées Ad Hoc doit être activée afin d’ouvrir la connexion à un serveur distant à l’aide D’OPENDATASOURCE.,
|
1
2
3
4
5
6
|
EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
EXEC sp_configure ‘ad hoc distributed queries’, 1
RECONFIGURE
GO
|
Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully., OPENDATASOURCE peut remplacer le nom du serveur dans le nom en quatre parties de la table ou de la vue dans une instruction SELECT, INSERT, UPDATE ou DELETE. Il peut également être utilisé dans L’instruction EXECUTE pour exécuter une procédure stockée à distance.
OPENROWSET
la deuxième façon d’interroger une base de données hébergée dans un serveur SQL distant est la fonction OPENROWSET T-SQL. Afin d’utiliser la méthode ad hoc OPENROWSET, vous devez fournir toutes les informations de connexion requises pour vous connecter au serveur SQL distant et à de nombreuses autres ressources., Il peut également être utilisé pour une opération en vrac via le fournisseur en vrac intégré pour lire les données à partir de fichiers. OPENROWSET est utilisé dans la clause FROM en tant que nom de table dans les instructions SELECT, INSERT, UPDATE ou DELETE. Bien que la requête puisse renvoyer plusieurs jeux de résultats, OPENROWSET ne renvoie que le premier.
L’utilisation D’OPENROWSET nécessite l’activation de L’option de configuration avancée des requêtes distribuées ad Hoc, identique à la fonction OPENDATASOURCE.,979d8a2e »>
Vous pouvez écrire la requête OPENDATASOURCE précédente en utilisant la fonction OPENROWSET comme suit:
serveur lié
un serveur lié SQL Server est utilisé pour accéder à des sources de données OLE DB distantes telles qu’une instance SQL Server située en dehors du serveur SQL ou d’autres systèmes tels qu’Oracle, Microsoft Access et Excel, et exécuter les requêtes T-SQL distribuées contre elles.,
un serveur lié SQL Server est différent des requêtes ad hoc en ce que les requêtes ad hoc ouvrent une connexion temporaire avec le serveur distant et la ferment, où le serveur lié permanent est toujours disponible pour une utilisation. Lorsque l’utilisateur exécute une requête distribuée sur une source de données distante à l’aide d’un serveur lié, le moteur SQL Server analyse cette commande et envoie les requêtes à OLE DB. Cette requête peut être une requête à exécuter ou une table à ouvrir sur ce serveur distant.,
un serveur lié peut être configuré en utilisant SQL Server Management Studio ou en utilisant l’instruction sp_addlinkedserver T-SQL.
afin de configurer un serveur lié utilisant SQL Server Management Studio, développez le noeud D’objets de serveur de la fenêtre D’Explorateur D’objets. Cliquez avec le bouton droit sur le nœud du serveur lié et choisissez Nouveau serveur lié.
Dans l’onglet Général de la Nouveau Serveur Lié fenêtre, choisissez un nom pour votre serveur lié, puis choisissez le type de serveur vous devez vous connecter à l’aide de ce serveur lié., Sélectionnez SQL Server si vous parvenez à vous connecter à une instance SQL Server distante, ou choisissez une autre Source de données à sélectionner parmi les types de serveur OLE DB disponibles dans la liste déroulante Fournisseur autre que SQL Server. Si vous choisissez SQL Server comme type de serveur, le nom du serveur lié doit être le nom de réseau du serveur SQL distant.
remplissez le champ Nom du produit avec le nom du produit de la source de données OLE DB sélectionnée, telle que SQL Server si vous vous connectez à un serveur SQL distant., Saisissez le nom de la source de données sélectionnée dans le champ Source de données, par exemple le nom de L’Instance SQL Server si vous vous connectez à une instance SQL Server distante. Remplissez le champ chaîne de fournisseur avec la chaîne de connexion spécifique au fournisseur OLE DB qui identifie une source de données unique. Le champ Emplacement peut être rempli avec l’emplacement de la base de données distante tel qu’interprété par le fournisseur OLE DB. Saisissez le nom du catalogue à utiliser par le fournisseur OLE DB dans le champ Catalogue.,
Vous n’êtes pas obligé d’utiliser tous les décrit arguments ensemble, comme les arguments nécessaires dépendent du fournisseur sélectionné. Par exemple, en utilisant SQL Server provider, vous n’utiliserez que deux arguments.
sur la page Sécurité de la nouvelle fenêtre du serveur lié, spécifiez le contexte de sécurité que le serveur lié utilisera pour connecter le serveur SQL D’origine à la source de données distante., Comme son nom l’explique, dans la partie mappages connexions de serveur Local à Connexions de serveur distant de la fenêtre sécurité, Vous pouvez spécifier une liste d’utilisateurs pouvant utiliser le serveur lié pour se connecter au serveur distant en mappant ces utilisateurs locaux avec des connexions de serveur distant. Ces utilisateurs peuvent être via L’authentification SQL Server ou une connexion D’authentification Windows.
pour les connexions qui n’ont pas été définies dans la liste de mappage, vous devez choisir le contexte de sécurité pour leurs connexions au serveur distant à l’aide de ce serveur lié., Choisir Not be made empêchera tout utilisateur non inclus dans la liste précédente d’utiliser ce serveur lié. En sélectionnant être effectué sans utiliser de contexte de sécurité, les utilisateurs non inclus dans la liste de mappage précédente se connecteront au serveur distant à l’aide de ce serveur lié sans spécifier de contexte de sécurité pour eux. Si vous choisissez être établi à l’aide du contexte de sécurité actuel de la connexion, la connexion au serveur distant sera établie à l’aide de l’utilisateur connecté, ce qui est le meilleur choix., Dans L’option être fait à l’aide de ce contexte de sécurité, spécifiez les informations d’identification D’authentification SQL Server qui seront utilisées pour établir la connexion au serveur distant pour les utilisateurs non définis dans la liste de mappage.
Il existe de nombreuses options que vous pouvez régler en fonction de vos besoins à partir de la page Options du serveur de la nouvelle fenêtre du serveur lié. Par exemple, définissez l’option Collation Compatible sur true si vous êtes sûr que la source de données distante a le même jeu de caractères et le même ordre de tri que le serveur local., Par défaut, SQL Server évalue localement les comparaisons sur les colonnes de caractères si vous n’utilisez pas cette option. L’option Accès aux données est utilisée pour activer et désactiver l’accès aux requêtes distribuées pour le serveur lié. Pour activer l’appel de procédure distante à partir du serveur spécifié, définissez le RPC sur true, et pour activer l’appel de procédure distante vers le serveur spécifié, définissez le RPC sur true.
décider si le classement du serveur local ou distant sera utilisé dans la requête est déterminé par L’option Utiliser le classement distant., Si la valeur de cette option est true, vous pouvez spécifier le nom de classement qui sera utilisé par le serveur distant dans l’option Nom de classement. Cela s’applique si la source de données distante n’est pas SQL Server, où vous pouvez spécifier n’importe quel nom de classement pris en charge par SQL Server dans ce champ.
Vous pouvez remplacer le délai de connexion à distance du serveur par défaut pour ce serveur lié en changeant l’option Délai de connexion à une valeur supérieure à 0.Cette valeur spécifie le délai d’expiration en secondes pour la connexion au serveur lié., Vous pouvez également spécifier le délai d’expiration en secondes pour les requêtes se connectant au serveur lié en changeant l’option de délai D’expiration de la requête en une valeur supérieure à 0. Cela remplacera le délai d’expiration de la requête distante du serveur pour le serveur lié.
Une autre option utile utilisée pour démarrer une transaction distribuée lors de l’appel d’une procédure stockée distante, dans laquelle cette transaction sera gérée et protégée par le DTC MS. Cette option S’appelle activer la Promotion des Transactions distribuées.,le message ci-dessous, sinon un message d’erreur s’affichera indiquant que quelque chose empêche l’ouverture de la connexion:
Vous pouvez facilement créer le serveur lié précédent en utilisant l’instruction sp_addlinkedserver T-SQL en passant les arguments requis comme suit:
|
1
2
3
4
|
utilisez
Go
EXEC master.,dbo.sp_addlinkedserver @serveur = N’DEV_SQL’, @srvproduct=N’SQL Serveur »
ALLER
|
une Fois que le serveur lié est créé avec succès, vous serez capable de l’utiliser en spécifiant le nom en quatre parties, qui comprend: Nom_de_serveur_lié.Remote_Database_Name.Schema_Name.Table_Name comme dans l’exemple ci-dessous:
|
1
2
|
SELECT * from DEV_SQL.,testdb.dbo.Profil
ALLER
|
OPENQUERY
La dernière SQL Server méthode qui est utilisée pour se connecter à une source de données distante est la fonction OPENQUERY. Il s’agit d’une méthode ad hoc unique alternative pour se connecter à un serveur distant à l’aide du serveur lié. Pour des connexions plus fréquentes au serveur distant, il est préférable d’utiliser le serveur lié au lieu de la fonction OPENQUERY.
la fonction OPENQUERY peut être utilisée dans la clause FROM de L’instruction SELECT, INSERT, UPDATE ou DELETE remplaçant le nom de la table., Il faut deux arguments; le nom du serveur lié et la requête. Ces paramètres ne peuvent pas être variable, comme suit:
OPENQUERY ( linked_server , « query »)
ci-Dessous est un exemple simple de la OPENQUERY utilisation:
|
1
|
SÉLECTIONNEZ * à PARTIR de OPENQUERY(DEV_SQL,’SELECT * from testdb.dbo.,
|
comparaison
lorsque vous utilisez le serveur lié pour interroger un serveur distant, l’optimiseur de requêtes crée le plan d’exécution après avoir Classifié et divisé la requête en requêtes locales et distantes, où les requêtes locales seront exécutées localement et les requêtes distantes seront envoyées au serveur distant, puis combinées ensemble pour afficher le résultat final à l’utilisateur en tant qu’ensemble de résultats unique., Un autre inconvénient du serveur lié est qu’aucun filtrage ne sera appliqué sur le serveur distant si la requête a la clause WHERE, où elle récupérera tous les enregistrements de la table distante et effectuera le filtrage et la jointure localement.
dans le cas de la fonction OpenQuery, le moteur SQL n’essaiera pas de classer la requête ou de vérifier ce qu’il fera, il enverra simplement la requête telle quelle au serveur distant. L’analyse de la requête SQL, la génération du plan d’exécution et tout le filtrage seront effectués sur le serveur distant.,
en général, OpenQuery est plus rapide que le serveur lié car le moteur SQL ne casse pas la requête avant de l’envoyer au serveur distant, mais n’est utile que pour des connexions distantes ponctuelles et moins fréquentes.
en utilisant les fonctions OPENROWSET et OPENDATASOURCE, vous devez spécifier tous les détails de connexion, y compris le nom d’utilisateur et le mot de passe chaque fois que vous l’utilisez. Bien que ces fonctions ne fournissent pas toutes les fonctionnalités du serveur lié telles que la gestion de la sécurité, elles consomment moins de ressources de votre serveur., Comme ces fonctions ouvrent une connexion unique au serveur distant, il est préférable d’utiliser le serveur lié pour un accès fréquent au serveur distant.
- Auteur
- Derniers Posts
Il est expert en solutions certifiées Microsoft en gestion et analyse de données, Microsoft Certified Solution Associate en administration et développement de bases de données SQL, Azure Developer Associate et Microsoft Certified Trainer.
En outre, il contribue avec ses conseils SQL dans de nombreux blogs.,
Afficher tous les articles par Ahmad Yaseen
- Comment faire pour surveiller Azure Data Factory – 15 janvier 2021
- Utilisation de la Source de Contrôle dans Azure Data Factory – 12 janvier 2021
- à l’Aide de Modèles dans Azure Data Factory – 8 janvier 2021