MS SQL Server 2005

Objektehierarchie und Aufbau

• (Standardinstanz | Benannte Instanz)
  • Datenbanken
    • Tabellen, Sichten
    • Prozeduren, Funktionen
    • Benutzer, Rollen
    • ...
  • Anmeldungen, Serverrollen
  • Endpunkte, Verbindungsserver, Trigger, Sicherungsmedien
  • ...

Kapazitätsplanung

Hardwareplanung

• Leistungsindikatoren

  Indikator	Beschreibung	Normwert
  Prozessor: Prozessorzeit (%)		sollte unter 75% liegen (vorzugsweise unter 50%)
  System: Prozessor-Warteschlangenlänge		unter 2 pro Prozessor
  Speicher: Seiten/s		unter 20% (vorzugsweise unter 15%)
  Speicher: Verfügbare Bytes		größer als 50MB
  Physikalischer Datenträger: Zeit (%)		unter 50%
  Physikalischer Datenträger: Durchschnittliche Warteschlangenlänge des Datenträgers		unter 2 pro Datenträger
  Physikalischer Datenträger: Durchschnittliche Lesevorgänge/s		unter 85% der Datenträgerkapazität
  Physikalischer Datenträger: Durchschnittliche Schreibvorgänge/s		unter 85% der Datenträgerkapazität
  Netzwerkschnittstelle: Gesamtanzahl Bytes/s		Dient der Bestimmung der benötigten Netzwerkbandbreite
  SQL Server: Puffer-Manager - Puffercache-Trefferquote		sollte über 90% liegen (ideal 99%)
  SQL Server: Puffer-Manager - Lebenserwartung von Seiten		sollte mehr als 300s betragen
  SQL Server: Allgemeine Statistik - Benutzerverbindungen		hiflreich für Dimensionierung des Arbeitsspeichers
  SQL Server: Datenbanken - Transaktionen/Sekunde
  SQL Server: Datenbanken - Größe der Datendatei(en) (KB)
  SQL Server: Datenbanken - Prozentsatz der Protokollverwendung
  SQL Server:Zugriffsmethoden:Erstellte Arbeitsdateien/Sekunde
  SQL Server:Zugriffsmethoden:Erstellte Arbeitstabellen/Sekunde
  SQL Server:Zugriffsmethoden:Zuordnungen gemischter Seiten/Sekunde
  SQL Server: Allgemeine Statistik: Erstellungsrate für temporäre Tabellen
  SQL Server: Allgemeine Statistik: Temporäre Tabellen zum Löschen
  SQL Server: SQL-Statistik: Batchanforderungen/Sekunde
  SQL Server: SQL-Statistik: SQL-Kompilierungen/Sekunde
  SQL Server: SQL-Statistik: Erneute SQL-Kompilierungen/Sekunde

• Arbeitsspeicher
  • Bedarf
    • Puffercache
    • Prozedurcache
    • Verbindungskontext
      • Benutzerverbindungen benötigen 500 KB je Verbindung
    • Betriebssystem + sonstige Anwendungen
  • AWE - Address Windowing Extensions

  • PAE - Physical Address Extensions

  • /3GB -

• Datenträger
  • Betriebssystem und SQL-Server-Installation auf ein Laufwerk (gespiegelt)
  • Transaktionsprotokolldateien auf ein Laufwerk (gespiegelt)
  • Datenbankdateien auf ein Raid-Laufwerk (RAID 5)

Instanzplanung

• Sicherheit
  • Datenbanken können in verschiedenen Instanzen vollständig voneinander getrennt werden
  • Benutzer(gruppen) können in verschiedenen Instanzen vollständig voneinander getrennt werden
• Organisation
  • Verschiedene Administratorgruppen für den SQL-Server (jede Gruppe bekommt eine eigene Instanz)
  • Verschiedene Organisationen als Benutzer (Jede Organisatione bekommt eine eigene Instanz)
  • Möglichkeit einzelne Instanzen zu aktualisieren
  • Physische Server konsolidieren (Instanzen statt Rechner)

Sichern und Wiederherstellen

Wiederherstellungsmodelle

• Einfach
  • Protokolliert nicht dauerhaft, Transaktionen werden aus dem Protokoll gelöscht sobald die Transaktion beendet ist
  • Keine Protokollsicherungen möglich
• Vollständig
  • Protokolliert alles
  • ProtokollDatensätze werden aufbewahrt bis sie gesichert werden
• Massenprotokolliert
  • Protokolliert Massenvorgänge nur minimal
  • Betrifft: bcp, BULK INSERT, WRITETEXT, UPDATETEXT, SELECT INTO, CREATE INDEX, ALTER INDEX REBUILD, DBCC DBREINDEX

Datenbanksicherung

• Vollständige Datenbankicherung

  BACKUP DATABASE datenbank
  TO DISK = 'pfad'
  

