SQL Server replikointi ilman domain-tunnuksia

Joskus sitä on ihmisellä tarvetta replikoida kantoja yhdestä palvelimesta toiseen, vaikkapa tapauksissa joissa halutaan rakennella raportteja tai jotakin kivaa web-näkymää valittuun tietoon ilman että kyselyjä kohdistetaan vaikkapa ennestään perin rasittuneeseen OLTP-tyyppiseen (hurjan hieno lyhenne tarkoittaa sanoa että On-Line Transactional Processing, kanta jossa tapahtuu paljon uuden tiedon kirjoittamista ja lukemista samaan aikaan) kantaan. Samalla ehkä hingutaan sitä ettei siellä replikoidussa kannassa ole tarpeettomasti tietoa - vaikkapa niitä luottokorttinumeroita tai jotain muuta krääsää jota se raporttigeneraatio tai web-näkymä ei tarvitse.

Jos koneet ovat samalla toimialueella, tämä on yleensä melkolailla yksinkertainen toimenpide, ja verkko on täynnä helppoja kliksutteluesimerkkejä joilla kanta A siirtyy kokonaan tai osittain kantaan B toisella koneella. Esimerkeissä palvelimet ovat sitten järjestään samalla toimialueella ja samat toimialueen tunnukset pelittävät koneissa kivasti.

Hieman harmillisemmaksi homma muuttuu kun koneet ovat joko työryhmässä (workgroup) tai erillisillä toimialueilla. Jälkimmäinenkin menee vielä luottosuhteita luomalla, mutta jos koneilla ei ole lainkaan AD:tä josta tunnareita ammentaa, aletaan olla vähän nk. jännän äärellä. Replikoinnissa kuitenkin suristelee melkoinen määrä kaikenlaista agenttijobia ja muuta jotka tarvitsevat mitä vängimpiä oikeuksia kantoihin, levylle ja jopa sinne toiseen koneeseen. Vaan eipä hätää! Homman saa pelaamaan myös käyttämällä SQL:n paikallisia tunnuksia, joskin tietoturva ei sitten ole siellä kaikken kivoimmalla tasolla. Sitäkin voi parantaa, mutta tässä esimerkissä astumme helppouden takia reippaasti sinne "toimintatapa ei ole suositeltu" -alueelle, mutta jos replikointiin osallistuvat palvelimet eivät nyt ihan internettiin näy, ei ongelma sinänsä ole suuren suuri.

Replikaatiosta yleensä

SQL Serverin repertuaarissa on useampaa sorttia mekanismeja kantojen ja tiedon kopioimiseen, eikä niitä pidä keskenään sotkeman. Noin modernina aikana periaatteellisia mekanismeja on kolme - peilaus (mirroring), replikaatio (replication) ja transaktiologien toisto (log shipping).

Noin hyvin paljon yksinkertaistaen:

Tässä töherryksessä käymme läpi vain replikaatiota, ja esimerkkimme päämääränä on luoda replikointi joka tapahtuu ilman toimialueen tunnuksia, ja se on nk. Push Replication (jossa lähdekone itse aktiivisesti työntää muuttuneen datan kohdekoneelle). Vaihtoehtoisena tapana on Pull Replication, jossa kohdekone itse käy hakemassa tietoa lähdekoneelta.

Replikaation tyyppi on myös nk. transactional replication (transaktioreplikaatio). Mahdolliset replikaation tyypit ovat:

Liikkuvat osat transaktioreplikaatiossa

Replikaatiossa noin korkealla tasolla on kolme liikkuvaa osaa (no, vain yksi niistä "liikkuu", mutta...):

Muita asiaan liittyviä käsitteitä ovat:


Ja asiaan.

