0
5. September 2016

Virtualisierter Datenzugriff SQL Server zu Oracle

In meinem letzten Beitrag habe ich über den Nutzen von virtualisierten Datenzugriffen in einem DWH geschrieben, gegenüber von indirekten Datenzugriffen. In diesem Beitrag würde ich gerne ein Beispiel vorstellen, wie ein solch virtualisierter Datenzugriff eingerichtet werden kann. Dazu werden ich einen Linked Server von einer SQL Server Instanz zu einer Oracle Datenbank einrichten.


Voraussetzungen innerhalb einer SQL Server Instanz

Um einen Linked Server innerhalb einer SQL Server Instanz bereit zu stellen, müssen unterschiedliche Voraussetzungen erfüllt werden.

  • Ein installierter Oracle Client auf der selben Maschine, auf der auch die SQL Server Instanz läuft. In den meisten fällen reicht hier der einfache Instant-Client von Oracle. Der Client findet Sie unter dem folgenden Link
  • Die passenden Datenbankprovider auf der selben Maschine, auf der auch die SQL Server Instanz läuft. Die von Oracle bereitgestellten Provider für Windows finden Sie unter dem folgendem Link

In unserem Fall stammt der benötigte Datenbankprovider von Oracle (Oracle OLE DB Provider). Welche Provider auf der SQL-Server Instanz vorhanden sind, findet man im SQL Server Management Studio heraus unter dem Pfad: ./Server Objects/Linked Servers/Providers.

provider
SQL Server Provider

Sollte der Oracle-Provider nicht in dem Verzeichnis aufgeführt sein, muss dieser erst installiert und konfiguriert werden. Sowohl die Installation des Clients und die des OLE DB Providers werden nachfolgend erklärt.

Installation Oracle Instant Client

Um den Oracle Instant Client zu installieren, müssen nach erfolgtem Download die folgenden Schritte durchgeführt werden:

  • Entpacken der Daten in ein beliebiges Verzeichnis. Im Normalfall wählt man dafür <systemlaufwerk>\oracle\products\<InstantClient>
  • Systemvariable %ORACLE_HOME% = <systemlaufwerk>\oracle\products\ anlegen, unter Systemsteuerung/System/Erweiterte Systemeinstellungen/Umgebungsvariablen
  • Systemvariable %ORACLE_CLIENT_PATH% = %ORACLE_HOME%\<InstantClient>  anlegen, unter Systemsteuerung/System/Erweiterte Systemeinstellungen/Umgebungsvariablen
  • Systemvariable %Path% erweitern um %ORACLE_CLIENT_PATH%, unter Systemsteuerung/System/Erweiterte Systemeinstellungen/Umgebungsvariablen
  • Erstellen einer tnsnames.ora-Datei, bzw. von der Systemadministration anfordern. Im Normalfall wird diese unter %ORA_CLIENT_PATH%\network\admin abgelegt. Dieser Schritt ist nicht zwingend notwendig, aber ermöglicht es einen Alias für die Oracle Instanz anzulegen. Im Vergleich zu dem vollständigen Connectionstring von Oracle ist dies eine erhebliche Erleichterung der Schreibweise
  • Systemvariable %TNS_ADMIN% = <tnsname.ora-Datei-Verzeichnis> anlegen unter Systemsteuerung/System/Erweiterte Systemeinstellungen/Umgebungsvariablen

Der Instant Client von Oracle ermöglicht es nun über einen Alias oder einen ausgeschriebenen Connectionstring, mit einer Oracle-Datenbank zu kommunizieren. Ein Vollständiger Oracle Connectionstring, ohne Authentifizierung, sieht wie folgt aus: //server:port/orclservice-name. Im Vergleich dazu kann in der tnsnames.ora z. B. der Alias ORACLE12c gesetzt und verwendet werden.

Installation des Oracle Providers

