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

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

В отличие от создания и возврата к моментальному снимку, удалить моментальный снимок можно и через 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 можно удалить снимок базы данных так же, как и обычную базу данных.

1. Запустите SQL Server Management Studio.
2. Разверните папку Database Snapshots (Моментальные снимки базы данных) в Object Explorer (Обозревателе объектов).
3. Выделите снимок, который нужно удалить.
4. Щелкните правой кнопкой мыши на этом снимке и выберите из контекстного меню команду Delete (Удалить). Откроется диалоговое окно Delete Object (Удаление объекта), показанное на рисунке.
   

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

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

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

• Управление базой данных
  • Вы не сможете удалить или отсоединить исходную базу данных, пока существует снимок базы данных. Сначала нужно будет удалить снимок базы данных.
  • Вы не сможете восстановить исходную базу данных из резервной копии, пока существуют снимки базы данных.
    Примечание. Операции резервного копирования исходной базы данных продолжают функционировать как обычно. На эти операции наличие моментальных снимков не влияет.
  • Если происходит возврат к моментальному снимку, цепочка журналов разрывается, и восстановление данных из резервных копий журнала транзакций в полном объеме будет невозможным.
  • Нельзя удалить файлы из исходной базы данных, пока не будут удалены моментальные снимки.
• Производительность базы данных
• Неизбежно снижение производительности, потому что базе данных придется управлять и исходной версией, и связанными моментальными снимками. Базе данных придется копировать оригинальное значение в моментальный снимок, а затем записывать изменение в исходную базу данных. Это приводит к дополнительным операциям ввода/вывода до тех пор, пока используются моментальные снимки.

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

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

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

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

1. Для того, чтобы использовать эти примеры в полном объеме, придется создать таблицу в базе данных Adventure Works, как показано ниже. Следующий код предназначен для создания таблицы и ее кластеризованного индекса (этот код можно найти среди файлов примеров под именем CreateSalesPerson ProductWeeklySummary.sql ). Введите и выполните код в новом окне запроса в SQL Server Management Studio.
   

   
   

   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
2. Далее, нам потребуется хранимая процедура, которую можно использовать для выборки загружаемых в таблицу 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 можно гарантировать, что разрешения на доступ к сведениям о продажах будут применяться и к новым объектам сводных данных.
3. Теперь можно загрузить таблицу при помощи хранимой процедуры 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
4. Наконец, можно использовать варианты этого кода в агенте SQL Server, чтобы автоматизировать еженедельную загрузку данных.
   Совет. Для загрузки данных можно также использовать SQL Server Integration Services (Службы интеграции SQL Server).

   Если вам приходится выполнять больше одной операции загрузки, то это может быть наилучшим вариантом.
5. В SQL Server Management Studio откройте диалоговое окно New Job (Новое задание), развернув дерево узла SQL Server Agent в Object Explorer (Обозревателе объектов) и щелкнув правой кнопкой мыши на папке Jobs (Задания). Выберите из контекстного меню команду New Job (Создать задание).
6. На странице General (Общие) диалогового окна New Job (Новое задание) дайте заданию понятное имя. Здесь можно также добавить описание.
7. На странице Steps (Шаги) диалогового окна New Job (Новое задание) нажмите кнопку New (Создать), чтобы создать новый шаг задания. Откроется окно New Job Step (Новый этап задания), показанное ниже.
   

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

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

    
    увеличить изображение
    
    Совет. Во многих диалоговых окнах SQL Server Management Studio есть кнопка Script (Сценарий), которая изображена на следующем рисунке. Нажмите эту кнопку, чтобы записать только что внесенные изменения в виде сценария. Непосредственно перед тем, как сохранить задание, можно использовать эту возможность, чтобы сгенерировать сценарий для задания, шага задания и расписания.

    

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