ProHoster > Blog > Utongi > MS SQL Server: BACKUP pane steroids

MS SQL Server: BACKUP pane steroids

Mira! Mira! Chokwadi, ichi hachisi chimwe chinyorwa nezve mhando dzeSQL Server backups. Ini handisi kuzotaura nezve misiyano pakati pekudzoreredza mamodheru uye maitiro ekubata neiyo yakakura danda.

Zvichida (zvichida), mushure mekuverenga iyi positi, iwe uchakwanisa kuve nechokwadi chekuti backup inobviswa kubva kwauri uchishandisa yakajairwa nzira ichabviswa mangwana manheru, zvakanaka, 1.5 nguva nekukurumidza. Uye chete nekuda kwekuti iwe unoshandisa zvishoma zvishoma BACKUP DATABASE paramita.

Kana zviri mukati meiyo post zvaive pachena kwauri, ndine urombo. Ndakaverenga zvese zvakawanikwa neGoogle pamutsara wekuti "habr sql server backup", uye hapana kana chinyorwa chimwe chete chandakawana chero kutaurwa nezvenyaya yekuti nguva yekuchengetedza inogona kukanganiswa neimwe nzira uchishandisa paramita.

Ini ndichakurumidza kukwevera pfungwa dzako kune zvakataurwa naAlexander Gladchenko (@mssqlhelp):

Usambofa wakashandura iyo BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE paramita mukugadzira. Izvo zvakagadzirirwa chete kunyora zvinyorwa zvakadaro. Mukuita, iwe unobvisa matambudziko ekurangarira pasina nguva.

Zvingave, zvechokwadi, zvinotonhorera kuve akangwara uye kutumira ega zvemukati, asi, zvinosuruvarisa, izvi handizvo. Kune zvese zviri zviviri zvinyorwa zvemutauro weChirungu nemutauro weRussia (ndinogara ndichinetsekana kuti ndezvipi zvekudzidaidza nemazvo) zvakapihwa kune iyi nyaya. Hezvino zvimwe zvezvandakawana: nguva, Π΄Π²Π°, matatu (pa sql.ru).

Saka, kutanga, ini ndichabatanidza yakabviswa-pasi BACKUP syntax kubva msdn (nenzira, ini ndakanyora pamusoro nezve BACKUP DATABASE, asi zvese izvi zvinoshanda kune ese ari maviri transaction log backup uye mutsauko backup, asi pamwe ine isinga nyatsojeka mhedzisiro):

BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  <...>
  [ WITH { <...>
           | <general_WITH_options> [ ,...n ] } ]
[;]

<general_WITH_options> [ ,...n ]::=
<...>
--Media Set Options
 <...>
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
<...>

<…> - zvinoreva kuti paive nechimwe chinhu ipapo, asi ndakachibvisa nekuti ikozvino hachina basa nemusoro wenyaya.

Iwe unowanzo tora sei backup? Ivo "vanodzidzisa" sei maitiro ekutora backups mumabhiriyoni ezvinyorwa? Kazhinji, kana ndichida kuita imwe-nguva backup yeimwe isiri yakakura kwazvo dhatabhesi, ini ndinozonyora chimwe chinhu chakadai:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--Π»Π°Π΄Π½ΠΎ, CHECKSUM я написал Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΠΊΠ°Π·Π°Ρ‚ΡŒΡΡ ΡƒΠΌΠ½Π΅Π΅

Uye, kazhinji, pamwe 75-90% yeese ma paramita anowanzo kutaurwa muzvinyorwa nezve backups akanyorwa pano. Zvakanaka, kune zvakare INIT, SKIP. Wakamboshanyira MSDN here? Waona here kuti pane zvingasarudzwa zveimwe nehafu skrini? Ndakaona zvakare...

Iwe unogona kunge watoona kuti mberi tichataura nezve matatu maparamita akasara mubhokisi rekutanga rekodhi - BLOCKSIZE, BUFFERCOUNT uye MAXTRANSFERSIZE. Heino tsananguro yavo kubva kuMSDN:

