június 2013 havi bejegyzések

Az MSSQL HA-ról és failoverről kicsit másképpen

Régóta tervezem, hogy írok az SQL failover lehetőségekről és most eléggé témába is vág. Sokan ismeritek ezeket. A Microsoft a következő failover lehetőségeket biztosítja számunkra:

Failover jellemző Failover Cluster Log shipping Mirroring AlwaysOn Replikáció + NLB
Adatvesztés elleni
védelem
Van Nincs Beállítás függő Beállítás függő Nincs
Tartalék szerver írható Nem Nem Nem Nem Igen
Tartalék szerver
olvasható
Nem Többnyire Pillanatfelvétellel Beállítás függő Igen
Failover idő (mp) 30-60 Van, bizonytalan 2+ 2+ 2+
Failover alatt az
adatbázis elérhető
Nem Nem Nem Nem Nem
Átállás módja
(kézi / automatikus)
Beállítás függő Kézi Beállítás függő Beállítás függő Beállítás függő

Ha megvizsgáljuk a táblázatot látunk néhány kritikus pontot, ami nem vagy csak részben támogatott:

  1. Tartalék szerver írható
  2. Failover idő (mp)
  3. A failover alatt az adatbázis elérhetetlensége (egyszerűen elvágja a TCP kapcsolatokat).

Vajon miért nem támogatott? Mert mind az adatbázis konzisztencia rovására mehet.
Itt megjegyezném, hogy 2013-at írunk és egyre erősebb az úgynevezett üzleti igény. Nézzük meg ebből a nézőpontból is a fenti pontokat.

Üzleti igény:

  1. Adatvesztés elleni védelem
  2. Tartalék szerver írható
  3. Tartalék szerver olvasható
  4. Failover idő (mp)
  5. Failover alatt az adatbázis elérhető
  6. Átállás módja (kézi / automatikus)
  7. Szeretném magam (mint cég) felállítani a prioritást a pontok között!

Ugye mennyivel komolyabban hangzik?
Van rá igény, hogy magam (mint cég) döntsem el, hogy a konzisztencia vagy a folyamatos kiszolgálás a fontosabb számomra. Most biztos többen rázzátok a fejetek, de vannak olyan esetek, amikor ez teljesen jogos igény. Mondok is egyet.

Tételezzük fel, hogy van 1 rendszerünk, ami MSSQL-t használ és szükséges egy karbantartást beiktatni (pl: Service Pack-ot feltenni). Az MS-nél ez gyakran az SQL Server elérhetetlenségével és restart-al is jár. A megoldás a failover. Tájékoztatjuk a vezetőt, hogy mit a szeretnénk, és az alábbi lehetőségek vannak:

  1. Csinálunk egy SQL failovert, ami X időre elérhetetlenné teszi az SQL szervert, ami érzékenyen érinti a rendszerünket (egyéb szerverek) és megáll, majd csak Y idő múlva lesz ismét kiszolgálásra alkalmas (Tételezzük fel, hogy Y idő = jelentős bevételkiesés).
  2. Csinálunk egy SQL failovert, de 0,1% eséllyel előfordulhat, hogy max. 5 felhasználónál a sokmillióból kialakulhat adatvesztés (pl: megváltozott a foglalkozása, de ő beleesett a 0,1%-ba), viszont folyamatos marad a kiszolgálás.

Nyilván a második mellett fog dönteni a vezetőség.

(Témába pedig azért vág, mert mi megvalósítottuk a 2. pontot. Sőt, többször alkalmaztuk sikeresen 🙂 )

Reklámok

Fontosabb adatbázistulajdonságok

Időnként meg szoktam nézni az adatbázisok alapvető tulajdonságait, még akkor is, ha nem érkezik semmilyen riasztás. Amire ilyenkor szükségem van:

  1. Adatbázis neve: Ehhez nincs mit megjegyezni
  2. Státusza: Látszik, hogy elérhető e az adatbázis
  3. Recovery modell: Innen látni, hogy milyen mentési módot kell rá alkalmazni
  4. Log reuse wait: Ez mutatja mire vár a log file. Akkor fontos, amikor a log fájlunk vég nélkül növekszik
  5. Adatfájl mérete: Ehhez nincs mit megjegyezni
  6. Log fájl mérete: Ha túl nagy és a mentések hatására is tovább növekszik, akkor a log resource wait mondja meg, hogy mi a gond
  7. Last full backup: Ekkor készült utoljára full backup az adatbázisban.
  8. Last diff backup: Ekkor készült utoljára különbözeti mentés az adatbázisban.
  9. Last log backup: Ekkor készült utoljára log backup az adatbázisban.

Ezekből egy egész jó képet kapok, hogy az adatbázisok és a mentések rendben vannak e. Erre ezt a lekérdezést használom:

USE [master]
GO

