Kungani i-SQL commit yakho ehambisanayo ilinda uhambo lwe-VPN lokuya nokubuya

Ukubambezeleka kokubhala kwehla kakhulu ngemva kokuba ikhophi ithuthelwe endaweni yesibili, futhi umhubhe uyingxenye entsha kakhulu, ngakho uthola iphutha. Umhubhe ungaba msulwa: ngaphakathi kweqembu lokutholakala eline-commit ehambisanayo, eyinhloko ayikwazi ukuqinisekisa i-commit kuze kube yilapho ukubhalwa kwelogi ekude kuqedwa, ngakho zonke izinkokhelo zikhokha uhambo lokuya nokubuya, kungakhathaliseki ukuthi zingaki izigidi zamabhithi ezihamba ngoxhumo. Ukugeleza kukala amabhithi ngomzuzwana, ukubambezeleka kukala ukuthi ukushintshana okukodwa kuthatha isikhathi esingakanani, futhi i-commit idinga ukushintshana okukodwa okuncane ukuze iqede. Inombolo eyodwa ixazulula impikiswano: ubude be-commit yokwenziwa okuncane, kulinganiswa ngaphakathi kwesizindalwazi sokutholakala bese kuphinda kulinganiswe netafula lendawo kuleyo instance efanayo.

Eyinhloko ilindelani ngempela

Ngaphansi kwe-commit ehambisanayo, eyinhloko ibamba ukuqinisekiswa kwe-commit kuze kube yilapho umlingani eqinisekisa ukuthi amarekhodi akhe elogi aqinisiwe. Ukuqiniswa kusho ukubhalwa unomphela kulogi yokwenziwa yesibili, hhayi ukuphindwa kumakhasi ayo edatha. I-redo ingasala ngemuva ngamaminithi ngaphandle kokwengeza ngisho imilisekondi eyodwa ku-commit yeyinhloko.

Idokhumenti ye-Microsoft mayelana nezimo zokutholakala ichaza lokho kubambana ngesandla futhi ichaza ukuthi inkundla yenza ini lapho umlingani ewa. Ikhophi emakwe njengehambisanayo iwumthetho mayelana nokulinda, hhayi isithembiso esihlala njalo sokuthi kukhona ikhophi ekude enempilo yakho konke ukwenziwa. Indlela ephukile ichazwa ngokwehlukana, futhi akusikho nje ukuthi i-commit iyahluleka.

Ngakho ukulinda okubonayo kuyisamba: ukuthuthwa kwelogi, ukuhlelwa kuzo zombili izingqalazizinda kanye nokubhalwa esikhungweni esikude. Eyesibili enevolumu yelogi egcwele yengeza isikhathi okungekho ukuhlolwa kwesivinini okuzokukhombisa. Leyo diski ayivamisile ukukalwa lapho isikhalo sisho i-VPN.

Ububanzi bebanzi akusikho ukubambezeleka kohambo lokuya nokubuya

Ukuhlolwa kwesivinini kugcwalisa indlela isikhashana futhi kubika izinga. I-commit icela into encane kakhulu: ukuthi umlayezo omncane uthatha isikhathi esingakanani ukufinyelela kolunye uhlangothi, ugcinwe futhi ubuya. Ubude bendlela, imigqa kanye nokulahleka kwamaphakethe kunquma leyo nombolo, futhi akukho kulokho okuba ngcono lapho umhlinzeki ekhuphula uphahla.

Ngakho umhubhe «osheshayo» ungangeza inhlawulo ebonakalayo emthwalweni womsebenzi osindayo ekubhaleni owakhiwe ngezinkokhelo eziningi ezincane ezilandelanayo. Isamba samabhithi esifanayo sehlukaniswe ngezinkokhelo eziyinkulungwane sikhokha uhambo lokuya nokubuya izikhathi eziyinkulungwane; ukudluliswa okukodwa okukhulu kukhokha kanye.

Cela umnikazi wenethiwekhi ukuthi akale ukubambezeleka nokulahleka endleleni yekhophi uqobo ngaphansi komthwalo omelela. I-ping esuka kukhompyutha yomhlane yesihlalo ihla iye esangweni le-VPN, noma ukuhlolwa kwesivinini kuyiye kunodi eseduze, cishe akukho okukwabelayo endleleni ilogi ehamba ngayo ngempela. Qinisekisa ukuthi iwindi lokukala lihambisana namahora okubhala okunensayo.

Funda isimo sekhophi ngaphambi kokushintsha noma yini

Qala ngalokho okubikwa ngamainstance mayelana nawo, ngoba izinhlangothi zombili zingase zingavumelani ngokuthi ubudlelwano bunempilo engakanani futhi isilinganiso sedashibhodi sifihla ukuthi imuphi umugqa onyakazayo. Imibuzo engezansi iphendula imibuzo ehlukene: eyodwa ifunda isimo sokuphindaphinda ngesizindalwazi ngasinye, enye ifunda ukuthi ikhophi ngayinye ihlelwe kanjani. Sebenzisa zombili ngaphambi kokuthinta imodi.

SELECT ag.name AS ag_name, drs.database_id, drs.synchronization_state_desc,
       drs.log_send_queue_size, drs.redo_queue_size, drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id;

SELECT ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc
FROM sys.availability_replicas AS ar;

