január 2013 havi bejegyzések

Adatfájlok költöztetése minimális offline idővel

DBA-ként előfordulhat, hogy nagy adatbázisok fájljait kell átköltöztetni egy másik meghajtóra, természetesen mindezt úgy, hogy a felhasználók közben tudjanak dolgozni vagy minimális legyen állásidő (max 1-2 perc). Ezt többféleképpen is megoldható, egy egyszerűbbet mutatok itt most be.

Első nekifutásra ilyesmi tervet lehet elképzelni:

  • Adatbázis detach
  • Adatfájlok átmozgatása az új meghajtóra
  • Adatbázis attach

Ezzel az a probléma, hogy ha az adatbázis fájljai (.mdf, .ldf) nagyok, akkor a másolási idő több tíz perc vagy több óra is lehet.

Tuningoljuk fel a tervet.

  • Egy másodlagos adatfájl (.ndf) létrehozása az új meghatón
  • Az elsődleges adatfájlból (.mdf) az adatok átpumpálása a a másodlagos adatfájlba
  • Shrink az elsődleges (.mdf) és a log (.ldf) fájlokon
  • Adatbázis detach
  • Fájlok (.mdf, .ldf) átmozgatása az új meghajtóra
  • Adatbázis attach

A fenti módszer már alkalmas arra, hogy minimális offline idővel átmozgassuk az adatbázis fájljait az egyik meghajtóról egy másikra. A tervet lehet tökéletesíteni, a célom, hogy a lényeget mutassam be, ami scriptben így néz ki:

USE [master]
GO

SET NOCOUNT ON;
GO

-- Létrehoznk teszt adatbázist
IF DB_ID('fast_transfer') IS NOT NULL BEGIN
	ALTER DATABASE fast_transfer SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE fast_transfer;
END
GO

CREATE DATABASE fast_transfer
GO

USE [fast_transfer]
GO
-- Teszünk bele egy táblát
CREATE TABLE tbl1 (
	id INT NOT NULL IDENTITY(1,1),
	szam INT NOT NULL
	CONSTRAINT PK_tbl1 PRIMARY KEY CLUSTERED
)
GO

-- Feltöltjük adatokkal
DECLARE
	 @i INT = 0
	,@add_count INT = 1

WHILE (@i < 100) BEGIN
	SELECT @add_count = COUNT(*) FROM tbl1

	BEGIN TRAN
		INSERT INTO tbl1 (szam)
			SELECT TOP 1000
				ROW_NUMBER() OVER (ORDER BY name) + @add_count rwn
			FROM master.dbo.spt_values (NOLOCK)
	COMMIT

	SET @i += 1
END
GO

-- Megnézzük, hogy mennyi adatunk van benne
SELECT
	COUNT(*) db,
	MIN(SZAM) min_szam,
	MAX(SZAM) max_szam
FROM tbl1

USE [master]
GO

-- Létehozunk egy másodlagos adatfájlt a másik meghatjón,
-- ahova át fogjuk pumpálni az adatokat.
-- Az adatbázis fájl filename nálam most ez, nálatok lehet eltérés..
ALTER DATABASE [fast_transfer] ADD FILE (
	NAME = N'fast_transfer2',
	FILENAME = N'D:\MSSQL_2008\Database\Data\fast_transfer2.ndf' ,
	SIZE = 3072KB ,
	FILEGROWTH = 10240KB
) TO FILEGROUP [PRIMARY]
GO

USE [fast_transfer]
GO

-- Megnézzük, hogy a fájljaink mennyi helyet foglalnak,
-- és abból mennyi ami ténylegesen ki van töltve.
SELECT
	database_id = DB_ID(),
	a.FILEID,
	DatabaseName = DB_NAME() ,
	a.NAME,
	[FILE_SIZE_MB] =
	convert(decimal(12,2),round(a.size/128.000,2)),
	[SPACE_USED_MB] =
	convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
	[FREE_SPACE_MB] =
	convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
	a.FILENAME
FROM dbo.sysfiles a (NOLOCK)
GO

-- Átpumpáljuk az adatokat.
-- A sctipt ezen része hibát fog dobni, hogy nem tudott minden adatot átvinni,
-- de ez nem gond, mert a .mdf file tartalmaz olyan adatokat is,
-- amik nem mozgathatóak át másoldagos fájlokba.
DBCC SHRINKFILE (N'fast_transfer' , EMPTYFILE)
GO

-- Shrinkeljük az eredei fájlokat olyan kicsire, amilyenre csak tudjuk.
-- Így gyorsabb lesz az áthelyezés.
DBCC SHRINKFILE (N'fast_transfer' , 1)
GO

-- Átállunk SIMPLE recovery modellbe, hogy tudjuk shrinkelni a logfájlt is.áthelyezve az új meghatjóra.
ALTER DATABASE [fast_transfer] SET RECOVERY SIMPLE WITH NO_WAIT
GO
-- Shrinkeljük a log fájlt.
DBCC SHRINKFILE (N'fast_transfer_log' , 1)
GO

