SQL Server 2000 - HERDT
Werbung
Microsoft SQL Server 2000 Administration SQL2000A 00-0-03-72-64 Autor: G. Hölbling 1. Auflage: März 2001 (120301) by HERDT-Verlag, Nackenheim (Germany) Internet: www.herdt.com Alle Rechte vorbehalten. Kein Teil des Werkes darf in irgendeiner Form (Druck, Fotokopie, Microfilm oder einem anderen Verfahren) ohne schriftliche Genehmigung des Herausgebers reproduziert oder unter Verwendung elektronischer Systeme verarbeitet, vervielfältigt oder verbreitet werden. Diese Unterlage wurde mit großer Sorgfalt erstellt und geprüft. Trotzdem können Fehler nicht vollkommen ausgeschlossen werden. Verlag, Herausgeber und Autoren können für fehlerhafte Angaben und deren Folgen weder eine juristische Verantwortung noch irgendeine Haftung übernehmen. Diese Unterlage ist auf chlorfrei gebleichtem Papier gedruckt. INHALTSVERZEICHNIS 1 Einsatzgebiete und Voraussetzungen......... 4 1.1 1.2 1.3 Verwendungsfelder...........................................4 Hardware und Software ....................................5 Vorausgesetzte Kenntnisse ..............................6 SQL Server 2000 - Administration 5.2 5.2.1 5.2.2 5.3 Physischer Aufbau des Systems ......................9 Einrichtung und Pflege der Software ..............10 Login- und Benutzerverwaltung ......................12 Backup- und Restore-Prozeduren ..................12 Systemüberwachung und System-Tuning ......13 Wissens-Check ..............................................14 3 Architektur und Systemkomponenten ...... 16 3.1 3.1.4 3.1.5 MSSQLServer........................................19 Server-Agent..........................................20 Search-Dienst ........................................20 Distributed-Transaction-CoordinatorDienst ....................................................20 SQL-Server-Dienst-Manager..................20 SQL-Server-Enterprise-Manager............21 SQL Server Query Analyzer ...................24 Weitere grafische Werkzeuge ................24 Assistenten ............................................25 Befehlszeilenwerkzeuge.........................26 4.1 Vorbereitungen ...............................................30 4.1.1 4.1.2 4.1.3 4.1.4 4.2 4.3 Hardware-Check ....................................30 Check der Netzwerkumgebung ..............31 Systemstabilität checken........................32 Vorbereiten des Betriebssystems ...........32 Überprüfen der Ordnerstruktur ...............36 Überprüfen der Programmgruppe...........36 Server-Dienste starten ...........................37 Client-Zugriff testen................................37 Überprüfen der Konfiguration .................37 Wissens-Check ..............................................38 5 Objektverwaltung ........................................ 40 5.1 Logische Datenbankobjekte ...........................40 5.1.1 5.1.2 5.1.3 5.1.4 5.1.5 5.1.6 Bezeichner.............................................40 Datenbank .............................................41 Tabellen .................................................43 Index......................................................44 Sichten...................................................46 Dateigruppen .........................................47 Das Sicherheitskonzept von SQL Server 2000............................................54 6.2.2 6.2.3 6.3 6.3.3 6.3.4 Zuordnen von Datenbankrollen.............. 62 Verwalten von Anweisungsberechtigungen...................................... 63 Verwalten von Objektberechtigungen .... 64 Erstellen benutzerdefinierter Datenbankrollen .................................... 64 Praxisgerechte Sicherheitsstrategien .............65 6.4.1 6.4.2 6.4.3 6.4.4 6.5 Festlegen des globalen Sicherheitsmodus .................................. 58 Erstellen eines Login-Kontos ................. 59 Verwalten der Login-Konten................... 60 Benutzerkonten und Rollen ............................62 6.3.1 6.3.2 6.4 Login- oder Anmeldekonto..................... 54 Sicherheitsmodi von SQL Server 2000 .. 55 Spezielle Login-Konten .......................... 56 Erteilen von Berechtigungen .................. 56 Login-Konten erstellen und verwalten ............58 6.2.1 Auswahl des Sicherheitsmodus ............. 65 Datenbankrolle public und Benutzerkonto guest.............................. 65 Das Benutzerkonto sa ........................... 66 Entwerfen des Sicherheitsmodells ......... 66 Wissens-Check ..............................................67 7 Eine Benutzerdatenbank erstellen .............68 7.1 Grundkonzept einer relationalen Datenbank......................................................68 7.1.1 7.1.2 7.1.3 7.1.4 Das Setup Schritt für Schritt ...........................33 Installationskontrolle und Konfiguration ..........36 4.3.1 4.3.2 4.3.3 4.3.4 4.3.5 4.4 6.2 Transact-SQL .................................................26 Wissens-Check ..............................................28 4 Installation und Konfiguration ................... 30 Wissens-Check ..............................................53 6.1.1 6.1.2 6.1.3 6.1.4 Werkzeuge und Assistenten ...........................20 3.3.1 3.3.2 3.3.3 3.3.4 3.3.5 3.3.6 3.4 3.5 6.1 Server-Dienste ...............................................19 3.2.1 3.2.2 3.2.3 3.2.4 3.3 Tier- oder Schicht-Modelle .....................16 2-Tier auf verteilten Rechnern ................16 3-Tier oder Mehr-Tier auf verteilten Rechnern ...............................................17 2-Tier auf einem Rechner.......................18 Vorteile der Client-Server-Architektur .....18 Die Systemdatenbank master ................ 50 Die Systemdatenbank model ................. 51 Die Systemdatenbank msdb .................. 52 Die Systemdatenbank tempdb ............... 52 Dateien der Systemdatenbanken ........... 52 6 Login-Verwaltung und Erteilung von Rechten .........................................................54 Client-Server-Architektur ................................16 3.1.1 3.1.2 3.1.3 3.2 5.4 Dateien .................................................. 48 Seiten und Blöcke.................................. 49 Systemdatenbanken.......................................49 5.3.1 5.3.2 5.3.3 5.3.4 5.3.5 2 Berufsbild "Datenbankadministrator"......... 8 2.1 2.2 2.3 2.4 2.5 2.6 Physische Datenbankobjekte .........................48 7.2 7.3 Einrichten einer Benutzerdatenbank...............73 Erstellen von Tabellen....................................76 7.3.1 7.3.2 7.4 7.5 7.6 Daten und Datenbank............................ 68 Relationale Datenbanken....................... 69 Datennormalisierung.............................. 70 Beziehungen.......................................... 72 Planen der Tabellenstruktur................... 76 Die Tabelle erzeugen............................. 77 Beziehungen zwischen Tabellen ....................77 Übung ............................................................79 Wissens-Check ..............................................80 8 Mit einer Benutzerdatenbank arbeiten .......82 8.1 8.2 8.3 8.4 8.5 Daten eingeben, bearbeiten, löschen .............82 Filtern nach Kriterien ......................................83 Erstellen von Sichten......................................86 Übung ............................................................88 Wissens-Check ..............................................88 I Inhaltsverzeichnis 9 Daten sichern (Backup) .............................. 90 9.1 Sicherungsmethoden und Sicherungsmedien ........................................................... 90 9.1.1 9.1.2 9.1.3 9.2 Vollständige Datenbanksicherung .......... 96 Differenzialsicherung ............................. 96 Sicherung des Transaktionsprotokolls.... 97 Differenzialsicherung und Sicherung des Transaktionsprotokolls .................... 98 Sicherung erstellen ........................................ 99 9.3.1 9.3.2 9.3.3 9.4 Backup-Methoden.................................. 90 Sicherungsmedien ................................. 93 Vorkehrungen im laufenden Betrieb ....... 93 Das Sicherungskonzept ................................. 95 9.2.1 9.2.2 9.2.3 9.2.4 9.3 Backup mit dem Sicherungsassistenten ............................................ 99 Erstellen und Verwalten benannter Sicherungsmedien ............................... 102 Automatische Sicherung ...................... 104 Wissens-Check ............................................ 105 10 Daten wiederherstellen (Restore) ............ 106 10.1 10.2 10.3 10.4 13.2 Automatische Wiederherstellung.................. 106 Wiederherstellungsverfahren für Benutzerdatenbanken .................................. 107 Systemdatenbanken wiederherstellen.......... 110 Wissens-Check ............................................ 111 13.2.1 Auftragserstellung mit dem Assistenten...........................................135 13.2.2 Aufträge bearbeiten ..............................137 13.2.3 Der Auftragsverlauf...............................139 13.3 13.4 11.4 14.1 14.2 14.3 14.4 14.5 12.1 Leistung einer Datenbank ............................ 120 12.1.1 Leistungsparameter (Output) ............... 120 12.1.2 Faktoren des Leistungsverhaltens (Input)................................. 121 12.2 15.1 12.3 SQL Profiler ......................................... 124 Systemmonitor..................................... 128 SQL Server Query Analyzer................. 128 Aktuelle Aktivität .................................. 129 Abfragekontrolle................................... 130 Wissens-Check ............................................ 131 Komponenten und Typen der Replikation .... 155 15.1.1 15.1.2 15.1.3 15.1.4 15.2 Verleger, Abonnent, Verteiler ...............155 Push- und Pull-Abonnements ...............156 Publikationen und Artikel ......................157 Replikationstypen .................................158 Einrichten und Durchführen einer Replikation ................................................... 159 15.2.1 Die Replikationsagenten.......................159 15.2.2 Vorbereitungen für die Replikation........160 15.2.3 Verteiler, Verleger, Abonnenten einrichten..............................................160 15.2.4 Erstellen einer Publikation ....................162 15.2.5 Einrichten von Abonnements ................165 15.2.6 Verwalten der Replikationen.................166 15.3 Szenarios für Replikationen ......................... 167 15.3.1 Szenario A: Ein Verleger, mehrere Abonnenten..........................................167 15.3.2 Szenario B: Ein Abonnent, mehrere Verleger .................................168 15.4 15.5 Verteilte Datenbanken zur Echtzeit .............. 168 Wissens-Check............................................ 170 16 Publizieren im World Wide Web .............. 172 16.1 Datenbanken im Internet.............................. 172 16.1.1 Statische HTML-Seiten.........................172 16.1.2 Dynamische HTML-Seiten....................173 16.1.3 Sicherheitsaspekte ...............................174 Werkzeuge zur Überwachung der Performance ................................................ 123 12.2.1 12.2.2 12.2.3 12.2.4 12.2.5 Die Data Transformation Services (DTS)..... 147 Arbeiten mit dem DTS-Assistenten .............. 148 Massendatentransfer mit bcp....................... 151 Übung .......................................................... 152 Wissens-Check............................................ 152 15 Datenbanken verteilen (Replikation) ....... 154 Wissens-Check ............................................ 118 12 Geschwindigkeit und Stabilität (Performance) ............................................ 120 Wissens-Check............................................ 144 14 Import und Export von Daten................... 146 Fehlerquellen im System.............................. 112 Die Fehlermeldungen von SQL Server......... 113 Gezielte Fehlerbehandlung .......................... 115 11.3.1 Fehlermeldungen im Team auswerten ............................................ 115 11.3.2 Fehlerprotokolle einsetzen ................... 115 11.3.3 Die DBCC-Anweisung (DataBase Consistency Checker).......................... 117 11.3.4 Die Online-Dokumentation ................... 118 Warnmeldungen erzeugen........................... 140 13.3.1 Definieren von Operatoren ...................141 13.3.2 Bearbeiten bestehender Operatoren.....142 13.3.3 Der Warnungserstellungsassistent .......142 11 Fehlerbehandlung ..................................... 112 11.1 11.2 11.3 Auftragserstellung und Auftragsverwaltung ................................................... 135 16.2 16.3 16.4 Der Web-Assistent ....................................... 174 Active Server Pages (ASP) .......................... 179 Wissens-Check............................................ 181 17 Wissens-Check: Antworten...................... 182 Anhang 1: Web-Adressen.............................. 194 13 Verwaltungsaufgaben automatisieren .... 132 13.1 Dienste und Werkzeuge zur Automatisierung ........................................... 133 13.1.1 SQL Server Agent................................ 133 13.1.2 Werkzeuge zur Automatisierung .......... 133 Anhang 2: Glossar.......................................... 196 Stichwortverzeichnis...................................... 200 5 SQL Server 2000 - Administration 5 Objektverwaltung In diesem Kapitel erfahren Sie à wie Sie Datenbankobjekte mit dem Enterprise-Manager verwalten à wie SQL Server 2000 Datenbanken physisch speichert à wozu Systemdatenbanken benötigt werden Voraussetzungen ü Basiskenntnisse über Computersysteme und Datenbanken SQL Server 2000 verwendet zur Speicherung und Verwaltung von Daten eine Vielzahl von Objekten oder Komponenten. Dabei kann zwischen logischen und physischen Objekten unterschieden werden: ý Logische Objekte werden von SQL Server 2000 verwaltet. Logische Objekte sind z.B. Datenbanken, Tabellen, Indexe oder Dateigruppen. ý Physische Objekte werden vom Betriebssystem oder von SQL Server 2000 gemeinsam mit dem Betriebssystem verwaltet. Die wichtigsten physischen Objekte sind Dateien auf Betriebssystemebene. 5.1 Logische Datenbankobjekte 5.1.1 Bezeichner Logische Datenbankobjekte werden über einen Bezeichner (Namen) angesprochen. Der Bezeichner darf aus maximal 128 Zeichen bestehen. Erfüllt der Bezeichner die folgenden Bedingungen, handelt es sich um einen regulären Bezeichner: þ Der Bezeichner beginnt mit einem Buchstaben oder mit @ (At-Zeichen), _ (Unterstrich) oder # (Nummernzeichen). þ Der Bezeichner ist nicht mit einem reservierten Wort von Transact-SQL identisch (z.B. SELECT, TEXT). þ Der Bezeichner enthält keine Leer- oder Sonderzeichen. Reguläre Bezeichner können in Transact-SQL ohne besondere Vorkehrungen verwendet werden. SELECT * FROM Mitarbeiter WHERE Abteilung = 'Verkauf' Entspricht ein Bezeichner nicht den Regeln für einen regulären Bezeichner, muss er in [eckige Klammern] eingeschlossen werden. Ist für die Datenbank die Option BEZEICHNER IN ANFÜHRUNGSZEICHEN VERWENDEN aktiviert, können statt der eckigen Klammern auch "Anführungszeichen" verwendet werden. SELECT * FROM [Verzeichnis der Mitarbeiter] WHERE "Wohnort privat" = 'Berlin' Um Fehlerquellen auszuschalten, sollten ausschließlich reguläre Bezeichner verwendet werden. 40 Objektverwaltung 5.1.2 5 Datenbank An der Spitze der Hierarchie von Datenbankobjekten steht die Datenbank selbst. Sie können sich die Datenbank als einen Container vorstellen, der alle anderen Datenbankobjekte in sich enthält. Die Datenbank verfügt über eine Reihe von Eigenschaften, die das Verhalten der Datenbank bestimmen. Diese Eigenschaften können Sie mit Hilfe des Enterprise-Managers festlegen. Þ Klicken Sie in der Konsolenstruktur des Enterprise-Managers mit der rechten Maustaste auf die Datenbank, deren Eigenschaften Sie festlegen möchten. Þ Wählen Sie im Kontextmenü den Befehl EIGENSCHAFEN. Das Dialogfeld EIGENSCHAFTEN wird geöffnet. Þ Klicken Sie auf das Register OPTIDie Kontrollfelder dieses Dialogfensters ermöglichen die Festlegung der gewünschten Datenbankeigenschaften. ONEN. Die zur Verfügung stehenden Op- Register OPTIONEN zur Festlegung von Datenbankeigenschaften tionen werden im Folgenden ausführlich beschrieben. Zugriff einschränken ý Mitglieder von ’db_owner’, ’dbcreater’ oder ’sysadmin’ Die Datenbank kann nur von einem Systemadministrator geöffnet werden. Jeder Systemadministrator gehört automatisch einem speziellen Benutzerkreis namens DBO (DataBase Owner) an (vgl. Kapitel 6). ý Einzelbenutzermodus Es kann sich nur ein einziger Benutzer bei der Datenbank anmelden. Aktivieren Sie diesen Modus, wenn Sie als Datenbankadministrator Arbeiten an der Datenbank vornehmen, bei denen keine weiteren Benutzeraktivitäten zulässig sind. Dies ist z.B. beim Wiederherstellen einer beschädigten Datenbank der Fall. Schreibgeschützt Die Datenbank kann nur für Leseoperationen geöffnet werden. Das Hinzufügen, Ändern oder Löschen von Daten ist nicht gestattet. Aktivieren Sie diese Option z.B. dann, wenn Sie die Datenbank auf einem StandbyServer einsetzen. Notizen 41 5 SQL Server 2000 - Administration Wiederherstellung Im Kombinationsfeld MODELL können Sie festlegen, wie die Datenbank im Schadensfall wiederhergestellt werden soll. ý Einfach Die Datenbank wird mit dem Zustand der letzten Sicherung restauriert. ý Massenprotokollier: Die Datenbank kann mit dem Status der letzten Transaktionsprotokoll-Sicherung wiederhergestellt werden. ý Vollständig Die Datenbank kann bis zum Zeitpunkt des zuletzt aufgetretenen Fehlers rekonstruiert werden. ANSI NULL ist Standard Für die Spalten einer neuen Tabelle wird standardmäßig festgelegt, dass Nullwerte zulässig sind. Rekursive Trigger Ist dieses Kontrollkästchen aktiviert, so kann sich ein Trigger direkt oder indirekt selbst erneut aufrufen. Das Zulassen rekursiver Trigger kann zu Endlosschleifen und dadurch zu unkontrollierten Programmabbrüchen führen. Statistiken automatisch aktualisieren Zur Optimierung der Geschwindigkeit von Abfragen erstellt SQL Server 2000 interne Statistiken über die Verteilung von Werten in Tabellenspalten. Ist diese Option aktiv, werden diese Statistiken bei Bedarf automatisch erstellt. Erkennung von zerrissenen Seiten Ein Strom- oder Systemausfall kann bewirken, dass eine Datenänderung nur unvollständig auf der Festplatte gespeichert wurde. Ist diese Option aktiviert, erkennt SQL Server 2000 bei der automatischen Wiederherstellung den unvollständigen Speichervorgang. Falls Ihr System über keine unterbrechungsfreie Stromversorgung verfügt, sollten Sie diese Option aktivieren. SQL Server 2000 setzt dann bei jedem Speichervorgang ein Prüf-Bit, das die Vollständigkeit der Speicherung überwacht. Automatisch schließen Die Datenbank wird automatisch geschlossen, sobald kein Benutzer mehr mit der Datenbank verbunden ist. Aktivieren Sie diese Option, wenn Sie z.B. mit einem Notebook arbeiten und der einzige Benutzer der Datenbank sind. Das Schließen von Datenbanken gibt Systemressourcen frei. In einer Mehrbenutzerumgebung mit häufigen Zugriffen auf die Datenbank sollte diese Option allerdings deaktiviert sein, da sonst bei jedem Verbindungsversuch die Datenbank erneut geöffnet werden muss. Dies führt zu Geschwindigkeitseinbußen. Automatisch verkleinern Ist diese Option aktiviert, überprüft SQL Server 2000 periodisch, ob nicht verwendeter Speicherplatz in Dateien freigegeben werden kann. Ist dies der Fall, wird die Datei auf eine Größe reduziert, die noch 25 Prozent freien Speicherplatz enthält. Eine Datei kann nicht unter die bei ihrer Erstellung angegebene Mindestgröße verkleinert werden. 42 5 Objektverwaltung Statistiken automatisch erstellen Aktivieren Sie diese Option, wenn SQL Server 2000 die Statistiken nach einer Änderung der Tabellendaten automatisch aktualisieren soll. Die Verwendung von unaktuellen Statistiken kann die Ausführung von Abfragen erheblich verlangsamen. Bezeichner in Anführungszeichen verwenden Ist diese Option aktiv, können Bezeichner (z.B. Tabellennamen) in "Anführungszeichen" oder in [eckige Klammern] eingeschlossen werden. Zeichenfolgen wie 'Fritz Müller' müssen in diesem Fall in einfachen Anführungszeichen stehen. Ist die Option nicht aktiv, können Zeichenfolgen in einfachen oder doppelten Anführungszeichen stehen. Bezeichner, die nicht den Regeln für reguläre Bezeichner entsprechen, müssen in diesem Fall in [eckige Klammern] gesetzt werden. 5.1.3 Tabellen Sämtliche Daten einer Datenbank sind in Tabellen gespeichert. Tabellen organisieren den Datenbestand in Spalten und Zeilen: Primärschlüssel Spalten bzw. Felder KundenNr Zuname Vorname 1 Müller Sabine 2 Meier Gert 3 Scholz Christa Feldnamen Zeilen bzw. Datensätze Datenorganisation in einer Tabelle Jede Spalte in der Tabelle wird durch einen Feldnamen identifiziert. Die Feldnamen sollten den Regeln für reguläre Bezeichner entsprechen. Die Anzahl der Felder und deren Namen werden durch die operative Datenbearbeitung nicht beeinflusst. Jede Zeile der Tabelle bildet einen Datensatz. Ein Datensatz ist die Gesamtheit der Informationen, die über ein bestimmtes Objekt, z.B. über Frau Müller, in der Tabelle gespeichert werden. Die Anzahl und der Inhalt der Datensätze werden durch die operative Datenbearbeitung ständig verändert. Eine besondere Rolle in dieser Tabelle spielt das Feld KundenNr. Es dient dazu, jeden Datensatz eindeutig (unverwechselbar mit anderen Datensätzen) zu kennzeichnen. Ein Feld, das diese Aufgabe in einer Tabelle erfüllt, heißt Primärschlüssel. Notizen 43 5 SQL Server 2000 - Administration Tabelleneigenschaften anzeigen Wie die Datenbank selbst, verfügt auch jede Tabelle über eine Reihe von Eigenschaften, die das Verhalten der Tabelle bestimmen. So können Sie sich im Enterprise-Manager einen Überblick über die Tabelleneigenschaften anzeigen lassen: Þ Klicken Sie im Detailbereich des EnterpriseManagers doppelt auf die gewünschte Tabelle. Das Dialogfenster TABELLENEIGENSCHAFTEN wird geöffnet. Das Dialogfenster informiert Sie über þ den Besitzer der Tabelle þ das Erstellungsdatum der Tabelle þ die Dateigruppe, der die Tabelle zugeordnet ist þ die aktuelle Anzahl der Zeilen (Datensätze) in der Dialogfenster TABELLENEIGENSCHAFTEN Tabelle Zusätzlich erhalten Sie Informationen über die wichtigsten Eigenschaften der in der Tabelle vorhandenen Felder, wie Name, Datentyp und Feldgröße. Das Dialogfeld TABELLENEIGENSCHAFTEN ist schreibgeschützt, d.h., Sie können hier keine Änderungen der Eigenschaften vornehmen. Systemtabellen Jede Datenbank erhält automatisch einen Satz von Systemtabellen, in denen Informationen über die Datenbank selbst gespeichert sind. Gemeinsam bilden diese Tabellen den so genannten Datenbankkatalog. Der Datenbankkatalog speichert und verwaltet Informationen über alle Datenbankobjekte. Sie sollten keine direkten Änderungen in den Systemtabellen vornehmen, da die Datenbank dadurch zerstört werden kann. Verwenden Sie stattdessen die Werkzeuge des Enterprise-Managers. 5.1.4 Index Ein Index dient dazu, das Sortieren, Suchen und Auswählen von Datensätzen einer Tabelle zu beschleunigen. Wie das Stichwortverzeichnis dieser Seminarunterlage, besteht auch ein Datenbankindex im Prinzip aus einer Tabelle mit zwei Spalten: ý In der einen Spalte befinden sich, alphabetisch sortiert, die Einträge eines Tabellenfeldes. ý In der zweiten Spalte wird die physische Zeilennummer dieses Datensatzes gespeichert. So wie ein Buch mehrere Indizes enthalten kann (Index der Ortsnamen, Index der Personen, Index der Abbildungen), können auch auf eine Tabelle mehrere Indizes gesetzt werden. SQL Server 2000 kann maximal 249 Indizes pro Tabelle verwalten. Ein Index kann aus einer oder aus mehreren Spalten (zusammengesetzter Index) einer Tabelle gebildet werden. Maximal können 16 Spalten zur Bildung eines Indexes verwendet werden. Die Gesamtlänge der Spalten, aus denen sich der Index zusammensetzt, darf allerdings 900 Byte nicht überschreiten. Ein Index kann eindeutig sein oder Wiederholungen von Werten zulassen. Wenn Sie auf ein Tabellenfeld einen eindeutigen Index setzen, verhindert SQL Server 2000 die mehrfache Eingabe desselben Werts in dieses Feld. Für das Primärschlüsselfeld einer Tabelle setzt SQL Server 2000 automatisch einen eindeutigen Index. Eine Tabelle kann über genau einen gruppierten Index verfügen. Ein gruppierter Index sortiert die Originaltabelle selbst nach den gewählten Indexspalten. 44 5 Objektverwaltung Indizes werden von SQL Server 2000 als interne Tabellen verwaltet, auf die kein direkter Zugriff möglich ist. Mehrere Indizes, vor allem Indizes über mehrere Spalten, können leicht die Größe der ursprünglichen Tabelle erreichen oder sogar überschreiten. Bei der Festlegung von Indizes sollte daher sorgfältig verfahren werden. Erstellen Sie nur Indizes für ein Feld oder mehrere Felder, wenn Sie sicher sind, dass diese Felder oft für Sortierungen bzw. Sichten verwendet werden. Die Wirkung von Indizes auf die Performance einer Datenbank hängt entscheidend von deren Einsatzgebiet ab. þ Bei einer operativ verwendeten Datenbank, wo täglich sehr viele Änderungen stattfinden, vermindern viele Indizes die Performance der Datenbank, da nach jeder Datenbearbeitung die Indizes aktualisiert werden müssen. Dies kann zu einer hohen Belastung der Systemressourcen führen. þ Bei einer analytisch genutzten Datenbank, auf die Benutzer nur lesend zugreifen, können zweckmäßig gesetzte Indizes den Datenzugriff erheblich beschleunigen. Indizes erstellen und verwalten Þ Klicken Sie mit der rechten Maustaste im Detailbereich des Enterprise-Managers auf die Tabelle, deren Indizes Sie verwalten wollen. Þ Wählen Sie im Kontextmenü den Befehl ALLE TASKS und daraus den Befehl INDIZES VEWALTEN. Das Dialogfenster INDIZES VERWALTEN wird geöffnet, und die vorhandenen Indizes der Tabelle werden angezeigt. Þ Klicken Sie auf NEU, um der Tabel- Verwalten von Indizes - Übersicht le einen Index hinzuzufügen. Das Dialogfeld NEUEN INDEX ERSTELLEN wird geöffnet. oder Þ Klicken Sie auf BEARBEITEN, um Änderungen an einem bereits vorhandenen Index vorzunehmen. Þ Geben Sie in das Feld INDEXNAME einen Namen für den neuen Index ein. Þ Aktivieren Sie das Kontrollfeld SPALTE für die Tabellenspalten, die Sie in den Index aufnehmen möchten. Ein Index über mehrere Spalten wird hierarchisch von oben nach unten sortiert. Mit den Schaltflächen AUFWÄRTS bzw. ABWÄRTS können Sie die Reihenfolge der Spalten anpassen. In der nebenstehenden Abbildung wird ein zusammengesetzter Index erstellt, der zuerst nach Zuname und dann nach Vorname sortiert (Telefonbuchsortierung). Þ Erstellen Sie den Index mit OK. Erstellen und Bearbeiten eines Indexes 45 5 SQL Server 2000 - Administration Für einen Index können folgende Optionen festgelegt werden: Gruppierter Index Die Originaltabelle selbst wird nach den Indexspalten sortiert. Pro Tabelle kann nur ein gruppierter Index erstellt werden. Eindeutige Werte Ist diese Option aktiviert, kann die indizierte Spalte nur eindeutige Werte enthalten. Bei einem zusammengesetzten Index können die einzelnen Spalten des Indexes Wiederholungen enthalten. Die Kombination aller indizierter Spalten muss jedoch einen eindeutigen Wert ergeben. Doppelte Werte ignorieren Diese Option ist nur für einen eindeutigen Index wirksam. Sie steuert das Verhalten von SQL Server 2000, wenn Sie versuchen, in eine Tabelle Werte einzufügen, die dem eindeutigen Index widersprechen. Ist die Option nicht aktiv, wird die gesamte Aktion mit einer Fehlermeldung abgebrochen. Ist die Option aktiv, wird eine Warnmeldung ausgegeben und der Datensatz mit dem doppelten Wert nicht in die Tabelle eingefügt. Sie wollen 100 Datensätze einer Tabelle hinzufügen. Einer dieser Datensätze enthält einen doppelten Wert für einen eindeutigen Index. Ist die Option nicht aktiv, wird kein einziger Datensatz hinzugefügt, da SQL Server 2000 die gesamte Aktion abbricht. Ist die Option aktiv, wird nur der eine Datensatz mit dem doppelten Wert nicht hinzugefügt. Statistik nicht erneut berechnen (nicht empfohlen) Zur Optimierung von Abfragen führt SQL Server 2000 interne Statistiken über die Verteilung von Werten in Tabellenspalten. Sie sollten diese Option nicht aktivieren, da durch sie die Systemgeschwindigkeit herabgesetzt werden kann. Dateigruppe Falls Sie in der Datenbank Dateigruppen erstellt haben, können Sie den Index einer vorhandenen Dateigruppe zuordnen. So können Sie bewirken, dass der Index physisch auf einer bestimmten Festplatte gespeichert wird. Vorhandenen löschen Aktivieren Sie diese Option, wenn bereits ein Index desselben Namens vorhanden ist, den Sie durch den neuen Index ersetzen wollen. Füllfaktor Der Füllfaktor weist SQL Server 2000 an, intern physischen Speicherplatz für künftige Indexänderungen freizuhalten. Die Zahl gibt den Füllungsgrad einer Indexseite in Prozent an. Ein kleiner Füllfaktor vergrößert proportional den für den Index benötigten Speicherplatz. Er kann jedoch zu einer Verbesserung der Geschwindigkeit beitragen, falls sich der Index sehr häufig ändert. Index auffüllen reserviert zusätzlichen internen Speicherplatz und verwendet dafür den Prozentwert von Füllfaktor. 5.1.5 Sichten Sichten dienen dazu, gezielt Datensätze oder Felder aus einer oder aus mehreren Tabellen auszuwählen. In einer Sicht werden keine Daten gespeichert. Eine Sicht speichert vielmehr eine Transact-SQL-Anweisung, die festlegt, welche Daten aus welchen Tabellen ausgewählt werden sollen. Das Ergebnis einer Sicht spiegelt daher stets den aktuell in den beteiligten Tabellen vorhandenen Datenbestand wider. Die folgende Transact-SQL-Anweisung erstellt eine Sicht namens TestView. Die Sicht wählt aus der Tabelle Mitarbeiter die Felder Vorname und Zuname aus, aber nur für die Mitarbeiter, die in der Abteilung Verkauf tätig sind: 46 5 Objektverwaltung CREATE VIEW TestView AS SELECT Vorname, Zuname FROM Mitarbeiter WHERE Abteilung = 'Verkauf' Jede Datenbank enthält eine Reihe vordefinierter Sichten zur Abfrage des Datenbankkatalogs. Löschen oder ändern Sie diese Sichten nicht, da sie intern vom Enterprise-Manager verwendet werden. Für eine Schritt-für-Schritt-Anleitung zur komfortablen Erstellung einer Sicht vgl. Kapitel 8. 5.1.6 Dateigruppen Eine Datenbank kann physisch in mehreren Datendateien gespeichert werden. Es ist jedoch nicht möglich, Datenbankobjekte wie Tabellen oder Indizes direkt einer bestimmten Datendatei zuzuordnen. Eine oder mehrere physische Datendateien können jedoch logisch einer Dateigruppe zugeordnet werden. Da auch Datenbankobjekte einer Dateigruppe zugeordnet werden können, ermöglichen Dateigruppen indirekt die Festlegung des physischen Speicherortes von Datenbankobjekten. Dateigruppen werden meistens eingesetzt, um Datenbankobjekte gezielt auf verschiedene Festplatten zu verteilen. In einer Unternehmensdatenbank lassen sich zwei Arten von Tabellen unterscheiden: þ Tabellen, in denen nur selten Änderungen vorgenommen werden þ Tabellen, in denen täglich viele Änderungen vorgenommen werden Die entsprechenden Tabellen werden mit Hilfe von Dateigruppen auf verschiedene Festplatten verteilt. Diese Konfiguration þ þ vermindert Konflikte bei Lese-/Schreiboperationen auf der Festplatte und steigert dadurch die Performance des Systems erlaubt getrennte und effektive Sicherungsstrategien für die verschiedenen Tabellenarten Laufwerk D: Stamm1.ndf Stamm2.ndf Dateigruppe STAMMDATEN tblKunde tblArtikel Dateigruppe AKTUELL tblBestellung tblRechnung Laufwerk E: Aktuell1.mdf Aktuell2.ndf Verteilung von statischen und dynamischen Tabellen auf verschiedene Festplatten Notizen 47 5 SQL Server 2000 - Administration Eine Dateigruppe erstellen und zuordnen Þ Klicken Sie in der Konsolenstruktur des Enterprise-Managers mit der rechten Maustaste auf die Datenbank, in der Sie eine Dateigruppe erstellen wollen. Þ Wählen Sie im Kontextmenü den Befehl EIGENSCHAFTEN. Das Dialogfenster EIGENSCHAFTEN für Dateigruppe(n) erstellen und Dateien zuordnen diese Datenbank wird geöffnet. Aktivieren Sie die Registerkarte DATENDATEIEN. Þ Geben Sie in die erste leere Zeile den Dateinamen, den Speicherort (Pfad), die Größe des reservierten Speichers und den Namen der Dateigruppe ein. Wiederholen Sie den letzten Schritt für jede Datendatei, die Sie einer Dateigruppe zuordnen möchten. Þ Bestätigen Sie Ihre Eingaben mit OK. Þ Erweitern Sie in der Konsolenstruktur die Datenbank, in der Sie Dateigruppen verwenden möchten. Klicken Sie auf den Eintrag TABELLEN. Þ Klicken Sie im Detailbereich des Enterprise-Managers mit der rechten Maustaste auf die Tabelle, die Sie einer Dateigruppe zuordnen möchten. Wählen Sie im Kontextmenü den Befehl TABELLE BEARBEITEN. Ein neues Fenster mit der Tabellenstruktur wird geöffnet. Þ Klicken Sie auf das Symbol TABELLEN- UND INDEXEIGENSCHAFTEN wird geöffnet. . Das Dialogfenster EIGEN- SCHAFTEN Þ Wählen Sie im Kombinationsfeld TABELdie Dateigruppe aus, der Sie die Tabelle zuordnen möchten. LENDATEIGRUPPE Þ Þ Falls die Tabelle Felder vom Datentyp text, ntext oder image enthält: Wählen Sie im Kombinationsfeld TEXTDATEIGRUPPE die Dateigruppe aus, in der Sie diese Felder speichern möchten. Beenden Sie Ihre Eingaben mit SCHLIESSchließen Sie das Fenster im Enterprise-Manager. Bestätigen Sie in den folgenden Dialogfenstern das Speichern Ihrer Änderungen mit JA. SEN. Tabelle einer Dateigruppe zuordnen 5.2 Physische Datenbankobjekte 5.2.1 Dateien Physisch werden alle Datenbanken und mit ihnen alle logischen Datenbankobjekte als Dateien des Betriebssystems gespeichert. SQL Server 2000 kann Dateien im Dateiformat FAT bzw. FAT32 (Betriebssystem Windows 98) und NTFS (Betriebssystem Windows NT/2000) speichern. SQL Server 2000 kann keine Dateien auf komprimierten Laufwerken speichern. 48 Objektverwaltung 5 Jede Datenbank besteht aus zumindest zwei Dateien: þ der primären Datendatei mit der Erweiterung .mdf þ einer Protokolldatei mit der Erweiterung .ldf Eine Datenbank kann jedoch auf zusätzliche Dateien verteilt werden. Dies wird dann der Fall sein, þ wenn eine Datendatei die Kapazität eines Laufwerks überschreiten würde þ wenn eine Aufteilung von Datendateien auf mehrere Laufwerke erwünscht ist (vgl. Abschnitt 5.1.6) Zusätzliche Datendateien erhalten die Erweiterung .ndf. Alle zusätzlichen Protokolldateien besitzen ebenfalls die Erweiterung .ldf. Die Dateierweiterungen für die verschiedenen Dateitypen werden von SQL Server 2000 nicht erzwungen. Es ist jedoch zu empfehlen, die standardmäßigen Erweiterungen beizubehalten, da das Dateimanagement dadurch erleichtert wird. 5.2.2 Seiten und Blöcke Innerhalb der Datendateien werden die Daten in Seiten und Blöcken gespeichert. Eine Seite stellt die kleinste Speichereinheit dar und hat eine Größe von 8 KB. Seiten werden in Blöcken organisiert. Ein Block ist die kleinste Einheit zur Speicherreservierung und umfasst 8 Seiten, also 8 x 8 = 64 KB. Ein Block kann von einem Objekt oder von maximal acht verschiedenen Objekten genutzt werden. Bei einem einheitlichen Block sind alle acht Seiten des (neuen) Blocks für ein einziges Objekt, z.B. eine Tabelle, reserviert. Ein gemischter Block kann unterschiedliche Seitentypen beinhalten. Neue Tabellen oder Indizes werden in einem gemischten Block angelegt. Erst wenn ein Objekt die Größe von 8 Seiten überschreitet, wird in Zukunft ein einheitlicher Block für die Erweiterung reserviert. Die Organisation der Dateien in Seiten und Blöcke kann vom Datenbankadministrator nicht direkt beeinflusst werden. 5.3 Systemdatenbanken Bei jeder Installation von SQL Server 2000 werden automatisch vier Systemdatenbanken erzeugt. Diese Systemdatenbanken speichern und verwalten Daten, die Informationen über das gesamte SQL-Server-System enthalten, so genannte Metadaten. SQL Server 2000 erzeugt und aktualisiert diese Informationen automatisch und schreibt Einstellungen, die Sie mit Hilfe grafischer Werkzeuge vornehmen, in die entsprechende Systemdatenbank. Notizen 49 5 SQL Server 2000 - Administration Standardmäßig werden die Systemdatenbanken aus Sicherheitsgründen nicht im Enterprise-Manager angezeigt. Führen Sie folgende Schritte aus, um die Systemdatenbanken anzuzeigen: Þ Klicken Sie in der Konsolenstruktur mit der rechten Maustaste auf den SQL Server. Þ Wählen Sie den Befehl EIGENSCHAFTEN DER SQL SERVER-REGISTRIERUNG BEARBEITEN. Das Dialogfenster REGISTRIERTER SQL SERVER - EIGENSCHAFTEN wird geöffnet. Þ Aktivieren Sie das Kontrollfeld SYSTEMDASYSTEMOBJEKTE ANZEIGEN. TENBANKEN UND Þ Bestätigen Sie mit OK. Die vier Systemdatenbanken werden nun in der Konsolenstruktur des Enterprise-Managers angezeigt. Jede Änderung an den Systemdatenbanken kann den Ausfall von SQL Server 2000 zur Folge haben. Sie sollten daher keine direkten Änderungen in den Systemdatenbanken vornehmen, sondern stets die dafür vorgesehenen grafischen Werkzeuge verwenden. In den folgenden Abschnitten dieses Kapitels werden Systemdatenbanken anzeigen die Funktionen und der Aufbau der vier Systemdatenbanken master, model, msdb und tempdb beschrieben. 5.3.1 Die Systemdatenbank master Die Datenbank master ist der zentrale Speicherort für alle Informationen, die das gesamte Server-System betreffen. Die mehr als 60 Systemtabellen der master-Datenbank bilden den so genannten Systemkatalog, der die Metadaten des Systems enthält. Zu diesen Metadaten zählen Informationen über die Benutzerdatenbanken im System, Login-Konten und Einstellungen der Server-Konfiguration. Gespeicherte Prozeduren master beinhaltet außerdem einen Satz gespeicherter Prozeduren (ca. 670) und erweiterter gespeicherter Prozeduren (ca. 190), die im gesamten System zur Verfügung stehen. Eine gespeicherte Prozedur besteht aus einer Reihe von Transact-SQL-Anweisungen, die unter einem Prozedurnamen zusammengefasst sind und aufgerufen werden können. Der Name einer gespeicherten Prozedur beginnt meist mit dem Präfix sp_ für Stored Procedure bzw. System Procedure. Erweiterte gespeicherte Prozeduren werden in einer Programmiersprache wie C++ erstellt und als Dynamic Link Library (.DLL-Datei) gespeichert. Ihr Name beginnt ebenfalls mit dem Präfix sp_ oder auch mit xp_ für Extended Procedure. Gespeicherte und erweiterte gespeicherte Prozeduren werden verwendet, um þ Informationen über das System und über Datenbankobjekte zu erhalten þ Änderungen an der Konfiguration oder an Datenbankobjekten vorzunehmen Assistenten und die grafischen Werkzeuge des Enterprise-Managers verwenden intern oft gespeicherte Prozeduren. Dies gestattet Ihnen den komfortablen Einsatz dieser Prozeduren, ohne dass Sie sich mit der teilweise sehr komplexen Befehlszeilensyntax vertraut machen müssten. 50 Objektverwaltung 5 Die Systemprozedur sp_help gibt Informationen über ein beliebiges Datenbankobjekt aus. Starten Sie den Query Analyzer und geben Sie den Befehl sp_help ein. sp_help liefert eine Auflistung aller vorhandenen Datenbankobjekte: Liste der Datenbankobjekte (Ausschnitt) mit sp_help sp_help Objektname gibt Informationen über das jeweilige Objekt aus: Informationen über die Tabelle tblArtikel mit sp_help Eine Referenz aller Prozeduren mit Aufrufparametern und Beispielen finden Sie in der Online-Dokumentation von SQL Server 2000. 5.3.2 Die Systemdatenbank model Diese Datenbank dient als Vorlage für die Erstellung neuer Benutzerdatenbanken. Eine neue Benutzerdatenbank enthält bei ihrer Erstellung alle Systemobjekte von model, insbesondere die Systemtabellen und Sichten. Änderungen in der Datenbank model wirken sich auf jede Benutzerdatenbank aus, die nach diesen Änderungen erstellt wird. Notizen 51 5 SQL Server 2000 - Administration 5.3.3 Die Systemdatenbank msdb Die Datenbank msdb wird vom Server-Dienst SQL Server-Agent verwendet. msdb speichert z.B. þ Terminpläne für Datenbanksicherungen þ Wartungsaufträge þ die Ausgabe von Warnmeldungen bei bestimmten Ereignissen 5.3.4 Die Systemdatenbank tempdb Die Datenbank tempdb dient zum Speichern aller temporärer Tabellen und anderer temporärer Objekte, z.B. von Prozeduren. Bei der Arbeit mit einem Datenbanksystem werden oft Tabellen und andere Objekte erzeugt und verwendet, die zwischenzeitlich gespeichert werden müssen. SQL Server 2000 verwaltet alle diese Objekte in der Datenbank tempdb. Greifen viele Benutzer gleichzeitig mit komplexen Aufgaben auf den Server zu, kann die Größe dieser Datenbank drastisch anwachsen. Für die Datenbankdateien sollte daher eine automatische Vergrößerung zugelassen werden, um eine Systemüberlastung zu vermeiden. Kommt es im Betrieb von SQL Server 2000 regelmäßig zu einer Vergrößerung von tempdb, sollten Sie manuell mehr Speicher für diese Datenbank reservieren. Die automatische Vergrößerung im laufenden Betrieb kann zu Wartezeiten bei der Datenbearbeitung führen. Wird SQL Server 2000 beendet, werden alle temporären Objekte in tempdb gelöscht. Bei einem Neustart von SQL Server 2000 wird daher stets eine "leere" Datenbank verwendet, die nur die Systemobjekte enthält. Falls im Verlauf der vergangenen Sitzung eine automatische Vergrößerung der Datenbank notwendig wurde, wird die Datenbank beim Neustart wieder auf den ursprünglich gewählten Wert verkleinert. 5.3.5 Dateien der Systemdatenbanken Wie alle Datenbanken in SQL Server 2000 werden auch die Systemdatenbanken physisch als ein Satz von Dateien gespeichert. Standardmäßig werden diese Dateien im Installationsverzeichnis im Ordner DATA gespeichert. Jede Datenbank besteht zumindest aus zwei Dateien, der Datendatei und der Protokolldatei. Die folgende Tabelle gibt einen Überblick über die Dateinamen und standardmäßigen Dateigrößen: 52 Datenbank Datendatei master master.mdf model model.mdf msdb msdbdata.mdf tempdb tempdb.mdf Standardgröße in MB 11,00 Protokolldatei Standardgröße in MB mastlog.ldf 1,25 0,75 modellog.ldf 0,75 12,00 msdblog.ldf 2,25 8,00 temblog.ldf 0,50 Objektverwaltung 5.4 5 Wissens-Check Die folgenden Fragen sollen Ihnen die Einschätzung Ihrer Lernerfolge erleichtern. Falls Sie Schwierigkeiten bei der Beantwortung haben, arbeiten Sie die entsprechenden Abschnitte dieses Kapitels noch einmal durch. Die Antworten auf die Fragen finden Sie im Kapitel 17. 5.1 Welche Funktion erfüllt die Systemdatenbank model? 5.2 Erläutern Sie das Konzept der Dateigruppen. Wozu dient die Verwendung von Dateigruppen? 5.3 Für eine Tabelle existiert ein Index mit der Option EINDEUTIGE WERTE. Sie wollen nun dieser Tabelle eine Reihe von Datensätzen hinzufügen. Einige dieser Datensätze enthalten jedoch doppelte Werte. Wie wirkt sich die Option DOPPELTE WERTE IGNORIEREN auf diesen Vorgang aus? 53