Umphumela wokuqala wehlukanisa ukuthuthwa kwelogi ne-redo. log_send_queue_size ekhuphuka ngesikhathi redo_queue_size imile ithe gqolo kusho ukuthi ilogi ayifikanga; okuphambene nakho kusho ukuthi ifikile futhi ukudlala kwephinda kusemva. Isimo sokuqala kuphela esihlala endleleni ye-commit.

Umphumela wesibili uqinisekisa ukuthi yiziphi izikhophi ezihlelwe ngempela ngendlela ocabanga ngayo, ngoba imodi eshintshiwe ngesikhathi sesigameko sangaphambilini ivamise ukuhlala ishintshiwe futhi ingabhalwanga. Funda umugqa onendaba kuwe esikhundleni sokuzenzakalela kweqembu: availability_mode_desc kanye ne-failover_mode_desc kwezikhophi ngazinye. I-secondary_lag_seconds ikhona kuphela ku-SQL Server 2016 nangaphezulu, ngakho ukwakhiwa okudala kukushiya nemibhalo yomugqa wokuthumela ne-redo kuphela.

Bese ukala ukubhala okumelelayo esikhundleni sokuthembela isilinganiso sedashibhodi. Sebenzisa ibhlokhi engezansi esizindalwazini sokutholakala, bese uyisebenzisa futhi ngokumelene nesimo setafula elifanayo kusizindalwazi esingaphandle kweqembu kuleyo instance efanayo. Igebe phakathi kwalezi zikhathi ezimbili iyintengo elinganiselwe yelogi ekude, futhi engxoxweni neqembu lenethiwekhi ima kahle kakhulu kunegrafu. Ngemva kwalokho shintsha uguquko olulodwa ngesikhathi, kungenjalo igelekeqe alisasho lutho.

IF OBJECT_ID('dbo.CommitProbe') IS NULL
    CREATE TABLE dbo.CommitProbe (probe_id int PRIMARY KEY, touched_at datetime2(3));
IF NOT EXISTS (SELECT 1 FROM dbo.CommitProbe WHERE probe_id = 1)
    INSERT INTO dbo.CommitProbe (probe_id) VALUES (1);
SET STATISTICS TIME ON;
BEGIN TRANSACTION;
UPDATE dbo.CommitProbe SET touched_at = SYSUTCDATETIME() WHERE probe_id = 1;
COMMIT TRANSACTION;
SET STATISTICS TIME OFF;

I-commit engahambisanayo ithenga ini, futhi ibiza ini

Nge-commit engahambisanayo, eyinhloko iyeka ukulinda ukuqinisekiswa kokuqiniswa okukude, ngakho uhambo lokuya nokubuya luyaphuma endleleni ye-commit. Ukubambezeleka kwe-commit kuyehla futhi eyesibili ikhululekile ukusala ngemuva ngenani okungekho muntu olilawulayo. Lokho kuwukulungiswa kwangempela kokulinda kanye noshintsho lwangempela esithembisweni.

Intengo iyisinqumo mayelana nephuzu lokubuyisela. Uma eyinhloko ifa ngaphambi kokuba ilogi yayo ifinyelele umgomo wokudlulela ekwehlulekeni ongawusebenzisa ngempela, ukuthutha isevisi iye kukhophi engahambisanayo kulahla izinkokhelo ezazivele zabikwa uhlelo lokusebenza njengeziqinisekisiwe. Asikho isikhulumiswano sokumisa esikubeka kanjalo, ngoba umbuzo webhizinisi mayelana nokulahleka okwamukelekayo, hhayi isilungiselelo sokusebenza. Bhala iwindi lokulahleka elibekezelelekayo ngaphambi kwesigameko, hhayi phakathi naso.

Vumelana ngemodi nomnikazi wedatha, ngokubhaliwe, kanye nenqubo yokudlulela ekwehlulekeni yesimo lapho umgomo okhethwayo ungatholakali. Okuhambisanayo embhalweni wokwakha akuyona incwadi yokusebenza, futhi okungahambisanayo akusona imvume yokuyeka ukubheka imigqa.

Khetha ukulungiswa ongakuvikela kamuva

Kunezinketho ezine eziqotho. Sika uhambo lwe-commit lokuya nokubuya olungagwemeka lapho incazelo yohlelo lokusebenza ivumela khona ukuhlanganisa; lungisa umgoqo wokuthutha noma wokugcina oyikalisile, okungase kusho ivolumu yelogi eshesha kakhulu kweyesibili esikhundleni sepayipi elikhudlwana; sondela ikhophi ukuze uhambo lunciphe; noma ukhulule inqubomgomo yokuvikela futhi wamukele iwindi lokulahleka elibanzi. Ngakunye kuthenga ukubambezeleka ngokusebenzisa okunye, futhi okwesine kusebenzisa ukuqina. Shono incitho ngaphambi kokukhetha.

Yenza isinqumo ngenkathi umgomo wokubambezeleka womthwalo womsebenzi kanye nesidingo sawo sokuqina kukho kokubili phambi kwakho ngesikhathi esisodwa, ngoba ukuthuthukisa okukodwa kusebenzisa okunye buthule. Bese usebenzisa futhi lokho kwenziwa okukalile ngaphambi koshintsho. Ukulungiswa okungakwazi ukunyakazisa leyo nombolo ukuqagela okugqoke ithikithi loshintsho.

Gcina ukufunda kwangaphambi nangemva embhalweni woshintsho. Uma kungenjalo umuntu olandelayo obona ukubhala okunensayo uphinda uphenyo kusukela ekuqaleni futhi aqale futhi ngokusola umhubhe.