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

Удаление моментального снимка базы данных

В отличие от создания и возврата к моментальному снимку, удалить моментальный снимок можно и через T-SQL, и через интерфейс SQL Server Management Studio. Как вы уже успели заметить, для выполнения операций моментальные снимки используют варианты команды базы данных в T-SQL. То же происходит и при удалении моментального снимка.

Удаление моментального снимка при помощи T-SQL

При удалении моментального снимка с помощью T-SQL используйте команду DROP DATABASE. Для удаления моментальных снимков существуют те же ограничения, что и для удаления баз данных. Перед выполнением действия необходимо закрыть все соединения и иметь разрешение на удаление базы данных. Чтобы удалить созданный нами моментальный снимок, введите и выполните следующий код в окне нового запроса. (Этот код можно найти в файлах примеров под именем DeleteSnapshot.sql.)

DROP DATABASE AdventureWorks_SBSExample1;

Удаление моментального снимка базы данных через интерфейс SQL Server Management Studio

В SQL Server Management Studio можно удалить снимок базы данных так же, как и обычную базу данных.

Запустите SQL Server Management Studio.

Разверните папку Database Snapshots (Моментальные снимки базы данных) в Object Explorer (Обозревателе объектов).

Выделите снимок, который нужно удалить.

Щелкните правой кнопкой мыши на этом снимке и выберите из контекстного меню команду Delete (Удалить). Откроется диалоговое окно Delete Object (Удаление объекта), показанное на рисунке.

В этом диалоговом окне можно указать, чтобы SQL Server закрыл соединения; для этого надо установить флажок Close Existing Connections (Закрыть существующие соединения). Преимущество этого подхода заключается в том, что операции удаления не придется ждать завершения транзакции. Это эквивалентно отправке инструкции KILL всем соединениям базы данных.

Нажмите кнопку ОК, чтобы удалить моментальный снимок.

Влияние на исходную базу данных

При использовании моментального снимка базы данных имеют место следующие влияния на исходную базу данных:

Обобщение информации в таблице хроник

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

Затем мы создадим итоговую таблицу из базы данных Adventure Works, которая будет отслеживать продажи данного продукта с сортировкой по продавцам. Задача - выполнив запрос по фамилии продавца, быстро узнать, сколько единиц данного изделия он продал.

Создаем и загружаем таблицу хроники

Для того, чтобы использовать эти примеры в полном объеме, придется создать таблицу в базе данных

USE AdventureWorks;
GO
CREATE TABLE Sales.SalesPersonProductWeeklySummary
  (SalesPersonID INT
  ,SalesPersonFirstName NVARCHAR(50)
  ,SalesPersonLastName NVARCHAR(50)
  ,OrderWeekOfYear INT
  ,OrderYear INT ,ProductID INT
  ,ProductName NVARCHAR(50) 
  ,WeeklyOrderQty INT 
  ,WeeklyLineTotal MONEY ); 
GO
CREATE CLUSTERED INDEX cidx_SalesPersonProductWeeklySummary
  ON Sales.SalesPersonProductWeeklySummary(OrderYear, 
                             OrderWeekOfYear, SalesPersonID);
GO

Далее, нам потребуется хранимая процедура, которую можно использовать для выборки загружаемых в таблицу SalesPersonProductWeeklySummary данных. Следующий код (он включен в файлы примеров под именем CreateUspGetWeeklySalesSummary.sql ) создает процедуру. Введите и выполните код в окне нового запроса.

USE AdventureWorks GO
CREATE PROCEDURE Sales.uspGetSalesWeeklySummary 
        (@StartOfWeek DATETIME 
        ,@EndOfWeek DATETIME ) 
AS 
BEGIN
  SELECT hdr.SalesPersonID
        ,cntc.FirstName AS SalesPersonFirstName 
        ,cntc.LastName AS SalesPersonLastName 
        ,DATEPART(WEEK, hdr.OrderDate) AS OrderWeekOfYear 
        ,DATEPART(YEAR, hdr.OrderDate) AS OrderYear 
        ,prod.ProductID ,prod.Name AS ProductName 
        ,SUM(dtl.OrderQty) as WeeklyOrderQty 
        ,SUM(dtl.LineTotal) as WeeklyLineTotal 
    FROM Sales.SalesOrderHeader hdr
    INNER JOIN Sales.SalesOrderDetail dtl
      ON hdr.SalesOrderID = dtl.SalesOrderID 
    INNER JOIN HumanResources.Employee emp 
      ON hdr.SalesPersonID = emp.EmployeeID 
    INNER JOIN Person.Contact cntc
      ON emp.ContactID = cntc.ContactID 
    INNER JOIN Production.Product prod 
      ON dtl.ProductID = prod.ProductID 
    WHERE hdr.OrderDate BETWEEN @StartOfWeek AND @EndOfWeek 
    GROUP BY hdr.SalesPersonID 
            ,cntc.FirstName 
            ,cntc.LastName 
            ,prod.ProductID 
            ,prod.Name 
            ,hdr.OrderDate 
