ویژگی Change Tracking در SQL Server | بررسی عملکرد و نحوه استفاده SQL Server امنیت SQL Server نوشته شده توسط: تیم فنی نیک آموز تاریخ انتشار: ۰۷ فروردین ۱۴۰۱ آخرین بروزرسانی: 12 اسفند 1403 زمان مطالعه: 9 دقیقه ۳ (۲) ویژگی Change Tracking در SQL Server، این دو ویژگی Change Data Capture (CDC) و Change Tracking (CT) هر دو در SQL Server 2008 برای ردیابی دادهها معرفی شدند. در حالی که CDC فقط برای نسخه Enterprise بود، CT برای همه نسخههای SQL Server 2008 در دسترس بود. هدف این مجموعه مقاله ۳ قسمتی این است که ببینیم این دو ویژگی در SQL Server، چه شباهتها و اختلافهایی با Temporal Tables در SQL Server 2016 دارند. ویژگی Change Tracking در SQL Server در قسمت اول این مجموعه مقالات، ما قصد داریم به طور عمیق به ویژگی Change Tracking در SQL Server بپردازیم. سپس در قسمت ۲ به بررسی عملکرد و کاربردهای Change Data Capture خواهیم پرداخت. قبل از عمیقتر شدن در موضوع، توجه به این نکته مهم است که بدانید Temporal Tables جایگزین CDC یا CT نیست. CDC برای ذخیره تاریخچه دادهها برای مدت زمان کوتاه در نظر گرفته شده است. بسته به زمانبندی ETL اپلیکیشنی که روی آن کار میکنید، آن سوابق را که برای ذخیره، علامتگذاری شدهاند را از لاگ تراکنش به برخی از جداول داده انبار داده منتقل میکنید. Temporal Tables تغییرات DML را در جدول temporal history ذخیره میکند و در نظر گرفته شده است که برای مدت زمان طولانیتری در آنجا بمانند. Change Tracking یکی دیگر از ویژگیهایی است که تنها آخرین تغییر ردیف را ذخیره میکند. اگر چه استفاده محدودی دارد، برخی از برنامهها ممکن است فقط به این قابلیت تازهسازی ساده نیاز داشته باشند و برای اهداف برنامه به Change Tracking دادهها یا Change Data Capture نیاز ندارند. مشاهده کاملترین و بروزترین آموزش sql server در نیک آموز ویژگی Change Tracking در SQL Server چگونه کار میکند؟ ویژگی Change Tracking ابتدا باید در سطح پایگاه داده و سپس برای هر جدولی که میخواهید تغییرات را ردیابی کنید فعال شود. Change Tracking تعداد دفعات تغییر در جدول را از زمانی که فعال شده است، پیگیری میکند. Change Tracking برایی ردیف بر اساس ستون کلید اصلی ردیابی میشود. هیچ تغییری در شِمای جدول مورد نیاز نیست، اما کد برنامه موجود برای استفاده از Change Tracking باید به روز شود. دو روش وجود دارد که برنامهها و پایگاههای داده میتوانند تغییرات دادهها را همگام سازی کنند. در همگامسازی یک طرفه، برنامه وظیفه دارد تا حافظه cache را با تغییرات به روز شده در جدول، به روز نگه دارد. در همگام سازی دو طرفه، تغییرات ایجاد شده از طریق برنامه میتواند به جدول منتقل شود و Change Tracking میتواند این تغییرات را علاوه بر تغییرات ایجاد شده در خود جدول، ردیابی کند. پیشنهاد میکنیم برای درک بهتر مفاهیم کوئری نویسی را مطالعه کنید. باید از توابع Change Tracking بسیار پیچیده استفاده کنید تا آنچه را در جدولی که ردیابی میشود تغییر میکند، ردیابی کنید. در مثال زیر خواهید دید که این یک ویژگی مفید است، اما کمی پیچیده است و همچنین نیاز به تغییر کد برنامه دارد. افراد علاقهمند میتوانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئرینویسی گسترش دهند. CREATE DATABASE TrackChange GO USE TrackChange GO CREATE TABLE Customer ( CustomerId INT IDENTITY (1,1) ,FirstName VARCHAR(30) ,LastName VARCHAR(30) NOT NULL ,Amount_purchased DECIMAL ) GO ALTER TABLE dbo.Customer ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerId, LastName) GO INSERT INTO dbo.Customer ( FirstName, LastName, Amount_Purchased) VALUES ( 'Frank', 'Sinatra',20000.00),( 'Shawn', 'McGuire',30000.00),( 'Amy', 'Carlson',40000.00) GO SELECT * FROM dbo.Customer -- Now enable change Tracking at Database Level ALTER DATABASE TrackChange SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) -- Then enable change Tracking at Table Level ALTER TABLE dbo.Customer ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) -- Verify the status of the change tracking -- You will find that there is no version history yet. SELECT CHANGE_TRACKING_CURRENT_VERSION () AS CT_Version SELECT * FROM CHANGETABLE (CHANGES Customer,0) as CT ORDER BY SYS_CHANGE_VERSION SELECT c.CustomerId, c.LastName , ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT FROM Customer AS c CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct; ستون Sys_Change_Version مقدار Null را نشان میدهد زیرا پس از فعال کردن CT، هیچ تغییری در جدول ایجاد نشده است. حال اجازه دهید برخی تغییرات DML را در این جدول ایجاد کنیم و سپس دوباره مقدار ستون Sys_Change_Version را که توسط تابع ChangeTable برگردانده شده است بررسی کنیم. -- Now make some changes in the table -- insert a row INSERT INTO Customer(FirstName, LastName, Amount_purchased) VALUES('Ameena', 'Lalani', 50000) GO -- delete a row DELETE FROM dbo.Customer WHERE CustomerId = 2 GO -- update a row UPDATE Customer SET Lastname = 'Clarkson' WHERE CustomerId = 3 GO -- Let us query to see what it reports SELECT CHANGE_TRACKING_CURRENT_VERSION () AS CT_Version SELECT * FROM CHANGETABLE (CHANGES Customer,0) as CT ORDER BY SYS_CHANGE_VERSION SELECT c.CustomerId, c.LastName , ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT FROM Customer AS c CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct; اکنون میبینیم که نسخه Change Tracking برابر با ۳ است. ما ۳ عملیات انجام دادیم. ۱ درج، ۱ حذف و ۱ به روز رسانی. ۲ ردیف در جدول change به روز رسانی میشود. یک حذف و یک درج، همان طور که از ستون SYS_CHANGE_OPERATION در تصویر زیر قابل مشاهده است. بیایید ۳ تغییر دیگر در جدول Customer انجام دهیم. -- Update the above row one more time UPDATE Customer SET Lastname = 'Blacksmith' WHERE CustomerId = 3 GO -- Let INSERT few more rows INSERT INTO Customer(FirstName, LastName, Amount_purchased) VALUES('Sponge', 'Bob', 5000) GO INSERT INTO Customer(FirstName, LastName, Amount_purchased) VALUES('Donald', 'Duck', 6000) GO -- Let us query to see what it reports now SELECT CHANGE_TRACKING_CURRENT_VERSION () as CT_Version SELECT * FROM CHANGETABLE (CHANGES Customer,0) as CT ORDER BY SYS_CHANGE_VERSION SELECT c.CustomerId, c.LastName , ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT FROM Customer AS c CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct; ما دوباره همان ۳ عمل را انجام دادیم و اکنون شاهد هستیم نسخه Change Tracking برابر با ۶ است. -- Let us make one more update UPDATE Customer SET Lastname = 'Cool' WHERE CustomerId = 6 GO SELECT CHANGE_TRACKING_CURRENT_VERSION () as CT_Version SELECT * FROM CHANGETABLE (CHANGES Customer,0) as CT ORDER BY SYS_CHANGE_VERSION SELECT c.CustomerId, c.LastName , ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT FROM Customer AS c CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct; مشاهده کردیم که SYS_CHANGE_VERSION = 6 وقتی ردیف را به روز کردیم (customerId = 6) حذف شد و نسخه جدید برابر ۷ با همان اطلاعات ثبت شد. از این رو ما ثابت کردیم که ویژگی Change Tracking تنها آخرین تغییر را برای ردیف ذخیره میکند، بنابراین هیچ داده تاریخی واقعی با ویژگی Change Tracking در دسترس نخواهد بود. تابع CHANGE_TRACKING_CURRENT_VERSION () همیشه نسخه فعلی ردیف را برمیگرداند. همچنین توجه داشته باشید که هیچ کنترلی بر روی تغییر رفتار ویژگی Change Tracking ندارید. در ابتدا، ما در مورد نحوه عملکرد ویژگی Change Tracking در SQL Server صحبت کردیم. Change Tracking فقط آخرین تغییر ایجاد شده در ردیف را ذخیره میکند. تاریخچه تغییرات قبلی را حفظ نمیکند. اگر چه کاربرد محدودی دارد، برخی از برنامهها ممکن است فقط به این قابلیت تازهسازی ساده نیاز داشته باشند و برای اهداف ردیابی دادهها به temporal table یا CDC نیاز ندارند. در این قیسمت میخواهیم ببینیم که یکی دیگر از ویژگیهای ردیابی تغییر، Change Data Capture در SQL Server چگونه کار میکند. ویژگی Change Data Capture در SQL Server Change Data Capture (CDC) در SQL Server 2008 معرفی شد. CDC نیز مانند Change Tracking (CT)، تغییرات فعالیت DML را در یک جدول ثبت میکند. مانند CT، این ویژگی باید ابتدا در سطح پایگاه داده و سپس در سطح جدول فعال شود. اما برخلاف CT، CDC دارای ویژگیهای بسیار پیچیدهای است. با این وجود، CDC یک ویژگی عالی است و موارد استفاده خاص خود را دارد و Temporal Table ها جایگزین مناسبی برای CDC ها نیستند. هنگامی که یک جدول در یک پایگاه داده برای ذخیره دادههای تغییر یافته، فعال میشود، تمام تغییرات آن جدول در یک جدول تغییرات، ذخیره شده و به این ترتیب تغییرات ردیابی میشوند. جدول تغییرات شامل یک رکورد برای هر INSERT خواهد بود که میتواند برای شناسایی مقادیر ستون برای رکوردهای درج شده استفاده شود. هر بار که یک DELETE انجام میشود، جدول تغییرات شامل یک رکورد برای هر دستور DELETE خواهد بود که مقادیر هر ستون را قبل از DELETE نشان میدهد. هنگامی که یک UPDATE انجام میشود، در جدولی که قابلیت ذخیره دادههای تغییر یافته را دارد، دو رکورد در جدول تغییرات ایجاد میشود، یکی با مقادیر ستون به روز شده و دیگری با مقادیر ستون اصلی قبل از به روز رسانی، ذخیره میشود. با استفاده از ذخیره دادههای تغییر یافته، میتوانید تغییراتی را که در طول زمان رخ دادهاند در جدول خود ردیابی کنید. این نوع عملکرد برای برنامهها مفید است، مانند فرآیند بارگذاری انبار داده که نیاز به شناسایی تغییرات دارند، بنابراین میتوانند به درستی به روز رسانیها را برای ردیابی تغییرات تاریخی در طول زمان استفاده شوند. مشاهده کاملترین و بروزترین آموزش sql server در نیک آموز ویژگی Change Data Capture در SQL Server چگونه کار میکند؟ یک پایگاه داده به نام DataCapture و یک جدول به نام Customer ایجاد میکنیم. سپس چند ردیف را در جدول Customer وارد میکنیم. پس از آن ویژگی CDC را در سطح پایگاه داده فعال میکنیم. USE master GO CREATE DATABASE DataCapture GO USE DataCapture GO CREATE TABLE Customer ( CustomerId INT PRIMARY KEY ,FirstName VARCHAR(30) ,LastName VARCHAR(30) ,Amount_purchased DECIMAL ) GO INSERT INTO dbo.Customer( CustomerId, FirstName, LastName, Amount_Purchased) VALUES (۱, 'Frank', 'Sinatra',20000.00),( 2,'Shawn', 'McGuire',30000.00),( 3,'Amy', 'Carlson',40000.00) GO SELECT * FROM dbo.Customer -- Now enable CDC at the Database Level EXEC sys.sp_cdc_enable_db GO هنگامی که سعی کردم ویژگی CDC را در پایگاه داده DataCapture فعال کنم، یک پیام خطای بسیار دقیق دریافت کردم. Msg 22830, LEVEL 16, State 1, PROCEDURE sys.sp_cdc_enable_db_internal, Line 198 [Batch START Line 31] Could NOT UPDATE the metadata that indicates database DataCapture IS enabled FOR Change Data Capture. The failure occurred WHEN executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15404: 'Could not obtain information about Windows NT group/user 'NCalalani', error code 0x54b.'. USE the action AND error TO determine the cause OF the failure AND resubmit the request. CDC مستلزم این است که owner پایگاه داده یک sysadmin باشد. به طور پیشفرض کاربر ایجاد کننده پایگاه داده owner خواهد بود. بنابراین تغییر owner به ‘ ‘saخطای فوق را برطرف میکند و یا اگر کاربر فوق را به نقش sysadmin اضافه کنید خطا برطرف میشود. البته دقت داشته باشید که اضافه کردن کاربر به sysadmin همچنین مجوزهای زیادی را به کاربر میدهد که ممکن است نخواهید به آنها بدهید. EXEC sp_changedbowner 'sa' GO EXEC sys.sp_cdc_enable_db GO اکنون CDC را در سطح جدول فعال میکنیم. -- Enable on the table level EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Customer', @role_name = NULL, @filegroup_name = N'Primary', @supports_net_changes = 0 GO فعال کردن CDC در سطح جدول به سادگی در سطح پایگاه داده نیست. به این دلیل است که تمام اشیا CDC به عنوان اشیا سیستم ایجاد میشوند. همچنین وابستگی به پایگاه داده MSDB و سرویس SQL Server Agent وجود دارد. اگر دستور بالا را با موفقیت اجرا کنیم، پیام زیر را برمیگرداند: Job 'cdc.DataCapture_capture' started successfully. Job 'cdc.DataCapture_cleanup' started successfully. یک تریگر DDL و تعدادی از پروسیجرهای سیستم نیز ایجاد میشود. اشیا CDC تماما در یک پایگاه داده هستند. اگر به اشتباه جدول را drop کنید، تاریخچه شما از بین میرود. برخلاف Temporal Table، در صورت فعال بودن CDC، هیچ مکانیسم ایمنی برای محدود کردن حذف جدول وجود ندارد. تغییراتی به صورت زیر در جدول Customer ایجاد میکنیم. -- insert a row INSERT INTO Customer (Customerid, FirstName, LastName, Amount_purchased) VALUES (4, 'Ameena', 'Lalani', 50000) GO -- delete a row DELETE FROM dbo.Customer WHERE CustomerId = 2 GO -- update a row UPDATE Customer SET Lastname = 'Clarkson' WHERE CustomerId = 3 GO -- Let us query to see what it reports SELECT * FROM dbo.Customer DECLARE @begin_lsn binary (10), @end_lsn binary (10) -- get the first LSN for customer changes SELECT @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer') -- get the last LSN for customer changes SELECT @end_lsn = sys.fn_cdc_get_max_lsn() -- get individual changes in the range SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customer(@begin_lsn, @end_lsn, 'all'); توجه کنید customerId = 2 که حذف شد، اکنون همه تغییرات ظاهر میشود. CDC اطلاعات تغییر DML را به صورت ناهمزمان مینویسد. ابتدا در لاگ تراکنش مینویسد و سپس لاگ های تراکنش را جستجو میکند و اطلاعات را با شروع و پایان Log Sequence Number (LSN) ذخیره میکند. برخلاف CT، CDC همه ستونها را ذخیره میکند و نه فقط ستونهای کلید اصلی. به ستون $operation در تصویر بالا نگاه کنید، عدد ۲ نشان دهنده یک Insert است، عدد ۱ برای Delete و عدد ۴ برای Update است. افراد علاقهمند میتوانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئرینویسی گسترش دهند. بیایید ببینیم پس از تغییر ردیفهایی که در بالا انجام شدهاند، اطلاعات چگونه ذخیره میشوند. -- Update the above row one more time UPDATE Customer SET Lastname = 'Blacksmith' WHERE CustomerId = 3 GO -- Let INSERT few more rows INSERT INTO Customer (Customerid, FirstName, LastName, Amount_purchased) VALUES (5, 'Sponge', 'Bob', 5000) GO INSERT INTO Customer (Customerid, FirstName, LastName, Amount_purchased) VALUES (6, 'Donald', 'Duck', 6000) GO -- Let us query to see what it reports now SELECT * FROM dbo.Customer DECLARE @begin_lsn binary (10), @end_lsn binary (10) -- get the first LSN for customer changes SELECT @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer') -- get the last LSN for customer changes SELECT @end_lsn = sys.fn_cdc_get_max_lsn() -- get individual changes in the range SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customer(@begin_lsn, @end_lsn, 'all'); Temporal Tableها در ذخیرهسازی دادههای تاریخی کارآمدتر هستند در مقاله بعدی به این موضوع دقیقتر نگاه خواهیم کرد. از آنجایی که جدول فعلی دارای داده است و آنها محدود به زمان هستند، نیازی به نگه داشتن دادههای اضافی در شی history وجود ندارد. CDC آن دادههای اضافی را همان طور که برای id های مشتری با شمارههای ۵ و ۶ در مثال بالا میبینیم، نگه میدارد. CDC هیچ بُعد زمانی ندارد، تغییرات دادهها را بر اساس LSN (شماره توالی ورود به سیستم) پیگیری میکند در حالی که Temporal Table درSQL Server 2016 بعد زمانی را نیز در نظر میگیرد. سخن پایانی ویژگی Change Tracking در SQL Server یک ابزار ردیابی بسیار ساده است که کاربرد محدودی دارد و هیچ راه مستقیمی برای کوئری از جدول ردیابی داخلی که با فعال بودن CT ایجاد میشود وجود ندارد. شما میتوانید دوره نگهداری دادههای CT را در سطح پایگاه داده تعریف کنید. تنها ستونهایی که بخشی از کلید اصلی در جدول هستند، پس از فعال کردن Change Tracking برای تغییرات ردیابی میشوند. ویژگی Change Data Capture در SQL Server جایگاه خاص خود را دارد و Temporal Table در نسخه SQL Server 2016 جایگزین آنها نمیشوند. CDC را میتوان برای ثبت تغییرات در Master Data در Master Data Management (MDM) با ثبت ناهمزمان تغییرات استفاده کرد. با مثالهایی در این مقاله، مزایا و معایب ویژگی CDC را مشاهده کردیم. سینتکس و اجرای کلی CDC بسیار پیچیدهتر از Change Tracking و Temporal Table است. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم. چه رتبه ای میدهید؟ میانگین ۳ / ۵. از مجموع ۲ اولین نفر باش دانلود مقاله ویژگی Change Tracking در SQL Server | بررسی عملکرد و نحوه استفاده فرمت PDF 7 صفحه حجم 3 مگابایت دانلود مقاله معرفی نویسنده مقالات 415 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تیم فنی نیک آموز معرفی محصول مسعود طاهری دوره آموزش امنیت در SQL Server 2022 7.000.000 تومان 4.200.000 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