ProHoster > Blog > Administrasyon an > MS SQL sèvè: BACKUP sou estewoyid

MS SQL sèvè: BACKUP sou estewoyid

Tann! Tann! Se vre, sa a se pa yon lòt atik sou kalite sovgad SQL sèvè. Mwen pa pral menm pale sou diferans ki genyen ant modèl rekiperasyon ak fason yo fè fas ak yon boutèy demi lit anvayi.

Petèt (jis petèt), apre ou fin li pòs sa a, ou pral kapab asire w ke backup la ke yo retire nan ou lè l sèvi avèk mwayen estanda yo pral retire demen swa, byen, 1.5 fwa pi vit. Epi sèlman akòz lefèt ke ou itilize yon ti kras plis paramèt BACKUP DATABASE.

Si kontni pòs la te evidan pou ou, mwen regrèt. Mwen li tout sa Google te rive nan fraz "habr sql sèvè backup la", epi nan pa yon sèl atik mwen te jwenn okenn mansyone nan lefèt ke tan an backup ka yon jan kanmenm enfliyanse lè l sèvi avèk paramèt.

Mwen pral imedyatman atire atansyon ou sou kòmantè a nan Alexander Gladchenko (@mssqlhelp):

Pa janm chanje paramèt BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE nan pwodiksyon an. Yo fè sèlman pou ekri atik sa yo. Nan pratik, ou pral debarase m de pwoblèm memwa nan pa gen tan.

Li ta, nan kou, fre yo dwe pi entelijan ak afiche kontni eksklizif, men, malerezman, sa a se pa ka a. Genyen tou de atik/posts nan lang angle ak nan lang Ris (mwen toujou konfonn sou sa yo rele yo kòrèkteman) konsakre nan sijè sa a. Men kèk nan sa mwen te rankontre yo: tan, два, twa (sou sql.ru).

Se konsa, pou kòmanse, mwen pral tache yon sentaks BACKUP yon ti kras dezabiye soti nan MSDN (Bon wout la, mwen te ekri pi wo a sou BACKUP DATABASE, men tout sa a aplike a tou de backup log tranzaksyon ak backup diferans, men petèt ak yon efè mwens evidan):

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

<…> - sa vle di ke te gen yon bagay la, men mwen retire li paske kounye a li pa enpòtan nan sijè a.

Ki jan ou anjeneral pran yon backup? Ki jan yo "anseye" ki jan yo pran sovgad nan dè milya de atik? An jeneral, si mwen bezwen fè yon backup yon sèl fwa nan kèk baz done ki pa trè gwo, mwen pral otomatikman ekri yon bagay tankou sa a:

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

Epi, an jeneral, pwobableman 75-90% nan tout paramèt ki anjeneral mansyone nan atik sou sovgad yo ki nan lis isit la. Oke, gen tou INIT, SKIP. Èske w te vizite MSDN? Èske w te wè ke gen opsyon pou yon sèl ak yon mwatye ekran? Mwen te wè tou...

Ou pwobableman deja reyalize ke pi lwen nou pral pale sou twa paramèt ki te rete nan premye blòk nan kòd - BLOCKSIZE, BUFFERCOUNT ak MAXTRANSFERSIZE. Men deskripsyon yo soti nan MSDN:

BLOCKSIZE = { blocksize | @ blocksize_variable } - endike gwosè blòk fizik la an byte. Gwosè yo sipòte yo se 512, 1024, 2048, 4096, 8192, 16, 384, ak 32 bytes (768 KB). Valè default la se 65 pou aparèy kasèt ak 536 pou lòt aparèy. Tipikman paramèt sa a pa nesesè paske deklarasyon BACKUP otomatikman chwazi gwosè blòk apwopriye pou aparèy la. Anviwònman gwosè blòk la pase plis pase seleksyon otomatik gwosè blòk la.

