Oracle 10g Und MS SQL Server 2005 - Ein Vergleich

download Oracle 10g Und MS SQL Server 2005 - Ein Vergleich

of 22

Transcript of Oracle 10g Und MS SQL Server 2005 - Ein Vergleich

Datenbanken IIAusarbeitung zum Thema: Oracle 10g und MS SQL Server 2005 ein Vergleich

Thomas Wchtler MatrNr.: 39221

Thomas Wchtler

Matrikelnummer: 39221

Inhaltsverzeichnis

0. 1. 2. 2.1 2.2 2.3 3. 3.1 3.2 3.3 3.4 3.5 3.6 4. 4.1 4.2 4.3 4.4 4.5 5. 5.1 5.2 5.3 5.4 5.5 5.6 5.7 6.

Einfhrung Komponenten Architektur des SQL Server 2005 SQLOS Relational Engine Protocol Layer Werkzeuge des SQL Server 2005 Object Explorer Profiler Tuning Anvisor Activity Monitor Configuration Manager SQLCMD Services des SQL Server 2005 Replikation Reporting Analysis Notification Integration Vergleich SQL Server 2005 und Oracle 10g Daten Verwaltung Indexierung Concurrency Control und Locking Backup und Recovery Datenbank Tuning Partitionierung Clustering Quellen

Seite 1

Thomas Wchtler

Matrikelnummer: 39221

0. EinfhrungMicrosoft SQL Server 2005 ist ein RDBMS (relationales Datenbank Management System) welches das alte SQL Server 2000 nach 5 Jahren Entwicklungszeit abgelst hat. Wie Oracle mit PL/SQL hat auch Microsoft zusammen mit Cybase den SQL Standard mit der Sprache Transact-SQL (T-SQL) um Variablen, Funktionen, Prozeduren und Objekt Relationale Erweiterungen ausgebaut. Mit einer maximalen Speicherkapazitt von einem ExaByte bietet der SQL Server 2005 die drittgrte mgliche relationale Datenbank an, die nur noch von PostgreSQL und Oracle bertroffen wird. Der SQL Server 2005 ist ausschlielich fr Windows-Plattformen entwickelt worden und bietet die Windows-typische grafische Benutzeroberflche. Die mit der Version 2005 eingefhrte enge Verknpfung mit dem .NET Framework ermglicht es Entwicklern einfacher ber die integrierte API auf die Datenbank zuzugreifen und .NET Code direkt in der Datenbank Engine auszufhren. Der SQL Server ist von seiner Konzeption her ein direkter Konkurrent von Oracle und DB2 von IBM. Im Folgenden soll ein Einblick in den Aufbau, die Komponenten und Services des SQL Server 2005 gegeben werden. Weiterhin soll ein Vergleich zu Oracle 10g im Bereich der technischen Aspekte angestellt werden, der jedoch nicht auf Vorteile bzw. Nachteile hinweisen soll, sondern die Unterschiede beider DBMS aufzeigen soll.

1. KomponentenDer SQL Server 2005 beinhaltet neben der fr ein DBMS wichtigsten Komponente, der Datenbank Engine, viele weitere Komponenten und Services. Datenbank-Engine: Die Engine ist das Kern Element des gesamten Pakets und beinhaltet Services zur Speicherung, Verarbeitung und Sicherung der Daten. Sie ist fr den kontrollierten Zugriff auf die Daten, sowie fr eine schnelle Verarbeitung von Anfragen und Transaktionen zustndig. Integration Services: Der Service wird verwendet um Daten aus verschiedenen Datenquellen

Seite 2

Thomas Wchtler

Matrikelnummer: 39221

