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.
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>oracleproducts<InstantClient>
- Systemvariable %ORACLE_HOME% = <systemlaufwerk>oracleproducts 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%networkadmin 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:
[code lang=“powershell“].install.bat oledb <InstallverzeichnisFürOLEDB> odac64 false[/code]
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.
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
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
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
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.
- 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.
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.
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>.
Auch hier erhalten wir ein Resultset, als würde es sich um eine lokale Tabelle handeln:
