.NET und PDF Software

Kurz nach der Migration einiger Datenbank von einem SQL Server 2008 auf einen SQL Server 2016 ist aufgefallen, dass es große Sprünge in den Identity-Spalten gab, die nicht durch verworfene Datensätze erklärt werden konnten. Dabei ging es jeweils um Sprünge von ca. 1000 oder sogar 10.000. Die Ursache ist in der MSDN zu finden:

The identity property on a column does not guarantee the following: … Consecutive values after server restart or other failures – SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert.

Dieser Sprung beträgt bei Spalten mit bigint aber bis zu 10.000, bei Spalten mit int immerhin noch 1.000. Da ist sehr viel und bringt einen in einen Erklärungsbedarf. Basierend auf diesem Beitrag lässt sich das Problem leicht nachstellen:

CREATE TABLE MyTestTable(Id INT IDENTITY(1,1), Name VARCHAR(255));
 
DECLARE @i INT = 100000
 
While @i > 0
BEGIN
    INSERT INTO MytestTable (Name) VALUES ('Test2')
    SET @i = @i-1
END

Während diese Schleife läuft (das Problem tritt nur bei Tabellen in Verwendung auf!), den SQL Server neu starten. Dann

INSERT INTO MyTestTable(Name) VALUES ('Mr.Jackson'); 
 
SELECT Id, Name FROM MyTestTable ORDER BY Id DESC;

Damit sollte das Problem sichtbar sein. Eine Option ist die Nutzung von Sequenzen mit der NOCACHE Option, anstatt auf IDENTITY Spalten zu setzen. Wenn diese Option zu aufwendig ist, kann auch die Option -T272 für die komplette Instanz genutzt werden. Damit ist das alte Verhalten (bis SQL 2008) wiederhergestellt.

Es bleibt noch anzumerken, dass das TRACE Flag T272 das Caching-Verhalten für die Generierung von Identity-Werten deaktiviert. Negative Auswirkungen auf die Performance beim Erzeugen neuer Datensätze sind somit möglich.

Immer wieder steht man vor der Aufgabe, Datenmengen innerhalb einer Tabelle zu kopieren. Typisierte Datenzugriffsschichten, wie das Entity Framework, bieten zwar viel Komfort, lassen aber zukünftige DDL Updates außen vor. Ein Update des Datenmodells, speziell mit ergänzten oder gelöschten Spalten erzwingt dann automatisch ein Update der Anwendung, wenn die Kopierfunktion vollständige Kopien anlegen soll. Bei oft geänderten Tabellen wir das zu einem Problem. Zudem sind komplexere Kopieroperationen direkt auf dem Server mittels einer StoredProcedure meist wesentlich schneller.

Hier bietet sich natürlich die Lösung mit einem INSERT SQL an:

INSERT INTO MyTable SELECT mt.* FROM MyTable mt;

Das Problem mit diesem Befehl sind aber IDENTITY Spalten, die nicht übergeben werden dürfen. Dies kann man natürlich durch Angabe der Spaltennamen umgehen. Damit landet man aber wieder am Anfang des Problems. Die Spaltennamen müssen fest definiert oder zuvor aufwändig ermittelt werden.

Eine andere Lösung lässt sich jedoch über temporäre Tabellen entwickeln. Man erstellt mit SELECT INTO eine temporäre Tabelle mit den benötigten Daten. Deren Identity-Spalte kann man problemlos verwerfen und danach funktioniert ein INSERT INTO ohne Problem:

CREATE PROCEDURE dbo.CopyTable @SourceId BIGINT
AS 
  SET NOCOUNT ON;
 
  IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable;
 
  SELECT mt.* INTO #MyTable FROM MyTable mt WHERE mt.Id = @SourceId;
 
  ALTER TABLE #MyTable DROP COLUMN Id;
 
  INSERT INTO MyTable SELECT mt.* FROM #MyTable mt;
 
  IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable;
 
  SET NOCOUNT OFF;
GO

Ein weiterer Vorteil ist das einfach Aktualisieren der neuen Datenmenge in der temporären Tabelle.

Regelmäßig nach einer Wiederherstellung einer bestehenden Datenbank auf einem neuen Server müssen die Logins, die serverseitig gespeichert und angelegt werden, mit dem User der Datenbank verbunden werden. Ansonsten erhält der Login auf die Datenbanken einfach keinen Zugriff.

Dafür gibt es drei Möglichkeiten:

