Belangrijk nieuws: Nieuwe backup formaat in SQL Server 2019, CU16; dit kan logshipping kapot maken!

Op 18 april is SQL Server CU16 door Microsoft vrijgegeven. Hier zit een zeer belangrijke wijziging in. Backups worden in een nieuw formaat opgeslagen; in het bijzonder de zogenaamde Transparent Data Encryption (TDE)-gecomprimeerde backups. TDE-gecomprimeerde backups die met deze cumulative update zijn gemaakt, kunnen niet worden teruggezet op SQL Server 2019 servers met een eerdere cumulative update. Andersom blijft het overigens wél werken (dus een gecomprimeerde backup op een eerdere CU van SQL Server 2019 kan wél op deze CU worden terug gezet).

Wanneer is dit belangrijk?
Deze informatie is belangrijk wanneer:

  • … u uw database met TDE heeft versleuteld, en …
  • … wanneer u LOG backups neemt met de optie WITH COMPRESSION óf wanneer de server standaard (default) op backups met WITH COMPRESSION nemen staat, en…
  • … u maakt van logshipping gebruik naar andere SQL Server 2019 servers, en …
  • … u voorziet eerst de primary database (de bron database) van SQL Server 2019 CU16, voordat u de secondary database (kopie-database) van CU16 voorziet

Wat betekent dit concreet voor u!
Wanneer uw bedrijf eerst de secondary server (waar de kopie-database dus staat) van de nieuwe cumulative update voorziet, dan zal SQL Server 2019 tijdens het voorzien van de update op de primary server (de server waar de brondatabase staat) een fail-over naar de secondary database doen. In deze strategie zit er vaak een aantal uur tussen de eerste update op de secondary server en de volgende update op de primary server zitten. De primary server wordt in dit geval de secondary, maar backups die op de secondary zijn gemaakt (en nu dus de primary zijn), zullen niet meer kunnen worden ingelezen, vanwege deze nieuwe feature.

Ik ga ervan uit dat u als database administrator voldoende op de hoogte bent van update-procedures binnen uw bedrijf. Mijn dringende advies is te kijken of deze situatie op u van toepassing is en op gepaste wijze uw SQL Server 2019 servers bij te werken.

Belangrijk: deze situatie geldt dus alleen voor configuraties die voldoen aan de vier punten onder het kopje: “Wanneer is dit belangrijk?

Links en verwijzingen
Alle informatie over de cumulative update (KB5011644)
Gedeelte dat over de TDE-gecomprimeerde backup gaat

Met dank aan Brent Ozar

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

Veldlengtes (n)varchar en (n)char velden vs. query performance vs. server performance

Vandaag werd ik door mijn collega getriggerd om onderzoek te doen naar extreme lengtes in (n)varchar velden en de impact daarvan op performance. Welnu… riemen vast, want dit is belangrijk.

Management samenvatting
Een goede inrichting en adequate keuze van veldlengtes bij (n)varchar en (n)char velden draagt bij aan een optimaler query plan en daarmee aan een betere query en server performance in het algemeen.

Hoe zit dit precies
U kent het wel: “Laten we dat tekstveld alvast maar wat langer maken, zodat we ook in de toekomst deze velden niet zo snel hoeven te verlengen”. Die beslissing kan impact hebben op de query performance van uw query’s op deze velden. SQL Server kijkt bij het maken van het query plan (die SQL Server bij query’s gebruikt) naar de grootte van de tekstvelden die in de query wordt gebruikt. Op basis daarvan maakt SQL Server een inschatting van het benodigd geheugen. Hoe meer de veldlengte van het veld in de tabel afwijkt van de daadwerkelijke vulling des te meer geheugen zal SQL Server onnodig aan deze query toewijzen. Gelukkig zal SQL Server (2019) u hier in het Actual Query Plan op wijzen bij het meest linkse (SELECT) component. U vindt in het Actual Query Plan op dat component een geel driehoekje met een waarschuwing.

Eén en ander is met onderstaand TestLab verduidelijkt. Deze set met query’s helpt u hopelijk beter te begrijpen wat er precies fout gaat. Dit is overigens geen ‘incident’. De onjuiste bepaling van het benodigd geheugen zal continue fout gaan wanneer de betrokken tabel (of, indien dit zich in meerdere tabellen voordoet, betrokken tabellen) in een query wordt betrokken. Draaien er parallel meerdere query’s, dan zal dit potentieel onnodig veel resources op het geheugen van uw SQL Server gaan leggen; en geheugen is schaars en kostbaar.

Vragen staat vrij!


/*
Auteur : Mickel Reemer
Datum : 23 maart 2022
Thanks to : Erik Darling
Credits by : https://www.brentozar.com/archive/2017/02/memory-grants-data-size/
SQL Server : SQL Server 2019 Developer Edition

Doel van het script:
--------------------
Aantonen dat varchar die te groot zijn aangemaakt
oorzaak zijn excessive memory grants op een SQL Server
*/
-- Zet tijdstatistieken aan
SET STATISTICS TIME ON

