Glorf IT

Heute bemerkten wir zufällig, dass im Rahmen der Neuinstallation einer MSDEs mit integriertem SP4 wird der Gruppe "Public" das Leserecht auf die Tabelle master.dbo.sysxlogins nicht gewährt. Bis inklusive SP3 wurde das Recht vergeben. Bei einem Update mit SP4 bleibt das Recht erhalten. Das habe ich in der Doku zu SP4 nicht gefunden bzw. übersehen.

Ich weiss schon, dass man auf die Tabelle eigentlich sowieso nicht zugreifen soll, aber wer es dennoch tut, sollte wissen, dass er auf zwei Sorten von MSDEs stoßen kann: Welche mit dem Recht und welche ohne.. 😉

Heute entdeckte ich durch Zufall, dass ein ganz tolles, dokumentiertes Feature aus dem "SQL Server 2005" auch schon (undokumentiert) im "SQL Server 2000" funktioniert.

Bevor ich eine Procedure oder View anlege, prüfe ich immer zuerst, ob es sie schon gibt und lösche Sie dann gegebenenfalls. "Früher" nutzte ich dazu die Systemtabellen, Z.B. so:

if exists(select *
from sysobjects
where name = N'MyProc'
and type = N'P')
drop procedure MyProc
go
Create procedure MyProc
as
select N'tolles Beispiel' as "Info"

Nachdem ich oft genug gehört hatte, dass man keine direkten Zugriffe auf die Systemtabellen machen soll, weil sie irgendwann (Z.B. wurde mit Yukon gedroht) abgeschafft würden, habe ich das (völlig umsonst) umgestellt:

if objectproperty(object_id(N'MyProc'), 'IsProcedure') = 1
drop procedure MyProc
go
Create procedure MyProc
as
select N'tolles Beispiel' as "Info"

Heute entdeckte ich, dass auch am 2000er die Funktion object_id um den Typ erweitert werden kann:

if object_id(N'MyProc', N'P') is not null
drop procedure MyProc
go
Create procedure MyProc
as
select N'tolles Beispiel' as "Info"

Mit diesem schicken Feature aus dem 2005er ist es doch gleich viel handlicher… 🙂

Bei der PC-Welt gibt es das Handbuch "Datenrettung" von Ontrack zum kostenlosen Download. Der Preis besteht in dem Umstand sich einen (kostenlosen) Account bei der PC-Welt (bzw. dem Verlag IDG) anlegen zu müssen.

Die Lektüre der insgesamt 116 Seiten lohnt sich meines Erachtens für alle, die für die Sicherheit bzw. Sicherung von Datenbeständen verantwortlich sind. Es liest sich flüssig und ist sehr informativ. Auch für mich sind neue Infos dabei.

Im Gegensatz zu der in den Medien oft verbreiteten Meinung spielen
Naturkatastrophen als Ursache für Datenverlust nur eine untergeordnete
Rolle. Tatsächlich beruhen drei Viertel aller Schadensfälle
auf Störungen an der Hardware oder auf Bedienungsfehlern.

Als Hauptursachen werden genannt (aus einer Ontrack-Studie von 2002):

• 44% – Funktionsstörungen der Hardware oder des Systems
• 32% – Bedienungsfehler
• 10% – Software-Fehler o. Funktionsstörungen von Software
• 7% – Computerviren
• 3% – Höhere Gewalt (Naturkatastrophen, Brände etc.)
• 4% – Sonstiges

Das die Bedienungsfehler (z.B. versehentliches Überschreiben) so häufig sind, hätte ich nicht gedacht. Allerdings bekomme ich davon auch nichts mit: unsere Kunden wenden sich nur an uns, wenn die Dateien noch da aber defekt sind. 😉

Zu den Ursachen, die defekte Datenbanken haben können, verweise ich auch gerne auf meine Serie zu dem Thema.

Etwas das mir beim Microsoft SQL Server 2005 besonders gut gefällt, sind die Ranking-Functions: row_number, rank, dense_rank und ntile:

Hier ein Aufrufbespiel:

SELECT OrderID, Quantity,
row_number() OVER (ORDER BY quantity) AS "row_number()",
rank() OVER (ORDER BY quantity) AS "rank()",
dense_rank() OVER (ORDER BY quantity) AS "dense_rank()",
ntile(10) OVER (ORDER BY quantity) AS "ntile(10)"
FROM Northwind.dbo."Order Details"
WHERE ProductID=1
ORDER BY Quantity

