április 2013 havi bejegyzések

Dátum generátor, avagy amire jó még a CROSS JOIN

Éppen a netet böngészgettem, amikor beleakadtam, hogy valakinek egy dátumsorozatot kellett volna előállítania.
A megoldása ez volt:

use tempdb
go

declare 
	 @from_date date = '2010-01-01' 
	,@to_date date = '2010-02-01' 

;With date_sequence( date_name ) as
(
    Select @from_date as date_name
        union all
    Select dateadd(day, 1, date_name)
        from date_sequence
        where date_name < @to_date
)

select * from date_sequence

A megoldás szépen kihasználja a rekurzív cte erejét, egyetlen probléma vele, hogy a rekurziónak van maximuma, tehát egy nagyobb tartománynál könnyen elszálhat.

Helyette mutatnék egy másik, sokkal stabilabb megoldást:

use tempdb
go

if object_id('dbo.number_sequence', 'TF') is not null
	drop function dbo.number_sequence
go

create function dbo.number_sequence (@from smallint, @to smallint)
returns @result table (num varchar(4))
as
begin
	;with cte (number) as (
		select @from as number
		union all
		select cast(number + 1 as smallint)
		from cte
		where number < @to
	)

	insert into @result (num) 
		select case 
				when number < 10 then '0' + cast(number as varchar(4)) 
				else cast(number as varchar(4)) end 
		from cte
	return
end
go


select 
	ev.num + ho.num + nap.num as date_name
from dbo.number_sequence (2000, 2010) ev
cross join dbo.number_sequence (1, 12) ho
cross join dbo.number_sequence (1, 31) nap
where ISDATE(ev.num + ho.num + nap.num) = 1

A eredményhalmaz dátumformátuma pedig tetszés szerint formázható a CONVERT függvénnyel.

Amit a NULL-ról érdemes tudni

Gondoltam gyorsan összedobok egy postot arról, hogy mit érdemes tudni a NULL-ról. Kis buta postnak készült végül egész sok mindent tudok mondani róla. Pl.: bizonyos helyzetekben a NULL = NULL, minden trükk nélkül.

Először is érdemes néhány dolgot megjegyezni:

– Összehasonlításnál a NULL minden relációban FALSE (szerk: valójában UNKNOWN, de lényeg, hogy nem ad vissza sort) értékkel tér vissza (IS szóval kell hasonlítani)

– A NULL-al végzett műveletek NULL-t fognak visszaadni

– A COUNT(*) beleszámolja a NULL-okat, meg a COUNT(mezőnév) nem

– Minden adat típus felveheti a NULL értéket

– A NULL nem foglal helyet (ez csak részben igaz, mert valahogy jelezni kell, hogy adott helyen NULL van)

– Primary key nem lehet olyan oszlop, ami engedi a NULL értéket

– Kezelni az ISNULL, COALESCE függvényekkel, illetve a CASE WHEN utasítással lehet

– A GROUP BY és DISTINCT egyenlőként kezeli a NULL értékeket (NULL = NULL!)

– Az EXCEPT, INTERSECT, UNION operátorok egyenlőként kezelik a NULL értékeket (NULL = NULL!)

Tipp:

– A WHERE feltételben lévő ISNULL és társai teljesítmény gyilkos lekérdezéseket eredményezhetnek, kerüljük őket.

– Default beállítás az SQL Serverben az ANSI_NULLS ON. Ezt meg lehet változtatni a SET ANSI_NULLS OFF paranccsal és akkor tényleges NULL = NULL lesz. Ez a beállítás nem javasolt, de azért előfordulhatnak olyan esetek, amikor jól jöhet.

Demó

-- Összehasonlításnál a NULL minden relációban
-- FALSE (szerk: valójában UNKNOWN, de lényeg, hogy nem ad vissza sort) 
-- értékkel tér vissza (IS szóval kell hasonlítani)
-- Itt 0 sort kell visszakapni
SELECT 1 as igaz
WHERE    1 = NULL
OR    NULL = NULL
OR    NULL <> NULL
OR    NULL > NULL
OR    1 <> NULL
OR    1 / NULL = NULL
-- stb

-- A NULL--al végzett műveletek NULL
--t fognak visszaadni
SELECT 'String' + NULL, 1 - NULL, LEFT(NULL,1)
-- A COUNT(*) beleszámolja a NULL-okat, míg a COUNT(mezőnév) nem
SELECT    count(a) as a,    count(b) as b,    count(*) as ossz
FROM (    
SELECT cast(NULL as int) AS a, 1 AS b
) tmp

