code it

Martins Tech Blog

Mit dem Service Broker auf Datenänderungen reagieren

Im SQL Server gibt es seit längerem den SQL Server Service Broker. Mit dessen Hilfe kann man live auf Datenänderungen reagieren. Beispiel gefällig?

Ich habe eine sehr einfache Bibliotheksdatenbank. Enthalten ist nur eine Tabelle (dbo.Books) mit den Spalten ID, Author und Title. Und ich habe eine WPF-Anwendung in der in der MainView per Entitiy Framework die Bücher geladen und an die Liste gebunden werden.
<Window x:Class="MyApp.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <ListBox ItemsSource="{Binding BookList}" >
            <ListBox.ItemTemplate>
                <DataTemplate>
                    <TextBlock Text="{Binding Title}"/>
                </DataTemplate>
            </ListBox.ItemTemplate>
        </ListBox>
    </Grid>
</Window>

public class MainViewModel
{
    public MainViewModel()
    {
        ReloadData();
    }

    private void ReloadData()
    {
        using (var entitites = new LibraryEntities())
        {
            bookList.Clear();
            foreach (var book in entitites.Books)
            {
                bookList.Add(book);   
            }
        }
    }

    private readonly ObservableCollection bookList = new ObservableCollection();

    public ObservableCollection BookList
    {
        get
        {
            return bookList;
        }
    }
}

So weit erst einmal kein Hexenwerk. Der spannende Teil kommt jetzt.

Seit ADO.NET 2.0 gibt es die Klasse SqlDependency, mit der Änderungen an den Daten überwacht werden können. Diese kann für diesen Zweck auch hier verwendet werden.

SqlDependency verfügt über 2 statische Methoden Start und Stop und wie der Name schon vermuten lässt, kann man damit das Tracking starten und auch wieder beenden. In meinem Beispiel sind diese im Konstrukor und im Dispose des ViewModels. Der restliche Code wird in der Reload-Methode angefügt...
public class MainViewModel : IDisposable
{
    private string connectionString;

    public MainViewModel()
    {
        connectionString = new LibraryEntities().Database.Connection.ConnectionString;
        dispatcher = Dispatcher.CurrentDispatcher;
        SqlDependency.Start(connectionString);
        ReloadData();
    }

    private void ReloadData()
    {
        using (var entitites = new LibraryEntities())
        {
            bookList.Clear();
            foreach (var book in entitites.Books)
            {
                bookList.Add(book);   
            }
        }

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("SELECT Title, Author FROM dbo.Books", connection))
            {
                command.Notification = null;
                var dependency = new SqlDependency(command);
                dependency.OnChange += OnDependencyChange;
                command.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
    }

    private void OnDependencyChange(object s, SqlNotificationEventArgs e)
    {
        ((SqlDependency)s).OnChange -= OnDependencyChange;

        if (e.Type == SqlNotificationType.Change)
        {
            dispatcher.Invoke(this.ReloadData);
                
        }
    }

    private readonly ObservableCollection<Books> bookList = new ObservableCollection<Books>();

    private Dispatcher dispatcher;

    public ObservableCollection<Books> BookList
    {
        get
        {
            return bookList;
        }
    }

    public void Dispose()
    {
        SqlDependency.Stop(connectionString);
    }
}

... und hier beginnt es etwas schmutzig zu werden.

SqlDependency basiert auf den Möglichkeiten von ADO.NET 2.0. Damit wird hier eine SqlConnection benötigt und ein SqlCommand, das mit einem DataReader auch ausgeführt werden muss. Und es gibt noch ein paar weitere Punkte zu beachten:
  1. Auf der Datenbank muss der Broker aktiviert sein.
  2. Der Benutzer benötigt ausreichende Berechtigungen (SUBSCRIBE QUERY NOTIFICATIONS)
  3. Der Command bei der Initialisierung der SqlDependency muss bestimmten Voraussetzungen entsprechen (also z.B. nicht SELECT * FROM ....)

Alle diese Einschränkungen kann man ausführlich nochmal auf CodeProject nachlesen.

Während der Ausführung ist es dann wichtig zu wissen, dass der Event ein One-Shot ist - heißt er wird nur bei der ersten Änderung ausgelöst. Deshalb muss dann wenn die Daten neu geladen werden auch der Event wieder registriert werden.