BLOGSIZE = { blocksize | @ blocksize_variable } - inoratidza saizi yebhuroka yemuviri mumabhaiti. Saizi dzinotsigirwa dzinoti 512, 1024, 2048, 4096, 8192, 16, 384, uye 32 bytes (768 KB). Iko kukosha kweiyo 65 yematepi zvishandiso uye 536 kune mamwe madivayiri. Kazhinji iyi parameter haina basa nekuti BACKUP statement inosarudza otomatiki saizi yeblock yakakodzera yechishandiso. Kuseta saizi yebhuroka zvinonyanya kukurira iyo otomatiki block size kusarudzwa.

BUFFERCOUNT = { buffercount | @ buffercount_variable } - Inotsanangura huwandu hwese hwemabhafa eI/O achashandiswa kuita backup. Iwe unogona kutsanangura chero yakanaka yehuwandu kukosha, asi huwandu hukuru hwemabuffers hunogona kukonzera kunze-kwe-memory kukanganisa nekuda kwekunyanyisa kwekero nzvimbo muSqlservr.exe maitiro.

Huwandu hwenzvimbo inoshandiswa nemabuffers inotarwa neinotevera fomula: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } inotsanangura saizi yakakura kwazvo yepakiti yedata, mumabhayithi, kuchinjanisa pakati peSQL Server neiyo backup set media. Mazhinji e65 bytes (536 KB) anosvika 64 bytes (4 MB) anotsigirwa.

Ndinopika - ndakamboverenga izvi, asi hazvina kumboitika kwandiri kuti vangave nekukanganisa kwakadii pakubudirira. Uyezve, sezviri pachena, ndinofanira kuita rudzi rwe "kubuda" uye kubvuma kuti kunyange iye zvino handisi kunyatsonzwisisa kuti chii chaizvo chavari kuita. Ini pamwe ndinoda kuverenga zvakawanda nezve buffered I/O uye kushanda nehard drive. Rimwe zuva ndichaita izvi, asi ikozvino ndinogona kungonyora script iyo inotarisa kuti izvi zvakakosha zvinokanganisa sei kumhanya kunotorwa backup.

Ndakagadzira dhatabhesi diki, rinenge 10 GB muhukuru, ndikaisa paSSD, uye ndaisa dhairekitori rekuchengetedza paHDD.

Ini ndinogadzira tafura yenguva pfupi yekuchengetedza mhedzisiro (ini handina iyo yenguva pfupi, saka ndinogona kuchera mukati memibairo zvakadzama, asi iwe unozvisarudzira wega):

DROP TABLE IF EXISTS ##bt_results; 

CREATE TABLE ##bt_results (
    id              int IDENTITY (1, 1) PRIMARY KEY,
    start_date      datetime NOT NULL,
    finish_date     datetime NOT NULL,
    backup_size     bigint NOT NULL,
    compressed_size bigint,
    block_size      int,
    buffer_count    int,
    transfer_size   int
);

Nheyo yezvinyorwa zviri nyore - nested loops, imwe neimwe inoshandura kukosha kweimwe parameter, isa ma parameter aya mumirairo yeBACKUP, chengetedza rekodhi yekupedzisira nenhoroondo kubva msdb.dbo.backupset, bvisa faira yekuchengetedza uye kudzokorora kunotevera. . Sezvo iyo backup yekuuraya data yakatorwa kubva kune backupset, iko kurongeka kwakarasika (hapana zvikamu zvemasekondi), asi isu tichapona izvi.

Kutanga iwe unofanirwa kugonesa xp_cmdshell kudzima backups (saka usakanganwa kuidzima kana iwe usingazvide): 

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  
GO

Zvakanaka, chaizvoizvo:

DECLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH 
    COMPRESSION,
    BLOCKSIZE = {bs},
    BUFFERCOUNT = {bc},
    MAXTRANSFERSIZE = {ts}';

DECLARE @sql AS nvarchar(max);

/* BLOCKSIZE values */
DECLARE @bs     int = 4096, 
        @max_bs int = 65536;

/* BUFFERCOUNT values */
DECLARE @bc     int = 7,
        @min_bc int = 7,
        @max_bc int = 800;