-- Minden adat típus felveheti a NULL értéket
-- Ezt nem nagyon kommentálnám, de egy példa
DECLARE
@i int = NULL,
@s varchar(10) = NULL

SELECT @i as i, @s as s

-- Primary key nem lehet olyan oszlop, ami engedi a NULL értéket
DECLARE @tbl TABLE (id INT NULL PRIMARY KEY)

-- Kezelni az ISNULL, COALESCE függvényekkel,
-- illetve a CASE WHEN utasítással lehet
SELECT    
isnull(NULL, 1) as 'isnull',    
coalesce(NULL, 1) as 'coalesce',    
case when null is null then 1 else null end as [null is null],    
case when null is not null then 1 else null end [null is not null]

-- Végül azok a példák maradnak,
-- ahol az SQL Server a NULL = NULL-t használja összehasonlításkor!
-- A GROUP BY és DISTINCT egyenlőként kezeli a NULL értékeket (NULL = NULL!)
SELECT DISTINCT a FROM (    SELECT NULL as a    UNION ALL    SELECT NULL) tmp
SELECT a FROM (    SELECT NULL as a    UNION ALL    SELECT NULL) tmp
GROUP BY a

-- Az EXCEPT, INTERSECT, UNION operátorok
-- egyenlőként kezelik a NULL értékeket (NULL = NULL!)
-- Kivonás
SELECT NULL as a
EXCEPT
SELECT NULL
-- Metszet
SELECT NULL as a
INTERSECT
SELECT NULL
-- Egyesítés
SELECT NULL as a
UNION
SELECT NULL

SQL Server mentés – alapok

Kérték tőlem, hogy írjak a címben megjelölt témáról postot, ha megtehetem. Ám legyen, hátha többőtöknek is hasznára válik. Előre bocsájtom, hogy nem fogok újdonságokat írni.  Az elméleti rész igaz minden SQL Server Edition-re!

Amiről beszélni fogok (röviden):
–    Mentési típusok és jellemzőik
–    Adatbázis recovery modell-ek (mivel ezekkel szorosan összefüggenek a mentések típusai)
–    Backup stratégia
–    Adatbázisok helyre állítása
–    Buta demó

Mentési típusok (backup) és jellemzőik: A mentés típusok szorosan összefüggenek az adatbázisok recovery modell-jével (egyelőre csak jegyezzük meg). Management Studio-ból a következőképpen érhetjük el: Jobb gomb egy adatbázison => Task => Back up… => és a felugró ablakon a backup type combobox-ot ha lenyitjuk, akkor recovery modell-től függően 2 vagy 3 féle lehetőségünk lesz:

–    FULL: Az egész adatbázist menti.

  • Az összes recovery modell-ből elérhető
  • Ajánlott kiterjesztés: .bak
  • Általában ritkábban futtatjuk, a mentések közül ez szokott a legnagyobb méretű lenni és időben hosszabb ideig szokott tartani a futása, mint a másik kettőé.
  • Ahhoz, hogy további mentéseket tudjuk készíteni, egy FULL backupra mindenképpen szükség lesz

–    Differential: A legutóbbi FULL backup óta történt változásokat menti le. (Ha hétfőn készítünk egy FULL backup-ot, kedden és szerdán pedig 1-1 differential backup-ot, csütörtökön pedig elromlik az adatbázis, akkor kell a hétfői full backup és a szerdai differential backup, mivel ez tartalmazza a változásokat a hétfői FULL backup óta, tehát a keddire nem lesz szükség).

  • Az összes recovery modell-ből elérhető
  • Előfeltétele, hogy legyen legalább egy FULL backup
  • Ajánlott kiterjesztés: .bak
  • Sűrűbben futtatjuk, mint az FULL mentést, annál kisebb méretű fájlok szoktak keletkezni és rövidebb ideig tart a futása

–    Transaction log: Az utolsó FULL mentés vagy az utolsó tranzakció log mentés óta történt változásokat menti le.

  • Csak FULL vagy Bulk-logged recovery modellből érhető el
  • Előfeltétele, hogy legyen legalább egy FULL bakcup
  • Ajánlott kiterjesztés: .trn
  • A legsűrűbben ez szokott lefutni, a legrövidebb ideig szokott tartani és a legkisebb méretű fájlok szoktak keletkezni a másik kettőhöz viszonyítva
  • Egyesek incrementális mentésnek is nevezik