Wenn man das alles berücksichtigt, dann kann man damit aber recht coole Sachen machen. Und man ist nicht auf die WPF beschränkt. Mit der Hilfe von OWIN und SignalR auch weitreichender über Datenänderungen informieren.

SQL Server 2012 und neue analytische Funktionen

Dieser Post zum Thema Neuerungen im SQL Server 2012 widmet sich einigen neu hinzugekommenen analytischen Funktionen. Damit sind Auswertungen der Datenbestände möglich. Und ganz besonders zum Jahresende sind auch Performanceberichte und Vorjahresvergleiche immer von besonderer Bedeutung. Aus diesem Grund möchte ich mich heute etwas näher damit beschäftigen, wie man aktuelle Zahlen - seien es nun Verkaufszahlen, Umsatzzahlen, Gewinne, Arbeitslosenanzahl oder was auch immer mit anderen Zeiträumen vergleicht.

Der SQL Server 2012 bietet hier ein paar wesentliche Verbesserungen, dass sich der Datenbankentwickler nicht mehr zwingend die Finger brechen muss um mit Verrenkungen an diese Werte zu kommen. Mit Hilfe zweier neuer Funktionen und einer neuen Klausel sind Vorzeitraum- bzw. Folgezeitraumbetrachtungen oder Auswertungen mit einem gleitenden Fenster kein Problem mehr. Schauen wir uns das gemeinsam an einigen Beispielen an:

Mehr...

SQL Server 2012 und neue Datumsfunktionen

Über KonvertierungsfunktionenFormatierungsmöglichkeiten und Logikfunktionen in SQL Server 2012 habe ich schon berichtet. Heute soll es um neue Datumsfunktionen gehen.

*FROMPARTS-Funktionen

Eine Möglichkeit Datums- und Zeitwerte zu erstellen ist, die Werte aus Textwerten zu parsen bzw. zu konvertieren  (z.B. mit PARSE oder mit CONVERT). Wenn der Wert nur als Text zur Verfügung steht, dann ist das durchaus ein akzeptabler Weg. In der Vergangenheit wurde dieser Weg jedoch auch häufig gewählt, wenn man Datumsteile vorliegen hatte. Oft wurde dann ein Text im passenden Format per Stringverkettung erstellt und dieser dann mit Hilfe von CONVERT in einen Datumswert konvertiert. Dieser Weg ist aber recht umständlich und fehleranfällig, da die Culture beim Format eine wesentliche Rolle spielt.

Neu im SQL Server 2012 ist sind nun Funktionen wie DATEFROMPARTS oder DATETIMEFROMPARTS, in der man Datumsfraktale angeben kann und hier typsicherer Datumswerte erzeugen kann.

DECLARE @year int, @month int, @day int;
SET @year = 2012;
SET @month = 1;
SET @day = 12;

-- alt
SELECT CONVERT(datetime, LTRIM(@day) + '.' + LTRIM(@month) + '.' + LTRIM(@year), 104)
-- neu
SELECT DATETIMEFROMPARTS(@year, @month, @day, 0, 0, 0, 0)

Genau wie DATETIMEFROMPARTS gibt es Funktionen für so ziemlich alle Datums- und Zeitdatentypen des SQL Servers.

EOMONTH 

Welches Datum hat der Monatsletzte? Besonders wenn man Zeitraumberechnungen oder Ultimoberechnungen durchführen möchte, spielt diese Frage eine relevante Rolle. Wie löst man dieses Problem bisher?

DECLARE @currentdate datetime = GETDATE();
SELECT DATEADD(dd,-DATEPART(dd,DATEADD(MM,1,@currentdate)),DATEADD(MM,1,@currentdate))

Man addiert einen Monat zum aktuellen Datum hinzu und zieht dann die Anzahl von Tagen ab, die im aktuellen Monat schon vergangen sind.

Damit man nicht mehr solche Verrenkungen machen muss, gibt es nun die Funktion EOMONTH, die den Monatsletzten des Monats ermittelt.

SELECT EOMONTH(GETDATE())

Wer mag, kann im zweiten Parameter noch einen zusätzlichen Offset hinzufügen, um so zum Beispiel den Monatsletzten des Folgemonats zu ermitteln.

SQL Server 2012 und neue Konvertierungsfuktionen

Mit SQL Server 2012 kommen auch neue Konvertierungsmöglichkeiten hinzu. Bisher bekannt sind ja schon die Möglichkeiten CAST und CONVERT.

TRY_CONVERT

