Структура современной СУБД на примере Microsoft SQL Server 2008
Цель лекции: показать основные элементы структуры современной СУБД (архитектуры базы данных и структуры программного обеспечения) на примере Microsoft SQL Server 2008 .
10.1 Общая структура СУБД
Для лучшего понимания принципов работы современных СУБД рассмотрим структуру одной из наиболее распространенных клиент-серверных СУБД — Microsoft SQL Server 2008 . Несмотря на то, что каждая коммерческая СУБД имеет свои отличительные особенности, информации о том, как устроена какая-то из СУБД , обычно бывает достаточно для быстрого первоначального освоения другой СУБД . Краткий обзор возможностей Microsoft SQL Server — 2008 был приведен в разделе, посвященном краткому обзору современных СУБД . В данном разделе рассмотрим основные моменты, связанные со структурой соответствующей СУБД (архитектурой базы данных и структурой программного обеспечения).
Под архитектурой (структурой) базы данных конкретной СУБД будем понимать основные модели представления данных, используемые в соответствующей СУБД а также взаимосвязи между этими моделями.
В соответствии с рассмотренными в «Различные архитектурные решения, используемые при реализации многопользовательских СУБД. Краткий обзор СУБД» различными уровнями описания данных различают разные уровни абстракции архитектуры базы данных .
Логический уровень (уровень модели данных СУБД) — средство представления концептуальной модели. Здесь каждая СУБД имеет некоторые отличия, но они являются не очень значительными. Отметим, что у разных СУБД существенно отличаются механизмы перехода от логического к физическому уровню представления.
Физический уровень (внутреннее представление данных в памяти ЭВМ — физическая структура базы данных). Данный уровень рассмотрения подразумевает изучение базы данных на уровне файлов, хранящихся на жестком диске. Структура этих файлов – особенность каждой конкретной СУБД , в т.ч. и Microsoft SQL Server .
10.2. Архитектура базы данных. Логический уровень
Рассмотрим логический уровень представления базы данных (http://msdn.microsoft.com). Microsoft SQL Server 2008 представляет собой реляционную СУБД (данные представляются в виде таблиц). Таким образом, основной структурой модели данных этой СУБД являются таблицы.
Таблицы и типы данных
Таблицы содержат данные о всех сущностях концептуальной модели базы данных. При описании каждого столбца (поля) пользователь должен определить тип соответствующих данных. Microsoft SQL Server 2008 поддерживает как уже ставшие традиционными типы данных (символьная строка с разным представлением, число с плавающей точкой длиной 8 или 4 байта, целое число длины 2 или 4 байта, дата и время, поле примечаний, булево значение и т. д.), так и новые типы данных. Кроме этого Microsoft SQL Server 2008 предоставляет специальный аппарат для создания пользовательских типов данных .
Рассмотрим краткую характеристику некоторых новых типов данных, значительно расширяющих возможности пользователя (http://www.oszone.net).
Тип данных hierarchy >Тип данных hierarchyid позволяет создавать отношения между элементами данных в таблице, для того, чтобы задать позицию в иерархии связей между строками таблицы. В результате использования этого типа данных в таблице строки таблицы могут отображать определенную иерархическую структуру, соответствующую связям между данными этой таблицы.
Пространственные типы данных
Пространственные данные – это данные, определяющие географические расположения и формы, преимущественно на Земле. Это могут быть ориентиры, дороги и даже расположение фирмы. В SQL Server 2008 есть географические (geography) и геометрические ( geometry ) типы данных для работы с этой информацией. Тип данных geography работает с информацией для шарообразной земли. Модель шарообразной земли использует при расчетах кривизну земной поверхности. Информация о положении задается широтой и долготой. Эта модель хорошо годится для приложений, связанных с морскими перевозками, военным планированием и краткосрочными приложениями, имеющими привязку к земной поверхности. Эту модель нужно использовать, если данные хранятся в виде широт и долгот.
Тип данных geometry работает с планарной моделью или моделью плоской земли. В этой модели земля считается плоской проекцией из определенной точки. Модель плоской земли не принимает в расчет кривизну поверхности земли, поэтому используется, в первую очередь, для описания коротких расстояний, например, в базе данных приложения, описывающего внутреннюю часть строения.
Типы geography и geometry создаются из векторных объектов, заданных в форматах Well-Known Text (WKT) или Well-Known Binary (WKB). Это форматы для перенесения пространственных данных, описанные в простых функциях открытого геопространственного консорциума (Open Geospatial Consortium ( OGC ) Simple Features) для спецификаций SQL (SQL Specification).
Ключи
Для каждой таблицы должен быть определен первичный ключ – минимальный набор атрибутов, уникально идентифицирующий каждую запись в таблице. Для реализации связи между таблицами в одну из связанных таблиц включается дополнительное поле (несколько полей) – первичный ключ другой таблицы. Дополнительно включенные поле или поля в этом случае называются внешним ключом соответствующей таблицы.
Кроме таблиц, в модель данных Microsoft SQL Server 2008 входит еще целый ряд компонентов. Дадим краткую характеристику основным из них.
Индексы
В «Использование формального аппарата для оптимизации схем отношений» рассматривалось понятие индекса. Здесь понятие индекса вынесено на логический уровень для удобства пользователя. Индексы создаются для ускорения поиска нужной информации и содержат информацию об упорядоченности данных по различным критериям. Индексирование может быть выполнено по одному или нескольким столбцам. Индексирование может быть произведено в любой момент. Индекс содержит ключи, построенные из одного или нескольких столбцов в таблице или представлении. Эти ключи хранятся в виде структуры сбалансированного дерева , которая поддерживает быстрый поиск строк по их ключевым значениям в SQL Server.
Представления
Представление — это виртуальная таблица, содержимое которой определяется запросом. Представление формируется на основе SQL-запроса SELECT, формируемого по обычным правилам. Таким образом, представление есть поименованный запрос SELECT.
Как и настоящая таблица, представление состоит из совокупности именованных столбцов и строк данных. Пока представление не будет проиндексировано, оно не существует в базе данных как хранимая совокупность значений. Строки и столбцы данных извлекаются из таблиц, указанных в определяющем представление запросе и динамически создаваемых при обращениях к представлению. Представление выполняет функцию фильтра базовых таблиц , на которые оно ссылается. Определяющий представление запрос может быть инициирован в одной или нескольких таблицах или в других представлениях текущей или других баз данных. Кроме того, для определения представлений с данными из нескольких разнородных источников можно использовать распределенные запросы. Это полезно, например, если нужно объединить структурированные подобным образом данные, относящиеся к разным серверам, каждый из которых хранит данные конкретного отдела организации.
Сборки
Сборки являются файлами динамической библиотеки, которые используются в экземпляре SQL Server для развертывания функций, хранимых процедур, триггеров, определяемых пользователем статистических вычислений и определяемых пользователем типов.
Ограничения
Ограничения позволяют задать метод, с помощью которого компонент СУБД Database Engine автоматически обеспечивает целостность базы данных. Ограничения задают правила допустимости определенных значений в столбцах и представляют собой стандартный механизм обеспечения целостности. Рекомендуется использовать ограничения, а не триггеры , правила и значения по умолчанию. Оптимизатор запросов также использует определения ограничений для построения высокопроизводительных планов выполнения запросов.
Правила
Правила – еще один специальный механизм, предназначенный для обеспечения целостности базы данных, по функциональности напоминающие некоторые типы ограничений. Microsoft отмечает, что при соответствующей возможности использование ограничений по ряду причин предпочтительнее и, возможно, в будущей версии эта возможность будет удалена.
Значения по умолчанию
Значения по умолчанию определяют, какими значениями заполнять столбец, если при вставке строки для этого столбца значение не указано. Значение по умолчанию могут быть любым выражением, результат которого — константа, например собственно константой, встроенной функцией или математическим выражением.
Различия в архитектуре Oracle и MS SQL Server
Мне приходилось сотрудничать со многими клиентами, использующими крупные производственные приложения, которые были “перенесены” в Oracle из другой платформы баз данных (например, MS SQL Server). Слово “перенесено” взято в кавычки потому, что большинство встречаемых мною адаптаций сводились к точке зрения “найти минимальные изменения, которые обеспечили бы успешную компиляцию и выполнение кода MS SQL Server на платформе Oracle”. Откровенно говоря, приложения, построенные в результате такого подхода к делу, попадались мне чаще всего, поскольку именно они требовали наибольшей помощи. Я вовсе не критикую SQL Server в этом отношении — ведь справедливо и обратное! Перенос приложения Oracle и его помещение с минимальными изменениями в среду SQL Server приведет к получению столь же плохо работающего кода, как и наоборот; проблема имеет обоюдный характер.
Однако в одном конкретном случае архитектура SQL Server и способ применения SQL Server действительно были навеяны реализацией Oracle. В качестве конечной цели ставилось масштабирование, но обратившиеся ко мне разработчики на самом деле не хотели переходить на другую базу данных. Они хотели провести перенос с минимальными усилиями со своей стороны, и потому оставили архитектуру в основном прежней — на уровне клиента и базы данных. Это решение имело два важных последствия.
- Архитектура подключений в Oracle осталась той же самой, что и использованная в SQL Server.
- Разработчики применяли литеральный (неограниченный) SQL-код.
Эти два обстоятельства повлекли за собой построение системы, которая не могла поддерживать необходимую пользовательскую нагрузку (серверу базы данных просто не хватало доступной памяти) и обладала крайне низкой производительностью.
Используйте единственное подключение в Oracle
В среде SQL Server открытие подключения к базе данных для каждого параллельно выполняющегося оператора является весьма распространенной практикой. При выполнении пяти запросов в среде SQL Server вполне можно встретить пять подключений. С другой стороны, в Oracle вне зависимости от того, сколько запросов нужно выполнить, пять или даже пятьсот, максимальное количество открываемых подключений равно единице. В результате то, что принято в SQL Server, совершенно не рекомендуется применять в среде Oracle; наличие множества подключений к базе данных просто нежелательно.
Однако, как раз это и было сделано. Простое веб-приложение для каждой веб-страницы может открывать 5, 10, 15 и более подключений, а это значит, что сервер мог поддерживать только 1/5, 1/10, 1/15 и менее параллельно работающих пользователей от того числа, которое должен. Кроме того, была предпринята попытка использования базы данных на обычной платформе Windows — в среде простого сервера Windows без доступа к Datacenter-вepcии Windows Server. В результате архитектура Windows с единственным процессом ограничила общий объем оперативной памяти, доступной серверу баз данных Oracle, до приблизительно 1,75 Гбайт. Поскольку каждое подключение Oracle занимает, как минимум, определенный фиксированный объем памяти, возможности масштабирования количества пользователей, работающих с приложением, были существенно ограничены. Объем оперативной памяти сервера составлял 8 Гбайт, но из них можно было использовать только около 2 Гбайт.
Важно! В среде 32-разрядной ОС Windows доступны способы использования большего объема оперативной памяти, такие как ключ /AWE, но для этого требуются версии ОС, которые в описанной ситуации не применялись.
Существовало три подхода к решению этой проблемы, причем все три были достаточно трудоемкими — и это после завершения “переноса”!
Были доступны следующие варианты.
- Изменить архитектуру приложения, чтобы оно могло получить преимущества выполнения в среде Oracle, и во время генерирования страницы применять одно подключение, а не от 5 до 15. Это единственное решение, которое действительно устранило бы проблему.
- Провести модернизацию ОС (отнюдь не простая задача) и задействовать модель с большим объемом доступной памяти, предлагаемую версией Windows Server Datacenter (что само по себе совсем не просто, т.к. сопряжено со сложной настройкой базы данных, с определением косвенных буферов данных и других нестандартных параметров).
- Перенести базу данных из Windows в среду какой-то другой ОС, которая поддерживает множество процессов. Это фактически позволило бы базе данных задействовать всю установленную оперативную память. На 32-разрядной платформе Windows вы ограничены примерно 2 Гбайт памяти комбинированных областей PGNSGA (2 Гбайт для обеих вместе), поскольку они выделяются единственным процессом. При использовании платформы с множеством процессов, которая также является 32-разрядной, вы будете ограничены примерно 2 Гбайт для SGA и 2 Гбайт на процесс для PGA, что существенно больше, чем обеспечивает 32-разрядная платформа Windows.
Как видите, ни одна из этих возможностей не относилась к решениям, о которых можно было бы сказать: “Хорошо, мы сделаем это до обеда”. Каждая из них представляла собой сложное решение проблемы, которую проще всего было решить на этапе переноса базы данных, пока вносились изменения в код и в наиболее важные элементы системы. Более того, простое тестирование масштабирования, проведенное до развертывания системы, позволило бы выявить проблемы подобного рода еще до того, как конечные пользователи начали бы испытывать какие-то неудобства.
Используйте переменные привязки
Если бы я писал книгу о том, как строить немасштабируемые приложения Oracle, то первая и последняя глава называлась бы “Не используйте переменные привязки”. Отказ от применения переменных привязки является основной причиной возникновения проблем с производительностью и главным ограничителем возможностей масштабирования, не говоря уже об огромной степени риска в плане безопасности. Способ работы Oracle с разделяемым пулом (очень важная структура данных в разделяемой памяти) в большинстве случаев определяется разработчиками, использующими переменные привязки. Если вы хотите заставить транзакционную реализацию Oracle работать медленно, вплоть до полной ее остановки, просто откажитесь от применения переменных привязки.
Переменная привязки — это метка-заполнитель в запросе. Например, извлечения записи сотрудника 123 можно выполнить следующий запрос:
В качестве альтернативы можно запустить такой запрос:
В типичной системе запрос информации о сотруднике 12 3 вполне может быть выполнен один или два раза и больше никогда на протяжении длительного периода времени. Позже может требоваться информация о сотруднике 456, затем — о сотруднике 7 8 9 и т.д. Или, как в предшествующих операторах SELECT, если вы не указываете в своих операторах вставки переменные привязки, то значения первичного ключа будут жестко закодированы в них, и мне известен тот факт, что такие операторы вставки никогда не смогут использоваться повторно! Если в запросе применяются литералы (константы), то каждый запрос оказывается совершенно новым, никогда ранее не выполнявшимся в базе данных. Он должен быть синтаксически разобран, определен (произведено распознавание имен), проверен на соблюдение правил безопасности, оптимизирован и т.п.
Короче говоря, каждый запускаемый уникальный оператор будет требовать компиляции при каждом своем выполнении .
Во втором запросе использовалась переменная привязки :empno, значение которой передается во время выполнения запроса. Этот запрос компилируется только один раз, а затем план запроса сохраняется в разделяемом пуле (библиотечном кеше), из которого он может быть извлечен и применен повторно. Разница между этим двумя методами с точки зрения производительности и масштабируемости не просто велика — она огромна.
Из предыдущего объяснения должно быть совершенно понятно, что синтаксический разбор оператора с жестко закодированными переменными (называемый полным разбором) будет проходить дольше и потреблять намного больше ресурсов, чем повторное использование уже разобранного плана запроса (которое называется частичным разбором). Однако степень снижения количества пользователей, которых может поддерживать система, при первом методе может оказаться не настолько ясной. Очевидно, что отчасти это объясняется увеличением потребления системных ресурсов, но более значительный фактор связан с влиянием механизмов защелок, применяемых к библиотечному кешу.
При выполнении полного разбора запроса база данных будет дольше хранить определенные низкоуровневые устройства последовательной обработки, называемые защелками (или внутренними блокировками). Эти защелки защищают структуры данных в разделяемой памяти Oracle от одновременных изменений двумя сеансами (в противном случае структуры данных были бы повреждены) и от считывания структуры данных кем-либо во время ее изменения. Чем дольше и чаще приходится “защелкивать” эти структуры данных, тем более длинной будет становиться очередь для получения таких защелок. Это приведет к монополизации ограниченных ресурсов. Временами компьютер может выглядеть недогруженным, тем не менее, все действия в базе данных будут выполняться очень медленно. Внешне все выглядит так, будто кто- то владеет одним из механизмов последовательной обработки, создавая очередь — достичь максимальной производительности не удастся. Достаточно наличия в базе данных одного неправильно ведущего себя приложения, чтобы производительность всех приложений значительно снизилась. Единственное небольшое приложение без переменных привязки со временем приведет к удалению из разделяемого пула всех SQL-запросов, принадлежащих остальным хорошо настроенным приложениям. Одной ложки дегтя хватит, чтобы испортить бочку меда.
Важно! Чтобы увидеть отличие между полным и частичным разбором в действии, рекомендуется пересмотреть демонстрационный видеоролик, доступный по ссылке http://tinyurl. corn/RWP-OLTP-PARSiNG. Он был смонтирован командой, с которой я работал — командой Real World Performance ( Производительность в реальном мире) из Oracle. В нем наглядно показана разница между полным и частичным разбором — она близка к отличию на порядок! В транзакционной системе, архитектура которой ориентирована на использование переменных привязки, можно добиться десятикратного увеличения скорости выполнения в случае их применения. Вы можете использовать эту короткую визуальную презентацию, чтобы убедить других разработчиков о высоком влиянии переменных привязки (либо их отсутствия) на производительность!
Единственное отличие между этими двумя процедурами состоит в том, что в одной применяется переменная привязки, а в другой — нет. Обе процедуры используют динамический SQL-код с идентичной логикой. Разница заключается только в применении переменной привязки в первой процедуре.
Фактически если вы не используете переменные привязки и применяете прием с конкатенацией строк, то код становится уязвимым для атак внедрением SQL и потому должен быть тщательно пересмотрен. Причем это должен сделать кто-то другой, а не разработчик, написавший этот код, потому что код должен быть пересмотрен критически и объективно. Если проверяющий является партнером автора кода или хуже того — другом или подчиненным, то проверка будет не столь критической, какой должна быть. К коду, разработанному без использования переменных привязки, следует относиться с подозрением — он должен быть скорее исключением, нежели нормой.
Архитектура системы безопасности MS SQL Server
Дата добавления: 2013-12-23 ; просмотров: 2773 ; Нарушение авторских прав
После проектирования логической структуры базы данных, связей между таблицами, ограничений целостности и других структур необходимо определить круг пользователей, которые будут иметь доступ к базе данных.
Чтобы разрешить этим пользователям обращаться к серверу, создайте для них учетные записи в SQL Server либо предоставьте им доступ посредством учетных записей в домене, если вы используете систему безопасности Windows. Разрешение доступа к серверу не дает автоматически доступа к базе данных и ее объектам.
Второй этап планирования системы безопасности заключается в определении действий, которые может выполнять в базе данных конкретный пользователь.
Полный доступ к базе данных и всем ее объектам имеет администратор, который является своего рода хозяином базы данных — ему позволено все.
Второй человек после администратора — это владелец объекта. При создании любого объекта в базе данных ему назначается владелец, который может устанавливать права доступа к этому объекту, модифицировать его и удалять.
Третья категория пользователей имеет права доступа, выданные им администратором или владельцем объекта.
Учетные записи и пользователи
Система безопасности SQL Server 2000 и выше базируется на учетных записях (имена входа) и пользователях.
Рис. 1. Учетные записи (мена входа) определяются на уровне сервера
Рис. 2. Пользователи определяются для БД
Пользователи проходят следующие два этапа проверки системой безопасности. На первом этапе пользователь идентифицируется по имени учетной записи и паролю, то есть проходит аутентификацию.
Если данные введены правильно, пользователь подключается к SQL Server. Подключение к SQL Server, или регистрация, не дает автоматического доступа к базам данных. Для каждой базы данных сервера регистрационное имя (или учетная запись — login) должно отображаться в имя пользователя базы данных (user).
На втором этапе, на основе прав, выданных пользователю как пользователю базы данных (user), его регистрационное имя (login) получает доступ к соответствующей базе данных. В разных базах данных login одного и того же пользователя может иметь одинаковые или разные имена user с разными правами доступа.
Для доступа приложений к базам данных им также понадобятся права. Чаще всего приложениям выдаются те же права, которые предоставлены пользователям, запускающим эти приложения. Однако для работы некоторых приложений необходимо иметь фиксированный набор прав доступа, не зависящих от прав доступа пользователя. SQL Server 2000 позволяет предоставить такие с применением специальных ролей приложения.
Рис. 3. Роли приложений
Итак, на уровне сервера система безопасности оперирует следующими понятиями:
— учетная запись (login);
— встроенные роли сервера (fixed server roles).
На уровне базы данных используются следующие понятия:
— пользователь базы данных (database user);
— фиксированная роль базы данных (fixed database role);
— пользовательская роль базы данных (users database role);
— роль приложения (application role).
SQL Server 2000 может использовать два режима аутентификации пользователей:
— режим аутентификации средствами Windows;
— смешанный режим аутентификации (Windows Authentication and SQL Server Authentication).
Смешанный режим позволяет пользователям регистрироваться как средствами Windows, так и средствами SQL Server. Кроме того, этот режим предлагает некоторые удобства по сравнению с первым. В частности, при аутентификации только средствами домена Windows, если пользователь не имеет учетной записи в домене Windows, то он не сможет получить доступа к серверу баз данных. Смешанный режим аутентификации позволяет избежать этой проблемы.
При выборе режима аутентификации следует исходить как из требований обеспечения наибольшей безопасности, так и из соображений простоты администрирования. Если ваша организация небольшая и должности администратора сети и администратора баз данных совмещает один человек, то удобнее использовать аутентификацию Windows. Если же в организации сотни пользователей и функции системного администратора и администратора баз данных выполняют различные люди, то может оказаться, что аутентификация средствами SQL Server удобнее. В противном случае человеку, занимающемуся администрированием сервера баз данных, придется постоянно обращаться к системному администратору для создания нового пользователя, смены пароля или для перевода пользователя из одной группы в другую. К тому же системный администратор будет иметь возможность назначать права доступа по своему усмотрению, а это совсем ни к чему.
С другой стороны, каждый пользователь организации, скорее всего, имеет в домене учетную запись, администрированием которой занимается системный администратор. Благодаря аутентификации Windows администратор баз данных может использовать уже готовые учетные записи, а не отвлекаться на создание новых.
Обратите внимание, что речь идет только о праве подключения пользователя к серверу баз данных. После регистрации пользователя в SQL Server способ проверки прав доступа к конкретной базе данных одинаков для обоих режимов аутентификации.
Рис. 4. Выбор режима аутентификации
После того как клиент успешно прошел аутентификацию, он получает доступ к SQL Server. Для получения доступа к любой базе данных учетная запись пользователя (login) отображается в пользователя данной базы данных (user). Объект «пользователь базы данных» применяется для предоставления доступа ко всем объектам базы данных: таблицам, представлениям, хранимым процедурам и т.д. В пользователя базы данных может отображаться:
— учетная запись Windows;
— учетная запись SQL Server.
Подобное отображение учетной записи необходимо для каждой базы данных, доступ к которой хочет получить пользователь. Отображения сохраняются в системной таблице sysusers, которая имеется в любой базе данных. Такой подход обеспечивает высокую степень безопасности, предохраняя от предоставления пользователям, получившим доступ к SQL Server, автоматического доступа ко всем базам данных и их объектам.
Пользователи баз данных, в свою очередь, могут объединяться в группы и роли для упрощения управлением системой безопасности.
В ситуации, когда учетная запись не отображается в пользователя базы данных, клиент все же может получить доступ к базе данных под гостевым именем guest, если оно, разумеется, имеется в базе данных. Обычно пользователю guest предоставляется минимальный доступ только в режиме чтения. Но в некоторых ситуациях и этот доступ необходимо предотвратить.
Если в сети имеется небольшое количество пользователей, то достаточно легко предоставить доступ каждому пользователю персонально. Однако в больших сетях с сотнями пользователей подобный подход займет много времени. Гораздо более удобным и эффективным является подход, когда доступ к SQL Server предоставляется целым группам пользователей. Как раз такой подход возможен при аутентификации средствами Windows, когда на уровне домена создается несколько групп, каждая из которых предназначена для решения специфических задач. На уровне SQL Server такой группе разрешается доступ к серверу, предоставляются необходимые права доступа к базам данных и их объектам. Достаточно включить учетную запись Windows в одну из групп, и пользователь получит все права доступа, предоставленные этой группе. Более того, одна и та же учетная запись может быть включена во множество групп Windows, что даст этой учетной записи возможность пользоваться правами доступа, предоставленными всем этим группам.