Ennen kuin replikaatiota voidaa tehdä, täytyy jakelija (Distributor) luoda ja konfiguroida. Kuten sanottu, jakelija voisi sijaita omassa kantainstanssissaan ulkoisella koneella, mutta tämän kaltainen konfiguraatio tarvitsee enenmmän tunnuksia, säätämistä ja oikeuksia kuin mihin olemme valmiit ryhtymään. Tässä nimenomaisessa tapauksessa konfiguroimme jakelijan lähdekoneelle - ts. siihen instanssiin jossa myös replikoitavan tiedon lähdekanta on.

Toivottavasti SSMS-ohjelman käyttö on tuttua, sillä aivan jokaista klikkausta emme käy läpi. Käyttämämme oletusarvot hommassa ovat:

Aloitamme siis jakelijan konfiguroimisella. Tämä käy Replication-haaran päältä hiiren oikealla hiplaamalla ja valitsemalla "Configure Distribution". Jos koneessa on jo jakelija, em. valintaa ei ole. Mutta eteenpäin, käymme dialogeja läpi selostaen sen mitä niissä tapahtuu.

Jännähälytys ennen wizardien starttailua!

Tietenkään mitään ei saada toimimaan oikein ennen kuin muutama pikkuasia koneessa on kunnossa. Aiomme nimittäin käyttää SQL Agent -palvelua snapshottien (ts. kannan alkutilan kuvan) ottamiseen. Snapshotit kannoista pitää tallettaa joko levylle tai levyjaolle. Ja yhdessä dialogissa sitten olemmekin valitsemassa jo sitä mihin snapshot talletetaan - ja kuinka ollakkaan, jos levyllä ei ole oikeudet kunnossa, kaikki menee tietenkin nenälleen. Aivan aluksi valitsemme siis paikalliselta levyltä kansion johon aiomme snapshotteja tallentaa, asetamme sen oikeudet  ja mielellään vielä tulevaisuutta varten teemme siitä levyjaonkin (tämä ei ole pakollista, mutta...)

Tässä esimerkkitoiminnassamme SQL (ja instanssin kannat) on asennettu kylmästi C-levylle, hakemistoon C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ (ts. tämä on instance data root).

Olemme luoneet em. hakemiston alle uuden "Snapshot" -nimisen hakemiston.

Teemme hakemistosta levyjaon nimeltä "Snapshot" ja annamme kaikille (ts. pseudoryhmä "Everyone") lukuoikeuden nimenomaisesti jakoon, ei NTFS-pinnalle (ts. "Share permissions"-välilehdellä on Everyone-ryhmällä "read").

Tämän jälkeen muokkaamme niitä NTFS-oikeuksia samaiselle Snapshot-hakemistolle.

Ja tämä on erinomaisen tärkeää: tunnukselle "NT SERVICE\SQLSERVERAGENT" annetaan kuuluisa "Full Control" em. Snapshot-hakemistoon ja kaikkeen sen alle ("This folder and subfolders").

Aiomme nimittäin käyttää SQL Agenttia erilaisten jobien ajamiseen, ja jos Agentin käyttämällä tunnuksella ei ole kansioon oikeuksia, ei synny snapshotteja eikä oikein mitään muutenkaan tapahdu. Tämä ei ole nk. suositeltu tapa, mutta tämän artikkelin tarkoitus on saada homma pelittämään helposti, ei välttämättä kaikkein tietoturvallisimmalla tavalla. Jakelijan konfiguraatiota voi myöhemmin muuttaa jos haluat saada kiristettyn konfiguraation aikaan.

Lisähuomio: jos jostain syystä koneessasi ajat SQL-agenttia jollain muulla tunnuksella kuin oletusarvoisella nk. servicetunnuksella, täytyy oikeudet tietty olla sillä nimenomaisella tunnuksella. Voit varmistaa käytetyn tunnuksen vilkaisemalla SQL Configuration Manageria, Services-haaran alla on lueteltu SQL:n palvelut ja niiden käyttämät palvelutunnukset. Jos siellä näkyy em. NT SERVICE\SQLSERVERAGENT, kaikki on kivasti, jos ei, lunttaa sieltä tunnus ja anna sille Snapshot-kansioon Full Control.

