SSISDB-luettelon oletusasetukset Parhaat käytännöt

Esittely

SSISDB-tietokanta (eli integraatiopalvelukatalogi) otettiin käyttöön SQL Server 2012:ssa, jotta MSDB-tietokannasta saataisiin vähemmän epäselvyyksiä ja jotta saataisiin aikaan sisäinen kirjaus- ja raportointijärjestelmä. Pähkinänkuoressa SSISDB on SSIS-kehys, joka tekee SQL Server Integration Services -palveluista vankemman ja yritysystävällisemmän tarjoamalla:

• Tietokannan varmuuskopiointi
• Tietokannan salaus
• Ympäristöjen tuki
• Projekti- ja pakettiparametrien tuki
• Paketti versiointi
• Asiakkaiden SSRS-suorituskykyraportit sisäänrakennettuna SSMS:ään
• Toteutus suoraan SSDT:stä

Vaikka SSISDB-kehys teki SSIS:stä paljon suorituskykyisemmän, sen mukana tuli joitakin Microsoftin oletuksia (a.k.a. oletusarvot). Nämä oletusarvot auttavat sinua seisomaan jaloillasi, mutta ne eivät ehkä ole optimaalisia, kun aloitat juoksun, ja ovat kaukana täydellisestä, kun sprinttaat.

Tässä artikkelissa käsitellään oletusarvoja, miksi ne eivät ehkä ole optimaalisia ja miten niitä voidaan muuttaa.

Obgelma

SSISDB Catalogin mukana toimitetaan sisäänrakennettu prosessi, jolla voidaan siivota toimintoja ja projektin versiointia. Tämä siivousprosessi perustuu SSISDB:n oletusasetuksiin, jotka saattavat tehdä SSIS:stä käyttökelvottoman, jos niitä ei muuteta.

Lähes jokainen uusi SSIS-projekti aloitetaan rakentamalla ja suorittamalla vain muutama paketti, kun vapaata levytilaa on runsaasti. Siirry eteenpäin 3-6 kuukautta, ja sinulla on useita SSIS-projekteja, kymmeniä tai satoja paketteja, ja saatat tarvita myös tuoretta dataa raportointia ja analytiikkaa varten (lue: pakettien jatkuva ajaminen ympäri vuorokauden). Nämä SSIS-paketit saattavat kerätä versiointitietoja (pienempi ongelma) ja lokitietoja (suurempi ongelma) paljon enemmän kuin Microsoftin siivouksen oletusarvot, mikä saattaa johtaa SSISDB-tietokannan koon paisumiseen ja odottamattomaan kasvuun (lue: kaiken käytettävissä olevan levytilan syömiseen).

Yksittäinen oletusarvotieto, joka kaappaa SSIS:n suoritusaikaan liittyvät tiedot, on palvelimenlaajuinen oletusarvotaso (Default Loggaus Level) = Basic. Tämä asetus hallinnoi viime kädessä SSISDB-tietokannan kokoa ja viime kädessä vie levytilaa. Tästä huolimatta SSIS Catalog (Kuva 1) tarjoaa muita vaihtoehtoja SSISDB-tietokannan koon/levytilan hallintaan:

1. Server-wide Default Logging Level
2. Clean-up defaults:
   1. Puhdista lokit säännöllisesti = True
   2. Poista vanhat versiot säännöllisesti = True
   3. Pidätysaika (päivinä) = 365
   4. Versioiden enimmäismäärä projekteissa = 10

Palvelimen laajuisen oletuslokitason muuttaminen perusasetuksesta Ei mitään -asetukseksi levytilan palauttamiseksi on erittäin epäkäytännöllistä ja tekee vianmäärityksestä painajaisen, muutoksilla muihin SSISDB-luettelon asetuksiin, kuten Maximum Number of Versions per Projects ja Retention Period (days), voi olla minimaalinen vaikutus vianmääritykseen, ja samalla SSISDB-tietokanta/levytila pysyy hallinnassa.

