ProHoster > blog > Amministrazzjoni > MS SQL Server: BACKUP fuq sterojdi

MS SQL Server: BACKUP fuq sterojdi

Stenna! Stenna! Veru, dan mhuwiex artikolu ieħor dwar tipi ta 'backups ta' SQL Server. Lanqas se nitkellem dwar id-differenzi bejn il-mudelli ta 'rkupru u kif tittratta log overgrown.

Forsi (forsi biss), wara li taqra din il-kariga, tkun tista 'tassigura li l-backup li jitneħħa minnek bl-użu ta' mezzi standard jitneħħa għada bil-lejl, ukoll, 1.5 darbiet aktar malajr. U biss minħabba l-fatt li tuża ftit aktar parametri BACKUP DATABASE.

Jekk il-kontenut tal-post kien ovvju għalik, jiddispjaċini. Qrajt dak kollu li wasal għalih Google għall-frażi "habr sql server backup", u f'ebda artiklu wieħed ma sibt xi aċċenn tal-fatt li l-ħin tal-backup jista 'jkun b'xi mod influwenzat bl-użu ta' parametri.

Immedjatament niġbed l-attenzjoni tiegħek għall-kumment ta 'Alexander Gladchenko (@mssqlhelp):

Qatt tbiddel il-parametri BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE fil-produzzjoni. Huma magħmula biss għall-kitba ta 'artikoli bħal dawn. Fil-prattika, int se teħles mill-problemi tal-memorja fi ftit ħin.

Ikun, ovvjament, jibred li tkun l-aktar intelliġenti u post kontenut esklussiv, iżda, sfortunatament, dan mhux il-każ. Hemm kemm artikli/postijiet bil-lingwa Ingliża kif ukoll bil-lingwa Russa (dejjem inkun konfuż dwar x'sejħilhom b'mod korrett) iddedikati għal dan is-suġġett. Hawn huma xi wħud minn dawk li ltqajt magħhom: ħin, два, tlieta (fuq sql.ru).

Allura, biex tibda, se nwaħħal sintassi BACKUP kemmxejn imqaxxar minn MSDN (mill-mod, ktibt hawn fuq dwar BACKUP DATABASE, iżda dan kollu japplika kemm għall-backup tal-log tat-tranżazzjonijiet kif ukoll għall-backup differenzjali, iżda forsi b'effett inqas ovvju):

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 }
<...>

<…> - ifisser li kien hemm xi ħaġa hemmhekk, imma neħħejtha għax issa mhix rilevanti għas-suġġett.

Kif normalment tieħu backup? Kif "jgħallmu" kif jieħdu backups f'biljuni ta 'artikli? B'mod ġenerali, jekk għandi bżonn nagħmel backup ta' darba ta' xi database mhux kbira ħafna, awtomatikament nikteb xi ħaġa bħal din:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

U, b'mod ġenerali, probabbilment 75-90% tal-parametri kollha li normalment jissemmew f'artikoli dwar backups huma elenkati hawn. Ukoll, hemm ukoll INIT, SKIP. Żajt l-MSDN? Rajt li hemm għażliet għal skrins wieħed u nofs? Rajt ukoll...

Probabilment diġà rrealizzajt li aktar se nitkellmu dwar it-tliet parametri li baqgħu fl-ewwel blokk ta 'kodiċi - BLOCKSIZE, BUFFERCOUNT u MAXTRANSFERSIZE. Hawn huma d-deskrizzjonijiet tagħhom minn MSDN:

BLOCKSIZE = { jimblokka | @ blocksize_variable } - jindika d-daqs tal-blokk fiżiku f'bytes. Daqsijiet appoġġjati huma 512, 1024, 2048, 4096, 8192, 16, 384, u 32 bytes (768 KB). Il-valur default huwa 65 għal tagħmir tat-tejp u 536 għal tagħmir ieħor. Tipikament dan il-parametru mhuwiex meħtieġ minħabba li d-dikjarazzjoni BACKUP tagħżel awtomatikament id-daqs tal-blokk xieraq għall-apparat. L-issettjar tad-daqs tal-blokk jegħleb b'mod espliċitu l-għażla awtomatika tad-daqs tal-blokk.

