ProHoster > Blog > Utongi > Kuendesa data rekuchengetedza kubva kushanduro itsva yeMS SQL Server kuenda kune yekare vhezheni

Kuendesa data rekuchengetedza kubva kushanduro itsva yeMS SQL Server kuenda kune yekare vhezheni

prehistory

Pane imwe nguva, kuti ndibudise bug, ini ndaida backup yedhatabhesi rekugadzira.

Kukushamisika kwangu, ndakasangana nemiganhu inotevera:

  1. Iyo yekuchengetedza database yakagadzirwa pane shanduro SQL Server 2016 uye yaisaenderana neyangu SQL Server 2014.
  2. Pakombuta yangu yebasa yaive OS Windows 7saka handina kukwanisa kugadzirisa SQL Server kusvika kune shanduro 2016
  3. Chigadzirwa chinotsigirwa chaive chikamu cheiyo yakakura sisitimu ine yakanyatsobatanidzwa nhaka yekuvaka uye zvakare yakawana zvimwe zvigadzirwa nemabhesi, saka zvinogona kutora nguva yakareba kwazvo kuendesa kune chimwe chiteshi.

Tichifunga nezvezviri pamusoro apa, ndakasvika pakugumisa kuti nguva yasvika yemadondoro ezvisina-standard mhinduro.

Kudzoreredza data kubva kune backup

Ndakasarudza kushandisa muchina chaiwo Oracle VM VirtualBox ne Windows 10 (unogona kutora mufananidzo wekuyedza weEdge browser kubva pano) SQL Server 2016 yakaiswa pamushini chaiwo uye dhatabhesi rekushandisa rakadzoreredzwa kubva kune backup (manual).

Kumisikidza kupinda kuSQL Server pamushini chaiwo

Tevere, zvaive zvakakodzera kutora matanho ekuita kuti zvikwanise kuwana SQL Server kubva kunze:

  1. Kune firewall, wedzera mutemo kuti zvikumbiro zvechiteshi zvipfuure 1433.
  2. Zvinokurudzirwa kuti kupinda kune sevha kunoitika kwete kuburikidza neWindows authentication, asi kuburikidza neSQL uchishandisa login uye password (zviri nyore kugadzirisa kuwana). Nekudaro, mune iyi kesi, iwe unofanirwa kuyeuka kugonesa SQL kuvimbiswa muSQL Server zvivakwa.
  3. Mune zvigadziriso zvevashandisi paSQL Server pane iyo tab Mushandisi Mepu tsanangura basa remushandisi kune yakadzoreredzwa dhatabhesi db_securityadmin.

Data transfer

Chaizvoizvo, iyo data yekuendesa pachayo ine nhanho mbiri:

  1. Kuendesa data schema (matafura, maonero, akachengetwa maitiro, nezvimwewo)
  2. Kuendesa iyo data pachayo

Data Schema Transfer

Isu tinoita zvinotevera mashandiro:

  1. Sarudza Mabasa -> Gadzira Zvinyorwa kune inotakurika base.
  2. Sarudza zvinhu zvaunoda kuendesa kana kusiya iyo default kukosha (mune iyi kesi, zvinyorwa zvichagadzirwa kune zvese zve database zvinhu).
  3. Nyora marongero ekuchengetedza chinyorwa. Nzira iri nyore ndeyekuchengetedza script mufaira rimwechete muUnicode encoding. Zvino, kana pane kutadza, hauzodi kudzokorora matanho ese zvakare.

Kana iyo script yachengetwa, inogona kuitwa pane sosi SQL Server (shanduro yekare) kugadzira dhatabhesi rinodiwa.

Yambiro: Mushure mekuita script, unofanirwa kutarisa kuenderana kwezvirongwa zve database kubva kune backup uye dhatabhesi yakagadzirwa ne script. Mune yangu, iyo script yakashaya marongero eCOLLATE, izvo zvakakonzera kukundikana pakuendesa data uye kutamba netambourine kugadzira zvakare dhatabhesi uchishandisa yakawedzera script.

Data transfer

Usati waendesa data, unofanirwa kudzima kutarisa zvese zvinorambidzwa pane dhatabhesi:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Kuendesa data kunoitwa uchishandisa iyo Data Import Wizard Mabasa -> Import Data paSQL Server, uko dhatabhesi yakagadzirwa nescript iripo:

  1. Taura marongero ekubatanidza kune sosi (SQL Server 2016 pamushini chaiwo). Ndakashandisa Data Source SQL Server Native Client uye yambotaurwa SQL kuvimbiswa.
  2. Isu tinotsanangura marongero ekubatanidza kunzvimbo yekuenda (SQL Server 2014 pamushini wekutambira).
  3. Zvadaro tinogadzirisa mapepa. Unofanira kusarudza zvose kwete kuverenga-chete zvinhu (somuenzaniso, maonero haafaniri kusarudzwa). Zvimwe zvingasarudzwa zvinofanira kusarudzwa "Bvumira kuiswa mumakoramu ekuzivikanwa", kana zvakadaro zvakashandiswa.
    Yambiro: kana iwe ukaedza kusarudza akati wandei matafura uye ugovapa iyo pfuma "Bvumira kuiswa mumakoramu ekuzivikanwa" iyo pfuma yakatove yakagadzirirwa kune imwe yetafura yakasarudzwa, nhaurirano icharatidza kuti pfuma yakatove yakagadzirirwa matafura ose akasarudzwa. Chokwadi ichi chinogona kuvhiringa uye chinotungamira kune kutamisa kukanganisa.
  4. Ngatitange kutumira.
  5. Kudzoreredza cheki yekumanikidza: 
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Kana paine zvikanganiso zvikaitika, tinotarisa marongero, bvisa dhatabhesi yakagadzirwa nezvikanganiso, gadzira zvakare kubva pane script, ita zvigadziriso uye dzokorora kuendesa data.

mhedziso

Iri basa harisi nyore uye rinomuka chete nekuda kwezvipimo zviri pamusoro. Mhinduro yakajairika ndeyekusimudzira SQL Server kana kubatana kune iri kure server kana iyo application yekuvaka ichibvumira. Nekudaro, hapana akachengeteka kubva kune legacy kodhi uye nemaoko akatsveyama ehurombo-mhando yekuvandudza. Ndinovimba kuti hauzoda iyi mirairo, uye kana uchizvida, ivo vanokubatsira kuchengetedza nguva yakawanda uye tsinga. Ndinokutendai nekuteerera kwenyu!

Rondedzero yezvinyorwa zvakashandiswa

Source: www.habr.com