• Man kann den User in der Datenbank entfernen und den User einfach auf Serverebene neu anlegen. Das SQL Managmentstudio bietet hierbei die Möglichkeit, die Rechte auf einer Datenbank zu erteilen. Aber dabei verliert man ALLE bereits konfigurierten Rechte, die der User in der Datenbank hatte. Diese Lösung ist somit nicht empfehlenswert.
• Der Microsoft SQL Server bietet die Procedure sp_change_users_login, mit der ein neu angelegter Login im Server mit dem User der Datenbank verbunden werden kann. Ich nehme dafür eigentlich immer einen identischen Namen, um Verwechslungen zu vermeiden:

  EXEC sp_change_users_login 'Update_One', 'User1', 'User1';

  EXEC sp_change_users_login 'Update_One', 'User1', 'User1';

  Dieser Befehl muss für jeden User in jeder Datenbank ausgeführt werden, kann aber leicht in einem Skript vorbereitet werden.

• Abgelöst wird diese vorstehende Procedure durch folgenden SQL Befehl: ALTER USER. In der Anwendung kann man dann schreiben:

  ALTER USER User1 WITH LOGIN = User1;

  ALTER USER User1 WITH LOGIN = User1;

  Auch dieser Befehl muss für jeden User in jeder Datenbank ausgeführt werden und kann leicht in einem Skript vorbereitet werden.

Seit einigen Monaten nervte in einer Microsoft SQL 2008 Datenbank ein Problem, welches gelegentlich auftrat. Auf die betreffende Datenbank waren ab einem scheinbar zufälligen Zeitpunkt keine schreibenden Zugriffe mehr möglich, lesende Zugriffe aber schon. Ein einfacher Blick nach der Verfügbarkeit reichte also nicht, um die Lauffähigkeit der Datenbank zu testen.

Ein Blick in die Log-Dateien brachte die Erkenntnis, dass diese Datenbank ziemlich oft gestartet wurde (im Gegensatz zu den anderen Datenbanken) und plötzlich ein Fehler 1205 auftrat:

Transaction (Process ID …) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Danach war die Datenbank schreibend nicht mehr verfügbar, der Fehler 9001 trat auf:

The log for database ‚…‘ is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Select – Abfragen waren davon aber nicht betroffen, da diese ja keine Log-Einträge erzeugen.

Die Lösung wurde in SQL ServerCentral.com beschrieben: Abschalten der Option AutoClose in den Eigenschaften der Datenbank! Da diese Eigenschaft weder bewußt gesetzt worden war, noch benötigt wurde, konnte das Problem so einfach gelöst werden.

Manchmal ist man in der Situation, dass man keinen Zugriff mehr auf einen SQL Server mehr hat, obwohl man selbst Administrator der darunter liegenden Windows-Version ist. In diesem Fall hat Microsoft dafür gesorgt, dass man recht einfach die Administrationsrechte für den SQL Server erlangen kann. Der meiner Meinung nach einfachste Weg geht so:

Man startet den SQL-Server-Konfigurationsmanager und öffnet die Eigenschaften des gewünschten SQL-Dienstes. Bei aktuellen Version hat man nun die Möglichkeit, direkt den Parameter -m hinzuzufügen. Dieser Parameter startet den SQL-Server Einzelbenutzermodus und gibt allen lokalen Administratoren Zugriff. Alternativ muss man bei älteren Versionen auf der Seite erweitert den Parameter direkt in der Auflistung angeben. Aber Achtung, dort werden die Parameter mit ; getrennt. Es muss dann also lauten:

-m;--dC:\Program Files\Microsoft S...

Anschließend muss der Dienst neu gestartet werden. Nun kann man sich mit dem SQL Server Managementstudio direkt verbinden. Aber auch hier Achtung: Das Programm muss dazu unbedingt als Administrator gestartet werden, selbst wenn unter Windows als Admin angemeldet ist! Nun kann man seinen Admin-Login hinzufügen und mit den notwendigen Rechten versehen. Zum Abschluss nicht vergessen, den Parameter -m wieder zu entfernen, damit der Server normal zur Verfügung steht.

Eine Abfrage im SQL Server enthält eine time-Spalte:

SELECT [Activity], [Duration] FROM TableName

Die Aufgabenstellung bestand darin, die Zeiten zu addieren. Leider wurde dieser Versuch

SELECT SUM([Duration]) FROM TableName

abgelehnt:

Der Operanddatentyp time ist für den sum-Operator ungültig.

was zwar nicht einleuchtend, aber auch nicht zu ändern war.
Die Lösung war die vorherige Umwandlung der Zeit in eine Datumsdifferenz vom Typ Minuten:

