Andraso! Andraso! Marina fa tsy lahatsoratra hafa momba ny karazana backup SQL Server ity. Tsy hiresaka momba ny fahasamihafana misy eo amin'ny modely fanarenana sy ny fomba fiatrehana ny hazo be loatra aho.
Angamba (angamba fotsiny), rehefa avy mamaky ity lahatsoratra ity ianao dia ho azonao antoka fa ny backup izay nesorina aminao amin'ny fampiasana fomba mahazatra dia hesorina rahampitso alina, tsara, in-1.5 haingana kokoa. Ary noho ny fampiasanao masontsivana DATABASE BACKUP bebe kokoa.
Raha mazava aminao ny votoatin'ny lahatsoratra dia miala tsiny aho. Namaky izay rehetra azon'i Google aho tamin'ny fehezan-teny hoe "habr sql server backup", ary tsy nisy lahatsoratra iray nahitako ny filazana fa ny fotoana backup dia mety hisy fiantraikany amin'ny fampiasana masontsivana.
Hisarika ny sainao avy hatrany amin'ny fanehoan-kevitr'i Alexander Gladchenko aho ():
Aza ovaina na oviana na oviana ny masontsivana BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE amin'ny famokarana. Natao ho an'ny fanoratana lahatsoratra toy izany ihany izy ireo. Amin'ny fampiharana, dia hanaisotra olana amin'ny fitadidiana ianao ao anatin'ny fotoana fohy.
Mazava ho azy fa ho mahafinaritra ny ho votoaty marani-tsaina indrindra sy mamoaka votoaty manokana, saingy, indrisy, tsy izany no izy. Misy lahatsoratra/lahatsoratra amin'ny teny anglisy sy amin'ny teny rosiana (fanina foana aho hoe inona no tokony hiantsoana azy ireo) natokana ho an'ity lohahevitra ity. Ireto ny sasany amin'ireo hitako: , , .
Noho izany, hanomboka amin'ny, hametaka syntax BACKUP kely avy amin'ny (Etsy ankilany, nanoratra momba ny DATABASE BACKUP aho, fa izany rehetra izany dia mihatra amin'ny backup log transactions sy backup backup, fa angamba misy fiantraikany tsy dia mazava loatra):
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 }
<...><...> - midika izany fa nisy zavatra tao, saingy nesoriko satria tsy mifanaraka amin'ny lohahevitra.
Ahoana no matetika maka backup? Ahoana no "mampianaran'izy ireo" ny fomba fanaovana backup amin'ny lahatsoratra an'arivony tapitrisa? Amin'ny ankapobeny, raha mila manao backup indray mandeha amin'ny angon-drakitra tsy dia lehibe loatra aho dia hanoratra ho azy toy izao:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееAry, amin'ny ankapobeny, ny 75-90% amin'ny masontsivana rehetra izay matetika voalaza ao amin'ny lahatsoratra momba ny backup dia voatanisa eto. Eny ary, misy ihany koa ny INIT, SKIP. Efa nitsidika MSDN ve ianao? Hitanao ve fa misy safidy ho an'ny efijery iray sy tapany? Hitako koa...
Efa tsapanao angamba fa mbola hiresaka momba ireo masontsivana telo sisa tavela ao amin'ny bloc code voalohany isika - BLOCKSIZE, BUFFERCOUNT ary MAXTRANSFERSIZE. Ireto ny famaritana azy ireo avy amin'ny MSDN:
BLOCKSIZE = { haben'ny sakana | @ blocksize_variable } - manondro ny haben'ny sakana ara-batana amin'ny bytes. Ny habe tohana dia 512, 1024, 2048, 4096, 8192, 16, 384, ary 32 octet (768 Kio). Ny sandan'ny default dia 65 ho an'ny fitaovana kasety ary 536 ho an'ny fitaovana hafa. Amin'ny ankapobeny dia tsy ilaina io mari-pamantarana io satria ny fanambarana BACKUP dia mifidy ho azy ny haben'ny sakana mety ho an'ny fitaovana. Ny fametrahana ny haben'ny sakana dia manafoana mazava ny fisafidianana ny haben'ny sakana.
BUFFERCOUNT = { buffercount | @ buffercount_variable } - Mamaritra ny totalin'ny buffer I/O izay hampiasaina amin'ny asa backup. Azonao atao ny mamaritra ny sanda integer tsara, fa ny buffers marobe dia mety hiteraka hadisoana tsy misy fitadidiana noho ny habaka adiresy virtoaly be loatra amin'ny dingana Sqlservr.exe.
Ny totalin'ny habaka ampiasain'ny buffers dia faritana amin'ny alalan'ity formula manaraka ity:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } dia mamaritra ny haben'ny fonosana angona lehibe indrindra, amin'ny bytes, hifanakalozana eo amin'ny SQL Server sy ny media backup. Ampitomboina 65 octet (536 Kio) ka hatramin'ny 64 bytes (4 MB) no tohana.
Mianiana aho - Efa namaky an'ity aho taloha, saingy tsy tonga saina mihitsy hoe hatraiza ny fiantraikan'izy ireo amin'ny famokarana. Ankoatr'izay, toa mila manao karazana "mivoaka" aho ary miaiky fa na dia izao aza dia tsy azoko tsara hoe inona marina no ataon'izy ireo. Mety mila mamaky bebe kokoa momba ny buffered I/O sy miasa amin'ny kapila mafy aho. Indray andro any dia hanao izany aho, fa amin'izao fotoana izao dia afaka manoratra script fotsiny aho izay hanamarina ny fiantraikan'ireo soatoavina ireo amin'ny hafainganam-pandehan'ny backup.
Nanao tahiry kely aho, manodidina ny 10 GB ny habeny, nametraka izany tamin'ny SSD, ary nametraka ny lahatahiry ho an'ny backups amin'ny HDD.
Mamorona latabatra vonjimaika aho mba hitahirizana ny valiny (tsy manana izany aho, mba hahafantarako ny valiny amin'ny antsipiriany bebe kokoa, fa ianao no manapa-kevitra ho an'ny tenanao):
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
);Ny fitsipiky ny script dia tsotra - nested tadivavarana, izay samy manova ny sandan'ny paramètre iray, ampidiro ao amin'ny baiko BACKUP ireo masontsivana ireo, tehirizo ny rakitsoratra farany miaraka amin'ny tantara avy amin'ny msdb.dbo.backupset, esory ny rakitra backup ary ny famerenana manaraka . Satria nalaina tao amin'ny backupset ny angon-drakitra famonoana backup, dia somary very ny fahamarinany (tsy misy ampahany segondra), fa ho tafavoaka velona amin'izany isika.
Voalohany dia mila mamela ny xp_cmdshell hamafa ny backups ianao (aza adino ny manafoana izany raha tsy mila izany ianao):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOEny, raha ny marina:
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;
ENDRaha mila fanazavana tampoka momba ny zava-mitranga eto ianao dia manorata ao amin'ny commentaire na PM. Amin'izao fotoana izao, ny masontsivana napetrako ao amin'ny BACKUP DATABASE ihany no holazaiko aminao.
Ho an'ny BLOCKSIZE dia manana lisitry ny soatoavina "nakatona", ary tsy nanao backup aho tamin'ny BLOCKSIZE < 4KB. MAXTRANSFERSIZE izay isa maromaro misy 64KB - manomboka amin'ny 64KB ka hatramin'ny 4MB. Ny default amin'ny rafitra dia 1024KB, naka 512 - 1024 - 2048 - 4096 aho.
Sarotra kokoa izany tamin'ny BUFFERCOUNT - mety ho isa tsara izany, saingy hoy ny rohy . Lazainy ihany koa ny fomba hahazoana vaovao momba ny BUFFERCOUNT tena misy ny backup - ho ahy dia 7. Tsy nisy dikany ny fampihenana azy, ary ny fetra ambony dia hita tamin'ny fanandramana - miaraka amin'ny BUFFERCOUNT = 896 ary MAXTRANSFESIZE = 4194304 dia nianjera ny backup hadisoana (momba ny voasoratra ao amin'ny rohy etsy ambony):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE dia tapitra tsy ara-dalàna.
Msg 701, Level 17, State 123, Andalana 7 Tsy ampy ny fitadidiana rafitra ao amin'ny dobo loharanon-karena 'default' hampandehanana ity fangatahana ity.
Ho fampitahana, hasehoko aloha ny valin'ny fampandehanana backup tsy misy mason-tsivana mihitsy:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Eny, backup sy backup:
Namboarina pejy 1070072 ho an'ny tahiry 'bt', rakitra 'bt' amin'ny rakitra 1.
Pejy 2 nokarakaraina ho an'ny tahiry 'bt', rakitra 'bt_log' amin'ny rakitra 1.
BACKUP DATABASE dia nahomby tamin'ny fanodinana pejy 1070074 tao anatin'ny 53.171 segondra (157.227 MB/s).
Ny script mihitsy, nanandrana ny masontsivana, dia niasa tao anatin'ny adiny roa, tao ny fandrefesana rehetra . Ary ity misy fifantenana valiny miaraka amin'ny fotoana famonoana tsara indrindra telo (Niezaka nanao grafika mahafinaritra aho, fa ao amin'ny lahatsoratra dia tsy maintsy manao latabatra aho, ary ao amin'ny fanehoan-kevitra Hoy izy nanampy ).
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;
Attention, fanamarihana tena manan-danja avy amin'ny avy amin'ny :
Afaka milaza amim-pahatokiana isika fa ny fifandraisana misy eo amin'ny masontsivana sy ny hafainganam-pandehan'ny backup ao anatin'ireo soatoavina ireo dia kisendrasendra, tsy misy lamina. Saingy ny fialana amin'ireo mari-pamantarana naorina dia mazava ho azy fa nisy fiantraikany tsara tamin'ny vokatra
Ireo. Tamin'ny alalan'ny fitantanana ny masontsivana BACKUP mahazatra ihany no nahazo tombony in-2 tamin'ny fotoana fanesorana backup: 26 segondra, mifanohitra amin'ny 53 tany am-piandohana. Tsy ratsy izany, sa tsy izany? Mila jerena anefa izay mitranga amin’ny famerenana amin’ny laoniny. Ahoana raha mila in-4 izao vao sitrana?
Voalohany, andeha horefesina hoe hafiriana no hamerenana ny backup miaraka amin'ny fanovana default:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Eny ary, fantatrao fa, misy ny fomba, ny fanoloana dia tsy manolo, ny fanarenana dia tsy fanarenana. Ary manao toy izao aho:
Namboarina pejy 1070072 ho an'ny tahiry 'bt', rakitra 'bt' amin'ny rakitra 1.
Pejy 2 nokarakaraina ho an'ny tahiry 'bt', rakitra 'bt_log' amin'ny rakitra 1.
Ny RESTORE DATABASE dia nahomby tamin'ny fanodinana pejy 1070074 tao anatin'ny 40.752 segondra (205.141 MB/s).
Ankehitriny aho dia hiezaka hamerina ny backup nalaina tamin'ny BLOCKSIZE, BUFFERCOUNT ary MAXTRANSFERSIZE niova.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304Ny RESTORE DATABASE dia nahomby tamin'ny fanodinana pejy 1070074 tao anatin'ny 32.283 segondra (258.958 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304Ny RESTORE DATABASE dia nahomby tamin'ny fanodinana pejy 1070074 tao anatin'ny 32.682 segondra (255.796 MB/s).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152Ny RESTORE DATABASE dia nahomby tamin'ny fanodinana pejy 1070074 tao anatin'ny 32.091 segondra (260.507 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304Ny RESTORE DATABASE dia nahomby tamin'ny fanodinana pejy 1070074 tao anatin'ny 32.401 segondra (258.015 MB/s).
Ny fanambarana RESTORE DATABASE dia tsy miova mandritra ny fanarenana; tsy voafaritra ao anatin'izany ireo mari-pamantarana ireo; Ny SQL Server mihitsy no mamaritra azy ireo amin'ny backup. Ary mazava ho azy fa na dia amin'ny fanarenana aza dia mety hisy tombony - efa ho 20% haingana kokoa (Raha ny marina, tsy nandany fotoana firy tamin'ny fanarenana aho, nihazakazaka tamin'ireo backups "faingana indrindra" aho ary nanao izay hahazoana antoka fa tsy misy fahasimbana.).
Raha sanatria, mamelà ahy hanazava fa tsy ireo paramètres izay mety indrindra ho an'ny rehetra. Afaka mahazo ny masontsivana tsara indrindra ho anao ianao amin'ny alalan'ny fitsapana. Nahazo ireto vokatra ireto aho, hafa no ho azonao. Saingy hitanao fa azonao atao ny "manitsy" ny backups ary afaka mamorona sy miparitaka haingana kokoa izy ireo.
Manoro hevitra mafy koa aho mba hamaky manontolo ny antontan-taratasy, satria mety misy ny nuance manokana amin'ny rafitrao.
Hatramin'ny nanombohako nanoratra momba ny backups dia te hanoratra avy hatrany momba ny "optimization" iray hafa aho, izay mahazatra kokoa noho ny mari-pamantarana "tuning" (raha ny fahazoako azy dia ampiasain'ny fitaovana backup sasany, angamba miaraka amin'ny masontsivana. nofaritana teo aloha), saingy mbola tsy voalaza ao amin'ny Habré ihany koa.
Raha mijery ny andalana faharoa ao amin'ny antontan-taratasy isika, eo ambanin'ny DATABASE BACKUP, dia hitantsika:
TO <backup_device> [ ,...n ]Inona araka ny eritreritrao no hitranga raha manondro backup_devices maromaro ianao? Ny syntax dia mamela izany. Ary hisy zavatra tena mahaliana hitranga - ny backup dia "hiparitaka" amin'ny fitaovana maromaro. Ireo. Ny "fitaovana" tsirairay dia tsy hisy ilàna azy, very ny iray, very ny backup manontolo. Ahoana anefa no fiantraikan'ny fanosoram-potaka toy izany amin'ny hafainganam-pandehan'ny backup?
Andeha isika hanandrana hanao backup amin'ny "fitaovana" roa izay mifanila amin'ny lahatahiry iray ihany:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Ry Rain'izao tontolo izao, nahoana no atao izany?
Namboarina pejy 1070072 ho an'ny tahiry 'bt', rakitra 'bt' amin'ny rakitra 1.
Pejy 2 nokarakaraina ho an'ny tahiry 'bt', rakitra 'bt'log' amin'ny rakitra 1.
BACKUP DATABASE dia nahomby tamin'ny fanodinana pejy 1070074 tao anatin'ny 40.092 segondra (208.519 MB/s).
Lasa 25% haingana kokoa ve ny backup? Ahoana raha manampy fitaovana roa hafa isika?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE dia nahomby tamin'ny fanodinana pejy 1070074 tao anatin'ny 34.234 segondra (244.200 MB/s).
Amin'ny fitambarany, ny tombony dia eo amin'ny 35% amin'ny fotoana maka backup fotsiny noho ny hoe ny backup dia nosoratana tamin'ny rakitra 4 amin'ny kapila iray indray mandeha. Nijery isa lehibe kokoa aho - tsy misy tombony amin'ny solosainako, tsara indrindra - fitaovana 4. Ho anao - tsy fantatro, mila manamarina ianao. Eny ary, raha manana an'ireo fitaovana ireo ianao - ireo dia kapila tena samihafa, arahabaina, ny tombony dia tokony ho lehibe kokoa.
Andeha isika hiresaka momba ny fomba hamerenana izany fahasambarana izany. Mba hanaovana izany dia mila manova ny baiko fanarenana ianao ary mitanisa ny fitaovana rehetra:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;Ny RESTORE DATABASE dia nahomby tamin'ny fanodinana pejy 1070074 tao anatin'ny 38.027 segondra (219.842 MB/s).
Haingana kely, fa any amin'ny toerana akaiky, tsy manan-danja. Amin'ny ankapobeny, nesorina haingana kokoa ny backup ary averina amin'ny fomba mitovy - fahombiazana? Raha ny amiko dia fahombiazana tokoa izany. izany dia zava-dehibe, dia averiko indray - raha ianao raha very ny iray amin'ireo rakitra ireo ianao dia very ny backup manontolo.
Raha mijery ao anaty log ianao amin'ny fampahalalana backup aseho amin'ny alàlan'ny Trace Flags 3213 sy 3605, dia ho hitanao fa rehefa miverina amin'ny fitaovana maromaro ianao, farafaharatsiny dia mitombo ny isan'ny BUFFERCOUNT. Azo inoana fa azonao atao ny manandrana misafidy paramètre tsara indrindra ho an'ny BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, saingy tsy nahomby teo no ho eo aho, ary kamo loatra aho nanao fitsapana toy izany indray, fa ho an'ny rakitra hafa. Ary mahamenatra ny momba ny kodiarana. Raha te handamina fitsapana toy izany ao an-trano ianao, dia tsy sarotra ny mamerina ny script.
Farany, andao hiresaka momba ny vidiny. Raha esorina ny backup miaraka amin'ny asan'ny mpampiasa, dia mila mandray andraikitra tena tompon'andraikitra amin'ny fitsapana ianao, satria raha esorina haingana kokoa ny backup, dia mihenjana kokoa ny kapila, mitombo ny enta-mavesatra eo amin'ny processeur (mbola mila manindry ianao. amin'ny lalitra), ary noho izany dia mihena ny fandraisan'anjaran'ny rafitra.
Mivazivazy fotsiny, fa azoko tsara fa tsy nanao fanambarana aho. Ny voasoratra etsy ambony dia fanehoana fotsiny ny fomba ahafahanao misafidy ny masontsivana tsara indrindra amin'ny fanaovana backup.
Tsarovy fa ny zavatra rehetra ataonao dia atao amin'ny loza sy ny loza mety hitranga aminao. Jereo ny backups ary aza adino ny momba ny DBCC CHECKDB.
Source: www.habr.com