Een SQL Server-onderhoudsplan maken
Typische activiteiten die u kunt plannen voor regelmatig ONDERHOUD van SQL Server zijn onder andere:
- Back-ups van database- en transactielogboeken
- Controles voor databaseconsistentie
- Indexonderhoud
- Updates voor statistieken
Het is van cruciaal belang om inzicht te hebben in het belang van back-ups, evenals het onderhoud van indexen en statistieken voor al uw databases. Databaseconsistentiecontroles, ook wel CHECKDB genoemd (met behulp van de opdracht DBCC CHECKDB), zijn even belangrijk omdat ze de enige manier zijn om een volledige database te controleren op beschadiging. Afhankelijk van de grootte van uw databases en uw uptimevereisten, kunt u al deze activiteiten 's nachts uitvoeren. In productiesystemen worden onderhoudsbewerkingen echter vaak verspreid over de week, omdat zowel indexonderhoud als consistentiecontroles zeer I/O-intensief zijn en meestal tijdens weekenduren worden uitgevoerd.
Veel DBA's verspringen back-ups van grote databases, voeren één volledige back-up per week uit en maken gebruik van differentiële en transactielogboekback-ups om herstel naar een bepaald tijdstip te beheren. SQL Server biedt een ingebouwde manier om al deze taken te beheren met behulp van onderhoudsplannen. Onderhoudsplannen maken een werkstroom van taken ter ondersteuning van uw databases en worden gemaakt als Integration Services-pakketten, zodat u uw onderhoudsactiviteiten kunt plannen. Daarnaast maken veel DBA's gebruik van opensourcescripts voor databaseonderhoud om meer flexibiliteit en controle over onderhoudsactiviteiten te krijgen.
Aanbevolen procedures voor onderhoudsplannen
Onderhoudsplannen helpen u niet alleen bij het uitvoeren van databaseonderhoud, maar bieden ook opties voor het verwijderen van gegevens uit de msdb-database, die fungeert als het gegevensarchief voor de SQL Server Agent. Met onderhoudsplannen kunt u ook het verwijderen van oudere databaseback-ups van schijf opgeven. Als u oude back-upbestanden verwijdert, verkleint u de grootte van het back-upvolume en kunt u de grootte van de msdb-database beheren.
Zorg ervoor dat de bewaarperiode voor back-ups langer is dan het venster voor consistentiecontrole. Als u bijvoorbeeld wekelijks een consistentiecontrole uitvoert, moet u voldoende back-upgeschiedenis behouden om te herstellen van mogelijke beschadigingen die tijdens consistentiecontroles zijn gedetecteerd. Houd er rekening mee dat de back-upbewerking geen beschadiging in een database detecteert, dus het is mogelijk om beschadiging in een back-upbestand te hebben. Onderhoudsplanactiviteiten worden gepland als SQL Server Agent-taken voor uitvoering.
Een onderhoudsplan maken
U kunt een onderhoudsplan maken met BEHULP van SQL Server Management Studio, zoals hieronder wordt weergegeven. In het voorbeeld worden meerdere onderhoudstaken gecombineerd tot één onderhoudsplan. De best practice is echter om een afzonderlijk onderhoudsplan te maken voor elk type taak, en mogelijk zelfs voor specifieke databases op uw server. U kunt bijvoorbeeld één onderhoudsplan maken voor het maken van back-ups van systeemdatabases en een andere om een back-up te maken van gebruikersdatabases. Daarnaast kunt u een afzonderlijk onderhoudsplan hebben voor het afhandelen van de back-up van een bijzonder grote gebruikersdatabase. In de onderstaande afbeelding en de volgende voorbeelden ziet u hoe u een onderhoudsplan maakt met behulp van de wizard Onderhoudsplan.
In de afbeelding ziet u het eerste scherm van de wizard Onderhoudsplan in SQL Server Management Studio (SSMS). U moet een naam opgeven voor uw onderhoudsplan en een uitvoeren als-account. De meeste onderhoudstaken worden uitgevoerd als het SQL Server Agent-serviceaccount, maar voor beveiligingsdoeleinden moeten sommige taken mogelijk als een ander account worden uitgevoerd. Als u bijvoorbeeld een back-up moet maken van een bestandsshare die alleen toegankelijk is voor een specifiek account, gebruikt u een proxygebruiker, een onderdeel van de SQL Server Agent.
Wat is een proxyaccount?
Een proxyaccount is een account met opgeslagen referenties die de SQL Server Agent kan gebruiken om specifieke taakstappen uit te voeren als een aangewezen gebruiker. De aanmeldingsgegevens voor deze gebruiker worden opgeslagen als referentie in het SQL Server-exemplaar. Proxyaccounts worden doorgaans gebruikt wanneer voor specifieke taakstappen zeer gedetailleerde beveiligingsrechten zijn vereist.
Stel dat u een SQL Server Agent-taak hebt die een back-up van een database naar een netwerkbestandsshare moet maken. Als het SQL Server Agent-serviceaccount geen toegang heeft tot de bestandsshare, kunt u een proxyaccount maken met de benodigde machtigingen. Dit proxyaccount kan vervolgens worden gebruikt om de back-upstap uit te voeren, zodat het over de vereiste toegangsrechten beschikt.
Jobplanningen
Taakplanningen maken deel uit van het taaksysteem in de msdb systeemdatabase. SQL Server Agent-taken en -planningen hebben een veel-op-veel-relatie, wat betekent dat elke taak meerdere planningen kan hebben en dat elke planning kan worden toegewezen aan meerdere taken. De wizard Onderhoudsplan staat het maken van onafhankelijke planningen echter niet toe. In plaats daarvan maakt het een specifiek schema voor elk onderhoudsplan.
In het volgende voorbeeld ziet u het schema voor een wekelijkse uitvoering, maar u hebt ook de mogelijkheid om een planning te maken met elk uur of dagelijks terugkeerpatroon.
De volgende stap bestaat uit het selecteren van de onderhoudstaken die u aan het plan wilt toevoegen. In het volgende voorbeeld ziet u de bewerkingen die door uw onderhoudsplan kunnen worden uitgevoerd.
Databaseintegriteit controleren: met deze taak wordt de DBCC CHECKDB opdracht uitgevoerd om de logische en fysieke consistentie van elke databasepagina te valideren. U moet deze taak regelmatig uitvoeren en uitlijnen op het bewaarvenster voor back-ups. Zorg ervoor dat u een consistentiecontrole voltooit voordat u eventuele eerdere back-ups verwijdert om beschadiging te voorkomen.
Database verkleinen : met deze taak wordt de grootte van een database- of transactielogboekbestand verkleind door gegevens naar vrije ruimte op pagina's te verplaatsen. Zodra er voldoende ruimte vrij is, kan deze worden geretourneerd naar het bestandssysteem. Het wordt aanbevolen om deze actie niet op te nemen in regelmatig onderhoud omdat dit ernstige indexfragmentatie veroorzaakt, waardoor de databaseprestaties worden beschadigd. De bewerking is ook zeer I/O- en CPU-intensief, wat de systeemprestaties aanzienlijk kan beïnvloeden.
Index opnieuw ordenen/herbouwen : met deze taak wordt het niveau van fragmentatie in de indexen van een database gecontroleerd en wordt de index opnieuw opgebouwd of opnieuw georganiseerd op basis van het door de gebruiker gedefinieerde fragmentatieniveau. Als u een index opnieuw bouwt, worden ook de statistieken bijgewerkt.
Statistieken bijwerken: met deze taak worden de kolom- en indexstatistieken bijgewerkt die door SQL Server worden gebruikt om queryuitvoeringsplannen te bouwen. Nauwkeurige statistieken zijn cruciaal voor de queryoptimalisatie om de beste beslissingen te nemen. U kunt kiezen welke tabellen en indexen u wilt scannen en het percentage of het aantal rijen dat u wilt scannen. De standaardsampling is meestal voldoende, maar mogelijk hebt u meer gedetailleerde statistieken nodig voor specifieke tabellen.
Opschoningsgeschiedenis : met deze taak verwijdert u de geschiedenis van back-up- en herstelbewerkingen uit de msdb database, evenals de geschiedenis van SQL Server Agent-taken. Hiermee kunt u de grootte van de msdb-database beheren.
SQL Server Agent-taak uitvoeren : met deze taak wordt een door de gebruiker gedefinieerde SQL Server Agent-taak uitgevoerd.
Back-updatabase (volledig/differentieel/logboek): met deze taak wordt een back-up gemaakt van databases op een SQL Server-exemplaar. Een volledige back-up legt de volledige database vast en fungeert als uitgangspunt voor een herstelbewerking. Differentiële back-ups leggen de pagina's vast die zijn gewijzigd sinds de laatste volledige back-up, waardoor een incrementeel herstelpunt wordt geboden. Back-ups van transactielogboeken leggen de actieve pagina's in uw transactielogboek vast, zodat u uw herstelpuntdoelstelling kunt definiëren. Houd er rekening mee dat back-ups van transactielogboeken niet kunnen worden uitgevoerd op databases in de EENVOUDIGE herstelmodus.
Als u bijvoorbeeld elke week vanavond een volledige back-up en een differentiële back-up wilt maken, herstelt u de volledige back-up van zondag, de differentiële back-up van woensdag en de back-ups van het transactielogboek van woensdag naar donderdag om middag.
Onderhoudsopschoontaken : met deze taak worden oude bestanden verwijderd die betrekking hebben op onderhoudsplannen, inclusief tekstrapporten en back-upbestanden. Er worden alleen back-ups in de opgegeven mappen verwijderd, dus alle submappen moeten expliciet worden vermeld of ze worden overgeslagen.
Elke taak kan worden afgestemd op gebruikersdatabases, systeemdatabases of een aangepaste selectie van databases, en elke taak heeft specifieke configuratieopties.
Bij het maken wordt het plan weergegeven als een taak in de SQL Server Agent. Als u een planning hebt toegevoegd tijdens het aanmaakproces of daarna, wordt die taak uitgevoerd en worden de onderhoudstaken uitgevoerd.
Omgeving met meerdere servers
In een omgeving met meerdere servers kunt u met SQL Server Agent één server aanwijzen als een hoofdserver die taken op andere servers kan uitvoeren, ook wel doelservers genoemd. De hoofdserver slaat een hoofdkopie van de taken op en distribueert deze naar de doelservers. Doelservers maken periodiek verbinding met de hoofdserver om hun taakschema's bij te werken. Met deze installatie kunt u een taak eenmaal definiëren en implementeren in uw hele onderneming. U kunt bijvoorbeeld databaseonderhoudstaken op de hoofdserver configureren en deze pushen naar een groep doelservers, waardoor consistente implementatie wordt gegarandeerd.