-- Verwijder de testtabel wanneer deze al bestaat
DROP TABLE IF EXISTS dbo.MemoryGrants;

-- Maak de testtabel aan
CREATE TABLE dbo.MemoryGrants
(
ID INT PRIMARY KEY CLUSTERED,
Ordering INT,
Field1 VARCHAR(10),
);

-- Voeg de records in, in de tabel
INSERT dbo.MemoryGrants WITH (TABLOCK)
( ID, Ordering, Field1)
SELECT
c,
c % 1000,
REPLICATE('X', c * 10 % 10)
FROM (
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS c
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) x

-- Zet Actual Query Plan aan en draai de query hieronder
SELECT mg.ID, mg.Ordering, mg.Field1 --Returning a VARCHAR(10)
FROM dbo.MemoryGrants AS mg
ORDER BY mg.Ordering
GO
-- De doorlooptijd is 1751ms. De memory grant is 13MB

/*
Dat gaan we nu nog een keer testen, maar dan met varchar(8000)
*/

-- Verwijder de testtabel opnieuw wanneer deze bestaat
DROP TABLE IF EXISTS dbo.MemoryGrants;

-- Maak de testtabel aan
CREATE TABLE dbo.MemoryGrants
(
ID INT PRIMARY KEY CLUSTERED,
Ordering INT,
Field1 VARCHAR(8000), -- We gaan nu Field1 een lengte van 8000 geven
);

-- Voeg de records in, in de tabel
INSERT dbo.MemoryGrants WITH (TABLOCK)
( ID, Ordering, Field1)
SELECT
c,
c % 1000,
REPLICATE('X', c * 10 % 10)
FROM (
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS c
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) x

-- Zet Actual Query Plan aan en draai de query hieronder
SELECT mg.ID, mg.Ordering, mg.Field1 --Returning a VARCHAR(10)
FROM dbo.MemoryGrants AS mg
ORDER BY mg.Ordering
GO
-- De memory grant is 574MB. De doorlooptijd is 2706ms

Hoe houdt u de groei van de SQL Server msdb database in de hand?

Weet u hoe groot de msdb database op uw SQL Server is?

Wanneer u het antwoord op deze vraag niet weet, begrijp ik dat. De situatie is echter dat de msdb database van SQL Server de nodige logging bevat die niet automatisch wordt opgeschoond. Vooral wanneer u gebruik maakt van database mail kan deze database heel snel explosief groeien. In deze video laat ik u zien waar u op kunt letten en op welke manier u de groei in de hand kunt houden. Bij deze video hoort dit script wat gratis kan worden gebruikt.

Waarom duurt die database restore zo lang?

Bij maken van plannen voor disaster recovery is het een goed idee om te kijken hoe snel een full backup terug gezet kan worden. Onlangs duurde het terugzetten van één van de backups waar ik mee werkte zo’n 17 uur en dat is natuurlijk vrij fors. We gaan er uiteraard dan wél vanuit dat er geen parallelle processen op de machine draaien die de performance negatief beïnvloeden. Microsoft SQL Server biedt mogelijkheden om te kijken waar de restore precies zijn tijd aan kwijt is. Hiervoor worden trace flags gebruikt. Mijn tip is om de volgende trace flags te gebruiken:

– 3004; deze geeft inzicht in elke stap van de restore
– 3213; deze geeft inzicht in de restore configuratie zoals buffer en maxtransfersize
– 3605; schrijft de resultaten van de trace flags hierboven weg naar de SQL Server errorlog

Vergeet de trace flags na de restore niet uit te zetten, omdat de trace flags zelf ook de performance van de machine beïnvloeden!

Een aantal aandachtspunten onder het trage terug zetten van de database:

  • Controleer of in de SQL Server Logs de volgende melding voorkomt: SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete etc. Dit betekent vrijwel zeker dat de storage waaronder de SQL Server draait niet snel genoeg id.

  • Paul Randall heeft via deze pagina een script gedeeld waarmee de I/O latency van een SQL Server over een periode van tijd is vast te leggen. Met de cijfers kan het team dat verantwoordelijk is voor hardware kijken wat er nodig is.

  • Een derde probleem is wat in het engels ‘Zeroing’ wordt genoemd. Dit is de tijd die nodig is om het logbestand van de database door te nemen en alle verwerkte transactielogs te verwerken. Het is niet aan te raden, maar als het logbestand significant via SHRINKLOGFILE kan worden verkleind, komt dit de snelheid waarmee de backup kan worden teruggezet ten goede

Succes!

Ongebruikte indexen op tabellen in een database vinden

Het is als database administrator belangrijk periodiek te kijken of er indexen op tabellen staan die door SQL Server nooit meer worden gebruikt. Dit voorkomt dat deze indexen onnodig schijfruimte innemen en/of onnodige overhead (performance) veroorzaken bij het bewerken, toevoegen of verwijderen van records op de tabel. In deze video wordt daar uitgebreider door mij op ingegaan en wordt getoond op welke wijze hier het best een analyse op kan worden gemaakt.