WITH cte_last_backups AS (
	SELECT
		database_name = b2.database_name COLLATE SQL_Latin1_General_CP1_CI_AI,
		b2.backup_set_id,
		backup_type = b2.[type] COLLATE SQL_Latin1_General_CP1_CI_AI,
		b1.backup_start_date
	FROM msdb.dbo.backupset b1
	INNER JOIN (
		SELECT
			database_name,
			MAX(backup_set_id) backup_set_id,
			[type]
		FROM msdb.dbo.backupset
		GROUP BY database_name, [type]
	) b2
		ON b1.backup_set_id = b2.backup_set_id
)

SELECT
	sd.name,
	sd.state_desc,
	sd.recovery_model_desc,
	sd.log_reuse_wait_desc,
	files_size.db_size_mb,
	files_size.log_size_mb,
	tmp1.backup_start_date as last_full_backup,
	tmp2.backup_start_date as last_diff_backup,
	tmp3.backup_start_date as last_log_backup
FROM master.sys.databases sd
LEFT JOIN
	(SELECT
		database_id,
		CAST(SUM(CASE [type] WHEN 0 THEN size / 128.0 ELSE 0 END) AS DECIMAL(18,2)) AS [db_size_mb],
		CAST(SUM(CASE [type] WHEN 1 THEN size / 128.0 ELSE 0 END) AS DECIMAL(18,2)) AS [log_size_mb]
	FROM master.sys.master_files
	GROUP BY database_id
	) files_size
ON sd.database_id = files_size.database_id
LEFT JOIN cte_last_backups tmp1
	ON tmp1.database_name COLLATE SQL_Latin1_General_CP1_CI_AI = sd.name
		AND tmp1.backup_type = N'D'
LEFT JOIN cte_last_backups tmp2
	ON tmp2.database_name COLLATE SQL_Latin1_General_CP1_CI_AI = sd.name
		AND tmp2.backup_type = N'I'
LEFT JOIN cte_last_backups tmp3
	ON tmp3.database_name COLLATE SQL_Latin1_General_CP1_CI_AI = sd.name
		AND tmp3.backup_type = N'L'

Akkor is hasznos ez a lekérdezés, amikor egy backupot kell egy másik szerveren helyreállítani és a log fájl shrink-elése makacskodik.
És akkor egy kimenet nálam:
result_033_a
Remélem nektek is olyan hasznos lesz, mint nekem szokott.

Frissítsünk oszlopstatisztikát

Bizonyára sokan tudjátok, hogy a statisztikák jelentős szerepet játszanak az adatbázisokban, ugyanis erősen támaszkodik rá az optimalizáló. Ha rosszak a statisztikák az optimalizáló rossz lekérdezési tervet fog készíteni, a rossz lekérdezési terv pedig teljesítményromláshoz fog vezetni.

Pl: a statisztikák alapján dönthet úgy az optimalizáló, hogy nem használ egy indexet vagy éppen úgy, hogy használ, de azzal sokkal rosszabb lesz a válaszidő. A friss statisztikák segítenek mindezt megelőzni, a kulcsszó pedig az UPDATE STATSTICS. A pontos használatát leírja a books online, erre most nem is térnék ki.

Arra viszont igen, hogy egy ilyen parancs UPDATE STATISTICS tábla WITH FULLSCAN fel fogja olvasni a diszkről az egész táblát (érdemes nézni a performance countereket ilyenkor) és berántja a memóriába. Gondoljunk bele, hogy van pl.: egy szerverünk 10 GB memóriával és egy táblánk, ami 20 GB. Futtatjuk rajta pl.: az ütemezett karbantartást, ami simán tartalmazhatja az UPDATE STATISTICS tábla WITH FULLSCAN parancsot, és szépen kisöpri a memóriát. Ráadásul ennyi adatot felolvasni időbe telik. Ez egy 7×24-es rendszeren nagy terhelésnél szolgáltatáskiesést is okozhat, mert a kiszolgáláshoz szükséges adatokat ismét a vinyóról kell felolvasni, az viszont idő. Én arra a következtetésre jutottam, hogy ilyen helyen ez a parancs nem futhat az éles szerveren.

Jöhet a kérdés, hogy hogyan tovább? A statisztika kellene, viszont mégsem futtatható, mert túlságosan is kockázatos. Itt kezdjük el az SQL szerver határait feszegetni.

Mielőtt a megoldanánk a dolgot, térjünk még ki a statisztikák frissítési rendszerességére. Ki mennyi időnként szokta frissíteni a statisztikákat? Mivel idő és performancia igényes a feladat, ezért a naponta egy bőven elég szokott lenni egy átlagos rendszeren.

Ha ebből indulunk ki, akkor egy 2 órával korábbi backupon futtatott statisztika frissítés már jó lenne az éles környezetben is. A megoldás is ez. Egy backupon kell megcsinálni a statisztikák frissítését, majd azt binárisan “bemonyni” az éles szerverre. Figyelem, az index statisztikákkal ilyet soha ne csináljunk! Az index statisztikák frissítése nagyon gyorsan megvan az UPDATE STATISTICS tábla (index) formával. A módszert csak az oszlop statisztikák frissítésére ajánlom.

