تمرين - دفتر الأستاذ

مكتمل

تخيل أنك مدقق لشركة Contoso. يستخدم قسم الموارد البشرية في Contoso تطبيق ويب بسيط لإدارة رواتب الموظفين. يطلب منك تدقيق البيانات في قاعدة البيانات للتأكد من صحة البيانات وأنه لم يعبث بها أحد.

في هذا التمرين، نرى كيف يمكنك استخدام دفتر الأستاذ في سيناريو العالم الحقيقي لتدقيق البيانات باستخدام جداول دفتر الأستاذ. نقوم بتنفيذ المهام التالية:

  • إنشاء قاعدة بيانات تسمى ContosoHR.
  • إنشاء جدول دفتر الأستاذ القابل للتحديث يسمى Employees.
  • قم بإجراء تحديثات على البيانات.
  • تحقق من أن البيانات يمكن الوثوق بها بالكامل.
  • استعلم عن طرق عرض جدول المحفوظات ودفتر الأستاذ لمشاهدة التعقب الذي يحدث والعلاقة بين الجداول.

المتطلبات الأساسية

  • تم تثبيت SQL Server 2022.
  • أحدث إصدار من SQL Server Management Studio (SSMS).

إنشاء قاعدة البيانات وجدول دفتر الأستاذ

  1. سجل الدخول إلى مثيل SQL Server 2022 باستخدام SSMS مع sysadmin الأذونات. إنشاء قاعدة بيانات تسمى ContosoHR والتبديل إلى سياق استخدام قاعدة البيانات هذه.

    USE master;
    GO
    -- Create the ContosoHR database
    DROP DATABASE IF EXISTS ContosoHR;
    GO
    CREATE DATABASE ContosoHR;
    GO
    USE ContosoHR;
    GO
    
  2. إنشاء جدول دفتر الأستاذ القابل للتحديث من Employees أجل عن طريق تنفيذ البرنامج النصي التالي:

    USE ContosoHR;
    GO
    -- Create the Employees table and make it an updatable ledger table
    DROP TABLE IF EXISTS [dbo].[Employees];
    GO
    CREATE TABLE [dbo].[Employees](
    	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    	[SSN] [char](11) NOT NULL,
    	[FirstName] [nvarchar](50) NOT NULL,
    	[LastName] [nvarchar](50) NOT NULL,
    	[Salary] [money] NOT NULL
    	)
    WITH 
    (
      SYSTEM_VERSIONING = ON,
      LEDGER = ON
    ); 
    GO
    

    إذا قمت بفحص الجدول الذي تم إنشاؤه حديثا في مستكشف عناصر SSMS، فستلاحظ أن الجدول يحتوي على أيقونة جديدة مع علامة اختيار تشير إلى أنه جدول دفتر الأستاذ.

    لقطة شاشة لجدول دفتر الأستاذ الذي تم إنشاؤه حديثا في مستكشف عناصر SSMS.

  3. قم بتعبئة Employees الجدول ببعض بيانات العينة عن طريق تنفيذ البرنامج النصي التالي:

    USE ContosoHR;
    GO
    -- Clear Employees table
    DELETE FROM [dbo].[Employees];
    GO
    -- Insert 10 employees. The names and SSN are completely fictional and not associated with any person
    DECLARE @SSN1 char(11) = '795-73-9833'; DECLARE @Salary1 Money = 61692.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN1, 'Catherine', 'Abel', @Salary1);
    DECLARE @SSN2 char(11) = '990-00-6818'; DECLARE @Salary2 Money = 990.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN2, 'Kim', 'Abercrombie', @Salary2);
    DECLARE @SSN3 char(11) = '009-37-3952'; DECLARE @Salary3 Money = 5684.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN3, 'Frances', 'Adams', @Salary3);
    DECLARE @SSN4 char(11) = '708-44-3627'; DECLARE @Salary4 Money = 55415.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN4, 'Jay', 'Adams', @Salary4);
    DECLARE @SSN5 char(11) = '447-62-6279'; DECLARE @Salary5 Money = 49744.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN5, 'Robert', 'Ahlering', @Salary5);
    DECLARE @SSN6 char(11) = '872-78-4732'; DECLARE @Salary6 Money = 38584.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN6, 'Stanley', 'Alan', @Salary6);
    DECLARE @SSN7 char(11) = '898-79-8701'; DECLARE @Salary7 Money = 11918.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN7, 'Paul', 'Alcorn', @Salary7);
    DECLARE @SSN8 char(11) = '561-88-3757'; DECLARE @Salary8 Money = 17349.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN8, 'Mary', 'Alexander', @Salary8);
    DECLARE @SSN9 char(11) = '904-55-0991'; DECLARE @Salary9 Money = 70796.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN9, 'Michelle', 'Alexander', @Salary9);
    DECLARE @SSN10 char(11) = '293-95-6617'; DECLARE @Salary10 Money = 96956.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN10, 'Marvin', 'Allen', @Salary10);
    GO
    