Um die Oracle OLE DB Provider installieren und nutzen zu können, muss zuvor der Instant Client installiert sein. Die von Oracle bezogene ODAC-Datei liefert diesen mit (s. o.). Defaultmäßig wird dieser bei der Installation der OLE DB-Treiber mitinstalliert. In unserem Fall ist das obsolet. Nach dem Download werden folgende Schritte durchgeführt:

  • Erstellen eines Verzeichnis unter %ORACLE_HOME%, das ist das Verzeichnis zur Installation des Providers
  • Entpacken der ODAC-Daten
  • Windows Eingabeaufforderung öffnen mit Windowstaste + R,  cmd eingeben und Enter drücken
  • Dann in das Verzeichnis wechseln, in dem die entpackten ODAC-Daten liegen
  • Folgenden Befehl ausführen:
    .\install.bat oledb <InstallverzeichnisFürOLEDB> odac64 false

    der Schalter „false“ unterbindet die erneute Installation des Instant Client als dependency
  • Nach erfolgreicher Installation die Systemvariable %ORA_ODAC_PATH% = <ODAC-Installationsverzeichnis> untern Systemsteuerung/System/Erweiterte Systemeinstellungen/Umgebungsvariablen anlegen
  • Systemvariable %Path% erweitern um %ORA_ODAC_PATH%, unter Systemsteuerung/System/Erweiterte Systemeinstellungen/Umgebungsvariablen
  • Systemvariable %Path% erweitern um %ORA_ODAC_PATH%\bin, unter Systemsteuerung/System/Erweiterte Systemeinstellungen/Umgebungsvariablen
  • Dann sollte das Gesamte System neu gestartet werden

Konfiguration des SQL Servers

Nach erfolgtem Neustart kann im SQL Server Management Studio erneut unter dem Pfad: ./Server Objects/Linked Servers/Providers geprüft werden, ob der Provider „OraOLEDB.Oracle“ vorhanden ist.

provider
SQL Server Provider

Ist das der Fall, müssen noch folgende Statements ausgeführt werden:

exec master.dbo.sp_MSset_oledb_prop ‚ORAOLEDB.Oracle‘, N’AllowInProcess‘, 1

exec master.dbo.sp_MSset_oledb_prop ‚ORAOLEDB.Oracle‘, N’DynamicParameters‘, 1

Das erlaubt dem OLE DB-Provider innerhalb des SQL Server Prozesses ausgeführt zu werden und Parameter anzunehmen.

Erstellen eines Linked Server

Da nun alle notwendigen Schritte zur Schaffung der Rahmenbedingungen durchgeführt wurden, kann ein Linked Server angelegt werden. Das erfolgt entweder über die GUI im SQL Server Management Studio (Rechtsklick auf den Ordner „Linked Server“) oder per T-SQL-Statement. Vollständig vorgestellt werden die Methoden von Microsoft unter diesem Link. Das T-SQL Statement zum Anlegen des Linked Servers sieht wie folgt aus.

exec sp_addlinkedserver @server = ‚Name für den Linked Server‘, @srvproduct=’Oracle‘, @provider = ‚OraOLEDB.Oracle‘, @datasrc=’Alias aus tnsnames.ora‘;

Hier hat aber noch keine Konfiguration stattgefunden für den Sicherheitskontextes, oder die Server Optionen. Über die GUI müssen folgende Schritte zum Einrichten durchgeführt werden:

Rechtsklick auf dem Verzeichnis ./Server Objects/Linked Servers und New Linked Server anklicken

rightclick_linked_server
Linked Server in SSMS erstellen

Das Fenster New Linked Server öffnet sich mit unterschiedlichen Konfigurationsseiten. Auf der Seite General werden folgende Felder gefüllt:

  • Linked server: <gewünschter Name des Linked Servers>
  • Server type: In unserem Fall „Other Data Source
  • Provider: Im Dropdown-Menü „Oracle Provider for OLE DB“ auswählen
  • Product name: Hier kommt der Name rein, der Unter Providers sichtbar ist. In diesem Fall lautet der Eintrag  „OracleOLEDB.Oracle
  • Data source: Dieses Feld enthält den Namen der Datenquelle. Da wir eine tnsnames.ora eingerichtet haben, tragen wir hier den Alias des gewünschten Servers ein. In diesem Fall also „ORCL12c
  • Wäre das nicht der Fall, hätten wir noch den vollständigen Oracle-Connectionstring im Feld Provider string eintragen müssen
linked_server_generalpage
Linked Server Propertis General

Sicherheitskontext des Linked Servers

