Хранение архивных данных

Настраиваем таблицы аудита

1. Запустите SQL Server Management Studio и найдите в Object Explorer (Обозревателе объектов) в базе данных Adventure Works таблицу Sales.SpecialOffer.
2. Сгенерируйте базовый сценарий аудита, щелкнув правой кнопкой мыши таблицу Sales.SpecialOffer и выбрав из контекстного меню команды Script Table As, Create To, New Query Editor Window (Создать сценарий для таблицы, Используя CREATE, В новом окне редактора запросов). После этого откроется новое окно запроса с готовым для редактирования сценарием CREATE TABLE.
3. Отредактируйте сценарий, выполнив перечисленные ниже действия. Для этого примера окончательная редакция сценария показана в действии 4.
   1. Сначала удалите все дополнительные сценарии. Нужно удалить все строки кода, которые не входят в инструкцию CREATE.
   2. Затем измените имя таблицы с Sales.SpecialOffer на Sales.SpecialOffer_Audit.
   3. Теперь удалите все ограничения для таблицы и разрешите для всех столбцов значения NULL. Благодаря этому таблица будет больше похожа на журнальную таблицу. В этом случае таблица аудита не должна мешать обычным операциям в таблице с самого начала. Это также должно упростить управление таблицей.
   4. Добавьте все дополнительные столбцы, которые будут помогать в определении типа изменений, даты изменений и других элементов аудита, которые нужно отслеживать. В данном примере нужно добавить столбцы, перечисленные в табл. 7.2.

      Таблица 7.2. Столбцы, которые нужно добавить в таблицу аудита

      Имя столбца	Тип данных
      AuditModifiedDate	DATETIME
      AuditType	NVARCHAR(20)
4. Выполните окончательный сценарий, представленный ниже, в базе данных AdventureWorks. (Этот код можно найти в файлах примеров под именем CreateAuditTable.sql.)

   USE AdventureWorks;
   GO
   CREATE TABLE Sales.SpecialOffer_Audit(
       SpecialOfferID INT NULL,
       Description NVARCHAR(255) NULL,
       DiscountPct SMALLMONEY NULL,
       [Type] NVARCHAR(50) NULL,
       Category NVARCHAR(50) NULL,
       StartDate DATETIME NULL,
       EndDate DATETIME NULL,
       MinQty INT NULL,
       MaxQty INT NULL,
       rowguid UNIQUEIDENTIFIER NULL,
       ModifiedDate DATETIME NULL,
       AuditModifiedDate DATETIME NULL,
       AuditType NVARCHAR(20) null 
   ); 
   GO

   Совет. Возможно, придется создать новую схему для объектов аудита.

Запись данных аудита в таблицу аудита

Основные способы перемещения данных в таблицы аудита в SQL Server 2005 - это триггеры базы данных и новое предложение T-SQL OUTPUT. Однако новое предложение OUTPUT добавляет некоторые интересные возможности. В следующем разделе мы на примере изучим каждый из этих двух вариантов.

Используем триггер UPDATE для заполнения таблицы аудита

1. Создайте в таблице Sales.SpecialOffer триггер, который будет записывать предыдущее состояние данных в созданную нами таблицу Sales.SpecialOffer_Audit. Код, приведенный ниже – это пример синтаксической конструкции, которую можно использовать. (Этот код можно найти в файлах примеров под именем CreateTrigger.sql.) Введите и выполните код в окне нового запроса SQL Server Management Studio.

   USE AdventureWorks
   GO
   CREATE TRIGGER SpecialOfferUpdateAudit ON Sales.SpecialOffer
   FOR UPDATE
   AS
   INSERT INTO Sales.SpecialOffer_Audit 
          (SpecialOfferID
          ,Description
          ,DiscountPct
          ,[Type]
          ,Category
          ,StartDate
          ,EndDate
          ,MinQty
          ,MaxQty
          ,rowguid
          ,ModifiedDate
          ,AuditModifiedDate
          ,AuditType) 
   SELECT TOP 1 d.SpecialOfferID
               ,d.Description
               ,d.DiscountPct
               ,d.[Type]
               ,d.Category
               ,d.StartDate
               ,d.EndDate
               ,d.MinQty
               ,d.MaxQty
               ,d.rowguid
               ,d.ModifiedDate
               ,GETDATE()
               ,'UPDATE' 
     FROM deleted d; GO

Примечание. Если вы не делали никаких изменений в таблице Sales.SpecialOffer, скорее всего, вам придется удалить триггер, который уже существует в таблице ( uSpecial Offer ). Необходимо сохранить сценарий для триггера, чтобы можно было использовать его в дальнейшем. Если этот триггер не удалить, то вы будете получать строки данных в таблице Sales.SpecialOffer_Audit при любом обновлении таблицы.

Преимущество использования триггера заключается в том, что он будет захватывать любые обновления, которые произойдут в таблице, независимо от их источника. Это вариант аудита с полным охватом. Если речь идет о данных, которые изменяются без контроля с вашей стороны, то это превосходный вариант. Но если вы тщательно контролируете данные, которые заносятся в таблицу, особенно если это выполняется при помощи хранимых процедур, то в SQL Server 2005 есть новая возможность аудита изменений - предложение OUTPUT.

Используем предложение OUTPUT для заполнения таблицы аудита