zusammenzufhren und bilden somit die Grundlage fr den Bereich Business-Intelligence, bei dem das Datawarehousing eine wichtige Rolle spielt. Hier werden ETL-Prozesse genutzt um Daten zu extrahieren, zu transformieren und die so bearbeiteten Daten in eine andere Datenquelle zu laden. Analytical Services: Sie sind fr analytische Verarbeitung der durch die Integration Services bereitgestellten Daten zustndig. Es werden verschiedene Services fr OLAP und Data Mining angeboten. Es werden MOLAP, ROLAP, HOLAP durch die OLAP-Engine untersttzt. Fr Data Mining wird die DMX query language genutzt. Reporting Services: Eine Umgebung in der tabellarische, grafische oder formfreie Reports generiert werden, die klassisch oder per Web-Interface ausgewertet werden knnen. Hierzu werden die Integration Services genutzt, was ermglicht, auch nicht persistente Datenquellen fr Reports zu verwenden. Notification Services: Ein Service der Benachrichtigungen sendet, wenn ein bestimmtes Ereignis in der Datenbank eintritt. Dieser Service registriert sich als Trigger auf dem Datenbank-Server und versendet beim eintreten eines zuvor festgelegten Events eine Nachricht ber den Service Broker, der die unterschiedlichen Komponenten zusammen synchronisiert, auf Basis des Nachrichtenaustauschs. Wird z.B. fr die schnelle bermittlung von Daten, die von Analyse Diensten erstellt wurden, verwendet. Replication Services: Wird zur Wiederherstellung und Synchronisierung der Daten in der Datenbank genutzt. Funktioniert wie eine Art Client/Server. Der DB-Server sendet eine Nachricht an alle Clients, diese wiederum aktualisieren ihren Datenbestand. .NET Framework: umfasst Klassenbibliotheken (API's), Dienste, und eine Laufzeitumgebung. Es ermglicht die Ausfhrung von Programmen, die speziell fr das Framework programmiert wurden. Es erleichtert durch vorgegebene Standards, die Kommunikation zwischen verschiedenen Microsoft Diensten, wie dem SQL Server und z.B. dem Visual Studio, indem es

Seite 3

Thomas Wchtler

Matrikelnummer: 39221

Schnittstellen bereitstellt, die von beiden genutzt werden knnen. SQL-Server Management Studio: ist eine Umgebung in der die Komponenten des SQL Server verwaltet, konfiguriert und entwickelt werden. Ermglicht den Zugriff auf den SQL Server ber grafische Tools und Editoren. Weiterhin gibt es Werkzeuge fr Monitoring, Management, Backup und Restore.

2. Architektur des SQL Server 2005Der Microsoft SQL Server 2005 ist in 3 Hauptbestandteile aufgeteilt: SQLOS beinhaltet die Grundlegenden Dienste, die zur Ausfhrung des SQL Server notwendig sind. Es beinhaltet die Verwaltung von Threads und Speicher, sowie die Ein- und Ausgabeoperationen. Die Ralational Engine implementiert die relationalen Datenbankkomponenten, wie Tabellen, Anfgragen und Stored Procedures und das Typsystem mit dem die Datenbanken arbeiten. Der Protocol Layer ist die Schnittstelle zu anderen Anwendungen, auerhalb des SQL Server.

2.1 SQLOSSQLOS implementiert Funktionen, die sonst vom Betriebssystem bernommen werden, wie Ablaufplanung von Threads, Speicherverwaltung, Resourcenmanagement, Fehlerbehandlung und Deadlock Erkennung. Da eine Datenbank anders als ein Filesystem, wie es das Betriebssystem zur Verfgung stellt, auf die schnelle und effektive Verarbeitung von Daten spezialisiert ist und auch viel mehr Daten zu verwalten hat, wurde eine eigene Speicherverwaltung fr den SQL Server programmiert. Auch weitere bereits erwhnte Bestandteile mussten dafr angepasst werden. SQLOS beinhaltet eine API, die es Programmierern ermglicht, auf die im System vorhandene Hardware zuzugreifen, ohne dabei die technische Komplexitt der Hardware verstehen zu mssen. Die Hauptbestandteile des SQLOS sind Nodes, Schedulers und Tasks. Die oberste Ebene bilden die Memory Nodes, denen die CPU Nodes untergeordnet sind. Jeder CPU Node beinhaltet mehrere Scheduler, der wiederum die einzelnen Tasks ordnet um sie dann auf der CPU auszufhren. SQLOS bietet somit Support fr Programmierung paralleler Prozesse. Seite 4

Thomas Wchtler

Matrikelnummer: 39221

2.2 Relational EngineDie relationale Engine implemtiert die Logik fr die relationale Datenbank auf Basis der durch SQLOS bereitgestellten API. Hier werden die Datentypen definiert, die dann die Objekte charakterisieren, die in Tabellen gespeichert werden. Weiterhin werden hier Objekte wie Tabellen, Indizes, Logs und andere definiert, die gespeichert werden knnen. Sie beinhaltet eine Storage Engine, die die Speicherung von Daten auf persistenten Datentrgern und den schnellen Zugriff auf diese Daten verwaltet. Diese Engine implementiert Log- basierte Transaktionen, sodass alle nderungen dem ACID Prinzip gengen. Sie beinhaltet weiterhin den Query- Prozessor, der die gespeicherten Daten abruft. Die SQL Anfrage definiert, welche Daten verndert oder gelesen werden sollen, der Query- Prozessor optimiert und bersetzt die Anfrage, sodass SQLOS sie versteht und teilt sie in Sequenzen auf, die beschreiben, welche Operationen nacheinander durchgefhrt werden mssen um die Daten letztendlich zu ndern oder zu erhalten.

2.3 Protocol LayerDer Protocol Layer implementiert das externe Interface zum SQL Server. Alle Operationen, die auf dem SQL Server aufgerufen werden knnen, werden mittels eines von Microsoft definierten Formats kommuniziert, welches sich Tabular Data Stream (TDS) nennt. TDS ist ein Protokoll, das verwendet wird um den Transfer der Daten zwischen Datenbank-Server und Client durchzufhren. TDS kann in andere Kommunikationsprotokolle wie TCP/IP oder Pipes eingebaut werden.

3. Werkzeuge des SQL Server 2005 3.1 Object ExplorerDer Objekt Explorer ist das zentrale Element zur Administration des SQL Server. Er bietet eine grafische Benutzeroberflche, in der alle auf dem Server vorhandenen Datenbanken und Objekte Seite 5

Thomas Wchtler

Matrikelnummer: 39221

(Tabellen + Spalten, Relationen, Indizes, Views, Stored Procedures und Trigger) angezeigt und verwaltet werden knnen. Hier kann man sich zu beliebigen Diensten verbinden und alle administrativen Ttigkeiten ausfhren. Der Object Explorer verbindet sich zu Datenbank Instanzen, Analyse-, Integrations-, Reporting- Diensten.

3.2 ProfilerDer Profiler dient zur Analyse von Aktivitten, wie SQL Operationen, und gibt Ausfhrzeiten oder I/O-Lasten an. Er berwacht eine Instanz des SQL Server oder einen Analyse Service. Die Ergebnisse der Analyse knnen in einer Tabelle oder in einer Datei gespeichert werden, diese nennt man auch Ablaufverfolgungsdatei. Man kann so z.B. selbst programmierte Prozeduren berwachen, deren Ausfhrungszeiten analysieren und ggf. die Prozedur weiter optimieren. Probleme, die zu Fehlern fhren knnen so ebenfalls analysiert werden. Vom Grundkonzept Aufgaben:

her

hnlich

in

der

Funktionalitt

wie

Profiler

in

der

Anwendungsprogrammierung, wie dem Java Profiler. Schrittweises Untersuchen problematischer Abfragen Untersuchen langsamer Abfragen Erfassen von problembehafteten T-SQL Abfragen (Replikation auf einem Testserver) berwachung der Auslastung des SQL-Server (zur Optimierung)

3.3 Tuning AdvisorDer Database Engine Tuning Advisor (DTA) dient der Analyse von SQL-Abfragen. Dies dient dem aufspren sehr lang dauernder Transaktionen, fr die der Advisor auch Verbesserungsvorschlge generieren kann. Dies umfasst sowohl Vorschlge fr die strukturelle Anordnung der Daten und die Verteilung auf dem Speichermedium, als auch Vorschlge fr mgliche Indizes auf bestimmten Spalten. Nach der Auswertung stellt der DTA ein Script fr sinnvolle nderungen und somit zur Optimierung der Datenbank zu Verfgung. Der DB-Administrator kann dann bequem die verschiedenen Vorschlge miteinander vergleichen und nderungen an der Struktur der Daten vornehmen. Funktionsumfang: Seite 6

Thomas Wchtler

Matrikelnummer: 39221

Empfehlen von Indizes auf bestimmten Spalten, Partitionen nach Arbeitsauslastung und indizierten Sichten Analysieren der Auswirkungen von vorgeschlagenen nderungen gezielte Optimierung auf bestimmte Gruppen von kritischen Anfragen Bercksichtigen von Alternativen Vorschlge zur Anpassung

3.4 Activity Monitorder Activity Monitor ermglicht die berwachung aller Usersessions und deren Connections zur Datenbank Engine. Hier knnen vergebene Sperren und somit auch Dead-Locks analysiert werden um diese dann mit Hilfe anderer Werkzeuge zu beheben. Es knnen sowohl alle aktuellen Sperren der aktuellen Session, als auch Sperren auf bestimmten Objekten angezeigt werden.

3.5 Configuration ManagerMit dem Configuration Manager lassen sich die verschiedenen bereits erwhnten Dienste verwalten. Auerdem knnen hier die Netzwerkprotokolle konfiguriert werden, ber die der SQL Server mit anderen Clients kommuniziert. Mit diesem Tool lassen sich die Dienst starten, anhalten, fortsetzen und beenden, oder Eigenschaften eines einzelnen Dienstes gendert werden. Es lassen sich im Bereich Protokollverwaltung die Server und Client-Protokolle bearbeiten und auch Verschlsselung erzwingen. Vom SQL Server untersttzte Protokolle zur bertragung sind: Shared Memory, TCP/IP, Named Pipes und VIA.

3.6 SQLCMDDies ist ein Werkzeug mit dem interaktiv T-SQL Kommandos ausfhren kann. Es ist mit der MySQL Konsole oder iSQL Plus von Oracle vergleichbar. Der Verbindung ber den Kommandozeileneditor SQLCMD wird eine hhere Prioritt in der Hierarchie der aktuellen Verbindungen gegeben, als einer regulren Benutzer- oder Applikationsverknpfung. Die Ressourcen fr diesen Prozess werden schon beim Start des SQL Server reserviert. Zweck

Seite 7

Thomas Wchtler

Matrikelnummer: 39221

dieser Anwendung ist, das trotz voller Beanspruchung aller Ressourcen der Administrator die Mglichkeit hat, im Fehlerfall notwendige Eingriffsmglichkeiten zu haben um. Dieses Tool ist also ausschlielich fr administrative Zwecke gedacht.

4. Services des SQL Server 2005Die Services des SQL Server 2005 verstehen sich als so genannte Add-ons. Sie sind fr den Betrieb nicht notwendig, liefern aber sinnvolle Funktionalitten. Sie laufen teilweise innerhalb des SQL Server, als auch als eigenstndige Windows-Prozesse mit eigener API. Die Grundideen der einzelnen Services sind unter dem 1. Komponenten zusammengefasst. Hier soll nochmal etwas detaillierter auf die einzelnen Services eingegangen werden.

4.1 ReplikationReplikationen sind Technologien zum Kopieren und Verteilen von Daten und zur anschlieenden Synchronisierung der gesendeten/empfangenen Daten mit dem eigenen Datenbestand. Der SQL Server untersttzt die folgenden 3 Arten der Replikation: Transaction Replication: Jede bei der Master Datenbank eingegangene nderung des Datenbestandes wird mit allen aktuell mit der DB verbundenen Clients synchronisiert Merge Replication: nderungen sowohl bei der Master- Datenbank als auch bei den Clients werden verfolgt und periodisch bidirektional kommuniziert und dann in den jeweiligen Server/Clients zusammengefgt. Wenn mehrere nderungen auf einem bestimmten Objekt gettigt haben, entsteht ein Konflikt, der gelst werden muss. Dies geschieht entweder ber vordefinierte Regeln oder Manuell. Snapshot Replication: Die Snapshot Replication erzeugt eine komplette Kopie der gesamten Datenbank (Snapshot als aktuelle Ansicht auf die gesamte Datenbank in diesem Moment) und versendet diese an alle Clients, sodass diese den aktuellen Stand der Master Datenbank haben. Seite 8

Thomas Wchtler

Matrikelnummer: 39221

4.2 ReportingSQL Server Reporting Services (SSRS) ist dazu gedacht Reports aus verschiedenen Datenquellen zu generieren. Dabei gibt es mehrere Mglichkeiten innerhalb der ReportErstellung:

relationale, multidimensionale und XML- Datenquellen. Es knnen alle mglichen multidimensionalen aus Datenbanken oder durch den Analyse Service erstellte Daten verwendet werden. ber ODBC kann auch auf Datenbanken anderer Anbieter zugegriffen werden. Layoutmglichkeiten. tabellarisch - fr spaltenbasierte Auswertungen, matix - fr zusammengefasste Daten, diagramm - fr grafische Auswertungen, formlos - fr alles andere, Kombination mehrerer Layouts in einem Bericht Ad-Hoc Berichte aus dem Berichtsgenerator Drillthroughberichte und Interaktivitt. Hyperlinks und Skriptausdrcke knnen nach belieben eingefgt werden Parametrisierte Berichte. Parameter um DataSets zu filtern Aggregationen. Zusammenfassungen von Daten mittels Summe, Mittelwert...

um dies alles zu ermglichen wird fr die Reports eine spezielle Mark-Up Sprache namens RDL definiert. Somit knnen die Berichte auch in verschiedensten anderen Formaten exportiert werden (pdf, xml). User knnen direkt mit dem Report Manager kommunizieren, oder ber eine vordefinierte Web-Schnittstelle darauf zugreifen.

4.3 AnalysisSQL Server Analsysis Services (SSAS) stellt OLAP und Data-Mining Funktionalitt zur Verfgung. Es werden sowohl Server- als auch Clientkomponenten zum Bereitstellen der OLAP Funktionen verwendet. Zur Architektur:

Die Serverkomponente ist als Windows-Dienst implementiert Die Clients kommunizieren mittels eines SOAP-basierten Protokolls (XMLA) mit dem Service Abfragen werden mittels SQL oder MDX formuliert Seite 9

Thomas Wchtler

Matrikelnummer: 39221

Grundkonzept ist UDM, das es dem Entwickler erlaubt ein universelles Modell fr alle physikalischen Datenquellen zu erstellen. Es werden verschiedene Algorithmen fr das Data Mining bereitgestellt, welche nach bestimmten Mustern oder Trends suchen knnen. Es gibt 2 verschiedene Service-Objekte:

Datenbanken mit OLAP- und Data Mining Objekten, Cubes, Dimensionen, Attributen... Assemblys, die benutzerdefinierte Funktionen enthalten, die die Standardsprachen MDX und DMX systemintern erweitern

4.4 NotificationNotfication Services bieten Abonnenten Abonnements fr bestimmte Benachrichtigungsanwendungen, d.h. ein Interesse an bestimmten Ereignissen innerhalb der Datenbank. Ein Ereignis kann nach einem vordefinierten Zeitplan bermittelt werden, oder sofort bei eintreten. Dabei knnen diese Nachrichten an verschiedene Gerte, wie Mobiltelefone versendet werden. Der Notification Service selbst ist eine Programmierumgebung in dem Anwendungen geschrieben werden knnen, die solche Nachrichten versenden. Die Anwendung muss nach der Programmierung auf der Notification Plattform bereitgestellt werden.

4.5 IntegrationSQL Server Integration Services (SSIS) ist eine Plattform zur Erstellung von Datenintegrationslsungen, die aus mehreren Datenquellen Daten Extrahieren, diese auf ein einheitliches Format transformieren und anschlieend in eine neue Datenquelle zusammengefhrt laden (ETL). Der SSIS besteht aus 4 Komponenten:

Dienst: Verwaltet die Speicherung und berwacht die Ausfhrung von SSIS Paketen Objektmodell: Verwaltet API fr Schnittstelle zum Zugriff auf SSIS-Tools Laufzeit: Speichert Layout von Paketen und untersttzt die Protokollierung, Konfiguration, Verbindungen und Transaktionen Datenfluss: stellt Puffer im Arbeitsspeicher bereit mit denen Daten von der Quelle Seite 10

Thomas Wchtler

Matrikelnummer: 39221

zum Ziel verschoben werden, es verwaltet die Transformationen auf den Daten

5. Vergleich SQL Server 2005 und Oracle 10gMicrosoft bietet mit dem SQL Server 2005 ein im Umfang und der Funktionalitt hnliches Produkt wie Oracle mit der aktuellen Version 10g. In diesem Kapitel sollen beide DBMS miteinander verglichen werden. Dieser Vergleich soll keinen Sieger kren, sondern die Unterschiede und Gemeinsamkeiten der Beiden Produkte erlutern.

5.1 Daten VerwaltungOracle 10g: Das Oracle DBMS speichert Daten auf 2 verschiedenen Ebenen. Die erste ist die logische Ebene, wo die Daten in so genannten Tablespaces gespeichert werden und der physischen Ebene, wo die Daten in Dateien organisiert sind. Die logische Ebene besteht aus einem oder mehreren Tablespaces. In der logischen Ebene werden die Benutzerdaten im SYSTEM Tablespace gespeichert. Ein Benutzer kann auf dem USERS Tabelspace mehrere Tabellen, Stored Procedures, Sequencen etc. haben. Jeder Benutzer ist als Schema in der Datenbank gespeichert und kann auch nur auf Tabellen innerhalb zugreifen, die fr sein Schema freigegeben sind. Diese Zugriffsrechte sind im Data Dictionary, das sich ebenfalls im SYSTEM Tabelspace befindet, definiert. In der physischen Ebene gibt 3 Stufen, in denen die Daten organisiert sind. Die kleinste Stufe sind die Daten Blcke (auch ORACLE Block oder Seite). Diese Blcke knnen Index Blcke und auch Datenblcke sein. Oracle allokiert Speicherplatz in Form solcher Blcke. Die nchst hhere Stufe sind die Extends. Ein Extend ist eine bestimmte Anzahl an zusammenhngenden Daten Blcken. Ein Extend wird allokiert um Informationen eines bestimmten Typs zu speichern. Die hchste Stufe sind die Segmente. In einem Segment sind mehrere Extends vorhanden, die zu einem Tabelspace gehren. Wenn ein Extend im Segment voll ist, allokiert das Segment einen weiteren Extend hinzu, diese werden also nur bei Bedarf angehngt. Ein Datenblock ist somit die kleinste Einheit im System und ist so auf einem physischen Datentrger gespeichert. ber den Header des Blocks kann nun auf diesen zugegriffen werden, wenn er von der Platte gelesen werden soll. Seite 11

Thomas Wchtler SQL Server:

Matrikelnummer: 39221

Einen Tablespace wie bei Oracle gibt es beim SQL Server nicht. Hier werden die Daten in separaten Datenbanken gespeichert. Wobei jede Datenbank von der anderen sowohl logisch als auch physisch unabhngig ist. In einer Datenbank sind jeweils deren Tabellen, Sequenzen, Trigger, Stored Procedures etc. gespeichert. Sie bildet nach auen hin ein geschlossenes Gebilde, sowohl physisch als auch logisch. Bei Oracle ist der USER Tablespace fr alle Tabellen zustndig. Dadurch sind unterschiedliche Schemata indirekt durch die Speicherung miteinander verbunden und eine Oracle Instanz muss alle Daten Schemata auch die gerade nicht bentigten Verwalten. Somit ist es nicht mglich mehrere unabhngige Instanzen des Oracle Server laufen zu lassen, die auf den selben Tablespace zugreifen mssen. Beim SQL Server knnen Datenbanken jedoch komplett als eigenstndiges Gebilde verwaltet werden und somit die Daten effektiver voneinander getrennt werden, wenn der Anwender mehrere Datenbanken verwalten mchte. hnlich zu Oracle 10g werden die Daten physikalisch in so genannten 8KB groen Pages gespeichert, das ist die grundlegende Einheit fr I/O-Operationen des SQL Server. Wie bei Oracle gibt es Extends, diese sind aber nicht von variabler Gre, sondern beinhalten immer 8 Pages. Dabei kann es passieren, dass ein Datenbank Objekt alle 8 Pages des Extend belegt (uniform extend) oder sich bis zu 8 Objekte den Platz teilen (mixed extend). Eine Eintrag in einer Spalte der Datenbank kann nur auf eine Page geschrieben werden, ist somit auf 8 KB beschrnkt. Sollte die Gre doch berschritten werden, muss der Eintrag in eine neue Page (oder eine Reihe von Pages) geschrieben werden (Alloction unit) und ein Zeiger zu diesen in die ursprngliche Page gesetzt werden.

5.2 IndexierungIndexe sind fr den schnellen Datenzugriff und knnen somit die Dauer von I/O-Operationen stark verkrzen, was die Performance einer Transaktion entscheidend verbessern kann. Die folgende Tabelle zeigt, welche Indexverfahren von den jeweiligen Systemen untersttzt werden.

Seite 12

Thomas Wchtler Indextyp B-Baum Index B-Baum Index (geclustert) Hash Index (geclustert) Heap Index (ungeclustert) Reverse Key Index Bitmap Index Bitmap join Index Funktionsbasierter Index Bereichs Index Index-organisierte Tabellen Volltext Index Oracle 10g

Matrikelnummer: 39221 SQL Server 2005

O

O

O O O O O

O

Sowohl Oracle als auch SQL Server bieten den B-Baum Index an, der eine geordnete Liste von Schlsseln enthlt, die auf den jeweiligen Dateneintrag auf dem Datentrger verweisen. Beide bieten diese Art des Index auch fr geclusterte Indexierung. Oracle eigen sind die Bitmap Indizes. Hier werden nicht die Inhalte der einzelnen Zeilen aus der Tabelle im Index verwaltet, sondern ein Zeiger auf den Eintrag gesetzt, der die Bedingung erfllt. Somit wird weniger Speicherplatz verbraucht, was jedoch zum ermitteln der Werte einen etwas hheren Rechenaufwand mit sich bringt. Bitmap Join Indizes enthalten Zeiger auf die Daten in den jeweils zu verknpfenden Tabellen, sodass im besonderen Fall Joins gespart werden knnen, wenn zuvor Regeln dafr definiert wurden. Zustzlich zu den B-Baum Indizes bietet SQL Server 2005 den Volltext Index, der auf der Windows Suchfunktion basiert. Hierbei fhrt nicht der SQL Server den Such-Task aus, sondern das OS auf dem er installiert ist. Da Windows immer den Search Service installiert hat, wird diese Aufgabe vom Betriebssystem ausgefhrt. Das Problem dabei ist, das auch das OS den Index verwaltet und somit kein Backup oder Recovery mglich ist. Die Volltextsuche ermglicht es Teile eines gesuchten Eintrages in Form von Strings einzugeben und dazu die betroffenen Spalten auszugeben.

5.3 Concurrency Control und LockingSeite 13

Thomas Wchtler

Matrikelnummer: 39221

Die berwachung gleichzeitig stattfindender Operationen auf dem selben Datenbestand einer Datenbank und die Sicherstellung der Konsistenz der Daten whrend parallel ablaufender Transaktionen, ist ein wichtiger Bestandteil eines DBMS. Bestandteile sind das Locking und die Isolation Levels. Beide Anbieter bieten die Mglichkeit schon vor Beginn irgendwelcher Transaktionen Sperren auf bestimmten Tabellen anzulegen. In der folgenden Tabelle ist gegenbergestellt, welche Sperren auf Tabellen jeweils mglich sind. Sperre (Oracle) RS (Row Shared) RX (Row Exclusive) S (Shared) SRX (Shared Row Exclusive) X (Exclusive) Exclusive DDL Lock Shared DDL Lock Sperren(SQL Server 2005) IS (Intent Shared) IX (Intent Exclusive) S (Shared) SIX (Shared Intent Exclusive) X (Exclusive) U (Update) BU (Bulk-Update) Sch-M (Schema Modification) Sch-S (Schema Shared)

Oracle sowie SQL Server untersttzen beide alle gngigen Sperrverfahren. Zustzlich bietet SQL Server noch 2 weitere Verfahren an um Tabellen zu sperren. Das wre zum einen das Update Lock, welches zwischen dem Shared und dem Exclusive Lock liegt. Diese Art des Sperren wird verwendet, wenn noch nicht feststeht, ob eine nderung in einer Tabelle stattfindet, sollte es zu einer nderung kommen wird dieses Lock in Exclusive Lock automatisch umgewandelt, wenn nicht, dann wird es ein Shared Lock. Die Besonderheit hier ist, das 2 Update Locks nicht mit einander kompatibel sind. D.h. wenn bereits ein Update Lock vorliegt, wird kein weiteres gestattet, ein Shared Lock ist jedoch noch mglich, was bei einem SIX Lock nicht mehr mglich ist. Das BU ist dazu gedacht, wenn groe Datenmengen in eine Tabelle geladen werden, diese zu sperren. Besonders ist hier, das auch das Auslesen der Struktur der Tabelle dabei unterbunden wird, d.h. Sch-S ist ebenfalls verboten, was bei allen anderen Sperren (auer Sch-M) jedoch erlaubt ist. Oracle und MS SQL Server bieten verschiedene Isolation Levels: Seite 14

Thomas Wchtler Isolationlevel Read Committed Read Uncommitted Repeatable Read Serializable Read Only Explizites Locking Oracle 10g

Matrikelnummer: 39221 SQL Server 2005 (Snapshot)

O O

Beide Systeme untersttzen des Read Committed Level. Bei diesem sieht die aktuelle Transaktion ausschlielich die vor dem Beginn stattgefundenen nderungen und arbeitet mit diesen. Das kann ein Unrepeatable Read, oder ein Phantom Read verursachen. SQL Server bietet zustzlich noch das Read Uncommitted, was es einzelnen Transaktionen ermglicht, uncommittete nderungen von anderen Transaktionen zu sehen und das Repeatable Read, was sicherstellt, das auch bei wiederholtem Lesen die selben Ergebnisse erzielt werden knnen. Beide untersttzen mit Serializable die hchst mgliche Isolations-Ebene, in der die Transaktion ablaufen, als wrden sie nacheinander durchgefhrt. Das von Oracle 10g verwendete ReadOnly Verfahren ist dem Snapshotverfahren des SQL Server in der Wirkungsweise sehr hnlich. Oracle stellt mit Versionsvergabe fr die einzelnen Transaktionen sicher, das Daten, die jede einzelne Transaktion bentigt, auch beim Update durch eine andere Transaktion noch vorhanden ist. SQL Server dehnt dies soweit aus, das ein komplettes Abbild der zu bearbeitenden Daten gemacht wird und in einer neuen Datenbank (TempDB) gespeichert wird. Somit hat jede Transaktion ihre eigenen konsistenten Daten und es mssen keine exklusiven Sperren auf die Tabellen vergeben werden. Weiterhin bieten beide Anbieter die Mglichkeit das Locking selbst zu bestimmen. Bei beiden kann das Isolation Level transaktionsbezogen gendert werden, siehe Tabelle.

5.4 Backup und RecoveryBackups werden dazu bentigt, um bei System-Crashes sicher zustellen, das alle Daten, die in der Datnebank vorhanden waren, wieder zur Verfgung gestellt werden knnen. Recovery Seite 15

Thomas Wchtler

Matrikelnummer: 39221

ist wichtig, wenn ein System whrend der Laufzeit einen kritischen Fehler erzeugt und abstrtzt und noch nicht beendete Transaktionen auf der Datenbank ausgefhrt wurde. In diesem Fall helfen Logs diese Transaktionen nachzuvollziehen und sie ggf. zu wiederholen, oder ungltig zu machen. Es gibt 4 verschiedene Arten eine Datenbank in Oracle mithilfe eines Backups zu sichern:

Export/Import. Dies sind logische Backup Operationen, die logische Definitionen einer Datenbank in einer Datei speichern Offline Backup. Wenn die Datenbank vom Netz genommen wird, werden hier alle Daten, Logs und Kontroll- Dateien auf einen anderem Datentrger gesichert Online Backup. Hier wird das Backup whrend des laufenden Betriebes gemacht. Bei dieser Form des Backups mssen die Log-Files weiterlaufen um nderungen whrend der Backup-Phase ebenfalls zu speichern. RMAN Backups. Hierzu wird der Recovery Manager von Oracle benutzt.

Sollte eine Datenbankanwendung abstrzen, obwohl noch nicht alle Transaktionen beendet wurden, kommt das Recovery ins Spiel. Oracle hat zu diesem Zweck 3 wichtige Log-Dateien, die jeden Vorgang berwachen:

Das Control File beinhaltet Informationen, wie den Datenbank Namen, Zeitstempel fr die Erstellung der Datenbank, Namen der DDL Files und des Redo-Log-Files, sowie den letzten Checkpoint, an dem der Status der Datenbank vollstndig gespeichert wurde. In die Online Redo Log Files werden alle nderungen gespeichert, die whrend des laufenden Betriebes gemacht werden. In den Redo- Records werden neue nderungen gespeichert, die eine Transaktion vorgenommen hat, in den UndoRecords werden die Daten vor der nderung gespeichert, damit das System in jeder Situation die Mglichkeit hat, den korrekten Wert beim Recovery zu setzten. Die Archived Redo Log Files sind Redo Log Files, die nur noch Redo Informationen enthalten und bereits in ein Log Archiv gespeichert wurden.

Der Recovery Manager bietet hier die einfachste Art und Weise seine Datenbank effektiv zu sichern. Der SQL Server 2005 bietet mehrere Strategien fr das Recovery an:

Seite 16

Thomas Wchtler

Matrikelnummer: 39221

Simple. Ermglicht es lediglich zum letzten vollstndigen Backup zurckzugehen, da das Transactionlog hier nicht gespeichert wird. Full. Hier wird zustzlich das Transaction Log gespeichert, sodass man zu jeder beliebigen Position vor dem Absturz zurckkehren kann Bulk Logged. Hier werden die normalen Datenbankoperationen in den Transaction Logs gespeichert, jedoch nicht die Erstellung von Indizes oder das laden per Bulk Load (siehe Bulk-Update unter Concurrency Control und Locking)

Die wichtigste Datei beim SQL Server ist das Transaction Log File, das wie das Redo Log File von Oracle, alle Transaktionen aufzeichnet, um jeden beliebigen konsistenten Punkt wiederherstellen zu knnen. Die Methode des SQL Server wird Systemintern geregelt, man muss sich nur fr eine der 3 oben genannten Methoden entscheiden. Oracle bietet mehr Vielfalt, ist aber auch komplizierter zu handhaben.

5.5 Datenbank TuningDatenbank Tuning kann von Indexoptimierung, ber Abfrageoptimierung, bis hin zu nderungen an der Speicherverwaltung gehen. Hier soll ein kurzer berblick in nicht technische Aspekte des Tunings. SQL Server und Oracle bieten unterschiedliche Mglichkeiten Aggregat-Funktionen die sich auf Spalten einer Zeile beziehen effektiver in Indizes zu speichern. Oracle bietet die Mglichkeit Indizes auf eine Funktion zu legen, die zu einer speziellen Spalte gehrt. SQL Server bietet die Mglichkeit einen Index direkt auf der Spalte, deren Inhalt durch eine Funktion berechnet wird. Diese beiden Anstze sind im Prinzip quivalent und nennt sich "function based index". SQL Server gibt zustzlich die Mglichkeit beim erstellen/ndern einer solchen Spalte das Schlsselwort PERSISTED zu verwenden um anzuzeigen, das diese Spalte auch physisch gespeichert werden soll. Im Unterschied zu SQL Server ist bei Oracle 10g die durch den funktionsbasierten Index indirekt definierte Spalte nicht physisch in der Datenbank vorhanden. Ein solcher Index (Oracle) oder eine Spalte mit entsprechendem Index (SQL Server) kann die Performance bestimmter Anfragen, die sich auf eine Aggregation mehrerer Eintrge in einer Zeile beziehen, stark verbessern. Auch im Bereich der Optimierung von Views gibt es unterschiedliche Konzepte auf beiden Seite 17

Thomas Wchtler

Matrikelnummer: 39221

Seiten. SQL Server bietet die Indexed Views und Oracle die Materialized Views. Um groe und teure Joins zu vermeiden, bietet Oracle Materialized Views, die einen View als Tabelle in der Datenbank speichern und so auch Informationen in einer Tabelle vereinen, die sonst ber komplexe Joins errechnet werden mssten. SQL Server hingegen erzeugt einen Index auf einem bestimmten View, der phsikalisch gespeichert wird. Verwendet man einen Clustered Index auf dem ensprechenden View hat man das selbe Ergebnis wie der Materialised View von Oracle bietet. Nachteile hierbei ist jedoch, das man auf einen Index keine Funktionen wie DISTINCT, SUM, etc. anwenden kann, somit ist die Oracle Variante was die Auswertung betrifft leistungsstrker, verbraucht aber auch mehr Speicherplatz im Vergleich zum Index auf dem View. Beide Anstze beinhalten das Problem, das bei starker nderung der einzelnen Spalten, die auf den View Einfluss haben, auch der View angepasst werden muss. Dies Beeinflusst die Materialized Views strker als die Index Views, da dort nur der Index angepasst werden muss. Diese Methode ist also nur sinnvoll, wenn die Anfragen schwerer wiegen, als die nderungen der Basis-Tabellen. SQL Server bietet mit dem Tuning Advisor ein sehr ntzliches Tool an, das beim Finden von optimalen Indizes, Sichten und Partitionen hilft. Dabei wertet es voll automatisch die Datenbank nach Struktur und hufigen Anfragen aus und macht Vorschlge fr Optimierungen (siehe auch 3.3 Tuning Advisor).

5.6 PartitionierungPartitionierung erlaubt es groe Tabellen, Indizes und komplexe Strukturen in kleinere Stcke aufzuspalten. Dieses Verfahren wird hauptschlich zur bersichtlichkeit und Wartbarkeit der Daten eingesetzt, bietet aber unter bestimmten Umstnden auch Performance Verbesserungen. mgliche Partitionierungsmethoden fr Tabellen sind:

Aufteilen einer Tabelle indem die Tabelle horizontal aufgespalten wird, d.h. es werden die Zeilen einer Tabelle auf mehrere von der Struktur her gleiche Tabellen aufgeteilt. Ist z.B. bei einer Geschichts-Datenbank sehr ntzlich, wenn sie in mehrere Zeitrume aufgeteilt wird. Seite 18

Thomas Wchtler

Matrikelnummer: 39221

Hash- Partitionierung benutzt eine Hash-Funktion um die einzelnen Zeilen einer Tabelle neuen Tabellen zuzuordnen. Function Partitionierung bietet die Mglichkeit die Daten ber eine bestimmte Funktion aufzuteilen Listen Partitionierung. In Listen kann genau definiert werden, in welcher Reihenfolge die Zeilen auf die jeweiligen Tabellen abgebildet werden sollen. gemischte Partitionierung erlaubt es mehrere Partitionierungs-Techniken

anzuwenden. Wenn man z.B. eine Tabelle per Hash partitioniert und die Partitionen wieder mittels Listen in weitere Partitionen aufsplittet. mgliche Methoden fr Indizes sind:

Lokale Indizes sind von der Partitionierung her genauso aufgebaut, wie die zu Grunde liegenden partitionierten Tabellen. D.h. ein partitionierter Index gehrt genau zu einer partitionierten Tabelle. global Partitionierte Indizes sind Indizes, die nicht nach Art der Tabelle partitioniert sind. Solch ein Index kann fr eine partitionierte Tabelle, oder eine nicht partitionierte Tabelle angelegt werden nicht partitionierte Indizes sind normale Indizes, wie sie normaler Weise fr eine nicht partitionierte Tabelle verwendet werden

Von Oracle und SQL Server werden folgende Mglichkeiten zur Partitionierung angeboten: Partition Typ Range Hash Function List Composite Lokale Indizes Globale Indizes Oracle 10g SQL Server 2005

O

O

O

SQL Server verfolgt mit dem funktionsbasiertem Partitionieren eine andere Strategie, als Oracle mit hashbasiertem Partitionieren. Durch Funktionen kann der Anwender viel genauer

Seite 19

Thomas Wchtler

Matrikelnummer: 39221

bestimmen, wie die Tabelle aufgeteilt werden soll, wohingegen durch Hashing eine gleichmigere Verteilung der Daten mglich ist. Unterschiede bestehen aber besonders in der maximalen Anzahl an Partitionen, whrend Oracle bis zu 1 Million Partitionen pro Tabelle erlaubt, sind es beim SQL Server lediglich 1000.

5.7 ClusteringCluster sind eine Reihe von unabhngig voneinander positionierten Servern, die ber ein Netzwerk miteinander verbunden sind, so dass sie als ein ganzes System zusammenarbeiten knnen. Im Datenbank Technischen Sinn ist dies also eine Datenbank, die auf verschiedenen Servern luft, aber als eine Datenbank betrachtet wird. Clustering findet hauptschlich bei sehr groen Datenbestnden Anwendung, wenn ein einzelner Server nicht mehr die Kapazitten hat, sei es speicherplatztechnisch, oder leistungsmig, zur Abarbeitung der einzelnen Transaktionen. Oracle verfolgt eine andere Strategie beim Clustering als der SQL Server 2005. Oracle ermglicht es eine Datenbank auf mehrere Server zu verteilen und nach belieben neue Knoten (Server) zum Cluster hinzuzufgen, wenn es der Umstand erfordert. Dies ist auch dicht an das Konzept zur Verwaltung der Daten innerhalb der Datenbank gekoppelt. Der Vorteil ist, das die Last gleichmig auf alle Server verteilt werden kann, was die Skalierbarkeit, Parallelitt und Performance sehr stark verbessert. Auerdem gibt es nur ein DDL File, das die Struktur der gesamten Datenbank beinhaltet. SQL Server bietet eine indirekte Clusterung, indem eine Datenbank in mehrere Datenbanken per Partitionierung aufgeteilt werden. Nachteil hierbei ist, das nur der Server, auf dem die angefragte Partition vorhanden ist, diese auch verndern, bzw. auslesen kann. Somit ist die Geschwindigkeit von den einzelnen Rechnern abhngig und widerspricht dem eigentlichen Konzept der Clusterung. Vorteil der SQL Server Methode besteht nur dann, wenn die Datenbanken unabhngig voneinander sind. Das Problem der Oracle Methode ist die berwachung der gleichzeitig stattfindenden Transaktionen. Im Prinzip ist das Cluster eine groe Datenbank und damit ist die Bearbeitung der Daten sehr von der Implementierung der Datenbank Engine abhngig. Seite 20

Thomas Wchtler

Matrikelnummer: 39221

6. QuellenNeutraler Vergleich von Oracle 10g und SQL Server 2005

http://www.wisdomforce.com/dweb/resources/docs/MSSQL2005_ORACLE10g_compare.p df

Oracle Whitepaper zum technischen Vergleich

http://www.oracle.com/technology/deploy/performance/pdf/twp_perf_oracle%20databa se%2010gr2%20vs%20ss2005.pdf

bersicht SQL Server 2005

http://www.datenbank-spektrum.de/pdf/dbs-16-34.pdf http://en.wikipedia.org/wiki/Microsoft_SQL_Server

Oracle Architecture

http://ugweb.cs.ualberta.ca/~c391/manual/chapt2.html http://www.ucertify.com/article/THE-ORACLE-10g-ARCHITECTURE.html

Concurrency Control & Locking

http://www.db.cs.ucdavis.edu/teaching/289F/handout-5-6.pdf https://www.indiana.edu/~dbateam/resources/tips/oracle_locking.ppt http://www.sws.bfh.ch/~schmd/db/MEMOS/Oracle%20Isolation%20Levels.pdf http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

Partitionierung

http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_general_partitioning_10 gr2_0505.pdf

Microsoft SQL Server 2005

http://msdn.microsoft.com/de-de/library/ms130214.aspx http://technet.microsoft.com/de-de/library/ms130214.aspx Seite 21