Слайд 1Цели и задачи дисциплины
Цель – освоение технологий хранения и анализа
корпоративных данных в системах клиент-серверной архитектуры. Изучение программных средств разработки
и администрирования многопользовательских баз данных и корпоративных хранилищ для решения экономических задач.
Задачи:
способствовать освоению клиент-серверные технологии СУБД;
способствовать освоению СУБД, позволяющие создавать информационные системы, имеющие архитектуру клиент-сервер;
способствовать освоению структурированного языка запросов SQL, приемов и особенностей разработки запросов и процедур обработки данных на SQL;
способствовать получению базового уровня в проектировании и разработке баз данных в системе СУБД MS SQL Server;
способствовать приобретению практических навыков по созданию клиентского приложения и работу из него с базой данных сервера.
Слайд 2В результате изучения дисциплины студент должен
Знать: основные концепции СУБД клиент-серверной архитектуры,
типовые задачи, выполняемые при создании серверных баз данных и их
администрировании основные функции и типовую организацию современных СУБД; структурированный язык запросов; программные интерфейсы подключения клиентского приложения к базе данных.
Уметь: выполнять логическое проектирование учебной базы данных; создавать учебную базу данных средствами MS SQL Server; разрабатывать запросы к базе данных и процедуры обработки данных с целью получения данных, необходимых при решении экономических задач; разрабатывать клиентские приложения для работы с учебной базой данных, созданной средствами MS SQL Server; проектировать и создавать базы данных и приложения пользователя в клиент-серверной архитектуре; эффективно выполнять задачи их администрирования.
Владеть: языком SQL для создания и администрирования многопользовательских баз данных и объектов серверной бизнес-логики; навыками проектирования и создания приложений в клиент-серверной архитектуре.
Слайд 3
Наиболее популярные серверные СУБД
Слайд 4Microsoft® SQL Server™ — это система анализа и управления реляционными
базами данных в решениях электронной коммерции, производственных отраслей и хранилищ
данных
Слайд 5Microsoft SQL Server: история
Началом истории Microsoft SQL Server по праву
можно считать 1986 год,
когда Microsoft и Sybase выпустили совместную версию
продукта — SQL
Server 1.0 и адаптировали ее для операционной системы OS/2 при поддержке компании AshtonTate, которая в то время была лидером на рынке СУБД для персональных компьютеров. Выпущенный в 1989 году продукт не получил должного признания из-за проблем, связанных с продвижением OS/2.
В 1990 году Sybase и Microsoft прервали соглашение с AshtonTate и выпустили версию SQL Server 1.1 для новой операционной системы Windows 3.0.
Microsoft отвечала за клиентские утилиты, программные интерфейсы и средства управления, а Sybase — за разработку ядра базы данных.
В 1992 году началась разработка новой версии продукта — SQL Server on Windows NT, который был выпущен в 1993 году одновременно с серверной операционной системой — Microsoft Windows NT. Тесная интеграция с Windows NT обеспечила продукту высокую производительность, управляемость и впервые у Microsoft появилась система управления базами данных, которая могла конкурировать с аналогичными продуктами на платформе UNIX.
В 1994 году Microsoft и Sybase прервали совместное пятилетнее соглашение и бывшие партнеры занялись самостоятельным развитием своих, теперь уже конкурирующих продуктов.
Слайд 6
Microsoft SQL Server: история
В 1995 и 1996 годах увидели свет
версии SQL Server 6.0 и 6.5, но некоторые проблемы с
производительностью и управляемостью не позволили этим
продуктам завоевать существенную долю рынка корпоративных СУБД. Было принято решение приостановить развитие текущей версии платформы и начать создание продукта «с нуля».
Примерно в то же время компания DEC продала свою систему управления базами данных компании Oracle и Microsoft удалось заполучить ведущих специалистов компании DEC — Джима Грея (Jim Gray), Дэйва Ломета (Dave Lomet) и Фила Бернштейна (Phil Bernstein).
Команде разработчиков была поставлена задача — создать новое ядро базы
данных с поддержкой масштабируемости, новый процессор обработки
запросов, систему самонастройки, самоуправления, а также реализовать
поддержку OLAP и ETL с привлечением специалистов из компании Panorama.
Разработка новой СУБД заняла около трех лет и в 1998 году был выпущен
продукт под названием SQL Server 7.0 — Microsoft начала завоевывать не
только рынок реляционных СУБД, но и такие новые рынки, как business
intelligence и data warehousing.
Слайд 7
Microsoft SQL Server: история
Параллельно велась работа над SQL Server 2000,
который включал в себя поддержку XML, индексированные представления, распределенные разделы
на основе представлений, а также более чем 20%ное увеличение производительности для практически всех ключевых компонентов продукта. В 2000 году Microsoft стала полноправным лидером на рынке СУБД для платформы Windows.
Дальнейшее развитие продукта — в версиях, выпущенных в 2005 г. Microsoft SQL Server (кодовое название Yukon) и в 2008-2010 гг. – Microsoft SQL Server 2008 (кодовое название Katmai), Microsoft SQL Server 2008 R — добавило увеличение производительности, управляемости, расширенную поддержку различных типов данных, интегрированные системы создания отчетов, трансформации данных, расширенные функции анализа и т. п.
В 2010-2012 гг. выпущен MS SQL Server 2012 - полностью готовая к использованию в облаке информационная платформа, предоставляющая уверенность в надёжности критически важных для бизнеса систем при работе с данными и характеризующаяся повышенной доступностью, высокой производительностью и улучшенными функциональными возможностями в части безопасности для критически важных рабочих нагрузок.
Слайд 8
Microsoft SQL Server: история
SQL Server 2012 доступен в редакциях (выпусках):
Standard -
базовые возможности по управлению данными и бизнес-аналитике с некритичными для
бизнеса нагрузками и с минимальными затратами.
Business Intelligence - новый выпуск, который предоставляет расширенную корпоративную и персональную бизнес-аналитику
Enterprise для критически важных приложений и больших хранилищ данных
Web Edition предназначен для размещения веб-сайтов и дополнительных веб-услуг, который по доступной цене обеспечивает масштабируемость и функции управления для небольших и крупномасштабных веб-проектов.
Developer Edition включает все функциональные возможности выпуска Enterprise Edition, однако лицензируется как система для разработки и тестирования, а не для применения в качестве рабочего сервера
Express является бесплатной базой данных начального уровня и идеально подходит для обучения, а также для создания управляемых данными приложений, работающих на рабочих станциях и небольших серверах. Этот выпуск — лучший выбор для независимых поставщиков программного обеспечения, непрофессиональных разработчиков и любителей, создающих клиентские приложения.
Слайд 9Редакции SQL Server
SQL Server Express Edition
SQL Server Standard Edition
SQL
Server Enterprise Edition
SQL Web Edition
SQL Business Intelligence
SQL Server Developer
Edition
Слайд 10
Microsoft SQL Server: история
В следующей таблице более подробно описаны серверные
выпуски SQL Server
Слайд 11
Специализированные выпуски предназначены для особых пользовательских сред.
Слайд 12SQL Server 2012 SP1
При разработке каждой версии SQL Server корпорация Microsoft использует
внутреннее версионирование для контроля за выпускаемыми версиями продукта, например 7.00.699,
где первое число соответствует поколению продукта, второе число — резервное, используется когда изменений в новой версии продукта для присваивания очередной версии поколения недостаточно, но данная версия выпускается как самостоятельный продукт, а не дополнение к имеющемуся. Третье число используется для указания версии основного исполняемого файла продукта
Определение версии и выпуска
Select @@version
Слайд 15
Компоненты SQL Server
Основным компонентом является SQL Server Database Engine, в
состав которого входит компонент Database Engine, это служба для хранения,
обра-ботки и обеспечения безопасности данных, репликации, полнотекстового поиска и средств управления реляционными и XML-данными.
Службы Analysis Services содержат средства создания и управления приложениями интерактивной аналитической обработки (OLAP) и приложениями интеллектуального анализа данных.
Службы Reporting Services включают в себя серверные и клиентские компоненты для создания, управления и развертывания табличных, матричных и графических отчетов, а также отчетов в свободной форме, службы можно использовать для разработки приложений отчетов.
Службы Integration Services представляют собой набор графических средств и программируемых объектов для перемещения, копирования и преобразования данных.
Слайд 16
Компоненты SQL Server
Full-Text Search (полнотекстовый поиск) содержит функциональность, необходимую для
выполнения полнотекстовых запросов к простым символьным данным в таблицах SQL
Server. Полнотекстовые запросы могут включать слова и фразы, несколько форм слова или фразы. Полнотекстовый поиск позволяет быстро и гибко индексировать текстовые данные, хранящихся в базе данных Microsoft SQL Server для поискового запроса.
Replication (репликация) – представляет собой набор технологий копирования и распространения данных и объектов между базами данных, а также синхронизации баз данных для поддержания согласованности. Используя репликацию, можно распространять данные в различные расположения, а также удаленным или мобильным пользователям по локальным или глобальным сетям посредством коммутируемого соединения, по беспроводным соединениям и через Интернет.
Слайд 17Платформа данных MS SQL Server включает следующие инструменты:
Relational Database
Engine – безопасное, надёжное, масштабируемое ядро с улучшенной производительностью и
поддержкой структурированных и неструктурированных (XML) данных.
Слайд 18Платформа данных MS SQL Server включает следующие инструменты (продолжение):
Integration
Services - набор графических средств и программируемых объектов для перемещения,
копирования и преобразования данных.
Analysis Services - службы для анализа данных, поддерживают OLAP - аналитическая обработка в реальном времени (OLAP) для быстрого, сложного анализа больших и смешанных наборов данных, использующая многомерное хранение.
Reporting Services - службы для создания и публикации отчетов - исчерпывающее решение для создания, управления и доставки как традиционных бумажных отчётов, так и интерактивных, основанных на технологии WWW отчётов.
Слайд 19Analysis Services
Analysis Services предоставляют единообразное и интегрированное представление всех бизнес
данных, как основы для всей традиционной отчётности, OLAP анализа, и
Data Mining.
UDM является центральным хранилищем метаданных, определяющим бизнес сущности, бизнес логику, вычисления, и метрики, служащее источником для всех отчётов, электронных таблиц, программ просмотра OLAP и аналитических приложений.
Слайд 20Reporting Services
Службы Службы Reporting Services включают в себя серверные и
клиентские компоненты для создания, управления и развертывания табличных, матричных и
графических отчетов, а также отчетов в свободной форме. Службы Службы Reporting Services являются расширяемой платформой, которую можно использовать для разработки приложений отчетов.
Слайд 23
Компоненты SQL Server
Средства управления SQL Server
Среда SQL Server Management Studio
представляет собой интегрированную среду для доступа, настройки, управления, администрирования и
разработки компонентов SQL Server. Среда Management Studio позволяет работать с SQL Server разработчикам и администраторам любого уровня подготовки.
Диспетчер конфигурации SQL Server обеспечивает базовые возможности управления конфигурациями для служб, серверных протоколов, клиентских протоколов и псевдонимов клиентов SQL Server.
SQL Server Profiler предоставляет графический пользовательский интерфейс для наблюдения за экземпляром компонента Database Engine или служб Analysis Services.
Среда Business Intelligence Development Studio представляет собой интегрированную среду разработки для решений служб Analysis Services, Reporting Services и Integration Services.
Слайд 26Диспетчер конфигурации SQL Server
обеспечивает базовые возможности управления конфигурациями для
служб, серверных протоколов, клиентских протоколов и псевдонимов клиентов SQL Server.
Слайд 27SQL Server Configuration Manager —
средство администрирования SQL Server .
Запускается оно Пуск |
Программы | Microsoft SQL Server | Configuration Tools | SQL Server Configuration Manager.
Слайд 28SQL Server Configuration Manager
Слайд 29SQL Server Configuration Manager
Первый контейнер SQL Server Services (Службы SQL
Server) ответственен за службы SQL Server
второй контейнер SQL Server Network
Configuration (Сетевая конфигурация SQL Server) — за серверные сетевые библиотеки SQL Server
третий контейнер SQL Native Client Configuration (Конфигурация SQL Native Client) — за параметры работы SQL Native Client.
Слайд 30SQL Server Configuration Manager Службы SQL Server
SQL Server реализован в
виде набора служб.
Службы – это специальные программы, которые работают
от имени своей собственной учетной записи. Службы запускаются независимо от того, вошел ли пользователь в систему.
Для каждой службы создаются специальные записи в разделе реестра HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services.
Слайд 31Псевдонимы
Обычно псевдоним нужен тогда, когда в клиентском приложении жестко прописано
имя сервера, к которому это приложение должно обращаться, а база
данных перенесена на сервер с другим именем. В этом случае проще всего создать псевдоним на клиенте, при помощи которого клиент, обращаясь по старому имени, будет перенаправляться на новый сервер. Другая ситуация, когда вам может потребоваться псевдоним, — когда вы обращаетесь на SQL Server по нестандартному порту.
Псевдонимы, которые настраиваются средствами SQL Server Configuration Manager,
Слайд 32Задание
Настройте на вашем компьютере псевдоним MyServer. При обращении к этому
псевдониму должно производиться подключение к серверу имя_вашего_компьютера\SQL
Слайд 37Проверяем – подключаемся с именем MyServer
Слайд 39
Объекты базы данных SQL Server
Создание клиент-серверного приложения, работающего с базой
данных, требует прохождения следующих этапов:
1 – разработка структуры реляционной базы
данных;
2 – администрирование базы данных на стороне сервера;
3 – программирование на стороне сервера;
4 – программирование на стороне клиента.
Разработка клиент-серверной информационной системы начинается с разработки базы данных на стороне сервера и настройки серверной платформы. Здесь можно выделить следующие задачи:
1 – создание базы данных и установка ее свойств;
2 - разработка таблиц;
3 – установление отношений между таблицами и обеспечение целостности данных;
4 – программирование на стороне сервера, написание триггеров и транзакций;
5 - ввод первоначальных данных.
Слайд 40MS SQL Server хранит данные в базах данных. На физическом
уровне БД состоит из двух или более файлов. Физическая структура
видна администраторам БД.
На логическом уровне БД состоит из следующих компонентов – таблиц, представлений и хранимых процедур.
Слайд 42
Объекты базы данных SQL Server
Таблицы - являются основной формой для
сбора информации, содержат все данные в базах данных SQL Server.
Каждая таблица представляет собой тип объекта, который имеет смысл для пользователей.
Индекс – структура на диске, связанная с таблицей или представлением, которая ускоряет поиск строк таблицы или представления. Индекс содержит ключи, созданные для одного или нескольких столбцов таблицы или представления. Эти ключи хранятся в виде b-древовидной структуры, что позволяет SQL server, быстро и эффективно находить строку или строки, связанные с ключевыми индексами. Существуют кластерные и не кластерные индексы.
Представление - виртуальная таблица или хранимый запрос. Данные доступные через представление хранятся в базе данных не как отдельный объект, а как выражение SELECT, в результате SELECT формирует виртуальную таблицу, возвращая представление. Пользователь может использовать эту виртуальную таблицу, ссылаясь на представление в
transact-sql (T-SQL) так же, как на таблицы ссылок.
Слайд 43
Объекты базы данных SQL Server
Хранимые процедуры в Microsoft SQL Server,
аналогичны процедурам в языках программирования относительно их действий:
• принимают входные
параметры для вызова процедуры или пакета и возвращать несколько значений в виде выходных параметров,
• содержат выражения программирования, которые выполняют операции в базе данных, включая вызов других процедур,
• возвращают значение состояния вызывающей процедуре или пакету, чтобы указать, выполнение или не выполнение (и причины отказа).
Для запуска хранимой процедуры используют выражение EXECUTE языка Transact-SQL. Хранимые процедуры отличаются от функций тем, что могут не возвращать значения в место их вызова и поэтому и они не могут быть использованы непосредственно в выражениях.
Слайд 44
Объекты базы данных SQL Server
Триггеры представляют собой объекты базы данных,
связанные с таблицей. Во многом они похожи на хранимые процедуры
и часто упоминаются как "особый вид хранимых процедур". Основное различие между триггером и хранимой процедурой в том, что триггер связан с таблицей и работает только при работе выражения INSERT, UPDATE или DELETE.
Основная работа по ограничениям является соблюдением правил в базе данных, предназначенных для обеспечения целостности данных. Например, у нас есть ограничения внешнего ключа, чтобы убедиться, что все заказы ссылаются на существующую продукцию. Поддержание целостности имеет первостепенное значение для базы данных, т.к. мы не можем доверять пользователям и приложения и быть уверенными, эти правила будут соблюдены. Если целостность нарушается, то возможны ситуации, когда у клиентов будет двойной счет, расчетов с поставщиком не хватает, что приводит к потере уверенности в работе приложения.
Правило определяет допустимые значения, которые могут быть вставлены в этот столбец.
Слайд 46
Создание баз данных
Файлы и файловые группы баз данных
В базе данных
для хранения используются три типа файлов. Это первичные файлы, вторичные
файлы и журналы транзакций. База данных должна содержать первичный файл данных и, по крайней мере, один файл журнала транзакций. При необходимости можно создать один или несколько вторичных файлов данных и дополнительные файлы журналов транзакций.
Первичные файлы. В этих файлах содержится информация запуска базы данных. Также в первичных файлах хранят данные. В каждой базе данных имеется один первичный файл данных. Для имени первичного файла данных рекомендуется использовать расширение MDF.
Вторичные файлы. В этих файлах содержатся все данные, которые не поместились в первичный файл данных. Если первичный файл обладает достаточно большим размером для хранения всей информации базы данных, то в такой базе данных вторичные файлы не используются. Для имени вторичного файла данных рекомендуется использовать расширение NDF.
Журналы транзакций. В этих файлах содержатся данные журнала, которые используются для восстановления базы данных. В каждой базе данных должен быть, как минимум, один файл журнала транзакций, а может быть и несколько таких файлов. Для имен файлов журналов рекомендуется использовать расширение LDF. Минимальный размер файла журнала равен 512 КБ.
Слайд 47
Создание баз данных
Файлы и файловые группы баз данных
Логические и физические
имена файлов
Файлы SQL Server имеют два имени.
logical_file_name — имя, используемое для
ссылки на физический файл во всех инструкциях Transact-SQL. Логическое имя файла должно соответствовать правилам для идентификаторов SQL Server и быть уникальным среди логических имен файлов в соответствующей базе данных.
Имя os_file_name — это имя физического файла, включая путь к каталогу. Оно должно соответствовать правилам для имен файлов операционной системы.
Страницы файлов данных
Страницы файлов данных SQL Server нумеруются последовательно; первая страница файла получает нулевой номер (0). Каждый файл базы данных имеет уникальный цифровой идентификатор. Чтобы уникальным образом определить страницу базы данных, необходимо использовать как идентификатор файла, так и номер этой страницы. В следующем примере показаны номера страниц базы данных, содержащей первичный файл данных объемом в 4 МБ и вторичный файл данных объемом в 1 МБ.
Слайд 48
Создание баз данных
Файлы и файловые группы баз данных
Слайд 49Кроме имен
Дополнительными свойствами файла являются идентификатор, начальный размер, величина приращения
при увеличении и максимальный размер.
Файлы SQL Server могут автоматически увеличиваться
в размерах, превосходя первоначально заданные показатели. При определении файла пользователь может указывать требуемый шаг роста. Каждый раз при заполнении файла его размер увеличивается на указанный шаг роста FILEGROWTH growth_increment. Если в файловой группе имеется несколько файлов, их автоматический рост начинается лишь по заполнении всех файлов.
Эти данные хранятся на странице заголовка файла, которая является первой страницей любого файла данных.
Слайд 50
Для упрощения администрирования и повышения производительности файлы данных можно объединять
в файловые группы - Например, таблицы можно выделить в отдельные
файловые группы.
Распределение объектов базы данных по разным файловым группам позволяет реализовать преимущества разных дисковых подсистем и разрешить SQL Server выполнять параллельные дисковые операции. Кроме того, создав несколько файловых групп, вы сможете выполнять резервное копирование и восстановление файлов независимо друг oт друга.
Слайд 51Файлы журнала транзакций
Каждая БД содержит хотя бы один файл журнала
транзакций.
Журнал транзакций хранит записи обо всех изменениях БД и содержит
информацию необходимую для отмены изменений или их выполнений повторно.
Каждый файл транзакций – отдельный файл операционной системы, используемый только одной БД, и имеет по умолчанию расширение .ldf.
Каждый журнал транзакций имеет логическое имя, используемое в предложениях Transact-SQL, и физическое.
К дополнительным свойствами файла транзакций относятся:
Идентификатор;
Начальный размер;
Приращение, при увеличении размера;
Максимальный размер.
Слайд 52Структура файла транзакций
Файл журнала транзакций содержит последовательность записей.
Каждая запись имеет
порядковый номер в журнале (Log Sequence Number, LSN). Журнал транзакций
БД SQL Server рассматривает как единый журнал, безотносительно в скольких физических файлах он располагается.
Слайд 53Принцип работы журнала транзакций
SQL Server использует буферный кэш – хранящуюся
в оперативной памяти структуру, куда перемещаются страницы данных с диска.
Приложения работают с данными, расположенными в кэше.
Измененная страница в кэше, еще не записанная на диск, называется затронутой страницей (dirty page).
Изменения вносятся вначале в журнал транзакций, а потом на диск. За выполнением данной процедуры следят встроенные механизмы SQL Server.
Запись затронутой страницы из кэша на диск называется сбросом страниц (page flushing).
Запись журнала содержит всю информацию, необходимую для отмены или повторения любого изменения БД.
SQL Server периодически записывает затронутые страницы из кэша на диск. Запись данных производится в результате процесса – контрольная точка,
Если транзакция отменена или не завершена, журнал транзакций гарантирует, что все изменения внесенные незавершенными транзакциями будут отменены.
Слайд 54Процесс контрольной точки
Процесс контрольной точки используется для оптимизации использования буферного
кэша, уменьшения простоя в случае сбоя в работе кэша.
Процесс контрольной
точки выполняется в следующих случаях:
если используется оператор CHECKPOINT
если используется оператор ALTER DATABASE
при корректном завершении работы экземпляра SQL Server
при автоматическом запуске процесса контрольной точки (выполняется периодически в зависимости от числа записей в активной части журнала).
При запуске SQL Server начинается процесс восстановления каждой БД. Проверяются журналы транзакций на наличие незавершенных транзакций
Слайд 55Физический уровень
БД
*.MDF
Контр. точка
Журнал
*.LDF
Insert into Tabl1() Values ()
Слайд 56Модели восстановления
В SQL Server существует три модели восстановления:
простая (Simple model)
отдельных
операций (Full model);
результатов импорта (Bulk-Logged model).
Каждая БД имеет одну из
моделей. Выбор модели влияет на размер журнала транзакций, а также возможности резервного копирования и восстановления данных.
Слайд 57Модель восстановления отдельных операций Full
Данная модель позволяет восстановить БД до
того состояния, в котором она была на момент сбоя или
на любой иной указанный момент времени.
При использовании данной модели в журнал заносятся все операции (в том числе широкомасштабные – такие как, операция создания индекса, копирования данных). Такие операции требуют значительного объема доступного пространства в журнале транзакций.
При выборе такой модели необходимо регулярно и часто выполнять резервное копирование журнала транзакций.
Слайд 58Модель восстановления результатов импорта
При использовании данной модели регистрируются все операции,
кроме широкомасштабных. Хранимой информации о широкомасштабных операциях недостаточно для восстановления
после сбоев.
Данная модель позволяет сократить место для журнала транзакций.
При выборе данной модели необходимо периодически выполнять резервное копирование журнала транзакций.
Слайд 59Простая модель (Simple)
При использовании простой модели восстановления в журнал транзакций
записываются все операции, в том числе и широкомасштабные.
Но для
восстановления данных не используется резервное копирование журнала транзакций, каждый процесс контрольной точки усекает файл журнала. Это позволяет избежать переполнения журнала транзакций.
При использовании простой модели БД может быть восстановлена только до момента, когда была сделана последняя резервная копия.
Системная БД tempbd всегда использует простую модель. Пользовательские БД используют простую модель по умолчанию, однако данный режим можно изменить.
Слайд 60Системные таблицы SQL Server
SQL Server использует системные таблицы для управления
работой СУБД и связанными с ней таблицами.
Системные таблицы делятся на
две группы:
системный каталог;
каталог базы данных.
Изменение или удаление системных таблиц повлечь за собой неполадки в работе SQL Server.
Слайд 61Системный каталог
Системный каталог включает в себя системные таблицы, используемые СУБД
для управления системой.
Системный каталог существует только в БД master.
Системный каталог
содержит метаданные, относящиеся ко всему экземпляру SQL Server и параметры системы.
Слайд 63Каталог базы данных
Каталог базы данных состоит из системных таблиц, используемых
для управления отдельной БД. В каждой БД имеется набор таких
таблиц.
Слайд 64Системные хранимые процедуры
Системные хранимые процедуры – процедуры составленные из операторов
T-SQL, поставляемые вместе с SQL Server.
Системные процедуры работают с
системными таблицами, позволяют получить системную информацию и выполнять задачи администрирования БД.
Слайд 65Использование системных хранимых процедур
Слайд 66Системные функции
Системные функции – набор встроенных функций, позволяющих обращаться к
системным таблицам при помощи операторов T-SQL и получать специфические данные
о значениях параметров, объектах и настройках.
Слайд 68
Создание баз данных
Для создания базы данных необходимо определить имя базы
данных, ее владельца, размер, а также файлы и файловые группы,
которые будут в ней храниться.
При создании базы данных нужно учесть следующие условия:
Для создания базы данных необходимо обладать, как минимум, разрешениями CREATE DATABASE, CREATE ANY DATABASE или ALTER ANY DATABASE.
В SQL Server для файлов данных и журналов каждой базы данных заданы некоторые разрешения. Эти разрешения предотвращают случайное повреждение файлов в случае их сохранения в каталоге с открытыми разрешениями.
Пользователь, создавший базу данных, становится ее владельцем.
В одном экземпляре SQL Server может быть создано до 32 767 баз данных.
Имя базы данных должно соответствовать правилам, определенным для идентификаторов.
Все пользовательские объекты в базе данных model копируются во вновь создаваемые базы данных. Можно добавлять любые объекты, такие как таблицы, представления, хранимые процедуры и типы данных, в базу данных model, чтобы включать их во все создаваемые базы данных.
Слайд 69Задаем параметры БД при создании
Параметры БД определяют различные аспекты работы
с системными или пользовательскими базами данных.
Параметра пользовательских БД при
создании наследуют параметры системной БД model.
Выделяют следующие типы параметров:
управление автоматическим поведением БД (такие как автоматическое создание и обновление данных статистики);
выбор модели восстановления;
совместимость с ANSI;
режим доступа к БД (например только для чтения и доступа, предоставленный только для владельца БД – dbo).
Слайд 70Изменение размера базы данных
После создания БД возникает необходимость в периодическом
изменении ее размеров.
При увеличении размеров БД возрастает нагрузка на систему,
журналы транзакций увеличиваются быстрее.
SQL Server поддерживает ряд механизмов управления изменениями БД: автоматические методы контроля размера БД, ручного изменения размеров файлов БД и журналов транзакций.
Слайд 71Автоматическое увеличение размера файлов
При создании пользовательской БД по умолчанию выбирается
автоматическое увеличение размеров файлов данных и журналов транзакций.
Однако при каждом
увеличении файлов нагрузка на систему возрастает. Кроме того, увеличение дискового пространства для хранения файлов приводит к дефрагментации диска.
Все это обуславливает необходимость контроля за объемом данных и журнала транзакций и отказ от модели автоматического увеличения размеров файлов данных и журнала транзакций.
Слайд 72Изменение параметров автоматического увеличения размера файлов
Изменение параметров автоматического увеличения размеров
файлов данных и журнала транзакций выполняется с помощью графических средств
SQL Server Management Studio.
Другой вариант – использование операторов Transact-SQL. Для отключения, например, автоматического увеличения файла данных БД semdb, необходимо выполнить запрос:
ALTER DATABASE SEMDB
MODIFY FILE ( NAME = 'semdb_data', FILEGROWTH = 0 )
Слайд 73Выполнение некоторых служебных операций с базами данных
Слайд 741. Увеличение размера базы данных
Слайд 75На графическом экране SQL Server Management Studio для этого достаточно
открыть свойства базы данных, перейти на вкладку Files и ввести
новый размер для файла базы данных в столбце Initial Size (Исходный размер) (или добавить в список новый файл).
Слайд 772. Перемещение файлов баз данных SQL Server
Слайд 78 Для выполнения этой операции можно прибегнуть к резервному копированию и
восстановлению, отсоединению и присоединению баз данных, но можно выполнить перемещение
при помощи T-SQL
Слайд 79 Перенос файлов базы данных производится очень просто:
переведите базу данных в
автономный режим, например:
ALTER DATABASE testdb SET OFFLINE; или в контекстном
меню этой базы данных в Object Explorer выберите команду Tasks | Detach (Задачи | Отсоединить)
затем средствами операционной системы перенесите файлы баз данных в другое место;
после этого укажите SQL Server, что файлы базы данных теперь находятся в другом месте:
ALTER DATABASE testdb MODIFY FILE (NAME = testdb, FILENAME = 'D:\testdb1.mdf');
верните базу данных в обычный режим:
ALTER DATABASE testdb SET ONLINE;
Слайд 812. Скопируйте
Скопируйте файлы базы данных и журнал транзакций в нужное
вам место обычными средствами Windows.
Слайд 834. Присоедините скопированную БД к новому серверу
Слайд 86 Удалить базу данных в SQL Server можно двумя способами:
из контекстного
меню базы данных в SQL Server Management Studio по команде
Delete (Удалить);
при помощи команды DROP DATABASE, например:
DROP DATABASE testdb;
В любом случае удаляемая база данных не должна быть открыта ни пользователями, ни служебными процессами, такими как репликация.
При удалении базы данных SQL Server одновременно удаляются файлы этой базы данных на диске (если она не находилась в автономном режиме).
Слайд 88Цель резервного копирования
для администратора:
копия базы данных (которую можно восстановить)
на случай сбоя
Слайд 89Устройства резервного копирования
— это специальные объекты, которые хранятся в базе
данных master. Их единственное назначение — хранить информацию о пути к
физическому файлу в операционной системе или о стриммере. Создать такое устройство можно:
на графическом интерфейсе — из контейнера Server Objects | Backup Devices (Объекты сервера | Устройства резервного копирования) в Management Studio;
Слайд 93База данных - Database
указывает базу данных, для которой необходимо
создать резервную копию.
Слайд 94Модель восстановления - Recovery model
определяет, в течение какого времени
ведется запись журнала транзакций и какая информация в нем фиксируется.
Устанавливается заранее в Свойствах БД.
■ Full
■ Bulk-Logged
■ Simple
Слайд 95Тип резервного копирования Backup type
Full
Differential
Transaction Log
………………
Для
полного или разностного резервного копирования используется команда BACKUP DATABASE (для
разностного еще указывается параметр WITH DIFFERENTIAL), для резервного копирования журнала транзакций — команда BACKUP LOG
Слайд 96полное резервное копирование
В резервную копию записываются все данные, которые есть
в базе данных.
Пустые страницы при этом не копируются, поэтому
если, например, файлы вашей базы данных имеют размер в 1 Гбайт, а реально данные в них занимают всего лишь 200 Мбайт, то получится резервная копия размером 200 Мбайт.
Слайд 97разностный differential
В этом случае на резервную копию записываются все изменения,
которые были произведены с момента последнего полного резервного копирования.
Слайд 98резервное копирование журналов транзакций (transaction log)
Если вы используете режим восстановления
Full или Bulk-logged, то выполнение такого резервного копирования практически обязательно.
Причина проста: если вы не будете производить резервное копирование журналов транзакций, то не будет производиться и их очистка. В результате место в файлах журналов транзакций может закончиться (а если для них установлен неограниченный размер, то закончится и место на диске).
Слайд 99В качестве дополнительного типа резервного копирования можно рассматривать резервное копирование
файлов (file backup) и файловых групп (filegroup backup).
Слайд 100Компонент, подлежащего резервному копированию Backup component
всей базы данных
Файлы
и группы файлов. Данная опция позволяет выбрать только один файл
резервной копии.
Слайд 101 Корпорация Microsoft считает идеальным вариантом такое расписание резервного копирования (такой
вывод можно сделать из официальных учебных курсов и сертификационных экзаменов):
раз
в неделю — полное резервное копирование;
раз в сутки (каждую ночь) — разностное резервное копирование;
несколько раз в день — резервное копирование журналов транзакций.
Этот вариант наилучшим образом подходит для больших баз данных (десятки и сотни гигабайт), которые активно изменяются.
Слайд 102SQL-операторы для резервного копирования
Полное
Разностное
Журнал транзакций
Слайд 104Обеспечение безопасности SQL Server можно представить как последовательность шагов в
следующих областях: платформа, объекты (в том числе данные) и приложения,
которые обращаются к системе.
Слайд 1051 Безопасность платформы и сети
Платформа для SQL Server включает в
себя физическое оборудование и сетевые компьютеры, с помощью которых клиенты
соединяются с серверами базы данных, а также двоичные файлы, применяемые для обработки запросов базы данных.
Физическая безопасность
Безопасность операционной системы
Слайд 1061 а Физическая безопасность
Рекомендуется строго ограничивать доступ к физическим серверам
и компонентам оборудования. Например, оборудование сервера базы данных и сетевые устройства
должны находиться в закрытых охраняемых помещениях. Доступ к резервным носителям также следует ограничить. Для этого их рекомендуется хранить в отдельных охраняемых помещениях.
Слайд 1071 б Безопасность операционной системы
В состав пакетов обновления и отдельных
обновлений для операционной системы входят важные дополнения, позволяющие усилить безопасность.
Кроме того, эффективную безопасность можно реализовать с помощью брандмауэров. Использование брандмауэра повышает безопасность на уровне операционной системы, обеспечивая узкую область, на которой можно сосредоточить меры безопасности.
Слайд 1082 Безопасность участников и объектов БД
Участники — это отдельные пользователи,
группы и процессы, которым предоставлен доступ к ресурсам SQL Server. Защищаемые
объекты — это сервер, база данных и объекты, которые содержит база данных. У каждого из них существует набор разрешений, с помощью которых можно уменьшить контактную зону SQL Server.
Слайд 109Шифрование и сертификаты
Шифрование не решает проблемы управления доступом. Однако оно повышает
безопасность, ограничивая потерю данных даже в тех редких случаях, когда
средства управления доступом удается обойти. Например, если главный компьютер, на котором установлена база данных, был настроен неправильно, и злонамеренный пользователь смог получить конфиденциальные данные (например, номера кредитных карточек), то украденная информация будет бесполезна, если она была предварительно зашифрована.
Слайд 1103 Безопасность приложений
Безопасность в SQL Server, помимо прочего, обеспечивается разработкой
защищенных клиентских приложений на сетевом уровне
Слайд 111Конфигурирование участников системы безопасности сервера
Рассмотрим подробнее шаг № 2 …
Слайд 112Конфигурирование защищаемых объектов БД
Слайд 113Участники безопасности (Principal )
это те объекты, которым в SQL Server
можно предоставлять разрешения. Они могут быть как индивидуальными (например, учетная
запись), так и групповыми (например, роль).
Участник безопасности управляется на трех уровнях: Windows, SQL Server и БД
Слайд 114Примеры участников безопасности
логин для учетной записи Windows,
логин SQL
Server
Пользователь БД,
Роль БД,
Слайд 115Защищаемые объекты
(Securable )
Это все, на что в SQL Server
можно назначить разрешения.
Слайд 117Разрешения (Permission)
Разрешения — это права пользователя на проведение тех
или иных действий на сервере или в базе данных. Пользователи
должны иметь соответствующие разрешения, прежде чем они смогут выполнить любое действие, связанное с изменением структуры БД или доступом к данным
Слайд 118В большинстве случаев процесс предоставления разрешений выглядит так:
1. Создать логин —
учетную запись для подключения к SQL Server.
2. Затем создать пользователя
базы данных, которому соответствует этот логин.
3. Предоставить пользователю необходимые разрешения.
Слайд 120Прежде, чем создавать логин, необходимо понять, какой тип вы будете
использовать для этого логина:
логин Windows (для входа на сервер используется
уч. запись Windows)
логин SQL Server. (для входа на сервер вводится свое имя и пароль)
Слайд 121Логины любого типа создаются одинаково:
при помощи графического интерфейса — из окна
Login — New (Новый логин). Это окно открывается с помощью команды
New Login контекстного меню контейнера Security | Logins (Безопасность | Логины) в Object Explorer в SQL Server Management Studio;
из скрипта — при помощи команды CREATE LOGIN.
Например, команда на создание логина SQL Server с именем User1 и паролем P@ssw0rd (для всех остальных параметров будут приняты значения по умолчанию) может выглядеть так:
CREATE LOGIN User1 WITH PASSWORD = 'P@ssw0rd';
Слайд 122Встроенные логины SQL Server
Сразу же после установки SQL Server 2005
в контейнере Logins появляется набор логинов, которые создаются автоматически:
sa (от
System Administrator) — это единственный логин типа SQL Server, который создается по умолчанию. Он обладает правами системного администратора SQL Server.
Слайд 123Вы обеспечили пользователям возможность входа на SQL Server , создав для
них логины. Но сам по себе вход на сервер ничего
не дает: пользователю нужны также права на выполнение определенных действий. Обычно для этой цели создаются пользователи или роли баз данных и им предоставляются разрешения
Слайд 124Роли
Роли позволяют легко назначать разрешения группе пользователей и могут иметь
ассоциированный набор встроенных (неявных) разрешений, не подлежащих изменению.
Доступны два
типа ролей:
роли сервера - применяются на уровне сервера;
роли БД — применяются на уровне базы данных.
Слайд 125Роли сервера
Используются для предоставления возможности администрирования сервера.
Если включить учетную
запись в какую-либо роль, то пользователи, использующие эту учетную запись,
смогут производить любые действия, разрешенные для этой роли. Например, пользователи роли sysadmin имеют наивысший в SQL Server уровень прав и могут выполнить любые задачи
Слайд 126Роли уровня БД
Используются если требуется назначать разрешения на базу данных
и ее объекты. Эти роли определяются для каждой базы данных
отдельно, поэтому каждая БД имеет собственный набор ролей.
SQL Server на уровне баз данных поддерживает три типа ролей:
стандартные пользовательские роли БД;
пользовательские роли приложений;
предопределенные (встроенные) роли БД.
Слайд 127 На графическом экране работа с ролями сервера производится или из
свойств логина (вкладка Server Roles (Серверные роли)), или из свойств
самой серверной роли (контейнер Server Roles в Management Studio).
Из кода Transact-SQL для назначения логину серверной роли можно использовать хранимую процедуру sp_addsrvrolemember. Например, чтобы предоставить пользователю User4 права роли SYSADMIN, соответствующий код может быть таким:
EXEC sp_addsrvrolemember @loginame = 'user4', @rolename = 'sysadmin';
Слайд 128для предоставления прав на уровне всего сервера необязательно использовать серверные
роли. Вы вполне можете предоставить эти права напрямую логину (при
помощи вкладки Permissions (Разрешения) свойств SQL Server).
Слайд 129Вкладка Permissions свойств SQL Server
Слайд 130Серверные роли - пример
PUBLIC — эту роль вы не найдете
в списке серверных ролей. Тем не менее, она существует и
активно используется. Права этой роли автоматически получают все, кто подключился к SQL Server, и лишить пользователя членства в этой роли нельзя. Обычно эта роль используется для предоставления разрешений всем пользователям данного сервера;
SYSADMIN — логин, которому назначена эта роль, получает полные права на весь SQL Server (и возможность передавать эти права другим пользователям). С точки зрения серверных разрешений, это соответствует праву CONTROL SERVER с параметром WITH GRANT (т. е. с возможностью передачи);
Слайд 132После создания логинов следующая задача администратора — спуститься на уровень базы
данных и создать объекты пользователей базы данных. Пользователи баз данных —
это специальные объекты, которые создаются на уровне базы данных и используются для предоставления разрешений в базе данных (на таблицы, представления, хранимые процедуры). Для пользователей используется термин database users (или просто users),
Слайд 133Создать пользователя базы данных можно:
на графическом экране из контейнера Имя_базы_данных |
Security | Users в Management Studio;
при помощи команды CREATE USER
Например, команда
на создание пользователя User1, которому будет соответствовать логин SQL Server Login1 со схемой по умолчанию dbo, может выглядеть так:
CREATE USER User1 FOR LOGIN Login1 WITH DEFAULT_SCHEMA = dbo;
Слайд 134Схема
Схема формально определяется как набор объектов в базе данных,
объединенных общим пространством имен. Проще всего представить себе схему как
некий логический контейнер в базе данных, которому могут принадлежать таблицы, представления, хранимые процедуры, пользовательские функции, ограничения целостности, пользовательские типы данных и другие объекты базы данных. Этот контейнер удобно использовать как для именования объектов и их логической группировки, так и для предоставления разрешений.
Слайд 135Встроенные пользователи базы данных
При создании любой базы данных в ней
автоматически создаются специальные пользователи:
dbo (от database owner) — пользователь-владелец базы данных.
Он автоматически создается для того логина, от имени которого была создана эта база данных. Конечно же, как владелец, он получает полные права на свою базу данных;
….
Слайд 137Следующее действие после создания пользователей — предоставление разрешений на объекты
базы данных
Слайд 138Разрешения (продолжение)
разрешения начинаются с ключевого слова или слов, определяющих предоставляемые
права:
ALTER, CREATE, DELETE, SELECT, и.т.д.
Слайд 139Передача разрешений
SQL Server 2005 оперирует правами доступа трех типов:
GRANT —
даст право доступа к объекту;
DENY —запретить что-то делать.
REVOKE — отменить
явно предоставленное разрешение или запрет;
Слайд 140Работа с разрешениями производится одинаково для всех объектов базы данных:
на
вкладке Permissions свойств этого объекта;
при помощи команд SQL - GRANT,
DENY, REVOKE (пример на след. слайде)
Слайд 141Например, чтобы предоставить пользователю User1 возможность просматривать данные в таблице
Table1 в схеме dbo, можно воспользоваться командой:
GRANT SELECT ON dbo.Table1
TO User1;
Лишить его ранее предоставленного права можно при помощи команды:
REVOKE SELECT ON dbo.Table1 TO User1;
Слайд 1451 шаг
CREATE LOGIN [UL\Ivan]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks
Слайд 1462 шаг
USE Adventureworks
CREATE USER Ivan FOR LOGIN (UL\Ivan)
Слайд 1473 шаг
CREATE TABLE Products (productlD INT, productNAME nvarchar(50))
GRANT SELECT
ON Products To Ivan
GO
Слайд 1484 шаг
CREATE SCHEMA stockSchema
CREATE TABLE stockSchema.stock (roomiD INT, productID INT,
quantity INT) GRANT SELECT ON Stock TO Ivan
GO
Слайд 1495 шаг
ALTER USER Ivan
WITH DEFAULT_SCHEMA = stockSchema
Слайд 1506 шаг
INSERT INTO dbo.Products VALUES (1, 'Pentium 4 Processors')
INSERT
INTO stockSchema.stock VALUES (1,1,11)
Слайд 151Проверка
Select * from Products
Select * from stock
![1 шагCREATE LOGIN [UL\Ivan] FROM WINDOWSWITH DEFAULT_DATABASE = AdventureWorks](/img/thumbs/5faaaa73ccb05c082fb05ef3eb258e09-800x.jpg "Цели и задачи дисциплины 1 шагCREATE LOGIN [UL\Ivan] FROM WINDOWSWITH DEFAULT_DATABASE = AdventureWorks")
