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.

Western Digital My Book Live via windows verkenner openen

Heel lang geleden is er een Western Digital My Book Live door mij aangekocht. Op dat moment was dat een behoorlijk vooruitstrevend product. Over de jaren heeft deze NAS mij prima gediend, totdat… de NAS niet meer door Windows Verkenner binnen Windows 10 werd herkend. Uiteindelijk is mij duidelijk geworden waarom de NAS niet meer door Windows Verkenner werd herkend: Bij de Fall Creators Update van Windows 10 heeft Microsoft ervoor gekozen om standaard het netwerk protocol Service Message Block 1.0 (SMB 1.0) uit te schakelen. In onderstaande instructievideo wordt getoond hoe deze weer kan worden aangezet.

LET OP: het opnieuw aanzetten van deze feature brengt veiligheidsrisico’s met zich mee. Meer informatie over het SMB 1.0 netwerk protocol via Microsoft: https://docs.microsoft.com/en-us/windows-server/storage/file-server/troubleshoot/smbv1-not-installed-by-default-in-windows

SQL Server met Microsoft 365 combineren

Ongeveer een jaar geleden heb ik een Microsoft 365 Business Standard abonnement genomen. Na een maand of twee vroeg ik mij af waarom ik dat nooit eerder heb gedaan. Wat een mooie omgeving. Zo’n abonnement kent wel z’n uitdagingen. Er is zóveel te configureren dat het haast onmogelijk is om door de instellingen het bos nog te zien.

Vandaag was ik bezig met het configureren van een nieuwe SQL Server 2019 Developer instance. Eén van de belangrijke punten is het configureren van Database Mail, zodat er operators en alerts kunnen worden geconfigureerd en belangrijke zaken (alerts en SQL Agent jobs) naar mij gestuurd worden. Dat opzetten ging niet helemaal zonder slag of stoot. Twee pijnpunten waar ik tegen aan liep:

  • Wat is de SMTP server van Microsoft 365 Exchange –> dat bleek smpt.office365.com te zijn (vergeet SSL niet; poort 587!)

  • SMTP op tenant niveau moet worden aangezet; die leg ik hieronder even uit

Ik kreeg bij het sturen van database mail vanuit mijn SQL Server de melding Cannot send mails to mail server. (Voor de SMTP-server is een beveiligde verbinding vereist of de client is niet geverifieerd. Het serverantwoord is: 5.7.57 Client not authenticated to send mail. Error: 535 5.7.139 Authentication unsuccessful, SmtpClientAuthentication is disabled for the Tenant. Visit aka.ms/smtp_auth_disabled for more info terug.

Standaard staat het versturen van mail via SMTP dus uit bij het aanmaken van een Exchange account binnen Microsoft 365. Dat zag ik niet helemaal aankomen, maar onlogisch is het ook niet. Wat is er voor nodig om dit aan te zetten?

  • Ga binnen Microsoft 365 Admin naar Users > Active users

  • Selecteer de gebruiker en rechts op het scherm daarna Mail

  • In het Email apps gebied, klik op Manage email apps

  • Zet het vinkje uthenticated SMTP setting aan

  • Vergeet niet met ‘Save changes’ weg te schrijven
Dit is binnen Microsoft 365 dus per gebruiker in te stellen; wel zo veilig.

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!

Voorleesontbijt door Mickel: Stap maar op mijn bezemsteel

Vandaag zijn de nationale voorleesdagen begonnen. Ook SQLTeam.NL levert hier graag een bijdrage aan met een voorleesmoment. Het voorleesmoment zal op zaterdag 23 januari 2021 om 8:30 uur zijn en kan via Microsoft Teams worden gevolgd (zie deze link).

Het boek dat zal worden gelezen is het boek “Stap maar op mijn bezemsteel” van Julia Donaldson. Het verhaal gaat over een heks die mooie avonturen op en met haar bezemsteel meemaakt. Pak op 23 januari een boterham en een glas melk, ga er lekker voor zitten en luister mee!

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.