END; 
GO

Совет. С помощью схемы Sales можно гарантировать, что разрешения на доступ к сведениям о продажах будут применяться и к новым объектам сводных данных.

Теперь можно загрузить таблицу при помощи хранимой процедуры uspGetWeeklySalesSummary, выполнив следующий код (его можно найти в файле примеров с именем LoadSalesPersonProduct WeeklySummary.sql ). Введите и выполните код в окне нового запроса.

INSERT INTO Sales.SalesPersonProductWeeklySummary
   (SalesPersonID
   ,SalesPersonFirstName
   ,SalesPersonLastName
   ,OrderWeekOfYear
   ,OrderYear
   ,ProductID
   ,ProductName
   ,WeeklyOrderQty
   ,WeeklyLineTotal
   )
EXEC Sales.uspGetSalesWeeklySummary 
     @StartOfWeek = "1/1/2004 00:00:00",
     @EndOfWeek = "1/7/2004 11:59:59"; 
GO

Наконец, можно использовать варианты этого кода в агенте SQL Server, чтобы автоматизировать еженедельную загрузку данных.Совет. Для загрузки данных можно также использовать SQL Server Integration Services (Службы интеграции SQL Server).

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

В SQL Server Management Studio откройте диалоговое окно New Job (Новое задание), развернув дерево узла SQL Server Agent в Object Explorer (Обозревателе объектов) и щелкнув правой кнопкой мыши на папке Jobs (Задания). Выберите из контекстного меню команду New Job (Создать задание).

На странице General (Общие) диалогового окна New Job (Новое задание) дайте заданию понятное имя. Здесь можно также добавить описание.

На странице Steps (Шаги) диалогового окна New Job (Новое задание) нажмите кнопку New (Создать), чтобы создать новый шаг задания. Откроется окно New Job Step (Новый этап задания), показанное ниже.

После того, как шагу будет присвоено понятное имя (здесь используется имя Load The Weekly Summary), нужно выбрать сценарий Transact SQL (T-SQL) из раскрывающегося меню Type (Тип), а из раскрывающегося меню Database (База данных) - Adventure Works.Совет. Для запуска задания можно выбрать другую учетную запись, задав параметр Run As (Запуск от имени). По умолчанию для выполнения команды используется учетная запись службы Агент SQL Server.

Затем введем код команды. Нам нужно, чтобы значение даты устанавливалось автоматически. Введите следующий код, чтобы определить переменные для запуска задания в любой день с воскресенья до субботы предыдущей недели и загрузки таблицы. На этом этапе мы предполагаем, что задание запланировано для запуска во вторник (этот код можно найти в файлах примеров под именем ScheduledJobStep.sql.)

DECLARE @StartOfWeek datetime
       ,@EndOfWeek datetime 
SET @StartOfWeek = CAST(ROUND(CAST(DATEADD(DAY, -2, GETDATE())
  AS FLOAT),0,1) AS DATETIME) 
SET @EndOfWeek = DATEADD(DAY, 7, @StartOfWeek)

INSERT INTO Sales.SalesPersonProductWeeklySummary
   (SalesPersonID
   ,SalesPersonFirstName
   ,SalesPersonLastName
   ,OrderWeekOfYear
   ,OrderYear
   ,ProductID
   ,ProductName
   ,WeeklyOrderQty
   ,WeeklyLineTotal
   ) 
 EXEC Sales.uspGetSalesWeeklySummary 
   @StartOfWeek, 
   @EndOfWeek

Совет. Поскольку SQL Server не имеет типа данных, который поддерживал бы только дату, то для того, чтобы правильно настроить параметры, необходимо удалить время из DateTime. Это можно сделать следующим образом: SET @StartOfWeek = CAST(CONVERT(VARCHAR, DATEADD(DAY, -2, GETDATE()), 101) AS DATETIME).

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

Нажмите кнопку OK, чтобы завершить создание шага задания Load the Weekly Summary.

