SQL Server Verknüpfte Server verstehen

Von: Eric Blinn / Aktualisiert: 2019-06-28 / Kommentare (4) | Verwandt: Mehr > Verknüpfte Server

Problem

Ich sehe, dass es in SQL Server Management Studio (SSMS) eine Option für verknüpfte Server gibt und Ich möchte mehr darüber erfahren, was sie tun und wie sie funktionieren.

Lösung

Verknüpfte Server sind eine Methode, mit der ein SQL-Server mit einer directT-SQL-Abfrage mit einer anderen ODBC-kompatiblen Datenbank, z. B. einer anderen SQL Server-Instanz oder einer Oracle-Datenbank, kommunizieren kann.,

Beim Einrichten eines verknüpften Servers sind einige wichtige Einstellungen zu beachten.Dieser Tipp behandelt das Erstellen eines verknüpften Servers mit einer anderen SQL Server-Instanz mithilfe der SSMS-GUI sowie der entsprechenden Sicherheitsoptionen. Es wird auch gezeigt, wie man verwendetder verknüpfte Server in einer T-SQL-Anweisung. Es gibt eine Bibliothek mit fortgeschritteneren Themen, sobald die Grundlagen beherrscht wurden.,

Erstellen eines SQL Server Linked Servers

Erweitern Sie in SQL Server Management Studio im Bereich Object Explorer den Abschnitt „ServerObjects“, klicken Sie mit der rechten Maustaste auf „Linked Servers“ und wählen Sie „NewLinked Server…“ aus dem Menü.

New Linked Server – Allgemeine Seite

, die einen Bildschirm öffnet, der wie der Bildschirm unten aussieht. Wählen Sie servertype als SQL Server und geben Sie den Namen der anderen Instanz von SQL Server ein. In diesem Beispiel wird die Verbindung zu einer benannten Instanz von SQL Server auf dem gleichen Computer hergestellt., Dies ist sicherlich erlaubt, aber häufiger wird die Verbindung hergestelltzu einer anderen Maschine vollständig. Der Name des verknüpften Servers wird als theServerName\instanceName angegeben. Im folgenden Beispiel stelle ich eine Verbindung zum Localserver her und der Instanzname lautet „SECURITY_TEST“.

Neue verknüpfte Server – Sicherheitsseite

Wechseln Sie anschließend über das Menü links von der Seite Allgemein zur Sicherheitsseite.Auf dieser Seite kann der Administrator diesem SQL Server mitteilen, wie er sich bei der anderen Instanz authentifizieren soll., Die obere Hälfte des Bildschirms ermöglicht es einzelnen Anmeldungen, den Zugriff auf den verknüpften Server zu erhalten.

Es gibt 2 Methoden zur Authentifizierung von Benutzern. Die erste besteht darin, ein Login fürthem auszuwählen, das bei Verwendung des verknüpften Servers verwendet werden soll. Im Screenshot unten meldet sich der sekundäre Benutzer automatisch beim sekundären Server als Login-LinkedServerTeston der anderen Instanz an. Dieser Berechtigungsnachweis wird in der SQL Server Engine gespeichert und ist verschlüsselt. In der zweiten Zeile wird die LinkedServerTest-Anmeldung (eine SQL-Anmeldung) in der Impersonatebox überprüft., Dadurch wird versucht, auf dem LinkedServer denselben Benutzernamen und dasselbe Kennwort wie auf dem ursprünglichen Server zu verwenden. Der Identitätswechsel einer Windows-Anmeldung kann ebenfalls erfolgen, erfordert jedoch die Einrichtung einer ordnungsgemäßen Delegierung.

Konzentrieren Sie sich nun auf die untere Bildschirmhälfte. Es stehen 4 Optionen zur Verfügung. Welche Option auch immer hier ausgewählt ist, gilt für jeden Benutzer, der nicht im oberen Abschnitt aufgeführt ist.,

Option	Beschreibung
Nicht	Dies wird eine Fehlermeldung zu geben, um jedem Benutzer, der versucht, zu verwenden die Verlinkten Serverthat ist nicht in der Liste oben. Um diese Option auszuwählen, muss mindestens 1 Login in der oberen Bildschirmhälfte vorhanden sein. Dies ist der Fehler, den alle anderen Benutzer erhalten. Msg 7416, Level 16, Status 1, Zeile 1 Der Zugriff auf den Remote-Server wird verweigert, da keine Login-mappingexists.,
Ohne Verwendung eines Sicherheitskontexts hergestellt werden	Dies sendet eine anonyme Verbindung zum verknüpften Server und bietet wahrscheinlich keinen Zugriff auf den sekundären Server.
Kann mit dem aktuellen Sicherheitskontext des Logins erstellt werden	Dies entspricht dem Platzieren jedes einzelnen Benutzers im oberen Abschnitt mit aktiviertem Kontrollkästchen „Identitätswechsel“.,
Unter Verwendung dieses Sicherheitskontexts:	Diese Option erfordert die Eingabe eines Logins und Passworts und funktioniert so, als ob jeder einzelne Benutzer zum oberen Abschnitt hinzugefügt und genau derselbe Benutzername und Passwort angegeben würde. Es ist die am wenigsten sichere Methode zum Einrichten eines verknüpften Servers, da jeder, der Zugriff auf den ersten SQL Server hat, den verknüpften Server unter Verwendung der gespeicherten Anmeldeinformationen verwenden kann.,

