تنظیمات Logging و گزارش گیری در SSIS Catalog

در SSIS Catalog، چندین گزینه برای logging بصورت داخلی وجود دارد. این گزینه‌ها logging level نامیده می‌شوند و جزئیات logging را هنگام اجرای پکیجهای SSIS در کاتالوگ کنترل می‌کنند. این اطلاعات مربوط به رویدادها و آمارهای مختلف است که آنها را در پایگاه داده SSISDB ذخیره  و ثبت می‌کند. مثلا هر بار که یک پکیج SSIS را در کاتالوگ اجرا می‌کنید، رویدادها  در جداولی در پایگاه داده SSISDB ثبت می‌شوند.

این اطلاعات از طریق ویوهای سیستمی مربوط به کاتالوگ و گزارش‌های (report) تصویری از پیش ساخته شده در معرض نمایش قرار می‌گیرند.

نکته مهم این است که بدانید یک سطح ثبت وقایع پیش‌فرض وجود دارد که می‌توانید برای کل کاتالوگ SSIS پیکربندی کنید. می‌توانید با کلیک راست روی کاتالوگ و انتخاب Properties، تنظیمات سطوح گزارشگیری (ثبت رویدادها) یا Logging Level را تنظیم کنید.

در پنجره Properties، می‌توانید logging level پیش‌فرض در سطح سرور را پیدا کنید.

Logging Levels

دارای چند سطح می باشد که میتوانید آنها را انتخاب کنید. با انتخاب هر کدام از آنها مشخص می‌کنید کدام رویدادها در Catalog SSIS ثبت بشوند:

• None

• Basic

• Performance

• Verbose

• RuntimeLineage

توجه داشته باشید که می‌توانید سطوح ثبت وقایع سفارشی خود را نیز ایجاد کنید، که در آن رویدادها و آمارهایی را که باید ثبت شوند، انتخاب می‌کنید.

None

اصلاً چیزی را ثبت نمی‌کند. اگر پرفورمنس پکیجهای SSIS شما از اهمیت بالایی برخوردار است، از این سطح ثبت وقایع استفاده کنید. با حذف logging، کمی از سربارهایی که معمولاً با ثبت وقایع مرتبط است، کاهش می‌یابد.

اگر می‌خواهید برخی از لاگ‌ها ثبت شوند – مثلاً زمان شروع و پایان پکیج یا تعداد ردیف‌های منتقل شده – خودتان باید بصورت custom این را در پکیج خود پیاده سازی کنید.

Basic

سطح گزارش‌گیری پایه، سطح پیش‌فرض logging پس از فعال کردن Catalog SSIS در موتور پایگاه داده SQL Server است. با این حال، Basic بهترین اصطلاح موجود نیست، زیرا اطلاعات زیادی با این سطح logging ثبت می‌شود. تقریباً همان اطلاعاتی است که وقتی یک بسته را در Visual Studio اجرا می‌کنید و به تب Execution Results نگاه می‌کنید، مشاهده می‌کنید.

در مورد بالا اطلاعات زیادی  بابت یک پکیج ساده نمایش داده شده. اگر به catalog view مربوطه برای اجرای همان پکیج در کاتالوگ نگاه کنیم، می‌توانیم ببینیم که ۲۱ ردیف در حال ثبت هستند.

در بیشتر موارد، به این اطلاعات زیاد نیاز ندارید، بنابراین ایده خوبی است که سطح گزارش‌گیری پیش‌فرض را به logging level دیگری تغییر دهید. اگر از SQL Server 2016 یا بالاتر استفاده می‌کنید، می‌توانید یک سطح logging سفارشی ایجاد کنید تا سربار را به حداقل برسانید.

Logging level، اطلاعات زیر را ثبت می‌کند:

• آمار اجرای موجودیتهای قبل اجرا – اطلاعات مربوط به اجرای (زمان و نتیجه) taskها در control floe
• همه رویدادها به جز OnProgress، OnCustomEvent، Diagnostic، DiagnosticEx، NonDiagnostic.