TAMPON = { konte tanpon | @ buffercount_variable } - Defini kantite total tanpon I/O ki pral itilize pou operasyon backup la. Ou ka presize nenpòt valè nonb antye relatif pozitif, men yon gwo kantite tanpon ka lakòz yon erè nan memwa akòz twòp espas adrès vityèl nan pwosesis Sqlservr.exe.

Kantite total espas tanpon itilize yo detèmine pa fòmil sa a: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } espesifye pi gwo gwosè pake done, an byte, pou fè echanj ant SQL sèvè ak medya seri sovgad la. Yo sipòte miltip 65 bytes (536 KB) jiska 64 bytes (4 MB).

Mwen fè sèman - mwen te li sa a anvan, men li pa janm te rive m 'ki kantite nan yon enpak yo te kapab genyen sou pwodiktivite. Anplis, aparamman, mwen bezwen fè yon kalite "soti" ak admèt ke menm kounye a mwen pa byen konprann ki sa egzakteman yo ap fè. Mwen pwobableman bezwen li plis sou tanpon I/O ak travay ak yon kondwi difisil. Yon jou mwen pral fè sa, men pou kounye a mwen ka jis ekri yon script ki pral tcheke kijan valè sa yo afekte vitès la nan ki backup la pran.

Mwen te fè yon ti baz done, apeprè 10 GB nan gwosè, mete l sou SSD a, epi mete anyè a pou sovgad sou HDD la.

Mwen kreye yon tab tanporè pou sere rezilta yo (mwen pa genyen li tanporè, pou mwen ka fouye nan rezilta yo an plis detay, men ou deside pou tèt ou):

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

Prensip la nan script la se senp - bouk enbrike, chak nan yo ki chanje valè a nan yon paramèt, mete paramèt sa yo nan lòd la BACKUP, sove dènye dosye a ak istwa soti nan msdb.dbo.backupset, efase dosye a backup ak pwochen iterasyon an. . Depi done ekzekisyon backup yo pran nan backupset la, presizyon an se yon ti jan pèdi (pa gen okenn fraksyon nan segonn), men nou pral siviv sa a.

Premyèman, ou bezwen aktive xp_cmdshell pou efase sovgad (apresa pa bliye enfim li si ou pa bezwen li): 

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

Oke, aktyèlman:

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

Si ou toudenkou bezwen klarifikasyon sou sa k ap pase isit la, ekri nan kòmantè yo oswa PM. Pou kounye a, mwen pral sèlman di ou sou paramèt yo ke mwen mete nan BACKUP DATABASE.

Pou BLOCKSIZE nou gen yon lis "fèmen" nan valè, epi mwen pa t 'fè yon backup ak BLOCKSIZE < 4KB. MAXTRANSFERSIZE nenpòt nimewo ki se yon miltip 64KB - soti nan 64KB rive 4MB. Defo a sou sistèm mwen an se 1024KB, mwen te pran 512 - 1024 - 2048 - 4096.

Li te pi difisil ak BUFFERCOUNT - li kapab nenpòt nimewo pozitif, men lyen an di ki jan yo kalkile li nan BACKUP DATABASE e poukisa gwo valè yo danjere?. Li di tou ki jan yo ka jwenn enfòmasyon sou ki BUFFERCOUNT backup la aktyèlman fèt ak - pou mwen li nan 7. Pa te gen okenn pwen nan diminye li, ak limit la anwo te dekouvri eksperimantal - ak BUFFERCOUNT = 896 ak MAXTRANSFERSIZE = 4194304 backup la te tonbe ak yon erè (sou ki ekri nan lyen ki pi wo a):

Msg 3013, Nivo 16, Eta 1, Liy 7 BAZ DONNÈ BACKUP ap fini anòmalman.

Msg 701, Nivo 17, Eta 123, Liy 7 Pa gen ase memwa sistèm nan pisin resous 'default' pou fè rechèch sa a.

Pou konparezon, mwen pral premye montre rezilta yo nan kouri yon backup san yo pa espesifye nenpòt paramèt ditou:

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

