drs Paul van Oordt MCITP‑dba

Freelance SQL Server specialist en troubleshooter gevestigd te Utrecht

paul@vanoordt.nl - 0627400408

LinkedIn - curriculum vitae

vanoordt.nl - English
 

Aanbevelingen voor goed SQL Server beheer

Hier volgt een beknopte lijst van aanbevelingen voor goed SQL Server beheer. Iedere toepassing vereist beheer op maat. Deze lijst kan je helpen bij het opstellen daarvan. Dit is tevens de disclaimer, voer wijzigingen alleen uit als je goed weet wat je doet en test het altijd zorgvuldig. Er is geen garantie dat de aanbevelingen of de code op deze pagina (in alle of zelfs sommige gevallen) juist zijn.

Planning, selectie van editie

  • Bepaal welke editie van SQL Server benodigd is. In het algemeen is de dure 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.
  • Bepaal het licentiemodel, per server + CAL of per core.
  • Bepaal of software assurance gewenst is. Dit geeft het recht op nieuwe software versies (service packs kun je altijd installeren) 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.

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 te draaien 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 in je contract altijd de mogelijkheid open is te down scalen, 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 is. Het hangt af van erg veel factoren waaronder natuurlijk de omvang van de (actieve) data, maar ook bijvoorbeeld van de kwaliteit van 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 SQLIO of CrystalDiskMark. 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 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. Veel delen werken goed zowel bij kleine als heel grote implementaties, maar sommige zaken moeten toch met de hand geconfigureerd worden. Het is onmogelijk een voor iedere toepassing goed systeem out of the box te installeren.

  • Zorg ervoor dat de tempdb goed presteert:
    • Plaats de files op snelle schijven, wellicht SSD.
    • Voorkom dat de files op runtime vergroot moeten worden.
    • Het kan voordelig zijn meerdere tempdb datafiles aan te maken, met gelijke grootte en gelijke groeisettings, zelfs op dezelfde fysieke disks.
    • traceflag
  • Gebruik de SQL Server Configuration Manager om het service account aan te passen, dan worden meteen de rechten goed gezet. Doe dit niet met services.msc.
  • Wijzig een paar van de server-configuratie instellingen:
    • Zet "show advanced options" op 1 om enkele van de volgende settings te kunnen doen.
    • Zet "remote admin connections" op 1 om in geval van nood een dedicated admin connection toe te staan vanaf een andere host.
    • Zet "max server memory (MB)" op bijvoorbeeld ¾ van het fysiek geheugen om te voorkomen dat andere processen last hebben van een te gulzige SQL Server - de waarde kan natuurlijk veel nauwkeuriger bepaald worden; niet vergeten aan te passen bij een wijziging in de hoeveelheid fysiek geheugen. V.a. SQL Server 2012 heeft deze waarde betrekking op alle SQL Server-geheugen, niet meer alleen op de buffer pool.
    • Zet "max degree of parallelism" op niet meer dan het aantal fysieke cores per NUMA-node.
    • Zet "cost threshold for parallelism" op bijvoorbeeld 25 wat in de meeste gevallen beter werkt dan de default van 5.
    • > toon script

      exec sp_configure 'show advanced options', 1

      reconfigure

      exec sp_configure

      < verberg script

Database setup

  • Bepaal of significant dataverlies acceptabel is, en zo niet kies voor een full of bulk-logged recovery model.
  • Files
    • Wijzig de default groei-settings (vr SQL Server 2016) voor databasefiles zodat je voorkomt dat de files op runtime vergroot moeten worden. 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.
    • Gebruik eventueel read-only filegroups.

Index en statistics beheer

  • Zorg voor regelmatige defragmentatie van indexes.
  • 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.
  • 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).
  • Update statistics..

Beveiliging

  • Indien mogelijk, gebruik alleen Windows-authenticatie.
  • Zorg voor sterke wachtwoorden van SQL logins.
  • 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.
  • 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.
  • Plan de manier waarop rechten worden gegeven zorgvuldig. Er zijn verschillende mogelijkheden en het wordt snel ondoorzichtig.
  • Overweeg een model waarbij je alleen rechten geeft op stored procedures en niet op de onderliggende tabellen.
  • 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).
  • Overweeg de veschillende encryptie-vormen die SQL Server en Windows bieden.
    • Transparant data encryption om..
    • Always encrypted waarmee data onleesbaar kan worden ook voor sysadmins.
    • Encrypted file system om de files op disk onleesbaar te maken voor alle accounts behalve de SQL Server service account.
    • Dynamic data masking
    • Encrypted backups.
  • Wijzig eventueel de TCP-poort waarop SQL Server luistert.
  • Auditting
    • Mislukte inlog-pogingen worden gelogd in de default trace.
    • Extra server side traces kunnen worden gecreerd om uitgebreidere auditting te doen.
    • Mislukte inlog pogingen worden ook gelogd in de error log. Dit kan worden uitgebreid met succesvolle logins.
    • Met SQL Server audits kan zeer gedetailleerd worden ge-audit, bijvoorbeeld ook datamodificaties.

Backup

  • Bepaal recovery point objective acceptabel dataverlies en recovery time objective, acceptabele downtijd.
  • In de meeste gevallen is een dagelijkse full backup praktisch.
  • Voor grote databases kunnen differential backups worden gemaakt.
  • In de meeste gevallen is backup compression een goede keuze.
  • 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.
  • Voor alle databases met een full of bulk-logged recovery model configureer transactielog backups bijvoorbeeld iedere 10 minuten.
  • Zorg ook voor actuele backups van de master, msdb en model databases. Van de tempdb hoeft en kan geen backup worden gemaakt.
  • 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.
  • Overweeg backup via url naar Microsoft Azure.

Monitoring

  • Monitor regelmatig belangrijke indicators als geheugendruk (page life expectancy), cpu-gebruik, IO-performance, blocking.
  • Zorg voor een regelmatige "dbcc checkdb"
  • Configureer een mail profile ten behoeve van notificatie.
  • Zorg ervoor dat het falen van jobs wordt genotificeerd.
  • Zorg ervoor dat het optreden van fouten vanaf bijvoorbeeld niveau 19 wordt genotificeerd.
  • Bekijk zo nu en dan de missing indexes statistieken.
  • Configureer een server side trace of xevent session voor deadlocks, (system_health bevat een xml deadlock report).
  • Test of je in staat bent een Dedicated Admin Connection te gebruiken.
  • Inspecteer regelmatig de SQL Server errorlogs.
  • Maak en schedule eventueel een job die bijvoorbeeld wekelijks een log rollover uitvoert.

    code

    exec sp_cycle_errorlog

    verberg

Beschikbaarheid

Bepaal de beschikbaarheids-eisen. Hoe lang mag de server in geval van nood uit de lucht zijn? En in geval van gepland onderhoud.

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, www.vanoordt.nl
 

www.vanoordt.nl Laatst gewijzigd: 11 april 2015