Het bijbehorende script is hier te downloaden.

Overzicht open transacties in SQL Server

Stel je voor dat je op een SQL Server in een database tabel binnen transacties data aan het bewerken bent. Het is dan handig te weten of er nog open transacties zijn, die nog definitief moeten worden gemaakt of terug moeten worden gedraaid. Ook wanneer wordt geprobeerd om de transactielog vrij te maken, is het handig te weten of er nog transacties nodig zijn. Er zijn een aantal manieren om inzicht te krijgen in de transacties die nog open staan.

    DBCC OPENTRAN
    Deze functie wordt vooral gebruikt bij het vrijmaken van ruimte in de transactielogs. Het commando geeft de oudste transactie aan die nog open staat voor verwerking.
    Belangrijk: dit commando geeft alleen informatie over de actief geselecteerde database waarbinnen deze instructie wordt uitgevoerd.

    sys.sysprocesses
    De query …

    SELECT
    * 
    FROM sys.sysprocesses
    WHERE open_tran <> 0
    

    … geeft informatie over open transacties. Deze system view (dus geen tabel!) geeft onder andere informatie over het proces ID waaronder de transactie draait, op welke machine de transactie is gestart ook met welk account. Deze tabel is vooral handig om te identificeren wie, welke transacties open heeft.
    Belangrijk: de informatie wordt serverbreed weergegeven (en dus niet per databases)

    sys.dm_tran_active_transactions
    Onderstaande handige query is een query op de serverbrede view (dus geen tabel!) sys.dm_tran_active_transactions. Deze tabel wordt voornamelijk gebruikt om de status en voortgang van verschillende open transacties in kaart te brengen. De velden zijn in onderstaande query alvast vertaald naar betekenissen (in het Engels).

    select transaction_id, name, transaction_begin_time
     ,case transaction_type 
        when 1 then '1 = Read/write transaction'
        when 2 then '2 = Read-only transaction'
        when 3 then '3 = System transaction'
        when 4 then '4 = Distributed transaction'
    end as transaction_type 
    ,case transaction_state 
        when 0 then '0 = The transaction has not been completely initialized yet'
        when 1 then '1 = The transaction has been initialized but has not started'
        when 2 then '2 = The transaction is active'
        when 3 then '3 = The transaction has ended. This is used for read-only transactions'
        when 4 then '4 = The commit process has been initiated on the distributed transaction'
        when 5 then '5 = The transaction is in a prepared state and waiting resolution'
        when 6 then '6 = The transaction has been committed'
        when 7 then '7 = The transaction is being rolled back'
        when 8 then '8 = The transaction has been rolled back'
    end as transaction_state
    ,case dtc_state 
        when 1 then '1 = ACTIVE'
        when 2 then '2 = PREPARED'
        when 3 then '3 = COMMITTED'
        when 4 then '4 = ABORTED'
        when 5 then '5 = RECOVERED'
    end as dtc_state 
    ,transaction_status, transaction_status2,dtc_status, dtc_isolation_level, filestream_transaction_id
    from sys.dm_tran_active_transactions
    

    Belangrijk: de informatie wordt serverbreed weergegeven (en dus niet per databases)

    Credits
    Met dank aan: Tharif, Sebastian Brosch en Alisson Gomes
    Gebruikte link op StackOverflow: hier
    Microsoft Docs voor meer informatie: hier

Microsoft SQL Server vraagt om poort 587 (STARTTLS) bij database mail

Tijdens het installeren van Microsoft SQL Server op een dev omgeving, struikelde ik bij het installeren van database mail over de e-mail instellingen. Bij uitgaande mail gebruikte ik de reguliere SSL poort (465) die bij de meeste providers binnen een mailprogramma zoals MS Outlook ook wordt gebruikt. Bij Microsoft SQL Server werkt dient STARTTLS, poort 587 bij de meeste providers, te worden gebruikt. Vergeet bij het instellen dan niet het vinkje “This server requires a secure connection (SSL)” aan te zetten.

Hoe eenvoudig data tussen twee SQL Server databases wordt gekopieerd

Het komt wel eens voor dat er data tussen twee SQL Server databases moet worden gekopieerd. Daar zijn legio mogelijkheden voor. In deze bijdrage behandel ik er drie:

  • Via ‘Generate scripts…’
  • Via ‘Import/Export data…’
  • … en een combinatie van de twee bovenstaande opties

Take away:

  • In de import/export data wizard gebruikt u de data source Microsoft OLE DB Driver for SQL Server (Download)
  • De Microsoft OLE DB Provider for SQL Server en de SQL Server Native Client zijn vervallen; zie Microsoft Docs
  • De import/export wizard houdt geen rekening met de tabel opties in de brontabel(len)
  • De ‘Generate scripts…’ wizard is niet heel geschikt voor heel grote tabellen
  • Voor grote tabellen kunt u gebruik maken van ‘Generate scripts…’ voor het aanmaken van de objecten en de import/export wizard voor het kopiëren van de data