Теория администрирования баз данных вне зависимости от выбранной СУБД. Практические основы администрирования на СУБД MS SQL Server

Администрирование баз данных ЛЕКЦИЯ 1. ВВЕДЕНИЕ В АДМИНИСТРИРОВАНИЕ MYSQL. РЕДИЗАЙН (ПЕРЕПРОЕКТИРОВАНИЕ) СТРУКТУРЫ БД. Цель и задачи курса: Цель курса – изучение теории администрирования баз данных вне зависимости от выбранной СУБД, практические основы администрирования на СУБД MS SQL Server. Задачи курса:  реализация задач администрирования баз данных;  освоить принципы управления индексами;  изучение средств управления физической моделью данных;  оптимизация процедурных планов исполнения SQL запросов. 2 ЛИТЕРАТУРА: Д.Крёнке. Теория и практика построения БД Волк В.К. Проектирование, программирование, управление и администрирование БД 3 Полякова Л.Н. Основы SQL И.В. Ананченко, И.В. Козлов. Администрирование Microsoft SQL Server 4 ПЛАН ЛЕКЦИОННЫХ ЗАНЯТИЙ: Лекция 1. Введение в администрирование MySQL. Лекция 2. Резервное копирование: планирование и реализация. Лекция 3. Управление многопользовательскими БД. Лекция 4. Управление безопасностью многопользовательских БД. Лекция 5. Автоматизация обслуживания сервера. 5 ПЛАН ПРАКТИЧЕСКИХ РАБОТ: Практическая работа №1. Работа с базами данных SQL Server. Файловая структура. Практическая работа Восстановление БД. №2. Резервное копирование баз данных. Практическая работа №3. Автоматизация административных задач. Обмен данными. Практическая работа №4. Индексирование таблиц в SQL SERVER. 6 ПЛАН ЛАБОРАТОРНЫХ РАБОТ: Лабораторная работа №1. Обеспечение безопасности в SQL Server. Лабораторная работа №2. Мониторинг производительности сервера. Лабораторная работа №3. Репликация. Лабораторная работа №4. Реализация алгоритмов модификации данных. 7 Необходимое ПО: Microsoft SQL Server Management Studio 8 СОДЕРЖАНИЕ ЛЕКЦИИ:  Основные требования, предъявляемые к администраторам баз данных.  Обзор платформы SQL Server.  Инструменты и методы управления базами данных.  Редизайн (изменение) структуры БД (назначение, технология анализа, инжиниринг). 9 1. Основные требования, предъявляемые к администраторам баз данных Технологические знания и навыки. Администрирование баз данных требует не только глубокого знания платформы, используемой для размещения баз данных, но также знаний в области конфигураций операционной системы, устройств хранения данных и сетей. Бизнес-осведомленность. Администратор баз данных должен понимать бизнес-контекст, в котором функционирует база данных, и ее роль в поддержке бизнеса. Организационные навыки. Системы баз данных могут быть сложными, с большим количеством компонентов и подсистем. Некоторые задачи должны выполняться в определенное время. Хороший администратор должен отслеживать эти задачи, а также оперативно реагировать на неожиданные проблемы в случае их возникновения. Умение выстраивать приоритеты. Когда возникают неожиданные проблемы, которые могут негативно повлиять на работу с базой данных, администратор должен грамотно расположить их решение по приоритетам, основываясь на таких факторах, как соглашения об уровне обслуживания (service level agreement, SLA), число пользователей и затронутых систем, а также степень влияния возникшей проблемы на текущие операции. 10 Задачи администрирования баз данных Подготовка баз данных и серверов баз данных. Это может включать установку и настройку экземпляров SQL Server на физических или виртуальных серверах или создание новых виртуальных машин на основе шаблонов изображений, а также создание баз данных и распределение их данных и файлов журналов на устройствах хранения. Сохранение файлов баз данных и объектов. После того, как база данных создана и заполняется данными, для оптимальной работы требуется ее постоянное обслуживание и оптимизация. Это предполагает уменьшение фрагментации, которая появляется по мере того, как записи добавляются и удаляются, сохранение файлов данных соответствующего размера и обеспечение последовательной структуры логических и физических данных. Управление восстановлением в случае сбоя базы данных. Базы данных часто имеют решающее значение для деловых операций, поэтому главной задачей DBA является планирование соответствующей стратегии резервного копирования и восстановления для каждой базы данных, что позволило бы осуществить восстановление базы данных в случае сбоя. 11 Импорт и экспорт данных. Данные часто передаются между системами, поэтому администраторам баз данных необходимо выполнять экспорт или импорт данных. Применение безопасности к данным. Серверы баз данных организации содержат данные, которые позволяют бизнесу работать. Нарушение безопасности может быть дорогостоящим и трудоемким для восстановления, приводить к потере доверия клиентов. Администратор баз данных должен реализовывать такие политики безопасности, которые обеспечивают пользователям доступ к необходимым данным, но при этом соблюдают правовые нормы бизнеса по защите своих активов, а также снижают риски, связанные с нарушением безопасности. Мониторинг и устранение неполадок систем баз данных. Многие операции по администрированию баз данных являются реактивными, то есть они предполагают принятие мер для устранения неполадок и возникающих проблем. Грамотные администраторы БД осуществляют упреждающий подход, чтобы попытаться обнаружить потенциальные проблемы до того, как они начнут влиять на операции с данными. 12 Перечень профессиональных стандартов 1. В стандарте США SOC (Standard Occupational Classification) работы по сопровождению баз данных включены в подкатегорию "Database Administrators» в составе категории «Database and Systems Administrators and Network Architects», а функции по созданию баз данных определены в подкатегории «Software Developers», входящей в состав категории «Software and Web Developers, Programmers and Testers». 2. Соответствующий европейский стандарт European ICT Professional Profiles предусматривает профиль «Developer» для деятельности по проектированию баз данных и отдельный профиль «Database Administrator», в котором определены работы по обеспечению их функционирования в процессе эксплуатации. 3. Российские профессиональные стандарты «Архитектор программного обеспечения», «Руководитель разработки программного обеспечения" и «Программист» определяют функции проектирования и программирования баз данных, а комплекс работ по их сопровождению регламентирован стандартом «Администратор баз данных», в котором приведен детальный перечень трудовых функций с указанием для каждой из них квалификационного уровня, требований к профессиональной компетентности, базовому образованию и опыту работы. Стандарты «Специалист по информационным системам», «Системный администратор информационно- коммуникационных систем» и «Специалист по защите информации в автоматизированных системах" также регламентируют отдельные 13 аспекты администрирования баз данных. 1. Standard Occupational Classification [Electronic resource] / U. S. Bureau of Labor Statistics. Electronic data (1 file: 974848 bytes). URL: https: //www. bls. gov/soc/2018/soc_structure_2018. pdf, free. Title from screen. 2. ГОСТ Р 56413-2015. Информационные технологии. Европейские про- фили профессий ИКТ-сектора / CWA 16458:2012 Information technologies. European ICT professional profiles. Введ. 01.06.2016 приказом Федерального агентства по техническому регулированию и метрологии от 29 мая 2015 г. № 465-ст. 3. Профессиональный стандарт 06.011 «Администратор баз данных». Утвержден приказом Министерства труда и социальной защиты Российской Федерации от 12 декабря 2016 г. № 727н. 4. Профессиональный стандарт 06.015 «Специалист по информационным системам». Утвержден приказом Министерства труда и социальной защиты Российской Федерации от 12 декабря 2016 г. № 727н. 5. Профессиональный стандарт 06,026 «Системный администратор информационно-коммуникационных систем". Утвержден приказом Министерства труда и социальной защиты Российской Федерации от 5 октября 2015 г. № 684н. 6. Профессиональный стандарт 06.033 «Специалист по защите информации в автоматизированных системах". Утвержден приказом Министерства труда и социальной защиты Российской Федерации от 15 сентября 2016 г. № 522н. 14 Документация Параметры конфигурации и расположения файлов Контактная информация персонала Сведения Стандартные правила и графики технического обслуживания Процедуры аварийного восстановления 15 2. Обзор платформы SQL Server Enterprise, который является ведущим выпуском. Содержит все функции SQLсервера, включая службы BI и поддержку виртуализации. Standard, включающий базовый механизм database engine, а также базовое создание отчетов и возможности аналитики. Однако поддерживает меньше ядер процессора и не предлагает всех возможностей, безопасности и функций организации хранилищ данных, представленных в версии Enterprise. 16 Web Edition - решение с низкой совокупной стоимостью владения, предназначенное для размещения веб-сайтов и дополнительных вебуслуг, в котором по доступной цене обеспечивает масштабируемость и функции управления для небольших и крупномасштабных вебпроектов. Express - бесплатная СУБД для баз данных начального уровня. Выпуск хорошо подходит для обучения, для создания управляемых данными приложений, работающих на рабочих станциях и небольших серверах. 17 3. Инструменты и методы управления базами данных SQL Server Management Studio SQL Server Management Studio (SSMS) является основным инструментом управления базами данных для серверов баз данных SQL Server. Он представляет собой графический пользовательский интерфейс (GUI) и интерфейс сценариев Transact-SQL для управления компонентом ядра базы данных и базами данных. SQL Server Configuration Manager SQL Server Configuration Manager (SSCM) можно использовать для настройки и управления службами SQL Server, а также для управления протоколами сети клиента и псевдонимами. 18 SQL Profiler Можно использовать SQL Profiler в случаях, когда необходимо изучить работу базы данных SQL Server или для записи трассировки. Это позволяет исследовать и устранять проблемы, а также оптимизировать конфигурацию базы данных на основе фактических моделей использования. Однако в настоящее время этот инструмент является устаревшим и заменен расширенными событиями. SQL Server Database Engine Tuning Advisor Помощник по настройке ядра СУБД SQL Server (DTA). Правильно оптимизированная база данных использует индексы и другие структуры для повышения производительности запросов. DTA дает рекомендации на основе анализа типичных рабочих нагрузок базы данных и может служить полезной отправной точкой для оптимизации баз данных. 19 SQL Server Import and Export Этот инструмент представляет собой графический мастер, который упрощает процесс передачи данных в базу данных или из базы данных. Sqlcmd utility Произносится "SQL Command". Это инструмент командной строки, который можно использовать для подключения к экземпляру SQL Server и запускать инструкции Transact-SQL или сценарии 20 Bcp utility BCP (Bulk Copy Program) означает программы массового копирования, и утилита bcp является инструментом командной строки для импорта и экспорта данных в SQL Server. Использование Transact SQL для выполнения задач управления Команды Transact-SQL, которые можно использовать для выполнения задач управления. 21 Задачи управления Инструкции языка DDL. Например, можно использовать инструкции «CREATE DATABASE» или «DROP DATABASE» для создания БД или для удаления базы данных. - Системные хранимые процедуры и функции. SQL Server предоставляет системные хранимые процедуры и функции, которые инкапсулируют общие задачи настройки и управления системы. Например, можно использовать системную хранимую процедуру sp_configure для задания параметров конфигурации экземпляра SQL Server. - DBCC (Database Console Commands). Команды DBCC используются для выполнения конкретных задач по настройке и обслуживанию, а также для выполнения проверок в базе данных. Например, можно использовать команду DBCC CHECKDB для проверки физической и логической целостности объектов в базе данных. 22 Операторы определения данных: (data definition language, DDL). группы операторов SQL: Операторы манипуляции данными: (data manipulation language, DML). Операторы определения доступа к данным: (data control language, DCL) Операторы управления транзакциями: (transaction control language, TCL) 23 CREATE TABLE <имя таблицы> (<имя столбца> [доп.параметры: первичный ключ, возможность NULL и т.д.] <тип столбца>) Операторы определения данных: (data definition language, DDL). ALTER TABLE <имя таблицы> ADD <имя столбца> (свойства столбца) ALTER TABLE <имя таблицы> ALTER COLUMN <имя столбца> (новые свойства столбца) DROP TABLE <имя таблицы> 24 SELECT <столбец\столбцы> FROM <имя таблицы> WHERE <условия> Операторы определения доступа к данным: (data control language, DCL) INSERT INTO <имя таблицы> (<столбцы, куда будут записаны новые значения>) VALUES(<имя столбца> <значение>, …) UPDATE <имя таблицы> SET <столбец, куда будут записаны новые значения> = <значение> WHERE <условие> DELETE FROM <имя таблицы> WHERE <условия> 25 Операторы манипуляции данными: (data manipulation language, DML). Операторы управления транзакциями: (transaction control language, TCL) GRANT – предоставляет пользователю или группе разрешения на определённые операции с объектом; REVOKE – отзывает выданные разрешения; DENY– задаёт запрет, имеющий приоритет над разрешением. BEGIN TRANSACTION – служит для определения начала транзакции; COMMIT TRANSACTION – применяет транзакцию; ROLLBACK TRANSACTION – откатывает все изменения, сделанные в контексте текущей транзакции; SAVE TRANSACTION – устанавливает промежуточную точку сохранения внутри транзакции. 26 4. Редизайн структуры БД Причины перепроектирования БД - Увеличение компании за счет новых дивизионов. - Выход компании на новые рынки. - Добавление новых продуктов или услуг. - Переход компании на новую форму функционирования. - Внедрение новых политик безопасности информации. - Внедрение новых политик по принципам хранения информации 27 Инструменты изучения физических моделей БД «обратный инжиниринг» (reverse engineering) БД Графики зависимости (dependency graphs) позволяют составить представление о большинстве элементов БД в удобном и доступном виде. Инструменты REмоделирования выводят физическую модель в логическом виде (без представлений, триггеров, процедур и т.д.) Инструмент анализа баз данных. Данные графики состоят из узлов, являющихся артефактами баз данных. Узлы связаны друг с другом многочисленными связями. Одна сторона связи показывает ее источник, а другая – адресат. Erwin Data Modeler и MySQL Workbench 28 Решение задач изменения БД. Изменение названия таблицы. Изучение БД через граф зависимостей часто показывает, что у одной таблицы может быть несколько связанных с ней элементов. Это провоцирует проблему переименования таблицы. Данную проблему принято решать через создание новой таблицы, переноса всех данных старой таблицы и уничтожение старой таблицы, потерявшей актуальность. 29 Пример процесса изменения названия таблицы: 1. Создаем переменные с внешними и внутренними ключами для новой таблицы 2. С помощью вложенного запроса копируем данные в новую таблицу из старой 3. Заменяем внешние ключи в других связанных таблицах Добавление и изменение столбцов существующих таблиц: Для добавления столбцов используется команда SQL ALTER TABLE с функцией ADD. В случае использования команды в таком виде добавляется столбец, заполненный нулевыми или дефолтными значениями. В случае необходимости добавить определенные значения в создаваемый столбец или поставить условие NOT NULL используется команда ALTER TABLE с функцией ALTER COLUMN и NOT NULL. Удаление столбца осуществляется командой ALTER TABLE с функцией DROP COLUMN 30 Изменение кардинальности связей: Ключевым элементом изменения кардинальности связей является управление свойствами внешних ключей. В первую очередь это касается свойства NULLNOT NULL. В случае, когда необходимо превратить идентификационно- независимую связь в идентификационно-зависимую, технически у внешнего ключа таблицы потомка свойство NULL меняется на NOT NULL. При этом используется грамматика команды ATLER TABLE с обращением к переменной внешнего ключа DROP-ADD CONSTRAINT. 31 Прямой инжиниринг Прямой инжиниринг, в отличие от обратного – это процедура генерации кода приложения или кода SQL относительно диаграммы (логической или физической) базы данных. Данная процедура позволяет облегчить процесс создания сложных физических моделей данных. 32 Системные БД SQL Server поддерживает набор баз данных системного уровня, которые называются системными базами данных и жизненно важны для работы экземпляра сервера. После каждого значительного обновления необходимо обязательно создавать резервные копии ряда системных баз данных: msdb, master и model. Если какая-нибудь из баз данных на экземпляре сервера использует репликацию, то необходимо также создавать резервную копию системной базы данных distribution . Резервные копии системных баз данных позволят восстановить систему SQL Server в случае сбоя, например отказа жесткого диска. 33 Системные БД Системная база данных Master msdb model Resource tempdb Описание В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server. Используется агентом SQL Server для планирования предупреждений и задач. Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения. База данных только для чтения. Содержит системные объекты, которые входят в состав SQL Server. Системные объекты физически хранятся в базе данных Resource, но логически отображаются в схеме sys любой базы данных. Рабочее пространство для временных объектов или взаимодействия результирующих наборов. 34 Изменение параметров файловой структуры пользовательской БД с помощью команд T-SQL: ALTER DATABASE Catalogue MODIFY FILE (NAME = 'Catalogue_log', SIZE=20MB) процедура: Exec sp_helpdb (сообщает информацию об указанной базе данных или всех базах данных), Exec sp_spaceused (выводит количество строк, зарезервированное место на диске и место на диске, которое используется таблицей, индексированным представлением или очередью компонента). Use Catalogue EXEC sp_helpdb Use Catalogue EXEC sp_spaceused 35 Cписок ролей сервера, доступных в SQL Server   bulkadmin: член этой роли может выполнять инструкции Bulk Insert. dbcreator: член этой роли может создавать, изменять, восстанавливать и удалять любую базу данных в SQL.  diskadmin: роли Disk admin serer используются для управления дисковыми файлами.  processadmin: эта роль сервера может завершить все запущенные процессы в экземпляре SQL.  public: по умолчанию все учетные данные принадлежат роли общего сервера.     securityadmin: член этой роли предоставляет, запрещает, отменяет разрешения как на уровне сервера, так и на уровне базы данных. Могут сбросить все учетные записи SQL Server. serveradmin: члены администратора сервера могут завершить работу сервера, или они могут изменить общие конфигурации сервера. setupadmin: члены с этой ролью сервера могут удалять или добавлять связанные серверы с помощью запросов Transact-SQL. sysadmin: участник может выполнять любые действия на сервере. Обходит любые ограничения, предоставляемые другими участниками. 36 37 Ведение учетных записей пользователей 1. Хранимая процедура sp_helplogins возвращает два результирующих множества. В первом перечисляется каждое регистрационное имя и сведения о нем, во втором – пользователи, связанные с этими регистрационными именами. 2. Создание учетных записей пользователей: CREATE LOGIN <имя> PASSWORD=‘ ’; 3. Удаление учетных записей пользователей: DROP LOGIN <имя> 4. Активизация и дезактивизация учетных записей: дезактивировать учетную запись , не удаляя ее: ALTER LOGIN <имя> DISABLE активировать учетную запись: ALTER LOGIN <имя> ENABLE 38 Ведение учетных записей пользователей 5. Переименование регистрационных имен: ALTER LOGIN <имя> WITH NAME= <н_имя> 6. Смена паролей: ALTER LOGIN <имя> WITH PASSWORD=‘н_пароль’; 7. Управление правами доступа: 7.1 установка прав доступа – GRANT: GRANT CREATE TABLE TO <имя> , т.е. предоставляется право пользоваться оператором CREATE TABLE для создания таблиц в БД. (WITH GRANT OPTION – предоставить права доступа одного пользователя другим) 7.2 отмена прав доступа – REVOKE: REVOKE CREATE TABLE FROM <имя> ; 8. DENY имеет приоритет над всеми разрешениями, но не применяется к владельцам объектов или членам с предопределенной ролью сервера sysadmin. 39