• Часть 2: Транзакционные механизмы SQL Server на примерах. Журнал транзакций

    sqlИ сразу выкладываю второй доклад, посвященный назначению журнала транзакций, демонстрации работы журнала на логическом и физическом уровне, архитектуре файлов виртуальных журналов (Virtual Log File – VLF), изменению размеров журнала транзакций и принципу формирования Log Sequence Number (LSN).  Видео-урок продолжает серию вебинаров, посвященных архитектуре работы буфер-кэша и транзакционного лога, процессов Lazy Writer и Checkpoint, отличиям в разных моделях восстановления и нововведениям SQL Server 2012-2014. Особенностью серии является преобладание демонстраций, в частности примеров использования представлений динамического управления (DMV), счетчиков производительности (Performance counters), расширенных событий (Extended Events) и недокументированных возможностей SQL Server. В демонстрациях используется последняя доступная версия SQL Server 2014 CTP2.

    Посмотреть доклад можно на Techdays http://www.techdays.ru/videos/7306.html

    • Часть 1: ACID. Из серии Транзакционные механизмы SQL Server на примерах

      sql

      30 ноября 2014 года состоялось мое выступление на конференции SQL Saturday #261 2013 на тему: Транзакционные механизмы SQL Server на примерах. Доклад был посвящен назначению журнала транзакций, демонстрации работы журнала на логическом и физическом уровне, архитектуре файлов виртуальных журналов,  буфер-кэша, процессам Lazy Writer и Checkpoint, отличиям в разных моделях восстановления и нововведениям SQL Server 2012-2014. Выступление вызвало большой интерес, особенно новинка 2014 года — Delayed Durability. Доклад был часовой и поэтому вместил только часть подготовленного материала и презентаций. Понимая важность и нужность темы для IT специалистов, я решил сделать серию видеоуроков под общим названием: Транзакционные механизмы SQL Server на примерах. Особенностью серии является преобладание демонстраций, в частности примеров использования представлений динамического управления (DMV), счетчиков производительности (Performance counters), расширенных событий (Extended Events) и недокументированных возможностей SQL Server. В демонстрациях используется последняя доступная версия SQL Server 2014 CTP2. Сегодня я представляю первый доклад, посвященный демонстрации обеспечения принципов ACID: атомарности, согласованности, изолированности и «живучести» при реализации транзакций.

      Посмотреть доклад можно на Techdays http://www.techdays.ru/videos/7305.html

    • Главная SQL, Без рубрики, Новое SQL, SQL 2008 R2, Конкурс
      • Итоги конкурса: Microsoft SQL Server 2008 R2

        AND-THE-WINNER-IS-OSCAR-37748Спустя месяц обсуждений и голосований мы с удовольствием подводим итоги конкурса посвященного Microsoft SQL Server 2008 R2.

        От лица организаторов конкурса прощу прощения у авторов за перенос сроков окончания конкурса и столь длительный процесс подведения итогов.

        Данный конкурс мы проводили совместно с порталом sql.ru при поддержке компании Microsoft. Конкурсные статьи номинировались в трех категориях:

      • Главная SQL, Без рубрики, Новое SQL, SQL 2008 R2
        • Обработка ошибок и формирование сообщений об ошибках для баз данных Microsoft SQL Server 2005/2008

          errorreportДля программ, работающих с БД, важным является не только корректность обработки ошибок БД, но и формирование информативных сообщений об этих ошибках. Наличие таких сообщений позволяет быстрее выявлять причины ошибок изменения данных БД и их исправлять. Особенно это актуально для пользователей программных продуктов, которым в большинстве случаев может быть не известна не только структура конкретной БД, но и теоретические основы реляционных баз данных. Хотя Microsoft SQL Server и формирует довольно подробные сообщения об ошибках ограничений базы данных, но при непосредственной передаче их пользователям в большинстве случаев, сложно рассчитывать на то, что ему удастся выявить по такому сообщению причину ошибки. Можно выделить несколько основных причин возникновения такой ситуации:

        • Главная SQL, Без рубрики, Новое Scripts, SQL, SQL 2008 R2
          • Безопасные скрипты по-новому


            imagesCACUV4IU

            SQL Server 2008 содержит несколько давно ожидаемых расширений синтаксиса, которые облегчают написание скриптов вообще и написание безопасных скриптов в частности. Под безопасными скриптами мы будем понимать перезапускаемые скрипты, которые проверяют состояние данных перед тем как вносить изменения, и изменяют только нуждающиеся в изменениях данные. Например, перед добавлением новой строки будет произведена проверка наличия строки в таблице, и если ее нет, она будет добавлена. В предыдущей статье была кратко затронута тема написания безопасных скриптов с использованием “классического” TSQL. В этой статье мы рассмотрим, что предлагает SQL Server 2008 для облегчения задачи написания таких скриптов.

          • Главная SharePoint, SQL, Без рубрики, Новое Reporting Services, SharePoint, SQL 2008 R2
            • SQL Server 2008 R2. Интеграция Reporting Services и SharePoint 2010 шаг за шагом

              special_reportНе секрет, что продукт фирмы Microsoft SharePoint – это один из самых динамически быстро развивающихся продуктов. В SQL Server 2008 R2 появилась долгожданная возможность интеграции Reporting Services и SharePoint 2010. Если вдуматься, это вполне логичный шаг. В обоих этих продуктах используются одни и те же технологии, так почему же не скрестить их и попробовать, что из этого получиться? В данной статье остановлюсь на процессе инсталляции Reporting Services входящий в SQL Server 2008 R2 в режиме интеграции с SharePoint 2010, и какие новые возможности предоставляются администратору баз данных.

              В предыдущих версиях Reporting Services требовалось скачать дополнительный компонент для интеграции с SharePoint 2010, на сегодняшний день инсталляция этого компонента производиться автоматически. Давайте посмотрим шаг за шагом процесс установки Reporting Services в режиме интеграции с SharePoint 2010. Статья навеяна по мотивам практически исполняемой задачей интеграции этих двух продуктов. При разборе технической документации выяснилось, что написано достаточно много, но чтобы выстроить четкий план решения практической задачи интеграции Reporting Services SQL Server 2008 R2 и SharePoint 2010 – на это уходит достаточно много времени. Итак, приступим.

              Посмотрим на демонстрационное окружение:

              Имя компьютера

              Операционная система

              Службы и приложения

              DC Windows Server 2008 R2 Доменная служба Active Directory (test.local), DNS
              SPS Windows Server 2008 R2 SharePoint 2010, Microsoft SQL Server 2008 R2

              Это могут быть физические серверы, могут быть виртуальные под Hyper-V. С точки зрения безопасности лучше, чтобы домен контроллер был всегда выделенным без установленного лишнего программного обеспечения. Все программные продукты русской редакции, надо отметить, что фирма Microsoft серьезно потрудилась над переводом своих продуктов.

              Конечно же, для установки нам понадобиться дистрибутивы SQL Server 2008 R2 и SharePoint 2010. На первом этапе – предположим у вас уже есть установленный SharePoint 2010 на сервере SPS, начнем интеграцию с него.

              1. Вставляем дистрибутив SharePoint 2010. Если автозапуск не стартовал, с компакт диска запускаем файл splash.hta. В разделе «Установить» выбираем пункт «Установить необходимое ПО». Здесь нужен компонент «Настройка служб отчетов SQL Server 2008R2». Выбираем «Далее».

              alt

              Рисунок 1. Начало установки средств интеграции.

              1. Соглашаемся с лицензионным соглашением, перед этим внимательно и обстоятельно его читаем. Выбираем «Далее».
              2. На этом этапе программа установки SharePoint 2010 автоматически скачивает необходимые компоненты из Интернет и конфигурирует сервер. Дожидаемся окончания установки. Выбираем «Готово».

              alt

              Рисунок 2. Окончание установки средств интеграции.

              Надо отметить, что интеграция возможна только в том случае, если программный продукт SharePoint 2010 установлен в режиме фермы.

              В том случае, если служба Reporting Services и сервер фермы SharePoint находятся на разных серверах, нужно произвести минимальную установку SharePoint 2010 с присоединением сервера к существующей ферме, а затем переходить к следующему этапу.

              На втором этапе настроим сервер SPS, на котором уже установлен экземпляр по умолчанию SQL Server 2008 R2. Для этой цели нам понадобиться дистрибутив SQL Server 2008 R2.

              1. Вставляем дистрибутив SQL Server 2008 R2. Если автозапуск не стартовал, с компакт диска запускаем файл setup.exe. Выбираем справа «Установка» – «Новая установка или добавление компонентов к существующей установке».
              2. На странице «Правила поддержки установки» нажимаем «ОК». Если кнопка не доступна для нажатия нужно выбрать кнопку «Показать подробности» и посмотреть ошибки.
              3. На странице «Файлы поддержки программы установки» нажимаем «Установить».
              4. На странице «Правила поддержки установки» нажимаем «Далее». Если кнопка не доступна для нажатия нужно нажать «Показать подробности» и посмотреть ошибки.
              5. На странице «Тип установки» выбираем «Добавление компонентов к существующему экземпляру SQL Server R2». Нажимаем «Далее».

              alt

              Рисунок 3. Выбор типа установки.

              1. На странице «Выбор компонентов» отмечаем «Службы Reporting Services». Нажимаем «Далее» 3 раза.
              2. На странице «Конфигурация сервера» вводим доменную учетную запись, от имени которой будет запускаться служба Reporting Services. Нажимаем «Далее».
              3. На странице «Настройка служб Reporting Services» выбираем «Установить конфигурацию по умолчанию для работы в режиме интеграции с SharePoint». Нажимаем «Далее» 3 раза и затем «Установить».
              4. На странице «Готово» можно убедиться, что служба Reporting Services успешно установлена и нажать «Готово».
              5. Как видите все достаточно просто, в случае если и SharePoint 2010 и SQL Server 2008 R2 находится на одном сервере.

              Если на шаге 8 (страница «Настройка служб Reporting Services») выбрано «Установить, но не настраивать сервер отчетов» то службу Reporting Services необходимо будет настроить через инструмент «Диспетчер конфигурации служб Reporting Services»:

              1. Нажимаем последовательно «Пуск» – «Все программы» – «Microsoft SQL Server R2» – «Средства настройки» – «Диспетчер конфигурации служб Reporting Services» – «MSSQLServer»
              2. Если сервис остановлен, то на странице «SPS\MSSQLServer» нажимаем «Запуск»

              alt

              Рисунок 4. Стартовая страница диспетчера конфигурации

              1. На странице «Учетная запись службы» выбираем «Использовать другую учетную запись» – вводим доменную учетную запись и пароль, нажимаем «Применить».

              alt

              Рисунок 5. Страница выбора учетной записи службы.

              1. На странице «URL-адрес веб-службы» можно оставить все значения по умолчанию, нажимаем «Применить».
              2. На странице «База данных» настроим базу данных для режима интеграции с SharePoint:
                1. Нажимаем «Изменить базу данных».
                2. Выбираем «Создать новую базу данных сервера отчетов», нажимаем «Далее».
                3. На странице «Сервер базы данных» набираем имя экземпляра сервера SQL (в данном случае SPS) и учетную запись обладающая административными привилегиями создания базы данных, нажимаем «Далее».
                4. На странице «База данных» набираем имя базы данных, к примеру «ReportServer», и выбираем «Режим интеграции с SharePoint», нажимаем «Далее».

              alt

              Рисунок 6. Выбор базы данных.

              1. На странице «Учетные данные» можно выбрать учетные данные, отличные от данных текущего пользователя. От имени выбранной учетной записи SQL Server будет подключаться к базе данных сервера отчетов. По умолчанию выбирается учетная запись службы. Нажимаем «Далее»
              2. На странице «Сводка» еще раз просматриваем все выбранные установки и жмем «Установить».
              3. Нажимаем «Готово»

              После окончания создания новой базы данных в режиме интеграции с SharePoint, нажимаем «Применить».

              1. На странице «URL-адрес диспетчера отчетов» нужно указать виртуальный каталог. Можно оставить название по умолчанию «Reports». Нужно заметить, что напрямую к этому каталогу из браузера обратиться в режиме интеграции с SharePoint будет невозможно. Отчеты можно будет просматривать непосредственно с сервера SPS. Не забываем нажать «Применить».

              alt

              Рисунок 7 URL-адрес диспетчера отчетов.

              1. На следующем шаге чтобы убедиться, что все настроено правильно, нужно закрыть и снова открыть «Диспетчер конфигурации служб Reporting Services». Переходим на страницу «Масштабное развертывание». Проверим что «Режим сервера отчетов» действительно «Интеграция с SharePoint» и статус состояния «Соединенный».

              alt

              Рисунок 8. Проверка настройки Reporting Services.

              Настройка Reporting Services успешно завершена. Как уже упоминалось ранее, управлять отчетами Reporting Services SQL Server 2008 R2 в режиме интеграции с SharePoint можно только из инструмента «Центр администрирования». На третьем этапе, перед тем как начать управление нужно произвести следующие настройки на сервере SPS:

              1. Открываем «Центр администрирования» – «Пуск» – «Все программы» – «Microsoft SharePoint 2010 Products» – «Центр администрирования SharePoint 2010».
              2. Переходим «Общие параметры приложения» – «Службы Reporting Services» – выбираем ссылку «Интеграция со службами Reporting Services»

              alt

              Рисунок 9. Инструмент «Центр администрирования SharePoint 2010».

              1. Вводим необходимую информацию:
              • URL-адрес веб-службы сервера отчетов – http://sps/ReportServer
              • Режим проверки подлинности – проверка подлинности Windows
              • Имя пользователя – пользователь домена, обладающий административными привилегиями в ферме SharePoint 2010.

              Нажимаем «ОК»

              alt

              Рисунок 10. Страница «Интеграция со службами Reporting Services».

              1. В случае успешного ввода необходимой информации, процесс интеграции успешно завершается. Выбираем «Закрыть»

              alt

              Рисунок 11. Сводка информации по интеграции со службами Reporting Services.

              На последнем этапе остается задача создание отчетов прямо из необходимых библиотек. Здесь есть два подхода. Первый подход это создать узел SharePoint 2010 с использованием шаблона «BI Center». Этот подход не требует дополнительных усилий, на уровне библиотеки на таком узле уже доступны необходимые типы контента при создании нового документа. На втором подходе давайте остановимся поподробней, так как чаще всего встречается именно такая практическая задача. Подход заключается в том, чтобы в существующей библиотеке добавить, например новый тип контента, позволяющий создавать отчет Reporting Services. Итак, по шагам:

              1. Открываем необходимый узел SharePoint, например http://sps.
              2. Переходим в библиотеку «Общие документы» на ленте выбираем – «Библиотека» – «Параметры» – «Параметры библиотеки» (Если на ленте нет меню «Параметры» значит, пользователь под которым вы зашли не обладает необходимыми административными привилегиями).

              alt

              Рисунок 12. Навигация на ленте библиотеки SharePoint.

              1. В разделе «Общие параметры» переходим по ссылке «Дополнительные параметры» – В разделе «Типы контента» устанавливаем «Да». Тем самым мы разрешаем управление типами контента для текущей библиотеки. Выбираем внизу экрана «ОК»

              alt

              Рисунок 13. Включение выбора типа контента на уровне библиотеки.

              1. На странице «Библиотека документов» в разделе «Типы контента» переходим по ссылке «Добавление из существующих типов контента». В выпадающем списке выбираем «Типы содержимого сервера отчетов». Есть три типа – «Источник данных отчета», «Модель в построителях отчетов» и Отчет в построителях отчетов. Выбираем необходимые кнопкой «Добавить». Нажимаем «Ок».

              alt

              Рисунок 14. Выбор нужного типа контента на уровне библиотеки.

              Давайте проверим правильность произведенных настроек. На уровне библиотеки «Общие документы» на ленте выбираем «Документы» – «Создать документ». На ряду со стандартными типами контента, появилось три новых элемента «Источник данных контента», «Модель в построителе отчетов» и «Отчет в построителе отчетов».

              alt

              Рисунок 15. Создание нового отчета как документа библиотеки.

              Как можно убедиться, настройки прошли успешно теперь на уровне библиотеки «Общие документы» можно создать, к примеру, отчеты в Reporting Services. Нужно отметить, что построитель отчетов Report Builder 3.0 уже присутствует в системе и его устанавливать отдельно нет необходимости. Данная статья не охватывает работу с этим инструментом, полагаю, этот материал больше подойдет для отельной следующей статьи.

              Очевидно, что жизнь администраторов баз данных, которые занимаются построением отчетов с помощью Reporting Services, значительно облегчается с выходом Microsoft SQL Server 2008 R2, за счет унификации интерфейса и полной интеграции с продуктом Microsoft SharePoint 2010.

              И в заключение привожу полезные ссылки, которые помогут вам в дальнейшей работе:

              [1] Интеграция служб Reporting Services с SharePoint http://technet.microsoft.com/ru-ru/library/ee210649.aspx

              [2] Ален Ле Марканд Интеграция: Интеграция SQL Server 2008 R2 Reporting Services и SharePoint 2010

              http://technet.microsoft.com/ru-ru/magazine/ff686706.aspx

              Дмитрий Пронькин

            • Главная SQL, Без рубрики, Новое Scripts, SQL, SQL 2008 R2
              • Делаем простые вещи сложными или безопасные скрипты

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

                Также при современной разработке часто используются build –системы. Одним из шагов такой системы может быть применение скриптов к базе данных. Что означает, что одни и те же скрипты будут вызываться снова и снова. А значит их нельзя писать в произвольном стиле, скрипты должны быть пере-запускаемыми (re-runnable) или их еще называют безопасными (safe-style).

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

                По назначению мы можем разделить скрипты на две большие группы. Первая группа – это скрипты, которые изменяют метаданные. Если вам надо добавить колонку к таблице, создать новую таблицу и так далее – это скрипты первой группы. Вторая группа – скрипты изменяющие данные в таблицах. В этой статье мы рассмотрим только вторую группу скриптов, так как именно их чаще всего пишут программисты. Со схемой данных должны работать DBA, но есть одно исключение – хранимые процедуры, которые достойны отдельной статьи. Также есть еще один вид скриптов, которые часто оформляются в виде SSIS пакетов – это регулярные процессы. Удаление неактуальных данных, экспорт в архивную базу, пересчет чего-то – все что периодически запускается и поддерживает базу в бодром состоянии. Эти процессы тоже пишут и поддерживают DBA, мы же сосредоточимся на программистских скриптах, т.е. на скриптах которые пишут разработчики при работе над очередным релизом.

                Несколько оговорок. Первое, наши скрипты мы будем писать для Microsoft SQL Server. Второе, процесс разработки очень сильно различается от компании к компании. Я буду ориентироваться на некоторую абстрактную большую компанию, выбирая лучшее из своей практики. Что-то может быть лишним для вас или чего-то может недоставать. Смело присылайте мне свои соображения.

                Прежде всего, несколько общих замечаний.

                1. Заголовок скрипта.

                  Каждый уважающий себя скрипт должен иметь заголовок. В заголовке должно быть написано кто написал скрипт, зачем и когда. Автора нужно указывать, чтобы потом его можно было найти, если со скриптом возникнут проблемы. Цель добавления скрипта (имя проекта, номер тикета и так далее) поможет найти человека, кто отвечает за этот проект, чтобы уточнить неясные моменты. Дату создания скрипта можно не указывать, но иногда она бывает полезна чтобы составить представление об актуальности скрипта. Итого, заголовок может выглядеть так:

                2. –Description: Adding two more client profiles
                  –Ticket No : D0012345
                  –Author : Pupkin Ivan
                  –Created on : 19/08/2010

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

                3. Начало и конец скрипта должны быть обозначены оператором PRINT.Билд-система будет запускать скрипты один за другим. Или хитрый DBA слепит из всех скриптов один мега-скрипт и будет запускать его. Когда что-то пойдет не так, скрипт выдаст ошибку. Часто есть возможность получить текстовый вывод от запуска интерпретатора. Если начало и конец каждого скрипта обозначены, найти скрипт-источник ошибки легко. Если нет – придется решать ребусы. Итого – помещаем в начало и конец скрипта что-то вроде:
                4. print ‘BEGIN D0012345 script’
                  print ‘END D0012345 script’

                  Не надо писать строки вроде «Такого-то числа такого-то месяца, я, разработчик Василий, с трепетом в душе и верой в Бога, начинаю обработку пользовательских документов». DBA будет искать напечатанную строку в файлах скриптов, и если по строке можно найти ваш файл, это уже достаточно хорошо.

                5. Печатайте сообщения, когда ваш скрипт достиг (или не достиг) каких-то важных результатов. Когда ваш скрипт упадет на production сервере, и вам пришлют лог его выполнения и ошибку, будет гораздо легче разобраться в том, что же пошло не так, если вы делали правильные PRINT.
                6. if not exists(select …)
                  begin
                    <do something>
                    PRINT‘do completed successfully’
                  end
                  else
                    PRINT‘do has been already done’

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

                7. Три разные сущности реализуют концепцию уникальности строк в таблице – это первичный ключ, ограничение UNIQUE (иногда его называют альтернативным ключом) и уникальный индекс. Ключи физически реализуются с помощью индексов. Есть логическое различие, отличающее одно понятие от другого, но для наших целей эта разница не важна. Иногда для краткости я буду упоминать только первичный ключ, хотя на самом деле это может быть и UNIQUE и уникальный индекс.
                8. Внешние ключи и триггеры могут существенно разнообразить жизнь написателя скриптов. Я исхожу их того, что девелоперским скриптам не надо производить действия, вызывающие нарушение внешнего ключа, а триггеров либо нет, либо их работа абсолютно прозрачна.

                Три кита

                Теперь о самих скриптах, которыми мы будем изменять данные. У нас есть три оператора для изменения данных. Это INSERT, UPDATE и DELETE. Рассмотрим их по очереди.

                INSERT

                В таблицу можно добавить сколько угодно строк, если они не нарушают первичного ключа, ограничений UNIQUE и уникальных индексов. Можно создать таблицу, которая не будет содержать ничего из вышеперечисленного. В такую таблицу можно вставлять сколько угодно строк и не получить ни одой ошибки. Такие таблицы используются при операциях с данными, но не для постоянного хранения данных. Как правило, таблица имеет как минимум первичный ключ. Если вы попытаетесь добавить в таблицу строку с таким значением ключа, который уже есть в таблице, произойдет ошибка. Однако, есть подводные камни. Например, если в качестве первичного ключа используется тип данных DATETIME или автоинкрементное поле (IDENTITY) и на таблице больше не объявлено ни уникальных индексов, ни ограничений UNIQUE. Очень сложно представить себе ситуацию, когда произойдет нарушение первичного ключа в таком случае. Штамп времени меняется каждую долю секунды, автоинкрементное поле послушно выдает увеличенное на шаг значение. Такие ключи называются суррогатными, и если в таблице есть только суррогатный первичный ключ, для наших целей это то же самое, что не иметь первичного ключа вообще.

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

                if not exists(select 1 from dbo.Table1 where PK = 10)
                  INSERT INTO dbo.Table1 VALUES (10,…)

                Если в таблице нет такого значения, мы его добавляем. Почему возвращаем единицу из подзапроса, а не какую-нибудь колонку таблицы? Потому что нам все равно что возвращать, предикат EXISTS истинен если вложенный в него запрос вернет хоть что нибудь. Если бы я проверял наличие строки в таблице не по ключу, я записал бы так:

                if not exists( select top 1 1 from dbo.Table1 where nonPK = ‘abc’)
                  INSERT INTO dbo.Table1 VALUES (…)

                Потому что для срабатывания предиката EXISTS достаточно и одного значения.

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

                create table #tmp(ID int, Name nvarchar(50))

                insert into #tmp values (10, ‘aaa’)
                insert into #tmp values (11, ‘bbb’)

                INSERT INTO dbo.Table1
                SELECT *
                FROM #tmp t
                  left join dbo.Table1 t1
                  on t.ID = t1.ID
                WHERE
                  t1.ID is null

                drop table #tmp

                Если оператор левого соединения вызывает у вас протест, то INSERT можно переписать так:

                INSERT INTO dbo.Table1
                SELECT *
                FROM #tmp
                WHERE
                  ID not in (SELECT ID FROM dbo.Table1)

                Несмотря на то, что запрос со вложенным селектом многими понимается проще, я бы рекомендовал использовать JOIN во всех случаях.

                UPDATE

                Казалось бы, оператор обновления изначально является безопасным. Одну и ту же строку можно обновлять множество раз, без всяких ошибок. Но и здесь есть нюансы. Безопасно обновление только неключевых полей по ключевым. Такой оператор безопасен:

                UPDATE dbo.Table1
                SET Name =‘abc’
                WHERE ID = 10

                Маленькое замечание – если скрипт запускается многократно, нет смысла обновлять снова и снова то, что уже было обновлено. Эту проблему можно решить, проверяя изменяемое поле в WHERE.

                UPDATE dbo.Table1
                SET Name =‘abc’
                WHERE ID = 10
                  and Name <>‘abc’

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

                Например, рассмотрим такую таблицу, хранящую атрибуты клиента, оба поля в ней ключевые.

                CustomerID    AttribID
                ----------------------------
                1    100
                1    101
                2    100

                Рассмотрим две типичные задачи. Первая задача – поменять атрибут 101 у клиента 1 на атрибут 100.

                Запрос такого вида отлично работает на девелоперских и тестовых серверах (где нет первой строки) и падает на продакшен сервере (гда такая строка есть).

                UPDATE dbo.CustomerAttributes
                SET AttribID = 100
                WHERE CustomerID = 1
                   and AttribID = 101

                Почему именно так а не иначе? По закону Мерфи о полноте базы данных.

                Усложним запрос, чтобы избежать неприятностей:

                UPDATE ca
                SET ca.AttribID = 100
                FROM dbo.CustomerAttributes ca
                  left join dbo.CustomerAttributes ca1
                    on ca.CustomerID = ca1.CustomerID
                      and ca1.AttribID = 100
                WHERE ca.CustomerID = 1
                   and ca.AttribID = 101
                   and ca1.CustomerID is null

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

                UPDATE ca
                SET ca.AttribID = 100
                FROM dbo.CustomerAttributes ca
                WHERE ca.CustomerID = 1
                  and ca.AttribID = 101
                  and not exists (select 1 from dbo.CustomerAttributes
                    where CustomerID = 1 and AttribID = 100)

                Также можно записать этот UPDATE как DELETE и безопасный INSERT.

                begin tran

                DELETE FROM dbo.CustomerAttributes
                WHERE CustomerID = 1
                  and AttribID = 101

                if not exists(
                  select 1
                  from dbo.CustomerAttributes
                  where CustomerID = 1 and AttribID = 100
                  )
                  INSERT INTO dbo.CustomerAttributes VALUES (1, 100)

                commit tran

                Вторая задача – атрибут 101 больше не используется, вместо него надо использовать атрибут 100 для всех клиентов. Решаем через удаление 101 атрибута для тех клиентов, у которых есть оба атрибута, и обновлением 101 атрибута на 100 для тех, у кого есть только 101 атрибут. Это DELETE и UPDATE. Можно записать во временную таблицу всех клиентов, у которых есть 101 атрибут, удалить все строки со 101 атрибутом, безопасно вставить клиентов с 100-м атрибут из временной таблицы, DELETE + INSERT.

                Третья задача – клиенты 1 и два объединились, теперь это один клиент. Надо объединить атрибуты клиентов 1 и 2. В этом случае нам надо добавить к клиенту 1 те атрибуты, которые есть у клиента 2, но нет у клиента 1, затем удалить все атрибуты клиента 2, INSERT + DELETE.

                Итого, безопасный UPDATE должен обновлять данные только однажды, и правильно обрабатывать изменения в первичном ключе.

                DELETE

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

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

                Триггеры какому-либо обобщению не поддаются. Например, они могут проверять некоторые условия, и не позволять удаления если условия не выполняются. С триггерами проблем меньше всего. Потому что если они не разрешают какое-либо изменение, то и вам не стоит его делать. В крайнем случае триггеры можно временно отключить.

                Транзакции

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

                Первое – что они обязательно нужны. На самом деле это не так. Особенно это верно для безопасных скриптов. Мы их пишем специально таким образом, чтобы их можно было перезапускать, и при перезапуске они проверяют состояние данных, внося изменения по мере необходимости. Поэтому в большинстве девелоперских скриптов транзакции не нужны. У транзакций есть неочевидные минусы. Работающая транзакция накапливает блокировки, и освобождает их после финального commit. Чем больше транзакция – тем большая часть базы данных оказывается задействована. Во время работы транзакции другие транзакции вынуждены ждать. Это наблюдается в виде «тормозов». Если скрипты запускают на сервере под нагрузкой, такие «тормоза» стараются минимизировать. На самом деле транзакции необходимы только для рисковых операций. Если логика процесса такова, что при возникновении ошибки мы получим нарушение целостности (кто-то недополучит денег, или получит слишком много, появятся «висящие» строки, и так далее), то транзакция строго необходима.

                Второе заблуждение – что оператора транзакции достаточно. На самом деле нет. Обработка ошибок на SQL Server заслуживает отдельного описания, но я упомяну только один нюанс. Не достаточно написать begin transaction в начале скрипта и commit transaction в конце. Потому что откат транзакции вызывают не все ошибки, как многие ожидают. То есть возможна ситуация, когда в контексте транзакции произойдет ошибка, но выполнение транзакции будет продолжено. Пример – ошибки преобразования типа. Это расстраивает, но решение простое. Достаточно установить переменную xact_abort в on. В этом случае практически все ошибки будут вызывать откат транзакции. Увы, все равно возможны ошибки, которые не вызовут откат транзакции. Но транзакция с установленной переменной уже достаточно надежна, а все редкие исключения можно отследить и исправить вручную. Итого, транзакция должна выглядеть так:

                set xact_abort on
                begin tran

                commit tran

                Заключение

                Хоть было сказано и достаточно много слов, написание безопасных скриптов совсем не трудно. Больше всего хлопот причиняет оператор INSERT, потому что приходится дописывать проверку существования записей. Оператор UPDATE требует внимания в редких случаях. DELETE не требует внимания почти никогда. К месту и со вкусом примененная транзакция может сделать жизнь значительно легче. В результате мы получаем перезапускаемый скрипт, который легко можно комбинировать с другими скриптами и встраивать в build и deployment системы.

                Александр Синицын

              • Главная SQL, Без рубрики, Новое SQL, SQL 2008 R2
                • Microsoft StreamInsight и обработка сложных событий Complex Event Processing (CEP)

                  00_logo С выходом новой версии SQL Server 2008 R2 Microsoft представила платформу StreamInsight для создания приложений обработки сложных событий. Главной задачей CEP приложений является обработка в режиме реального времени множества событий из различных источников (потоков событий) с целью выявления значимых событий, основанных на одном или нескольких потоков событий, либо выявления ряда событий, за определенный промежуток времени.

                • Главная SQL, Без рубрики, Новое SQL, SQL 2008 R2
                  • Интеграция Данных

                    data_monthly2 Служба Integration Services, входящая в SQL Server 2008 R2
                    является платформой для построения высокоэффективной интеграции данных и для решения задач, связанных с последовательно выполняемыми действиями, включающими операции по извлечению, преобразованию и загрузке (ETL) данных в хранилище. Функциональные возможности службы Integration Services SQL Server 2008 R2 имеют улучшенную производительность, благодаря лучшей поддержке многопоточности в мультипроцессорных системах и высокоэффективным коннекторам доступа к источникам данных сторонних производителей. Платформа Integration Services в SQL Server 2008 R2 версии Enterprise превосходит традиционный процесс ETL благодаря набору адаптеров и преобразований для "добывания" данных, очистки данных и поддержке, близкой к реальному времени, среды окружения Analysis
                    Services.

                  • Главная SQL, Без рубрики, Новое SQL, SQL 2008 R2, StreamInsight, Конкурс
                    • Обзор технологии Microsoft StreamInsight

                      SQL-Server-StreamInsight-CTP2-Available-for-Download-2 Технология StreamInsight – это новая технология для анализа данных от Microsoft. Microsoft StreamInsight предназначен для обработки потоков событий и базируется на технологии Complex Event Processing.

                      Complex Event Processing (CEP) – это непрерывная инкрементальная обработка потоков событий из множества источников на основании декларированных запросов и шаблонов с близкой к нулю задержкой.

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

                      В парадигме СУБД данные сначала должны быть загружены на сервер, а это уже может потребовать значительных затрат ресурсов и времени. Затем, нужно сделать запрос к базе данных, чтобы получить некоторый ответ, на основании которого можно принять то или иное решение.

                      Понятно, что для описанного примера использование подхода СУБД, возможно, является не самой лучшей идеей.

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

                      Данные из входного потока обрабатываются инкрементально, то есть CEP движку не нужно обращаться к старым данных, если в этом нет необходимости.

                      В таблице приводится сравнение СУБД и CEP по различным параметрам. Видно, что CEP позволяет обрабатывать большое количество данных с малой задержкой, в то время как СУБД не позволяет этого сделать.

                      СУБД

                      CEP

                      Парадигма запросов Запросы выполняются по требованию Запросы обрабатываются непрерывно
                      Задержка Секунды, часы, дни Миллисекунды или меньше
                      Пропускная способность Сотни записей/сек Десятки тысяч записей/сек

                      Связь StreamInsight и Microsoft SQL Server 2008 R2

                      StreamInsight поставляется вместе с SQL Server 2008 R2 и является частью платформы обработки данных MS SQL, однако StreamInsight никак не относится к Database Engine и не зависит от SQL Server (обратное тоже верно).

                      Так что связь SQL Server и StreamInsight является достаточно условной.

                      Более того, StreamInsight можно скачать отдельно от MS SQL Server.

                      С другой стороны, при установке StreamInsight отдельно от MS SQL Server, нужно ввести ключ активации MS SQL Server 2008 R2 или выбрать ознакомительный 180-дневный режим. Полностью бесплатную версию я найти не смог.

                      Возможности StreamInsight зависят от редакции MS SQL Server 2008 R2, с которой ассоциирован вводимый ключ, и представлены в таблице:

                      Возможности StreamInsight Редакция Microsoft SQL Server 2008 R2
                      Standard

                      Тысячи событий/сек

                      Задержка: секунды

                      Standard

                      Enterprise

                      Web

                      Premium

                      Десятки тысяц событий/сек

                      Задержка: менее секунды

                      Datacenter

                      Developer

                      Evaluation

                      Установка StreamInsight

                      Дистрибутив StreamInsight можно скачать по ссылке

                      http://www.microsoft.com/sqlserver/2008/en/us/R2-complex-event.aspx, на момент написания статьи доступны версии для 32-битных и 64-битных операционных систем.

                      Системные требования

                      Аппаратное обеспечение:

                      • Рекомендуется: 2.2 GHz CPU, 1024 MB RAM
                      • Минимум: 1.6 GHz CPU, 384 MB RAM

                      Программное обеспечение:

                      Поддерживаются следующие операционные системы:

                      • Windows XP Service Pack 2 и более новые (x86 и x64)
                      • Windows Server 2003 Service Pack 2 и более новые (x86 и x64)
                      • Windows Server 2003 R2 и более новые (x86 и x64)
                      • Windows Vista (x86 и x64)
                      • Windows Server 2008 и более новые (x86 и x64)
                      • Windows 7 (x86 и x64)

                      Дистрибутив занимаем около 10 Mb, установка происходит при помощи простого мастера. Наиболее важный вопрос при установке – это имя инстанса StreamInsight – это имя нужно обязательно запомнить, поскольку оно используется при программировании.

                      После установки необходимо удалить старую версию Microsoft SQL Server Compact (Панель управления->Установка и удаление программ) и установить новую (идущую в комплекте со StreamInsight). На момент написания статьи, это Microsoft SQL Server Compact 3.5 SP1.

                      В комплект установки входят следующие компоненты (после установки их можно найти в папке c:\Program Files\Microsoft StreamInsight 1.0\):

                      Файлы Путь
                      Microsoft.ComplexEventProcessing DLLs*

                      StreamInsightDumper

                      C:\Program Files\Microsoft StreamInsight 1.0\Bin
                      Документация StreamInsight C:\Program Files\Microsoft StreamInsight 1.0\Documentation\<LanguageFolder>
                      Конфигурация и исполняемые файлы Microsoft StreamInsight Server C:\Program Files\Microsoft StreamInsight 1.0\Host

                      C:\Program Files\Microsoft StreamInsight 1.0\Host\<InstanceName>

                      Лицензия Microsoft StreamInsight C:\Program Files\Microsoft StreamInsight 1.0\license\<LanguageFolder>
                      Пакет установки SQL Server Compact Edition C:\Program Files\Microsoft StreamInsight 1.0\Redist
                      Отладчик потоков событий Microsoft StreamInsight

                      Для Windows XP и Windows Server 2003 урезанные версии

                      C:\Program Files\Microsoft StreamInsight 1.

                      * – Сборки регистрируются в GAC во время установки

                      Схема работы StreamInsight

                      Рассмотрим более подробно схему работы StreamInsight:

                      Данные могут поступать с различных источников. Например, это могут быть различные сенсоры, веб-сервисы, хранилища данных и другие источники. Информация из таких источников, скорее всего, является разнородной (например, имеет разный формат), для преобразования этой информации в события, понятные системе, используются входные адаптеры (Input Adapters). Полученные события поступают на движок StreamInsight, где обрабатываются заранее описанными запросами. Причем, данные могут обрабатываться несколькими запросами по очереди, может также использоваться информация из внешних источников данных. Всю работу по максимально быстрой обработке событий берет на себя движок StreamInsight. Обработанные данные нужно каким-то образом передать получателям событий. Для преобразования данных в удобную для получателей форму используются выходные адаптеры (Output Adapters).

                      Основные понятия

                      К основным структурым элементам StreamInsight относятся:

                      • Потоки
                      • События
                      • Адаптеры
                      • Запросы

                      Рассмотрим каждый элемент:

                      Потоки

                      Поток – это набор событий, который, в общем случае, может быть бесконечным.

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

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

                      События

                      Пожалуй, основной элемент StreamInsight – это событие.

                      Любое событие в StreamInsight состоит из двух частей:

                      • Заголовок
                        • Тип события (Event Kind)
                        • Временные отметки (Timestamps)
                      • Нагрузка (Payload)
                        • Данные

                      Событие может иметь одну или более (начало события, конец события) временных отметок. Время задается типом DateTime в фомате UTC. Стоит отметить, что временные отметки задаются программно, то есть разработчик отвечает за корректное заполнение временных отметок, а не движок StreamInsight.

                      Тип события может иметь значения INSERT (новые данные) и CTI (Current Time Increment). Событие CTI используется для корректной обработки данных в тех случаях, когда события поступают в неправильной хронологической последовательности. Событие CTI по сути сообщает о том, что все события, появившиеся до временной отметки, соответствующей событию CTI, уже были отправлены и теперь могут быть обработаны.

                      Рассмотрим пример:

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

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

                      Добавим события CTI. Событие CTI говорит о том, что можно начать обрабатывать события лежащие левее его по оси времени. Таким образом мы вычисляем среднее значение 1.

                      Идем дальше: пусть появилось еще некоторое количество событий и событие CTI 2. Теперь все события, лежащие левее CTI 2 могут быть обработаны.

                      Что произойдет если после появления события CTI появилось новое событие лежащее левее него по оси времени (обозначено красным)? Ведь получается, что среднее значение 1 уже не является актуальным.

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

                      Существует два вида таких политик:

                      • Drop – отбрасывать все подобные события
                      • Adjust – изменять временные характеристики события таким образом, чтобы оно оказалось правее последнего события CTI на временной оси. Правда такая политика применима не ко всем событиям, а лишь к тем которые обладают моделью отличной от точечной.

                      Что такое модель события?

                      Еще одной характеристикой события является его модель (Event Model), существует три модели событий:

                      • Interval – известен период времени, в течении которого происходило событие

                        Пример: температура в комнате с 14-00 до 16-00 была равна 21 градусу.

                      • Point – точечная модель, событие произошло мгновенно.

                        Пример: атмосферное давление в 14-30 было равно 760 мм.

                      • Edge – известно время начала события, но не известно его время окончания

                        Пример: дождь начался в 18-00 (пока не закончился)

                      Рассмотрим примеры:

                      Interval

                      Тип события Начало Конец Нагрузка (Id)
                      INSERT 2009-12-27

                      02:04:00.213

                      2009-12-27

                      02:04:04.329

                      EU-23423-12
                      INSERT 2009-12-27

                      02:04:04.329

                      2009-12-27

                      02:04:08.234

                      EU-23423-15
                      INSERT 2009-12-27

                      02:04:04.234

                      2009-12-27

                      02:04:04.523

                      EU-23423-18

                      Point

                      Тип события Начало Конец Нагрузка (Id)
                      INSERT 2009-12-27

                      02:04:00.213

                      2009-12-27

                      02:04:00.213 + c

                      EU-23423-12
                      INSERT 2009-12-27

                      02:04:04.329

                      2009-12-27

                      02:04:04.329 + c

                      EU-23423-15
                      INSERT 2009-12-27

                      02:04:04.234

                      2009-12-27

                      02:04:04.234 + c

                      EU-23423-18

                      Где с – это наименьшая измеримая единица времени.

                      События типа Edge обычно состоят из двух событий (начало и конец события):

                      Тип события Тип границы Начало Конец Нагрузка (Id)
                      INSERT Start 2009-12-27

                      02:04:00.213

                      EU-23423-12
                      INSERT End 2009-12-27

                      02:04:00.213

                      2009-12-27

                      02:04:04.329

                      EU-23423-12
                      INSERT Start 2009-12-27

                      02:04:04.234

                      EU-23423-18
                      INSERT End 2009-12-27

                      02:04:04.234

                      2009-12-27

                      02:04:08.238

                      EU-23423-18

                      ∞ – на самом деле DateTime.MaxValue

                      Нагрузка

                      Нагрузка – это те данные, которые сопровождают событие. Например, текущее значение температуры, полученное с термометра.

                      Нагрузка описывается стандартной структурой на C#, однако существует ряд ограничений, отметим основные (полный список ограничений может быть найден в Microsoft StreamInsight Help -> Developer’s Guide -> Creating Event Types):

                      • Размер события не должен превышать 16Kb с учетом всей служебной информации.
                      • Событие должно иметь не менее одного поля нагрузки
                      • Допустимы только скалярные и элементарные типы (byte,int,byte[],string,datetime)
                      • Нагрузка должна быть упакова в класс или структуру C# (даже если в нагрузке содержится только одно поле, оно должно быть размещено в структуре или классе)
                      • Нельзя использовать пользовательские атрибуты для полей

                      Следующий класс описывает нагрузку с двумя полями типа int.

                      public class SimplePayload

                      {

                      public int V1 { get; set; }

                      public int V2 { get; set; }

                      }

                      Адаптеры

                      Как было замечено ранее, адаптеры служат для преобразования потоков данных. Адаптеры делятся на входные и выходные. Входные адаптеры преобразуют данные полученные с источников, в формат понятный StreamInsight, а выходные адаптеры преоразуют данные в формат понятный конечным получателям.

                      Основные задачи, которые нужно решить при разработке адаптеров:

                      Определить тип адаптера.

                      Входной или выходной.

                      Определить тип событий.

                      Адаптер может быть типизированным либо нетипизированным. Структура событий для типизированных адаптеров известна заранее (описана в виде класса), а для нетипизиванных неизвестна (например, данные получаются с использованием DataReader из Microsoft SQL Server).

                      Определить модель событий.

                      Point, Interval или Edge. Рекомендуется создавать отдельный адаптер для каждой модели событий.

                      Выбрать подходящий базовый класс для адаптера.

                      Существуется несколько базовых классов адаптеров для каждого типа адаптера и модели события.

                      Тип адаптера Базовый класс входного адаптера Базовый класс выходного адаптера
                      Типизированный, Point TypedPointInputAdapter TypedPointOutputAdapter
                      Нетипизированный, Point PointInputAdapter PointOutputAdapter
                      Типизированный, Interval TypedIntervalInputAdapter TypedIntervalOutputAdapter
                      Нетипизированный, Interval IntervalInputAdapter IntervalOutputAdapter
                      Типизированный, Edge TypedEdgeInputAdapter TypedEdgeOutputAdapter
                      Нетипизированный, Edge EdgeInputAdapter EdgeOutputAdapter

                      Создать фабрики для входных и выходных адаптеров

                      Тип адаптера Базовый класс фабрики входного адаптера Базовый класс фабрики выходного адаптера
                      Типизированный ITypedInputAdapterFactory ITypedOutputAdapterFactory
                      Нетипизированный IInputAdapterFactory IOutputAdapterFactory

                      Далее перечислены основные обязанности фабрики:

                      • Разделяет ресурсы между сходными адаптерами, различающимися только моделью событий.
                      • Предоставляет интерфейс Create() и Dispose(). С их помощью адаптеры работают с событиями.
                      • Автоматически создает CTI события с учетом пользовательских настроек.

                      Разработчик адаптера должен имплементировать методы Start() и Resume() базового класса, в которых производится преобразование событий.

                      Базовые классы предоставляют методы Enqueue() и Dequeue(), которые должны использоваться во входном и выходном адаптерах соответственно для обработки событий. Например, входной адаптер преобразует событие в формат понятный StreamInsight и помещает его в очередь, используя метод Enqueue().

                      Очень подробно разработка адаптеров описана в разделе Creating Input and Output Adapters документации StreamInsight.

                      Запросы

                      Пожалуй, наиболее интересным с точки зрения разработчика элементом StreamInsight являются запросы.

                      Запросы StreamInsight представляют собой не что иное, как LINQ запросы к потокам данных.

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


                      var queryOutput = from e in input


                      select e;

                      Такой запрос просто возвращает все полученные события.

                      StreamInsight запросы предоставляют следующие возможности:

                      • Проекции (Project)
                      • Фильтры (Filter)
                      • Корелляция потоков (Join)
                      • Объединения (Union)
                      • Агрегация (Aggregation)
                      • Оконные операции

                      Рассмотрим каждую из возможностей:

                      Проекции

                      Проекции используется в случаях, когда нужно:

                      • Получить только часть данных из входного потока.
                      • Произвести вычисления каких-либо значений.
                      • Создать новые поля, основываясь на имеющихся во входном потоке полях.

                      Следующий запрос использует пользовательскую функцию для получения нового поля:


                      var queryOutput = from e in input


                      select
                      new { e.Lane, e.TagId,

                      VehicleType = TollPointEvent.VehicleTypeName(e.VehicleTypeId) };

                      Фильтры

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

                      Следующий запрос выбирает только те события, для которых выполняется условие из блока where:


                      var queryOutput = from e in input


                      where e.VehicleTypeId == 2


                      select
                      new { e.Lane, e.TagId, VehicleType = TollPointEvent.VehicleTypeName(e.VehicleTypeId) };

                      Корелляция потоков

                      Используется операция Join, хорошо знакомая разработчикам, использующим SQL. Разница в том, что объединятся не все события, а только те, которые произошли в один и тот же промежуток времени.

                      Следующий запрос выполняет cross join двух потоков событий.


                      var queryOutput = from nbv in northboundVehicles


                      from sbv in lane0Vehicles


                      select
                      new

                      {

                      ExitNorth = nbv.ExitGate,

                      NorthVehicle = nbv.TagId,

                      TollPointId = sbv.TollPointId

                      };

                      Объединение

                      Объединение потоков просто объединяет все события их двух потоков в один. В отличие от корелляции, при объединении потоков информация о временных характеристиках событий не играет роли.

                      Следующий запрос объединяет события их двух разных потоков.


                      var northboundVehicles = from e in input


                      where e.DirectionId == 0


                      select e;


                      var lane0Vehicles = from e in input


                      where e.Lane == 0


                      select e;


                      var queryOutput = northboundVehicles.Union(lane0Vehicles);

                      Безусловно, можно объединять более двух потоков, последовательно вызывая Union()

                      Агрегация

                      Агрегация позволяет вычислять некоторую функцию по набору событий. К таким фукнциям относятся: Avg, Sum, Count и т.д. Также можно использовать пользовательские функции. Функции агрегации можно применять только к наборам событий, в данном случае – только к окнам.

                      Окна позволяют объединять события (для последующей обработки) на интервалах, которые могут задаваться временными или количественными характеристиками.

                      Для окон поддерживаются следующие виды операций:

                      • Агрегация.
                      • TopK – ранжирование.
                      • Пользовательские операторы.

                      В StreamInsight существует четыре типа окон:

                      • Временные окна: Hopping Window и Tumbling Window
                      • Окна моментальных снимков: Snapshot Window
                      • Окна количества: CountByStartTime Window

                      Hopping Window (прыгающее окно)

                      Для прыгающего окна определяются два параметра: H – размер «прыжка» и размер окна S. Новое окно создается через каждые H моментов времени (окно «прыгает»), а размер этого окна равен S.

                      Если H равно S, такое окно называется Tumbling Window (окно вращения).

                      Следующий запрос вычисляет сумму по всем событиям, произошедшим за последний час, каждые 10 минут.

                      var hoppingAgg = from w in inputStream.HoppingWindow(TimeSpan.FromHours(1),

                      TimeSpan.FromMinutes(10),

                      HoppingWindowOutputPolicy.ClipToWindowEnd)

                      select new { sum = w.Sum(e => e.i) };

                      Обратите внимание, что при создании окна используется параметр, задающий политику HoppingWindowOutputPolicy.ClipToWindowEnd. Интересным является тот факт, что в текущей версии StreamInsight существует всего один вариант задания данной политики (правда он называется по-разному для каждого типа окна).

                      Snapshot Window (Окно моментального снимка)

                      Параметры окон моментального снимка определяются только событиями в потоке. Появление нового события или окончание старого приводит к окончанию текущего окна и созданию нового (если какие-либо события еще не закончены). Рисунок хорошо иллюстрирует сказанное:

                      Следующий запрос создает окно мементального снимка:

                      var snapshotAgg = from w in inputStream.SnapshotWindow(WindowInputPolicy.ClipToWindow,

                      SnapshotWindowOutputPolicy.Clip)

                      select new { sum = w.Sum(e => e.i) };

                      CountByStartTime Window (Окно количества)

                      Размер окон количества зависит от количества событий с разными временными отметками (точнее, с разным временем начала события). Если все события имели разное начальное время, то окно будет содержать ровно N (параметр окна количества) событий.

                      Окно количество можно задать следующим запросом:

                      var agg = from w in inputStream.CountByStartTimeWindow(10, CountWindowOutputPolicy.PointAlignToWindowEnd)

                      select new { sum = w.Sum(e => e.i) };

                      Для более подробного ознакомления с окнами событий, я рекомендую обратиться к соответствующему разделу документации StreamInsight (Developer’s Guide->Writing Query Templates in LINQ->Using Event Windows).

                      Этапы разработки

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

                      1. Определить параметры событий и создать класс нагрузки
                      2. Создать входные адаптеры и фабрики входных адаптеров для каждого типа источников данных
                      3. Создать шаблоны StreamInsight запросов с использованием LINQ
                      4. Создать выходные адаптеры и фабрики выходных адаптеров для каждого типа получателей событий
                      5. Связать все компоненты системы вместе

                      Также нужно отметить, что движок StreamInsight может работать в нескольких режимах:

                      • Как отдельный сервис
                      • Как встроенная часть приложения

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

                      Пример

                      Теперь пора перейти к примеру. Я буду использовать пример, идущий в комплекте с SQL Server 2008 R2 Update for Developers Training Kit, который называется HighwayMonitor.

                      Задача:

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

                      Существует пять типов машин:

                      • Скорая (Ambulance)
                      • Автобус (Bus)
                      • Грузовик (Truck)
                      • Такси (Taxi)
                      • Легковой автомобить (Car)

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

                      Пока будем просто выводить события в DataGridView.

                      Открываем проект в Visual Studio 2010.

                      В окне Solution Explorer посмотрим на структуру проекта:

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

                      Определим формат событий в файле TollPointTypes.cs


                      public
                      class
                      TollPointEvent : IRandomInit

                      {


                      public
                      static
                      int TollPoints = 6;


                      public
                      static
                      int Lanes = 8;


                      public
                      static
                      Random rand;


                      public
                      Guid EventID;


                      public
                      Int32 TollPointId;


                      public
                      Int32 DirectionId;


                      public
                      Int32 Lane;


                      public
                      Int32 VehicleTypeId;


                      public
                      string TagId;


                      public
                      DateTime EnterGate;


                      public
                      Int32 MillisecondsToPassSpeedCheckPoint;


                      public
                      DateTime ExitGate;


                      public TollPointEvent()

                      {

                      }


                      public
                      void Init()

                      {


                      if (null == rand)

                      {

                      rand = new
                      Random();

                      }


                      this.EventID = Guid.NewGuid();


                      this.TollPointId = rand.Next(TollPoints);


                      this.DirectionId = PickDirection(rand);


                      this.Lane = rand.Next(Lanes);


                      this.VehicleTypeId = PickVehicleTypeId(rand);


                      this.TagId = rand.Next(Int32.MaxValue).ToString();


                      this.ExitGate = DateTime.Now;


                      Int32 vehicleSpeed = rand.Next(30, 120); // generate car speeds between 30 and 120 kph


                      //Int32 vehicleSpeed = 80;


                      Int32 vehicleLength = PickLength(rand, this.VehicleTypeId);


                      this.EnterGate = CalcEnter(this.ExitGate, vehicleSpeed, vehicleLength);


                      this.MillisecondsToPassSpeedCheckPoint = CalcPassSpeedCheckPoint(this.EnterGate, vehicleSpeed);


                      Trace.WriteLine(this.EventID.ToString());

                      }

                      }

                      Также в классе содержатся дополнительные методы, но для нас они не слишком важны.

                      В файле TollPointInput.cs содержится описание входного адаптера. Поскольку у нас нет реальных датчиков, адаптер самостоятельно создает события.


                      public
                      class
                      TollPointInput<TollPointEvent> : TypedPointInputAdapter<TollPointEvent>

                      {


                      private
                      TollPointInputConfig _config;


                      public TollPointInput(TollPointInputConfig config)

                      {

                      _config = config;

                      }


                      public
                      override
                      void Resume()

                      {

                      ProduceEvents();

                      }


                      public
                      override
                      void Start()

                      {

                      ProduceEvents();

                      }


                      private
                      void ProduceEvents()

                      {


                      PointEvent<TollPointEvent> currEvent = default(PointEvent<TollPointEvent>);


                      EnqueueOperationResult result = EnqueueOperationResult.Full;


                      Random rand = new
                      Random();


                      while (!HighwayMonitor.Program.MainWindow.NeedToStop)

                      {


                      if (AdapterState.Stopping == AdapterState)

                      {

                      Stopped();


                      return;

                      }

                      currEvent = CreateInsertEvent();


                      if (null == currEvent)

                      {


                      continue;

                      }

                      currEvent.StartTime = DateTime.Now;


                      currEvent.Payload = (TollPointEvent)Activator.CreateInstance(typeof(TollPointEvent));

                      (currEvent.Payload as
                      IRandomInit).Init();

                      result = Enqueue(ref currEvent);


                      if (EnqueueOperationResult.Full == result)

                      {

                      ReleaseEvent(ref currEvent);

                      Ready();


                      return;

                      }


                      Thread.Sleep(rand.Next(1,500));

                      }


                      this.Stopped();

                      }

                      }

                      Мы знаем параметры событий (TollPointEvent), а наши события являются мгновенными, так что в качестве базового класса выбран TypedPointInputAdapter.

                      Реализованы методы Resume() и Start(), которые просто вызывают метод ProduceEvents(), на котором стоит остановиться подробнее.

                      currEvent = CreateInsertEvent();

                      Создает новое событие с типом INSERT, далее поля события (включая время возникновения) заполняются. После того как все поля заполнены, вызывается метод Enqueue(), который ставит новое событие в очередь обработки.

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

                      В файле TollPointInputFactory.cs описана фабрика входных адаптеров, метод Create которой просто возвращает экземпляр только что рассмотренного адаптера.


                      public
                      class
                      TollPointInputFactory : ITypedInputAdapterFactory<TollPointInputConfig>, ITypedDeclareAdvanceTimeProperties<TollPointInputConfig>

                      {


                      public
                      InputAdapterBase Create<TollPointEvent>(TollPointInputConfig configInfo, EventShape eventShape)

                      {


                      return
                      new
                      TollPointInput<TollPointEvent>(configInfo);

                      }


                      public
                      void Dispose()

                      {

                      }


                      public
                      AdapterAdvanceTimeSettings DeclareAdvanceTimeProperties<TPayload>(TollPointInputConfig configInfo, EventShape eventShape)

                      {


                      var atgs = new
                      AdvanceTimeGenerationSettings(configInfo.CtiFrequency, TimeSpan.FromSeconds(0), true);


                      var ats = new
                      AdapterAdvanceTimeSettings(atgs, AdvanceTimePolicy.Drop);


                      return ats;

                      }

                      }

                      Метод DeclareAdvanceTimeProperties устанавливает политику работы с событиями CTI.

                      Первая строка метода создает настройки времени таким образом, чтобы событие CTI создавалось после каждого configInfo.CtiFrequency-го события. В основной программе для этого параметра будет задано значение 1, это значит, что событие CTI будет создаваться после каждого события.

                      Во второй строчке указывается политика обработки событий, которые поступили позже события CTI, но их временная отметка более раняя. В данном случае устанавливается значение Drop (на самом деле мы не могли установить другое значение, поскольку мы работаем с мгновенными (Point) событиями).

                      Файл GridOutputAdapter.cs содержит описание выходного адаптера


                      public
                      class
                      GridOutputAdapter : PointOutputAdapter

                      {


                      private
                      EventWaitHandle _adapterStopSignal;


                      private
                      CepEventType _bindtimeEventType;


                      private
                      int _eventsDequeued = 0;


                      public GridOutputAdapter(string StopSignalName, CepEventType EventType)

                      {

                      _bindtimeEventType = EventType;

                      _adapterStopSignal = EventWaitHandle.OpenExisting(StopSignalName);

                      }


                      public
                      override
                      void Start()

                      {


                      List<string> columnHeaders = new
                      List<string>();

                      columnHeaders.Add(“Command”);

                      columnHeaders.Add(“Timestamp”);


                      for (int fieldCounter = 0; fieldCounter < _bindtimeEventType.FieldsByOrdinal.Count; fieldCounter++)

                      {


                      CepEventTypeField eventFieldType = _bindtimeEventType.FieldsByOrdinal[fieldCounter];

                      columnHeaders.Add(eventFieldType.Name);

                      }

                      HighwayMonitor.Program.MainWindow.AddEventToDisplayListBox(columnHeaders.ToArray(), _bindtimeEventType.FieldsByOrdinal.Count + 2);

                      ConsumeEvents();

                      }


                      public
                      override
                      void Resume()

                      {

                      ConsumeEvents();

                      }


                      protected
                      override
                      void Dispose(bool disposing)

                      {


                      base.Dispose(disposing);

                      }


                      private
                      void ConsumeEvents()

                      {


                      PointEvent currEvent = default(PointEvent);


                      DequeueOperationResult result;


                      try

                      {


                      while (true)

                      {


                      if (AdapterState.Stopping == AdapterState)

                      {

                      result = Dequeue(out currEvent);

                      PrepareToStop(currEvent, result);

                      Stopped();

                      _adapterStopSignal.Set();


                      return;

                      }


                      result = Dequeue(out currEvent);


                      if (DequeueOperationResult.Empty == result)

                      {

                      PrepareToResume();

                      Ready();


                      return;

                      }


                      else

                      {

                      _eventsDequeued++;


                      HighwayMonitor.Program.MainWindow.AddEventToDisplayListBox(CreateStringArrayFromEvent(currEvent), _bindtimeEventType.FieldsByOrdinal.Count + 2);

                      ReleaseEvent(ref currEvent);

                      }

                      }

                      }


                      catch (AdapterException e)

                      {


                      Console.WriteLine(“ConsumeEvents – “ + e.Message + e.StackTrace);

                      }

                      }


                      private
                      void PrepareToStop(PointEvent currEvent, DequeueOperationResult result)

                      {


                      if (DequeueOperationResult.Success == result)

                      {

                      ReleaseEvent(ref currEvent);

                      }

                      }


                      private
                      void PrepareToResume()

                      {

                      }


                      private
                      string[] CreateStringArrayFromEvent(PointEvent currEvent)

                      {


                      if (EventKind.Cti == currEvent.EventKind)

                      {


                      return
                      new
                      string[] {null, “CTI”, currEvent.StartTime.ToString() };

                      }


                      else

                      {


                      List<string> eventDetails = new
                      List<string>();

                      eventDetails.Add(null); // leave the first column for an image

                      eventDetails.Add(“INSERT”);

                      eventDetails.Add(currEvent.StartTime.ToString());


                      for (int fieldCounter = 0; fieldCounter < _bindtimeEventType.FieldsByOrdinal.Count; fieldCounter++)

                      {


                      CepEventTypeField eventFieldType = _bindtimeEventType.FieldsByOrdinal[fieldCounter];


                      object value = Convert.ChangeType(currEvent.GetField(fieldCounter), eventFieldType.Type.ClrType, CultureInfo.CurrentCulture);

                      eventDetails.Add((value != null) ? value.ToString() : “NULL”);

                      }


                      return eventDetails.ToArray();

                      }

                      }

                      }

                      Наиболее интересным для нас методом тут является метод ConsumeEvents(). При помощи метода Dequeue() из очереди извлекается событие (если оно там есть), из которого создается набор строк в методе CreateStringArrayFromEvent() . Полученные строки добавляются в грид на форме.

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

                      Фабрика выходных адаптеров описана в файле GridOutputAdapterFactory.cs:


                      public
                      class
                      GridOutputAdapterFactory : IOutputAdapterFactory<string>

                      {


                      public
                      OutputAdapterBase Create(string StopSignalName,EventShape Shape, CepEventType EventType)

                      {


                      return
                      new
                      GridOutputAdapter(StopSignalName, EventType);

                      }


                      public
                      void Dispose()

                      {

                      }

                      }

                      Тут совсем нет ничего интересного, мы просто возвращаем новый экземпляр выходного адаптера.

                      Основная логика приложения находится в методе обработчика нажатия кнопки Start формы, рассмотрим только этот метод.


                      private
                      void StartButton_Click(object sender, EventArgs buttonArgs)

                      {


                      if (StartButton.Text == “&Start”)

                      {

                      NeedToStop = false;


                      try

                      {

                      _tracer.WriteLine(“Starting Toll Tracker”);

                      _server = Microsoft.ComplexEventProcessing.Server.Create(“si”);

                      _application = _server.CreateApplication(“HighwayMonitor”);


                      var input = CepStream<TollPointEvent>.Create(“TollPointInputStream”,


                      typeof(TollPointInputFactory),

                      _inputConfig,


                      EventShape.Point);


                      //———————————————————————-


                      //- Modify this query – must have a resultant query called queryOutput


                      // 2.1.01 – original query


                      var queryOutput = from e in input


                      select e;


                      //———————————————————————

                      _results = queryOutput.ToQuery(_application, “TollData”, “TollData Query”, typeof(GridOutputAdapterFactory),

                      _stopSignalName,


                      EventShape.Point,


                      StreamEventOrder.FullyOrdered);

                      _adapterStopSignal.Reset();

                      _results.Start();

                      StartButton.Text = “Sto&p”;

                      DisplayDataGridView.Rows.Clear();

                      DisplayDataGridView.Columns.Clear();

                      }


                      catch (Exception e)

                      {

                      _tracer.WriteLine(e.ToString());


                      if (_results != null) _results.Stop();


                      MessageBox.Show(“Unable to start query. Error returned was:\n\r” + e.ToString());

                      }

                      }


                      else

                      {

                      NeedToStop = true;

                      _adapterStopSignal.WaitOne();

                      _results.Stop();

                      _results = null;


                      if (_server != null)

                      {

                      _server.Dispose();

                      _server = null;

                      }

                      StartButton.Text = “&Start”;

                      }

                      }

                      Первое, на что стоит обратить внимание – это создание движка StreamInsight.

                      _server = Microsoft.ComplexEventProcessing.Server.Create(“si”);

                      Параметр метода Create() – это название экземпляра StreamInsight сервера, которые мы задавали при инсталляции (в моем случае – “si”).

                      Далее создается входной поток с использованием фабрики входных адаптеров.


                      var input = CepStream<TollPointEvent>.Create(“TollPointInputStream”,


                      typeof(TollPointInputFactory),

                      _inputConfig,


                      EventShape.Point);

                      queryOutput – это запрос для обработки входного потока, в данном случае просто выбираются все события.


                      var queryOutput = from e in input


                      select e;

                      Выходной поток инициализируется при помощи фабрики выходных адаптеров и запроса.

                      _results = queryOutput.ToQuery(_application, “TollData”, “TollData Query”, typeof(GridOutputAdapterFactory),

                      _stopSignalName,


                      EventShape.Point,


                      StreamEventOrder.FullyOrdered);

                      Обратите внимание на вызов метода ToQuery(), он преобразует LINQ запрос в запрос StreamInsight.

                      Вызов _results.Start(); запускает StreamInsight запрос на выполнение.

                      Если запустить приложение и нажать на кнопку Start, то должна получиться следующая картина:

                      Рекомендую ознакомиться с файлами Demo 2.1 QueryStreamInsightUsingLINQ.txt и Demo 2.2 Using Advanced Query Options.txt, в них содержатся примеры более интересных запросов для данного приложения.

                      Заключение

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

                      С другой стороны, технология новая и некоторые моменты вызывают сомнения, на мой взгляд, наиболее очевидные недостатки технологии на сегодняшний день следующие:

                      • Непонятная ценовая политика. Во время подготовки статьи я не смог найти бесплатной версии StreamInsight без ограничения по времени или возможности купить StreamInsight отдельно от SQL Server 2008 R2. (Хотя, возможно, я плохо искал)
                      • Не понятно, каким образом аналитик должен писать LINQ запросы. Как пока известно, планируется разработка специального языка StreamInsight запросов, понятного аналитику, а не только разработчику.

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

                      Материалы для дальнейшего изучения

                      В рамках данной статьи были рассмотрены лишь основные аспекты работы со StreamInsight, для более глубокого понимания технологии, я рекомендую обратиться к следующим источникам:

                      Techdays.ru – материалы на русском

                      (http://www.techdays.ru/Search.aspx?Tag=SQL%2bServer%2b2008%2bR2)

                      SQL Server 2008 R2 Update for Developers Training Kit – крайне полезные обучающие материалы
                      (http://www.microsoft.com/downloads/details.aspx?familyid=FFFAAD6A-0153-4D41-B289-A3ED1D637C0D&displaylang=en)

                      Документация по StreamInsight (поставляется вместе с StreamInsight)

                      Исходный код

                      Иван Андреев