Paul van Oordt  SQL Services

Freelance SQL Server specialist en troubleshooter, Utrecht / Antwerpen

0627400408 - WhatsApp - info@vanoordt.nl
LinkedIn - curriculum vitae
vanoordt.nl - English
nieuwsbrief

Aanbevelingen voor SQL Server beheer

Hier volgt een beknopte lijst van aanbevelingen voor SQL Server beheer. Bij sommige toepassingen zal veel meer aandacht aan de configuratie en het beheer worden besteed dan de onderstaande lijst aangeeft. Andere toepassingen, ook grote en bedrijfskritische, overtreden veel van deze best practices en functioneren nog steeds prima (al zijn er mogelijk niet-onderkende risico's). Deze pagina helpt je de weg te vinden bij het bepalen van SQL Server beheer. Dit is tevens de disclaimer: Een enkel recept dat goed is voor alle toepassingen is niet te geven. (Dan was het wel out of the box geïmplementeerd.) Voer wijzigingen alleen uit als je goed weet wat je doet en test het altijd zorgvuldig. Er is geen garantie dat de aanbevelingen op deze pagina in jouw geval juist zijn.

Planning, selectie van editie

Bepaal welke editie van SQL Server benodigd is. In het algemeen is de Enterprise Edition alleen benodigd voor zeer grote en kritieke 24/7 toepassingen. Soms volstaat ook de gratis Express Edition. De Web Edition is alleen beschikbaar voor third-party hosting service providers. Zie de SQL Server Feature Comparison.

Bepaal het licentiemodel, per server + CAL of per core. SQL Server pricing.

Bepaal of je installeert op een fysieke of virtuele machine. Bedenk dat je in het eerste geval alle fysieke cores voor SQL Server moet licenseren. Dat maakt dat je liever niet te veel cores in je server hebt, wat later opschalen bemoeilijkt. Overweeg ook Azure SQL Managed Instance.

Bepaal of software assurance gewenst is. Dit geeft het recht op nieuwe software versies én op het gebruik van een stand-by server.

Onderzoek of Azure SQL Database een optie is. Zeker voor nieuwe niet al te zware database-toepassingen kan dit een voordelige en zorgeloze optie zijn. Voor bestaande toepassingen kan onderzocht worden of migratie praktisch is.

Plan upgrades, het is in het algemeen een goed idee de laatste of in ieder geval op een recente versie te werken. Er zijn allerlei features die verbeteringen brengen, ook zonder wijzigingen in de programmatuur.

Hardware

Een belangrijke keuze is of je SQL Server gaat draaien op een fysieke machine, op een virtuele machine in eigen beheer, of bijvoorbeeld als Azure virtual machine in de cloud. In alle gevallen zijn voldoende resources uiteraard van belang voor een soepel werkend systeem. Het upscalen en downscalen van de server is in Azure en meestal ook bij andere virtuele machines makkelijker dan bij fysieke. Zorg dat er altijd de mogelijkheid is te downscalen, en minder kosten te maken. Zo kan je verbeteringen aan de performance van je programmatuur meteen te gelde maken. Uiteraard is ook de mogelijkheid te upscalen van belang.

Er is geen simpele formule te geven wat voldoende hardware is. Het hangt af van erg veel factoren zoals de omvang van de (actieve) data, het aantal gelijktijdige sessie en de kwaliteit van het datamodel, de queries en de indexering. Is de server eenmaal operationeel, dan is wel redelijk eenvoudig vast te stellen of er voldoende resources zijn. Zie daarvoor aanbevelingen voor SQL Server performance.

Voldoende geheugen is een eenvoudige en voordelige manier om te zorgen voor een soepel lopende database-server.

Disk-IO is een bepalende factor voor het goed presteren van SQL Server, en meer naarmate er minder geheugen is. Voor alle databases behalve de tempdb is de leessnelheid van de datafiles belangrijker dan de schrijfsnelheid. Voor databases met veel modificaties is ook de schrijfsnelheid van de logfile belangrijk. De snelheid van disk-IO is goed te meten, zowel vanuit SQL Server als met specifieke tools zoals diskspd. Op basis daarvan kunnen indien nodig verbeteringen worden aangebracht.

Het vooraf bepalen van het aantal benodige CPU-cores is lastig. Een complicatie is dat bij een per-core licentie alle fysieke cores voor SQL Server gelicenseerd moeten zijn. In het geval van een virtuele machine is het makkelijker om met weinig cores te beginnen, indien nodig cores toe te voegen en deze extra te licenseren.

Onderzoek de mogelijkheid je Windows servers met SQL Server als Azure virtual machines te draaien in de cloud. In geval van virtualisatie, zorg bij voorkeur voor dedicated resources.

Installatie en configuratie

Installatie van SQL Server is niet al te ingewikkeld, maar bedenk dat het om een ongelooflijk schaalbaar product gaat dat goed werkt bij kleine en ook bij heel grote implementaties. Sommige zaken moeten dus toch met de hand geconfigureerd worden. Het is onmogelijk een voor iedere toepassing goed systeem out of the box te installeren.

Installeer patches wanneer nodig. Zie de Latest updates.

Geef het account waaronder SQL Server draait het Perform Volume Maintenance Task privilege. Dat versnelt het aanmaken en vergroten van files. Als je het service account wilt wijzigen, gebruik dan de SQL Server Configuration Manager, dan worden meteen de rechten goed gezet. Doe dit niet met services.msc.

Zorg ervoor dat de tempdb goed presteert. Plaats de files op snelle schijven, wellicht SSD, en voorkom dat de files op runtime vergroot moeten worden. Het kan voordelig zijn meerdere tempdb datafiles aan te maken, met gelijke grootte en gelijke groei-settings, zelfs op dezelfde fysieke disks.

Na migratie naar een hogere SQL Server versie blijft het compatibility level van de databases op hetzelfde niveau, tenzij het zo laag stond, dat het door de nieuwe versie niet meer ondersteund wordt. Plan het verhogen van het compatibility level. Dit kan onverwachte performance-gevolgen hebben. Het compatibility level kan op ieder moment weer makkelijk worden teruggezet.

Wijzig een paar van de server-configuratie instellingen:

Beveiliging

Plan de manier waarop rechten worden gegeven zorgvuldig. Er zijn verschillende mogelijkheden en het wordt snel ondoorzichtig. Een goede stelregel is gebruikers indien mogelijk te groeperen in Windows Active Directory en voor deze groepen logins aan te maken in SQL Server.

Indien mogelijk, gebruik alleen Windows-authenticatie. Vermijd individuele Windows accounts toegang te geven, geef alleen toegang aan Windows groepen. Ook dan is de ingelogde gebruiker te behoeve van auditting nog steeds te achterhalen.

Gebruik je mixed authentication, zorg dan voor sterke wachtwoorden van SQL logins en hernoem eventueel het sa-account. Vermijd applicaties die het sa-account gebruiken.

Beperk het aantal logins met sysadmin of control server rechten. Gebruik in het algemeen het principe van minimaal benodigde rechten.

Schakel in je productie-databases het guest account uit.

Gebruik row-level security bijvoorbeeld als data van meerdere klanten in één database wordt opgeslagen (multi tenant).

Wijzig eventueel de TCP-poort waarop SQL Server luistert.

Overweeg de veschillende encryptie-vormen die SQL Server en Windows bieden.

Implementeer een vorm van auditting indien gewenst.

Database setup

Wijzig de default groei-settings voor databasefiles zodat je voorkomt dat de files op runtime vergroot moeten worden. Databases die zijn aangemaakt vóór SQL Server 2016 hebben mogelijk nog steeds de oude settings. Dat geldt in het bijzonder voor logfiles, aangezien deze altijd met nullen geïnitialiseerd worden, en omdat dit kan leiden tot een te groot aantal vitual log files. Het is doorgaans geen goed idee databasefiles regelmatig te verkleinen.

Zet geen ontwikkel-, test-, acceptatie-, of demo-databases op productieservers. Je bemoeilijkt op allerlei manieren het beheer als je dat wel doet. Voor niet-productie databases heb je geen SQL Server licentie nodig. Je kunt ze op de gratis te downloaden SQL Server Developer Edition draaien. Voor niet-productie databases zal doorgaans het simple recovery model passend zijn. Denk eraan dit te wijzigen wanneer een productie-database ge-restored is als test-database.

Haal databases die niet gebruikt worden offline, ze gaan dan ook niet mee in backup- of andere beheerroutines. En je weet echt zeker dat ze niet gebruikt worden. Andere databases kunnen wellicht in read-only modus worden gezet.

High Availability & Disaster Recovery (HADR)

Bepaal de recovery point objective (RPO), het acceptabel dataverlies en de recovery time objective (RTO), de acceptabele downtijd in geval van nood en van gepland onderhoud.

In de meeste gevallen is een dagelijkse full backup praktisch. Voor grote databases kunnen daarnaast differential backups worden gemaakt.

Databases waarvoor weinig dataverlies acceptabel is, dienen een full of bulk-logged recovery model te hebben waarbij de transactielogs bewaard blijven voor een eventuele restore. Backup van de transactielog moet dan plaatsvinden bijvoorbeeld iedere 10 minuten.

Backup-files moeten uiteraard op een goede plaats en veilig bewaard worden en ook weer worden opgeruimd. Adequate routines voor het maken en opruimen van backups kunnen met een SQL Server Maintenance Plan worden gemaakt. Er zijn daarnaast allerlei andere tools beschikbaar, variërend van externe backup software tot open source scripts zoals die van Ola Hallengren. Overweeg eventueel ook backup via url naar Microsoft Azure.

Backups met volume shadow copy service (VSS) zijn ook mogelijk, maar deze bieden geen point in time restore. Indien backup-software gebruikt wordt, let dan op of SQL native backup of VSS wordt gebruikt.

Zorg ook voor actuele backups van de master, msdb en model databases. Van de tempdb hoeft en kan geen backup worden gemaakt.

In de meeste gevallen is backup compression een goede keuze.

Doe regelmatig een restore-test inclusief log tail backups en applicatie-connectiviteit. Zorg voor een plan. Voor een restore naar een andere server kan al veel voorbereid worden. Wordt de acceptabel downtijd overschreden, zorg dan voor een high availability oplossing zoals een failover cluster of een (Basic) Availability Group.

Zorg voor een regelmatige dbcc checkdb. Ook dit kan met een Maintenance Plan.

Index en statistics beheer

Index-fragmentatie is meestal niet zo'n groot probleem als wordt gedacht. Toch kan het handig zijn zo nu en dan herindexering uit te voeren, ook om vrije ruimte in een index beschikbaar te maken. Gebruik idealiter een script dat actie neemt op basis van vastgestelde fragmentatie. Het alternatief is een Rebuild Index of Reorganize Index stap in een maintenance plan, wat ook prima kan zijn als er voldoende resources zijn. Bepaal, eventueel per index, of je een rebuild of reorganize doet. De eerste is grondiger, maar neemt een lange exclusieve lock op de index die ander gebruik onmogelijk maakt (behalve in de Enterprise Edition).

Updates van de statistics, inexacte informatie over de grootte van tabellen en de verdeling van waardes in kolommen die wordt gebruikt bij het maken van query plannen, vindt automatisch plaats. Niettemin is het vaak verstandig dit ook uit te voeren als deel van de onderhoudstaken, bijvoorbeeld nachtelijk of in het weekend.

Zowel index-defragmentatie als statistics-updates kunnen worden uitgevoerd met een Maintenance Plan.

Monitoring

Monitor regelmatig belangrijke indicators als geheugendruk (page life expectancy), cpu-gebruik, IO-performance en blocking.

Zorg ervoor dat het falen van jobs wordt genotificeerd. Configureer daartoe een mail profile. Maak een job 'testnotify' waarmee je notificatie heel eenvoudig kan testen. Zorg er ook voor dat het optreden van fouten vanaf bijvoorbeeld niveau 19 wordt genotificeerd.

Bekijk zo nu en dan de missing indexes statistieken.

Bekijk zo nu en dan deadlocks in de system_health extended events session.

Inspecteer regelmatig de SQL Server errorlogs. Maak en schedule eventueel een job die bijvoorbeeld wekelijks een log rollover uitvoert. Geslaagde backup operaties kunnen uit de error log geweerd worden met traceflag 3226.

De msdb bevat historische informatie over onder andere het uitvoeren van jobs. Schedule een clean up van deze informatie.

Monitor disk space en file growth. Het plotseling vollopen of groeien van logfiles is vaak een indicatie dat er wat mis is, bijvoorbeeld een lang openstaande transactie, of een fout in replicatie of high-availability.

Schakel voor de belangrijkste databases de Query Store in. Je hebt dan altijd een overzicht van de werklast inclusief duurste en plotseling verslechterende queries.

Draai regelmatig het open source script sp_blitz. Veel van de op deze pagina aangegeven aanbevelingen vind je daarin terug.

Scaling out

Wees terughoudend met scaling out in de vorm van bijvoorbeeld replicatie. Gebruik het als het echt nodig is. Je introduceert veel complexiteit.

Overweeg stretch databases voor archiveren van oude data.

 

(c) Paul van Oordt, vanoordt.nl
 

vanoordt.nl Laatst gewijzigd: 19 december 2022