اگر می‌خواهید تعداد ردیف‌های نوشته شده توسط یک مقصد را بازیابی کنید، می‌توانید رویداد OnInformation مربوطه را در نمای کاتالوگ event_messages پیدا کنید و پیام را برای یافتن تعداد ردیف‌ها تجزیه کنید.

سطح Basic، تعداد ردیف‌های نوشته شده در یک جدول جداگانه را ثبت نمی‌کند.

Performance

این گزینه اطلاعات زیر را ثبت می‌کند:

• آمار اجرای موجودیتهای قبل اجرا – اطلاعات مربوط به اجرای (زمان و نتیجه) taskها در control flow
• آمار اجرای کامپوننتها – اطلاعات مربوط به زمان اجرای transformationها در control flow
• خطاها و هشدارها (errors and warnings)

این سطح ثبت وقایع برای استفاده در هنگام تنظیم عملکرد یک پکیج در نظر گرفته شده است، زیرا تمام زمان‌های اجرای اشیاء مختلف را ثبت می‌کند. با این حال، به نسبت Basic رویدادهای کمتری ثبت می‌شوند. استثنا زمانی است که جریان‌های داده زیادی با تبدیل‌های زیاد دارید (Data Flow and Transformation)؛ در اینجا سربار ثبت تمام آمارهای اجرا ممکن است قابل توجه باشد.

به خاطر داشته باشید که سطح ثبت رویاداد performance، تعداد ردیف‌های نوشته شده در یک مقصد را ثبت نمی‌کند.

Verbose

همه چیز را ثبت می‌کند. همه آمارها و رویدادها در کاتالوگ ثبت می‌شوند. این به این معنی است که سربار قابل توجهی وجود دارد: یک بسته ساده می‌تواند ده‌ها صفحه logging ایجاد کند. فقط زمانی از این سطح logging برای اهداف تشخیصی استفاده کنید که به تمام اطلاعات ممکن برای عیب‌یابی یک مشکل نیاز دارید.

این تنها logging level است که تعداد ردیف‌های نوشته شده در یک مقصد را گزارش می‌کند. این گزارش به ازای هر بافر در catalog view، execution_data_statistics ثبت می‌شود:

RuntimeLineage

همانطور که در نکته سطوح گزارش‌گیری خدمات یکپارچه‌سازی در SQL Server 2016 توضیح داده شد، سطح گزارش‌گیری RuntimeLineage برای ردیابی سلسله جریان داده مرتبط  (Lineage) در نظر گرفته شده است (یعنی داده‌ها از کجا می‌آیند و به کجا می‌روند؟ )، احتمالاً برای یک ویژگی آینده که هنوز اعلام نشده است.

ایجاد یک Logging Level سفارشی

یک قابلیت بسیار خوب اضافه شده به Catalog SSIS، امکان ایجاد logging level خودتان است. با چنین سطح ثبت وقایعی، می‌توانید تعیین کنید که کدام آمار و رویدادها در پایگاه داده SSISDB ثبت می‌شوند. برای مثال، همانطور که قبلاً اشاره شد، سطح basic از قبل اطلاعات زیادی را ثبت می‌کند. اگر فقط به eventها و warningها علاقه‌مند هستید، میتوانید کاری کنید فقط آنها را log کند. برای ایجاد آن، روی Catalog SSIS کلیک راست کرده و Customized logging Level را انتخاب کنید:

در پنجره بازشده، می‌توانید سطوح logging سفارشی موجود را مشاهده کنید و موارد جدیدی ایجاد کنید یا موارد موجود را حذف کنید.

اگر روی create کلیک کنید، می‌توانید نام و توضیحی را مشخص کنید:

در پنل Statistics، می‌توانید انتخاب کنید که در صورت وجود، چه نوع آماری در پایگاه داده SSISDB نوشته شود.

جالب‌ترین پنل، Events، به شما امکان می‌دهد رویدادهایی را که ثبت می‌شوند، مشابه پروایدرهای logging SSIS در یک پکیج، انتخاب کنید. فرض کنید ما، فقط به رویدادهای OnWarning و OnError نیاز داریم.