/* MAXTRANSFERSIZE values */
DECLARE @ts     int = 524288,   --512KB, default = 1024KB
        @min_ts int = 524288,
        @max_ts int = 4194304;  --4MB

SELECT TOP 1 
    @bs = COALESCE (block_size, 4096), 
    @bc = COALESCE (buffer_count, 7), 
    @ts = COALESCE (transfer_size, 524288)
FROM ##bt_results
ORDER BY id DESC;

WHILE (@bs <= @max_bs)
BEGIN
    WHILE (@bc <= @max_bc)
    BEGIN       
        WHILE (@ts <= @max_ts)
        BEGIN
            SET @sql = REPLACE (REPLACE (REPLACE(@tmplt, N'{bs}', CAST(@bs AS nvarchar(50))), N'{bc}', CAST (@bc AS nvarchar(50))), N'{ts}', CAST (@ts AS nvarchar(50)));

            EXEC (@sql);

            INSERT INTO ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
            SELECT TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size,  @bs, @bc, @ts 
            FROM msdb.dbo.backupset
            ORDER BY backup_set_id DESC;

            EXEC xp_cmdshell 'del "D:SQLServerbackupbt.bak"', no_output;

            SET @ts += @ts;
        END
        
        SET @bc += @bc;
        SET @ts = @min_ts;

        WAITFOR DELAY '00:00:05';
    END

    SET @bs += @bs;
    SET @bc = @min_bc;
    SET @ts = @min_ts;
END

Kana iwe ukangoerekana wada kujekeswa pane zviri kuitika pano, nyora mune zvakataurwa kana PM. Parizvino, ini ndinongokuudza nezve ma paramita andinoisa mu BACKUP DATABASE.

Kune BLOCKSIZE tine "yakavharwa" runyorwa rwezvakakosha, uye ini handina kuita backup neBLOCKSIZE <4KB. MAXTRANSFERSIZE chero nhamba iyo yakawanda ye64KB - kubva pa64KB kusvika 4MB. Iyo yakasarudzika pane yangu system ndeye 1024KB, ini ndakatora 512 - 1024 - 2048 - 4096.

Zvakanga zvakaoma neBUFFERCOUNT - inogona kuve chero nhamba yakanaka, asi chinongedzo chinodaro inoverengerwa sei mu BACKUP DATABASE uye nei hukuru hukuru hune njodzi?. Inotaurawo kuti ungawana sei ruzivo rwekuti ndeipi BUFFERCOUNT iyo backup yakanyatsogadzirwa nayo - kwandiri ndeye 7. Pakanga pasina chikonzero chekudzikisa, uye muganho wepamusoro wakawanikwa nekuedza - neBUFFERCOUNT = 896 uye MAXTRANSFERSIZE = 4194304 kuchengetedza kwakawira ne. kukanganisa (pamusoro pezvakanyorwa mulink iri pamusoro):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE iri kupera zvisiri izvo.

Msg 701, Level 17, State 123, Line 7 Hapana kukwana system memory mune resource pool 'default' yekumhanyisa mubvunzo uyu.

Kuenzanisa, ini ndichatanga ndaratidza mhedzisiro yekumhanyisa backup pasina kutsanangura chero ma paramita zvachose:

BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;

Zvakanaka, backup uye backup:

Yakagadziriswa 1070072 mapeji e database 'bt', faira 'bt' pafaira 1.

Yakagadziriswa mapeji maviri e database 'bt', faira 'bt_log' pafaira 2.

BACKUP DATABASE yakabudirira kugadzirisa 1070074 mapeji mu53.171 seconds (157.227 MB/sec).

Iyo script pachayo, ichiyedza ma parameter, yakashanda mumaawa mashoma, zviyero zvese zvaive mukati google spreadsheet. Uye heino sarudzo yemhedzisiro iine matatu akanakisa ekuuraya nguva (ndakaedza kugadzira yakanaka girafu, asi mune positi ndichafanira kuita netafura, uye mune zvakataurwa. @mixsture akawedzera yakanaka kwazvo mifananidzo).