row_number liefert eine Nummerierung. Dabei muss man aber beachten, dass die Nummerierung nur dann eindeutig reproduzierbar ist, wenn die Sortierung präzise ist. Im obigen Beispiel kann es mehrere Order-Details mit der gleichen Anzahl geben. Deswegen ist die Row_Number hier "nicht deterministisch", sondrn könnte beim nächsten Aufruf anders Lauten. Die Abhilfe ist ganz einfach: Die Order-BY so erweitern, dass sie eindeutig ist.

ntile(n) verteilt die Datensätze gleichmäßig in n Töpfe. Die Töpfe werden dabei nummeriert. Die Reihenfolge basiert intern auf der Row_Numer. Deswegen ist es auch bei NTile von der genauen Sortierung abhängig, ob die Werte deterministisch sind oder nicht. Die Töpfe werden dabei immer reihum aufgefüllt, d.h. die vorderen Töpfe haben in der Regel ein Mitglieder mehr als die hinteren.

rank liefert den Rang, wie bei den olympischen Spielen: Wenn zwei den gleichen Werte haben, dann teilen sie sich den ersten Platz und der nächste sitzt auch Platz 3. Rank ist deswegen immer deterministisch.

dense_rank liefert den Rang, ohne Lücken. Auch diese Funktion ist immer deterministisch.

Wer sie noch nicht kennt, dem würde ich sie sehr ans Herz legen. Vertiefende Infos, insbesondere zu den Alternativen am Microsoft Sql Server 2000 werden in folgenden Beispielen von Itzik Ben-Gan besonders gut beschrieben (in den Beispielen nach "Rank" suchen, sie sind gut kommentiert).
Für die Vertiefung empfehle ich den Einsatz mit Partitions (in Gruppen durchzählen).

Bei Google-Trends kann man nicht nur die "Nachfrage" nach Suchphrasen ansehen, sondern auch zwei gegeneinander vergleichen:

Interessant finde ich beispielsweise den Vergleich der Google Trends zu den Stichwörtern
"sql server 2005" und "sql server 2000":

Die Nachfrage hat beim "SQL Server 2000" viel weniger stark abgenommen als sie beim "SQL Server 2005" zunahm…

Heise-Online schreibt im Artikel"SQL Anywhere 10 kommt im September":

Als "Snapshot Isolation" bezeichnet iAnyhwere ein weiteres Isolation Level, das konkurrierende Zugriffe auf Datensätze beschleunigt. Es sorgt dafür, dass der Server bei lesenden Zugriffen die letzte mit COMMIT geschriebene Version der Daten liefert. Dadurch sollen schreibende und lesende Zugriffe auf die Datenbank gleichzeitig möglich sein. Eine weitere Neuerung sind "materialized Views", die ähnlich wie Tabellen Daten aus Abfragen dauerhaft speichern.

Als jahrelanger Fan vom SQL-Anywhere (1996 bis 2002) freut es mich zu hören, dass es ein neue Version geben wird und dass die guten Dinge aus dem Microsoft-SQL-Server weiterhin ungeniert kopiert werden. Damals erleichterte es uns die Kompatbilität zwischen den Systemen sehr (sogar xp_cmdshell kann der SQL-Anywhere… :-)). Vermisst habe ich damals immer die Clustered-Indexes, immerhin kommt jetzt mit den "materialized Views" (die beim MSSQL mittels Clustered-Index auf Views gelöst sind, auch auf MSDE) ein ähnlich nützliches Feature. Die Geschichte mit der "Snapshot Isolation" ist auch gut kopiert, denn das ist wirklich ein ausgesprochen nützliches Feature bei MSSQL.

Früher krankte das System daran, dass die Vermarktung von Sybase nicht offensiv genug betrieben wurde, denn das System sollte ja dem großen Bruder "Adaptive Server Enterprise" nicht das Wasser abgraben. Das hat Sybase jetzt wohl aufgegeben, denn jetzt endlich können Queries auf mehrere Prozessoren aufgeteilt werden. Aber das kommt wahrscheinlich zu spät, denn warum sollte man unter Windows Geld für eine gute Kopie ausgeben, wenn man mit MSDE oder SQL Express umsonst das Original haben kann?

Im vorherigen Teil der Serie zum Vorgehen bei Datenbank-Reparaturen am SQL Server 2000 wurde erklärt, wie man vorgeht, wenn das Tranlog defekt ist und die Datenbank ordentlich geschlossen wurde, d.h. das Transaktionslog im Prinzip leer ist. Trotz Urlaub will ich daran anknüpfen:
Was macht man aber, wenn die Datenbank nicht geschlossen wurde, sondern der Server abstürzte, weil die Festplatte langsam schrottet und just da auch noch das Tranlog rüber rudert? Das ist ein häufiges Szenario, weil das Tranlog sehr oft zugegriffen wird. Und deswegen besonders oft von Festplattenproblemen betroffen ist.