Lisää hälytystä! Ei päästä vieläkään konfiguroimaan tai ajamaan wizardeja!

Kun aiomme tehdä touhumme käyttäen SQL:n sisäisiä tunnuksia (SQL Login), pitää meillä tietenkin olla jokin tunnus jolla kytkeydytään erilaisiin paikkoihin. Luomme siis lähde- ja kohdekoneillemme uuden tunnuksen:

Ja sitten konfiguraatiovelhon kimppuun:

distribution

Ensimmäinen dialogi. Tässä kysellään luodaanko tähän nimenomaiseen instanssiin jossa olemme, uusi jakelija (ja sen tarvitsema tietokanta), vai halutaanko vaihtoehtoisesti käyttää jo jossain muussa instanssissa oleskelevaa jakelijaa. Lähdimme nollasta ja haluamme jakelijan tähän samaan koneeseen, joten valitaan "will act as its own Distributor".

Tämä dialogi utelee juuri sen luomamme kansion, johon kannan snapshot talletetaan. Snapshottia käytetään vain replikaation alussa (tai jos myöhemmin halutaan nollata tilanne uudelleen). Kuten oikeuksien säätelyä koskevassa kohdassa hieman aiemmin, tässä nimenomaisessa koneessa luotiin se Snapshot-niminen kansio instanssin juuren alle, tähänpä se polku sitten laitellaan. Dialogin varoituksesta havaitaan, polku on syötetty paikallisena polkuna, eikä näin ollen pull-tyyppisiä replikaatioita voida tehdä (koska annettu polku ei ole verkkopolku). Pull-tyyppisessä touhussa kohdekone yrittäisi hakea halutun kannan snapshottinsa tavanomaisen Windows-levyjaon yli (ts. file share). Esimerkissä kuitenkin varuiksi loimme sen levyjaonkin - se pitäisi sitten syöttää tähän dialogiin levypolkuna (tyyliin "\\SQLMASTER\Snapshot") jos haluaisimme että kohdekoneet voisivat itse hakea snapshottinsa tämän koneen levyltä. Tästä kuitenkin seuraa kaikenlaista oikeusjumppaa, sillä shareen (ja levypintaan) pitäisi tietty olla oikeuksia sekä tällä jakelijalla (että se pääsisi ne levypolun yli kirjoittamaan) että tilaajalla (että se pääsisi snapshotit lukemaan). Push-tyyppisessä jakelussa vain jakelija tarvitsee oikeudet ja paikallinen levy riittää, sillä push-tyyppisessä touhussa se snapshot työnnetään SQL:n omien mekanismien yli kohdekoneelle, ei niin että kohdekone (ts. tilaaja) kävisi jotain lueskelemassa.

Metakannan luominen. Metakantaan menee siis kaikki replikaation tarvitsema konfiguraatiodata ja tieto siitä miten eri paikoissa replikoituvia kantoja tieto on muuttunut. Tästä tulee ihan ns. aito system database. Dialogissa on asiat jätetty oletusarvojen varaan - kannan nimeksi tulee "distribution" ja polut osoittavat sinne missä tämän koneen instanssijuuren alla olevat kannat normaalistikin sijatsevat. Kuten dialogi kertoo, kanta on jakelijalle paikallinen kanta ja sen tiedostojen pitää sijaita paikallisilla levyllä - jakelijahan sinänsä voi olla missä tahansa koneessa/instanssissa, mutta sen nimenomaisen koneen/instanssin suhteen nämä ovat paikallisia polkuja (ja tiedostoja).