CONVERT führt zu einem Fehler, wenn die Konvertierung nicht funktioniert. Kleines Beispiel gefällig?

SELECT ISNUMERIC('1e1')
SELECT CONVERT(int, '1e1')

Obwohl ISNUMERIC den Wert 1 zurückliefert, schlägt der Aufruf von CONVERT fehl. Eine Konvertierung in den float-Datentyp hingegen wäre erfolgreich. Hier hilft TRY_CONVERT.

SELECT TRY_CONVERT(int, '100')
SELECT TRY_CONVERT(int, '1e1')

Diese Funktion gibt NULL zurück, wenn die Konvertierung nicht durchgeführt werden kann, oder im Erfolgsfall eben den Wert.

PARSE und TRY_PARSE

Um Datums- oder Zahlenwerte aus einem Text in die jeweiligen Datentypen zu konvertieren gibt es nun die Funktionen PARSE und TRY_PARSE. Unterschied zwischen beiden Funktionen ist - ähnlich wie bei CONVERT und TRY_CONVERT auch, dass PARSE eine Exception wirft, wenn die Konvertierung nicht durchgeführt werden kann, TRY_PARSE hingegen NULL.

SELECT TRY_PARSE('01.02.2012' AS datetime USING 'de-DE')
SELECT PARSE('01.02.2012' AS datetime USING 'en-US')
SELECT PARSE('1234567' AS datetime USING 'en-US')
SELECT TRY_PARSE('1234567' AS datetime USING 'en-US')

Wird keine Culture angegeben, so verwendet PARSE die Culture, die mit Hilfe von SET LANGUAGE gesetzt wurde. Sowohl PARSE als auch TRY_PARSE basieren auf der CLR, was zur Folge hat, dass alle Cultures angegeben werden können, die die CLR kennt - nicht nur jene, die dem SQL Server bekannt sind.

SQL Server 2012 und neue Logikfunktionen

Der SQL-Server 2012 bringt unter anderem auch ein paar neue Funktionen mit sich, die im SQL verwendet werden können. Über die Format-Funktion habe ich ja bereits berichtet. Heute soll es um neue Logik-Funktionen gehen.

IIF (Inline IF)

Vor vielen Jahren als ich von Access zu SQL-Server gewechselt bin war es eine ziemliche Umstellung, dass ich die von dort bekannte IIF-Funktion nicht mehr hatte und alle Abfragen angepasst werden mussten. Klar konnte man mit ähnlichen Konstrukten das gleiche erreichen.

SELECT LastName, FirstName, 
CASE WHEN Gender = 1 THEN 'männlich' ELSE 'weiblich' END Gender 
FROM Person

Solche einfachen CASE-WHEN-Konstrukte kann man jetzt (etwas) kürzer schreiben, indem man die IIF-Funktion verwendet.

SELECT LastName, FirstName, 
IIF(Gender = 1, 'männlich', 'weiblich') Gender 
FROM Person

Der Unterschied in der Syntax ist marginal, kann aber dazu führen, dass die Statements einfacher zu lesen sind - besonders dann wenn man die Argumente schachtelt kommt man in meinen Augen bei der Klammern-Schreibweise weniger schnell durcheinander als beim Suchen der Wörter CASE, WHEN, THEN und des jeweils passenden END. Bei der Ausführung wird IIF zu CASE WHEN übersetzt - es handelt sich hier also um eine Art von syntactic sugar. Daraus abgeleitet erlaubt IIF ebenso wie CASE WHEN eine Verschachtelungstiefe von 10.

CHOOSE

Ebenso wie IIF sollte auch CHOOSE allen Access-Jüngern bekannt vorkommen. CHOOSE ermöglicht es, indexbasiert aus Werten auszuwählen. Dazu zunächst ein Beispiel in der bisherigen Syntax:

SELECT LastName, FirstName, 
CASE MaritalStatus 
WHEN 1 THEN 'ledig'
WHEN 2 THEN 'verheiratet'
WHEN 3 THEN 'geschieden'
WHEN 4 THEN 'verwitwet'
WHEN 5 THEN 'getrennt lebend'
WHEN 6 THEN 'verpartnert'
ELSE NULL
END MaritalStatus 
FROM Person

Dieses Konstrukt kann mit CHOOSE anders geschrieben werden:

SELECT LastName, FirstName, 
CHOOSE(MaritalStatus, 'ledig', 'verheiratet', 'geschieden', 'verwitwet', 'getrennt lebend', 'verpartnert') MaritalStatus 
FROM Person

