Pulogalamu ya ProHoster > Blog > Ulamuliro > Kusamutsa zosunga zobwezeretsera kuchokera ku mtundu watsopano wa MS SQL Server kupita ku mtundu wakale

Kusamutsa zosunga zobwezeretsera kuchokera ku mtundu watsopano wa MS SQL Server kupita ku mtundu wakale

prehistory

Kamodzi, kuti ndipangenso cholakwika, ndidafunikira zosunga zobwezeretsera zopanga.

Chondidabwitsa, ndinakumana ndi zolepheretsa zotsatirazi:

  1. Zosunga zosunga zobwezeretsera zidapangidwa pa mtunduwo SQL Server 2016 ndipo sizinali zogwirizana ndi zanga SQL Server 2014.
  2. Pa kompyuta yanga yantchito OS inali Windows 7kotero sindinathe kusintha SQL Server mpaka 2016 version
  3. Chothandiziracho chinali gawo la dongosolo lalikulu lokhala ndi zomanga zomangidwa molimba komanso zidapezanso zinthu zina ndi zoyambira, kotero zitha kutenga nthawi yayitali kuti zitumizidwe ku siteshoni ina.

Poganizira zomwe tafotokozazi, ndinafika pozindikira kuti nthawi yakwana yoti tipeze mayankho osagwirizana.

Kubwezeretsa deta kuchokera ku zosunga zobwezeretsera

Ndinaganiza zogwiritsa ntchito makina enieni Oracle VM VirtualBox ndi Windows 10 (mutha kutenga chithunzi choyesera cha msakatuli wa Edge kuchokera pano). SQL Server 2016 idayikidwa pamakina enieni ndipo nkhokwe ya pulogalamuyo idabwezeretsedwa kuchokera ku zosunga zobwezeretsera (Bukuli).

Kukhazikitsa mwayi wofikira ku SQL Server pamakina enieni

Kenako, kunali kofunikira kuchitapo kanthu kuti zitheke kupeza SQL Server kuchokera kunja:

  1. Kwa firewall, onjezani lamulo lololeza zopempha zamadoko kuti zidutse 1433.
  2. Ndikofunikira kuti mwayi wopezeka pa seva umachitika osati kudzera mu kutsimikizika kwa Windows, koma kudzera mu SQL pogwiritsa ntchito malowedwe ndi mawu achinsinsi (ndikosavuta kukhazikitsa). Komabe, pamenepa, muyenera kukumbukira kuti SQL itsimikizidwe muzinthu za SQL Server.
  3. Muzokonda za ogwiritsa pa SQL Server pa tabu Mapu Ogwiritsa Ntchito tchulani gawo la wogwiritsa ntchito pankhokwe yobwezeretsedwa db_securityadmin.

Kutumiza kwa data

Kwenikweni, kusamutsa deta palokha kumakhala ndi magawo awiri:

  1. Kusamutsa schema data (matebulo, mawonedwe, njira zosungidwa, etc.)
  2. Kusamutsa deta palokha

Kutumiza kwa Data Schema

Timachita izi:

  1. Sankhani Ntchito -> Pangani Zolemba kwa maziko onyamula.
  2. Sankhani zinthu zomwe mukufuna kusamutsa kapena kusiya mtengo wokhazikika (panthawiyi, zolemba zidzapangidwira zinthu zonse za database).
  3. Tchulani zokonda kuti musunge zolemba. Njira yabwino kwambiri ndikusunga script mu fayilo imodzi mu encoding ya Unicode. Ndiye, ngati pali kulephera, simudzasowa kubwereza masitepe onse kachiwiri.

Zolembazo zikasungidwa, zitha kuchitidwa pa gwero la SQL Server (mtundu wakale) kuti mupange database yofunikira.

Chenjezo: Mukamaliza kulemba, muyenera kuyang'ana kusasinthika kwa makonda a database kuchokera pazosunga zosunga zobwezeretsera ndi database yomwe idapangidwa ndi script. Kwa ine, script inalibe makonzedwe a COLLATE, zomwe zinapangitsa kulephera potumiza deta ndikuvina ndi maseche kuti apangenso nkhokwe pogwiritsa ntchito zolemba zowonjezera.

Kutumiza kwa data

Musanasamutse deta, muyenera kuletsa kuyang'ana zoletsa zonse pa database:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Kutumiza kwa data kumachitika pogwiritsa ntchito Wizard wa Data Import Ntchito -> Lowetsani Deta pa SQL Server, pomwe malo osungira omwe amapangidwa ndi script ali:

  1. Tchulani makonda olumikizira ku gwero (SQL Server 2016 pamakina enieni). Ndinagwiritsa ntchito Data Source SQL Server Native Client ndi kutsimikizika kwa SQL komwe kwatchulidwa kale.
  2. Timalongosola makonda olumikizira komwe mukupita (SQL Server 2014 pamakina olandila).
  3. Kenako timapanga mapu. Muyenera kusankha zonse osati kuwerenga kokha zinthu (mwachitsanzo, mawonedwe safunikira kusankhidwa). Zosankha zowonjezera ziyenera kusankhidwa "Lolani kuti alowetse m'mizere yozindikiritsa", ngati agwiritsidwa ntchito.
    Chenjezo: ngati muyesa kusankha matebulo angapo ndikuwapatsa katunduyo "Lolani kuti alowetse m'mizere yozindikiritsa" katunduyo adayikidwa kale patebulo limodzi losankhidwa, zokambiranazo zidzasonyeza kuti katunduyo adayikidwa kale pa matebulo onse osankhidwa. Izi zitha kukhala zosokoneza ndikupangitsa kusamutsa zolakwika.
  4. Tiyeni tiyambe kusamutsa.
  5. Kubwezeretsa cheke choletsa: 
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Ngati zolakwika zilizonse zichitika, timayang'ana zoikamo, kufufuta nkhokwe yomwe idapangidwa ndi zolakwika, timayipanganso kuchokera palemba, kukonza ndikubwereza kusamutsa deta.

Pomaliza

Ntchitoyi ndi yosowa kwambiri ndipo imangochitika chifukwa cha malire omwe ali pamwambawa. Yankho lofala kwambiri ndikukweza SQL Server kapena kulumikizana ndi seva yakutali ngati mamangidwe a pulogalamu amalola. Komabe, palibe amene ali wotetezeka ku code ya cholowa ndi manja okhotakhota a chitukuko chopanda bwino. Ndikukhulupirira kuti simudzasowa malangizowa, ndipo ngati muwafuna, adzakuthandizani kusunga nthawi yambiri ndi mitsempha. Zikomo chifukwa chakumvetsera!

Mndandanda wa magwero ogwiritsidwa ntchito

Source: www.habr.com