CLR должен знать каждый!

Одним из наиболее важных новшеств в готовящейся к выпуску версии SQL Server 2005 (ранее — проект Yukon) является возможность исполнения кода на основе среды исполнения Common Language Runtime (CLR). Разработчики программного обеспечения и администраторы баз данных (DBA) наверняка уже слышали о CLR. Но администраторы, которые никак не связаны с разработкой для SQL Server, могли и не обращать внимания на CLR, полагая, что на их работу CLR никак не повлияет. На мой взгляд, это не так: поскольку роль каждого, кто работает с SQL Server, постепенно меняется, необходимо иметь представление об этой новой технологии, и неважно, кем вы сами себя считаете — DBA, разработчиком или программистом.

В SQL Server 2000 и на более ранних платформах баз данных кодирование процедур SQL Server, триггеров, функций и иных объектов производилось на T-SQL. В SQL Server 2005 можно также создавать эти объекты на С# и Visual Basic .NET, и разработчики Microsoft планируют продолжить добавление языков .NET Framework в SQL Server. Более того, специалисты Microsoft написали существенную часть SQL Server 2005 на языках .NET, таких как C++, C# и Visual Basic .NET. В целом благодаря функциональности .NET Framework, SQL Server 2005 позволит на базе CLR выполнять следующие операции.

  • Создавать исполняемый код SQL Server, например хранимые процедуры и триггеры, на С# или Visual Basic .NET и вызывать эти хранимые процедуры точно так же, как хранимые процедуры, написанные на T-SQL.
  • Создавать функции на С# или Visual Basic .NET и вызывать их точно так же, как функции на T-SQL.
  • Создавать пользовательские типы данных (User-Defined Data Type, UDT) и задействовать их в соответствующем программном коде для описания таблиц и бизнес-правил.

В SQL Server 2000 и более ранних версиях SQL можно было обращаться к внешнему коду из расширенной хранимой процедуры (extended stored procedure), которая вызывала предварительно скомпилированную библиотеку DLL, созданную с учетом спецификации Open Data Services (ODS) API. Расширенные процедуры использовались для выполнения сложных математических вычислений, с привлечением функциональности операционной системы, ресурсов процессора или просто для получения функциональности, отсутствовавшей в T-SQL. Проблема при работе с расширенными процедурами состояла в том, что SQL Server должен был полностью «доверять» внешнему коду. Поскольку при создании расширенных хранимых процедур использовался неуправляемый код, а процедуры работали в адресном пространстве SQL Server, проблемы, возникающие в расширенных процедурах, могли нарушить работу SQL Server — фактически дело могло дойти до «голубого экрана смерти». Исполняемый код, созданный на базе CLR, означает, что разработчики SQL Server и DBA получают возможность безопасного наращивания возможностей своих процедур.

Чтобы оценить возможности выполнения CLR-кода, давайте рассмотрим процесс создания и развертывания проекта CLR, разработанного для выполнения общей задачи: захват и шифрование данных кредитной карты. Я использовал Visual Studio 2005 (ранее — проект Whidbey) и SQL Server 2005, бета-версии которых Microsoft представила на конференции разработчиков Professional Developers Conference, PDC, в октябре 2003 года, хотя все приведенные мною примеры можно создать с помощью текущей версии Visual Studio .NET. Бета-версии Visual Studio 2005 еще не интегрированы с SQL Server 2005, но Microsoft планирует летом этого года интегрировать оба продукта. Необходимо создать код и развернуть библиотеки DLL на SQL Server 2005 с помощью сценариев T-SQL, как описано в SQL Server Books Online (BOL). Одно предостережение: нужно иметь в виду, что я использовал альфа-код. Разработчики Microsoft продолжают совершенствовать детали и интерфейсы финального продукта, поэтому производительность текущей версии не стоит оценивать строго.

Знакомство с CLR

Архитектура .NET Framework (см. рис. 1) основана на нейтральной по отношению к используемому языку архитектуре CLR, которая позволяет разработчикам использовать для создания .NET-приложений фактически любой язык программирования. Язык стоит рассматривать просто как интерфейс между человеком и компьютером — языков так же много, как типов программ и программистов. На данный момент Microsoft предполагает реализовать в SQL Server 2005 поддержку трех языков программирования: T-SQL, C# .NET и Visual Basic .NET. Хранимые процедуры, использовавшиеся в SQL Server 2000 и еще более ранних версиях SQL, смогут работать и в SQL Server 2005, к тому же вы сможете создавать новые процедуры, функции и UDT на любом из трех поддерживаемых языков. В отличие от T-SQL, .NET-языки позволяют задействовать любой из более чем 5000 поддерживаемых классов .NET Framework.

