SQL Change Tracking + Azure Functions: kärlek vid första funktion

SQL Change Tracking i kombination med Azure Functions är fantastiskt för att exekvera programkod baserat på databasförändringar.

Denna artikel utforskar dessa teknologier och ger ett praktiskt exempel på hur de kan användas för att automatisera processer.

Vad är SQL Change Tracking?

SQL Change Tracking är en funktion i Microsoft SQL Server som möjliggör enkel spårning av ändringar i databasens tabeller. Genom att aktivera Change Tracking på en databas och specifika tabeller kan systemet hålla reda på och lagra information om vilka rader som har ändrats, när de ändrades och vilken typ av ändring som skedde (till exempel INSERT, UPDATE eller DELETE). Detta är särskilt användbart i miljöer där det är nödvändigt att snabbt svara på dataändringar utan att ständigt fråga databasen eller behålla stora datamängder för historik.

Hur fungerar SQL Change Tracking?

När SQL Change Tracking är aktiverat, skapar SQL Server en minimal mängd metadata för varje ändring som görs i de aktiverade tabellerna. Denna metadata lagras i systemtabeller som kan användas för att fråga vilka ändringar som har gjorts sedan ett visst tidpunkt. Några av de centrala koncepten för Change Tracking är:

1. Versioning: Varje gång en ändring görs på en aktiverad tabell, genereras ett nytt versionsnummer. Detta versionsnummer kan användas för att avgöra vilka ändringar som har gjorts sedan en specifik tidpunkt.
2. Change Tables: Ändringarna lagras inte direkt i de ursprungliga tabellerna utan i speciella systemtabeller som hanteras av SQL Server. Dessa tabeller innehåller information om ändringarna, såsom primary key-värden för de rader som har ändrats, typ av ändring (INSERT, UPDATE, DELETE) och versionsnummer.
3. Change Tracking Functions: SQL Server tillhandahåller flera sql-funktioner som kan användas för att fråga om ändringar.

• CHANGETABLE(CHANGES table_name, last_sync_version): Returnerar en lista över ändringar som har gjorts i den angivna tabellen sedan den specificerade versionsnumret.
• CHANGE_TRACKING_CURRENT_VERSION(): Returnerar den nuvarande versionsnummer som kan användas för att markera tidpunkten för en synkronisering.

Här är ett exempel på hur man kan använda CHANGETABLE funktionen för att få en lista över ändringar:

DECLARE @last_sync_version BIGINT = 0; -- Sätt till din senaste synkroniseringsversion 
-- Hämta alla ändringar sedan den senaste synkroniseringsversionen
SELECT CT.*, U.*
FROM CHANGETABLE(CHANGES UserTable, @last_sync_version) AS CT
JOIN UserTable AS U
ON CT.UserId = U.UserId;

Denna fråga returnerar en lista över alla ändringar som gjorts i UserTable sedan den senaste synkroniseringsversionen, inklusive information om vilken typ av ändring som gjordes och de aktuella värdena för de ändrade raderna.

Konfigurera SQL Change Tracking

För att använda SQL Change Tracking behöver du aktivera det på både databasen och de tabeller du är intresserad av att följa. Här är ett exempel på hur du aktiverar Change Tracking på en databas och en tabell:

-- Aktivera Change Tracking på databasen
ALTER DATABASE MyDatabase
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

-- Aktivera Change Tracking på tabellen
ALTER TABLE UserTable
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);

Parametern CHANGE_RETENTION anger hur lång till den ska spara ändringar. AUTO_CLEANUP anger om man vill att gammal tracking-data ska tas bort automatiskt.

Om parametern TRACK_COLUMNS_UPDATED är aktiverad kommer SQL servern spara information om vilka kolumner som blivit uppdaterade.

Azure Function med SQLTrigger

Med typen SQLTrigger kan en Azure Function konfigureras för att automatiskt köras på events triggade genom SQL Change Tracking. Detta är ett mycket effektivt sätt för att skapa actions för databasändringar.

Nedan har jag gjort ett litet exempel på hur man sätter upp en Azure Function som lyssnar på databasförändringar.

Exempel: Skicka ett välkomstmail till nya användare

Låt oss anta att vi vill skicka ett välkomstmail var gång en ny användare registrerar sig på vår plattform. Vi har en SQL-databas med en tabell som heter UserTable som innehåller alla användare. Vi har också aktiverat Change Tracking för vår databas och tabellen UserTable enligt ovan.

Azure Function som lyssnar på skapade användare

Vi skapar upp ett nytt Azure Function-projekt enligt nedan.

Här anger vi att den ska vara av typen SQL Trigger samt att den ska lyssna på ändringar för tabellen UserTable.

Här nedan har vi en väldigt simpel implementation för funktionen.

När vår Azure Function blir notifierad om ändringar i tabellen så itererar vi igenom alla ändringar och ser om någon har operationen "insert". Om så är fallet skickar vi ett email och hälsar personen välkommen till vår platform.

public class WelcomeUserFunction
{
    private readonly ILogger _logger;

    public WelcomeUserFunction(ILoggerFactory loggerFactory)
    {
      _logger = loggerFactory.CreateLogger<WelcomeUserFunction>();
    }

    [Function("WelcomeUser")]
    public void Run(
        [SqlTrigger("[dbo].[UserTable]", "SqlConnectionString")] IReadOnlyList<SqlChange<User>> changes,
            FunctionContext context)
    {

        foreach (var change in changes)
        {
            if (change.Operation == SqlChangeOperation.Insert)
            {
                var newUser = change.Item;

                SendWelcomeEmail(newUser.Email, newUser.Name);

                _logger.LogInformation($"Welcome email sent to user: {newUser.Name}");
            }
        }
    }

    private void SendWelcomeEmail(string email, string name)
    {
        //To be implemented
        throw new NotImplementedException();
    }
}

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

Sammanfattning

Genom att använda SQL Change Tracking och Azure Functions med SQLTrigger kan man skapa responsiva och skalbara applikationer som effektivt hanterar och reagerar på databasändringar i realtid. Med denna arkitektur kan du automatisera processer utan att konstant behöva övervaka databasen. Detta resulterar i en mer effektiv och flexibel databehandling som kan anpassas till förändrade affärsbehov.