In diesem Fall muss wird die "Reparatur" etwas fummelig. Wenn man Glück hat, dann ist die Datenbank noch am SQL Server angehängt, aber als suspekt markiert. Wenn man den vorherigen Tipp ausprobiert hat ohne vorher die Master-Datenbank zu sichern oder einem die DB-Dateien zur Untersuchung zugeschickt wurden, dann muss man der SQL-Server erst mal austricksen, um die defekte Datenbank an den Server angehängt zu bekommen. Wenn man dann soweit ist, dass die Datenbank angehängt, aber suspekt ist, dann ist es nicht mehr so schwierig. Mit beiliegendem Statement bekommt man übrigens eine Liste aller Datenbanken mit deren Status:

select name, databasepropertyex(name, 'status')
from master.dbo.sysdatabases

Im Folgenden wird erklärt, wie man das Tranlog löscht und vom SQL Srever ein Neues anlegen lässt. Da im Tranlog aber eventuell wichtige Daten standen, erzeugt man dadurch möglicherweise einen inkonsistenten Datenbestand.

(1) Datenbank in den "Emergency-Mode" schalten. Beim nächsten Start wird für die Datenbank kein Recovery versucht:

-- Änderungen in den Systemtabellen erlauben
exec sp_configure 'allow update', 1
reconfigure with override
go
– Datenbank in den Emergency mode setzen
update master..sysdatabases
set status = status | 32768
where name = 'MyCrashDB'
go
– Änderungen in den Systemtabellen verbieten
exec sp_configure 'allow update', 0
reconfigure with override

(2) SQL-Server-Dienst stoppen und neu starten. Erst danach ist die Datenbank im Emergency-Mode.

(3) Jetzt kann ein neues Transaktionslog angelegt werden. Vorher muss man die alte Tranlog-Datei noch schnell umbenennen (oder löschen, denn man hat ja vorher eine Kopie gemacht, oder?)

-- Log neu erzeugen lassen:
DBCC rebuild_log ('MyCrashDB')

Das Ergebnis sieht etwa so aus:

Warning: The log for database 'MyCrashDB' has been rebuilt. Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset, and extra log files may need to be deleted.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(4) Jetzt muss man die DB nur wieder auf "normal" schalten:

alter database MyCrashDB
set online

Und schon hat man es geschafft.
Die gute Nachricht ist, dass man beim SQL Server 2005 nicht mehr in den Systemtabellen rumpfuschen muss. Die schlechte ist, dass immer noch viel Handarbeit notwendig ist. Naja, aber so arme Hunde wie ich müssen ja auch leben… 😉

Im dritten Teil geht es dann um Reparaturen rund um DBCC CHECKDB.

Ich staune immer wieder über das Urvertrauen, dass Menschen ihrer EDV-Anlage entgegen bringen…

Kürzlich passiert: Ein Kunde hat keine Probleme, aber lässt ganz brav mal eine Datenbank-Prüfung durchlaufen. Dabei werden Fehler gefunden und angezeigt. Aber der Kunde ignoriert das zwar, aber mit sowas rechnet unsere Software… 😉
Von diesem Zeitpunkt an weisst unsere Software den Anwender nun ganz tapfer bei jedem Programmstart darauf hin, dass beim letzten Prüflauf Fehler gefunden wurden und dass er dringend etwas unternehmen soll… Z.B. mal eine Reparatur anstoßen oder unseren Service anrufen.
Im konkreten Fall haben die Anwender (sie kommt bei JEDEM: Chef und Mitarbeiter) die Meldung ebenso tapfer weggeklickt und einfach weitergearbeitet.

"Aufgeflogen" ist das Ganze nur, weil unser Datentrafo (im Rahmen eines Programm-Updates) sich weigerte auf einer defekten Datenbank loszulegen. Bei der Reparatur im Haus konnte nur eine Reparatur mit Datenverlusten durchgeführt werden. Einige "ältere" Daten sind einfach weg. Und weil der Defekt schon so lange besteht, kann der Kunde auch nicht einfach auf den letzten sauberen Stand zurücksetzen… Ich befürchte, dass der Kunde nicht nach der Ursache suchte, sondern jetzt wieder zufrieden ist.

Meine Erfahrung ist, dass PCs hier zu menschlich behandelt werden: "Jeder macht mal einen Fehler, Schwamm drüber." Meiner Ansicht nach ist Checkdisk schon recht einfach. Es wird nicht gemacht, weil das Bewusstsein für das Risiko nicht da ist.