Рисунок 1. Архитектура Microsoft .NET Framework

Рис. 2 иллюстрирует принцип работы CLR. Visual Studio запускает CLR-компилятор, который получает блок кода, называемый сборкой (assembly), и генерирует динамическую библиотеку, DLL. В ней содержится код в форме промежуточного языка (intermediate language, IL). CLR конвертирует этот код в машинный код и выполняет его.

Рисунок 2. Как работает CLR

Не так давно я присутствовал на встрече группы региональных директоров Microsoft (Microsoft Regional Directors) и редакторов журналов, где обсуждались новые технологии компании. Члены группы согласились с тем, что CLR — технология захватывающая, однако необходимо, чтобы пользователи более четко представляли себе ее преимущества. Многие выражали опасения, что разработчики могут просто посчитать, что основанные на CLR хранимые процедуры всего лишь работают быстрее и лучше, чем процедуры, написанные на традиционном T-SQL. Иногда приходится сталкиваться с упрощенным взглядом на жизнеспособность CLR-кодирования. Например, я читал статью, в которой говорилось, что «хранимые .NET-процедуры ничем не отличаются от использования привычных хранимых процедур SQL». Подобное восхваление достоинств CLR-программирования опасно. Я беспокоюсь, что в таком случае разработчики начнут рассматривать CLR как средство создать что-то вроде перевода шкалы Фаренгейта в градусы Цельсия, и, когда выяснится, что новый код работает медленнее, чем T-SQL, они откажутся от CLR. Давайте познакомимся с новой технологией поближе и попытаемся понять, что же в действительности она собой представляет.

Будем считать, что вызов DLL из T-SQL подобен вызову внешнего (по отношению к основному процессу) COM-объекта. Проверенный принцип программирования гласит: пореже обращайтесь к внешним процедурам, передавайте им большее число параметров и стремитесь к тому, чтобы на долю продуктивного времени в процедурах отводилось максимально возможное время. Говоря о продуктивном времени работы приложения во внешнем коде, я подразумеваю время продуктивного и эффективного использования циклов процессора.

Когда внешняя библиотека, CLR DLL, вызывается в первый раз, SQL Server должен загрузить код в память, скомпилировать исполняемый код и затем выполнить его. После того как сервер SQL выполнит эти действия, вызов внешнего объекта потребует меньших затрат по сравнению с первоначальным вызовом, но все же окажется более «дорогим», нежели вызов внутрипроцессного кода. Результат? Вызов CLR-объекта может оказаться более медленным, чем вызов хранимой процедуры T-SQL.

На уже упоминавшейся встрече директоров и редакторов рассматривались некоторые ситуации, когда сборки CLR для SQL Server могут оказаться жизнеспособными альтернативами процедурам T-SQL. Разработчики SQL Server уже реализовали решения фактически для всех подобных случаев, используя расширенные хранимые процедуры и библиотеки COM DLL. Однако в каждом из перечисленных ниже случаев было бы целесообразно переписать существующие решения на CLR-код.

  • Замена или наращивание функциональности сложных хранимых процедур T-SQL, особенно тех из них, в которых выполняются математические вычисления с использованием, например, сложных бизнес- или научных формул. Математические возможности T-SQL не намного лучше моих собственных, поэтому перенос сложной математики в CLR DLL может оказаться целесообразным.
  • Обращение к файлу Microsoft Project для обновления сложных задач, связанных с планированием. Логика CLR-процедур позволяет приложениям взаимодействовать со структурами данных Project, к которым T-SQL не может обратиться напрямую. Аналогичный подход может применяться для любых внешних структур данных, которые не имеют традиционного интерфейса доступа к данным через процессы или файлы.
  • Реализация сложных алгоритмов картографии. Используя координаты Geographic Positioning Satellite (GPS), один из региональных директоров составил запрос: "Показать все аэропорты в пяти милях от города". С помощью CLR-процедуры, работающей с геометрическими функциями, решить поставленную задачу оказалось гораздо проще, чем если бы пришлось писать сложный запрос в базу данных картографии.
  • Выполнение сложных инженерных вычислений. К примеру, моя дочь купила справочник толщиной 15 см с формулами химического машиностроения. С помощью CLR я мог бы закодировать эти формулы и с легкостью обращаться к ним из T-SQL как к функциям. Но напоминаю, что следует быть внимательным при оценке времени, необходимого для вычисления формул при стандартном подходе и при переходе на CLR.

