Материал: Разработка и проектирование базы данных в Microsoft SQL Server

Основным компонентом Microsoft SQL Server является SQL Server Database Engine, который контролирует хранение, обработку и безопасность данных. Он включает реляционный движок, который обрабатывает команды и запросы, а также механизм хранения, который управляет файлами базы данных, таблицами, страницами, индексами, буферами данных и транзакциями. Хранимые процедуры, триггеры, представления и другие объекты базы данных также создаются и выполняются механизмом Database Engine [18].

Управляет базой данных операционная система SQL Server или SQLOS; он обрабатывает функции нижнего уровня, такие как управление памятью и вводом-выводом, планирование работы и блокирование данных во избежание возникновения конфликтов. Уровень сетевого интерфейса находится над механизмом Database Engine, который использует протокол табличного потока данных Microsoft для облегчения взаимодействия запросов и ответов с серверами баз данных. На уровне пользователя администраторы баз данных SQL Server и разработчики пишут инструкции T-SQL для создания и изменения структур баз данных, управления данными, обеспечения безопасности и резервного копирования баз данных, среди других задач.

Архитектуру MS SQL Server можно разделить на следующие составляющие:

• Общая архитектура
• Архитектура памяти
• Архитектура файловой системы данных
• Архитектура файла журнала
• К общей архитектуре относят следующие компоненты:
• Клиент - если запрос инициирован.
• SQL-запрос, который является языком высокого уровня.
• Логические единицы - ключевые слова, выражения и операторы и т. д.
• Протоколы.
• Общая память
• Именованные каналы (для соединений, подключенных к локальной сети).
• TCP/IP (для соединений, подключенных к WAN).
• VIA-Virtual Interface Adapter
• Сервер - где установлены службы SQL и базы данных.
• Реляционный движок - здесь выполняются запросы БД. Он содержит парсер запросов, оптимизатор запросов и исполнитель запросов.
• Парсер команд (Command Parser) и компилятор (Translator). Проверяют синтаксис запроса и преобразуют запрос в машинный язык.
• Storage Engine - отвечает за хранение и извлечение данных в системе хранения
• Операционная система SQL – это промежуточное звено между главной машиной (ОС Windows) и SQL Server. Все действия, выполняемые с помощью ядра базы данных, выполняются ОС SQL. SQL OS предоставляет различные службы операционной системы, такие как управление памятью с пулом буферов, буфером журнала и обнаружением блокировки с использованием блокировки и блокировки.
• Checkpoint Process - контрольная точка - это внутренний процесс, который записывает все измененные страницы из буфера на физический диск. Контрольная точка помогает сократить время восстановления для SQL Server в случае неожиданного отключения или сбоя системы [19,20].

Ниже приведены некоторые из основных особенностей архитектуры памяти.

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

Память в Windows может быть ассоциирована с виртуальным адресным пространством, разделяемым режимом ядра (режим ОС) и пользовательским режимом (приложение, подобное SQL Server).

Управление буфером является ключевым компонентом в достижении высокой эффективности ввода-вывода. Компонент управления буфером состоит из двух механизмов: диспетчера буфера для доступа и обновления страниц базы данных и пула буферов для сокращения ввода-вывода файлов базы данных.

Пул буферов разделен на несколько разделов. Наиболее важными из них являются буферный кэш (также называемый кэшем данных) и кэш процедур. Буферный кэш хранит страницы данных в памяти, так что часто используемые данные могут быть извлечены из кэша. Альтернативой будет чтение данных с диска. Чтение страниц данных из кэша оптимизирует производительность, сводя к минимуму количество необходимых операций ввода-вывода, которые по своей природе медленнее, чем извлечение данных из памяти [21].

Хранилище процедур содержит процедуры и планы выполнения запросов, чтобы свести к минимуму количество раз, когда планы запросов должны быть сгенерированы. Более подробную информацию о размере и активности в кэше процедур, можно узнать, используя инструкцию DBCC PROCCACHE.

Другие части буферного пула включают в себя:

• Структуры данных уровня системы - содержат данные уровня экземпляра SQL Server о базах данных и блокировках.
• Кэш журнала - зарезервирован для чтения и записи страниц журнала транзакций.
• Контекст соединения. Каждое подключение к экземпляру занимает небольшую область памяти для записи текущего состояния соединения. Эта информация включает хранимую процедуру и пользовательские параметры функции, позиции курсора и многое другое.
• Стековое пространство - Windows выделяет пространство стека для каждого потока, запущенного SQL Server [22].
• Архитектура файла данных имеет следующие компоненты:
• Группы файлов. Файлы базы данных можно сгруппировать в группы файлов для целей распределения и администрирования. Ни один файл не может быть членом более чем одной группы файлов. Файлы журналов никогда не входят в группу файлов. Лог-пространство управляется отдельно от пространства данных. Существует два типа групп файлов в SQL Server: Primary и User-defined.
• Файлы. Базы данных имеют три типа файлов: первичный файл данных, файл вторичных данных и файл журнала. Первичный файл данных является отправной точкой базы данных и указывает на другие файлы в базе данных. Каждая база данных имеет один первичный файл данных. Мы можем предоставить любое расширение для основного файла данных, но рекомендуемое расширение - .mdf. Вторичный файл данных - это файл, отличный от основного файла данных в этой базе данных. Некоторые базы данных могут иметь несколько вторичных файлов данных. В некоторых базах данных может отсутствовать один дополнительный файл данных. Рекомендуемым расширением для файла вторичных данных является .ndf. Файлы журнала содержат всю информацию журнала, используемую для восстановления базы данных. База данных должна иметь как минимум один файл журнала. Рекомендуемым расширением для файла журнала является ldf. Расположение всех файлов в базе данных записывается как в основной базе данных, так и в основной файл базы данных. В большинстве случаев механизм базы данных использует расположение файла из основной базы данных. Файлы имеют два имени - логическое и физическое. Логическое имя используется для ссылки на файл во всех операторах T-SQL. Физическое имя является именем OS_file_name, оно должно соответствовать правилам ОС. Файлы данных и журнала могут быть расположены в файловых системах FAT или NTFS, но не могут работать со сжатыми файловыми системами [23]. В одной базе данных может быть до 32 767 файлов.
• Extents. Это базовая единица, в которой пространство выделяется для таблиц и индексов. Объем - 8 непрерывных страниц или 64 КБ. SQL Server имеет два типа экстентов - Uniform и Mixed. Равномерные экстенты состоят из одного объекта. Смешанные экстенты разделяются на количество до восьми объектов.
• Страницы. Это фундаментальная единица хранения данных в MS SQL Server. Размер страницы - 8 КБ. Начало каждой страницы - это 96-байтовый заголовок, используемый для хранения системной информации, такой как тип страницы, количество свободного места на странице и идентификатор объекта, владеющего страницей. В SQL Server имеется 9 типов страниц данных.
• Данные. Строки данных со всеми данными, кроме текстовых, текстовых и графических данных.
• Индекс - записи индекса

Журнал транзакций SQL Server работает логически, как если бы журнал транзакций представлял собой строку записей журнала. Каждая запись журнала идентифицируется по порядковому номеру журнала. Она содержит идентификатор транзакции, к которой принадлежит.

SQL Server Database Engine делит каждый физический файл журнала на внутренние файлы виртуальных журналов. Файлы виртуального журнала не имеют фиксированного размера, и нет фиксированного количества файлов виртуальных журналов для физического файла журнала.

Механизм Database Engine динамически выбирает размер виртуальных файлов журнала, когда он создает или расширяет файлы журналов. Движок базы данных пытается поддерживать небольшое количество виртуальных файлов. Размер или количество виртуальных файлов журнала не может быть настроено или установлено администраторами. Если файлы журналов увеличиваются до большого размера из-за большого количества небольших приращений, у них будет много виртуальных файлов журнала. Это может замедлить запуск базы данных, а также резервное копирование и восстановление.

Microsoft также объединяет множество инструментов управления данными, бизнес-аналитики (BI) и аналитики с SQL Server. В дополнение к технологиям R Services и технологии Machine Learning Services, впервые появившимся в SQL Server 2016, предложения по анализу данных включают SQL Server Analysis Services, аналитический механизм, который обрабатывает данные для использования в приложениях BI и визуализации данных, а также службы отчетов SQL Server , который поддерживает создание и доставку отчетов BI.

На стороне управления данными Microsoft SQL Server включает службы интеграции SQL Server, службы качества данных SQL Server и основные службы данных SQL Server. Также в комплекте с СУБД находятся два набора инструментов для администраторов баз данных и разработчиков: инструменты данных SQL Server для использования в разработке баз данных и SQL Server Management Studio для использования при развертывании, мониторинге и управлении базами данных.

Microsoft предлагает SQL Server в четырех основных версиях, которые предоставляют разные уровни услуг. Две из них доступны бесплатно: полнофункциональная версия для разработчиков для использования в разработке и тестировании базы данных, а также версия Express, которая может использоваться для запуска небольших баз данных объемом до 10 ГБ. Для больших приложений Microsoft продает корпоративную версию, которая включает в себя все функции SQL Server, а также стандартную версию с частичным набором функций и ограничениями на количество ядер процессора и размеров памяти, которые пользователи могут настраивать на своих серверах баз данных.