Ich will nicht zu sehr mit Steinen werfen, denn ich bin in den meisten Dinge auch nicht besonders konsequent. Wenn allerdings meine berufliche Existenz davon abhängen würde… 😉

Am Sybase SQL Anywhere, der zwischendrin mal auf den schönen Namen "Sybase Adaptive Server Anywhere" hörte, gibt es ein Feature, was ich am "Microsoft SQL Server" besonders vermisse: globale Variable, die während einer gesamten Session erreichbar sind.
Sie haben einen ähnlichen Gültigkeitsbereich, wie "normale" temporäre Tabellen beim SQL Server. Man deklariert sie einmal, kann der Wert dann beliebig setzen und in allen Batches in dieser Session darauf zugreifen.

Eigenartigerweise bietet der SQl Srever ein Featrue an, das etwas in diese Richtig geht:
mit CONTEXT_INFO kann man genau einen 128 Bytes langen Wert speichern.

Beispiel

Angenommen eine Anwendung muss in Ausnahmenfällen mal eine Verbindung zu einer Datenbank aufbauen, um eine Tabelle zu erweitern oder ein Backup durchzuführen. Normale Benutzer haben dazu nicht die entsprechenden Rechte. Die Anwendung meldet sich mit dem Datenbank-Besitzer "MyDBO" an, um bspw. ein neues benutzerdefiniertes Feld anzulegen. Selbstverständlich werden alle ändernden Operationen in einer Tabelle protokolliert, dazu kann der Benutzername mittels "user_name()" ermittelt werden, z.B. "Thomas".
Die Dinge aus der Admin-Verbindung sollen auch protokolliert werden. Hier soll jetzt aber nicht der Name "MyDBO" notiert werden, sondern der Verursacher: "Thomas".

Zu diesem Zweck kann man gleich nach dem Aufbauen der Vrebindung den Namen des Benutzers in die CONTEXT_INFO schreiben, falls es sich um so eine Sonderverbindung handelt:

declare @context_info varbinary(128);
set @context_info = cast('thomas' as varbinary(128))
set context_info @context_info

Um den Wert abzufragen, muss man beim SQL Server 2000 tief in die Trickkiste greifen und eine Systemtabelle abfragen:

select cast(context_info as varchar(128))
from sysprocesses
where spid=@@spid

Wenn man nun wissen will, wer die zu protokollierende Aktion veranlasst hat, kannman das so erfragen:

declare @user_info varchar(128);
set @user_info = (select cast(context_info as varchar(128)) from sysprocesses where spid=@@spid)
select case
when @user_info = ''
then user_name()
else @user_info
end as "User_name"

Am SQL Server 2005 hat sich daran nicht viel verändert. Immerhin muss man jetzt nicht mehr die Systemtabelle abfragen:

SELECT cast(CONTEXT_INFO() as varchar(128))

Im konkreten Beispiel würde sich das vereinfachend auswirken:

select case
when cast(CONTEXT_INFO() as varchar(128)) = ''
then user_name()
else cast(CONTEXT_INFO() as varchar(128))
end as "User_name"

Das ist für den Obige Fall schon eine akzeptable Lösung. Leider gibt es damit nur eine einzige "globale" Variable. Wenn man mehr benötigt, dann muss man sich das in temporären Tabellen speichern. Schade, der Overhead wäre zu vermeiden…

Im ersten Teil der Reihe zum Vorgehen bei Datenbank-Reparaturen wurde erklärt, wie man vorgehen kann, wenn man eine Datenbank an einen SQL Server 2000 anhängen will, das aber nicht klappt, weil die Datenbank kaputt ist.

In diesem Teil geht es darum, wie man vorgehen kann, wenn das Transaktionslog defekt ist oder es von jemandem gelöscht wurde, der es für eine "ganz normale Log-Datei" hielt, die man ruhig löschen kann. Diese Irrlehre hält sich noch in einigen Köpfen… 😉

Wenn das Transaktionslog futsch oder defekt ist, muss man beachten das eine inkrementelle Sicherungsstrategie damit schwere Rückschläge bekommt. Mann muss sofort nach der Reparatur eine Voll-Sicherung durchführen. Wie man bei der Wiederherstellung vorgeht, hängt vom Zustand der Datenbank ab. Wenn die Datenbank ordentlich geschlossen wurde und das Tranlog defekt ist, dann hat man sehr gute Chancen ohne Datenverluste aus der Geschichte rauszukommen. Wichtig ist immer, dass man vor dem Beginn der Analyse Kopien der Datenbank-Dateien anfertigt! Ich rate dazu den SQL Server zu stoppen und auch die Master-Dateien zu sichern.