هر بار که یک پکیج را اجرا می‌کنید، می‌توانید سطح ثبت رویدادی را که باید استفاده شود انتخاب کنید. به طور پیش‌فرض، این سطح Basic Logging است. اگر پکیجی را با موفقیت اجرا کنیم، می‌توانیم ببینیم که تعداد زیادی ردیف را ثبت می‌کند، که برخی از آنها ممکن است جالب نباشند.

خوشبختانه، گزارش Messages در کاتالوگ SSIS بخش جداگانه‌ای برای پیام‌های خطا دارد. با این حال، اگر به warningها علاقه‌مند هستید، یا باید تمام آن پیام‌ها را جستجو کنید، یا باید از فیلتر برای یافتن آنها استفاده کنید. اکنون بیایید پکیج را با logging level سفارشی جدید اجرا کنیم. در کادر محاوره‌ای Execute Package، باید به پنل Advanced بروید و customized logging level را انتخاب کنید.

در کادر محاوره‌ای بازشده، می‌توانید از بین سطوح ثبت رویدادهای سفارشی موجود انتخاب کنید:

کلیک روی سه نقطه، اطلاعات بیشتری در مورد آن سطح رویداد خاص به شما می‌دهد. اکنون، وقتی پکیج را اجرا می‌کنیم، می‌توانیم ببینیم که log به میزان زیادی کاهش یافته است.

اگر پکیج را کمی تغییر دهیم تا یک warning ایجاد شود، اکنون فقط هشدارهایی را مشاهده می‌کنید که در گزارش ظاهر می‌شوند:

متأسفانه در حال حاضر امکان تعیین یک سطح ثبت رویداد سفارشی به عنوان سطح پیش‌فرض برای کاتالوگ SSIS وجود ندارد. این بدان معناست که باید هر بار که یک پکیج را اجرا می‌کنید، آن را تنظیم کنید. خوشبختانه می‌توانید یک سطح سفارشی را در یک step از Job SQL Server Agent مشخص کنید:

توجه داشته باشید که می‌توان با استفاده از پروسیجر catalog.create_customized_logging_level یک logging level سفارشی ایجاد کرد.

تنظیمات ویژگی های Logging و Versionهای پروژه Deploy شده برای کاهش فضای مصرفی

ما باید همیشه نگران فضای مصرفی بابت ازدیاد حجم لاگ های دیتابیس SSISDB باشیم. ضمن آنکه از این دستابیس باید log backup بگیریم و موارد دیگر پرفورمنسی را باید به مانند دیگر دیتابیسها برای این دیتابیس نیز در نظر داشته باشیم باید تنظیمات خاصی نیز جهت کنترل حجم Logging و versionهای پروژه های Deploy شده داشته باشیم. به این منظور به properties دیتابیس SSISDB رفته و از آنجا برخی از گزینه های مرتبط را بررسی می کنیم:

در بخش Operation Logs گزینه Clean Logs Periodically باید روی True تنظیم شده باشد. تا پس از مدت زمانی که خودمان معین می کنیم Logها و گزارشات رویدادهای قدیمی پاک بشوند. 

آن مدت زمان را در گزینه Retention Period (days) معین می کنیم. که به طور پیش فرض مدت 365 روز معین شده است. که اگر شما تعداد خیلی زیادی پکیج و پروژه دپلوی شده دارید پیشنهاد میشود عدد را به 30 روز تغییر دهید.

برای اینکه بدانید حجم لاگهای شما در حال حاضر چه مقدار است گزینه Current Size of Operation Log بر حسب کیلوبایت آنرا برای شما مشخص خواهد کرد و همچنین تعداد رکوردهای موجود در لاگ را  میتوانید در گزینه Current Number of Records بیابید.

جهت باخبر شدن از حجم اشغال شده توسط لاگ های Versionها در بخش Project Versions  عدد گزینه Current Size of Versions Log را مشاهده کنید.

در Maximum Number of Versions per Project مشخص میکنید تا چه تعداد Version بابت هر پروژه ذخیره گردد. (پیش فرض 10 است)

در Total Number of Versions Retained تعداد ورژن های ذخیره شده برای کل پروژه ها تا به اکنون مشخص شده است.

گزینه Periodically Remove Old Versions هم روی True  تنظیم کنید تا به طور خودکار کلیه ورژن های قدیمی را پاک کند.