Adatbázis recovery modell: Ez határozza meg, hogy a tranzakciós log fájlba (akkor jön létre, amikor egy adatbázist létrehozunk és .ldf szokott lenni a kiterjesztése) hogyan történjen az írás (körkörösen vagy lineárisan). Az SQL Server a tranzakciós log fájlba mindig ír! Management Studio-ból a következőképpen érhetjük el: Jobb gomb az adatbázison => Properties => a felugró ablakon Options => Recovery modell. A lenyíló ablakban 3 féle lehetőségünk lesz:

–    FULL: Nem tévesztendő össze az ugyanilyen nevű backup típussal! Ez egy folyamatos írást tesz lehetővé, azaz az újabb eseményeket (pl: insert, update, delete) hozzáfűzi a tranzakciós log (.ldf kiterjesztés) fájlhoz.  Ebből kitalálható, hogy a tranzakciós log folyamatosan nőni fog, mivel csak hozzáfűz a log fájlhoz, így akár az összes helyet is elfogyaszthatja a diszkről! Ha ez megtörténik, akkor megáll az SQL Server. Hogy ez ne történjen meg, folyamatosan menteni kell a tranzakciós log fájlt (transaction log mentés, lásd feljebb). A mentés hatására az SQL Server a tranzakciós log fájl egy részét vagy az egészet kimenti, és az így a kimentett részek fizikailag felülírhatóvá válnak (körkörössé tehető az írás), azaz nem fog tovább növekedni az log fájl. Ez elég nagy biztonságot ad, ha esetleg egy adatbázis megsérül, vagy vissza kellene nézni, hogy pl: 3 óra 15 perc 45 másodperccel ez előtt hogyan nézett ki az adatbázisunk.  Ahol fontos, hogy ne vagy csak minimálisan veszítsünk adatokat, ott ilyen recovery modell-el hozzuk létre az adatbázist. (Pl: product adatbázisok).

–    Simple: Ilyenkor már alapból körkörösen történik az írás a log fájlba, ami azt jelenti, hogy a már véget ért tranzakciók automatikusan felülírhatóak. Pl.: ha most elindítok egy tranzakciót, ami két perc múlva véget ér, akkor az a része a log fájlnak két perc múlva fizikailag is felülírhatóvá válik, tehát a tranzakciós log fájl nem fog folyamatosan nőni, hanem beáll egy méretre és jó eséllyel akkora is marad. Hátránya ennek a modell-nek, hogy az adatvesztés kockázata nagy, mert csak a backup-ok idejére tudunk visszaállni. (Tehát akkorra, amikor full vagy differential backup készült). A ritkán változó (pl: archív) vagy kevésbé fontos adatbázisokat célszerű ilyen modell-be tenni. A tempdb-t viszont kifejezetten ajánlott!

–    Bulk-logged: A bulk műveleteket lazábban naplózza a tranzakciós log fájlba. Kevésbé használatos, így nem is ejtenék több szót róla.

Adatbázisok helyre állítása: Nagyon fontos, hogy amikor egy adatbázist helyre akarunk állítani, akkor először egy FULL backup-ot veszünk elő. Ha csak differential vagy transaction log bakcup van, akkor azzal nem megyünk semmire, nyugodtan ki lehet dobni! Ha megvan a FULL backup, akkor további backup típusokat (differential, transaction log) görgethetünk rá. Ezért mindig a FULL backup-hoz kell számolni a mentések törlését.

