Tárolt eljárások definíciójában keresés

Időről időre visszatérő probléma: melyik tárolt eljárást érinti egy adott tábla módosítása, melyik SP-ben van deprecated (elavult) (NOLOCK) hint, effélék – szóval keresni kell a definíciókban. Ezt sokféleképpen megtehetjük, a legkényelmesebb a definíciók felolvasása. Régebben az INFORMATION_SCHEMA.ROUTINES ANSI standard metatáblát használtam erre, most már váltottam a meglehetősen nem intuitív nevű sys.sql_modules rendszernézetre. A különbség egyébként nem nagy – a standardban csak az első 4000 karakter látszik, a MSSQL-specifikus pedig nvarchar(max), tehát hajszálnyit több fér bele.

Ebből született a tárolt eljárásban stringet kereső tárolt eljárás. Két paramétert vár: a stringet, amire keresni akarunk és az adatbázis nevét, amiben keresni akarunk. Ha az egész szerveren szeretnénk keresni, akkor használhatjuk az undocumented sp_MSforeachdb tárolt eljárást.

CREATE PROCEDURE dbo.spFindTextInSP
@string nvarchar(1000),
@dbname sysname = ''
AS
if (isnull(@dbname,'') = '')
        set @dbname = db_name()
declare @q nvarchar(2000)
set @q = 'select quotename(object_schema_name(object_id,db_id('''+ @dbname + '''))) + ''.'' + quotename(object_name(object_id,db_id(''' + @dbname +  '''))) AS matching_objects from ' + @dbname + '.sys.sql_modules
where definition like ''%' + @string + '%'''
exec(@q)

GO

-- és két minta felhasználás
exec dbo.spFindTextInSp 'alert'
exec dbo.spFindTextInSp 'alert', 'Customer_archiv'

Log shipping PowerShell scripttel

Egy hónapja ígértem meg Tibornak azt a PowerShelles log shipping megoldást, amit használtunk SQL 2000-2005 migrációra. Az alapgondolat a következő: a 2000-es szerveren csinálunk egy log backup jobot, ami már az új szerverre, UNC-n keresztül teszi le a backup fájlokat. Ez egy sima T-SQL job. A 2005-ös szerveren pedig egy Operating System jobstep lakik, ami egy powershell scriptet futtat. A jobstep maga kb. ennyi: powershell.exe D:\mssql\MigrateMyDB.ps1. Ez most egy bedrótozott verzió, de elég egyszerűen átalakítható paraméterezhetővé, és akkor egy scripttel lehet több adatbázist is tutujgatni.
Continue reading ‘Log shipping PowerShell scripttel’ »

Rendszeradatbázisok mozgatása SQL 2008-ban

Az új verzió mindig jobb, szebb, gyorsabb, kényelmesebb, stb. És tényleg: most először kellett elmozgatnom SQL 2008 szerver rendszeradatbázisait, és király volt. Aki csinált ilyet 2005 vagy 2000 alatt, az tudja, hogy milyen szívás mindenféle hülye trace flaggel indítgatni a SQL-t, aztán rossz sorrendben attacsolni a modelt meg az msdb-t. Ehhez képest az SQL 2008-ban négyféle adatbázis van:

  • Resource: Ő már nem is az adatfájloknál, hanem a programbinárisoknál lakik, hozzá se lehet nyúlni (és nem is kell).
  • master: Változatlanul, a service startup paraméterét kell átírni.
  • tempdb: Változatlanul alter database és nem kell fájlt másolni.
  • a többi: (igen, model, msdb is többi) Alter database, szerver leáll, fájlmásolás, szerver start.

Annyira egyszerű volt, hogy lescripteltem az egészet, egy SQL, egy Powershell:

