par: Eric Blinn / mise à jour: 2019-06-28 | Commentaires (4) | connexes: Plus > serveurs liés
problème
je vois qu’il existe une option dans SQL Server Management Studio (SSMS) pour les serveurs liés et je veux en savoir plus sur ce
Solution
Les serveurs liés sont une méthode par laquelle un serveur SQL peut parler à une autre base de données conforme ODBC, telle qu’une autre instance SQL Server ou une base de données Oracle, avec une requête directT-SQL.,
Il existe plusieurs paramètres importants à connaître lors de la configuration d’un serveur lié.Cette astuce couvrira la création d’un serveur lié à une autre instance SQL Server en utilisant L’interface graphique SSMS avec les options de sécurité appropriées. Il montrera également comment utiliser le serveur lié dans une instruction T-SQL. Il y a grande bibliothèque de sujets plus avancés disponibles une fois les bases maîtrisées.,
création D’un serveur lié SQL Server
dans SQL Server Management Studio, dans le volet Explorateur D’objets, développez la section « ServerObjects », faites un clic droit sur « Linked Servers » et choisissez « Newlinked Server… » dans le menu.
Nouveau Serveur Lié Général de la Page
Qui permet d’afficher une fenêtre qui ressemble à l’écran ci-dessous. Choisissez le servertype comme SQL Server et tapez le nom de l’autre instance de SQL Server. Dans cet exemple, la connexion sera établie à une instance nommée de SQL Server sur la même machine., Ceci est certainement autorisé, mais le plus souvent la connexion est faiteà une autre machine entièrement. Le nom du serveur lié est spécifié comme theServerName\InstanceName. Dans l’exemple ci-dessous, je me connecte au localserver et le nom de l’instance est « SECURITY_TEST ».
nouveau serveur lié – page Sécurité
ensuite, passez de la page général à la page Sécurité en utilisant le menu de gauche.Cette page permet à l’administrateur d’indiquer à ce serveur SQL comment s’authentifier auprès de l’autre instance., La moitié supérieure de l’écran permet aux connexions individuelles d’accéder à l’utilisation du serveur lié.
Il existe 2 méthodes pour authentifier les utilisateurs. La première consiste à sélectionner une connexion pourles utiliser lors de l’utilisation du serveur lié. Dans la capture d’écran ci-dessous, L’utilisateur Eric se connectera automatiquement au serveur secondaire en tant que connexion LinkedServerTeston l’autre instance. Ces informations d’identification sont stockées dans le moteur SQL Server et sont cryptées. Sur la deuxième ligne, la connexion LinkedServerTest (une connexion SQL) L’Impersonatebox est vérifiée., Cela tentera d’utiliser le même nom d’utilisateur et mot de passe sur le serveur LinkedServer que sur le serveur d’origine. L’usurpation d’identité D’une connexion Windows peut égalementêtre fait, mais nécessite une délégation appropriée.
Maintenant se concentrer sur la moitié inférieure de l’écran. Il y a 4 options disponibles. Whicheveroption est sélectionné ici s’appliquera à tout utilisateur non répertorié dans la section supérieure.,
| Option | Description |
|---|---|
| Pas fait | Cela va donner une erreur à tout utilisateur de tenter de l’utiliser Lié Serverthat n’est pas dans la liste ci-dessus. Pour sélectionner cette option, il doit y avoir au moins 1 connexion dans la moitié supérieure de l’écran. c’estl’erreur que tous les autres utilisateurs obtiendront. Msg 7416, niveau 16,état 1, Ligne 1 L’accès au serveur distant est refusé car aucun login-mappingexists., |
| être créé sans utiliser de contexte de sécurité | cela enverra une connexion anonyme au serveur lié et ne fournira probablement aucun accès au serveur secondaire. |
| être fait en utilisant le contexte de sécurité actuel de la connexion | cela revient à placer chaque utilisateur dans la section supérieure avec la case à cocher usurper l’identité cochée., |
| être fait en utilisant ce contexte de sécurité: | cette option exigera qu’un identifiant et un mot de passe soient entrés et fonctionnera de la même manière que si chaque utilisateur avait été ajouté à la section supérieure et donné exactement le même nom d’utilisateur et mot de passe. C’est la méthode la moins sécurisée pour configurer un serveur lié car toute personne ayant un accès au premier serveur SQL pourra utiliser le serveur lié en utilisant les informations d’identification enregistrées., |
nouveau serveur lié – page Options du serveur
Il existe également des paramètres sur la page Options, mais pour les requêtes simples, ces paramètres peuvent être laissés comme valeurs par défaut, comme indiqué ci-dessous.
Les paramètres n’ont pas besoin d’être modifié dans la plupart des cas d’utilisation et ce tableau willexplain ce que les paramètres ne.
| Option | Description |
|---|---|
| Compatible avec le Classement | valeur par Défaut est false., Cela peut être défini sur true si le serveur cible utilise exactement le même classement que le serveur principal. Le changepeut améliorer les performances de certaines requêtes en autorisant la comparaison de colonnes distantes dans les colonnes de type texte. |
| Accès aux Données | valeur par Défaut est True. Lorsque false toutes les exécutions de requêtes distantes individuelles échoueront. Ce paramètre est indépendant de RPC Out. Dans un scénario, où seuls les appels de procédure distants doivent être exécutés, ce paramètre peut être changé en false tandis que RPC Out reste true., |
| RPC/rpc Out | cela signifie Appel de procédure À Distance et vous permet d’exécuter storedprocedures sur le serveur lié. RPC active les appels de procédure distants à partir du serveur spécifié et RPC OUT active les appels de procédure distants vers le serveur spécifié. |
| utiliser le nom de classement/classement distant | la valeur par défaut est False. Lorsqu’il est faux, le classement du serveur local sera utilisé sur toutes les requêtes distantes. Lorsque true, le classement du remoteserver sera utilisé sur les requêtes., Si true et que le serveur distant n’est pas un SQLServer, le serveur local ne peut pas rechercher automatiquement la valeur de classement appropriée. Dans ce cas, utilisez le paramètre Nom du classement pour saisir le classement du serveur theremote. |
| Délai d’attente de Connexion | valeur par Défaut est de 0. Lorsqu’il est différent de zéro, il s’agit du nombre de secondes pourattendre avant de temporiser lors de la connexion à un serveur lié. Quand 0 alors thevalue est en fait lu à partir d’un serveur par défaut qui peut être trouvé par runningthis commande.
exec sp_configure 'remote login timeout (s)' |
| Délai d’attente de Requête | valeur par Défaut est de 0., Lorsqu’il est différent de zéro, il s’agit du nombre de secondes à attendre avant de chronométrer une requête exécutée sur un serveur lié. Lorsque 0, la valeur est en fait lue à partir d’une valeur par défaut du serveur qui peut être trouvée en exécutant cette commande.
exec sp_configure 'remote query timeout (s)' |
| distributeur / éditeur/abonné | ces paramètres ne sont utilisés que lorsque le serveur lié est utilisé pour prendre en charge la réplication. |
| validation de schéma paresseux | la valeur par défaut est False. Lorsqu’il est faux, le schéma d’une requête distante est checkedbefore chaque exécution de requête., Toutes les modifications apportées au schéma distant sont détectées à l’avance et un plan de requête approprié peut être recréé pour compterpour la modification. Lorsqu’il est vrai, le schéma n’est validé qu’au moment de l’exécution. Si le changement dans le schéma distant rend le plan de requête invalidthat provoquera une erreur d’exécution. |
| Activer la Promotion de Transactions Distribuées | valeur par Défaut est True. Lorsqu’il est activé, toute requête distante qui modifie les données appellera le DTC (Distributed Transaction Coordinator)., Ce processprotecte de telles requêtes en permettant aux deux serveurs d’avoir une transaction partagée qui termine 100% ou 0% – tout comme une transaction régulière avec une requête locale. Lorsqu’il est désactivé, le DTC n’est pas appelé et une remotequery défaillante peut échouer et ne pas être complètement annulée. |
interroger des données sur un serveur lié SQL Server
Pour lire des données à partir d’un serveur lié, toutes les tables ou vues doivent être référencées à l’aide d’un identifiant en 4 parties composé du nom du serveur lié, du nom de la base de données, du nom,
Par exemple:
SELECT * FROM .master.sys.databasesSELECT * FROM .WideWorldImporters.Sales.Orders
- tout d’abord, le nom du serveur lié est fourni pour notre exemple. Dans cet exemple, il doit être entre crochets en raison de son format. Tous les noms LinkedServer ne doivent pas être entre crochets.
- suivant est le nom de la base de données, maître et WideWorldImporters respectivement.
- Le troisième est le nom du schéma, sys et Sales respectivement.
- Enfin, le nom de l’objet est répertorié. Dans ces exemples, les objets sont des bases de données et des ordres.
cet exemple est assez laid en raison de la nature du nom du serveur lié., Heureusement, cette excellente astuce montre comment rendre les noms plus faciles à lire dans des scénarios commececi.
Une fois répertoriées dans une clause FROM, ces tables et vues peuvent être traitées de la même manière que toute autre table locale. Ils peuvent être interrogés, aliasés, joints, ETSI les autorisations appropriées sont définies, ils peuvent être modifiés à l’aide d’insert, update et deletestatements.
appel de procédures stockées sur un serveur lié
pour les besoins de cette démo, cette petite procédure a été créée dans la masterdatabase sur le serveur lié cible.,
USE masterGOCREATE PROCEDURE dbo.SQLTips1 AS SELECT 'I was able to be called';
la procédure est ensuite exécutée sur l’instance principale en utilisant cette commande qui suit le même schéma d’Identificateur de 4 parties que les instructions de la section précédente.
EXEC .master.dbo.SQLTips1
Cependant, au lieu de le résultat attendu, une erreur est générée.
RPC signifie » Appel de Procédure Distante. Par défaut, les serveurs liés sont remarquables pour effectuer des appels de procédure stockés. Ceci est facilement résolu en activant le paramètre asingle sur le serveur lié., Accédez aux propriétés du serveur lié,choisissez Options du serveur et la 4e option de la liste sera RPC Out. Changez simplement cette valeur en True et clickOK.
le paramètre RPC n’est pas la valeur à modifier. Cela peut sembler déroutant car le message d’erreur semble le nommer spécifiquement, mais c’est en fait un paramètre hérité qui n’active pas les appels RPC.
la Reprise de la procédure produit un bien meilleur résultat.,
limitations du serveur lié SQL Server
Les serveurs liés peuvent être très utiles, mais il existe quelques limitations dont il faut être conscient avant de les implémenter. Les serveurs liés ne peuvent pas être utilisés pour appeler des fonctions tablevalued ou scalar. En outre, la commande truncate n’est pas autorisée à êtreappelé à l’aide d’un serveur lié.
cas D’Utilisation du serveur lié SQL Server
les performances des serveurs liés ne sont pas toujours excellentes, en particulier avec des ensembles de données largedata et de nombreuses jointures entre les tables locales et liées. Mais ils peuvent être trèsutile pour interroger des ensembles de données plus petits.,
parfois, une exigence ETL pour un ensemble de données plus petit peut être satisfaite en utilisant un LinkedServer plutôt qu’un outil ETL comme SSIS. Lorsque les lignes impliquées se situent dans les milliers ou dans les 10 Secondes inférieures à des milliers, un ETL basé sur un serveur lié peut être en mesure de terminer en moins de temps qu’il ne faut pour démarrer le moteur SSIS.
prochaines étapes
- Il existe toute une famille de conseils avancés pour les serveurs liés sur MSSQLTips.,com
- de Décisions Liées Serveur de noms plus faciles à lire
Dernière mise à Jour: 2019-06-28
a Propos de l’auteur
Eric Blinn est le Père Architecte de Données pour Squire Patton Boggs. Il est également auteur SQL et chef de groupe local PASS.voir tous mes conseils
- plus de conseils SQL Server DBA…