SELECT TOP 7 WITH TIES 
    compressed_size, 
    block_size, 
    buffer_count, 
    transfer_size,
    DATEDIFF(SECOND, start_date, finish_date) AS backup_time_sec
FROM ##bt_results
ORDER BY backup_time_sec ASC;

MS SQL Server: BACKUP pane steroids

Attention, chinyorwa chakakosha kubva @mixsture kubva commentary:

Tinogona kutaura nechivimbo kuti hukama pakati pemaparamita uye kukurumidza kwekuchengetedza mukati meaya mazinga ehukoshi hazvina kurongeka, hapana pateni. Asi kuenda kure neakavakirwa-mukati maparameter zviri pachena aive nemhedzisiro yakanaka pamugumisiro

Avo. Chete nekugadzirisa zvakajairwa BACKUP paramita yaive 2-yakapetwa kuwana mu backup yekubvisa nguva: 26 masekonzi, maringe ne53 pakutanga. Izvo hazvina kuipa, handiti? Asi tinofanira kuona kuti chii chinoitika nekudzorerwa. Zvakadini kana zvino zvichitora 4 nguva yakareba kuti apore?

Kutanga, ngatiyere kuti zvinotora nguva yakareba sei kudzoreredza backup ine default marongero:

RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;

Zvakanaka, iwe pachako unoziva kuti, nzira dziripo, kutsiva hakusi kutsiva, kupora hakusi kupora. Uye ndinozviita seizvi:

Yakagadziriswa 1070072 mapeji e database 'bt', faira 'bt' pafaira 1.

Yakagadziriswa mapeji maviri e database 'bt', faira 'bt_log' pafaira 2.

RESTORE DATABASE yakagadziridzwa nemapeji 1070074 mumasekonzi 40.752 (205.141 MB/sec).