1. Чтобы эффективно использовать предложение OUTPUT, каждое событие, которое нужно отслеживать, потребует разработки хранимых процедур и инструкций SQL, которые будут использоваться для обновления ( UPDATE ), вставки ( INSERT ) или удаления ( DELETE ) данных в отслеживаемых таблицах. Предложение OUTPUT предоставляет доступ к вставляемым и удаляемым таблицам в этих процедурах и инструкциях SQL. Теперь не обязательно использовать триггеры для доступа к данным. Представленный ниже код показывает пример использования предложения OUTPUT для аудита обновления в таблице SpecialOffer в таблице SpecialOffer_Audit. (Этот код можно найти в файлах примеров под именем UsingOutputClause.sql.) Введите и выполните данный код в окне нового запроса SQL Server Management Studio.
   Важно. Предложение OUTPUT должно вставлять свои данные в табличную переменную, во временную таблицу или - как в данном случае - в постоянную таблицу.

   USE AdventureWorks
   GO
   UPDATE Sales.SpecialOffer
   SET description = "Big Mountain Tire Sale" 
   OUTPUT deleted.SpecialOfferID
         ,deleted.Description
         ,deleted.DiscountPct
         ,deleted.[Type]
         ,deleted.Category
         ,deleted.StartDate
         ,deleted.EndDate
         ,deleted.MinQty
         ,deleted.MaxQty
         ,deleted.rowguid
         ,deleted.ModifiedDate
         ,GETDATE()
         ,'UPDATE' INTO Sales.SpecialOffer_Audit 
     WHERE SpecialOfferID = 10
2. Предложение OUTPUT помещает измененные данные в рамках простого доступа в процессе изменения данных. В процессе операций UPDATE и DELETE доступен префикс DELETED. В процессе операций UPDATE и INSERT доступен префикс INSERTED. Обратите внимание на то, что оба префикса не могут быть доступными одновременно, в отличие от таблиц deleted и inserted, которые используются в триггерах. Эта взаимоисключающая доступность требует, чтобы различные операции обрабатывались по-разному для сбора нужных данных и помещения их в таблицы аудита.
   Совет. Предложение OUTPUT можно также использовать для возвращения значения из столбца IDENTITY в процессе операции INSERT.

Восстановление данных с помощью таблиц аудита

Теперь, когда у нас есть два варианта загрузки данных в таблицу аудита, можно подумать, для чего использовать эти данные. Поскольку все изменения в таблице сохраняются в таблице аудита, можно восстановить любые изменения данных, перезаписав текущие данные изменением, которое нужно сохранить. Таблица аудита может хранить несколько версий данных, поэтому чаще всего это придется делать вручную. Однако можно также создать обслуживающую хранимую процедуру для отмены самого последнего изменения.

Используем таблицу аудита для восстановления измененных данных

1. Определите, какую запись следует восстановить. Для этого нужно будет идентифицировать изменяемую запись и данные, которые ее заменят.
2. Воспользуйтесь приложением UPDATE для перезаписи текущих данных изменением, которое следует восстановить в этой таблице. В данном примере придется использовать либо свойство rowguid, либо столбец SpecialOfferID в сочетании с AuditModifiedDate в качестве критерия для инструкции UPDATE, как показано ниже. (Этот код можно найти в файлах примеров под именем RecoveringChangedData.sql.) Введите и выполните этот код в окне нового запроса SQL Server Management Studio.

   — В этом сценарии нужно заменить AuditModifiedDate на
   —AuditModifiedDate из таблицы SpecialOffer_Audit
   USE AdventureWorks
   GO
   UPDATE Sales.SpecialOffer
   SET Description = a.Description
      ,DiscountPct = a.DiscountPct
      ,Type = a.Type
      ,Category = a.Category
      ,StartDate = a.StartDate
      ,EndDate = a.EndDate
      ,MinQty = a.MinQty
      ,MaxQty = a.MaxQty
      ,rowguid = a.rowguid
      ,ModifiedDate = a.ModifiedDate 
     FROM Sales.SpecialOffer_Audit a 
     WHERE SpecialOffer.SpecialOfferID = 10
       AND a.SpecialOfferID = 10
       AND a.AuditModifiedDate = "2006-04-02 22:40:27.513"

Если у вас есть данные, которые нуждаются в регулярном восстановлении, можно инкапсулировать приведенный выше код в хранимую процедуру обслуживания. Однако если вы решите реализовать ее, у вас могут возникнуть проблемы с вводом восстановленных данных. Описанные варианты хорошо подходят для ограниченного количества строк в одной таблице. Если вы работаете с массовой высокопроизводительной загрузкой в нескольких таблицах, следует использовать моментальные снимки, о которых шла речь в первом разделе данной лекции.

Заключение

Вы научились создавать моментальные снимки данных. Моментальные снимки могут эффективно использоваться для решения различных задач в процессе разработки, тестирования и производства. Можно использовать архивные данные для подведения итогов и анализа данных на определенных отрезках времени. Вы также научились выполнять аудит изменений данных и восстанавливать отдельные записи при помощи данных аудита. Сохраняя архивные данные и отслеживая изменения в базе данных, можно обеспечить целостность данных, используя возможность отмены отдельных изменений, не восстанавливая всю базу данных.