Tässä udellaan sitä mikä nimenomainen kantainstanssi aikoo käyttää tätä jakelijaa kun instanssista tulee julkaisija. Jos meillä olisi monta julkaisijaa ja ulkoinen, toisessa instanssissa asuva jakelija, voitaisiin tässä ikään kuin yhteen keskitettyyn jakelijaan liimata monta julkaisijaa, ja vieläpä niin että ne käyttäisivät omia metakantojaan. Olemme kuitenkin asentamassa jakelijaa ja julkaisijaa samaan koneeseen, joten meille oletuksena tarjotaan tätä samaa kantainstanssia. Kaikki on siis hyvin.

Loppu lähestyy. Dialogissa voidaan valita tehdäänkö wizardin koostama toimenpide oikeasti, ja halutaanko mahdollisesti saada tästä T-SQL-skripti. Hyväksymme sen että saa suorittaa konfiguraation per heti.

Ja jos kaikki meni kivasti, wizardi loi meille distribution-kannan, konfiguroi itse jakelijan ja salli SQLMASTER-koneen (ts. instanssin) ryhtyä julkaisijaksi tälle jakelijalle.

Jakelija kunnossa, aika tehdä julkaisu

Seuraavaksi luomme julkaisun joka sisältää yhden artikkelin. Noin suomeksi - luomme siis määritelmän sille että kun meillä on kanta X, mitä tauluja tai muuta sälää haluamme siitä julkaista replikoitavaksi jollekulle tilaajalle.

New Publication Wizard auki ja suorittamaan.

Aluksi kysellään mistä kannasta aiotaan tämä julkaisu luoda. SQLMASTER-koneessa on uusin AdventureWorks-demokanta joka on nimetty nimelle "Adventure2016". Valitsemme kannan.

Seuraavaksi halutaan tietää julkaisun tyyppi. Kuten aiemmin määrittelimme, kyseessä on nk. Transactional Publication, jossa muutokset alkuperäiseen kantaan kopioidaan kohteeseen sitä mukaa kun niitä tapahtuu. Dialogi ansiokkaasti selittää myös muut julkaisutyypit.

Luomme julkaistavan artikkelin. Olemme tässä tapauksessa valinneet julkaisuumme vain yhden taulun (Address).

Seuraava dialogi kyselisi meiltä haluammeko joillakin säännöillä suodattaa valittuja tauluja, näkymiä tai stored procedureja. Tässä esimerkissä emme suodata mitään, joten hyppäämme dialogin yli.

Ollaan jännien asioiden äärellä. Säädämme kahden erillisen asian oikeuksia tehdä asioita - Snapshot agentin ja Log Reader -agentin. Snapshot Agent on käytännössä jobi joka julkaisun alussa lukee kannan sisällön ja luo snapshotin levylle. Log Reader puolestaan on se jobi, joka tarkkailee transaktioita julkaistavassa kannassa ja replikoi sitten niitä tilaajille. Käytännössä asiat voidaan tehdä niin, että laitetaan rasti ruutuun kohtaan "Use the security settings from the Snapshot Agent", jolloin meidän täytyy määritellä asiat vain kerran - Log Reader käyttäisi samoja asetuksia kuin Snapshot Agent.

Kliksutamme kohtaa "Security Settings" Snapshot -agentin kohdalta.

Ja nyt sitten tarkkana, sillä tässä alkavat nk. monimutkaiset asiat. Dialogi puhuu Snapshot Agent securitystä. Tämä on siis se SQL Agent - jobi joka haluaa julkaisun alussa lukea julkaistun kannan sisällön ja tallettaa sen sinne kansioon jonka oikeuksia sorkimme aiemmin (ts. se Snapshot-kansio). Kuten olimme peräti olevinamme ovelia, ajamme tätä agenttia SQL Server Agent -palvelun oikeuksilla ("run under the sql server agent service account"). Tähän asti helppoa.

Dialogin alaosa sitten haluaa tietää miten kytkeydymme julkaisijaan. Huomaa, ei esim. miten kytkeydymme jakelijaan. Tästä seuraa: tämä on se SQL-tunnus (ts. SQL Login) joka haluaa loggautua siihen KANTAAN josta snapshot luodaan. Arvaatkos miten käy jos siinä kannassa ei ole sitä SQL-tunnusta riittävin oikeuksin jonka tähän läiskäiset? Aivan, snapshot-agentti ei saa luettua sitä kannan sisältöä.