В каждом из этих примеров CLR-код расширяет возможности и сферу применения языка запросов T-SQL. Именно для этого и создаются процедуры, основанные на использовании CLR.

Тестирование

Для тестирования эффективности хранимых процедур на CLR я написал простую .NET-сборку на Visual Basic .NET для перевода шкалы Фаренгейта в шкалу Цельсия и обратно. Тестирование проводилось двумя способами. Сначала я вызвал сборку из хранимой процедуры на T-SQL и обнаружил, что времени на ее выполнение уходит примерно в 8 раз больше, чем при традиционном подходе (использовании эквивалентного преобразования на T-SQL).

Тогда я изменил .NET-сборку и закодировал ее таким образом, чтобы тестовое приложение вызывалось как CLR-функция T-SQL. Производительность оказалась лучше, чем в первом случае, но все еще примерно в пять раз медленнее эквивалентного T-SQL-кода. Однако, поскольку это был всего лишь несложный тест, я не особенно удивился. В данном случае T-SQL не потребовал серьезных математических вычислений — речь шла о простой арифметике.

Конечно, элементарные преобразования — это не то, ради чего создаются хранимые процедуры на CLR; они предназначены для сложных и ресурсоемких вычислений либо для тех случаев, когда решить задачу средствами традиционного T-SQL невозможно. Хранимые процедуры на CLR задумывались как замена расширенных хранимых процедур, а не как их расширение.

После консультаций с Петером Блекберном (Peter Blackburn), моим техническим гуру из Великобритании, я решил попробовать немного усложнить тест — сделать его более реалистичным. На этот раз речь шла о реализации функции RSA-шифрования с привлечением провайдера RSACryptoServiceProvider, на вход которому подается строка и возвращается зашифрованный байтовый массив. Почти три недели спустя новое приложение было готово к тестированию. На рис. 3 представлена схема его работы. В ходе разработки я обнаружил несколько проблем, из-за которых мне пришлось создать большую часть проекта в пакетах T-SQL. Кроме того, обнаружилась новая возможность отладки (ее описание приведено во врезке «Отладка процедур T-SQL и SQL CLR»). Приобретенный опыт позволил поближе познакомиться с новым инструментарием SQL Server 2005 — SQL Server Management Studio (ранее — проект Workbench), который заменил Enterprise Manager, Query Analyzer и еще несколько утилит. Microsoft в настоящее время интегрирует инструментарий Management Studio в Visual Studio 2005, чтобы упростить обслуживание баз данных SQL Server.

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

  • Я начал проектирование на бумаге (для некоторых это слишком экстремальный подход). Это помогло мне оставаться в контексте работы, если кто-нибудь меня прерывал.
  • Затем я написал CLR-код шифрования в Windows-приложении и проверил его с помощью Visual Studio .NET 2003 и .NET Framework 1.1. Я вернулся к более ранней версии .NET Framework из-за проблем в версии 2.0, не позволяющих конвертировать строки в байтовые массивы и обратно.
  • Потом я загрузил Visual Studio 2005 с PDC и импортировал приложение, которое использовало .NET Framework 1.1, а затем модифицировал код, отвечающий за передачу байтового массива (тип данных - varbinary) в программы преобразования, а также из них. Этот метод оказался более эффективным, чем преобразование байтовых массивов в строки Unicode перед передачей в качестве параметра.
  • Были написаны сценарии для создания хранимых процедур на T-SQL, которые вызывали CLR-функции шифрования и использовали CLR DLL сборки (все сценарии можно загрузить на www.winnetmag.ru). Этот пример позволил мне развертывать сборки непосредственно из Visual Studio 2005, но при обращении к классам шифрования происходило нарушение защиты (protect violation) памяти. Я вынужден был явно прописать для сборки атрибут UNSAFE, чтобы заставить ее работать. Далее, когда я попытался использовать функцию развертывания, Visual Studio 2005 Deploy, Visual Studio сбросил все хранимые процедуры T-SQL, работа которых зависела от обращений к сборкам CLR. Я надеюсь, что в последующих сборках Visual Studio Microsoft решит эти проблемы.
  • Затем я написал сценарий для создания тестовой таблицы CCards и установил для нее разрешения на доступ.
  • Далее я создал сценарии для хранимых процедур T-SQL для добавления строк в тестовую таблицу. Сценарии содержали код для установки разрешений на выполнение процедур.
  • И наконец, я написал приложение VB для получения параметров, передаваемых пользователем, и обращения к процедурам T-SQL. Процедуры T-SQL вызывали CLR-код для шифрования значения и сохранения значения в виде данных типа varbinary в тестовой таблице. В приложение также был включен код для выборки строк из тестовой таблицы и попытки дешифрации значения при помощи выбранного секретного ключа.