Iye zvino ndichaedza kudzoreredza ma backups akatorwa akachinja BLOCKSIZE, BUFFERCOUNT uye MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE yakagadziridzwa nemapeji 1070074 mumasekonzi 32.283 (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE yakagadziridzwa nemapeji 1070074 mumasekonzi 32.682 (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE yakagadziridzwa nemapeji 1070074 mumasekonzi 32.091 (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE yakagadziridzwa nemapeji 1070074 mumasekonzi 32.401 (258.015 MB/sec).

Iyo RESTORE DATABASE chirevo hachichinje panguva yekudzoreredza; aya ma paramita haana kutaurwa mairi; SQL Server pachayo inovasarudza kubva kune backup. Uye zviri pachena kuti kunyangwe nekupora kunogona kuve nekuwana - ingangoita 20% nekukurumidza (Kutaura chokwadi, handina kupedza nguva yakawanda ndichipora, ndakamhanya nepakati peakati wandei "anokurumidza" backups uye ndakaita chokwadi chekuti pakanga pasina kuparara.).

Zvingoitika, rega ndijekese kuti aya haasi mamwe ma paramita akanakira munhu wese. Iwe unogona chete kuwana iyo yakakwana ma parameter ega nekuyedza. Ndine mhedzisiro iyi, uchawana akasiyana. Asi iwe unoona kuti iwe unogona "tune" yako backups uye ivo vanogona chaizvo kuumba uye kutumira nekukurumidza.

Ini zvakare ndinokurudzira kuti uverenge zvinyorwa zvakazara, nekuti panogona kunge paine nuances yakanangana nehurongwa hwako.

Kubva pandakatanga kunyora nezve backups, ndinoda kunyora nekukurumidza nezve imwezve "optimization", iyo yakajairika pane "tuning" paramita (sekunzwisisa kwandinoita, inoshandiswa nezvimwe zvinoshandisa backup, pamwe pamwe nemaparamita. yakatsanangurwa kare), asi haisati yatsanangurwa paHabrΓ© zvakare.

Kana tikatarisa mutsara wechipiri mune zvinyorwa, pasi pe BACKUP DATABASE, ipapo tinoona:

TO <backup_device> [ ,...n ]

Iwe unofunga kuti chii chichaitika kana ukatsanangura akati wandei backup_devices? Syntax inobvumira. Uye chinhu chinonakidza kwazvo chichaitika - iyo backup inongo "kupararira" pamusoro pemidziyo yakati wandei. Avo. "mudziyo" wega wega uchange usingabatsiri, wakarasika, wakarasa backup yese. Asi kuzora kwakadaro kuchakanganisa sei kukurumidza kukurumidza?

Ngatiedzei kuita backup pa "midziyo" miviri iri padivi nepadivi mune imwechete folda:

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak'   
WITH COMPRESSION;

Madzibaba enyika zvirikuitirwei?

Yakagadziriswa 1070072 mapeji e database 'bt', faira 'bt' pafaira 1.

Yakagadziriswa mapeji maviri e database 'bt', faira 'bt'log' pane faira 1.

BACKUP DATABASE yakabudirira kugadzirisa 1070074 mapeji mu40.092 seconds (208.519 MB/sec).

Iyo backup yave 25% nekukurumidza kubva kunze kweblue? Ko kana tikawedzera mimwe michina?

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;

BACKUP DATABASE yakabudirira kugadzirisa 1070074 mapeji mu34.234 seconds (244.200 MB/sec).

Pakazara, iyo pfuma inenge 35% yenguva yekutora backup chete nekuda kwekuti iyo backup inonyorwa kune 4 mafaera pane imwe diski kamwechete. Ndakatarisa nhamba yakakura - hapana kuwana palaptop yangu, zvakanyatsonaka - 4 zvishandiso. Kwauri - ini handizive, iwe unofanirwa kutarisa. Zvakanaka, nenzira, kana iwe uine zvishandiso izvi - aya madhisiki akasiyana, makorokoto, kuwana kunofanirwa kuve kwakakosha.

Zvino ngatitaure pamusoro pekuti tingadzorera sei mufaro uyu. Kuti uite izvi, iwe uchafanirwa kushandura iyo yekudzoreredza kuraira uye kunyora ese maturusi:

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

RESTORE DATABASE yakagadziridzwa nemapeji 1070074 mumasekonzi 38.027 (219.842 MB/sec).

Zvinokurumidza zvishoma, asi pane imwe nzvimbo padyo, kwete yakakosha. Muzhinji, iyo backup inobviswa nekukurumidza uye kudzorerwa nenzira imwechete - kubudirira? Kana ndirini, kubudirira chaiko. Izvi inokosha, saka ndinodzokorora - kana iwe kana ukarasikirwa nerimwe remafaira aya, unorasa backup yese.

Kana iwe ukatarisa murogi pane iyo backup ruzivo inoratidzwa uchishandisa Trace Mireza 3213 uye 3605, uchaona kuti kana uchitsigira kune akati wandei, huwandu hweBUFFERCOUNT hunowedzera. Zvichida, unogona kuedza kusarudza mamwe maparamita akanyanya eBUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, asi handina kubudirira pakarepo, uye ndakanga ndine usimbe kuita kuedza kwakadaro zvakare, asi kune nhamba yakasiyana yemafaira. Uye zvinonyadzisa pamusoro pemavhiri. Kana iwe uchida kuronga kuedza kwakadaro kumba, hazvina kuoma kugadzirisa script.

Pakupedzisira, ngatitaure nezve mutengo. Kana iyo backup ikabviswa pamwe chete nebasa revashandisi, iwe unofanirwa kutora nzira ine mutoro wekuyedza, nekuti kana backup ikabviswa nekukurumidza, madhisiki anoremerwa zvakanyanya, mutoro uri pa processor unowedzera (iwe uchiri kumanikidza. iyo pane nhunzi), uye saizvozvo, kupindurwa kwese kwehurongwa kunoderera.

Kungoseka chete, asi ndinonzwisisa zvakakwana kuti handina kuzviratidza. Izvo zvakanyorwa pamusoro ingori ratidziro yekuti iwe ungasarudza sei yakakwana ma parameter ekutora backups.

Rangarira kuti zvese zvaunoita unozviitira wega nenjodzi. Tarisa ma backups ako uye usakanganwe nezveDBCC CHECKDB.

Source: www.habr.com

Voeg