Im linken Bereich des Fensters wählt man nun die Security Page aus, um den Sicherheitskontext für die lokalen SQL Server Logins zu konfigurieren:

  • Im oberen Bereich können die Logins der lokalen Instanz zu den Logins der Remote Instanz gemappt werden. Statt einer expliziten Angabe von Username und Passwort für den Linked Server, kann auch die Option „Impersonate“ gewählt werden. Für diese Option muss auf dem Remoteserver exakt der selbe User mit dem selben Passwort vorliegen
  • Im unteren Bereich kann man festlegen, was mit Benutzern geschieht, die nicht in der oberen Liste gemappt wurden
  • Not be made (aus Sicherheitsgründen unsere Wahl): Es wird für alle Benutzer, für die es kein explizites Mapping gibt, kein Verbindungsversuch durchgeführt
  • Be made without using a security context: Erlaubt es einen Verbindungsversuch ohne Übergabe eines Sicherheitskontextes durchzuführen
  • Be made using the login’s current security context: Wenn es sich um einen lokalen SQL User handelt, werden mit dieser Option beim Verbindungsversuch die Daten des lokalen Logins weitergegeben. Handelt es sich um einen Windows Login, werden dessen Windows Login Daten für den Verbindungsversuch verwendet
  • Be made using this security context: Hierbei muss man einen Benutzer und ein Passwort eines Benutzers eingeben, der auf dem Remoteserver existiert. Dessen Sicherheitskontext auf dem Remoteserver wird für alle User angenommen, die nicht explizit gemappt sind
linked_server_securitypage
Linked Server Propertis Security

Erweiterte Einstellungungen des Linked Servers

Als letztes können noch unterschiedliche Server Options konfiguriert werden. Hier wird nicht auf alle Punkte in den Server Options eingegangen, sondern lediglich auf einige in unserem Fall wichtige. Eine vollständige Liste der Konfigurationsmöglichkeiten findet sich unter diesem Link.

linked_server_serveroptionpage
Linked Server Propertis Server Options
  • Collation Compatible: Wenn diese Option auf true gestellt wird, wird davon ausgegangen dass die Zeichenkettenbehandlung auf beiden Servern identisch ist, im Fall von Oracle zu SQL Server wählen wir „false“
  • Data Access: Erlaubt es auf dem Remote Server distributed Querys auszuführen. Das erlaubt uns die Row Sets aus dem Linked Server, die der OLE DB-Provider liefert, in SQL-Statements zu nutzen. Sie werden genau so behandelt, als wären es lokale SQL Server Tabellen
  • RPC: Erlaubt es dem Remoteserver Remote Procedure Calls auf dem lokalen Server auszuführen
  • RPC OUT: Erlaubt dem lokalen Server Remote Procedure Calls auf dem Remoteserver auszuführen.

Abfragen ausführen

Um Daten von dem Remoteserver abzufragen, kann die Tabelle in einem T-SQL-Statement ganz einfach wie folgt referenziert werden.

query_linked_server_execute
T-SQL Statement Linked Server

Generell gilt der folgende Aufbau für die Benutzung eines Linked Servers in einem T-SQL-Statement: <Linked Server>.<Database>.<Schema>.<Table>. Wichtig ist es dabei zu bedenken, dass es architektonische Unterschiede zwischen der SQL Server Instanz und der Oracle Instanz gibt. Unterhalb einer SQL Server Instanz existieren mehrere Datenbanken. Im Gegensatz dazu ist die Instanz bei Oracle der Datenbank gleichzusetzen. Um das Problem in einem Statement zu umgehen, verzichtet man auf die Angabe von  dem <Database>-Bezeichner, beim Zugriff auf Oracle Datenbanken. Die Abfrage gegen einen Linked Server von Oracle ist im T-SQL also immer mit <Linked Server>..<Schema>.<Table> auszuführen. Angezeigt wird das Ergebnis, als würden wir an eine lokale Datenbanktabelle die Abfrage starten.

query_linked_server_tsql_resultset
Result Set T-SQL Statement SELECT

Als weitere Möglichkeit Statements auf dem Linked Server auszuführen, kann das EXECUTE-Statement verwendet werden. Da das EXECUTE-Statement die Ausführung des Statements dem Remoteserver überlässt und nur das Resultset als Ergebnis liefert, ist es nach Möglichkeit aus Performancegründen vorzuziehen. Der Aufbau des EXECUTE Statements ist im SQL Server für Linked Server: EXECUTE (‚Statement‘) AT <Linked Server>.

query_linked_server_execute
T-SQL Statement Linked Server mit EXECUTE

Auch hier erhalten wir ein Resultset, als würde es sich um eine lokale Tabelle handeln:

query_linked_server_execute_resultset

Dürsin Kurt

Dürsin Kurt

Business Intelligence & Database Consultant

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.