Oke, backup ak backup:

Te trete 1070072 paj pou baz done 'bt', fichye 'bt' sou dosye 1.

Trete 2 paj pou baz done 'bt', fichye 'bt_log' sou dosye 1.

BACKUP DATABASE trete avèk siksè 1070074 paj nan 53.171 segonn (157.227 MB/sec).

Script nan tèt li, tès paramèt yo, te travay nan yon koup la èdtan, tout mezi yo te nan google calcul. Ak isit la se yon seleksyon nan rezilta ak twa fwa yo ekzekisyon pi bon (mwen te eseye fè yon graf bèl, men nan pòs la mwen pral oblije fè ak yon tab, ak nan kòmantè yo @mixsture te ajoute grafik trè fre).

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 sèvè: BACKUP sou estewoyid

Atansyon, yon nòt trè enpòtan nan @mixsture nan kòmantè:

Nou ka di avèk konfyans ke relasyon ki genyen ant paramèt yo ak vitès backup nan seri sa yo nan valè se o aza, pa gen okenn modèl. Men, deplase lwen paramèt yo bati-an evidamman te gen yon bon efè sou rezilta a

Moun sa yo. Se sèlman nan jere paramèt BACKUP estanda yo te gen yon pwogrè 2-pliye nan tan retire backup: 26 segonn, kont 53 nan kòmansman an. Sa pa mal, pa vre? Men, nou bezwen wè sa k ap pase ak restorasyon an. E si kounye a li pran 4 fwa ankò pou refè?

Premyèman, ann mezire konbyen tan li pran pou retabli yon backup ak paramèt default:

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

Oke, ou menm ou konnen ke, fason yo la, ranplase se pa ranplase, rekiperasyon se pa rekiperasyon. Apre sa, mwen fè li tankou sa a:

Te trete 1070072 paj pou baz done 'bt', fichye 'bt' sou dosye 1.

Trete 2 paj pou baz done 'bt', fichye 'bt_log' sou dosye 1.

RESTORE DATABASE trete avèk siksè 1070074 paj nan 40.752 segonn (205.141 MB/sec).

