سیستم ثبت لاگ برای جداول مختلف

[IMANAZADI]

با سلام

دوستان میخوام واسه قسمت های مختلف سایت ، سیستم لاگ در نظر بگیریم
مثلا در یک صفحه از سایت که اطلاعات ورودی فرم در چند جدول مختلف insert یا update یا delete میشه چطوری میتونم سیستم لاگ در نظر بگیریم که فلان رکورد رو چه یوزری insert ، update ، delete کرده (همراه با تاریخ و ساعت) .
و اگر یوزری رکوردی رو حذف کرد اون رکورد رو در جایی داشته باشم

خواستم واسه هر جدول 4 فیلد واسه user1,user2,date , time در نظر بگیرم ولی فکر نمیکنم اصولی باشه

ممنون میشم راهنمایی کنید
که سایت های مهم که اطلاعات جداول مهم هست چطوری اینکار رو انجام میدن

[pswin.pooya]

دوستان میخوام واسه قسمت های مختلف سایت ، سیستم لاگ در نظر بگیریم
مثلا در یک صفحه از سایت که اطلاعات ورودی فرم در چند جدول مختلف insert یا update یا delete میشه چطوری میتونم سیستم لاگ در نظر بگیریم که فلان رکورد رو چه یوزری insert ، update ، delete کرده (همراه با تاریخ و ساعت) .
و اگر یوزری رکوردی رو حذف کرد اون رکورد رو در جایی داشته باشم

هیچ رکوردی رو حذف نکن. فقط یه فلگ بزار که بتونی برش گردونی

می تونی از سیستم version استفاده کنی. برای هر رکورد یه ورژن بزار و آخرین اون رو بردار البته برای همه جدولها نمیشه این رو اعمال کرد. اما جدول version رو می تونی جدا کنی

[pbm_soy]

هر دستوری بروی دیتابیس اجرا میکنید آنرا در یک فایل متنی به همراه تاریخ و ساعت و نام کاربری ذخیره کنید

البته فکر میکنم sql server , mysql و بقیه سیستم جامع و کاملی برای ثبت وقایع داشته باشند!

[حمیدرضاصادقیان]

سلام
یک راه ساده و بدون هزینه استفاده از CDC هست که جز قابلیتهای خود SQL Server هست.
همچنین میتونید یک جدول Log داشته باشید و با یک فلگ مشخص کنید وضعیت اون رکورد چیه( اضافه شده، حذف شده یا تغییر کرده)
بعد محتویات کل اون رکورد رو در قالب XML ذخیره کنید. البته با اینکار نیاز هست کلی تریگر بنویسید و یک ذره سخت میشه.
یا اینکه باید در هر دستور insert,Update,delete از دستور Output استفاده کنید و در داخل این جدول اضافه کنید.

[pswin.pooya]

یک راه ساده و بدون هزینه استفاده از CDC هست که جز قابلیتهای خود SQL Server هست.

CDC برای دیتابیسهای دیگه هم هست. مثلا لینکداین چون از MySQL استفاده می کنه خودش CDC خودش رو توسعه داده که با ارواکل هم کار می کنه:

https://engineering.linkedin.com/data-replication/open-sourcing-databus-linkedins-low-latency-change-data-capture-system

البته سیستم های سازگار دیگه هم هست.

[tanavar_ayat]

آقای پویا مثلا خیال کردی ج پی ام نمیدی دیگه خیلی:چشمک:

[pswin.pooya]

آقای پویا مثلا خیال کردی ج پی ام نمیدی دیگه خیلی:چشمک:




سوال هاتون رو باید توی تالار مطرح کنید. اگر در حد توان باشه حتما ج می دم

[IMANAZADI]

هیچ رکوردی رو حذف نکن. فقط یه فلگ بزار که بتونی برش گردونی

می تونی از سیستم version استفاده کنی. برای هر رکورد یه ورژن بزار و آخرین اون رو بردار البته برای همه جدولها نمیشه این رو اعمال کرد. اما جدول version رو می تونی جدا کنی

منظورتون اینه یعنی یگ فلگ بزارم و آیتم هایی که حذف میشن رو ، مقدار فلگ اونو true کنم و برای نمایش رکوردهایی که true نیستند رو نمایش بدم ؟؟؟

میشه دوست عزیز یه مقدار بیشتر راجع به سیستم version توضیح بدی یه چیزایی متوجه شدم ولی کامل نه و اینکه جدول ورژن رو چطوری جدا کنم ؟؟؟
اگه یه مثال بزنید ممنون میشم یا اگر لینک مرجع یا چیزی دارید معرفی کنید

[IMANAZADI]

آیا منظورتون یه همچین چیزی هست


