SQL Server-tietokanta ja klassinen DataOps – intro

Jani Savolainen

Microsoft SQL Server-tietokantaskriptien luominen ja skriptien ylläpito ovat oma taiteenlajinsa, josta iso osa on ohjelmistotuotannollisia periaatteita, alan parhaita käytäntöjä, sekä koko joukko erilaisia teknisiä niksejä, jotka helpottavat lähdekoodinhallintaa.

Yleisesti ottaen systeemityössä tietokannan rakennetta (kaikki objektit) kutsutaan englanniksi nimellä schema, joka taasen ei ole sama asia kuin SQL Serverin schema. Edelleen; tietokannan päivitystä uuteen tilaan kutsutaan englanniksi yleensä termillä schema migration, eli skeemamigraatio.

Kaksi pääsuuntausta skeemamigraatioon ovat:

• Migraatioskriptit, jotka ajetaan aina järjestyksessä ja vievät skeeman hallitusti tunnettuun tilaan versiomuutos kerrallaan. Yleissääntönä vain lisätään uusia skriptejä, ei poisteta vanhoja
• Tilaperusteiset (state) strategiat, joissa määritetään tavoitetila ja softatyökalut koettavat päätellä lennossa, miten se saadaan haluttuun tavoitetilaan. Tällaisia työkaluja ovat esimerkiksi Visual Studion SQL Server Data Tools (SQL Server Data Tools | Visual Studio – Visual Studio (microsoft.com) sekä Redgate SQL Compare.

Tässä blogisarjassani käsittelen ensiksimainittua tapaa, joka mahdollistaa mielestäni koodipuolella enemmän asioita, vaikka onkin työläämpää ylläpitää ja kehittää. Blogi SQL Server Data Toolsista ja vastaavista työkaluista saa siis ainakin toistaiseksi vielä odottaa.

Tarkoituksenani on käydä läpi myös hieman tietokantasuunnittelun käytännön filosofiaa sekä toteutustapaa, jossa tietokantaskriptit ovat alun alkaen dynaamisia, eikä sinun tarvitse kyhätä tietokannallesi ylenmäärin luonti- ja ylläpitoskriptejä. Lisäksi käyn läpi perusteita pilkkoa luontiskriptit loogisiin kokonaisuuksiin, jotta tietokantaratkaisusta tulee ohjelmistotuotannollisesti modulaarisempi ja monikäyttöisempi eri päivitysskenaarioihin liittyen. Blogisarjassa sivuutetaan hieman myös lähdekoodinhallintaa.

Oma taustani Microsoft SQL Server-tietokannoissa alkaa vuodesta 1999, josta lähtien olen suunnitellut ja koodannut useita kymmeniä tietokantaratkaisuja, myös kansainvälisiä tietokantatuotteita kuten Wicom Data Universe sekä SQL Governor. Nykyisin johdan SQL Governorin R&D:tä sekä toimin yhtenä teknisistä liideistä dataprojekteissamme DB Pro Servicessä.

Pilko elefantti osiin – SQL-skriptien modularisointi

Kuten sanottua, tietokantaskriptien modulariointi tekee tietokantaratkaisustasi joustavamman eri käyttöskenaarioihin. Tämän lisäksi skriptit säilyvät helpommin ylläpidettävinä ja muodostavat hyvin suunniteltuina selkeitä, loogisia kokonaisuuksia, jotka on helppo vaiheistaa, olipa sitten kysymys clean installista, softapäivityksestä, hotfixistä tai asennuksen poistosta. Sinun kannattaakin kategorioida skriptimoduulisi näiden loogisten kokonaisuuksien pohjalta:

• Clean install = Softan ensimmäinen asennuskerta hermeettiseen ympäristöön. Tätä varten on viisasta luoda muuttumaton skriptirunko, jossa tietokannan ns. corekomponentit asennetaan ja jäädytetään tiettyyn versioon.
• Softapäivitys = Uuden softaversion päivittäminen edellisestä seuraavaan major- tai minor -versioon. Tätä varten tarvitset omat sql-päivitysskriptit eli ns. skeemamigraatioskriptit. Tällä tavalla voit tarvittaessa kelata tuoteversiopäivityksen useita versioita eteenpäin.
• Hotfix = Pikakorjaus, jossa yleensä ajetaan yksittäinen .sql-tiedosto korjaamaan yksittäistä bugia. Hotfixit on aina hyvä koostaa seuraavaan major- tai minor-softapäivitysversioon.
• Asennuksen poiston tulisi jättää ympäristö mahdollisimman lähelle asennusta edeltänyttä tilaa ja siivota vanha softakoodi pois ympäristöstä kokonaisuudessaan. Tätä varten tarvitset yleensä vain yhden tietokantaskriptin.

Clean install -skriptimoduulit

Clean installissa on yleensä eniten skriptimoduuleja, koska se toimii runkona koko tietokantaratkaisullesi. Skriptit kannattaa dynamisoida SQL command-parametreillä, jolloin niitä voi ajaa kätevästi esim. Windows installerista käsin kutsuen. Windows installerin on toki toivuttava kaikista niistä mahdollisista tilanteista, joissa tietokantaskripteissä tulee poikkeuksia. Lisäksi skriptimoduulien tulee olla uudelleen ajettavia (=idempotent), eli kaikkialla tietokantaskripteissä on käytettävä mm. DROP / CREATE- taikka CREATE OR ALTER -määreitä ja tarpeen tullen TRUNCATE TABLE -määrettä.

Tyypilliset clean install-skriptimoduulit ovat:

• Core-skriptiin on hyvä sijoittaa tietokannan (uudelleen-) luontiskriptit käyttäjyyksineen. Ethän käytä default sa-tunnusta, sillä tämä ei ole koskaan tietoturvallinen vaihtoehto SQL Server-ympäristössä.
• Tables and views -skriptiin sijoitetaan taulut ja näkymät uudelleenluonteineen.
• Drop procedures -skripti tiputtaa kaikki user stored proceduret ja käyttäjäfunktiot kannasta.
• Table types -skripti (uudelleen-)luo kaikki taulutyypit.
• Create procedures -skripti (uudelleen-)luo kaikki user stored proceduret ja käyttäjäfunktiot. Jos proseduureja on todella paljon ja ne ovat pitkiä, haluat ehkä modularisoida proseduurit ja funktiot kunkin omikse skripteikseen.
• Encrypted procedures -skripti (uudelleen-)luo kaikki kryptatut user stored proceduret.
• Populate tables -skripti truncatoi ja populoi käyttäjätaulujen sisällön (=alustus).
• Jobs -skripti (uudelleen-)luo ratkaisun SQL Server jobit.
• Version -skripti hoitaa lopuksi versiopäivityksen lokitauluun.

Tulen tässä blogisarjassa kirjoittamaan osasta keskeisimpiä skriptimoduuleja erillisiä blogeja, joissa kuvaan kunkin skriptimoduulin parhaita käytäntöjä hieman tarkemmin koodiesimerkkeineen.

Softapäivitysskriptimoduulit

• Version upgrade -skripti (yksi per versiosiirtymä, yhden major-version inkrementein)
• Version -skripti hoitaa lopuksi major- tai minor -versiopäivityksen lokitauluun.

Hotfix-skriptimoduulit

• Hotfix-skripti sisältää kuvauksen korjatusta ongelmasta sekä itse spesifin korjauksen havaittuun bugiin. Suosituksena on enintään yksi bugikorjaus per yksi tiedosto.
• Version -skripti hoitaa lopuksi hotfix-versiopäivityksen lokitauluun.

Asennuksen poistoskriptimoduulit

• Undeploy-skripti hoitaa koko tietokannan poiston.

Tietokannat kasvavat – ajattele isosti tai Siperia opettaa

Muutama varoituksen sananen agile-kulttuuria vastaan, nimenomaan tietokannoissa kun se tuppaa olemaan joiltan osin haastavaa, tai jopa ei-sovellettavaa: Tietokannat kasvavat ajan saatossa, yhdet lineaarisesti, toiset eksponentiaalisesti. Ohjelmistotuotannollisesti sinun tulisi kyetä edes karkeasti arvioimaan, kuinka suureksi tietokantaratkaisusi voi kasvaa seuraavien 10 vuoden aikana, sillä ei ole lainkaan tavatonta, että tietokantaohjelmistojen elinkaaret ovat jopa kymmeniä vuosia. Esimerkiksi SQL Governor -tietokantaohjelmistomme enimmäinen versio näki päivänvalonsa jo loppuvuodesta 2011. Tätä blogia kirjoittaessani teemme parhaillaan ohjelmistoversiota 16.

On varsin tyypillinen virhe, että tietokantatarpeet alimitoitetaan alussa. Normaali kehityskaari, jonka olen vuosien saatossa nähnyt on, että ensin on tehty jotain XML- tai JSON-pohjaista ja sitten siirrytty partitioimattomaan standalone-OLTP-kantaratkaisuun ja kun tämäkään ei riitä, aletaan kyhäämään taulupartitiointia ja kompressointia sekä viimein erillistä datamarttia, että saadaan operatiivista ja analyyttistä kuormaa jaettua keskenään. Edelleen saatetaan päätyä jopa klusteroituun HA-ratkaisuun. Tällainen kehityskaari on tehoton ja maksaa tietokantakeskeisessä ohjelmistokehityksessä pahimmillaan jopa moninkertaisen euromäärän verrattuna siihen, että alun alkaen mietitään ohjelmiston kasvupotentiaali ja kohdemarkkina siten, että se kestää asiakassegmentin muutoksen isompaan kategoriaan, mahdollisen tietokannanhallintajärjestelmän saatavuusluokituksen nousun sekä siirtymän on premisestä julkipilveen.

Esimerkiksi SQL Governor-tuotteemme tietokannat olivat ohjelmiston alkutaipaleella maksimissaan gigatavun luokkaa. Tänä päivänä ne voivat kasvaa järeimmissä asiakasympäristöissä viidessä vuodessa jopa useiden teratavutasolle, kun lähdetietokantapalvelimia on useita satoja. Onneksi toteutimme tietokantaan lokitaulujen rinnalle jo alun alkaen tähtimallinnetut, conformed data warehouse bus -arkkitehtuurin mukaiset taulut, joka mahdollistaa tehokkaan ja kompaktin historiatietojen tallentamisen ja raportoinnin isoillakin datamäärillä. Suurimmat (~>30M riviä) tietokantataulut tulee partitioida heti alun alkaen. SQL Server -tietokantoihin kun on työlästä ja aikaa vievää sorvata partitiointia jälkikäteen ilman erinäisiä ei-triviaaleja skriptejä ja datamigraatiosta aiheutuvia käyttökatkoja – etenkin jos kyseessä on tuote. Siperia opettaa.

Lähdekoodin hallinnasta

Lähdekoodin hallintaan suosittelen Git:iä, koska se on ilmainen ja integroituu Azure DevOpsiin. Tietokantaskriptien osalta on aivan yhtä keskeistä kuin managed koodissakin, että versioidaan release candidaatteja jo hyvin aikaisessa vaiheessa osana muuta build-toiminnallisuutta.

Loppusanat

Tietokantojen lähdekoodinhallinta on monimutkaista ja pikkutarkkaa puuhaa. Juurikin tietokannoille ominainen datamassojen persistointi tuo enemmän haasteita päivityksiin ja bugikorjauksiin kuin managed-koodissa. Ja vaikka kuinka yrittäisit valmistautua kaikkeen ikävään, migraatioskripteissäkin voi tulla tuotannossa yllätyksiä mm. suurista datamääristä johtuen. Myös erilaiset ympäristötekijät, kuten aikavyhöhykkeet ja kollaatiot voivat hankaloittaa tietokantaskriptien yhteensovittamista. Toimintatapoihin vaikuttavia erityispiirteitä ja haasteita voivat myös olla riippuvuuksien määrä yhdistettynä sekä asiakasympäristöjen monimuotoisuuteen.

On kuitenkin paljon käytäntöjä, joilla helpottaa tätä tuskaa. Juuri näihin kikka-kolmosiin pureudun tässä blogisarjassani.

Kiinnostuitko SQL Server-tietokannoista? Voisimmeko ehkä olla avuksi? Ole hyvä ja ota yhteyttä!

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

CEO & Chairman

DB Pro Services Oy