Однако, когда SQL Server 2016 с пакетом обновления 1 (SP1) был выпущен в конце 2016 года, Microsoft сделала некоторые функции, ранее ограниченные версией Enterprise, доступной как часть стандартных и экспресс-версий. Это включало OLTP, PolyBase, индексы столбцов и разделение, сжатие данных и возможность изменения данных для хранилищ данных, а также несколько функций безопасности. Кроме того, компания реализовала согласованную модель программирования в разных выпусках с пакетом обновления 1 (SP1) для SQL Server 2016, что упростило масштабирование приложений от одного издания к другому [24].

Расширенные функции безопасности, поддерживаемые во всех выпусках Microsoft SQL Server, начиная с пакета обновления 1 (SP1) для SQL Server 2016, включают в себя три технологии, добавленные к версии 2016:

• Always Encrypted, которая позволяет пользователю обновлять зашифрованные данные без необходимости их расшифровки;
• безопасность на уровне строк, которая позволяет контролировать доступ к данным на уровне строк в таблицах базы данных;
• динамическое маскирование данных, которое автоматически скрывает элементы конфиденциальных данных от пользователей без прав на полный доступ.

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

Большинство этих инструментов и других функций в Microsoft SQL Server также поддерживаются в Azure SQL Database, службе облачной базы данных, созданной на базе SQL Server Database Engine. Кроме того, пользователи могут запускать SQL Server непосредственно на Azure с помощью технологии SQL Server на Azure Virtual Machines; он настраивает СУБД в виртуальных машинах Windows Server, работающих на Azure [25]. Предложение VM оптимизировано для переноса или расширения локальных приложений SQL Server в облаке, а база данных Azure SQL предназначена для использования в новых облачных приложениях.

В облаке Microsoft также предлагает Azure SQL Data Warehouse, службу хранилища данных, основанную на реализации SQL Server с использованием массивной параллельной обработки (MPP). Версия MPP, первоначально автономный продукт под названием SQL Server Parallel Data Warehouse, также доступна для использования на местах как часть платформы Microsoft Analytics Platform System, которая сочетает ее с PolyBase и другими крупными технологиями данных.

2.3 Версии SQL Server

В период с 1995 по 2016 год Microsoft выпустила 10 версий SQL Server. Ранние версии были нацелены в первую очередь на ведомственные и рабочие группы, но Microsoft расширила возможности SQL Server в последующих, превратив их в реляционную СУБД корпоративного класса, которая может конкурировать с Oracle Database, DB2 и другими платформами для использования в высокопроизводительных СУБД. За прошедшие годы Microsoft также включила в SQL Server различные инструменты управления данными и аналитики данных, а также функциональность для поддержки новых технологий, в том числе веб-технологий, облачных вычислений и мобильных устройств.

Microsoft SQL Server 2016, который стал общедоступным в июне 2016 года, был разработан в рамках «первой технологии мобильных технологий», принятой Microsoft двумя годами ранее. Среди прочего, SQL Server 2016 добавил новые функции для настройки производительности, аналитики в реальном времени, визуализации данных и отчетности на мобильных устройствах, а также поддержку гибридных облаков, которая позволяет администраторам баз данных запускать базы данных на основе комбинации локальных и общедоступных облачных сервисов для снижения затрат на. Например, технология SQL Server Stretch Database перемещает редко получаемые данные с локальных устройств хранения в облако Microsoft Azure, сохраняя при этом данные для запросов, если это необходимо.

SQL Server 2016 также увеличил поддержку аналитики больших объемов данных и других приложений расширенной аналитики через службы SQL Server R, что позволяет СУБД запускать аналитические приложения, написанные на языке программирования с открытым исходным кодом R, и PolyBase - технологию, которая позволяет пользователям SQL Server получать доступ к данным хранятся в кластерах Hadoop или хранилище Azure blob для анализа. Кроме того, SQL Server 2016 был первой версией СУБД для работы исключительно на 64-битных серверах на базе микропроцессоров x64.

Предыдущие версии включали SQL Server 2005, SQL Server 2008 и SQL Server 2008 R2, который считался основным выпуском. Далее появились SQL Server 2012 и SQL Server 2014. В SQL Server 2012 появились новые функции, такие как индексы столбцов. В SQL Server 2014 добавлен встроенный OLTP-модуль, который позволяет пользователям запускать приложения обработки транзакций онлайн (OLTP) с данными, хранящимися в таблицах с оптимизацией памяти, а не на стандартных дисковых. Еще одной новой особенностью SQL Server 2014 было расширение пула буферов, которое объединяет кеш-память буферного пула SQL Server с твердотельным диском - еще одна функция, предназначенная для увеличения пропускной способности ввода-вывода за счет выгрузки данных с обычных жестких дисков.