Beispiel aus der Praxis: Ein Mitarbeiter hat etwas zu viel gelöscht. Jetzt wird der Admin gebeten die Sicherung vom Vortag wieder einzuspielen. Der Admin überschreibt die Dateien von heute mit der Sicherung von gestern (Festplattenvollsicherung bei gestopptem SQL Server, benutzt wird Simple Recovery Mode). Unerklärlicherweise lässt sich die Datenbank danach nicht zugreifen. Analyse: Tranlog-Datei völlig geschrottet. Die Datenbank wurde als suspekt markiert, weil kein Recovery durchgeführt werden konnte.

In diesem Beispiel kann man vergleichsweise einfach zum Ziel kommen. Die Datenbank-Datei wurde ordnungsgemäß geschlossen. Daher enthält das Tranlog keine Infos, die nicht schon in der Datenbank eingepflegt wurden. Man kann den SQL Server "einfach" ein Neues anlegen lassen:

(1) SQL Server stoppen, LDF umbenennen (oder löschen, man hat ja eine Kopie), SQL Server starten.

(2) Datenbank trennen und nur mittels der MDF wieder anhängen:

exec sp_detach_db 'MyCrashDB'

exec sp_attach_single_file_db 'hahaha',
'e:\Programme\Microsoft SQL Server\MSSQL$MYLITTLESQL\data\MyCrashDB.MDF'

Man bekommt beim Anhängen zwar einen Fehler, aber die Datenbank lässt sich prima wieder anhängen.

Device activation error. The physical file name 'e:\Programme\Microsoft SQL Server\MSSQL$MYLITTLESQL\data\MyCrashDB_log.LDF' may be incorrect.
New log file 'e:\Programme\Microsoft SQL Server\MSSQL$MYLITTLESQL\data\MyCrashDB_log.LDF' was created.

Und fertig ist die Lauge. Herzlichen Glückwunsch. Man sollte danach in jedem Fall eine neue Datensicherung machen. Nicht nur, wenn man ein inkrementelles Backup verwendet.

Im nächsten Teil geht es dann darum, was man macht, wenn die Datenbank nicht sauber heruntergefahren wurde.

Gestern fragte jemand in der Newsgroup microsoft.public.de.sqlserver wie man die Primärschlüssel-Felder aus INFORMATION_SCHEMA-Tabellen ermitteln kann.
So geht:

select CU.COLUMN_NAME
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE as CU
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC
on CU.CONSTRAINT_CATALOG=TC.CONSTRAINT_CATALOG
and CU.CONSTRAINT_SCHEMA=TC.CONSTRAINT_SCHEMA
and CU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
where CONSTRAINT_TYPE = N'PRIMARY KEY'
and TC.TABLE_CATALOG = N'AdventureWorks'
and TC.TABLE_SCHEMA = N'Person'
and TC.TABLE_NAME = N'MyAddress'
order by ORDINAL_POSITION asc

Damit bekommt man eine Liste der Felder in der richtigen Reihenfolge.

Vor ein paar Tagen hat Paul Randall in seinem Artikel Can't I ever get a guarantee? diskutiert, ob man sicher sein kann, dass alles OK ist, wenn der DBCC-CheckDB-Lauf keine Fehler meldete. Er weißt zu Recht darauf hin, dass seit der Prüfung der ersten Seiten inzwischen genau dort ein Problem aufgetreten sein könnte. Das ist natürlich etwas spitzfindig, aber es ist nicht ganz von der Hand zu weisen. Generell bringt sie aber schon eine deutliche Sicherheit, ob das was man sichern will, auch OK ist.

Kann eine Prüfung schaden? Als wir noch den Sybase SQL-Anywhere einsetzten, kam einmal der Verdacht auf, dass eine Datenbank durch die Prüfung zerstört worden sein soll. Dazu muss man wissen, dass man ihn so einstellen kann, dass die Datenbank-Dateien generell geschlossen sind und nur geöffnet werden, wenn jemand darauf zugreift. Zwischen zwei Prüfläufen wurde die Datenbank angeblich nicht angfasst und dennoch war sie bei der vorletzten Prüfung OK und bei der Letzten wurde ein Fehler gemeldet.

Wenn man etwas wackelige Hardware einsetzt, z.B. ein defektes RAM im RAID-Controller, dann kann es schon sein, dass die Datenbank durch das Öffnen/Schließen und den damit verbundenen Checkpoints defekt wird.
Aber bevor ich wegen dieser Möglichkeit mein Sicherungskonzept überdenke, würde ich lieber vor der Datenbank-Prüfung eine Hardware-Prüfung durchführen…