Koulye a, mwen pral eseye retabli sovgad yo te pran ak chanje BLOCKSIZE, BUFFERCOUNT ak MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE trete avèk siksè 1070074 paj nan 32.283 segonn (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE trete avèk siksè 1070074 paj nan 32.682 segonn (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE trete avèk siksè 1070074 paj nan 32.091 segonn (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE trete avèk siksè 1070074 paj nan 32.401 segonn (258.015 MB/sec).

Deklarasyon RESTORE DATABASE la pa chanje pandan rekiperasyon an; paramèt sa yo pa espesifye nan li; SQL sèvè li menm detèmine yo nan backup la. Epi li klè ke menm ak rekiperasyon ka gen yon benefis - prèske 20% pi vit (Yo dwe onèt, mwen pa t 'pase anpil tan sou rekiperasyon, mwen kouri atravè plizyè nan sovgad "pi rapid" yo epi asire w ke pa gen okenn deteryorasyon.).

Jis nan ka, kite m 'klarifye ke sa yo se pa kèk paramèt ki pi bon pou tout moun. Ou ka sèlman jwenn paramèt yo pi bon pou tèt ou lè w teste. Mwen te resevwa rezilta sa yo, ou pral jwenn diferan. Men, ou wè ke ou ka "melodi" sovgad ou yo epi yo ka aktyèlman fòme ak deplwaye pi vit.

Mwen rekòmande tou pou w li dokiman an antye, paske ka gen nuans espesifik nan sistèm ou an.

Depi mwen te kòmanse ekri sou sovgad, mwen vle imedyatman ekri sou yon lòt "optimize", ki pi komen pase paramèt "akor" (jan mwen konprann, li se itilize pa omwen kèk sèvis piblik backup, petèt ansanm ak paramèt yo. dekri pi bonè), men li poko dekri sou Habré tou.

Si nou gade dezyèm liy nan dokiman an, dwa anba BACKUP DATABASE, la nou wè:

TO <backup_device> [ ,...n ]

Kisa ou panse ki pral rive si ou presize plizyè backup_devices? Sentaks la pèmèt li. Ak yon bagay trè enteresan pral rive - backup la pral tou senpleman "gaye" sou plizyè aparèy. Moun sa yo. chak "aparèy" endividyèlman pral initil, pèdi youn, pèdi backup la tout antye. Men, ki jan sa yo smearing pral afekte vitès backup?

Ann eseye fè yon backup sou de "aparèy" ki sitiye kòt a kòt nan katab la menm:

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

Papa lemonn, poukisa sa ap fèt?

Te trete 1070072 paj pou baz done 'bt', fichye 'bt' sou dosye 1.

Traite 2 paj pou baz done 'bt', dosye 'bt'log' sou dosye 1.

BACKUP DATABASE trete avèk siksè 1070074 paj nan 40.092 segonn (208.519 MB/sec).

Èske backup la vin 25% pi vit jis soti nan ble a? E si nou ajoute yon koup plis aparèy?

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

BACKUP DATABASE trete avèk siksè 1070074 paj nan 34.234 segonn (244.200 MB/sec).

Nan total, benefis la se apeprè 35% nan tan an nan pran yon backup sèlman akòz lefèt ke se backup la ekri nan 4 dosye sou yon sèl disk nan yon fwa. Mwen tcheke yon nimewo ki pi gwo - pa gen okenn benefis sou laptop mwen an, parfètman - 4 aparèy. Pou ou - mwen pa konnen, ou bezwen tcheke. Oke, nan chemen an, si ou gen aparèy sa yo - sa yo se reyèlman diferan disk, felisitasyon, benefis la ta dwe menm plis enpòtan.

Koulye a, ann pale sou ki jan yo retabli bonè sa a. Pou fè sa, w ap oblije chanje lòd rekiperasyon an epi lis tout aparèy:

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 trete avèk siksè 1070074 paj nan 38.027 segonn (219.842 MB/sec).

Yon ti kras pi vit, men yon kote fèmen, pa enpòtan. An jeneral, yo retire backup la pi vit, men restore nan menm fason an - siksè? Kòm pou mwen, li se byen yon siksè. Sa a se yon bagay enpòtan, Se konsa, mwen repete - si ou si ou pèdi omwen youn nan dosye sa yo, ou pèdi tout backup la.

Si w gade nan jounal la enfòmasyon backup ki parèt lè l sèvi avèk Trace Flags 3213 ak 3605, w ap remake ke lè w ap fè bak sou plizyè aparèy, omwen kantite BUFFERCOUNT ogmante. Pwobableman, ou ka eseye chwazi paramèt pi bon pou BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, men mwen pa t reyisi touswit, e mwen te twò parese pou fè tès sa yo ankò, men pou yon kantite diferan nan dosye. Epi li se yon wont sou wou yo. Si ou vle òganize tès sa yo lakay ou, li pa difisil pou refè script la.

Finalman, ann pale sou pri. Si yo retire backup la nan paralèl ak travay itilizatè yo, ou bezwen pran yon apwòch trè responsab nan tès la, paske si yo retire backup la pi vit, disk yo ap tension plis, chaj la sou processeur a ogmante (ou toujou gen konpresyon. li sou vole), epi kòmsadwa, repons jeneral sistèm nan diminye.

Jis plèzantri, men mwen konprann parfe byen ke mwen pa t 'fè okenn revelasyon. Sa ki ekri pi wo a se tou senpleman yon demonstrasyon sou fason ou ka chwazi paramèt yo pi bon pou pran sovgad.

Sonje ke tout sa ou fè yo fè nan pwòp danje ou ak risk. Tcheke sovgad ou yo epi pa bliye sou DBCC CHECKDB.

Sous: www.habr.com

Add nouvo kòmantè