Tästä seuraa tärkeä välitoimenpide:

Kun aiemmin tässä koko säätöhässäkässä loimme SQLMASTER-koneeseen tunnuksen nimeltä "ReplicationUser" ja annoimme sille salasanan, nyt on oikea hetki tarkistaa että em. käyttäjä on dbo-roolissa SIINÄ KANNASSA jonka aiomme tämän julkaisun läpi julkaista. Pärjäisi se varmaan vähemmilläkin oikeuksilla (datareader), mutta yhä haimme helppoa, ei turvallisinta tapaa.

Jos käytämme Log Reader -agentille samoja oikeuksia kuin Snapshot-agentille, tunnus todennäköisesti tarvitsee sen dbo-oikeuden kannassa että se pääsee transaktioihin käsiksi.

Dialogi haluaa tietää milloin ensimmäinen snapshot luodaan. Valitsemme että se luodaan heti ja pidetään tarjolla.

Julkaisu tarvitsee nimen. Vaikka ylläolevassa kuvassa mitään ei ole syötettynä, annamme julkaisulle tässä esimerkissä nimeksi "AdventurePublication".

Jos kaikki meni niin kuin piti - oikeudet joka paikassa ovat oikein ja agentti sai snapshotin luotua, Replication Monitor:in sisällön pitäisi näyttää nyt tältä.

Ja julkaisu on nyt valmis ja odottaa vain tilaajia.

Tilauksen luominen

Siirrymme toiselle koneelle (tässä tapauksessa SQLSECONDARY) ja aloitamme tilauksen luomisen. 

Tai emme sittenkään, sillä taas on hyvä olla niitä asioita etukäteen luotuna:

Tässä esimerkissä replikaatiodatan vastaanottava kanta on nimeltään "AdventurePartial". Luodaan siis tyhjä kanta em. nimellä ja annetaan ReplicationUser:ille sinne dbo-rooli.

Ja sitten wizardi käyntiin ("New Subscriptions")

Valitaan julkaisija. Dialogissa "publisher"-kohdassa voidaan valita "find SQL Server Publisher" ja sitten etsitään tässä meidän esimerkkitapauksessa se "SQLMASTER" ja kirjaudutaan sille. Tunnuksena voi käyttää sitä ReplicationUser:ia tai ehkäpä mieluummin jopa SQLMASTER-koneen sa-tunnusta.

Kun kirjautuminen onnistuu, näkyy aiemmin luotu "AdventurePublication" siellä. Valitaan em. julkaisu ja eteenpäin.

Wizard haluaa tietää missä jakelija on. Koska loimme sen SQLMASTER-instanssiin, valitsemme sen - ja kyseessähän on tällöin push subscription, kun SQLMASTERilla surisevat agentit hoitavat datan tänne päin. Myös sekalaiset konfiguraatiot olisivat mahdollisia, mutta pitäydymme nyt tässä mahdollisimman yksinkertaisessa esimerkkitapauksessa.

Tilaajaksi valitaan SQLSECONDARY, ja jos onnistuneesti loimme vastaanottavan tyhjän kannan jo aiemmin, se voidaan valita kohdekannaksi kuten kuvasta näkyy.

Nyt ollaan taas jännän äärellä. Tämä dialogi haluaa tietää miten JAKELIJA (distributor) on konfiguroitu/konfiguroidaan. Ei suinkaan tilaaja. Huomionarvoista on että tässä dialogissa kerromme miten jakelijan tulisi kytkeytyä, öh, jakelijaan itseensä ja miten jakelijan tulee kytkeytyä tilaajaan. Villiä asiassa on se, että varsinaisen tilauksen luo jakelija, ei suinkaan tilaaja - tässä on siis syytä olla tarkkana, sillä tilaus ei synny SQLSECONDARY:lle ilman että jakelija käy SQLMASTER-koneelta käsin täällä sen tekemässä. Hieman takaperoista, mutta niin se vaan on. Kliksutellaan "..." -kohtaa niin päästään säätämään.