Nézzük a demót:

1.) Az alábbi scripttel készítünk egy adatbázist majd egy táblát, amibe teszünk 700 sort. Tételezzük fel, hogy ez az adatbázisunk a backup, amin a statisztika frissítéseket készítjük el.

USE [master]
GO

IF DB_ID('stat1') IS NOT NULL
BEGIN
    ALTER DATABASE stat1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE stat1
END
GO

CREATE DATABASE stat1
GO

USE stat1
GO

CREATE TABLE dbo.t1 (
    id int identity(1,1) primary key,
    nap nvarchar(255)
)
GO

INSERT INTO dbo.t1 (nap)
SELECT TOP 700
    datename(dw,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))
FROM sys.columns c1
CROSS JOIN sys.columns c2

GO

2.) Készítünk statisztikát a nap oszlopra

create statistics nap on dbo.t1 (nap)

3.) Mentsük ki a statisztikát bináris formában, ehhez most a management studiót használjuk.
Ha management studioban megkeresük a táblát és a hozzá tartozó statisztikát, akkor látni láthatjuk, hogy ott van:

10_db_fa

Mentsük ki a statisztikát néhány egyszerű lépésben:

11_generate_script_01

11_generate_script_02

11_generate_script_03

11_generate_script_04

11_generate_script_05

11_generate_script_06

11_generate_script_07

11_generate_script_08

Végezetük kaptunk egy “New Query” window-ban egy kódot, ami tartalmaz egy ilyen részt:

CREATE STATISTICS [nap] ON [dbo].[t1]([nap]) WITH STATS_STREAM = 0x010000000100000000000000000000001B09C3AB00000000C9030000000000008903000000000000E7020FDDE7000000FE0100000000000008D000000000000007000000CA1E5501D8A1000070110100000000007011010000000000000000002549123E0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000070000000700000001000000100000004992644100B8884700000000499264410000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000130000000000000000000000000000003D0100000000000015020000000000001D0200000000000038000000000000005B000000000000007E00000000000000A500000000000000C800000000000000EF0000000000000014010000000000003000100000401C46000000000000803F040000010023004600720069006400610079003000100000401C46000000000000803F040000010023004D006F006E006400610079003000100000401C46000000000000803F04000001002700530061007400750072006400610079003000100000401C46000000000000803F04000001002300530075006E006400610079003000100000401C46000000000000803F04000001002700540068007500720073006400610079003000100000401C46000000000000803F0400000100250054007500650073006400610079003000100000401C46000000000000803F040000010029005700650064006E0065007300640061007900FF0100000000000000B60D00000700000028000000280000000000000000000000300000004600720069006400610079004D006F006E006400610079005300610074007500720064006100790075006E00640061007900540068007500720073006400610079007500650073006400610079005700650064006E00650073006400610079000A0000004000000000A006000000F6010000A006060000F5010000C0010C0000A0070D0000F60100002005140000F5010000C001190000A0071A0000F60100002006210000F50100002009270000F5010000007011010000000000
GO

Annyi dolgunk van, hogy az éles rendszeren lecserélni a statisztikát:

DROP STATISTICS [dbo].[t1].NAP
GO

CREATE STATISTICS [nap] ON [dbo].[t1]([nap]) WITH STATS_STREAM = 0x01000000010000000000000000000000D7C3D6C4000000002104000000000000E103000000000000E702FFFFE7000000FE0100000000000008D000000000000007000000EF3C4301D8A1000070110100000000007011010000000000000000002549123E0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000070000000700000001000000100000004992644100B88847000000004992644100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B0000000000000000000000000000003D01000000000000150200000000000065020000000000006D0200000000000038000000000000005B000000000000007E00000000000000A500000000000000C800000000000000EF0000000000000014010000000000003000100000401C46000000000000803F040000010023004600720069006400610079003000100000401C46000000000000803F040000010023004D006F006E006400610079003000100000401C46000000000000803F04000001002700530061007400750072006400610079003000100000401C46000000000000803F04000001002300530075006E006400610079003000100000401C46000000000000803F04000001002700540068007500720073006400610079003000100000401C46000000000000803F0400000100250054007500650073006400610079003000100000401C46000000000000803F040000010029005700650064006E0065007300640061007900FF0100000000000000B60D00000700000028000000280000000000000000000000300000004600720069006400610079004D006F006E006400610079005300610074007500720064006100790075006E00640061007900540068007500720073006400610079007500650073006400610079005700650064006E00650073006400610079000A0000004000000000A006000000F6010000A006060000F5010000C0010C0000A0070D0000F60100002005140000F5010000C001190000A0071A0000F60100002006210000F50100002009270000F50100000001000000F6DB4201D8A10000000000000017F140701101000000000007000000000000A02449C23F000000000000000000000000000000000000000000000000000000000000000000000000000000007011010000000000
GO