Neue verknüpfte Server – Server-Optionsseite

Es gibt auch Einstellungen auf der Optionsseite, aber für einfache Abfragen diese Einstellenkann als Standardwerte belassen werden, wie unten gezeigt.

Die Einstellungen müssen in den meisten Anwendungsfällen nicht geändert werden, und diese Tabelle erklärt, was die Einstellungen tun.

Option	Beschreibung
Sortierung Kompatibel	Standard ist false., Dies kann auf true gesetzt werden, wenn der Zielserver genau dieselbe Sortierung wie der Hauptserver verwendet. Die changecan die Leistung einiger Abfragen verbessern, indem Remote-Spalte comparisonsonson Text-Typ Spalten.
Datenzugriff	Default ist True. Bei false schlagen alle einzelnen Remote-Abfrageausführungen fehl. Diese Einstellung ist unabhängig von RPC-Out. In einem Szenario, in dem NUR Remoteprozeduraufrufe ausgeführt werden müssen, kann diese Einstellung auf false gesetzt werden, während RPC Out true bleibt.,
RPC / RPC-Out	Das steht für Remote Procedure Call und ermöglicht die Ausführung von storedprocedures auf dem Verbindungsserver. RPC aktiviert Remote ProcedureCalls vom angegebenen Server und RPC OUT aktiviert Remote ProcedureCalls zum angegebenen Server.
Verwenden Remote Collation / Collation Name	Standard ist False. Wenn false, wird die Sortierung des lokalen Servers für alle Remote-Abfragen verwendet. Bei true wird die Sortierung des Remoteservers für Abfragen verwendet., Wenn true und der Remote-Server KEIN SQLServer ist, kann der lokale Server den richtigen Collationvalue nicht automatisch nachschlagen. Verwenden Sie in diesem Fall die Einstellung Collation Name, um theremote server collation einzugeben.
Verbindungszeitüberschreitung	Standard ist 0. Bei ungleich Null ist dies die Anzahl der Sekunden, die vor dem Timing beim Herstellen einer Verbindung zu einem verknüpften Server ausgeführt werden müssen. Wenn 0, wird der Wert tatsächlich von einem Serverstandard gelesen, der durch Ausführen von gefunden werden kannthis Befehl. exec sp_configure 'remote login timeout (s)'
Abfrage-Timeout	Default ist 0., Wenn ungleich Null ist, ist dies die Anzahl der Sekunden, die vor dem Timing einer Abfrage, die auf einem verknüpften Server ausgeführt wird, ausgeführt wird. Wenn 0 dannDer Wert wird tatsächlich von einem Serverstandard gelesen, der durch Ausführen dieses Befehls gefunden werden kann. exec sp_configure 'remote query timeout (s)'
Distributor / Publisher / Subscriber	Diese Einstellungen werden nur verwendet, wenn der Verbindungsserver verwendet wird tosupport Replikation.
Lazy Schema Validation	Default ist False. Bei false wird das Schema einer Remote-Abfrage vor jeder Abfrageausführung überprüft., Alle Änderungen am Remote-Schema werden im Voraus festgelegt und ein ordnungsgemäßer Abfrageplan kann neu erstellt werden, um der Änderung Rechnung zu tragen. Wenn true, wird das Schema erst zum Zeitpunkt der Verfolgung validiert. Wenn die Änderung des Remote-Schemas den Abfrageplan ungültig macht, führt dies zu einem Laufzeitfehler.
Enable Förderung von Verteilten Transaktionen	der Standardwert ist True. Wenn eine Remote-Abfrage aktiviert ist, die Daten ändert, ruft Sie den DTC (Distributed Transaction Coordinator) auf., Dieser Prozess schützt solche Abfragen, indem beide Server eine gemeinsam genutzte Transaktion haben, die entweder 100% oder 0% abschließt – genau wie eine reguläre Transaktion mit einer lokalen Abfrage. Wenn deaktiviert, wird der DTC nicht aufgerufen und eine fehlgeschlagene Remotequery kann fehlschlagen und nicht vollständig zurückgesetzt werden.

Abfragen von Daten über einen mit SQL Server verknüpften Server

Zum Lesen von Daten von einem verknüpften Server müssen alle Tabellen oder Ansichten referenziert werdenein 4-teiliger Bezeichner, der aus dem Namen des verknüpften Servers, dem Datenbanknamen, dem Schemanamen und dem Objektnamen besteht – in dieser Reihenfolge.,

Zum Beispiel:

SELECT * FROM .master.sys.databasesSELECT * FROM .WideWorldImporters.Sales.Orders

Zuerst wird der verknüpfte Servername angegeben, der forour Beispiel. In diesem Beispiel muss es aufgrund seines Formats in Klammern gesetzt werden. Nicht alle LinkedServer-Namen müssen in Klammern gesetzt werden.
Weiter ist der Datenbankname, Master und WideWorldImporters jeweils.
Drittens ist der Schemaname sys bzw. Sales.
Zuletzt wird der Objektname aufgelistet. In diesen Beispielen sind die Objekte databasesand Orders.

Dieses Beispiel ist aufgrund der Art des verknüpften Servernamens ziemlich hässlich., Glücklicherweise zeigt dieser großartige Tipp, wie Sie die Namen in Szenarien wie diesen leichter lesen können.

Einmal in einer FROM-Klausel aufgeführt, können diese Tabellen und Ansichten genauso behandelt werden wie andere lokale Tabellen. Sie können abgefragt, mit einem Alias versehen, verbunden undwenn die richtigen Berechtigungen festgelegt sind, können sie mithilfe von Insert, update und Deletestatements geändert werden.

Aufrufen gespeicherter Prozeduren über einen verknüpften Server

Für die Zwecke dieser Demo wurde diese kleine Prozedur in der Masterdatabase auf dem verknüpften Zielserver erstellt.,

USE masterGOCREATE PROCEDURE dbo.SQLTips1 AS SELECT 'I was able to be called';

Die Prozedur wird dann auf der primären Instanz mit diesem Befehl ausgeführt, der das gleiche 4-teilige Bezeichnerschema wie die Anweisungen im vorherigen Abschnitt folgt.

EXEC .master.dbo.SQLTips1

Anstelle des erwarteten Ergebnisses wird jedoch ein Fehler ausgelöst.

RPC steht für Remote Procedure Call. Standardmäßig können verknüpfte Server gespeicherte Prozeduraufrufe ausführen. Dies kann leicht behoben werden, indem die Asingle-Einstellung auf dem verknüpften Server aktiviert wird., Navigieren Sie zu den verknüpften Servereigenschaften, wählen Sie Serveroptionen und die 4thoption in der Liste wird RPC Out. Ändern Sie diesen Wert einfach in True und klicken Sie auf Ok.

Die Einstellung RPC ist NICHT der Wert, der geändert werden muss. Dies kann verwirrend erscheinen, da die Fehlermeldung es speziell zu benennen scheint, aber das ist eigentlich eine Legacy-Einstellung, die keine RPC-Aufrufe aktiviert.

Das erneute Ausführen der Prozedur führt jetzt zu einem viel besseren Ergebnis.,

SQL Server-Verbindungsserver Grenzen

Verbindungsserver können sehr nützlich sein, aber es gibt ein paar Einschränkungen, von denen tobe bewusst, bevor Sie Sie implementieren. Verknüpfte Server können nicht zum Aufrufen von Tablevalued-oder Skalarfunktionen verwendet werden. Außerdem darf der Befehl truncate nicht mit einem verknüpften Server deinstalliert werden.

SQL Server Verknüpfte Server-Anwendungsfälle

Die Leistung verknüpfter Server ist nicht immer großartig, insbesondere bei großen Datensätzen und vielen Verknüpfungen zwischen lokalen und verknüpften Tabellen. Sie können jedoch sehr nützlich sein, um kleinere Datensätze abzufragen.,

Manchmal kann eine ETL-Anforderung für einen kleineren Datensatz mit einem LinkedServer und nicht mit einem ETL-Tool wie SSIS erfüllt werden. Wenn die beteiligten Zeilen in den tausendund niedrigeren Zehntausenden zählen, kann eine verknüpfte serverbasierte ETL möglicherweise in kürzerer Zeit abgeschlossen werden, als zum Starten der SSIS-Engine erforderlich ist.

Nächste Schritte

Es gibt eine ganze Familie von erweiterten Linked Server Tipps zu MSSQLTips.,com
Erleichtert das Lesen von verknüpften Servernamen

Zuletzt aktualisiert: 2019-06-28

Über den Autor

Eric Blinn ist der Sr. Data Architect für Squire Patton Boggs. Er ist auch ein SQL-Autor und ein lokaler Gruppenleiter.
Alle meine Tipps

Verwandte Ressourcen

Weitere SQL Server DBA Tipps…