SSISDB katalógus alapértelmezései Legjobb gyakorlatok

Bevezetés

Az SSISDB adatbázis (más néven az Integrációs szolgáltatások katalógusa) még az SQL Server 2012-ben került bevezetésre az MSDB adatbázis rendezetlenebbé tétele és egy házon belüli naplózási és jelentéstételi infrastruktúra biztosítása érdekében. Dióhéjban az SSISDB egy SSIS keretrendszer, amely az SQL Server Integration Services-t robusztusabbá és vállalatbarátabbá teszi azáltal, hogy biztosítja:

• Adatbázis mentés
• Adatbázis titkosítás
• Környezetek támogatása
• Projekt- és csomagparaméterek támogatása
• Pakettámogatás. verziókezelés
• Az SSMS-be épített SSRS teljesítményjelentések
• Telepítés közvetlenül az SSDT-ből

Míg az SSISDB keretrendszer sokkal képessé tette az SSIS-t, ez néhány Microsoft-feltételezéssel járt (a.k.a. alapértelmezések). Ezek az alapértelmezések azért vannak, hogy segítsenek talpon maradni, de nem biztos, hogy optimálisak, amikor futni kezdünk, és messze nem tökéletesek, amikor sprintelünk.

Ez a cikk az alapértelmezésekkel foglalkozik, azzal, hogy miért nem biztos, hogy ezek az alapértelmezések optimálisak, és hogyan lehet megváltoztatni ezeket az alapértelmezéseket.

Probléma

Az SSISDB Catalog egy beépített folyamatot tartalmaz a műveletek és a projektverzióztatás tisztítására. Ez a tisztítási folyamat az SSISDB alapértelmezett beállításaira támaszkodik, amelyek megváltoztatásuk nélkül működésképtelenné tehetik az SSIS-t.

Szinte minden új SSIS-projekt azzal kezdődik, hogy csak néhány csomagot épít és futtat, miközben rengeteg szabad lemezterület áll rendelkezésre. Gyorsan előre 3-6 hónapot, és már több SSIS-projektje van, több tíz vagy több száz csomagja, és lehet, hogy friss adatokra is szüksége van a jelentéskészítéshez és az analitikához (értsd: folyamatosan, éjjel-nappal futó csomagok). Ezek az SSIS csomagok a Microsoft tisztítási alapértelmezéseit messze meghaladó mértékben felhalmozhatnak verziókezelési adatokat (ez kevésbé probléma) és naplózási adatokat (ez inkább probléma), ami az SSISDB adatbázis méretének felduzzadásához és váratlan növekedéséhez vezethet (olvasd: felemészti az összes rendelkezésre álló lemezterületet).

Az SSIS futásidejű adatainak rögzítéséért felelős egyetlen alapértelmezés a Server-wide Default Logging Level = Basic. Ez a beállítás végső soron az, ami az SSISDB adatbázis méretét kezeli, és végső soron a lemezterületet foglalja. Ennek ellenére az SSIS Catalog (1. ábra) más lehetőségeket is biztosít az SSISDB adatbázis méretének/lemezterületének szabályozására:

1. Server-wide Default Logging Level
2. Tisztítási alapbeállítások:
   1. Naplók rendszeres tisztítása = True
   2. Régi verziók rendszeres eltávolítása = True
   3. Megőrzési időszak (napokban) = 365
   4. Versziók maximális száma projektenként = 10

Míg a szerver szintű alapértelmezett naplózási szint módosítása Basic-ről None-ra a lemezterület visszanyerése érdekében nagyon nem praktikus, és a hibaelhárítást rémálommá teszi, más SSISDB katalógusbeállítások, például a Projektek maximális verziószáma és a Megőrzési időszak (napok) megváltoztatása minimális hatással lehet a hibaelhárításra, miközben az SSISDB adatbázis/lemezterület ellenőrzés alatt tartható.

