KATHOLIEKE HOGESCHOOL KEMPEN GEEL
SQL SERVER 2008 Werking van Database Snapshots
ELINE STEYVERS BRAM DE SMEDT JOEY LEMMENS
WOORD VOORAF “Werking van Database Shapshots” is bedoeld om mensen wegwijs te maken met de functie Database Snapshots van SQL Server 2005 Enterprise Edition en latere versies. We zullen u in dit document het concept van deze functie duidelijk maken. We zullen u uitleggen wanneer het nuttig is om gebruik te maken van Database Snapshots, hoe de functie precies werkt. We zullen u ook de voor- en nadelen kort toelichten. Aan de hand van een uitgewerkt voorbeeld kan u makkelijk thuis of op het werk zelf aan de slag in Microsoft SQL Server. Het voorbeeld opgenomen in deze handleiding zijn fictief en dienen louter ter ondersteuning.
3
INHOUDSOPGAVE WOORD VOORAF.................................................................................................................. 2 INHOUDSOPGAVE ................................................................................................................ 3 INLEIDING .............................................................................................................................. 4 1
Werking van database snapshots ................................................................................. 5
1.1 1.2 1.3 1.3.1 1.3.2 1.4 1.5 1.5.1
Wat is een database snapshot? ........................................................................................ 5 Waarom deze functie gebruiken? .................................................................................... 5 Wat zijn de voor- en nadelen? ......................................................................................... 5 Voordelen .................................................................................................................... 5 Nadelen ........................................................................................................................ 6 Hoe werkt de functie? ..................................................................................................... 6 Hoe een database snapshot maken? ................................................................................ 7 Fictief voorbeeld .......................................................................................................... 7
Besluit ...................................................................................................................................... 11 Lijst van de figuren ................................................................................................................ 12 Literatuurlijst ......................................................................................................................... 13
4
INLEIDING Wat ga je doen als je een database op een bepaald tijdstip wil analyseren? Hoe ga je te werken als je bijvoorbeeld om twaalf uur een database wil analyseren? Hoogstwaarschijnlijk ga je een automatische back-up plannen om twaalf uur en daarna de database terugzetten op de server onder een andere naam. Daarna zou je de teruggezette database kunnen analyseren. Het probleem met deze methode is dat als je een grote database zou hebben, het uren kan duren vooraleer de back-up is gemaakt en dat het enorm veel schijfruimte kan innemen. Data analyseren op piekmomenten vraagt ook veel resources van de server waardoor andere belangrijkere taken op de achtergrond geschoven worden. SQL Server 2005 introduceerde een nieuwe functie namelijk, Database Snapshot, die veel gemakkelijker bovenstaande problemen zal oplossen. Naast dit zijn er ook nog andere mogelijkheden waarom je database snapshot zou gebruiken zoals het beheren van testdatabases, het beschermen tegen gebruikersfouten of voor het herstellen van gegevens. In deze handleiding zal je een grondig uitleg krijgen over deze functie. Zowel het theoretische aspect als de praktijk zal aan bod komen.
5
1
Werking van database snapshots
1.1
Wat is een database snapshot?
Een database snapshot is een alleen-lezen, statische weergave van een database. Je kan een database snapshot gebruiken voor verschillende doeleinden bijvoorbeeld voor controle van gegevens, reportage of voor het herstellen van gegevens. Het grootste pluspunt bij het gebruiken van database snapshots is niet alleen de snelheid waarmee je snapshots kan maken, maar ook de mogelijkheid om meerdere snapshots te maken op verschillende tijdstippen.
1.2
Waarom deze functie gebruiken?
Er zijn tal van redenen waarom je database snapshot zou gebruiken. In het algemeen kunnen we de volgende punten naar voren schuiven: Behouden van historische gegevens voor het genereren van rapporten: een database snapshot is een momentopname van een database. Een snapshot kan toegang krijgen van een bepaalde tijdstip. Voor het uitvoeren van belangrijke updates: wanneer je updates gaat uitvoeren of een schema gaat wijzigen is het verstandig om eerst een database snapshot te creëren van de primaire database. Op die manier kan je eventuele fouten herstellen. Terugkeren naar een momentopname van de database is veel sneller dan het herstellen vanaf een back-up. Gegevens beschermen tegen gebruikersfouten. Beheren van een testdatabase: in een testomgeving kan het zeer makkelijk zijn om snapshots te maken. De applicatieontwikkelaar of de tester maakt best een database snapshot van de test database. Na elke test kan men op die manier de database snel herstellen naar zijn voorafgaande toestand.
1.3
Wat zijn de voor- en nadelen?
1.3.1 Voordelen Hieronder staan de belangrijkste voordelen om gebruik te maken van database snapshots.
Het maken van een database snapshot is zeer snel. De snapshot bestanden zijn klein. Een database kan meerdere snapshots hebben. Snapshots bieden een gemakkelijke, read-only, point-in-time kopie van jouw gegevens. Database snapshots kunnen gebruikt worden als recovery databases bij administratieve- of gebruikersfouten.
6
1.3.2 Nadelen Database snapshots kunnen de database niet beschermen tegen schijffouten of corruptie van de database. Als de primaire database offline gaat, is de snapshot ook niet meer toegankelijk. Een back-up maken van een snapshot is niet mogelijk. Alleen beschikbaar in de SQL Server Enterprise Edition 2005 of hoger. Database snapshots zijn verbonden met de primaire database, je kan ze niet losmaken van deze database.
1.4
Hoe werkt de functie?
Het is niet noodzakelijk om te weten hoe de functie werkt om het te kunnen gebruiken, maar het kan altijd een hulpmiddel zijn. Wanneer je een database snapshot maakt zal de database toegewezen worden aan een leeg reservebestand. Als je data leest van de snapshot, lees je eigenlijk data van de primaire database. Wanneer er wijzigingen gebeuren in de primaire database, de originele database, wordt de data verplaatst naar het reserve bestand. Deze operatie noemen ze ook wel de copy-on-write operation.
Figuur 1 - copy-on-write operation
7
1.5
Hoe een database snapshot maken?
In het volgende voorbeeld zie je hoe een database snapshot wordt gemaakt. CREATE DATABASE Test_Snapshot_20111207 ON ( NAME = 'Test_Data', FILENAME = 'C:\SSDB.ss' ) AS SNAPSHOT OF Test;
1.5.1 Fictief voorbeeld Als je het volgende voorbeeld bestudeerd wordt het wellicht al een pak duidelijker. Het aanmaken van een snapshot, wijzigingen doorvoeren en terugzetten et verwijderen van snapshots worden hier besproken. Je kan het voorbeeld ook uitvoeren op een SQL Server. We maken een nieuwe database aan die we de naam TestDB noemen. In deze database zit één tabel met enkele waarden. Vervolgens maken we onmiddellijk een database snapshot aan. USE master GO --Creëer testdatabase CREATE DATABASE TestDB GO USE TestDB GO --Tabel aanmaken met waarden CREATE TABLE Tabel (Id INT IDENTITY, Waarde VARCHAR(10)) INSERT INTO Tabel VALUES ('Eerste'); INSERT INTO Tabel VALUES ('Tweede'); INSERT INTO Tabel VALUES ('Derde'); GO --Creëer database snapshot CREATE DATABASE SnapshotDB ON ( NAME = TestDB, FILENAME = 'D:\SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SSDB.ss' ) AS SNAPSHOT OF TestDB; GO
Wanneer we de eigenschappen van het originele databasebestand opzoeken vinden we de volgende resultaten:
8
Figuur 2 - eigenschappen originele database
Figuur 3 toont de eigenschappen van het database snapshotbestand.
Figuur 3 - eigenschappen snapshotbestand
9
Om de inhoud van de snapshot te bekijken voeren we de volgende query uit. Merk op dat dit net hetzelfde is als de inhoud van de originele database. SELECT * FROM TestDB.dbo.Tabel SELECT * FROM SnapshotDB.dbo.Tabel
Vervolgens gaan we alle waarden uit de tabel Tabel verwijderen van de originele database. We selecteren hierna opnieuw de waarden uit de originele database alsook die van de snapshot. In figuur 4 zie je het resultaat hiervan. DELETE FROM TestDB.dbo.Tabel GO SELECT * FROM TestDB.dbo.Tabel SELECT * FROM SnapshotDB.dbo.Tabel
Figuur 4 - resultaat na verwijderen
We hebben net data verwijderd uit de originele database dus worden de data pages gekopieerd naar de snapshot database. Daarom is het bestand ook toegenomen in grootte. In figuur 5 zie je het resultaat hiervan.
10
Figuur 5 - eigenschappen snapshotbestand na kopiëren data pages
We hebben net alle data uit de originele database verwijderd, we hebben hier eigenlijk een gebruikersfout gesimuleerd. We kunnen onze snapshot gebruiken om de originele database te herstellen. Dit gebeurt op de volgende manier. USE master GO --terugzetten database RESTORE DATABASE TestDB FROM DATABASE_SNAPSHOT = 'SnapshotDB' GO --waarden selecteren SELECT * FROM TestDB.dbo.Tabel SELECT * FROM SnapshotDB.dbo.Tabel
De originele database werd hersteld m.b.v. de database snapshot. Een simpel drop-statement volstaat om de database snapshot te verwijderen. --database verwijderen DROP DATABASE [SnapshotDB] DROP DATABASE [TestDB]
11
Besluit Database Snapshot is een handige functie die kan gebruikt worden in SQL Server Enterprise Edition (minstens de 2005-vesie). Het is geen alternatief voor een back-up, een snapshot is meer gericht op de analyse van een database, ook al is het mogelijk om de data uit de snapshot ”terug te plaatsen” in de originele database. De grote voordelen van een snapshot maken zijn dat het zeer snel kan gebeuren en de snapshotfile heel klein is. Het hele proces (zowel aanmaken als omgang met snapshot) is vrij simpel, en er is niet veel voorkennis vereist van SQL. Hiertegenover staat wel dat de database snapshot altijd verbonden is met de primaire database. Wanneer deze laatste dus offline gaat, is het snapshot ook niet meer beschikbaar. De Database Snapshot functie moet dus niet gezien worden als een alternatief voor een backup, aangezien het altijd in verbinding staat met de database. Het is een handige en gebruiksvriendelijke tool om een database te analyseren, zonder per ongeluk belangrijke gegevens aan te passen of te verwijderen in de primaire database.
12
Lijst van de figuren Figuur 1 - copy-on-write operation ............................................................................................ 6 Figuur 2 - eigenschappen originele database ............................................................................. 8 Figuur 3 - eigenschappen snapshotbestand ................................................................................ 8 Figuur 4 - resultaat na verwijderen ............................................................................................ 9 Figuur 5 - eigenschappen snapshotbestand na kopiëren data pages ......................................... 10
13
Literatuurlijst How Database Snapshots Work. Opgeroepen op 12, 7, 2011, van MSDN: http://msdn.microsoft.com/en-us/library/ms187054.aspx Database Snapshots. Opgeroepen op 12, 7, 2011 van MSDN: http://msdn.microsoft.com/en-us/library/ms175158.aspx How to: Create a Database Snapshot (Transact-SQL). Opgeroepen op 12, 7, 2011 van MSDN: http://msdn.microsoft.com/en-us/library/ms175876.aspx Usage of SQL Server Database Snapshots. Opgeroepen op 12, 7, 2011: http://www.sql-server-performance.com/2008/sql-server-database-snapshot/