Ja silmä kovana. Ylimmässä kohdassa kerrotaan millä oikeuksilla jakelija-agentti (siellä SQLMASTER:illa) ajaa asioitaan. Sinänsä mielenkiintoista, koska tämähän itse asiassa konfiguroitiin jakelijaa luotaessa. Ajamme sitä siis SQL Agentin oikeuksilla.

Keskellä kysytään miten jakelija-agentti kytkeytyy jakelijaan. Emme voi valita mitään.

Alhaalla kysytään miten jakelija kytkeytyy TILAAJAAN jotta se saa asiansa hoidettua kannassa johon dataa tuodaan. Ja tässä kohden syötetään sitten se tälle SQLSECONDARY-koneelle luotu ReplicationUser -tunnus ja sen salasana. Jos tämä tunnus on olemassa ja sillä on dbo-oikeus SQLSECONDARY-koneen AdventurePartial -nimiseen kantaan, jakelija pystyy sen datansa kantaan kirjoittamaan ja homma pelaa. Käyttäjän täytyy mielellään olla myös Sysadmin-roolissa SQLSECONDARY:llä, sillä ensimmäisenä liikkeenään se varsinaisesti luo sen tilauksen etäältä SQLSECONDARYlle.

Koska olemme tekemässä jatkuvaa transaktiopohjaista replikaatiota, kaikki tässä dialogissa onkin oletusarvoista.

Kerrotaan että aloitamme toiminnan välittömästi. Seuraava dialogi sisältää sitten yhteenvedon ja valinnan sen suhteen luodaanko tilaus ja/tai tehdäänkö siitä T-SQL -skripti. Ja siinä se noin kaikkiaan.

Jos kaikki meni oikein, snapshot saatiin luotua ja replikaatio lähti rullaamaan, SQLMASTERin Replication Monitor näyttää suunnilleen seuraavanlaista tulosta:


Muutamia lisähuomioita

Jakelijan (distributor) konfiguraatiosta löytyy ns. administrative link password, jota meiltä ei kysytty lainkaan. Se arvotaan automaattisesti kun paikallinen jakelija luodaan, eikä meillä ole sille varsinaisesti käyttöä kun jakelija ei ole erillisessä koneessa. Sitä ei sovi mennä muuttamaan, sillä jakelijan agenttijobit käyttävät sitä. Jos jakelijalle asetetaan jokin kone tekemään remote publishingia, salasana täytyy asettaa tunnetuksi että siellä remote publishing -koneessa tiedetään mikä salasana on (ja että se voidaan siellä sitten syöttää). Salasana menee distributor_admin -nimiselle tunnukselle SQL:ssä. Kyseistä tunnusta ei pidä mennä käsin sorkkimaan, lisätietoa alla:

Administrative link password

Use to specify or update the password for the connection replication makes between the Publisher and the remote Distributor using the distributor_admin login:

If the Distributor serves only as a local Distributor, this password is randomly generated and automatically configured. If the Distributor already has a remote Publisher, a password was initially supplied on this page or the Distributor Password page of the Configure Distribution Wizard.

If you enable the first remote Publisher for this Distributor, you are prompted to enter a password. For more information on security for Distributors, see Secure the Distributor.

Do not change the password for the distributor_admin manually. Always use the sp_changedistributor_password stored procedure, or the Distributor Properties or Update Replication Passwords dialog boxes in SQL Server Management Studio, because password changes are then applied to local publications automatically.

Ensure that the snapshot share has read access granted to the account under which the Merge Agent (for merge replication) or Distribution Agent (for snapshot or transactional replication) runs and write access granted to the account under which the Snapshot Agent runs.