Экран 1. Демоприложение Encrypt Decrypt

Как показано на экране 1, программа получает номер и срок действия кредитной карты пользователя. Когда пользователь нажимает кнопку Submit, из основного кода происходит вызов хранимой процедуры, которая в свою очередь вызывает сборку CLR. Последняя генерирует секретный ключ шифрования RSA и использует этот ключ для шифрования номера кредитной карты пользователя. Хранимая процедура записывает срок действия кредитной карты и шифрованные данные в базу данных (на этот раз в виде байтового массива). Основной код сохраняет секретный ключ шифрования (private encryption key, PEK) и отображает его в клиентском приложении для облегчения процедуры отладки. Однако PEK на сервере не хранится; таким образом, только сам пользователь может дешифровать закодированное значение на сервере, и только в том случае, если клиентское приложение хранит это значение на локальном диске. Я не могу сравнивать приведенный пример программ шифрования/дешифрации с эквивалентным кодом на T-SQL, такая функциональность в T-SQL просто не поддерживается, но этот пример демонстрирует механику создания CLR-кода, и описанный неформальный тест показал, что он прекрасно работает. Однако я бы посоветовал поэкспериментировать со своим CLR-кодом в условиях, приближенных к конкретной бизнес-среде.

Когда пользователю нужно извлечь номер своей кредитной карты, чтобы серверная функция смогла использовать его, приложение на стороне клиента вызывает соответствующую хранимую процедуру. Процедура извлекает ключ шифрования, находит запись клиента по его ID и сроку действия и дешифрует значение при помощи другой процедуры на CLR. Для облегчения отладки приложения код возвращает расшифрованный номер карты в клиентское приложение (в «боевой» программе серверное приложение может, например, просто разблокировать полученное значение для выполнения дальнейшей обработки).

Нужно иметь в виду, что приложение вызывает хранимые процедуры на CLR из процедур T-SQL. Я нахожу такой подход более удобным, чем непосредственный вызов CLR-процедур, и полагаю, что большинство разработчиков придут к такому же выводу. Другой подход состоит в кодировании CLR-процедур в виде функций и встраивании их в код T-SQL.

Итак, мы рассмотрели некоторые концепции хранимых CLR-процедур. В статье было показано, как работают такие процедуры и где они в большей мере соответствуют высокопроизводительным системам управления базами данных (DBMS). В дальнейшем я собираюсь рассказать о том, как применять Visual Studio 2005 и SQL Server 2005 для кодирования и тестирования хранимых CLR-процедур, используемых в тестовом приложении, которое было представлено выше.

Вильям Ваун (http://www.betav.com) — президент компании Bata V, имеет звание Microsoft MVP. В течение 14 лет работал в Microsoft

Отладка процедур T-SQL и SQL CLR

Тестирование кода Common Language Runtime (CLR) в моем приложении было делом нелегким, пока я не обнаружил новую замечательную возможность, связанную с включением межпроцессной отладки. В новой версии Visual Studio 2005 в меню Debug появилось окно Attach to Process, которое заменяет меню Debug Process в версии Visual Studio .NET 2003. Диалоговое окно Attach to Process связывает Visual Studio 2005 IDE с SQL Server, на котором запущена хранимая процедура. В моем примере нужно было установить связь с одним из двух серверов SQL — в системе одновременно работали SQL Server 2000 Developer Edition и SQL Server 2005. В окне Attach to Process перечисляются все запущенные процессы, нужно просмотреть список и отыскать процесс sqlservr.exe с типом SQL, .NET, Win32. Процесс SQL Server 2000 тип .NET не включает. Выбрав нужный процесс, следует щелкнуть Attach для переключения среды разработки (Integrated Development Environment — IDE) в режим Run. Теперь, если установить контрольную точку и запустить хранимую CLR-процедуру из SQL Server Management Studio IDE, Visual Studio 2005 остановится в указанном месте — предположим, что логика CLR этому не противоречит. Будьте внимательны: если долго «висеть» в режиме прерывания, сервер может прервать ожидание по тайм-ауту для завершения работы команды.