BUFFERCOUNT = { buffercount | @ buffercount_variable } - Jiddefinixxi n-numru totali ta 'buffers I/O li se jintużaw għall-operazzjoni ta' backup. Tista 'tispeċifika kwalunkwe valur sħiħ pożittiv, iżda numru kbir ta' buffers jista 'jikkawża żball barra mill-memorja minħabba spazju ta' indirizz virtwali eċċessiv fil-proċess Sqlservr.exe.

L-ammont totali ta' spazju użat mill-buffers huwa ddeterminat bil-formula li ġejja: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } jispeċifika l-akbar daqs tal-pakkett tad-dejta, f'bytes, li għandu jiġi skambjat bejn SQL Server u l-midja tas-sett tal-backup. Multipli ta' 65 bytes (536 KB) sa 64 bytes (4 MB) huma appoġġjati.

Naħlef - stajt qrajt dan qabel, iżda qatt ma ġratli kemm jista 'jkollhom impatt fuq il-produttività. Barra minn hekk, apparentement, għandi bżonn nagħmel tip ta '"coming out" u nammetti li anke issa ma nifhimx għal kollox x'qed jagħmlu eżattament. Probabbilment għandi bżonn naqra aktar dwar I/O buffered u naħdem b'hard drive. Xi darba se nagħmel dan, imma għalissa nista' nikteb b'kitba li tiċċekkja kif dawn il-valuri jaffettwaw il-veloċità li biha jittieħed il-backup.

Għamilt database żgħira, madwar 10 GB fid-daqs, poġġiha fuq l-SSD, u poġġi d-direttorju għall-backups fuq l-HDD.

Noħloq tabella temporanja biex naħżen ir-riżultati (m'għandix temporanju, u għalhekk nista' nħaffer ir-riżultati f'aktar dettall, imma int tiddeċiedi għalik innifsek):

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
);

Il-prinċipju tal-iskript huwa sempliċi - loops nested, li kull wieħed minnhom jibdel il-valur ta 'parametru wieħed, daħħal dawn il-parametri fil-kmand BACKUP, ħlief l-aħħar rekord bl-istorja minn msdb.dbo.backupset, ħassar il-fajl tal-backup u l-iterazzjoni li jmiss . Peress li d-dejta tal-eżekuzzjoni tal-backup hija meħuda mill-backupset, l-eżattezza hija kemmxejn mitlufa (m'hemm l-ebda frazzjonijiet ta 'sekondi), iżda aħna se jgħixu dan.