-- Ellenőrzések
SELECT COUNT(*) AS db FROM tbl1 (NOLOCK)
GO

-- Megnézzük, hogy mekkora adatmennyiség található a költöztetendő fájlokban.
SELECT
	database_id = DB_ID(),
	a.FILEID,
	DatabaseName = DB_NAME() ,
	a.NAME,
	[FILE_SIZE_MB] =
	convert(decimal(12,2),round(a.size/128.000,2)),
	[SPACE_USED_MB] =
	convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
	[FREE_SPACE_MB] =
	convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
	a.FILENAME
FROM dbo.sysfiles a (NOLOCK)
GO

/********************************************
    Ha elég kicsik lettek a fájlok,
    akkor lehet detach-olni az adatbázist,
	a fájlokat átmozgatni az új meghajtóra,
	majd attacholni.
	Ezt én most kihagyom.
*********************************************/

Rendszer nézetek vs. rendszer függvények

A programozásban az a szép, hogy 1-1 feladatot többféleképpen is meg lehet oldani. Többször belefutottam már, hogy a megoldandó feladat függvényében egyik lehetőség mégis sokkal jobb, mint a másik, pedig teljesen egyformának tűnik, tanultuk. Ez sokszor csak menet közben derül ki. Egy példa, hogy világosabb legyen. Keressük meg a master adatbázisban lévő spt_values object azonosítóját.

use [master]
go

select object_id from sys.tables (NOLOCK) where name = 'spt_values'
select object_id('spt_values')

Mindkét lekérdezés ugyanazt az eredményt adja vissza, tehát több megoldásunk is van ugyanarra a problémára. Az internetet túrva nagyon sok DBA script-et láttam, hol egyik, hol másik megoldást látva. Én mégis jobb szeretem az adatokat nézetekből lekérdezni (jelen esetben a sys.tables), mint a függvényeket használni (object_id()), mégpedig azért, mert a függvények érzékenyek a lock-okra, míg a nézetek mögé bátran odakarcintom a NOLOCK hintet.

Nézzünk egy rövid demót:

Nyissuk egy Management Studio-t, majd abban két Query ablakot (SESSION-t)

-- SESSION 1: futtassuk előbb ezt
use tempdb
go

if exists (select 1 from sys.tables (nolock) where name = 'tbl1')
drop table tbl1
go

create table tbl1 (id int not null primary key identity, szam int)
go

begin tran
alter table tbl1 add szoveg varchar(20)
-- rollback

-- SESSION 2: majd ez
use tempdb
go

-- Ez eredményt ad
select object_id from sys.tables (nolock) where name='tbl1'

-- Ez pedig csak "homokórázni" fog
select object_id('tbl1') as object_id

Ha most visszaváltunk a SESSION 1 ablakunkba, majd kiadjuk a ROLLBACK utasítást, akkor máris kapunk vissza eredményt a SESSION 2-ben az object_id függvénytől is.

Amikor kizártuk magunkat az adatbázisból

DBA-ként előfordul, hogy egyedül kell tevékenykedni egy adatbázisban vagy csak le kell csatolni, hogy máshova felcsatolhassuk, de még számos oka lehet, ami miatt ki kell rúgni mindenkit az adatbázisból. Attól függően, hogy milyen célunk van, több eszköz is rendelkezésünkre áll. A leguniverzálisabb eszköz, ha SINLE_USER módba tesszük az adatbázist. Ezt megtehetjük többféleképpen is.

1.) Kapcsoló nélkül, így a szerver a bent lévő user-eket nem rúgja ki, viszont új session-öket már nem enged csatlakozni az adatbázishoz. A parancs pedig akkor hajtódik végre, amikor már senki sincs az adatbázisban, tehát mindenki kilépett. Ez adatbázistól függően változó ideig is eltarthat (órák, napok, hónapok, évek), amiből látható, hogy ez nem minden esetben megengedhető. Ilyenkor jön jól a második lehetőség.
2.) WITH ROLLBACK IMMEDIATE kapcsolóval, ami azonnali hatállyal mindenkit kirúg az adatbázisból, a félbe maradt tranzakciókat pedig ROLLBACK-eli. Brutális módszer, de hatékony 🙂

Érdemes tudni, hogy SINGLE_USER előnye egyben a hátránya is lehet, ugyanis előfordulhat, hogy pont mi rekedünk kinn az adatbázisból (Sok usernél erre jó esély van). Ilyenkor lehet kicsit töprengeni, hogy mit is tegyünk. Ha végig gondoljuk, hogy mi célt szolgál a SINGLE_USER, akkor gyorsan rájövünk a megoldásra is. Viccesen így lehetne megfogalmazni: „Csak egy maradhat.” Tehát egyszerre csak 1 session-t enged be az adatbázisba az adatbázisszerver. A megoldás, hogy ki kell KILL-elni a konkurens session_id-t és nekünk bejutni.

