Onjuist instellen van LSBackup maakt secondary logship database kapot

Vandaag is er wat tijd door mij ingeruimd om het SQL Server proces van logshipping nog beter te begrijpen. De opdracht die ik mij gaf, was: “Maak de secondary logship database kapot door onjuist instellen van de LSBackup job op de primary database.”

Voor deze opdracht was er een demo-database ingericht met daarop een secondary database op basis van logshipping. De basis van logshipping is dat alle mutaties op de primary database (de ‘hoofddatabase’) in een zogenaamde transactielog wordt vastgelegd. Van deze transactielogs wordt periodiek een backup (SQL Server Agent LSBackup job). gemaakt (de standaard bij het opzetten van een logshipdatabase is 15 minuten). Vervolgens worden deze transactielogs naar de server gekopieerd (SQL Server Agent LSCopy job) waar de secondary database op basis van logshipping staat en worden deze transactielogs daar ingelezen (SQL Server Agent LSRestore job).

Eén van de situaties die zich het meest voordoet in een ‘gebroken logshipketen’ op de secondary database, is dat er transactielogs ‘verdwijnen’ door wat voor oorzaak dan ook. Mijn onderzoek richt zich op het ‘verdwijnen’ van transactielogs door de LSBackup job; bij het maken van de backups van transactielogs. Is dat mogelijk?? Het korte antwoord is ‘ja’! Hoe? Lees vooral verder!

In de job LSBackup (waarmee backups worden gemaakt van de transactielogs) zit een eigenschap die in het Engels “Delete files older than…” heet. Deze instelling speelt een cruciale rol in het geheel. Wanneer de retentieperiode (de periode waarin de transactielogbestanden moeten worden blijven bewaard) tekort wordt ingesteld, zullen transactielogs te snel worden verwijderd en verloren gaan. Wat is dan een goede retentieperiode? Dat hangt af van de frequentie waarin de SQL Server Agent Job LSCopy, de job die ervoor zorgt dat transactielogs naar de server met de secondary database op basis van logshipping worden gekopieerd, draait. De retentieperiode van de transactielogs in de LSBackup job moet in ieder geval langer zijn dan de frequentie waarin de LSCopy job draait. Wanneer dat niet het geval is, zullen transactielogs te vroeg verwijdert worden, waardoor de secondary database op basis van logshipping niet meer adequaat bijgewerkt kan worden.

Wie zit er aan mijn SQL Agent jobs?

De HiX Datawarehouse verversing van ChipSoft haalt de data uit een tijdelijk statische HiX secondary database op basis van logshipping op. Om de database statisch te maken, zorgt de software van HiX Datawarehouse voor de correcte afhandeling van de logship procedures. Het is voor gebruikers en beheerders nadrukkelijk niet de bedoeling om zélf de logship, met name de LSRestore job, in- of uit te schakelen. Maar… monitor dat maar eens! Microsoft SQL Server heeft daar zélf geen voorziening voor én het goede nieuws is dat deze voorziening eenvoudig zélf te schrijven is.

Functioneel
Wanneer een SQL Agent job in- of uitgeschakeld wordt, wordt dit in de tabel [dbo].[sysjobs] binnen de msdb database opgeslagen. Deze wijziging kunnen we afvangen en de wijziging kan in een aparte beheertabel worden vastgelegd.

Technisch
Met een AFTER UPDATE trigger op de tabel dbo.sysjobs binnen de msdb database wordt het mogelijk om de wijziging in de job status (ingeschakeld / uitgeschakeld) af te vangen. De gewijzigde informatie wordt vervolgens in een beheerdatabase / beheertabel opgeslagen, zodat hier later naar gekeken kan worden.

De code hiervoor …

USE msdb;
GO
CREATE TRIGGER [dbo].Alert_on_job_stat
   ON  [dbo].[sysjobs]
   AFTER UPDATE
AS 
BEGIN
DECLARE @old_status BIT, @new_status BIT, @job_name VARCHAR(1024)
 
SELECT @old_status = enabled FROM deleted
SELECT @new_status = enabled, @job_name = name FROM inserted
 
  IF(@old_status <> @new_status)
  BEGIN
    INSERT INTO [audit_database].dbo.[audit_tabel] (login_name, job_name, status_, datetime)
    VALUES(ORIGINAL_LOGIN(), @job_name, @new_status, GETDATE())
    
    DECLARE @body_content VARCHAR(1024) = '';
    SET @body_content = 'SQL job : ' + @job_name + ' has been ' + 
              CASE @new_status WHEN 1 THEN 'Enabled' ELSE 'Disabled' END 
              + ' @ ' + CAST(GETDATE() AS VARCHAR(30))

    /* Eventueel een e-mail sturen
    EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQL Alert', 
    @recipients = 'beheerders@domeinnaam.com',  
    @subject = 'SQL job status Change Alert',  
    @body = @body_content
    */
    ;  
  END
END

Aandachtspunten:

  1. De database audit_database is een zelfgekozen naam en dient uiteraard te worden aangemaakt
  2. De tabelnaam is een zelfgekozen naam en dient uiteraard te worden aangemaakt
  3. E-mail is standaard door mij uitgeschakeld
  4. De e-mail instellingen moeten goed worden geconfigureerd
  5. Gebruikers die SQL Agent Jobs in en uit kunnen schakelen, dienen voldoende schrijfrechten op de audit database en audit tabel te krijgen

Met dank aan Jignesh Raiyani voor het idee en de uitwerking in het Engels; Link naar de webpagina