استكشاف البيانات باستخدام طرق عرض دفتر الأستاذ

  1. فحص الأعمدة باستخدام SELECT استعلام. يحتوي البرنامج النصي التالي على مثالين لكيفية الاستعلام عن البيانات. يستخدم * المثال الأول حرف البدل لإرجاع كافة الأعمدة. يسرد المثال الثاني جميع الأعمدة. لاحظ كيف يحتوي الجدول على أعمدة أخرى ليست جزءا من تعريف الجدول الأصلي. يتم استخدام هذه الأعمدة بواسطة ميزة دفتر الأستاذ لتعقب التغييرات على البيانات.

    USE ContosoHR;
    GO
    -- Use * for all columns
    SELECT * FROM dbo.Employees;
    GO
    -- List out all the columns
    SELECT EmployeeID, SSN, FirstName, LastName, Salary, 
    ledger_start_transaction_id, ledger_end_transaction_id, ledger_start_sequence_number, 
    ledger_end_sequence_number
    FROM dbo.Employees;
    GO
    
  2. انظر إلى Employees_Ledger طريقة العرض عن طريق تنفيذ الاستعلام التالي SELECT . طريقة العرض هذه من Employees الجدول وجدول محفوظات دفتر الأستاذ. يحتوي دفتر الأستاذ على معلومات المعاملة من أعمدة مخفية في الجدول، بالإضافة إلى إشارة إلى نوع العملية التي تم تنفيذها على دفتر الأستاذ لصف معين.

    USE ContosoHR;
    GO
    SELECT * FROM dbo.Employees_Ledger;
    GO
    

    لقطة شاشة لعرض دفتر الأستاذ لجدول الموظفين في SSMS.

  3. للحصول على فهم أفضل لتعريف طريقة عرض دفتر الأستاذ، يمكنك تنفيذ الاستعلام التالي:

    USE [ContosoHR];
    GO
    sp_helptext 'Employees_ledger';
    GO
    
    CREATE VIEW [dbo].[Employees_Ledger] AS   SELECT [EmployeeID], [SSN], [FirstName], [LastName], [Salary], [ledger_start_transaction_id] AS [ledger_transaction_id], [ledger_start_sequence_number] AS [ledger_sequence_number], 1 AS [ledger_operation_type], N'INSERT' AS [ledger_operation_type_desc] FROM [dbo].[Employees]   UNION ALL   SELECT [EmployeeID], [SSN], [FirstName], [LastName], [Salary], [ledger_start_transaction_id] AS [ledger_transaction_id], [ledger_start_sequence_number] AS [ledger_sequence_number], 1 AS [ledger_operation_type], N'INSERT' AS [ledger_operation_type_desc] FROM [dbo].[MSSQL_LedgerHistoryFor_901578250]   UNION ALL   SELECT [EmployeeID], [SSN], [FirstName], [LastName], [Salary], [ledger_end_transaction_id] AS [ledger_transaction_id], [ledger_end_sequence_number] AS [ledger_sequence_number], 2 AS [ledger_operation_type], N'DELETE' AS [ledger_operation_type_desc] FROM [dbo].[MSSQL_LedgerHistoryFor_901578250]
    

    يمكنك أن ترى أن طريقة العرض عبارة عن اتحاد للجدول الأصلي (للإدراجات الجديدة)، والتحديثات من جدول المحفوظات (للإدراج والحذف).

  4. يمكنك دمج طريقة عرض دفتر الأستاذ مع جدول نظام للحصول على مزيد من معلومات التدقيق، مثل من أجرى المعاملة على قاعدة البيانات، وفي أي تاريخ ووقت. لمشاهدة مثال، قم بتنفيذ الاستعلام التالي.

    USE ContosoHR;
    GO
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.Salary, 
    dlt.transaction_id, dlt.commit_time, dlt.principal_name, e.ledger_operation_type_desc, dlt.table_hashes
    FROM sys.database_ledger_transactions dlt
    JOIN dbo.Employees_Ledger e
    ON e.ledger_transaction_id = dlt.transaction_id
    ORDER BY dlt.commit_time DESC;
    GO
    
  5. للتحقق من تكامل دفتر الأستاذ، دعنا ننشئ ملخصا عن طريق تنفيذ الإجراء sp_generate_database_ledger_digestالمخزن . احفظ قيمة الإخراج (بما في ذلك الأقواس) لاستخدامها للتحقق من دفتر الأستاذ.

    USE ContosoHR;
    GO
    EXEC sp_generate_database_ledger_digest;
    GO
    
  6. يمكنك الآن رؤية الكتل التي تم إنشاؤها لجدول دفتر الأستاذ من خلال النظر في sys.database_ledger_blocks.

    USE ContosoHR;
    GO
    SELECT * FROM sys.database_ledger_blocks;
    GO
    

إجراء تغييرات على البيانات