Lássunk egy demót is:
Nyissunk egy Management Studio-t, abban két Query ablakot (SESSION-t). Egyik Query ablak leszünk mi, mint DBA a másik pedig egy user, aki kizár minket az adatbázisból.

<strong>Első Query ablakba másoljuk az user demó kódját:</strong>
-- SESSION USER
USE egyedul_a_db_ben
GO

SELECT TOP 10 * FROM t1

A második Query ablakba másoljuk a DBA demó kódját:

-- SESSION DBA: 1.) Elkészítjük az adatbázist
USE [master]
GO
IF EXISTS (SELECT * FROM sys.databases (NOLOCK) WHERE name = 'egyedul_a_db_ben')
    DROP DATABASE egyedul_a_db_ben
GO
CREATE DATABASE egyedul_a_db_ben
GO
USE egyedul_a_db_ben
GO
CREATE TABLE t1 (id INT PRIMARY KEY, szam INT)
GO
INSERT INTO t1 VALUES (1, 10)
GO
SELECT * FROM t1

-- SESSION DBA:  2.) Kirúgjuk az embereket
USE [egyedul_a_db_ben]
GO
CHECKPOINT -- Jó az, ha minden ki van írva az adatfájlba is
GO
USE [master]
GO
ALTER DATABASE egyedul_a_db_ben SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- SESSION DBA: 3.) Most becsukjuk a session-t, de bármi más történhetne, ami miatt kirekedünk az adatbázisból

-- SESSION DBA: 4.) Dolgozni próbálunk, de nem fog menni (jobb, egyszerű példa nem jut eszembe)
USE [egyedul_a_db_ben]
GO
INSERT INTO [egyedul_a_db_ben].dbo.t1 VALUES (2, 20)

-- SESSION DBA: 5.) Bekeményítünk és kirúgjuk aki helyettünk került az adatbázisba
DECLARE @kill VARCHAR(1000) = N''
SELECT @kill += 'KILL ' + CAST(spid AS VARCHAR(5)) + ';' FROM sys.sysprocesses (NOLOCK) WHERE dbid = DB_ID('egyedul_a_db_ben')
PRINT @kill
EXECUTE (@kill)

-- SESSION DBA: 6.) Most ténylegesen dolgozni fogunk
USE [egyedul_a_db_ben]
GO
INSERT INTO [egyedul_a_db_ben].dbo.t1 VALUES (2, 20)

-- SESSION DBA: 7.) Visszaengedünk mindenkit az adatbázisba
USE [master]
GO
ALTER DATABASE egyedul_a_db_ben SET MULTI_USER WITH ROLLBACK IMMEDIATE

-- SESSION DBA 8.) Ellenőrzés
USE egyedul_a_db_ben
GO

SELECT TOP 10 * FROM t1

 A lépések:
1.) Futtassuk a DBA demó 1-es ponját, majd az user demó-t.
2.) Futtassuk a DBA demó 2-es pontját
3.) Futtassuk, a DBA demó 3-as pontját, azaz csukjuk be a DBA demó session-t. (Nem tudom ki hogy van vele, de nálam heti egyszer garantáltan kifagy a Management Studio és csak bezárni majd újra megnyitni lehet, tehát még életszerű is lehet a példa).
4.) Nyissuk új Query abalakot, másoljuk bele ismét a DBA demó kódját és próbájuk a 4-es pontot futtatni. Hibaüzenetet kell kapnunk, hogy az adatbázisban már vannak és új session nem csatlakozhat.
5.) Futtassuk a DBA demó 5-ös és 6-os pontjait. Ha mindent jól csináltunk, akkor probléma nélkül lefutnak és bejutunk az adatbázisba.
6.) Váltsunk az user session-ra és próbáljuk futtatni a lekérdezését. (Nem fog menni, mert már a DBA van az adatbázisban).
7.) Futtassuk a DBA demó 7-es és 8-as pontját, majd az user demó kódját. Most már mindkét session-nak kell tudni használni az adatbázist.

A valóságban ennél sokkal összetettebb feladatokkal lehet találkozni, de a lényege ez lesz.

Érdekesség:
Sok user-nél és nagy terhelésnél nem mindegy, hogy bekerül e egy felesleges go utasítás vagy sem.

USE [master]
GO
ALTER DATABASE egyedul_a_db_ben SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO -- <em>Láttam már olyat, hogy ez a GO parancs elég volt arra, hogy becsússzon egy user az adatbázisba és már nem lehetett detach-olni a db-t, mivel valaki használta.</em>
EXEC master.dbo.sp_detach_db @dbname = N'egyedul_a_db_ben'