На странице Schedules (Расписания) диалогового окна New Job (Новое задание) можно определить, когда будет выполняться задание. В этом примере задайте запуск задания по еженедельному регулярному расписанию по вторникам в 2 часа ночи. Нажмите кнопку New (Создать) и введите соответствующие параметры в диалоговом окне New Job Schedule (Новое расписание задания). На рисунке показано диалоговое окно New Job Schedule (Новое расписание задания), настроенное на выполнение этого задания. Нажмите кнопку OK, чтобы сохранить новое расписание и применить его к текущему заданию.

Нажмите кнопку ОК, чтобы выйти из диалогового окна New Job (Новое задание) и создать новое задание.

Вывод итоговых данных в индексированных представлениях

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

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

Создаем индексированное представление для итогов продаж

USE AdventureWorks; 
GO

IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 
       N'v_SalesPerson2004ProductSummary') 
  DROP VIEW Sales.v_SalesPerson2004ProductSummary 
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW Sales.v_SalesPerson2004ProductSummary 
  WITH SCHEMABINDING
AS  
  SELECT hdr.SalesPersonID
        ,cntc.FirstName AS SalesPersonFirstName 
        ,cntc.LastName AS SalesPersonLastName 
        ,prod.ProductID 
        ,prod.Name AS ProductName
        ,COUNT_BIG(*) AS OrderLineCount
        ,SUM(dtl.OrderQty) as OrderQty
        ,SUM(dtl.LineTotal) as LineTotal
    FROM Sales.SalesOrderHeader hdr
    INNER JOIN Sales.SalesOrderDetail dtl
      ON hdr.SalesOrderID = dtl.SalesOrderID 
    INNER JOIN HumanResources.Employee emp
      ON hdr.SalesPersonID = emp.EmployeeID 
    INNER JOIN Person.Contact cntc
      ON emp.ContactID = cntc.ContactID 
    INNER JOIN Production.Product prod
      ON dtl.ProductID = prod.ProductID
    WHERE hdr.OrderDate BETWEEN 
          CONVERT(DATETIME,  "1/1/2004 00:00:00",120)
      AND CONVERT(DATETIME,"12/31/2004 23:59:59",120)
    GROUP BY hdr.SalesPersonID
            ,cntc.FirstName
            ,cntc.LastName
            ,prod.ProductID
            ,prod.Name; 
GO

USE AdventureWorks
GO
CREATE UNIQUE CLUSTERED INDEX cidx_v_SalesPerson2004ProductSummary
  ON Sales.v_SalesPerson2004ProductSummary 
      (SalesPersonID 
      ,ProductID); 
GO

Параметры, обязательные при работе с индексированными представлениями

Хотя использование индексированных представлений способно повысить производительность при извлечении итоговых данных из среды, оно связано с некоторыми обязательными параметрами и ограничениями. Представление, созданное в разделе "Создаем индексированное представление для итогов продаж" было разработано для обхода некоторых из таких ограничений.

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

Отслеживание изменений при помощи столбцов и таблиц аудита

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

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

Аудит при помощи столбцов

Преимущество аудита с помощью столбцов заключается в том, что контрольная информация размещается в той же таблице, что и данные. В табл. 7.1 перечислены некоторые столбцы аудита, которые обычно добавляются в таблицы.

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

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

USE AdventureWorks
GO
ALTER TABLE Person.Address
  ADD CreatedDate DATETIME NULL DEFAULT GETDATE()
     ,ModifiedBy VARCHAR(50) NULL
     ,IsDeleted BIT DEFAULT (0)

UPDATE Person.Address 
SET CreatedDate = "1/1/1980";

WHERE IsDeleted = 0

Аудит с помощью таблиц

Теперь мы знаем, как использовать аудит для уведомления о сделанных изменениях. Однако единственное изменение, которое может быть легко отменено - это событие DELETE. Достаточно просто сбросить флаг IsDeleted, и данные будут снова доступны. Существует также возможность отменить событие CREATE, если об этом действии имеется достаточная информация. Однако если нужно иметь возможность полностью отслеживать состояние данных перед изменением, возможно, лучшим вариантом окажется использование таблиц аудита. Эту возможность следует использовать с осторожностью, потому что она может вызвать много проблем с обслуживанием и производительностью. Такие проблемы возникают потому, что приходится копировать данные в таблицу аудита и изменять их в исходной таблице. Для этого примера мы зададим аудит на базе таблицы в таблице Sales.Special Offer. Цель – отслеживание любых изменений в этой таблице и обеспечение возможности отменить изменения после того, как они были зафи ксированы.

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

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 для заполнения таблицы аудита

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

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

Используем предложение 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

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

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

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

— В этом сценарии нужно заменить 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"

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

Заключение

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