I-ProHoster > Π‘Π»ΠΎΠ³ > Ukuphatha > Idlulisela idatha eyisipele isuka enguqulweni entsha ye-MS SQL Server iye enguqulweni endala

Idlulisela idatha eyisipele isuka enguqulweni entsha ye-MS SQL Server iye enguqulweni endala

prehistory

Ngesinye isikhathi, ukukhiqiza kabusha iphutha, ngangidinga ikhophi yasenqolobaneni yokukhiqiza.

Okwangimangaza, ngihlangabezane nemikhawulo elandelayo:

  1. Ikhophi yasenqolobaneni yedatha yenziwe enguqulweni I-SQL Server 2016 futhi yayingahambisani neyami I-SQL Server 2014.
  2. Kukhompyutha yami yomsebenzi i-OS yayikhona Windows 7ngakho angikwazanga ukubuyekeza I-SQL Server kuze kube yinguqulo ka-2016
  3. Umkhiqizo osekelwayo ubuyingxenye yesistimu enkulu enezakhiwo zefa ezihlanganiswe ngokuqinile futhi wafinyelela neminye imikhiqizo nezisekelo, ngakho kungathatha isikhathi eside kakhulu ukuphakelwa kwesinye isiteshi.

Ngokucabangela lokhu okungenhla, ngifinyelele esiphethweni sokuthi sesifikile isikhathi sezinduku zezixazululo ezingajwayelekile.

Ibuyisela idatha kusuka kukhophi yasenqolobaneni

Nganquma ukusebenzisa umshini obonakalayo I-Oracle VM VirtualBox nge Windows 10 (ungathatha isithombe sokuhlola sesiphequluli se-Edge kusuka lapha). I-SQL Server 2016 yafakwa emshinini we-virtual futhi i-database yohlelo lokusebenza yabuyiselwa kusuka kukhophi yasenqolobaneni (incwadi).

Isetha ukufinyelela ku-SQL Server emshinini wokubuka

Okulandelayo, bekudingeka ukuthi kuthathwe izinyathelo ezithile ukwenza kube nokwenzeka ukufinyelela i-SQL Server ngaphandle:

  1. Ku-firewall, engeza umthetho wokuvumela izicelo zembobo ukuthi zidlule 1433.
  2. Kuyancomeka ukuthi ukufinyelela kuseva kungenzeki ngokugunyazwa kweWindows, kodwa nge-SQL kusetshenziswa ukungena ngemvume nephasiwedi (kulula ukulungisa ukufinyelela). Nokho, kulesi simo, kufanele ukhumbule ukunika amandla ukuqinisekiswa kwe-SQL kuzakhiwo ze-SQL Server.
  3. Kuzilungiselelo zomsebenzisi ku-SQL Server kuthebhu Umsebenzisi Mapping cacisa indima yomsebenzisi kusizindalwazi esibuyiselwe db_securityadmin.

Ukudluliswa kwedatha

Empeleni, ukudluliswa kwedatha ngokwako kunezigaba ezimbili:

  1. Ukudlulisa i-schema yedatha (amathebula, ukubuka, izinqubo ezigciniwe, njll.)
  2. Idlulisela idatha ngokwayo

Ukudluliswa Kwesikimu Sedatha

Senza imisebenzi elandelayo:

  1. Khetha Imisebenzi -> Khiqiza Izikripthi okwesisekelo esiphathekayo.
  2. Khetha izinto ozidingayo ukuze uzidlulise noma ushiye inani elimisiwe (kulokhu, imibhalo izodalelwa zonke izinto zesizindalwazi).
  3. Cacisa izilungiselelo zokulondoloza umbhalo. Indlela elula kakhulu ukugcina umbhalo efayeleni elilodwa ekubhalweni kwe-Unicode. Khona-ke, uma kukhona ukwehluleka, ngeke udinge ukuphinda zonke izinyathelo futhi.

Uma isikripthi sesilondoloziwe, singenziwa kumthombo we-SQL Server (inguqulo endala) ukuze kwakhiwe idatha egciniwe edingekayo.

Isexwayiso: Ngemva kokwenza iskripthi, udinga ukuhlola ukuvumelana kwezilungiselelo zesizindalwazi kusuka kusipele kanye nesizindalwazi esidalwe yiskripthi. Endabeni yami, iskripthi besingenaso isilungiselelo se-COLLATE, okuholele ekwehlulekeni lapho kudluliswa idatha nokudansa ngethamborini ukuze kudalwe kabusha isizindalwazi kusetshenziswa umbhalo owenziwe ukungathandwa kwabathelisi esikubona.

Ukudluliswa kwedatha

Ngaphambi kokudlulisa idatha, kufanele ukhubaze ukuhlola yonke imikhawulo kusizindalwazi:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Ukudlulisa idatha kwenziwa kusetshenziswa Iwizadi Yokungenisa Idatha Imisebenzi -> Ngenisa Idatha ku-SQL Server, lapho isizindalwazi esidalwe yiskripthi sitholakala:

  1. Cacisa izilungiselelo zokuxhuma emthonjeni (SQL Server 2016 emshinini obonakalayo). Ngisebenzise Umthombo Wedatha SQL Server Native Client kanye nokuqinisekiswa kwe-SQL okukhulunywe ngakho ngenhla.
  2. Sicacisa izilungiselelo zokuxhuma endaweni (SQL Server 2014 emshinini womsingathi).
  3. Okulandelayo simisa imephu. Kufanele ukhethe konke hhayi ukufunda kuphela izinto (isibonelo, ukubuka akudingi ukukhethwa). Izinketho ezengeziwe kufanele zikhethwe "Vumela ukufakwa kumakholomu obunikazi", uma zisetshenziswa.
    Isexwayiso: uma uzama ukukhetha amathebula amaningana bese uwabela indawo "Vumela ukufakwa kumakholomu obunikazi" impahla isivele isethwe ngaphambilini okungenani itafula elilodwa elikhethiwe, ingxoxo izokhombisa ukuthi isakhiwo sesivele sisethiwe kuwo wonke amathebula akhethiwe. Leli qiniso lingase lidide futhi liholele emaphutheni okudlulisa.
  4. Masiqale ukudlulisa.
  5. Ukubuyisela isheke lokuvinjelwa: 
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Uma kwenzeka noma yimaphi amaphutha, sibheka izilungiselelo, sisuse i-database edalwe ngamaphutha, sidale kabusha kusuka kuskripthi, senze izilungiso futhi siphinde ukudluliswa kwedatha.

isiphetho

Lo msebenzi uyivelakancane futhi uvela kuphela ngenxa yemikhawulo engenhla. Isixazululo esivame kakhulu ukuthuthukisa i-SQL Server noma ukuxhuma kuseva ekude uma ukwakheka kohlelo lokusebenza kukuvumela. Kodwa-ke, akekho ophephile kukhodi yefa kanye nezandla ezigwegwile zentuthuko yezinga eliphansi. Ngethemba ukuthi ngeke uyidinge le miyalo, futhi uma uyidinga, izokusiza ukuthi wonge isikhathi esiningi nezinzwa. Ngiyabonga ukulalela kwenu!

Uhlu lwemithombo esetshenzisiwe

Source: www.habr.com