Backup startégia: Ha menteni akarunk, akkor erre feltétlen szükség lesz, és alapos tervezést igényel.  Mivel minden rendszer egyedi, ezért a mentésük is más-más stratégián alapul a legegyszerűbbtől a nagyon bonyolultig. A backup stratégia a háromféle mentési mód kombinálásával szokott létre jönni azzal kiegészítve, hogy a mentéseket elmozgatjuk/átmásoljuk egy távoli helyre, ami általában egy backup szerver (lehetnek egyéb lépések is, de ezekre most nem térek ki). Kisebb cégeknél, ahol ilyen szerverre nincs igény, pénz, stb. jó megoldás lehet, hogy a hálózaton egy másik gépre, vagy a local szerverbe egy Pendrive-t vagy külső vincsesztert bedugva átmozgatjuk a mentéseket. Nézzünk egy egyszerű stratégiát:
–    Mentjük az adatbázist (lehetőleg egy kevésbé terhelt időszakban fussanak a nagyobb mentések)
–    Elmozgatjuk / átmásoljuk a backup fájlokat egy távoli helyre. Ez lehet pull vagy push típusú. A push, amikor az SQL szerverről másoljuk át a távoli helyre a mentést, a pull-nál pont fordítva, a távoli helyre „húzzuk” át a mentéseket. Ezeket egy megosztáson keresztül tehetjük meg.
–    Töröljük az x időnél régebbi mentéseket (mindig a FULL backup-hoz számoljuk a mentések törlését).

Készítsünk 1-2 pull típusú példa stratégiát, hogy megértsük a mentéseket.

1.)    Egy SIMPLE recovery modell-ben lévő adatbázis mentése: Már bizonyára tudjuk, hogy itt csak FULL és differential mentéseket tudunk alkalmazni. Egy lehetséges terv:

a.) Mentés:
i.      FULL mentés minden vasárnap 23:00-kor.
ii.      Differential mentés hétfőtől szombatig 23:00-kor (mert ez kisebb szokott lenni, mint egy full mentés).

b.    Elmozgatás / átmásolás egy backup szerverre:
i.    Tételezzük fel, hogy a mentésünk általában  10 percet vesz igénybe. Akkor beállítok egy scriptet (cmd,vbs, powershell, stb), hogy a legutóbbi mentést minden nap 24:00-kor átmásolja egy másik adattárolóra. (Hagyok 1 órát, hogy biztosan lefusson a mentés).
c.    Töröljük az x időnél régebbi backup fájlokat
i.    Beállítok egy scriptet, ami törli az SQL Serverről az 1 napnál régebbi differential mentéseket
ii.    Egy másik scriptet, ami törli az 1 hétnél régebbi full mentéseket. Ugye itt azért 1 hét a limit, mert ha nincs full backup-om, akkor hiába van differential mentés, nem megyek vele semmire
iii.    Beállítok egy scriptet, ami az elmozgatott 1 hétnél régebbi backup-okat is törli az adattárolóról (full, differential)

2.)    Egy FULL recovery modell-ben lévő adatbázis mentése: Egy lehetséges terv:
a.    Mentés:
i.    FULL mentés minden vasárnap 23:00-kor
ii.    Differential mentés hétfőtől szombatig 23:00-kor
iii.    Tranzakciós log mentés 3 óránként, folyamatosan. Tételezzük fel, hogy 12:00-kor kezdjük az elsőt. (Tehát logmentés lesz 12:00, 15:00, 18:00, stb…)
b.    Elmozgatás / átmásolás egy backup szerverre:
i.    Tételezzük fel, hogy a mentésünk általában  10 percet vesz igénybe. Akkor beállítok egy scriptet (cmd,vbs, powershell, stb), hogy a legutóbbi mentést minden nap 24:00-kor átmásolja egy másik adattárolóra. (Hagyok 1 órát, hogy biztosan lefusson a mentés).
ii.    Beállítok egy másik mentést, ami a tranzakciós log mentéseket másolja minden 3. órában, minden nap 13:00-tól kezdve. (Ugye a log mentés is 3 óránként van, de az 12:00-kor kezdődik, így 1 órával később már biztosan másolhatom).
c.    Töröljük az x időnél régebbi backup fájlokat
i.    Beállítok egy scriptet, ami törli az SQL Serverről az 1 napnál régebbi differential mentéseket
ii.    Egy másik scriptet, ami törli az 1 hétnél régebbi full mentéseket. Ugye itt azért 1 hét a limit, mert ha nincs full backup-om, akkor hiába van differential mentés, nem megyek vele semmire
iii.    Egy harmadikat, ami törli az 1 napnál régebbi tranzakciós log mentéseket
iv.    Beállítok egy scriptet, ami az elmozgatott 1 hétnél régebbi backup-okat is törli az adattárolóról. (full, differential, transaction log)

A push típusú mentéseknél 1 fájlba lehet írni mindent (mentés, másolás, törlés). Ezt viszonylag egyszerűbb kivitelezni.

Buta demó