اگر اینطور باشه حجم دیتابیس بالا نمیره ؟؟؟

هر سری هم که کوچکترین تغییرات انجام بشه باید کل رکوردها از اول تا آخر ذخیره بشه !!!

[pswin.pooya]

یه جدول برای تاریخ های ویرایش نگه دار. بعد. برای هر رکورد ویرایش های مختلفش رو توی اون جدول نگه دار.

[tanavar_ayat]

سوال هاتون رو باید توی تالار مطرح کنید. اگر در حد توان باشه حتما ج می دم


اون سوال ک تو پی ام پرسیدم اگه تو تالار میپرسیدم حذفش میکردی میگفتی فاقد محتوا . آخ از دستت من چکارت کنم ک دست از این کارا برداری

[pbm_soy]

دوستان فکر میکنم سیستم لاگ نباید بروی کارائی سیستم اصلی تاثیر منفی بگذارد مثلا یادم میاد همین امکان لاگ کردن وقایع نرم افزار را شدیدا کند میکرد و یا مخصوصا زمان تهیه نسخه پشتیبان از داده ها قفل میکرد و یا مشکلات دیگری را پیش میاورد!
من ثبت وقایع در فایل متنی را پیشنهاد میدهم چون حجم دیتابیس بالا نمیرود و باعث پایین آمدن کارائی دیتابیس نمیشود و دلیل بعدی به ترتیب زیر است
هدف از لاگ کردن چیست؟ در بیشتر مواقع پیدا کردن ایراد و خطا و یا بالا بردن امنیت با استفاده از بررسی وقایع و در سیستمهای اطلاعاتی و دیتابیسی میتواند برای بازیابی اطلاعات هم کاربرد داشته باشد
ولی مورد اصلی زمانی که نرم افزار کار نمیکند و یا کرش کرده باشد و خطا داده باشد به لاگ فایل مراجعه میکنند.
بنابه دلایل فوق لاگ فایل باید یک فایل متنی باشد چون میتوان خارج از نرم افزار قابل مشاهده است اکر لاگها در دیتابیس ثبت شود زمانی که نرم افزار خراب شده باشد نمیتوان لاگها را بررسی کرد در کل میخوام بگم که مشاهده کردن لاگ فایل بایستی مستقل از عملکرد نرم افزار اصلی باشد
البته حرف حدیث لاگ زیاد است چون خیلی وقتها اطلاعات موجود در لاگ فایلها باارزش هستند پس میتوان آنها را کدگذاری کرد و یا در جای دیگری مانند لاگ سرور تمام لاگها را ثبت کرد. و بسته به نوع سیستم و هدف و کاربرد آن سیستم ثبت وقایع آن نیز میتواند متفاوت باشد

در پست بالا هم عرض کرده بودم sql server از فایل LDF برای ثبت لاگ تمام تراکنشهای دیتابیس استفاده میکند که برای بحث Data Recovery کاربرد دارد نمیدانم امکان استفاده از ان برای موارد دیگر وجود دارد یا خیر؟!

[pswin.pooya]

در مورد سیستم لاگ گیری فایلی اگر اینکار رو با فایل معمولی انجام بدید به شدت سرعت رو میاره پایین چون مجبورین داده رو فلاش کنید (وگرنه با کرش کردن از بین می ره). بعضی ها لاگر رو توی ترد جدا می زارن اینم روش زیاد خوبی نیست. (با کرش برنامه یه قسمت از لاگ می تونه از دست بره) من خودم بعد از کلی تست روشهای مختلف یه سیستم IPC باینری اولیه ساختم (فعلا در حد تست هست این سیستم)‌ و کار لاگ رو یه نرم افزار دیگه انجام میده. از طرف دیگه برنامه لاگر هم می تونه یه برنامه پیچیده باشه. مثلا می تونید استراتژی لاگ گیری داشته باشید یا رسانه های مختلف (من اسمش رو گذاشتم کانال) برای ثبت لاگ. همینطور یکسری استانداردها هم برای لاگ گیری هست که می تونه کمک زیادی بکنه. مثلا اینکه چجوری ایراد رو به کاربر گزارش کنیم که بشه پی گیری کرد و ...