Wichtige Anmerkung für alle C#-Entwickler... der Index ist 1-basiert.

SQL Server 2012 und Contained Databases

Ein sehr spannendes Feature von SQL Server 2012 sind Contained Databases. Dieses Feature ist besonders auf Hosting-Umgebungen ausgerichtet, erlaubt es doch eine bessere Trennung der jeweiligen Datenbank- und damit Anwendungskontexte als es bisher der Fall war.

Was bedeutet das nun de facto? Um bisher einen Nutzer auf einer Datenbank zuzulassen, legte man einen Server-Login an und ließ diesen Nutzer dann auf der jeweiligen Datenbank zu. Das ist ohne Frage auch durchaus sinnvoll, wenn man in Unternehmensgrenzen denkt. Und mal abgesehen von Recovery-Szenarien, bei denen es in der Vergangenheit häufig zu verwaisten Logins und/oder verwaisten Nutzern kam, ist an diesem Ansatz auch nichts auszusetzen. Er ist dann aber hinderlich, wenn man bedenkt, dass man auf einem SQL-Server die Datenbanken völlig voneinander unabhängiger Anwendungen hostet und sicherstellen möchte, dass die dort zugelassenen Nutzer keinesfalls Zugriff auf andere Datenbanken bekommen. Nun unterstelle ich mal, dass Microsoft dieses Feature nicht nur macht, weil es Entwicklern von Webanwendungen und Hostern entgegenkommt, sondern weil Microsoft dieses Feature selbst für Azure auch ganz gut brauchen kann.

Mehr...

SQL Server 2012 und die Format-Funktion

Eigentlich ist Formatierung von Daten in meinen Augen nicht wirklich eine Aktion, die zwingend von der Datenbank durchgeführt werden muss. Aber wie es häufig ist, so gibt es auch hier Anwendungsszenarien in denen dies ein durchaus sinnvoller Weg ist.

In den bisherigen Versionen von SQL Server gestaltete sich dies aber immer etwas schwierig, denkt man nur an verschiedene Datumsformatierungen. Die Funktion CONVERT bot zwar schon begrenzt die Möglichkeit, hier Einfluss zu nehmen, aber wenn man bedenkt, dass für Datumsformate die Auswahlmöglichkeit auf "mit Jahrhundert oder ohne" begrenzt war, so schaute man schon etwas neidisch auf die Möglichkeiten, die beispielsweise die .NET CLR mit string.Format bot.

Mehr...

Verwaiste Logins in SQL-Server-Datenbanken neu zuordnen

In meinen Blogpost Verwaiste SQL-Logins auf einem SQL-Server ermitteln hab ich gezeigt, wie man mit Hilfe eines Skriptes ermitteln kann, welchen Logins gar keine Datenbanken mehr zugewiesen sind.

Aber das Thema der verwaisten Logins gibt es auch in umgekehrter Richtung: Jeder der bereits versucht hat, eine Datenbank von einem anderen Server wiederherzustellen und diese Datenbank verfügte über SQL-Benutzer, wird das Problem kennen: In der Datenbank gibt es unter dem Punkt Datenbank -> Security -> Users bereits mehrere Benutzer und diese sind unter Umständen auch mit Berechtigungen versehen, man möchte diese also nicht löschen. Die Benutzer existieren jedoch nicht als Logins unter Security -> Logins. Legt man dann gleichnamige Logins im SQL-Server an, so ist das auch noch recht unproblematisch - zumindest so lange bis man versucht, den neu angelegten Login auf der besagten Datenbank zuzulassen. Diesen Versuch quittiert der SQL-Server mit der Meldung, dass ein Benutzer mit diesem Namen in der Datenbank schon vorhanden ist - womit er ja grundsätzlich auch Recht hat.

 

Allerdings bedeutet das nicht, dass das eben neu angelegte Login aufgrund dieser Tatsache auch automatisch mit Zugriffsrechten auf der wiederhergestellten Datenbank ausgestattet ist.

Die Ursache liegt darin begründet, dass der SQL-Server den Benutzern intern eine SID zuweist. Die SID des Benutzers in der wiederhergestellten Datenbank und die SID des eben erstellten Logins unterscheiden sich.

Abhilfe schafft hier die Systemprozedur sp_change_users_login. Mit dem Parameter 'report' aufgerufen erhält man zunächst eine Auflistung aller Benutzer, die über keine Zuordnung zu Logins verfügen.

 