Tételezzük fel, hogy van egy alkalmazásunk, ami SQL Server Express-t használ. Az SQL szervíz egy olyan szerveren fut, ahol kevés hely áll rendelkezésünkre és megengedhető, hogy simple recovery módban fusson az adatbázis.
Készítünk egy adatbázist, amit menteni fogunk:

USE [master]
GO
IF db_id('backup_jatek') is not null
BEGIN
    ALTER DATABASE backup_jatek SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE backup_jatek
END
GO

CREATE DATABASE [backup_jatek]
GO

CREATE TABLE tbl1 (id int identity(1,1) primary key, szam int not null)
GO

ALTER DATABASE [backup_jatek] SET RECOVERY SIMPLE
GO

ALTER DATABASE [backup_jatek] MODIFY FILE ( NAME = N'backup_jatek', SIZE = 5120KB , FILEGROWTH = 3072KB )
GO
ALTER DATABASE [backup_jatek] MODIFY FILE ( NAME = N'backup_jatek_log', MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
GO

USE [backup_jatek]
GO

-- Adatok létrehozása
INSERT INTO tbl1 (szam) values (1), (2), (3)

Készítünk egy sql scriptet, ezt beletesszük egy sql file-ba (demo_backup.sql), ami készít egy full adatbázis mentést:

BACKUP DATABASE [backup_jatek] TO DISK = N'E:\MSSQL\2012\Backup\backup_jatek.bak' WITH NOFORMAT,
NOINIT, NAME = N'backup_jatek-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Készítünk egy batch file-t (demo_backup.bat) ami meghívja a mentő scriptünket (demo_backup.sql) és logolja az eredményt. A script tetszés szerint tovább fejleszthető, hogy törölje az előző napi mentést (mi most egyszerűen csak felülírjuk), másolja át a meglévő mentéseket egy távoli helyre, illetve x időnél régebbi fájlokat töröljön onnan. Amit kiemelnék, hogy windows autentikációval megyünk be az SQL szerverbe, így nem kell text-ben tárolni felhasználó nevet és jelszavat sem!

@echo off
cls
if exist sql_backup_log.txt del sql_backup_log.txt
@echo on
mkdir backup
sqlcmd -S .\SQLEXPRESS -E -I -i fájlútvonala\demo_backup.sql >> sql_backup_log.txt 2>&1

A demo_backup.bat fájlt elmentjük ütemezett feladatként és futtatjuk.

Csináljunk differential mentést.

Töltünk még adatot az adatbázisba.


USE [backup_jatek]
GO
SET NOCOUNT ON;
GO

-- Adatok töltése
INSERT INTO tbl1 (szam) values (4), (5), (6)
GO

Készítünk egy sql scriptet, ezt beletesszük egy sql file-ba (demo_backup_diff.sql), ami készít egy differential adatbázis mentést:


BACKUP DATABASE [backup_jatek] TO DISK = N'E:\MSSQL\2012\Backup\backup_jatek_diff.bak' WITH  DIFFERENTIAL ,
NOFORMAT, INIT,  NAME = N'backup_jatek-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Készítünk egy batch file-t (demo_backup_diff.bat) ami meghívja a mentő scriptünket (demo_backup_diff.sql) és logolja az eredményt.

@echo off
cls
if exist sql_backup_diff_log.txt del sql_backup_diff_log.txt
@echo on
mkdir backup
sqlcmd -S .\SQLEXPRESS -E -I -i fájlútvonala\demo_backup_diff.sql >> sql_backup_diff_log.txt 2>&1

Végezetül nézzünk meg néhány adatot a backupjainkról:


SELECT mdb.database_name, mdb.recovery_model,
CASE mdb.[type] WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' ELSE 'Else' END as [backup type],
backup_start_date, backup_finish_date, CAST(round(backup_size / 1048576.0,2,1) as decimal(9,2)) backup_size_MB

FROM msdb.dbo.backupset mdb
INNER JOIN (
SELECT database_name, [type], MAX(backup_set_id) backup_set_id
FROM msdb.dbo.backupset
GROUP BY database_name, [type]
) mdb_tmp
ON mdb.backup_set_id = mdb_tmp.backup_set_id
WHERE MDB.database_name = 'backup_jatek'

Ha összehasonlítjuk a backupjaink méretét, akkor láthatjuk, hogy a differential nagyságrendekkel kisebb lett. Nálam a full 2,6 MB, a differential 432 KB.