Mindezek az alapbeállítások a tisztítási folyamatot (más néven SSIS Server Maintenance Job) vezérlik. Míg a Naplók időszakos tisztítása = True és a Régi verziók időszakos eltávolítása = True gyakorlatilag mindig tökéletes, addig a Maximum Number of Versions per Projects = 10 és a Retention Period (napok) = 365 beállítások túl optimisták lehetnek. És itt van, hogy miért.

A fő ok, amiért mind a Naplók rendszeres tisztítása = True, mind a Régi verziók rendszeres eltávolítása = True alapbeállítások tökéletesek, az az, hogy ezen alapbeállítások nélkül (vagy ténylegesen, ha mindkettő False-ra módosul), az SSISDB a végtelenségig növekedni fog, mivel csak napló- és verziókezelési adatokat fog felhalmozni anélkül, hogy bármilyen adatot eltávolítana, és kifogy a lemezterületből.

A fő ok, amiért mind a Maximum Number of Versions per Projects = 10, mind a Retaining logging data for 365 days (naplózási adatok megőrzése 365 napig) túl optimista lehet, az a Microsoft döntése, hogy az adatokat nem méret, hanem időtartam alapján tartja meg. Ez azt jelenti, hogy a rögzített időszaki szám nem veszi figyelembe, hogy mennyi adat halmozódik fel. Míg a Maximum Number of Versions per Projects problémává válhat, ha több ezer csomagja van, a Retention Period (napok) hatalmas problémává válik, ha olyan csomagjai vannak, amelyek néhány percenként futnak. Nem hiszem, hogy a Microsoft valaha is számított volna a napi 1440 futás (percenkénti futás) naplóadatainak felhalmozódására, ami 365 naposnál gyakrabban igényel tisztítást, különben elfogy a lemezterület.

megoldás

A megoldás az alapértelmezett értékek kisebb számokra történő módosítását jelentené, így az SSISDB kevesebb információt tart meg. Személyes tapasztalataim alapján, amikor a csomagokat percenként futtatom (e-kereskedelmi platform követelménye), és legfeljebb 10 projektem van, a megőrzési időszak (napok) csökkentése 365-ről (alapértelmezett) 7 napra és a verziók maximális száma projektenként 10-ről (alapértelmezett) 5-re elegendő lenne az SSISDB/lemezterület ellenőrzéséhez, miközben elegendő információt tart meg hibakeresési és hibaelhárítási célokra.

Ezeknek a változtatásoknak a végrehajtására két lehetősége van:

• Az alapbeállítások megváltoztatása az SQL Server Management Studio (más néven “SSMS”) segítségével az Integrációs szolgáltatások kibontásával, az SSISDB-re kattintva a jobb gombbal, majd a Properties menüpontra kattintva (2. ábra – SSMS).

• Az alapbeállítások módosítása az alábbi T-SQL segítségével az ssisdb.catalog.catalog_properties tábla rekordjainak közvetlen frissítéséhez.

Mihelyt a módosítás megtörtént, az alábbi T-SQL lekérdezés futtatásával (3. ábra – SSISDB katalógus)

Míg az alapértelmezések megváltoztatása megoldhatja a problémát a továbbiakban, nem biztos, hogy elég gyorsan megoldja a meglévő SSISDB lemezterület problémáját. A kód módosításával és a @delete_batch_size_méret 1000-ről 10000-re történő megváltoztatásával felgyorsíthatja a tisztítási folyamatot az ssisdb.internal.cleanup_server_retention_window tárolt eljárás módosításával (4. ábra – Retention Procedure).

Összefoglaló

Ez a cikk rávilágított egy lehetséges problémára az SSISDB katalógus alapértelmezett beállításaival kapcsolatban, amely a lemezterület elfogyásához vezethet, és egy olyan megoldást adott, amely arra kényszeríti az SSISDB-t, hogy kevesebb információt tartson meg.