بعد النظر إلى بعض وظائف دفتر الأستاذ في SQL Server 2022، دعنا نقم بإجراء بعض التغييرات على البيانات ونرى كيف يتعقب دفتر الأستاذ التغييرات. يساعدك هذا التمرين في مهمتك المتمثلة في تدقيق البيانات في ContosoHR قاعدة البيانات، وتحديدا الجدول Employees .

  1. تظاهر بأن جاي، الموظف في الشركة، سجل الدخول إلى قاعدة البيانات، ونفذ الاستعلام التالي لإضافة 50000 إلى راتبه. لسوء الحظ بالنسبة لجاي، Employees الجدول هو جدول دفتر الأستاذ القابل للتحديث. وهذا يعني أن تغييرهم، جنبا إلى جنب مع هويتهم والطوابع الزمنية، مستمر في بنية بيانات دفتر الأستاذ الظاهر للعبث.

    UPDATE [dbo].[Employees] SET [Salary] = [Salary] + 50000
    WHERE [FirstName] = N'Jay' AND [LastName] = N'Adams'
    
  2. إذا كنت تستخدم استعلاما SELECT على Employees الجدول، يمكنك أن ترى أن راتب جاي قد تم تحديثه من وقت سابق.

    SELECT * FROM [dbo].[Employees]
    

    لقطة شاشة لجدول الموظفين المحدث في SSMS.

تدقيق البيانات

لنفترض أنه بعد بضعة أسابيع، تقوم بمراجعة روتينية للتغييرات في ContosoHR قاعدة البيانات. كخطوة أولى، يمكنك تشغيل التحقق من دفتر الأستاذ للتأكد من أنه يمكنك الوثوق بالبيانات التي ستقوم بفحصها.

  1. نفذ الاستعلام التالي لتشغيل التحقق من دفتر الأستاذ. تحتوي النتائج على أحدث ملخص لقاعدة البيانات وتمثل مزيجًا لقاعدة البيانات في الوقت الحالي. انسخ محتويات النتائج لاستخدامها في الخطوة التالية.

    USE ContosoHR;
    GO
    EXEC sp_generate_database_ledger_digest;
    GO
    

    لقطة شاشة لنتائج ملخص دفتر الأستاذ في SSMS.

  2. sp_verify_database_ledger استخدم الإجراء المخزن للتحقق من عدم العبث بالبيانات التاريخية في قاعدة البيانات الخاصة بك. استبدله <YOUR DATABASE DIGEST> بالملخص الذي نسخته في الخطوة السابقة.

    EXECUTE sp_verify_database_ledger N'
    <YOUR DATABASE DIGEST>
    ';
    

    إذا نجح التحقق ولم يتم العبث ببياناتك، فسترى الرسالة التالية:

    لقطة شاشة لنتائج التحقق من دفتر الأستاذ في SSMS.

    باستخدام هذا الملخص، نعرف ما يلي:

    • البيانات صالحة، استنادا إلى وقت التقاط الملخص.
    • تتطابق الكتل الداخلية مع تغييرات البيانات الحالية للتحديث إلى راتب جاي. إذا اضطر شخص ما إلى تزوير البيانات للجدول Employees دون القيام بأمر T-SQL UPDATE لجعل النظام يعتقد أن راتب جاي الحالي كان 50,000 أكثر مما هو عليه حقا، فإن النظام يثير خطأ أن تجزئة التغييرات لا تتطابق مع البيانات الحالية. سترى رسالة Ledger verification failed .
  3. الآن بعد أن تحققت من عدم العبث بالبيانات، يمكنك استعراض محتوى طريقة عرض دفتر الأستاذ للجدول Employees . لاحظت عملية تحديث مشبوهة قام بها Jay، والذي لا يمكنه رفض تحديث الراتب بشكل فعال. يتم التحقق من البيانات الموجودة في جدول دفتر الأستاذ بشكل مشفر على أنها أصلية وتظهر بوضوح اسم المستخدم الخاص ب Jay كحساب قام بتحديث الراتب. لإجراء هذا التحقق، قم بتشغيل الاستعلام التالي.

    SET NOCOUNT ON
    SELECT
    t.[commit_time] AS [CommitTime]
    , t.[principal_name] AS [UserName]
    , l.EmployeeId
    , l.[SSN]
    , l.[FirstName]
    , l.[LastName]
    , l.[Salary]
    , l.[ledger_operation_type_desc] AS Operation
    FROM [dbo].[Employees_Ledger] l
    JOIN sys.database_ledger_transactions t
    ON t.transaction_id = l.ledger_transaction_id
    WHERE t.[commit_time] > DATEADD(MONTH, -1, SYSDATETIMEOFFSET())
    ORDER BY t.commit_time DESC;
    

    لقطة شاشة لنتائج طريقة عرض دفتر الأستاذ لجدول الموظفين في SSMS تظهر من قام بالتغييرات.

التحقق من المعرفة - اختر أفضل إجابة

1.

يتم تقديم جداول دفتر الأستاذ في شكلين في SQL Server 2022. ما هي؟

2.

يتم التحقق المستقل لدفتر الأستاذ في SQL Server 2022 من خلال: