Eine häufige Aktivität beim Schreiben von T-SQL-Abfragen stellt eine Verbindung zu lokalen Datenbanken her und verarbeitet Daten direkt. Es gibt jedoch Situationen, in denen Sie eine Verbindung zu einer Remote-Datenbank herstellen müssen, die sich in einer anderen Instanz auf demselben Server oder auf einem anderen physischen Server befindet, und ihre Daten parallel zur lokalen Datenverarbeitung verarbeiten müssen.,
SQL Server bietet uns vier nützliche Methoden, um eine Verbindung zu den Remote-Datenbankservern, sogar zu anderen Datenbankservertypen, herzustellen und deren Daten in Ihrer T-SQL-Anweisung abzufragen. In diesem Artikel werden diese vier Methoden und deren Verwendung zum Abfragen von Remote-SQL Server-Datenbanken erläutert.
OPENDATASOURCE
Die erste Methode zum Abfragen einer entfernten SQL Server-Datenbank ist die folgende T-SQL-Funktion von OPENDATASOURCE:
OPENDATASOURCE (provider_name als char, init_string)
Wobei der provider_name der OLE DB-Anbieter ist, der für den Zugriff auf die Datenquelle verwendet wird., Und der init_string ist die Verbindungszeichenfolge des Remote-Servers.
Um die OPENDATASOURCE-Anweisung verwenden zu können, müssen Sie sicherstellen, dass der Registrierungsschlüssel DisallowAdhocAccess für den Anbieter, mit dem Sie eine Verbindung herstellen möchten, auf 0 gesetzt ist, der sich im folgenden Pfad der Registrierungsschlüssel befindet :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\<ProviderName>
Außerdem müssen Sie die erweiterte Konfigurationsoption für Ad Hoc Distributed Queries aktivieren, die in SQL Server standardmäßig deaktiviert ist., Wenn Sie versuchen, die folgende einfache Abfrage auszuführen, die die OPENDATASOURCE T-SQL-Anweisung verwendet, wird der Fehler angezeigt:
Msg 15281, Level 16, Status 1, Zeile 1
SQL Server hat den Zugriff auf die ANWEISUNG ‚OpenRowset/OpenDatasource‘ der Komponente ‚Ad Hoc Distributed Queries‘ blockiert, da diese Komponente als Teil der Sicherheitskonfiguration für diesen Server deaktiviert ist. Ein Systemadministrator kann mithilfe von sp_configure die Verwendung von „Ad Hoc Distributed Queries“ aktivieren., Weitere Informationen zum aktivieren von ‚Ad Hoc Distributed Queries‘, suchen ‚Ad Hoc Distributed Queries‘ in der SQL Server-Onlinedokumentation.
Wie Sie der Fehlermeldung entnehmen können, sollte die erweiterte Konfigurationsoption für verteilte Ad-hoc-Abfragen aktiviert sein, um die Verbindung zu einem Remote-Server mithilfe der OPENDATASOURCE zu öffnen.,
|
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 kann den Servernamen im vierteiligen Namen der Tabelle oder Ansicht in einer SELECT -, INSERT -, UPDATE-oder DELETE-Anweisung ersetzen. Es kann auch in der EXECUTE-Anweisung verwendet werden, um eine remote gespeicherte Prozedur auszuführen.
OPENROWSET
Die zweite Möglichkeit, eine Datenbank abzufragen, die auf einem Remote-SQL-Server gehostet wird, ist die T-SQL-Funktion OPENROWSET. Um die Ad-hoc-Methode OPENROWSET verwenden zu können, müssen Sie alle Verbindungsinformationen bereitstellen, die für die Verbindung mit dem Remote-SQL-Server und vielen anderen Ressourcen erforderlich sind., Es kann auch für einen Massenvorgang über den integrierten Massenanbieter verwendet werden, um Daten aus Dateien zu lesen. OPENROWSET wird in der FROM-Klausel als Tabellenname in den Anweisungen SELECT, INSERT, UPDATE oder DELETE verwendet. Obwohl die Abfrage möglicherweise mehrere Ergebnismengen zurückgibt, gibt OPENROWSET nur die erste zurück.
Die Verwendung von OPENROWSET erfordert die Aktivierung der erweiterten Konfigurationsoption für Ad Hoc Distributed Queries wie die OPENDATASOURCE-Funktion.,979d8a2e“>
Sie können die vorherige OPENDATASOURCE-Abfrage mit der OPENROWSET-Funktion wie folgt schreiben:
Linked Server
Ein SQL Server Linked Server wird verwendet, um auf entfernte OLE DB-Datenquellen zuzugreifen, z. B. auf eine SQL Server-Instanz, die sich außerhalb des SQL Servers oder anderer Systeme wie Oracle, Microsoft Access und Excel befindet, und die verteilten T-SQL-Abfragen für sie auszuführen.,
Ein mit SQL Server verknüpfter Server unterscheidet sich von Ad-hoc-Abfragen dadurch, dass Ad-hoc-Abfragen eine temporäre Verbindung mit dem Remote-Server öffnen und schließen, wobei der permanent verknüpfte Server immer zur Verwendung verfügbar ist. Wenn der Benutzer eine verteilte Abfrage für eine entfernte Datenquelle mithilfe eines verknüpften Servers ausführt, analysiert die SQL Server-Engine diesen Befehl und sendet die Anforderungen an OLE DB. Diese Anforderung kann eine auszuführende Abfrage oder eine Tabelle sein, die auf diesem Remote-Server geöffnet werden soll.,
Ein verknüpfter Server kann mithilfe von SQL Server Management Studio oder mithilfe der T-SQL-Anweisung sp_addlinkedserver konfiguriert werden.
Um einen verknüpften Server mit SQL Server Management Studio zu konfigurieren, erweitern Sie den Knoten Serverobjekte im Fenster Objektexplorer. Klicken Sie mit der rechten Maustaste auf den Knoten Linked Server und wählen Sie New Linked Server.
Wählen Sie auf der Registerkarte Allgemein des Fensters Neuer verknüpfter Server einen Namen für Ihren verknüpften Server und dann den Servertyp aus, mit dem Sie über diesen verknüpften Server eine Verbindung herstellen möchten., Wählen Sie SQL Server aus, wenn Sie eine Verbindung zu einer entfernten SQL Server-Instanz herstellen möchten, oder wählen Sie Eine andere Datenquelle aus, um aus den verfügbaren OLE DB-Servertypen aus der Dropdown-Liste Anbieter als SQL Server auszuwählen. Wenn Sie SQL Server als Servertyp auswählen, sollte der Name des verknüpften Servers der Netzwerkname des entfernten SQL-Servers sein.
Füllen Sie das Feld Produktname mit dem Produktnamen der ausgewählten OLE DB-Datenquelle, z. B. SQL Server, wenn Sie eine Verbindung zu einem entfernten SQL Server herstellen., Geben Sie den Namen der ausgewählten Datenquelle in das Feld Datenquelle ein, z. B. den Namen der SQL Server-Instanz, wenn Sie eine Verbindung zu einer entfernten SQL Server-Instanz herstellen. Füllen Sie das Feld Providerzeichenfolge mit der OLE DB providerspezifischen Verbindungszeichenfolge aus, die eine eindeutige Datenquelle identifiziert. Das Feld Speicherort kann mit dem Remote-Datenbankspeicherort gefüllt werden, der vom OLE-DB-Anbieter interpretiert wird. Geben Sie im Feld Katalog den Namen des Katalogs ein, der vom OLE DB-Anbieter verwendet werden soll.,
Sie müssen nicht alle beschriebenen Argumente zusammen verwenden, da die erforderlichen Argumente vom ausgewählten Anbieter abhängen. Wenn Sie beispielsweise den SQL Server-Anbieter verwenden, verwenden Sie nur zwei Argumente.
Geben Sie auf der Sicherheitsseite des Fensters Neuer verknüpfter Server den Sicherheitskontext an, den der verknüpfte Server zum Verbinden des ursprünglichen SQL-Servers mit der entfernten Datenquelle verwendet., Wie der Name erläutert, können Sie im Teil „Lokale Serveranmeldungen an Remoteserveranmeldungen zuordnen“ des Sicherheitsfensters eine Liste von Benutzern angeben, die mit dem verknüpften Server eine Verbindung zum Remoteserver herstellen können, indem Sie diese lokalen Benutzer mit Remoteserveranmeldungen zuordnen. Diese Benutzer können über SQL Server-Authentifizierung oder eine Windows-Authentifizierung anmelden.
Für die Anmeldungen, die nicht in der Zuordnungsliste definiert wurden, müssen Sie den Sicherheitskontext für ihre Verbindungen zum Remote-Server über diesen verknüpften Server auswählen., Wenn Sie Nicht erstellt werden, wird verhindert, dass Benutzer, die nicht in der vorherigen Liste enthalten sind, diesen verknüpften Server verwenden. Um ohne Verwendung eines Sicherheitskontexts ausgeführt zu werden, stellen die Benutzer, die nicht in der vorherigen Zuordnungsliste enthalten sind, über diesen verknüpften Server eine Verbindung zum Remote-Server her, ohne einen Sicherheitskontext für sie anzugeben. Wenn Sie den aktuellen Sicherheitskontext der Anmeldung verwenden, wird die Verbindung zum Remote-Server mithilfe des verbindenden Benutzers hergestellt, was die beste Wahl ist., Geben Sie in der Option Mit diesem Sicherheitskontext erstellt werden die SQL Server-Authentifizierungsanmeldeinformationen an, die zum Herstellen einer Verbindung zum Remoteserver für die Benutzer verwendet werden, die nicht in der Zuordnungsliste definiert sind.
Es gibt viele Optionen, die Sie je nach Ihren Anforderungen auf der Seite Serveroptionen des neuen Fensters Verknüpfter Server anpassen können. Setzen Sie beispielsweise die Option Collation Compatible auf true, wenn Sie sicher sind, dass die entfernte Datenquelle denselben Zeichensatz und dieselbe Sortierreihenfolge wie der lokale Server hat., Standardmäßig wertet SQL Server Vergleiche für Zeichenspalten lokal aus, wenn Sie diese Option nicht verwenden. Die Option Datenzugriff wird verwendet, um den verteilten Abfragezugriff für den verknüpften Server zu aktivieren und zu deaktivieren. Um den Remoteprozeduraufruf vom angegebenen Server zu aktivieren, setzen Sie die RPC auf true und um den Remoteprozeduraufruf an den angegebenen Server zu aktivieren, setzen Sie die RPC auf true.
Die Entscheidung, ob die Sortierung des lokalen oder Remote-Servers in der Abfrage verwendet wird, wird durch die Option Remote-Sortierung verwenden bestimmt., Wenn der Wert dieser Option wahr ist, können Sie den Sortiernamen angeben, der vom Remoteserver in der Option Sortiername verwendet wird. Dies gilt, wenn die Remote-Datenquelle kein SQL Server ist, in dem Sie jeden von SQL Server unterstützten Sortiernamen in diesem Feld angeben können.
Sie können das Zeitlimit für die Remote-Anmeldung des Standardservers für diesen verknüpften Server überschreiben, indem Sie die Option Verbindungszeitlimit auf einen beliebigen Wert größer als 0 ändern.Dieser Wert gibt die Zeitüberschreitung in Sekunden für die Verbindung mit dem verknüpften Server an., Sie können auch die Zeitüberschreitung in Sekunden für die Abfragen angeben, die eine Verbindung zum verknüpften Server herstellen, indem Sie die Option Abfragezeitüberschreitung auf einen beliebigen Wert größer als 0 ändern. Dadurch wird das Zeitlimit für die Remoteabfrage des Servers für den verknüpften Server außer Kraft gesetzt.
Eine weitere nützliche Option, mit der eine verteilte Transaktion beim Aufruf einer Remote Stored Procedure gestartet wird, in der diese Transaktion vom MS DTC verwaltet und geschützt wird. Diese Option wird als Förderung verteilter Transaktionen aktivieren bezeichnet.,die folgende Meldung, andernfalls wird eine Fehlermeldung angezeigt, die anzeigt, dass etwas das Öffnen der Verbindung verhindert:
Sie können den vorherigen verknüpften Server einfach mit der T-SQL-Anweisung sp_addlinkedserver erstellen, die die erforderlichen Argumente wie folgt übergibt:
|
1
2
3
4
|
USE
GO
EXEC master.,dbo.sp_addlinkedserver @server = N ‚DEV_SQL‘, @srvproduct=N ‚SQL Server‘
GO
|
Sobald der verknüpfte Server erfolgreich erstellt wurde, können Sie ihn verwenden, indem Sie den vierteiligen Namen angeben, der Folgendes enthält: Linked_Server_Name.Remote_Database_Name.Schema_Name.Table_Name, wie im folgenden Beispiel:
|
1
2
|
SELECT * FROM DEV_SQL.,testdb.dbo.Profil
GO
|
OPENQUERY
Die letzte SQL Server-Methode, mit der eine Verbindung zu einer entfernten Datenquelle hergestellt wird, ist die OPENQUERY-Funktion. Es ist eine alternative einmalige Ad-hoc-Methode, um über den verknüpften Server eine Verbindung zu einem Remote-Server herzustellen. Für häufigere Verbindungen zum Remote-Server ist es besser, den verknüpften Server anstelle der OPENQUERY-Funktion zu verwenden.
Die OPENQUERY-Funktion kann in der FROM-Klausel der SELECT -, INSERT -, UPDATE-oder DELETE-Anweisung verwendet werden, die den Tabellennamen ersetzt., Es werden zwei Argumente benötigt; der verknüpfte Servername und die Abfrage. Diese Parameter können nicht werden Variablen wie folgt an:
OPENQUERY ( linked_server ,’query‘ )
Nachfolgend finden Sie ein einfaches Beispiel für die Verwendung von OPENQUERY:
|
1
|
WÄHLEN Sie * AUS OPENQUERY(DEV_SQL,’SELECT * FROM testdb.dbo.,Profil‘)
|
Vergleich
Wenn der verknüpfte Server zum Abfragen eines Remote-Servers verwendet wird, erstellt der Abfrageoptimierer den Ausführungsplan, nachdem die Abfrage klassifiziert und in lokale und Remote-Abfragen unterteilt wurde, wobei die lokalen Abfragen lokal ausgeführt und die Remote-Abfragen an den Remote-Server gesendet und dann kombiniert werden, um dem Benutzer das Endergebnis als einzelne Ergebnismenge anzuzeigen., Ein weiterer Nachteil des verknüpften Servers besteht darin, dass auf dem Remoteserver keine Filterung angewendet wird, wenn die Abfrage über die WHERE Klausel verfügt, in der alle Datensätze aus der Remotetabelle abgerufen und die Filterung und der Beitritt lokal durchgeführt werden.
Im Fall der OpenQuery-Funktion versucht die SQL-Engine nicht, die Abfrage zu klassifizieren oder zu überprüfen, was sie tun soll, sondern sendet die Abfrage unverändert an den Remote-Server. Das Parsen der SQL-Abfrage, das Generieren des Ausführungsplans und die gesamte Filterung werden auf dem Remote-Server durchgeführt.,
Im Allgemeinen ist OpenQuery schneller als der verknüpfte Server, da die SQL-Engine die Abfrage nicht unterbricht, bevor sie an den Remote-Server gesendet wird, sondern nur für einmalige, weniger häufige Remote-Verbindungen nützlich ist.
Mit den Funktionen OPENROWSET und OPENDATASOURCE müssen Sie bei jeder Verwendung alle Verbindungsdetails einschließlich Benutzername und Kennwort angeben. Obwohl diese Funktionen nicht alle verknüpften Serverfunktionen wie das Sicherheitsmanagement bereitstellen, werden weniger Ressourcen von Ihrem Server verbraucht., Da diese Funktionen eine einmalige Verbindung zum Remote-Server herstellen, ist es besser, den verknüpften Server für den häufigen Remote-Serverzugriff zu verwenden.
- Autor
- Recent Posts
Er ist ein Microsoft Certified Solution Expert in Data Management und Analytics, Microsoft Certified Solution Associate in SQL-Datenbankverwaltung und-entwicklung, Azure Developer Associate und Microsoft Certified Trainer.
Außerdem trägt er mit seinen SQL-Tipps in vielen Blogs bei.,
Alle Beiträge von Ahmad Yaseen anzeigen
- So überwachen Sie Azure Data Factory – 15. 8. Januar 2021