Nun steht also fest, dass es in der Datenbank fünf ungemappte Benutzer gibt. Mit Hilfe der gleichen Prozedur kann man nun auch gleich das Mapping vornehmen. Dazu übergibt man einfach als ersten Parameter 'update_one' und als zweiten bzw. dritten Parameter Benutzer und Login.

Prüft man nun die verwaisten Logins erneut, wird man feststellen, dass dieser Benutzer hier nicht mehr erscheint und versucht man ein Login auf der Datenbank, wird man merken, dass das nun funktioniert.

Datenbankrollen eines Benutzers ermitteln

Möchte man ermitteln, welchen Datenbankrollen ein Benutzer angehört, so ist es anfänglich gar nicht so kompliziert. Die relevante Systemsicht ist recht schnell gefunden: sys.database_role_members.

SELECT USER_NAME(role_principal_id)
FROM sys.database_role_members
WHERE member_principal_id = USER_ID('MyUser')

Hierbei gibt es aber zwei Probleme: Zunächst ist die Rolle public nicht enthalten und die Rekursion wird nicht aufgelöst, können doch Datenbankrollen andere Datenbankrollen enthalten. Eine Lösung dafür ist die Verwendung von Common Table Expressions, um die Rekursion aufzulösen. Ausformuliert sieht das in etwa wie folgt aus:

DECLARE @username sysname;
SET @username = 'MyUser';

WITH CTE_Roles (role_principal_id)
AS
(
SELECT role_principal_id
FROM sys.database_role_members
WHERE member_principal_id = USER_ID(@username)
UNION ALL
SELECT dbrm.role_principal_id
FROM sys.database_role_members dbrm
INNER JOIN CTE_Roles CR
 ON dbrm.member_principal_id = CR.role_principal_id
)
SELECT USER_NAME(role_principal_id) RoleName
FROM CTE_Roles
UNION ALL
SELECT 'public'
FROM sys.sysusers
WHERE uid = USER_ID(@username)
ORDER BY RoleName;

Aufgelistet werden alle zugeordneten Datenbankrollen unter Beachtung der Schachtelungsmöglichkeit von Rollen sowie die Rolle public.

Nur 30 Sekunden

Auf den ersten Blick mag der Titel wie der Aufmacher eines Hollywood-Action-Films sein, aber eigentlich handelt dieser Post von SQL Server Managment Studio Express 2005. Nur 30 Sekunden gibt es dem Anwender, um Queries zu einem erfolgreichen Ende zu bringen, bis der Timeout kommt.

Wie lässt sich das nachstellen? Ganz einfach: Man erstellt eine etwas komplexere View und klickt dann im Kontextmenü der View auf "Open View". Kurze Zeit später erscheint folgende formschöne Meldung:

In einem solchen Fall ist der erste Ansatz meist, in den Optionen (Tools / Options) des Management Studios nachzusehen. Und da finden sich auch wirklich zwei Einträge, die den Timeout bestimmen. Der erste findet sich unter Query Execution / SQL Server und nennt sich Execution Timeout. Bei mir steht dieser schon auf 0, was laut Beschreibung "unlimited" bedeutet und damit nicht Grund des Problems sein dürfte. Der zweite Eintrag findet sich unter Designers und erlaubt laut Beschreibung das Überschreiben des Timeouts in den Tabellendesignern. Der Haken ist bei mir deaktiviert und der Wert steht ausgegraut auf 30.

30? 30 und TimeOut? Ok, kann ja sein, dass hier jemand den Haken nicht korrekt auswertet und sinngemäß könnte das "Open View" ja schon noch unter Designerbedienung fallen. Also Haken rein, Wert auf 3600 gesetzt, Management Studio neu gestartet und... (Trommelwirbel) ... nach 30 Sekunden die Ernüchterung.

Um es kurz zu machen: Anscheinend gibt es für das Problem kein passendes Eingabefeld. Ich hab letztenendes alle Einstellungen wieder zurückgesetzt und den Wert in der Registry gesetzt. Verantwortlich für diesen TimeOut ist der Wert SQLQueryTimeOut unter HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\DataProject. Setzt man diesen auf einen höheren Wert als 30, so gibt man damit dem Management Studio mehr Zeit, die View zu öffnen. Problem daran: Dieser Wert wird anscheinend bei jedem Start des Management Studios zurückgesetzt - das ist also nur eine temporäre Lösung.