• Differentielle Datenbanksicherung

  BACKUP DATABASE datenbank
  TO DISK = 'pfad'
  WITH DIFFERENTIAL
  

• Transaktionsprotokollsicherung

  BACKUP LOG datenbank
  TO DISK = 'pfad'
  

• Transaktionsprotokollsicherung ohne abschneiden

  BACKUP LOG datenbank
  TO DISK = 'pfad'
  WITH NO_TRUNCATE
  

• Transaktionsprotokoll nur abschneiden

  BACKUP LOG datenbank
  WITH TRUNCATE_ONLY
  

• Dateisicherungen

  BACKUP DATABASE datenbank
  FILE | FILEGROUP
  

• Teildatensicherungen
  • Enthält standardmäßig die primäre Dateigruppe sowie alle nicht schreibgeschützten Dateigruppen, es können weitere Gruppen angegeben werden

  BACKUP DATABASE datenbank
  READ_WRITE_FILEGROUPS
  

• Kopiersicherungen
  • Führt lediglich eine Kopie als Sicherung aus. Kein Sicherungspunkt im Protokoll, kein Abschneiden.

  BACKUP DATABASE datenbank
  COPY_ONLY
  

  BACKUP LOG datenbank
  COPY_ONLY
  

Datenbankwiederherstellung

• Überprüfende Wiederherstellung (keine echte Wiederherstellung, nur Überprüfung)

  RESTORE VERIFYONLY
  FROM DISK = 'pfad'
  

• Wiederherstellung von Datenbanksicherungen

  RESTORE DATABASE datenbank
  FROM DISK = 'pfad'
  WITH NORECOVERY
  

• Wiederherstellung von Protokollsicherungen

  RESTORE LOG datenbank
  FROM DISK = 'pfad'
  WITH NORECOVERY
  

• Überprüfen der Wiederherstellung

  DBCC CHECKDB ('datenbank')
  

Sicherungsgeräte

• Sicherungsgeräte anlegen

  sp_addumpdevice

  Servereobjekte -> Sicherungsmedien -> 

Replikation und Verfügbarkeit

Datenbankspiegelung

Rollenverteilung

• Prinzipal

  Primäre Datenbank, für Anwendungen verfügbar.

• Spiegel

  Sekundäre Datenbank, befindet sich im Wiederherstellungsstatus, nicht für Anwendungen verfügbar

• Zeuge

  Überwachungssystem. Ein Zeuge kann mehrere Spiegelungssitzungen überwachen.

Betriebsmodus

• hohe Verfügbarkeit (aka: hohe Sicherheit mit automatischem Failover)
  • synchrone Datenübertragung
  • automatisches Failover
• hohe Leistung
  • asynchrone Datenübertragung
  • manuelles Failover
• hohe Sicherheit
  • synchrone Datenübertragung
  • manuelles Failover

Protokollversand

Replikation

Komponenten

• Artikel

  Beschreiben die Objekte die Veröffentlicht werden sollen

  • Tabellen
  • Sichten
  • gespeicherte Prozeduren
  • Funktionen
• Publikation

  Sammlung von Artikeln

• Filter

  Werden auf Artikel angewandt

  • Zeilenfilter
  • Spaltenfilter
  • Statischer Filter
  • Dynamischer Filter

Rollen

• Verleger
  • Datenbank
  • Enthält Originaldaten
• Verteiler
  • Instanz
  • Beinhaltet die Verteilungsdatenbank
• Abonnent
  • Datenbank
  • Empfängt Publikationen
  • Kann Publikationen von mehreren Verlegern empfangen

Codesnippets

Datenbank-Mail

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'mail_profile',
	@recipients = 'adrsesse@domain.tld', 
	@subject = 'Betreff',
	@body = 'Text';

Servername abfragen/ändern

exec sp_dropserver 'altername'
go
exec sp_addserver 'neuername','LOCAL'

SELECT @@SERVERNAME
sp_helpserver

Datenbanken abfragen

SELECT * FROM sys.databases

sp_databases;

Tabellen abfragen

sp_tables;

Version abfragen

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SqlCmd.exe verwenden

sqlcmd -S servername -U username -P password -d databasename -Q "SELECT @@servername;"

Weitere Stichworte

• Volltextkataloge
• Aufbau
  • Volltextkatalog
    • Volltextindex
    • Volltextindex
    • ...
• Abfrageformen
  • FREETEXT
  • FREETEXTTABLE
  • CONTAINS
  • CONTAINSTABLE
• Instanzen
• Datenbanken
• Datenbanksnapshots
• Datenbankspiegelung
• Protokollversand
• Replikation
• Sichern und Wiederherstellen
• Tabellen
• Datentypen
• Indizes
• Sichten
• Partititionen
• Funktionen
• Prozeduren
• Trigger
• SQL Server Agent
• SQL Server Profiler
• Datenbankmodul-Optimierungsratgeber
• Verwaltungssichten und -Funktionen
• Service Broker