Waart! Waart! True, dëst ass keen aneren Artikel iwwer Zorte vu SQL Server Backups. Ech wäert net emol iwwer d'Ënnerscheeder tëscht Erhuelungsmodeller schwätzen a wéi Dir mat engem iwwerwuessene Logbuch ëmgeet.
Vläicht (just vläicht), nodeems Dir dëse Post gelies hutt, kënnt Dir sécher sinn datt de Backup, deen Dir mat Standardmëttelen ofgeschaaft hutt, muer Nuecht geläscht gëtt, gutt, 1.5 Mol méi séier. An nëmmen wéinst der Tatsaach, datt Dir e bësse méi BACKUP DATABASE Parameteren benotzt.
Wann den Inhalt vum Post fir Iech offensichtlech war, et deet mir leed. Ech hunn alles gelies wat Google fir den Ausdrock "habr sql Server Backup" kritt huet, an net an engem eenzegen Artikel hunn ech erwähnt datt d'Backupzäit iergendwéi mat Parameteren beaflosst ka ginn.
Ech wäert Iech direkt op de Kommentar vum Alexander Gladchenko opmierksam maachen ():
Verännert ni d'BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE Parameteren an der Produktioun. Si sinn nëmme gemaach fir esou Artikelen ze schreiwen. An der Praxis wäert Dir Erënnerungsproblemer a kenger Zäit lass ginn.
Et wier natierlech cool dee schlausten ze sinn an exklusiven Inhalt ze posten, awer leider ass dat net de Fall. Et gi souwuel engleschsproocheg wéi och russeschsproocheg Artikelen / Posts (ech sinn ëmmer duercherneen wéi ech se richteg nennen) fir dëst Thema gewidmet. Hei sinn e puer vun deenen, déi ech begéint hunn: , , .
Also, fir unzefänken, wäert ech e liicht ofgeschniddene BACKUP Syntax befestigen (iwwregens, ech hunn uewen iwwer BACKUP DATABASE geschriwwen, awer all dat gëllt fir béid Transaktiounslog-Backup an Differential-Backup, awer vläicht mat engem manner offensichtlechen Effekt):
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 }
<...><...> - et heescht datt et eppes do war, awer ech hunn et ewechgeholl well et elo net zum Thema relevant ass.
Wéi maacht Dir normalerweis e Backup? Wéi "léiere" se wéi Backups a Milliarden Artikelen huelen? Am Allgemengen, wann ech en eemolege Backup vun enger net ganz grousser Datebank muss maachen, schreiwen ech automatesch sou eppes:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееAn, am Allgemengen, wahrscheinlech 75-90% vun alle Parameteren, déi normalerweis an Artikelen iwwer Backups ernimmt ginn, sinn hei opgelëscht. Gutt, et gëtt och INIT, SKIP. Hutt Dir MSDN besicht? Hutt Dir gesinn, datt et Optiounen fir een an en halleft Schiirme sinn? Ech hunn och gesinn ...
Dir hutt wahrscheinlech scho gemierkt datt mir weider iwwer déi dräi Parameter schwätzen, déi am éischte Block vum Code bliwwen sinn - BLOCKSIZE, BUFFERCOUNT a MAXTRANSFERSIZE. Hei sinn hir Beschreiwunge vu MSDN:
BLOCKSIZE = { blocksize | @ blocksize_variable } - weist déi kierperlech Blockgréisst a Bytes un. Ënnerstëtzt Gréissten sinn 512, 1024, 2048, 4096, 8192, 16, 384, a 32 Bytes (768 KB). De Standardwäert ass 65 fir Bandapparaten an 536 fir aner Apparater. Typesch ass dëse Parameter net néideg, well d'BACKUP Ausso automatesch déi entspriechend Blockgréisst fir den Apparat auswielt. D'Astellung vun der Blockgréisst iwwerschreift explizit déi automatesch Blockgréisst Auswiel.
BUFFERCOUNT = { bufferzuel | @ buffercount_variable } - Definéiert d'Gesamtzuel vun I/O-Puffer, déi fir d'Backupoperatioun benotzt ginn. Dir kënnt all positiv ganz Zuel Wäert uginn, mä eng grouss Zuel vu Puffer kann en Out-of-Memory Feeler verursaache wéinst exzessiv virtuell Adress Plaz am Sqlservr.exe Prozess.
De Gesamtbetrag u Raum benotzt vu Puffer gëtt vun der folgender Formel festgeluegt:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } spezifizéiert déi gréisst Datepaketgréisst, a Bytes, fir tëscht SQL Server an de Backup-Set Medien auszetauschen. Multiple vu 65 Bytes (536 KB) bis zu 64 Bytes (4 MB) ginn ënnerstëtzt.
Ech schwieren - ech hunn dëst virdru gelies, awer et ass mir ni opgetrueden wéi en Impakt se op d'Produktivitéit hätten. Ausserdeem, anscheinend, muss ech eng Zort "erauskommen" maachen an zouginn datt ech och elo net ganz verstinn wat se genau maachen. Ech muss wahrscheinlech méi iwwer gebufferten I / O liesen a mat enger Festplack schaffen. Irgendwann wäert ech dat maachen, awer fir de Moment kann ech just e Skript schreiwen dat iwwerpréift wéi dës Wäerter d'Geschwindegkeet beaflossen mat där de Backup geholl gëtt.
Ech hunn eng kleng Datebank gemaach, ongeféier 10 GB an der Gréisst, hunn se op der SSD gesat an de Verzeechnes fir Backups op der HDD gesat.
Ech erstellen eng temporär Tabell fir d'Resultater ze späicheren (ech hunn et net temporär, also kann ech méi detailléiert an d'Resultater gräifen, awer Dir entscheet fir Iech selwer):
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
);De Prinzip vum Skript ass einfach - nestéiert Loops, déi jidderee de Wäert vun engem Parameter ännert, dës Parameteren an de BACKUP Kommando setzen, de leschte Rekord mat der Geschicht vu msdb.dbo.backupset späicheren, d'Backupdatei läschen an déi nächst Iteratioun . Zënter datt d'Backup-Ausféierungsdaten aus dem Backupset geholl ginn, ass d'Genauegkeet e bësse verluer (et gi keng Fraktiounen vu Sekonnen), awer mir wäerten dëst iwwerliewen.
Als éischt musst Dir xp_cmdshell aktivéieren fir Backups ze läschen (da vergiesst net ze deaktivéieren wann Dir et net braucht):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOGutt, eigentlech:
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;
ENDWann Dir op eemol Klärung braucht iwwer wat hei geschitt, schreift an de Kommentarer oder PM. Fir de Moment wäert ech Iech nëmmen iwwer d'Parameteren soen, déi ech an de BACKUP DATABASE setzen.
Fir BLOCKSIZE hu mir eng "zougemaach" Lëscht vu Wäerter, an ech hunn kee Backup gemaach mat BLOCKSIZE <4KB. MAXTRANSFERSIZE all Zuel datt e Multiple vun 64KB ass - aus 64KB ze 4MB. De Standard op mengem System ass 1024KB, ech hunn 512 - 1024 - 2048 - 4096 geholl.
Et war méi schwéier mat BUFFERCOUNT - et kann all positiv Zuel ginn, mä de Link seet . Et seet och wéi Dir Informatiounen kritt iwwer mat wéi engem BUFFERCOUNT de Backup tatsächlech gemaach gëtt - fir mech ass et 7. Et war kee Sënn et ze reduzéieren, an déi iewescht Limit gouf experimentell entdeckt - mat BUFFERCOUNT = 896 a MAXTRANSFERSIZE = 4194304 ass de Backup gefall mat e Feeler (iwwer deen am Link hei uewen geschriwwen ass):
Msg 3013, Niveau 16, Staat 1, Linn 7 BACKUP DATABASE gëtt abnormal ofgeschloss.
Msg 701, Niveau 17, Staat 123, Linn 7 Et gëtt net genuch Systemspeicher am Ressourcepool 'Standard' fir dës Ufro auszeféieren.
Zum Verglach weisen ech als éischt d'Resultater vun engem Backup auszeféieren ouni iwwerhaapt Parameteren ze spezifizéieren:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Gutt, Backup a Backup:
Veraarbechtt 1070072 Säiten fir Datebank 'bt', Datei 'bt' op Datei 1.
Veraarbecht 2 Säiten fir Datebank 'bt', Datei 'bt_log' op Datei 1.
BACKUP DATABASE huet erfollegräich 1070074 Säiten an 53.171 Sekonnen (157.227 MB / sec) veraarbecht.
D'Skript selwer, d'Parameteren testen, huet an e puer Stonnen geschafft, all Miessunge waren an . An hei ass eng Auswiel u Resultater mat den dräi beschten Ausféierungszäiten (ech hu probéiert eng flott Grafik ze maachen, awer am Post muss ech mech mat enger Tabell maachen, an an de Kommentarer derbäigesat ).
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;
Opgepasst, eng ganz wichteg Note aus aus :
Mir kënnen zouversiichtlech soen datt d'Relatioun tëscht de Parameteren a Backupgeschwindegkeet bannent dëse Wäerterberäicher zoufälleg ass, et gëtt kee Muster. Awer d'Bewegung vun den agebaute Parameteren huet selbstverständlech e gudden Effekt op d'Resultat
Déi. Nëmmen duerch d'Gestioun vun de Standard BACKUP Parameteren war en 2-fache Gewënn an der Backupsatellit Zäit: 26 Sekonnen, versus 53 am Ufank. Dat ass net schlecht, oder? Mee mir musse kucken wat mat der Restauratioun geschitt. Wat wann et elo 4 Mol méi laang dauert fir ze recuperéieren?
Als éischt, loosst eis moossen wéi laang et dauert fir e Backup mat Standardastellungen ze restauréieren:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Gutt, Dir selwer wësst dat, d'Weeër sinn do, Ersatz ass net ersat, Erhuelung ass keng Erhuelung. An ech maachen et esou:
Veraarbechtt 1070072 Säiten fir Datebank 'bt', Datei 'bt' op Datei 1.
Veraarbecht 2 Säiten fir Datebank 'bt', Datei 'bt_log' op Datei 1.
D'RESTORE DATABASE huet erfollegräich 1070074 Säiten an 40.752 Sekonnen (205.141 MB / sec) veraarbecht.
Elo probéieren ech Backups ze restauréieren, déi mat geännerten BLOCKSIZE, BUFFERCOUNT a MAXTRANSFERSIZE geholl goufen.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304D'RESTORE DATABASE huet erfollegräich 1070074 Säiten an 32.283 Sekonnen (258.958 MB / sec) veraarbecht.
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304D'RESTORE DATABASE huet erfollegräich 1070074 Säiten an 32.682 Sekonnen (255.796 MB / sec) veraarbecht.
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152D'RESTORE DATABASE huet erfollegräich 1070074 Säiten an 32.091 Sekonnen (260.507 MB / sec) veraarbecht.
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304D'RESTORE DATABASE huet erfollegräich 1070074 Säiten an 32.401 Sekonnen (258.015 MB / sec) veraarbecht.
D'RESTORE DATABASE Ausso ännert sech net während der Erhuelung; dës Parameteren ginn net an et spezifizéiert; SQL Server selwer bestëmmt se vum Backup. An et ass kloer datt och mat Erhuelung e Gewënn ka ginn - bal 20% méi séier (Fir éierlech ze sinn, hunn ech net vill Zäit fir d'Erhuelung verbruecht, ech sinn duerch e puer vun de "schnellsten" Backupe gelaf a gesuergt datt et keng Verschlechterung gouf).
Just am Fall, loosst mech klären datt dëst net e puer Parameteren sinn déi fir jiddereen optimal sinn. Dir kënnt nëmmen déi optimal Parameter fir Iech selwer kréien andeems Dir testen. Ech hunn dës Resultater kritt, Dir kritt verschidde. Awer Dir gesitt datt Dir Är Backups "ofstëmmen" kënnt a si kënnen tatsächlech méi séier formen an ofsetzen.
Ech recommandéieren och staark datt Dir d'Dokumentatioun a senger Ganzheet liest, well et kann Nuancen spezifesch fir Äre System sinn.
Zënter datt ech ugefaang hunn iwwer Backups ze schreiwen, wëll ech direkt iwwer eng méi "Optimisatioun" schreiwen, déi méi heefeg ass wéi "tuning" Parameteren (souwäit ech verstinn, gëtt et op d'mannst e puer Backup-Utilities benotzt, vläicht zesumme mat de Parameteren virdrun beschriwwen), awer et ass och nach net op Habré beschriwwe ginn.
Wa mir déi zweet Zeil an der Dokumentatioun kucken, direkt ënner BACKUP DATABASE, da gesi mir:
TO <backup_device> [ ,...n ]Wat mengt Dir wäert geschéien wann Dir e puer Backup_Devices spezifizéiert? D'Syntax erlaabt et. An eng ganz interessant Saach wäert geschéien - de Backup gëtt einfach iwwer verschidden Apparater "verbreet". Déi. all "Gerät" individuell wäert nëtzlos sinn, verluer eent, verluer de ganze Backupsatellit. Awer wéi wäert esou Schmieren d'Backupgeschwindegkeet beaflossen?
Loosst eis probéieren e Backup op zwee "Geräter" ze maachen, déi niewentenee am selwechten Dossier sinn:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Pappen vun der Welt, firwat gëtt dat gemaach?
Veraarbechtt 1070072 Säiten fir Datebank 'bt', Datei 'bt' op Datei 1.
2 Säite fir d'Datebank 'bt' veraarbecht, de Fichier 'bt'log' op Datei 1.
BACKUP DATABASE huet erfollegräich 1070074 Säiten an 40.092 Sekonnen (208.519 MB / sec) veraarbecht.
Huet de Backup 25% méi séier ginn just aus dem blo? Wat wa mir e puer méi Apparater derbäi?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE huet erfollegräich 1070074 Säiten an 34.234 Sekonnen (244.200 MB / sec) veraarbecht.
Am Ganzen ass de Gewënn ongeféier 35% vun der Zäit fir e Backup ze huelen nëmmen wéinst der Tatsaach datt de Backup op 4 Dateien op enger Disk gläichzäiteg geschriwwe gëtt. Ech hunn eng méi grouss Zuel gepréift - et gëtt kee Gewënn op mengem Laptop, optimal - 4 Apparater. Fir Iech - ech weess et net, Dir musst kontrolléieren. Gutt, iwwregens, wann Dir dës Geräter hutt - dat si wierklech verschidden Disken, Gratulatioun, de Gewënn sollt nach méi bedeitend sinn.
Elo schwätze mer iwwer wéi dëst Gléck ze restauréieren. Fir dëst ze maachen, musst Dir den Erhuelungskommando änneren an all Geräter oplëschten:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;D'RESTORE DATABASE huet erfollegräich 1070074 Säiten an 38.027 Sekonnen (219.842 MB / sec) veraarbecht.
E bësse méi séier, awer iergendwou no, net bedeitend. Am Allgemengen gëtt de Backup méi séier geläscht a restauréiert op déiselwecht Manéier - Erfolleg? Wat mech ugeet, ass et e ganz Erfolleg. Dëst ass wichteg, Also ech widderhuelen - wann Dir wann Dir op d'mannst ee vun dëse Fichieren verléieren, Dir verléiert de ganze Backupsatellit.
Wann Dir am Logbuch op d'Backupinformatioun kuckt, déi mat Trace Flags 3213 an 3605 ugewise gëtt, mierkt Dir datt wann Dir op e puer Apparater backt, op d'mannst d'Zuel vu BUFFERCOUNT eropgeet. Wahrscheinlech kënnt Dir probéieren méi optimal Parameteren fir BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE ze wielen, awer ech hunn net direkt gelongen, an ech war ze faul fir esou Tester erëm ze maachen, awer fir eng aner Zuel vu Dateien. An et ass schued iwwer d'Rieder. Wann Dir esou Tester doheem wëllt organiséieren, ass et net schwéier d'Skript nei ze maachen.
Schlussendlech schwätze mer iwwer de Präis. Wann de Backup parallel mat der Aarbecht vun de Benotzer geläscht gëtt, musst Dir eng ganz verantwortlech Approche zum Test huelen, well wann de Backup méi séier geläscht gëtt, ginn d'Disks méi gespannt, d'Laascht op de Prozessor erhéicht (Dir musst nach ëmmer kompriméieren et op der Flucht), an deementspriechend reduzéiert d'Gesamtreaktiounsfäegkeet vum System.
Just de Geck, awer ech verstinn ganz gutt datt ech keng Offenbarunge gemaach hunn. Wat hei uewen geschriwwen ass ass einfach eng Demonstratioun wéi Dir déi optimal Parameter fir Backups auswielen kënnt.
Denkt drun datt alles wat Dir maacht op Är eegen Gefor a Risiko gemaach gëtt. Préift Är Backups a vergiesst net iwwer DBCC CHECKDB.
Source: will.com