Rollback

Azt már mindenki tudja, h PS-ben lehet kiválóan scriptet generálni a SQL entitásokról a Script() metódus segítségével. Felvetődött egy kérdés vkiben, hogy hogyan lehet csak DB sémát backupolni, és felvetődött erre a Powershell, meg a get-contentitem|%{$_.Script()}, mint alapgondolat. Ja, ez ezt adja vissza egy új táblámra:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[KeynoteDaily](
        [day] [date] NOT NULL,
        [site] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [measurements] [smallint] NULL,
        [failures] [smallint] NULL,
        [contenterrors] [smallint] NULL,
        [availability_wc] [real] NULL,
        [availability_woc] [real] NULL,
        [errors] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [loadtime] [smalldatetime] NULL,
        [comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Szóval jól néz ki, nem? Nem. addig néz ki jól, amíg nem jut eszembe, hogy van primary key is a táblán. Meg még további indexek. Amiket persze megint ki lehet scriptelni: ˘

%{$_.Indexes}|%{$_.script()}

Meg még ki tudja, mi marad ki. Na mindegy, tuti megoldást nem tudok, de ez olyan lámának tűnt, hogy elkezdtem nézegetni a metódusokat, és találtam egy olyat, hogy EnumScript():

PS > gci |%{$_.EnumScript()}

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[KeynoteDaily](
        [day] [date] NOT NULL,
        [site] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [measurements] [smallint] NULL,
        [failures] [smallint] NULL,
        [contenterrors] [smallint] NULL,
        [availability_wc] [real] NULL,
        [availability_woc] [real] NULL,
        [errors] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [loadtime] [smalldatetime] NULL,
        [comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_KeynoteDaily] PRIMARY KEY CLUSTERED
(
        [site] ASC,
        [day] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ix_test] ON [dbo].[KeynoteDaily]
(
        [failures] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [ix_test2] ON [dbo].[KeynoteDaily]
(
        [measurements] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Vagyis ő megcsinálja nekünk a rekurziót. Szerintem aranyos kényelmi szolgáltatás.
Ja, a fentiekhez a teljes path a következő volt: PS SQLSERVER:\SQL\MYCOMPUTER\DENALI\Databases\MyDatabase\Tables> . Csak a tisztánlátás kedvéért.

Ideje, hogy valami szakmait is írjak, mert kezd uncsi lenni a blog. Szóval van egy szép hosszú listám arról, hogy mi mindent nem mondtam el a Millenárisban tartott beszédemben, és ebből mazsolázgatok. Kezdjük is el!

Az első egy kicsi, de hasznos apróság: az OUTPUT clause vagy ahogy Serény tanár úr írná, a klóz. Mire jó? Tipikus jelenet, hogy kell update-elnem rekordokat egy OLTP adatbázisban, azaz az adat változik alattam, de szeretném tudni, hogy pontosan melyeket update-eltem meg. Erre a gyenge megoldás a

select primary_key into #temptabla from tabla
where status = 'NEW'

update tabla where primary_key in (select primary_key from #temptabla)
set status = 'ACTIVE'

drop table #temptabla

Ez egy egész jó megközelítés, két feltétel teljesülése esetén:

1. Nulla konkurrencia lehetséges, azaz pl. nem akar egyszerre két alkalmazásszerver dolgozni az adatbáziban, ekkor ugyanis ők lelkesen dolgozzák fel a rekordokat – egyszerre. Ezt nem részletezném, hogy miért gáz.
2. 2008-at vagy kevesebbet írunk. Azóta ugyanis van OUTPUT clause.

A dolog roppant egyszerű: bele van dugva egy belső trigger gyakorlatilag, ami a következőt tudja: ha beletömjük az OUTPUT-ot az INSERT/UPDATE/DELETE utasításba, akkor lesz inserted és/vagy deleted táblánk, és lehet kiszipkázni az adatokat, akár csak kiechózni, akár betenni táblaváltozókba. Az OUTPUT helye a WHERE feltétel előtt van még, erre figyeljetek. Valahogy így:

UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key
WHERE status = 'NEW'

Valljuk be, hogy jobban néz ki. Ha el akarjuk menteni az outputot későbbi reszeléshez vagy csak nyakonvágni a teljesítményt, akkor pedig a fenti példa így néz ki:

DECLARE @tablacska table (primary_key int);
UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key INTO @tablacska
WHERE status = 'NEW';
SELECT primary_key FROM @tablacska;

Ez egyébként fokozható, például ha már így előjött a konkurrencia, mint probléma, kiválóan megoldható a sok alkalmazás egy táblát túr probléma ezzel meg egy kis locking hinteléssel:

UPDATE tabla WITH (READPAST)
SET status = 'ACTIVE'
OUTPUT inserted.*
WHERE status = 'NEW'

Így ha több szerver dolgoz fel egy queue-t, hogy szépen mondjam, nem kell nagyon foglalkozniuk a másikkal. Egy konkrét szerver odamegy, kivesz annyi rekordot, amennyit akar (mert lehet TOP-ot is mondani az update-ben), ha valaki más is éppen update-el, akkor az általa lockolt rekordokat átugorja, és keres olyat, ami nincs lockolva. Ez a READPAST ajándéka: átugorja a lockolt rekordokat, így nem akadnak fenn egymáson a párhuzamos processzek.

(disclaimer: épp nincs SQL szerverem, ezért a példákat fejből írtam, ha nem működnek, bocsi, kijavítom :)

Diszket kellett bővíteni egy SQL clusterben, a lehető legkevesebb leállással, úgyhogy úgy döntöttem, hogy folytatom, amiben jó kezdek lenni: scriptelek. A cluster diszkek hozzáadása önmagában nem nagy szám (most felhördül legalább 28 ember, akinek már voltak rossz élményei a témában – nekem is, de a lekapcsolt többi node majdnem mindig dobott a dolgon), a jó rész az, hogy az SQL csak azokat a diszkeket használja, amiken dependál a service, a dependenciát pedig csak leállított helyzetben lehet állítgatni. Itt jön jól a script, hogy ne kelljen klikkelni, míg megöregszem. Ezúttal a cluster.exe volt a műtéti alany, az eredmény pedig alant látható (a sql12clus clustert megtámogatva):

cluster.exe sql12clus resource "SQL Server" /Offline
cluster.exe sql12clus resource "SQL Server" /AddDependency:"K: BillingData"
cluster.exe sql12clus resource "SQL Server" /AddDependency:"L: BillingLog"
cluster.exe sql12clus resource "SQL Server" /Online

Azaz leállít, dependenciát hozzáad, elindít. Egy öröm volt.

Az SQL 2008 R2 előadásomon a Lurdy Házban mutattam egy PS scriptet, ami a következő igényt elégíti ki: egy fájlrendszeren lakó könyvtárban lévő backup fájlokból kinyalja a backup információkat. Íme a script:

function List-Backups(
[string] $filename, ##fájl vagy könyvtárnév, dir parancs számára emészthető input kell
$Html, ## HTML fájl output neve, .\ prefix kell neki lokális esetén
$servername = '.' ## az SQL Server, aki lefuttatja a restore headeronly parancsot
) {

## a tulajdonságok, amik kellenek - teljes lista a BOL-ban
$proplist = ('Position', 'DatabaseName','ServerName','BackupFinishDate','DatabaseVersion')
## mint fent, csak a filename mezővel prefixeltem. igénytelen, de gyors megoldás,
$proplist2 = ('FileName', 'Position', 'DatabaseName','ServerName','BackupFinishDate','DatabaseVersion')

## ez a tömb tárolja a teljes listát
$allbck = @()

foreach ($file in (dir $filename)) ## ezért kellett dir kompatibilis input
  {
  ## lefut a restore headeronly...
  $backups = invoke-sqlcmd -ServerInstance ".\mysql" -Query "restore headeronly from disk = '$($file.FullName)'"
  $bck = @()
  ## a kiválasztott mezoket eltesszük a $bck változóba.
  ##   Igazából perzisztált scriptben nem illik olyan shortcutokat használni, mint % (foreach) meg ? (where)
  $backups|select $proplist|%{$bck += $_}
  ## hozzáadjuk a fájlnevet is...
  $bck |add-member noteproperty filename $file.FullName
  ## ... és a kibövitett listát eltesszük az összegzett listába
  $allbck += $bck|select $proplist2
  } #foreach $file
## kiírjuk az összegzett listát a standard outputra - ez a függvény kimenete
$allbck
## ha meg lett adva HTML fájlnév, akkor abba is kitoljuk a táblázatot, és meg is nyitjuk. Itt lehetne kezelni a .\ hiányát, de nem tettem, lustaságból
if ($Html)
  {
  $allbck|ConvertTo-Html|Out-file $Html
  ## itt nyitjuk meg, az alkalmazás a default app a megadott kiterjesztéshez, tehát a böngészőnk htm(l)-nél
  invoke-expression $Html
  } #if $html
}  #function List-Backups

## És nézzünk néhány példát is:

## nézzük meg a D:\SQLData\Backups könyvtár tartalmát, HTML-ben is
List-Backups D:\SQLData\Backups .\lista.html
## nézzünk meg egyetlen fájlt a konzolon
List-Backups D:\SQLData\Backups\Full.BAK
##keressük meg a Billing adatbázis legfrissebb full backupját tartalmazó fájlt és a backup pozícióját a megadott könyvtárban
List-Backups D:\SQLData\Backups | ?{$_.DatabaseName -eq 'Billing' -and $_.BackupTypeDescription -eq 'DATABASE'} | `
 Sort -prop BackupFinishDate -desc| select Filename, Position -first 1

Az utolsó parancsba beletoltam több mindent is. A ? (where) a szűrést hivatott bemutatni, a -eq és az -and a vicces operátorokat, amit el is rontottam a demóban, a backtick a többsoros parancs sorvégét jelzi, a többi meg magától értetődő, de azért jó látni, hogy a lehetőségek szinte korlátlanok.

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'