SELECT SUM(DATEDIFF(MINUTE, '00:00:00', [Duration])) FROM TableName

Eher zufällig ist mir der SQL

SELECT CONVERT (datetime, CONVERT (VARCHAR, GETDATE(), 101), 101);

aufgefallen (natürlich ursprünglich in einem größeren Kontext). Schnell war die scheinbare Optimierung durchgeführt:

SELECT GetDate();

Diese habe ich dann zum Glück noch einmal überprüft und konnte doch eine Abweichung feststellen: GetDate liefert das aktuelle Datum inkl. Uhrzeit zurück. Der doppelte Convert entfernt einfach die Uhrzeit. Das ist immer dann sinnvoll, wenn Datumswerte ohne Uhrzeit gespeichert werden und man mit diesen Rechnen möchte.

In der MSDN ist auch erklärt, was der Parameter 101 im Convert bewirkt: Es wird ein Datum im Format mm/tt/jjjj als String erzeugt.

Bei der Suche nach einem praktischen SQL-Skript für eine Statistik, bin ich auf die Möglichkeit gestoßen, den SQL Count Befehl zu verwenden:

SELECT a.BewertungId, 	
 COUNT(*) Gesamt,
 COUNT(E_Datum) Offen, 
 COUNT(CASE WHEN ((a.Abgelehnt = 0) OR (b.OFI = 0)) THEN NULL ELSE 1 END) Abgelehnt
FROM Aktivitaeten a, Bewertung b
WHERE a.BewertungId = b.Id
GROUP BY a.BewertungId
ORDER BY a.BewertungId

Die Nutzungsmöglichkeit des COUNTs ist dabei vielfältig:

• Üblicherweise wird Count mit dem Parameter (*) aufgerufen. Dieser Aufruf führt zur vollständigen Zählung der Datenmenge.
• Gibt man aber als Parameter einen Spaltennamen ein, so werden nur die Datensätze gezählt, die in der gewünschten Spalte einen Eintrag ungleich NULL haben.
• Aber auch die Angabe eines Ausdrucks, der NULL oder einen Wert liefert, ist möglich.

Das aufgeführte Beispiel zählt also auf einmal: alle Datensätze, alle Datensätze mit einem Eintrag in der Spalte E_Datum und als letzten Punkt alle Datensätze, die eine Ablehnung enthalten.

Immer wieder habe ich den Fall gehabt, eine Sicherung eines Microsoft SQL Servers einzurichten. Dabei ging es meist nur um kleine Datenbanken, manchmal sogar unter der MSDE 2000 oder dem SQL Server 2005 Express. Dazu wurden dann einfach die Dienste gestoppt und die Dateien wegkopiert.

Gerade auf Webserver war die Lösung unbefriedigend. Darum habe ich mir eine neue Lösung gebastelt. Der SQL-Server bietet zwei Kommandozeilenbefehle: bcp und osql.

bcp bietet einem die Möglichkeit, die Ausgabe eines SQL-Befehls in eine Text-Datei zu leiten. Nutzt man nun einen Select, um alle Datenbank zu ermitteln:

SELECT [name] FROM master..sysdatabases

und erweitertert das Ergebnis um den BACKUP DATABASE Befehl, so hat man eine gute Möglichkeit, sich ein Backupskript dynamisch zu erzeugen:

bcp "Select N' use master', N'GO' UNION Select N'BACKUP DATABASE [' + [name] + N'] TO DISK = N''C:\BackupDB\' + [name] + N'.bak'' WITH FORMAT', N'GO' From master..sysdatabases"
 queryout C:\BackupDB\backupskript.sql -S ".\SQLEXPRESS" -T -c -t \r\n

Das sieht ein wenig unübersichtlich aus, da man jeden Befehl in eine neue Zeile bekommen muss (-t \r\n) und zudem nach jedem BACKUP DATABASE Befehl einen GO benötigt. Ist der Befehl durchgelaufen, sollte das Backupskript vorliegen.

Nun ist nur noch dafür zu sorgen, dass das Skript auch aufgerufen wird:

osql -S .\SQLEXPRESS -E -i C:\BackupDB\backupskript.sql

Läuft alles problemlos durch, wird so bei dem Aufruf beider Befehle ein vollständiges Backup alle Datenbanken des angegebenen SQL-Servers gemacht. Getestet habe ich es mit dem Microsoft SQL Server 2005, der MSDE 2000 und dem Microsoft SQL Server 2005 Express Edition.