Kaikki nämä oletusasetukset ohjaavat puhdistusprosessia (eli SSIS-palvelimen ylläpitotyötä). Vaikka sekä Clean Logs Periodically = True (Puhdista lokit säännöllisesti) että Periodically Remove Old Versions (Poista vanhat versiot säännöllisesti) = True (Poista vanhat versiot säännöllisesti) ovat käytännöllisesti katsoen aina täydellisiä, asetukset Maximum Number of Versions per Projects (Versioiden enimmäismäärä projekteja kohti) = 10 ja Retention Period (Days) (Säilytysaika (päiviä)) = 365 saattavat olla liian optimistisia. Ja tässä on syy siihen.

Pääsyy siihen, miksi sekä Clean Logs Periodically = True että Periodically Remove Old Versions = True -asetukset ovat täydellisiä, on se, että ilman näitä asetuksia (tai tehokkaasti, jos molemmat muutetaan arvoon False) SSISDB kasvaa loputtomiin, koska se vain kerää loki- ja versiointitietoja poistamatta mitään tietoja ja levytila loppuu kesken.

Tärkein syy siihen, että sekä Maximum Number of Versions per Projects = 10 että Retaining logging data for 365 days saattavat olla ylioptimistisia, on Microsoftin päätös säilyttää tiedot aikajakson eikä koon perusteella. Tämä tarkoittaa, että kiinteän ajanjakson määrässä ei oteta huomioon sitä, kuinka paljon tietoja kertyy. Vaikka Maximum Number of Versions per Projects (versioiden enimmäismäärä projekteja kohti) saattaa muodostua ongelmaksi, kun paketteja on tuhansia, Retention Period (päivää) muodostuu suureksi ongelmaksi, kun paketteja suoritetaan muutaman minuutin välein. En usko, että Microsoft on koskaan odottanut lokitietojen kertymistä 1440 ajosta päivässä (joka suoritetaan minuutin välein), mikä edellyttää siivousta useammin kuin 365 päivän välein tai levytila loppuu.

Ratkaisu

Ratkaisu sisältäisi oletusarvojen muuttamisen pienemmiksi luvuiksi, jolloin SSISDB säilyttää vähemmän tietoa. Henkilökohtaisen kokemukseni perusteella, jonka mukaan paketteja ajetaan minuutin välein (verkkokauppa-alustan vaatimus) ja minulla on enintään 10 projektia, säilytysajan (päiviä) pienentäminen 365:stä (oletusarvo) 7:ään päivään ja versioiden enimmäismäärän pienentäminen projektien osalta 10:stä (oletusarvo) 5:een riittäisi hallitsemaan SSISDB:tä/levytilaa ja samalla säilyttämään riittävästi tietoa virheenkorjausta ja vianetsintää varten.

Sinulla on kaksi vaihtoehtoa tämän muutoksen tekemiseen:

• Muuta oletusasetuksia SQL Server Management Studion (alias ”SSMS”) avulla laajentamalla Integration Services, napsauttamalla hiiren kakkospainikkeella SSISDB:tä ja napsauttamalla sitten Properties (Ominaisuudet) -valikkokohtaa (Kuva 2 – SSMS).

• Muuta oletusasetuksia alla olevalla T-SQL:llä päivittääksesi ssisdb.catalog.catalog_properties-taulun tietueet suoraan.

Kun muutos on valmis, sinun pitäisi pystyä vahvistamaan muutokset suorittamalla alla oleva T-SQL-kysely (Kuva 3 – SSISDB-katalogi)

Vaikka oletusasetusten muuttaminen saattaa korjata ongelman jatkossa, se ei välttämättä korjaa nykyistä SSISDB:n levytilaongelmaa riittävän nopeasti. Voit nopeuttaa siivousprosessia muuttamalla koodia ja muuttamalla @delete_batch_size 1000:sta esimerkiksi 10000:een muuttamalla ssisdb.internal.cleanup_server_retention_window tallennettua proseduuria (Kuva 4 – Retention Procedure).

Yhteenveto

Tässä artikkelissa tuotiin esiin SSISDB:n luettelon oletusasetuksiin liittyvä potentiaalinen ongelma, joka saattaa johtaa levytilan loppumiseen, ja tarjottiin ratkaisu, joka pakottaa SSISDB:n säilyttämään vähemmän tietoa.