ALTER DATABASE model MODIFY FILE (NAME = modeldev, FILENAME = 'D:\mssql10\db_data\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = 'E:\mssql10\db_logs\modellog.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\mssql10\db_data\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\mssql10\db_logs\templog.ldf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData, FILENAME = 'D:\mssql10\db_data\MSDBData.mdf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog, FILENAME = 'E:\mssql10\db_logs\MSDBLog.ldf')
# atirjuk a registryben a startup parametereket
Set-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCENAME\MSSQLServer\Parameters' -name "SQLArg0" -value "-dD:\mssql10\db_data\master.mdf"
Set-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCENAME\MSSQLServer\Parameters' -name "SQLArg2" -value "-eD:\mssql10\SrvLogs\ERRORLOG"
Set-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCENAME\MSSQLServer\Parameters' -name "SQLArg1" -value "-lE:\mssql10\db_logs\mastlog.ldf"
# SQL stop - a force azert kell, h a dependens service-ek is megalljanak
Stop-Service 'MSSQL$INSTANCENAME' -Force
Start-Sleep 10
# kopi adatbazis fajlok
Move-Item 'D:\mssql10\db_data\MSSQL10.INSTANCENAME\MSSQL\DATA\*.mdf' 'D:\mssql10\db_data'
Move-Item 'D:\mssql10\db_data\MSSQL10.INSTANCENAME\MSSQL\DATA\*.ldf' 'E:\mssql10\db_logs'
#Start-Service - az agent elinditja a SQL-t magat is
Start-Service 'SQLAgent$INSTANCENAME'

És kész is vagyunk… Scriptelni jó.

Folyamatos sorszámosztás, avagy szekvencia MSSQL módra

Itzik Ben-Gan egyike a nagy SQL koponyáknak napjainkban. Sajnos blogot nem ír, szerencsére könyvet igen. Alapvetően T-SQL programozással foglalkozna, de a jó T-SQL ugye hatékony is, tehát jön a Query Optimizer komponense az SQL Servernek, aztán a Query Processor, meg persze egy kicsit a tárolást sem árt ismerni… szóval ezermester. Szereti a fejtörőket, és szerintem ezzel összefüggésben áll az, hogy zseniális scripteket ír.

Van egy elég általános probléma alkalmazásfejlesztésben: folyamatos sorszámozás az adatbázisban – a klasszikus példa a számlaszám, amiben nem lehetnek lyukak, mert attól a könyvelő is meg az APEH is ideges. Erre van többféle megoldás, némelyik jó, némelyik nem. Nézzük előbb a rosszakat:

  1. Használjunk identity oszlopot! Ez minden sikertelen beszúrásnál is növelni fogja a sorszámot, és lukak maradnak utána.
  2. Kérdezzük le az utolsó kiadott sorszámot, és tároljuk el az eggyel nagyobbat. Ez csábítóan egyszerű, és speciális esetekben akár jó is lehet, a probléma a konkurrencia: ha ketten egyszerre kérdezik le a legutolsó sorszámot, ugyanazt fogják kiosztani következőnek.

És nézzünk egy jót: Tároljuk el a számlarekordot, egyelőre számlaszám nélkül, egy másik egyedi azonosítóval (ez lehet akár egy identity oszlop is), és tartsunk fenn egy táblát, aminek egyetlen oszlopa identity. Minden számlarekord eltárolás után húzzunk egy sorszámot (azaz szúrjunk be egy rekordot), és azt tegyük el számlaszámként. Mivel itt nem lehet sikertelen beszúrás, a sorszámozás folyamatos lesz. De mit csinálunk a nagyra nőtt sorszámgeneráló táblával? a TRUNCATE TABLE nem jó, mert az ugyan gyorsan üríti a táblát, de reseteli az identity értéket is, tehát megint az elejéről kezdjük osztani a számlákat. A DELETE technikailag jó, de sok rekord törlésénél esetleg lock eszkalációba futunk, aminek az lesz a vége, hogy senki nem fog új számlát készíteni egy darabig.

A problémára gyógyír Itzik egy kis mintascriptje:

USE tempdb;
CREATE TABLE dbo.AsyncSeq(val INT IDENTITY);
GO
CREATE PROC dbo.usp_AsyncSeq
 @val AS INT OUTPUT
AS
BEGIN TRAN
 SAVE TRAN S1;
 INSERT INTO dbo.AsyncSeq DEFAULT VALUES;
 SET @val = SCOPE_IDENTITY()
 ROLLBACK TRAN S1;
COMMIT TRAN
GO

Ez a dbo.usp_AsyncSeq tárolt eljárás tulajdonképpen az Oracle szekvencia implementációja. Fog egy táblát, amibe mindig beszúr, de nem menti el a beszúrást igazából, csak lekéri az identity értéket. Így a tábla mindig üres, és a karbantartása meg van oldva gyárilag. Használata kb. a következő:


USE tempdb;
-- készítünk egy számla táblát
CREATE TABLE dbo.Szamla (
a int identity,
b varchar(10) PRIMARY KEY, 
--csak azért kell ez a primary key, mert így a legkönnyebb sikertelen beszúrásokat szimulálni később
szamlaszam int)
GO

-- meg egy tárolt eljárást
CREATE PROCEDURE dbo.usp_UjSzamla
@b varchar(10)
AS
-- ha nem sikerül a számlarekord elmentése, ez a beállítás megakadályozza, hogy továbbfusson a script, és feleslegesen húzzon sorszámot
SET XACT_ABORT ON
declare @a int, @szla int
INSERT INTO dbo.Szamla(b) VALUES(@b)
SELECT @a = SCOPE_IDENTITY()
-- itt hívjuk meg a sorszámosztót, ha sikeresen elmentettük a számlát
EXEC dbo.usp_AsyncSeq @val = @szla OUTPUT;
UPDATE dbo.Szamla SET szamlaszam = @szla WHERE a = @a;
-- kikapcsoljuk a szigorúságot
SET XACT_ABORT OFF
GO

--és kipróbáljuk:
EXEC dbo.usp_UjSzamla 'PROBA'
EXEC dbo.usp_UjSzamla 'PROBA'
EXEC dbo.usp_UjSzamla 'PROBA2'

select * from dbo.Szamla
select * from dbo.AsyncSeq

Az eredeti tárolt eljárásban megüthette az ember szemét a SAVE TRAN(SACTION) utasítás. Ez pont olyan, mint a SAVE GAME a játékokban: vissza lehet állni a mentett állapotra. Igen, egy tranzakción belül, tulajdonképpen részleges rollback, ami ugye nincs. De mégis van. Erről a témáról hamarosan lesz egy cikk a Technet portálon, egyelőre érjétek be ennyivel.

Gyorsan jogot az összes tárolt eljárásra

Éppen építem a félautomata SQL Servert (mint már párszor említettem), és ennek egyik állomása az, hogy a szakmailag kevéssé felkészült, ámde elszánt fejlesztők ellen, akik hibernate-ben mindent összedobálnak, elveszem az alkalmazások dbo jogát, és helyette csak írni-olvasni tudnak majd. Ez jó ötlet, hiszen ott van a beépített db_datareader és db_datawriter role minden adatbázisban, de…

A problémát a tárolt eljárások és függvények okozzák, amelyekhez külön-külön kell jogot grantolni. Ezt persze lehet scriptből is, de azért elég jó esély van arra, hogy ezt el fogom izélni valamikor, és nem lesz joga az alkalmazás usernek vmit csinálni, mire azt mondják, hogy azonnal adjam vissza a dbo jogot. Ennek a problémának a kivédésén agyaltam, és már-már kezdtem hajlani a DDL triggerek felé (minden SP gyártás után automatikusan adjuk rá jogot a megfelelő usernek), amikor egy számomra sokkal szimpatikusabb ötletbe botlottam.
Continue reading ‘Gyorsan jogot az összes tárolt eljárásra’ »