L-ewwel trid tattiva xp_cmdshell biex tħassar backups (imbagħad tinsiex tiddiżattivah jekk m'għandekx bżonnha): 

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

Ukoll, fil-fatt:

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

Jekk f'daqqa waħda għandek bżonn kjarifika dwar dak li qed jiġri hawn, ikteb fil-kummenti jew PM. Għalissa, ngħidlek biss dwar il-parametri li npoġġi f'BACKUP DATABASE.

Għal BLOCKSIZE għandna lista "magħluqa" ta' valuri, u ma għamiltx backup b'BLOCKSIZE < 4KB. MAXTRANSFERSIZE kwalunkwe numru li huwa multiplu ta' 64KB - minn 64KB sa 4MB. Id-default fis-sistema tiegħi huwa 1024KB, ħadt 512 - 1024 - 2048 - 4096.

Kien aktar diffiċli ma 'BUFFERCOUNT - jista' jkun kwalunkwe numru pożittiv, iżda l-link tgħid kif huwa kkalkulat fil-BACKUP DATABASE u għaliex valuri kbar huma perikolużi?. Jgħid ukoll kif tikseb informazzjoni dwar liema BUFFERCOUNT huwa attwalment magħmul il-backup - għalija huwa 7. Ma kien hemm l-ebda punt li tnaqqasha, u l-limitu ta 'fuq ġie skopert b'mod sperimentali - b'BUFFERCOUNT = 896 u MAXTRANSFERSIZE = 4194304 il-backup waqa' bi żball (li dwaru miktub fil-link hawn fuq):

Msg 3013, Livell 16, Stat 1, Linja 7 BACKUP DATABASE qed jintemm b'mod anormali.

Msg 701, Livell 17, Stat 123, Linja 7 M'hemmx biżżejjed memorja tas-sistema fil-ġabra tar-riżorsi 'default' biex titħaddem din il-mistoqsija.

Għal paragun, l-ewwel ser nuri r-riżultati tat-tħaddim ta 'backup mingħajr ma nispeċifika l-ebda parametru:

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

Ukoll, backup u backup:

Ipproċessati 1070072 paġni għad-database 'bt', fajl 'bt' fuq il-fajl 1.

Ipproċessati 2 paġni għad-database 'bt', fajl 'bt_log' fuq il-fajl 1.

BACKUP DATABASE ipproċessat b'suċċess 1070074 paġna fi 53.171 sekondi (157.227 MB/sec).

L-iskript innifsu, li ttestja l-parametri, ħadem fi ftit sigħat, il-kejl kollu kien fih google spreadsheet. U hawn għażla ta 'riżultati bl-aħjar tliet ħinijiet ta' eżekuzzjoni (ppruvajt nagħmel grafika sabiħa, iżda fil-post ser ikolli nagħmel ma 'tabella, u fil-kummenti @mixsture miżjud grafika jibred ħafna).

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 fuq sterojdi

Attenzjoni, nota importanti ħafna minn @mixsture ta ' kumment:

Nistgħu ngħidu b'fiduċja li r-relazzjoni bejn il-parametri u l-veloċità tal-backup f'dawn il-firxiet ta 'valuri hija każwali, m'hemm l-ebda mudell. Iżda tbiegħed mill-parametri built-in ovvjament kellu effett tajjeb fuq ir-riżultat

Dawk. Biss bil-ġestjoni tal-parametri standard BACKUP kien gwadann 2 darbiet fil-ħin tat-tneħħija tal-backup: 26 sekonda, kontra 53 fil-bidu. Dak mhux ħażin, hux? Imma rridu naraw x’jiġri bir-restawr. X'jiġri jekk issa tieħu 4 darbiet itwal biex tirkupra?

L-ewwel, ejja nkejlu kemm iddum biex tirrestawra backup b'settings default:

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

Ukoll, int stess taf li, il-modi qegħdin hemm, issostitwixxi mhux jissostitwixxi, l-irkupru mhuwiex irkupru. U nagħmilha hekk:

Ipproċessati 1070072 paġni għad-database 'bt', fajl 'bt' fuq il-fajl 1.

Ipproċessati 2 paġni għad-database 'bt', fajl 'bt_log' fuq il-fajl 1.

RESTORE DATABASE ipproċessat b'suċċess 1070074 paġna f'40.752 sekondi (205.141 MB/sec).

Issa ser nipprova nirrestawra l-backups meħuda b'BLOCKSIZE, BUFFERCOUNT u MAXTRANSFERSIZE mibdula.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE ipproċessat b'suċċess 1070074 paġna f'32.283 sekondi (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE ipproċessat b'suċċess 1070074 paġna f'32.682 sekondi (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE ipproċessat b'suċċess 1070074 paġna f'32.091 sekondi (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE ipproċessat b'suċċess 1070074 paġna f'32.401 sekondi (258.015 MB/sec).

L-istqarrija RESTORE DATABASE ma tinbidilx waqt l-irkupru; dawn il-parametri mhumiex speċifikati fiha; SQL Server innifsu jiddeterminahom mill-backup. U huwa ċar li anke bl-irkupru jista 'jkun hemm gwadann - kważi 20% aktar malajr (Biex inkun onest, ma kontx qattajt ħafna ħin fuq l-irkupru, għamilt diversi backups "mgħaġġla" u żgurajt li ma kienx hemm deterjorament).

Fil-każ, ħalluni niċċara li dawn mhumiex xi parametri li huma ottimali għal kulħadd. Tista 'biss tikseb l-aħjar parametri għalik innifsek billi tittestja. Sibt dawn ir-riżultati, int se tikseb oħrajn differenti. Imma tara li tista '"tirfina" l-backups tiegħek u fil-fatt jistgħu jiffurmaw u jużaw aktar malajr.

Nirrakkomanda bil-qawwa wkoll li taqra d-dokumentazzjoni kollha kemm hi, għax jista' jkun hemm sfumaturi speċifiċi għas-sistema tiegħek.

Peress li bdejt nikteb dwar backups, irrid immedjatament nikteb dwar "ottimizzazzjoni" waħda aktar, li hija aktar komuni minn parametri "irfinar" (sa fejn nifhem, huwa użat minn tal-inqas xi utilitajiet backup, forsi flimkien mal-parametri deskritt qabel), iżda għadu ma ġiex deskritt fuq Habré lanqas.

Jekk inħarsu lejn it-tieni linja fid-dokumentazzjoni, eżatt taħt BACKUP DATABASE, hemm naraw:

TO <backup_device> [ ,...n ]

X'taħseb li se jiġri jekk tispeċifika diversi backup_devices? Is-sintassi tippermettiha. U se sseħħ ħaġa interessanti ħafna - il-backup se jkun sempliċement "mifrux" fuq diversi apparati. Dawk. kull "apparat" individwalment se jkun inutli, mitlufa waħda, tilfet il-backup kollu. Imma kif se taffettwa smearing bħal dan il-veloċità tal-backup?

Ejja nippruvaw nagħmlu backup fuq żewġ "apparati" li jinsabu ħdejn xulxin fl-istess folder:

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

Missirijiet tad-dinja, għaliex qed isir dan?

Ipproċessati 1070072 paġni għad-database 'bt', fajl 'bt' fuq il-fajl 1.

Ipproċessati 2 paġni għad-database 'bt', fajl 'bt'log' fuq il-fajl 1.

BACKUP DATABASE ipproċessat b'suċċess 1070074 paġna fi 40.092 sekondi (208.519 MB/sec).

Il-backup saret 25% aktar mgħaġġla biss mill-blu? X'jiġri jekk inżidu ftit apparati aktar?

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

BACKUP DATABASE ipproċessat b'suċċess 1070074 paġna fi 34.234 sekondi (244.200 MB/sec).

B'kollox, il-qligħ huwa madwar 35% tal-ħin tat-teħid ta 'backup biss minħabba l-fatt li l-backup huwa miktub għal 4 fajls fuq disk wieħed f'daqqa. Iċċekkja numru akbar - m'hemm l-ebda qligħ fuq il-laptop tiegħi, bl-aħjar mod - 4 apparati. Għalik - ma nafx, trid tiċċekkja. Ukoll, bil-mod, jekk għandek dawn l-apparati - dawn huma diski verament differenti, prosit, il-qligħ għandu jkun saħansitra aktar sinifikanti.

Issa ejja nitkellmu dwar kif nirrestawraw din il-kuntentizza. Biex tagħmel dan, ikollok tibdel il-kmand tal-irkupru u telenka l-apparati kollha:

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 ipproċessat b'suċċess 1070074 paġna f'38.027 sekondi (219.842 MB/sec).

Ftit aktar mgħaġġla, iżda x'imkien qrib, mhux sinifikanti. B'mod ġenerali, il-backup titneħħa aktar malajr u restawrata bl-istess mod - suċċess? Għalija, huwa pjuttost suċċess. Dan hija importanti, għalhekk nirrepeti - jekk int jekk titlef mill-inqas wieħed minn dawn il-fajls, titlef il-backup kollu.

Jekk tħares fil-ġurnal lejn l-informazzjoni tal-backup murija billi tuża Trace Flags 3213 u 3605, tinduna li meta tagħmel backup għal diversi apparati, tal-inqas in-numru ta’ BUFFERCOUNT jiżdied. Probabbilment, tista 'tipprova tagħżel parametri aktar ottimali għal BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, imma ma rnexxiex mill-ewwel, u kont għażżien wisq biex nerġa' nwettaq tali ttestjar, iżda għal numru differenti ta 'fajls. U huwa tal-mistħija dwar ir-roti. Jekk trid torganizza tali ttestjar fid-dar, mhuwiex diffiċli li terġa 'tiġi ġdida l-iskrittura.

Fl-aħħarnett, ejja nitkellmu dwar il-prezz. Jekk il-backup titneħħa b'mod parallel max-xogħol tal-utenti, għandek bżonn tieħu approċċ responsabbli ħafna għall-ittestjar, għax jekk il-backup titneħħa aktar malajr, id-diski huma tensjoni aktar, it-tagħbija fuq il-proċessur tiżdied (għad trid tikkompressa hija fuq il-fly), u għalhekk, ir-rispons ġenerali tas-sistema tonqos.

Niċċajta, imma nifhem perfettament li ma għamilt l-ebda rivelazzjoni. Dak li hemm miktub hawn fuq huwa sempliċement turija ta' kif tista' tagħżel l-aħjar parametri biex tieħu backups.

Ftakar li dak kollu li tagħmel isir bil-periklu u r-riskju tiegħek. Iċċekkja l-backups tiegħek u tinsiex dwar DBCC CHECKDB.

Sors: www.habr.com

Żid kumment