من فکر کنم بیشتر بحث اینجا برگردوندن داده باشه (مثل فایل پاک شده از Recycle-bin باشه) تا لاگ گیری. البته که لاگ گیری نباید رو دیتا بیس باشه. (فکر کنم اشتباه ترین راه حل این باشه چون معمولا (بسته به نوع موتور) کندترین عملیات مربوط به insert هست.

[IMANAZADI]

یه جدول برای تاریخ های ویرایش نگه دار. بعد. برای هر رکورد ویرایش های مختلفش رو توی اون جدول نگه دار.

آقا بازم ببخشید من خوب متوجه نشدم

فرض کنید جدول اصلی ما شامل فیلدهای id,materialCode,QTY,price,deleteFlag باشه

جداول دیگه شامل چه فیلدهایی میشه ؟؟؟

جدول تاریخ ویرایش ها(tbl_editDate) شامل فیلد های id, revision_date (اگه اشتباه نکرده باشم )؟؟؟؟؟

و جدول رکوردهای ویرایش شده شامل چه فیلدهایی است

بعد الگوریتم به چه صورت هست

یعنی اگر یک رکوردبه جدول اصلی اضافه شد یا یک رکورد ویرایش شد باید چکار کرد (چه عملیاتی باید انجام بشه)

اگر یک رکورد حذف شد چی فقط کافی فیلد فلگ اون رکورد رو تغییر بدیم یا در جدول ویرایش رکوردها هم باید چیزی ثبت بشه ؟؟؟؟

[IMANAZADI]

دوست عزیز من همچنان منتظر جواب شما هستم

[stabesh]

سلام
یک راه ساده و بدون هزینه استفاده از CDC هست که جز قابلیتهای خود SQL Server هست.
همچنین میتونید یک جدول Log داشته باشید و با یک فلگ مشخص کنید وضعیت اون رکورد چیه( اضافه شده، حذف شده یا تغییر کرده)
بعد محتویات کل اون رکورد رو در قالب XML ذخیره کنید. البته با اینکار نیاز هست کلی تریگر بنویسید و یک ذره سخت میشه.
یا اینکه باید در هر دستور insert,Update,delete از دستور Output استفاده کنید و در داخل این جدول اضافه کنید.

با عرض سلام
آقای صادقیان میشه یکم بیشتر در مورد CDC توضیح بدید؟
من چند تا لینک پیدا کردم ولی چیز زیادی نفهمیدم!!!
https://technet.microsoft.com/en-us/library/cc627369%28v=sql.105%29.aspx
https://msdn.microsoft.com/en-us/library/cc645937%28v=sql.105%29.aspx
https://en.wikipedia.org/wiki/Change_data_capture
https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/
با تشکر

[stabesh]

یه مقاله فارسی هم پیدا کردم گویا خیلی قابل اطمینان نیست؟!


Change Data Capture یا CDC چیست ؟
یکی از قابلیت های ویژه SQL Server Enterprise Edition (که البته درنسخه های Developer و editions هم بهخوبی کار میکند ) است که روی جدولی در دیتابیس ما فعال میشود و تغییرات ناشی از دستورات DML (Insert,Update,Delete) آن را نگهداریمیکند و ما میتوانیم از این اطلاعات استفاده کنیم. وقتی که ما cdc را روی جدولیفعال میکنیم، SQL Server جدولی مشابه آن به همرای یکسری Metadata ایجاد میکند وتغییرات را در آن ذخیره مینماید. در کنار آن تعدادی Table-valued function در اختیار ما قرار میگیرند که بهما امکان استفاده از داده های ذخیره شده را می دهند.



CDC چگونه کار میکند؟
هنگامی که CDC روی یک جدول فعال میشود، SQL Server از مکانیزم نامتقارنی (Async) استفاده میکندکه به کمک آن تغییرات رخ داده در جداول را از فایل log میخواند و درجداولی که به منظور نگهداری تغییرات ایجاد کرده ذخیره میکند. در فایلهای log هر رکوردی کهذخیره میشود یک شناسه یکتا با نام Log Sequence Number دارد که به اختصار LSN نامیده میشود.بسیاری از Metadataهایی که برای ما نگهداری میشود به خاطر قرابت نزدیکی که اینتکنولوژی با Log دارد از همین اطلاعات Log استخراج می شوند.



برای فعال سازی CDC روی یک جدول ابتدا به کمک دستور sys.sp_cdc_enable_db باید آنرا در پایگاهداده مورد نظر فعال کنیم. با اجرای این دستور سیستم CDC بر روی پایگاهداده ما فعال میشود و زیرساخت های مورد نیاز آن مانند meta-data table ها، CDC schema ، CDC database user و … روی پایگاه داده ما ایجادمیشوند. در صورتی که نیاز داشتهباشید میتوانید در sys.databases و به کمک ستون is_cdc_enabled بررسی کنید که آیا CDC برای دیتابیسشما فعال است یا خیر.
حالا شما میتوانید ازدستور sys.sp_cdc_enable_table برای فعال کردن CDC روی جدول خود استفادهکنید. با بررسی ستون is_tracked_by_cdc در sys.tablesمیتوانید از فعال بودن یا نبودن CDC روی جدول خود مطلع شوید.



با فعال شدن این امکان، با هر تغییری در داده های جدول موردنظر شما، تغییرات آن در جدول CDC متناظر از Log استخراج شده و ذخیره می گردد. به صورت پیش فرض تمامی ستون هایجدول نگهداری میشوند اما در صورت نیاز مثلا به دلایل امنیتی یا پرفورمنسی شمامیتوانید تنها اطلاعات بعضی از ستون های جدول را نگهداری کنید. این کار را به کمکپارامتر @captured_column_list هنگام ایجاد CDC روی جدول انجام میدهیم. همچنین بهصورت پیش فرض این اطلاعات روی FileGroup اصلی ما نگهداری میشوند که باز هم در صورت نیاز میتوانید بااستفاده از پارامتر @filegroup_name یک FileGroup دیگر را جهت نگهداری اطلاعات معرفی کنید.



فعال بودن SQL Server Agent برای فعال کردن این امکان اجبارینیست. اما فعال بودن آن برای کارکرد صحیح CDC لازم است. به همین منظور در صورتی که هنگام فعال کردن این امکانروی جدول اگر این SQL Server Agent استارت نباشد، با اینکه کار فعالسازی درست انجام میشود اما به شما پیامی مبنی بر عدم کارکرد صحیح سیستم داده میشود.


آیا جدول CDC بسیار بزرگ میشود؟
با توجه به اینکه هر تغییراتی که در جداول ما اتفاق می افتددر جدول CDC نگهداری میشود، احتمالا باید این سوال برای شما پیش آمده باشد کهآیا این جدول بسیار بزرگ نمیشود؟! پاسخ این است خیر.
یک روال سیستمی وجود دارد که به صورت اتوماتیک هر ۳ روزیکبار داده ها قدیمی را پاک میکند. البته میتوانید این اندازه را به هر اندازه ایکه نیاز دارید تغییر دهید و یا میتوانید به کمک روال ذخیره شده sys.sp_cdc_cleanup_change_table این کار را بهصورت دستی انجام دهید.




در صورت تغییر در جدولی که تغییرات آن را نگهداری میکنیم چهاتفاقی می افتد؟
با تغییر در ساختار جدول روال CDC به کار خودادامه میدهد، با این تفاوت که در صورتی که ستونی اضافه شود، دیگر تغییرات آننگهداری نمی شود، و اگر ستونی هم حذف شود مقدار NULL برای آننگهداری می شود. یعنی CDC توجهی به تغییرات اتفاق افتاده ندارد و کار خود را به همان شکلسابق ادامه می دهد. در صورت نیاز شما میتوانید یک روال دیگر برای نگهداری تغییراتستون های جدید ایجاد کنید. اما به خاطر داشته باشید که تنها ۲ نمونه از نگهداریسابقه را میتوانید برای یک جدولفعال کنیم.
http://nikamooz.com/change-data-capture/

[stabesh]

سلام دوباره
میشه در مورد SQL Server Audit هم صحبت کنید؟
:گیج: کاملا گیج شدم
https://technet.microsoft.com/en-us/library/dd392015%28v=sql.100%29.aspx
https://msdn.microsoft.com/en-us/library/cc280500%28v=sql.105%29.aspx
https://msdn.microsoft.com/en-us/library/cc280386%28v=sql.105%29.aspx
http://barnamenevis.org/showthread.php?462398-%D9%84%D8%A7%DA%AF-%DA%AF%D8%B1%D9%81%D8%AA%D9%86-%D8%A7%D8%B2-%D8%AF%DB%8C%D8%AA%D8%A7%D8%A8%DB%8C%D8%B3-%D9%85%D8%AB%D9%84-CDC-%D9%85%D8%B4%D8%AE%D8%B5-%D8%B4%D9%88%D8%AF-%DA%A9%D8%AF%D8%A7%D9%85-%D8%A2%DB%8C-%D9%BE%DB%8C-%D8%AA%D8%BA%DB%8C%DB%8C%D8%B1%D8%A7%D8%AF-%D8%AF%D8%A7%D8%AF%D9%87
و همین طور SQL Server Change Tracking
http://solutioncenter.apexsql.com/what-is-change-tracking-and-how-to-set-it-up/
دوستان کسی استفاده کرده تا حالا؟
متشکرم

[stabesh]

یه مقاله پیدا کردم تفاوت اینها رو گفته
پس فکر کنم خودمون جدول لاگ درست کنیم بهتر باشه اینا خیلی قابل اتکا نیستند آقا ایمان حداقل شما جواب بده شاید با هم فکری هم تونستیم طراحی کنیم
CT VS CDC

(http://sqlversity.com/comparison-between-change-tracking-and-change-data-capture/)