Цели и задачи дисциплины Цель освоение технологий

Цели и задачи дисциплины Цель – освоение технологий хранения и анализа корпоративных данных в системах клиент-серверной архитектуры. Изучение программных средств разработки и администрирования многопользовательских баз данных и корпоративных хранилищ для решения экономических задач. Задачи: • способствовать освоению клиент-серверные технологии СУБД; • способствовать освоению СУБД, позволяющие создавать информационные системы, имеющие архитектуру клиент-сервер; • способствовать освоению структурированного языка запросов SQL, приемов и особенностей разработки запросов и процедур обработки данных на SQL; • способствовать получению базового уровня в проектировании и разработке баз данных в системе СУБД MS SQL Server; • способствовать приобретению практических навыков по созданию клиентского приложения и работу из него с базой данных сервера. 1

В результате изучения дисциплины студент должен • • • Знать: основные концепции СУБД клиент-серверной архитектуры, типовые задачи, выполняемые при создании серверных баз данных и их администрировании основные функции и типовую организацию современных СУБД; структурированный язык запросов; программные интерфейсы подключения клиентского приложения к базе данных. Уметь: выполнять логическое проектирование учебной базы данных; создавать учебную базу данных средствами MS SQL Server; разрабатывать запросы к базе данных и процедуры обработки данных с целью получения данных, необходимых при решении экономических задач; разрабатывать клиентские приложения для работы с учебной базой данных, созданной средствами MS SQL Server; проектировать и создавать базы данных и приложения пользователя в клиент-серверной архитектуре; эффективно выполнять задачи их администрирования. Владеть: языком SQL для создания и администрирования многопользовательских баз данных и объектов серверной бизнеслогики; навыками проектирования и создания приложений в клиентсерверной архитектуре. 2

Наиболее популярные серверные СУБД Производитель Url Oracle Corp. http: //www. oracle. com/ Microsoft SQL Server Microsoft http: //www. microsoft. co m/ Informix http: //www. informix. com/ Sybase http: //www. sybase. com/ DB 2 IBM http: //www-4. ibm. com/ 3

• Microsoft® SQL Server™ — это система анализа и управления реляционными базами данных в решениях электронной коммерции, производственных отраслей и хранилищ данных

Microsoft SQL Server: история Началом истории Microsoft SQL Server по праву можно считать 1986 год, когда Microsoft и Sybase выпустили совместную версию продукта — SQL Server 1. 0 и адаптировали ее для операционной системы OS/2 при поддержке компании Ashton Tate, которая в то время была лидером на рынке СУБД для персональных компьютеров. Выпущенный в 1989 году продукт не получил должного признания из -за проблем, связ В 1990 году Sybase и Microsoft прервали соглашение с Ashton Tate и выпустили версию 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 прервали совместное пятилетнее соглашение и бывшие партнеры занялись самостоятельным развитием своих, теперь уже 5 конкурирующих продуктов.

Microsoft SQL Server: история В 1995 и 1996 годах увидели свет версии SQL Serve проблемы с производительностью и управляемостью не позволили этим продуктам завоевать существенную долю рынка корпоративных СУБД. Было принято решение приостановить развитие текущей версии платформы и начать создание продукта «с нуля» . Примерно в то же время компания DEC продала свою систему управления базами данных компании Oracle и Microsoft удалось заполучить ведущих специалистов компании DEC — Джима Грея (Jim Gray), Дэйва Ломета (Dave Lomet) и Фила Бернштейна (Phil Bernstein). Команде разработчиков была поставлена задача — создать новое ядро базы данных с поддержкой масштабируемости, новый процессор обработки запросов, систему самонастройки, самоуправления, а также реализовать поддержку OLAP и ETL с привлечением специалистов из компании Panorama. Разработка новой СУБД заняла около трех лет и в 1998 году был выпущен продукт под названием SQL Server 7. 0 — Microsoft начала завоевывать не только рынок реляционных СУБД, но и такие новые рынки, как business intelligence и data warehousing. 6

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 - полностью готовая к использованию в облаке информационная платформа, предоставляющая уверенность в надёжности критически важных для бизнеса систем при работе с данными и характеризующаяся повышенной доступностью, высокой производительностью и улучшенными функциональными возможностями в части безопасности для критически важных рабочих нагрузок. • 7

Microsoft SQL Server: история SQL Server 2012 доступен в редакциях (выпусках): Standard - базовые возможности по управлению данными и бизнес-аналитике с некритичными для бизнеса нагрузками и с минимальными затратами. Business Intelligence - новый выпуск, который предоставляет расширенную корпоративную и персональную бизнес-аналитику Enterprise для критически важных приложений и больших хранилищ данных Web Edition предназначен для размещения веб-сайтов и дополнительных вебуслуг, который по доступной цене обеспечивает масштабируемость и функции управления для небольших и крупномасштабных веб-проектов. Developer Edition включает все функциональные возможности выпуска Enterprise Edition, однако лицензируется как система для разработки и тестирования, а не для применения в качестве рабочего сервера Express является бесплатной базой данных начального уровня и идеально подходит для обучения, а также для создания управляемых данными приложений, работающих на рабочих станциях и небольших серверах. Этот выпуск — лучший выбор для независимых поставщиков программного обеспечения, непрофессиональных разработчиков и любителей, создающих клиентские приложения. 8

Редакции SQL Server Enterprise Editi SQL Business Intelligence SQL Server Standard Edition SQL Web Edition SQL Server Developer Edition SQL Server Express Edition

Microsoft SQL Server: история В следующей таблице более подробно описаны серверные выпуски SQL Server Термин Определение Enterprise (x 86, x 64 и Выпуск SQL Server Enterprise является IA 64)1 многофункциональной платформой данных, предоставляющей масштабируемость уровня предприятия, производительность, высокий уровень доступности и расширенные возможности бизнесаналитики для работы защищенных ответственных приложений. Standard (x 86 и x 64) SQL Server Standard — завершенная платформа данных, простая в использовании и управлении. Выпуск включает встроенные возможности бизнес-аналитики для работы с приложениями уровня отдела. Выпуск SQL Server Standard for Small Business обладает всеми техническими компонентами и возможностями выпуска SQL Server Standard и лицензирован для использования в компаниях малого бизнеса, число компьютеров в которых не превышает 75. 10

Специализированные выпуски предназначены для особых пользовательских сред. • Аргумент Определение SQL Server 2008 Developer Выпуск SQL Server 2008 Developer позволяет разработчикам строить приложения любого (x 86, x 64 и IA 64) вида на базе SQL Server. Этот выпуск включает все функциональные возможности выпуска SQL Server 2008 Enterprise, однако он лицензируется для разработки и тестирования системы, а не для применения в качестве рабочего сервера. Выпуск SQL. Web (x 86, x 64) SQL Server 2008 Web — это вариант с низкой общей стоимостью владения, предназначенный для размещения веб-узлов, который обеспечивает масштабируемость и функции управления для небольших и крупномасштабных веб-свойств. SQL Server Express (x 86 и x 64) SQL Server Express with Tools (x 86 и x 64) SQL Server Express with Advanced Services (x 86 и x 64) Платформа баз данных SQL Server Express основана на SQL Server 2008. Она также является заменой для Microsoft Desktop Engine (выпуск MSDE). Благодаря интеграции со средой Visual Studio, выпуск SQL Server Express облегчает разработку управляемых данными приложений, обладающих богатыми возможностями, обеспечивающими безопасность хранения данных и не требующими много времени для развертывания. SQL Server Express — бесплатный выпуск, который могут распространять независимые поставщики ПО (при соблюдении соответствующего соглашения). Выпуск SQL Server Express идеально подходит для обучения, а также для построения настольных или небольших серверных приложений. Этот выпуск — лучший выбор для независимых поставщиков программного обеспечения, непрофессиональных разработчиков и любителей, которые хотят создавать клиентские приложения. Если необходимы дополнительные функции базы данных, SQL Server Express можно с легкостью обновить до расширенных версий SQL Server. Compact 3. 5 с пакетом обновления 1 (x 86) Compact 3. 1 (x 86) SQL Server Compact 3. 5 — бесплатная внедренная база данных, которая идеально подходит для построения изолированных и иногда сетевых приложений для мобильных устройств, рабочих станций и веб-клиентов, работающих на всех платформах Windows. 11

• SQL Server 2012 SP 1 • При разработке каждой версии SQL Server корпорация Microsoft использует внутреннее версионирование для контроля за выпускаемыми версиями продукта, например 7. 00. 699, где первое число соответствует поколению продукта, второе число — резервное, используется когда изменений в новой версии продукта для присваивания очередной версии поколения недостаточно, но данная версия выпускается как самостоятельный продукт, а не дополнение к имеющемуся. Третье число используется для указания версии основного исполняемого файла продукта • Определение версии и выпуска • Select @@version

Компоненты сервера

Компоненты SQL Server

Компоненты SQL Server Основным компонентом является SQL Server Database Engine, в состав которого входит компонент Database Engine, это служба для хранения, обработки и обеспечения безопасности данных, репликации, полнотекстового поиска и средств управления реляционными и XML-данными. Службы Analysis Services содержат средства создания и управления приложениями интерактивной аналитической обработки (OLAP) и приложениями интеллектуального анализа данных. Службы Reporting Services включают в себя серверные и клиентские компоненты для создания, управления и развертывания табличных, матричных и графических отчетов, а также отчетов в свободной форме, службы можно использовать для разработки приложений отчетов. Службы Integration Services представляют собой набор графических средств и программируемых объектов для перемещения, копирования и преобразования данных. 15

Компоненты SQL Server Full-Text Search (полнотекстовый поиск) содержит функциональность, необходимую для выполнения полнотекстовых запросов к простым символьным данным в таблицах SQL Server. Полнотекстовые запросы могут включать слова и фразы, несколько форм слова или фразы. Полнотекстовый поиск позволяет быстро и гибко индексировать текстовые данные, хранящихся в базе данных Microsoft SQL Server для поискового запроса. Replication (репликация) – представляет собой набор технологий копирования и распространения данных и объектов между базами данных, а также синхронизации баз данных для поддержания согласованности. Используя репликацию, можно распространять данные в различные расположения, а также удаленным или мобильным пользователям по локальным или глобальным сетям посредством коммутируемого соединения, по беспроводным соединениям и через Интернет. 16

Платформа данных MS SQL Server включает следующие инструменты: • Relational Database Engine – безопасное, надёжное, масштабируемое ядро с улучшенной производительностью и поддержкой структурированных и неструктурированных (XML) данных.

Платформа данных MS SQL Server включает следующие инструменты (продолжение): • Integration Services - набор графических средств и программируемых объектов для перемещения, копирования и преобразования данных. • Analysis Services - службы для анализа данных, поддерживают OLAP - аналитическая обработка в реальном времени (OLAP) для быстрого, сложного анализа больших и смешанных наборов данных, использующая многомерное хранение. • Reporting Services - службы для создания и публикации отчетов - исчерпывающее решение для создания, управления и доставки как традиционных бумажных отчётов, так и интерактивных, основанных на технологии WWW отчётов.

Analysis Services • Analysis Services предоставляют единообразное и интегрированное представление всех бизнес данных, как основы для всей традиционной отчётности, OLAP анализа, и Data Mining. • UDM является центральным хранилищем метаданных, определяющим бизнес сущности, бизнес логику, вычисления, и метрики, служащее источником для всех отчётов, электронных таблиц, программ просмотра OLAP и аналитических приложений.

Reporting Services • Службы Reporting Services включают в себя серверные и клиентские компоненты для создания, управления и развертывания табличных, матричных и графических отчетов, а также отчетов в свободной форме. Службы Reporting Services являются расширяемой платформой, которую можно использовать для разработки приложений отчетов.

Построитель Отчетов

Средства управления

Компоненты SQL Server Средства управления SQL Server 1. Среда SQL Server Management Studio представляет собой интегрированную среду для доступа, настройки, управления, администрирования и разработки компонентов SQL Server. Среда Management Studio позволяет работать с SQL Server разработчикам и администраторам любого уровня подготовки. 2. Диспетчер конфигурации SQL Server обеспечивает базовые возможности управления конфигурациями для служб, серверных протоколов, клиентских протоколов и псевдонимов клиентов SQL Server. 3. SQL Server Profiler предоставляет графический пользовательский интерфейс для наблюдения за экземпляром компонента Database Engine или служб Analysis Services. 4. Среда Business Intelligence Development Studio представляет собой интегрированную среду разработки для решений служб Analysis Services, Reporting Services и Integration Services. 23

MS SQL Server Management Studio

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

SQL Server Configuration Manager — • средство администрирования SQL Server. • Запускается оно Пуск | Программы | Microsoft SQL Server | Configuration Tools | SQL Server Configuration Manager.

SQL Server Configuration Manager

SQL 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.

SQL Server Configuration Manager Службы SQL Server • SQL Server реализован в виде набора служб. • Службы – это специальные программы, которые работают от имени своей собственной учетной записи. Службы запускаются независимо от того, вошел ли пользователь в систему. • Для каждой службы создаются специальные записи в разделе реестра HKEY_LOCAL_MACHINESystemCurrent. Control. Set Services.

Псевдонимы • Обычно псевдоним нужен тогда, когда в клиентском приложении жестко прописано имя сервера, к которому это приложение должно обращаться, а база данных перенесена на сервер с другим именем. В этом случае проще всего создать псевдоним на клиенте, при помощи которого клиент, обращаясь по старому имени, будет перенаправляться на новый сервер. Другая ситуация, когда вам может потребоваться псевдоним, — когда вы обращаетесь на SQL Server по нестандартному порту. • Псевдонимы, которые настраиваются средствами SQL Server Configuration Manager,

Задание • Настройте на вашем компьютере псевдоним My. Server. При обращении к этому псевдониму должно производиться подключение к серверу имя_вашего_компьютераSQL

подключаемся с именем My. Server

Принципы создания баз данных 38

Объекты базы данных SQL Server Создание клиент-серверного приложения, работающего с базой данных, требует прохождения следующих этапов: q 1 – разработка структуры реляционной базы данных; q 2 – администрирование базы данных на стороне сервера; q 3 – программирование на стороне сервера; q 4 – программирование на стороне клиента. Разработка клиент-серверной информационной системы начинается с разработки базы данных на стороне сервера и настройки серверной платформы. Здесь можно выделить следующие задачи: q 1 – создание базы данных и установка ее свойств; q 2 - разработка таблиц; q 3 – установление отношений между таблицами и обеспечение целостности данных; q 4 – программирование на стороне сервера, написание триггеров и транзакций; q 5 - ввод первоначальных данных. 39

• MS SQL Server хранит данные в базах данных. На физическом уровне БД состоит из двух или более файлов. Физическая структура видна администраторам БД. • На логическом уровне БД состоит из следующих компонентов – таблиц, представлений и хранимых процедур.

БД на логическом уровне 41

Объекты базы данных SQL Server Таблицы - являются основной формой для сбора информации, содержат все данные в базах данных SQL Server. Каждая таблица представляет собой тип объекта, который имеет смысл для пользователей. Индекс – структура на диске, связанная с таблицей или представлением, которая ускоряет поиск строк таблицы или представления. Индекс содержит ключи, созданные для одного или нескольких столбцов таблицы или представления. Эти ключи хранятся в виде b-древовидной структуры, что позволяет SQL server, быстро и эффективно находить строку или строки, связанные с ключевыми индексами. Существуют кластерные и не кластерные индексы. Представление - виртуальная таблица или хранимый запрос. Данные доступные через представление хранятся в базе данных не как отдельный объект, а как выражение SELECT, в результате SELECT формирует виртуальную таблицу, возвращая представление. Пользователь может использовать эту виртуальную таблицу, ссылаясь на представление в transact-sql (T-SQL) так же, как на таблицы ссылок. 42

Объекты базы данных SQL Server Хранимые процедуры в Microsoft SQL Server, аналогичны процедурам в языках программирования относительно их действий: • принимают входные параметры для вызова процедуры или пакета и возвращать несколько значений в виде выходных параметров, • содержат выражения программирования, которые выполняют операции в базе данных, включая вызов других процедур, • возвращают значение состояния вызывающей процедуре или пакету, чтобы указать, выполнение или не выполнение (и причины отказа). Для запуска хранимой процедуры используют выражение EXECUTE языка Transact-SQL. Хранимые процедуры отличаются от функций тем, что могут не возвращать значения в место их вызова и поэтому и они не могут быть использованы непосредственно в выражениях. 43

Объекты базы данных SQL Server Триггеры представляют собой объекты базы данных, связанные с таблицей. Во многом они похожи на хранимые процедуры и часто упоминаются как "особый вид хранимых процедур". Основное различие между триггером и хранимой процедурой в том, что триггер связан с таблицей и работает только при работе выражения INSERT, UPDATE или DELETE. Основная работа по ограничениям является соблюдением правил в базе данных, предназначенных для обеспечения целостности данных. Например, у нас есть ограничения внешнего ключа, чтобы убедиться, что все заказы ссылаются на существующую продукцию. Поддержание целостности имеет первостепенное значение для базы данных, т. к. мы не можем доверять пользователям и приложения и быть уверенными, эти правила будут соблюдены. Если целостность нарушается, то возможны ситуации, когда у клиентов будет двойной счет, расчетов с поставщиком не хватает, что приводит к потере уверенности в работе приложения. Правило определяет допустимые значения, которые могут быть вставлены в этот столбец. 44

БД на физическом уровне 45

Создание баз данных Файлы и файловые группы баз данных В базе данных для хранения используются три типа файлов. Это первичные файлы, вторичные файлы и журналы транзакций. База данных должна содержать первичный файл данных и, по крайней мере, один файл журнала транзакций. При необходимости можно создать один или несколько вторичных файлов данных и дополнительные файлы журналов транзакций. q Первичные файлы. В этих файлах содержится информация запуска базы данных Также в первичных файлах хранят данные. В каждой базе данных имеется один первичный файл данных. Для имени первичного файла данных рекомендуется использовать расширение MDF. q Вторичные файлы. В этих файлах содержатся все данные, которые не поместились в первичный файл данных. Если первичный файл обладает достаточно большим размером для хранения всей информации базы данных, то в такой базе данных вторичные файлы не используются. Для имени вторичного файла данных рекомендуется использовать расширение NDF. q Журналы транзакций. В этих файлах содержатся данные журнала, которые используются для восстановления базы данных. В каждой базе данных должен быть, как минимум, один файл журнала транзакций, а может быть и несколько таких файлов. Для имен файлов журналов рекомендуется использовать 46 расширение LDF. Минимальный размер файла журнала равен 512 КБ.

Создание баз данных Файлы и файловые группы баз данных Логические и физические имена файлов Файлы SQL Server имеют два имени. logical_file_name — имя, используемое для ссылки на физический файл во всех инструкциях Transact-SQL. Логическое имя файла должно соответствовать правилам для идентификаторов SQL Server и быть уникальным среди логических имен файлов в соответствующей базе данных. Имя os_file_name — это имя физического файла, включая путь к каталогу. Оно должно соответствовать правилам для имен файлов операционной системы. Страницы файлов данных SQL Server нумеруются последовательно; первая страница файла получает нулевой номер (0). Каждый файл базы данных имеет уникальный цифровой идентификатор. Чтобы уникальным образом определить страницу базы данных, необходимо использовать как идентификатор файла, так и номер этой страницы. В следующем примере показаны номера страниц базы данных, содержащей первичный файл данных объемом в 4 МБ и вторичный файл данных объемом в 1 МБ. 47

Создание баз данных Файлы и файловые группы баз данных 48

Кроме имен • Дополнительными свойствами файла являются идентификатор, начальный размер, величина приращения при увеличении и максимальный размер. Файлы SQL Server могут автоматически увеличиваться в размерах, превосходя первоначально заданные показатели. При определении файла пользователь может указывать требуемый шаг роста. Каждый раз при заполнении файла его размер увеличивается на указанный шаг роста FILEGROWTH growth_increment. Если в файловой группе имеется несколько файлов, их автоматический рост начинается лишь по заполнении всех файлов. • Эти данные хранятся на странице заголовка файла, которая является первой страницей любого файла данных.

• Для упрощения администрирования и повышения производительности файлы данных можно объединять в файловые группы - Например, таблицы можно выделить в отдельные файловые группы. • Распределение объектов базы данных по разным файловым группам позволяет реализовать преимущества разных дисковых подсистем и разрешить SQL Server выполнять параллельные дисковые операции. Кроме того, создав несколько файловых групп, вы сможете выполнять резервное копирование и восстановление файлов независимо друг oт друга.

Файлы журнала транзакций • Каждая БД содержит хотя бы один файл журнала транзакций. • Журнал транзакций хранит записи обо всех изменениях БД и содержит информацию необходимую для отмены изменений или их выполнений повторно. • Каждый файл транзакций – отдельный файл операционной системы, используемый только одной БД, и имеет по умолчанию расширение. ldf. • Каждый журнал транзакций имеет логическое имя, используемое в предложениях Transact-SQL, и физическое. • К дополнительным свойствами файла транзакций относятся: o o Идентификатор; Начальный размер; Приращение, при увеличении размера; Максимальный размер.

Структура файла транзакций • Файл журнала транзакций содержит последовательность записей. • Каждая запись имеет порядковый номер в журнале (Log Sequence Number, LSN). Журнал транзакций БД SQL Server рассматривает как единый журнал, безотносительно в скольких физических файлах он располагается.

Принцип работы журнала транзакций • • SQL Server использует буферный кэш – хранящуюся в оперативной памяти структуру, куда перемещаются страницы данных с диска. Приложения работают с данными, расположенными в кэше. Измененная страница в кэше, еще не записанная на диск, называется затронутой страницей (dirty page). Изменения вносятся вначале в журнал транзакций, а потом на диск. За выполнением данной процедуры следят встроенные механизмы SQL Server. Запись затронутой страницы из кэша на диск называется сбросом страниц (page flushing). Запись журнала содержит всю информацию, необходимую для отмены или повторения любого изменения БД. SQL Server периодически записывает затронутые страницы из кэша на диск. Запись данных производится в результате процесса – контрольная точка, Если транзакция отменена или не завершена, журнал транзакций гарантирует, что все изменения внесенные незавершенными транзакциями будут отменены.

Процесс контрольной точки • Процесс контрольной точки используется для оптимизации использования буферного кэша, уменьшения простоя в случае сбоя в работе кэша. • Процесс контрольной точки выполняется в следующих случаях: o o если используется оператор CHECKPOINT если используется оператор ALTER DATABASE при корректном завершении работы экземпляра SQL Server при автоматическом запуске процесса контрольной точки (выполняется периодически в зависимости от числа записей в активной части журнала). • При запуске SQL Server начинается процесс восстановления каждой БД. Проверяются журналы транзакций на наличие незавершенных транзакций

Физический уровень Журнал *. LDF БД *. MDF Контр. точка Insert into Tabl 1() Values () 1 Модель1 Ля-ля

Модели восстановления • В SQL Server существует три модели восстановления: o простая (Simple model) o отдельных операций (Full model); o результатов импорта (Bulk-Logged model). • Каждая БД имеет одну из моделей. Выбор модели влияет на размер журнала транзакций, а также возможности резервного копирования и восстановления данных.

Модель восстановления отдельных операций Full • Данная модель позволяет восстановить БД до того состояния, в котором она была на момент сбоя или на любой иной указанный момент времени. • При использовании данной модели в журнал заносятся все операции (в том числе широкомасштабные – такие как, операция создания индекса, копирования данных). Такие операции требуют значительного объема доступного пространства в журнале транзакций. • При выборе такой модели необходимо регулярно и часто выполнять резервное копирование журнала транзакций.

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

Простая модель (Simple) • При использовании простой модели восстановления в журнал транзакций записываются все операции, в том числе и широкомасштабные. • Но для восстановления данных не используется резервное копирование журнала транзакций, каждый процесс контрольной точки усекает файл журнала. Это позволяет избежать переполнения журнала транзакций. • При использовании простой модели БД может быть восстановлена только до момента, когда была сделана последняя резервная копия. • Системная БД tempbd всегда использует простую модель. Пользовательские БД используют простую модель по умолчанию, однако данный режим можно изменить.

Системные таблицы SQL Server • SQL Server использует системные таблицы для управления работой СУБД и связанными с ней таблицами. • Системные таблицы делятся на две группы: o системный каталог; o каталог базы данных. • Изменение или удаление системных таблиц повлечь за собой неполадки в работе SQL Server.

Системный каталог • Системный каталог включает в себя системные таблицы, используемые СУБД для управления системой. • Системный каталог существует только в БД master. • Системный каталог содержит метаданные, относящиеся ко всему экземпляру SQL Server и параметры системы.

Таблицы системного каталога Системная таблица Описание Sysaltfiles Содержит строку информации о каждом файле БД Sysconfigures Содержит строку информации о системных параметрах, выбранных до запуска SQL Server и динамически настроенных после запуска Sysdatabases Содержит строку информации для каждой БД Sysdevices Содержит строку о каждом созданном устройстве резервного копирования, включая логические и физические имена файлов Syslockinfo Содержит строку информации о каждом запросе на блокировку объекта БД Syslogins Содержит строку информации о каждой учетной записи Sysmessages Содержит строку информации о каждой возвращаемой системной ошибке Sysperfinfo Содержит строку информации для каждого счетчика производительности

Каталог базы данных • Каталог базы данных состоит из системных таблиц, используемых для управления отдельной БД. В каждой БД имеется набор таких таблиц. Системная таблица Описание Syscomments Содержит строку информации для каждого представления (view), правила (rules), значения по умолчанию и др. объектов. Изменять или удалять значения в полях данной таблицы нельзя. Sysindexes Содержит строку информации о каждом индексе и таблице БД Sysobjects Содержит строку информации для каждого объекта БД Sysusers Содержит информацию о каждом пользователе или группе пользователей БД

Системные хранимые процедуры • Системные хранимые процедуры – процедуры составленные из операторов T-SQL, поставляемые вместе с SQL Server. • Системные процедуры работают с системными таблицами, позволяют получить системную информацию и выполнять задачи администрирования БД.

Использование системных хранимых процедур Системная хранимая процедура Описание SP_configure [‘name’, ’value’] Получение и изменение параметров настройки экземпляра SQL Server SP_dboption [‘db’, ‘parameter’, ‘value’] Получение и изменение значений параметров БД SP_help [‘object’] Получение информации об отдельном объекте БД SP_depends [‘object’] Получение информации о зависимости объектов БД SP_helpdb [‘db’] Получение информации о заданной БД (всех БД) SP_helpfile [‘file name’] Получение физического имя и атрибутов указанного файла SP_lock [‘id 1’, , ‘id 2’] Вывод информации о текущих блокировках SP_monitor Вывод информации о занятости SQL Server с начала его работы SP_spaceused [‘object’, ‘updateusage’] Вывод информации о числе записей, дисковом пространстве и т. д. SP_statistics [‘table’, ‘owner’, ‘id’, ‘index’] Вывод данных обо всех индексах и информацию о таблицах SP_who [‘user’] Вывод информации обо всех пользователях и процессах

Системные функции • Системные функции – набор встроенных функций, позволяющих обращаться к системным таблицам при помощи операторов T-SQL и получать специфические данные о значениях параметров, объектах и настройках. Системная функция Описание DATABASEPROPERTYEX (‘db’, ’property’) Возвращает значения свойства БД DB_ID(‘db’) Возвращает идентификатор БД DB_NAME(id_bd) Возвращает имя БД FILE_ID(‘file name’) Возвращает идентификатор файла FILE_NAME(id_file) Возвращает логическое имя файла FILEPROPERTY(‘file’, ’property’) Возвращает значение свойства файла GET DATE() Возвращает текущую системную дату HOST_NAME() Возвращает имя хоста STATS_DATE(id_table, id_ind) Возвращает дату обновления информации для индекса USER_ID(‘user_name’) Возвращает идентификатор пользователя USER_NAME(id_user) Возвращает имя пользователя БД

СОЗДАНИЕ БД 67

Создание баз данных Для создания базы данных необходимо определить имя базы данных, ее владельца, размер, а также файлы и файловые группы, которые будут в ней храниться. При создании базы данных нужно учесть следующие условия: q Для создания базы данных необходимо обладать, как минимум, разрешениями CREATE DATABASE, CREATE ANY DATABASE или ALTER ANY DATABASE. q В SQL Server для файлов данных и журналов каждой базы данных заданы некоторые разрешения. Эти разрешения предотвращают случайное повреждение файлов в случае их сохранения в каталоге с открытыми разрешениями. q Пользователь, создавший базу данных, становится ее владельцем. q В одном экземпляре SQL Server может быть создано до 32 767 баз данных. q Имя базы данных должно соответствовать правилам, определенным для идентификаторов. q Все пользовательские объекты в базе данных model копируются во вновь создаваемые базы данных. Можно добавлять любые объекты, такие как таблицы, представления, хранимые процедуры и типы данных, в базу 68 данных model, чтобы включать их во все создаваемые базы данных.

Задаем параметры БД при создании • Параметры БД определяют различные аспекты работы с системными или пользовательскими базами данных. • Параметра пользовательских БД при создании наследуют параметры системной БД model. • Выделяют следующие типы параметров: o управление автоматическим поведением БД (такие как автоматическое создание и обновление данных статистики); o выбор модели восстановления; o совместимость с ANSI; o режим доступа к БД (например только для чтения и доступа, предоставленный только для владельца БД – dbo).

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

Автоматическое увеличение размера файлов • При создании пользовательской БД по умолчанию выбирается автоматическое увеличение размеров файлов данных и журналов транзакций. • Однако при каждом увеличении файлов нагрузка на систему возрастает. Кроме того, увеличение дискового пространства для хранения файлов приводит к дефрагментации диска. • Все это обуславливает необходимость контроля за объемом данных и журнала транзакций и отказ от модели автоматического увеличения размеров файлов данных и журнала транзакций.

Изменение параметров автоматического увеличения размера файлов • Изменение параметров автоматического увеличения размеров файлов данных и журнала транзакций выполняется с помощью графических средств SQL Server Management Studio. • Другой вариант – использование операторов Transact-SQL. Для отключения, например, автоматического увеличения файла данных БД semdb, необходимо выполнить запрос: o ALTER DATABASE SEMDB MODIFY FILE ( NAME = 'semdb_data', FILEGROWTH = 0 )

Выполнение некоторых служебных операций с базами данных

1. Увеличение размера базы данных

• На графическом экране SQL Server Management Studio для этого достаточно открыть свойства базы данных, перейти на вкладку Files и ввести новый размер для файла базы данных в столбце Initial Size (Исходный размер) (или добавить в список новый файл).

2. Перемещение файлов баз данных SQL Server

Для выполнения этой операции можно прибегнуть к резервному копированию и восстановлению, отсоединению и присоединению баз данных, но можно выполнить перемещение при помощи T-SQL

Перенос файлов базы данных производится очень просто: • переведите базу данных в автономный режим, например: ALTER DATABASE testdb SET OFFLINE; или в контекстном меню этой базы данных в Object Explorer выберите команду Tasks | Detach (Задачи | Отсоединить) • затем средствами операционной системы перенесите файлы баз данных в другое место; • после этого укажите SQL Server, что файлы базы данных теперь находятся в другом месте: ALTER DATABASE testdb MODIFY FILE (NAME = testdb, FILENAME = 'D: testdb 1. mdf'); • верните базу данных в обычный режим: ALTER DATABASE testdb SET ONLINE;

1. Отсоединить

2. Скопируйте • Скопируйте файлы базы данных и журнал транзакций в нужное вам место обычными средствами Windows.

3. Присоединить исходную БД

4. Присоедините скопированную БД к новому серверу

3. Удаление базы данных

Удалить базу данных в SQL Server можно двумя способами: • из контекстного меню базы данных в SQL Server Management Studio по команде Delete (Удалить); • при помощи команды DROP DATABASE, например: DROP DATABASE testdb; В любом случае удаляемая база данных не должна быть открыта ни пользователями, ни служебными процессами, такими как репликация. При удалении базы данных SQL Server одновременно удаляются файлы этой базы данных на диске (если она не находилась в автономном режиме).

4. Резервное копирование

Цель резервного копирования для администратора: • копия базы данных (которую можно восстановить) на случай сбоя

Устройства резервного копирования — это специальные объекты, которые хранятся в базе данных master. Их единственное назначение — хранить информацию о пути к физическому файлу в операционной системе или о стриммере. Создать такое устройство можно: • на графическом интерфейсе — из контейнера Server Objects | Backup Devices (Объекты сервера | Устройства резервного копирования) в Management Studio;

Параметры

База данных - Database • указывает базу данных, для которой необходимо создать резервную копию.

Модель восстановления - Recovery model определяет, в течение какого времени ведется запись журнала транзакций и какая информация в нем фиксируется. Устанавливается заранее в Свойствах БД. ■ Full ■ Bulk-Logged ■ Simple

Тип резервного копирования Backup type • • Full Differential Transaction Log ……………… Для полного или разностного резервного копирования используется команда BACKUP DATABASE (для разностного еще указывается параметр WITH DIFFERENTIAL), для резервного копирования журнала транзакций — команда BACKUP LOG

полное резервное копирование • В резервную копию записываются все данные, которые есть в базе данных. • Пустые страницы при этом не копируются, поэтому если, например, файлы вашей базы данных имеют размер в 1 Гбайт, а реально данные в них занимают всего лишь 200 Мбайт, то получится резервная копия размером 200 Мбайт.

разностный differential • В этом случае на резервную копию записываются все изменения, которые были произведены с момента последнего полного резервного копирования.

резервное копирование журналов транзакций (transaction log) • Если вы используете режим восстановления Full или Bulk-logged, то выполнение такого резервного копирования практически обязательно. Причина проста: если вы не будете производить резервное копирование журналов транзакций, то не будет производиться и их очистка. В результате место в файлах журналов транзакций может закончиться (а если для них установлен неограниченный размер, то закончится и место на диске).

• В качестве дополнительного типа резервного копирования можно рассматривать резервное копирование файлов (file backup) и файловых групп (filegroup backup).

Компонент, подлежащего резервному копированию Backup component • всей базы данных • Файлы и группы файлов. Данная опция позволяет выбрать только один файл резервной копии.

Корпорация Microsoft считает идеальным вариантом такое расписание резервного копирования (такой вывод можно сделать из официальных учебных курсов и сертификационных экзаменов): • раз в неделю — полное резервное копирование; • раз в сутки (каждую ночь) — разностное резервное копирование; • несколько раз в день — резервное копирование журналов транзакций. Этот вариант наилучшим образом подходит для больших баз данных (десятки и сотни гигабайт), которые активно изменяются.

SQL-операторы для резервного копирования • Полное • Разностное • Журнал транзакций

Безопасность SQL Server

• Обеспечение безопасности SQL Server можно представить как последовательность шагов в следующих областях: платформа, объекты (в том числе данные) и приложения, которые обращаются к системе. 104

1 Безопасность платформы и сети • Платформа для SQL Server включает в себя физическое оборудование и сетевые компьютеры, с помощью которых клиенты соединяются с серверами базы данных, а также двоичные файлы, применяемые для обработки запросов базы данных. Физическая безопасность Безопасность операционной системы 105

1 а Физическая безопасность • Рекомендуется строго ограничивать доступ к физическим серверам и компонентам оборудования. Например, оборудование сервера базы данных и сетевые устройства должны находиться в закрытых охраняемых помещениях. Доступ к резервным носителям также следует ограничить. Для этого их рекомендуется хранить в отдельных охраняемых помещениях. 106

1 б Безопасность операционной системы • В состав пакетов обновления и отдельных обновлений для операционной системы входят важные дополнения, позволяющие усилить безопасность. • Кроме того, эффективную безопасность можно реализовать с помощью брандмауэров. Использование брандмауэра повышает безопасность на уровне операционной системы, обеспечивая узкую область, на которой можно сосредоточить меры безопасности. 107

2 Безопасность участников и объектов БД • Участники — это отдельные пользователи, группы и процессы, которым предоставлен доступ к ресурсам SQL Server. Защищаемые объекты — это сервер, база данных и объекты, которые содержит база данных. У каждого из них существует набор разрешений, с помощью которых можно уменьшить контактную зону SQL Server. 108

Шифрование и сертификаты • Шифрование не решает проблемы управления доступом. Однако оно повышает безопасность, ограничивая потерю данных даже в тех редких случаях, когда средства управления доступом удается обойти. Например, если главный компьютер, на котором установлена база данных, был настроен неправильно, и злонамеренный пользователь смог получить конфиденциальные данные (например, номера кредитных карточек), то украденная информация будет бесполезна, если она была предварительно зашифрована. 109

3 Безопасность приложений • Безопасность в SQL Server, помимо прочего, обеспечивается разработкой защищенных клиентских приложений на сетевом уровне 110

Рассмотрим подробнее шаг № 2 … Конфигурирование участников системы безопасности сервера

Конфигурирование защищаемых объектов БД

Участники безопасности (Principal ) • это те объекты, которым в SQL Server можно предоставлять разрешения. Они могут быть как индивидуальными (например, учетная запись), так и групповыми (например, роль). • Участник безопасности управляется на трех уровнях: Windows, SQL Server и БД

Примеры участников безопасности логин для учетной записи Windows, логин SQL Server Пользователь БД, Роль БД,

Защищаемые объекты (Securable ) • Это все, на что в SQL Server можно назначить разрешения.

Защищаемые объекты

Разрешения (Permission) • Разрешения — это права пользователя на проведение тех или иных действий на сервере или в базе данных. Пользователи должны иметь соответствующие разрешения, прежде чем они смогут выполнить любое действие, связанное с изменением структуры БД или доступом к данным

• В большинстве случаев процесс предоставления разрешений выглядит так: 1. Создать логин — учетную запись для подключения к SQL Server. 2. Затем создать пользователя базы данных, которому соответствует этот логин. 3. Предоставить пользователю необходимые разрешения.

1. Логин

• Прежде, чем создавать логин, необходимо понять, какой тип вы будете использовать для этого логина: • логин Windows (для входа на сервер используется уч. запись Windows) • логин SQL Server. (для входа на сервер вводится свое имя и пароль)

Логины любого типа создаются одинаково: 1. при помощи графического интерфейса — из окна Login — New (Новый логин). Это окно открывается с помощью команды New Login контекстного меню контейнера Security | Logins (Безопасность | Логины) в Object Explorer в SQL Server Management Studio; 2. из скрипта — при помощи команды CREATE LOGIN. • Например, команда на создание логина SQL Server с именем User 1 и паролем P@ssw 0 rd (для всех остальных параметров будут приняты значения по умолчанию) может выглядеть так: CREATE LOGIN User 1 WITH PASSWORD = 'P@ssw 0 rd';

Встроенные логины SQL Server Сразу же после установки SQL Server 2005 в контейнере Logins появляется набор логинов, которые создаются автоматически: • sa (от System Administrator) — это единственный логин типа SQL Server, который создается по умолчанию. Он обладает правами системного администратора SQL Server.

• Вы обеспечили пользователям возможность входа на SQL Server , создав для них логины. Но сам по себе вход на сервер ничего не дает: пользователю нужны также права на выполнение определенных действий. Обычно для этой цели создаются пользователи или роли баз данных и им предоставляются разрешения

Роли позволяют легко назначать разрешения группе пользователей и могут иметь ассоциированный набор встроенных (неявных) разрешений, не подлежащих изменению. Доступны два типа ролей: • роли сервера - применяются на уровне сервера; • роли БД — применяются на уровне базы данных.

Роли сервера • Используются для предоставления возможности администрирования сервера. • Если включить учетную запись в какуюлибо роль, то пользователи, использующие эту учетную запись, смогут производить любые действия, разрешенные для этой роли. Например, пользователи роли sysadmin имеют наивысший в SQL Server уровень прав и могут выполнить любые задачи

Роли уровня БД Используются если требуется назначать разрешения на базу данных и ее объекты. Эти роли определяются для каждой базы данных отдельно, поэтому каждая БД имеет собственный набор ролей. SQL Server на уровне баз данных поддерживает три типа ролей: • стандартные пользовательские роли БД; • пользовательские роли приложений; • предопределенные (встроенные) роли БД.

На графическом экране работа с ролями сервера производится или из свойств логина (вкладка Server Roles (Серверные роли)), или из свойств самой серверной роли (контейнер Server Roles в Management Studio). Из кода Transact-SQL для назначения логину серверной роли можно использовать хранимую процедуру sp_addsrvrolemember. Например, чтобы предоставить пользователю User 4 права роли SYSADMIN, соответствующий код может быть таким: EXEC sp_addsrvrolemember @loginame = 'user 4', @rolename = 'sysadmin';

• для предоставления прав на уровне всего сервера необязательно использовать серверные роли. Вы вполне можете предоставить эти права напрямую логину (при помощи вкладки Permissions (Разрешения) свойств SQL Server).

Вкладка Permissions свойств SQL Server

Серверные роли - пример • PUBLIC — эту роль вы не найдете в списке серверных ролей. Тем не менее, она существует и активно используется. Права этой роли автоматически получают все, кто подключился к SQL Server, и лишить пользователя членства в этой роли нельзя. Обычно эта роль используется для предоставления разрешений всем пользователям данного сервера; • SYSADMIN — логин, которому назначена эта роль, получает полные права на весь SQL Server (и возможность передавать эти права другим пользователям). С точки зрения серверных разрешений, это соответствует праву CONTROL SERVER с параметром WITH GRANT (т. е. с возможностью передачи);

2. Пользователь

• После создания логинов следующая задача администратора — спуститься на уровень базы данных и создать объекты пользователей базы данных. Пользователи баз данных — это специальные объекты, которые создаются на уровне базы данных и используются для предоставления разрешений в базе данных (на таблицы, представления, хранимые процедуры). Для пользователей используется термин database users (или просто users),

Создать пользователя базы данных можно: • на графическом экране из контейнера Имя_базы_данных | Security | Users в Management Studio; • при помощи команды CREATE USER Например, команда на создание пользователя User 1, которому будет соответствовать логин SQL Server Login 1 со схемой по умолчанию dbo, может выглядеть так: • CREATE USER User 1 FOR LOGIN Login 1 WITH DEFAULT_SCHEMA = dbo;

Схема • Схема формально определяется как набор объектов в базе данных, объединенных общим пространством имен. Проще всего представить себе схему как некий логический контейнер в базе данных, которому могут принадлежать таблицы, представления, хранимые процедуры, пользовательские функции, ограничения целостности, пользовательские типы данных и другие объекты базы данных. Этот контейнер удобно использовать как для именования объектов и их логической группировки, так и для предоставления разрешений.

Встроенные пользователи базы данных • При создании любой базы данных в ней автоматически создаются специальные пользователи: • dbo (от database owner) — пользовательвладелец базы данных. Он автоматически создается для того логина, от имени которого была создана эта база данных. Конечно же, как владелец, он получает полные права на свою базу данных; • ….

3. Разрешения

• Следующее действие после создания пользователей — предоставление разрешений на объекты базы данных

Разрешения (продолжение) • разрешения начинаются с ключевого слова или слов, определяющих предоставляемые права: ALTER, CREATE, DELETE, SELECT, и. т. д.

Передача разрешений SQL Server 2005 оперирует правами доступа трех типов: • GRANT — даст право доступа к объекту; • DENY —запретить что-то делать. • REVOKE — отменить явно предоставленное разрешение или запрет;

• Работа с разрешениями производится одинаково для всех объектов базы данных: • на вкладке Permissions свойств этого объекта; • при помощи команд SQL - GRANT, DENY, REVOKE (пример на след. слайде)

• Например, чтобы предоставить пользователю User 1 возможность просматривать данные в таблице Table 1 в схеме dbo, можно воспользоваться командой: • GRANT SELECT ON dbo. Table 1 TO User 1; • Лишить его ранее предоставленного права можно при помощи команды: • REVOKE SELECT ON dbo. Table 1 TO User 1;

Безопасность SQL Server 2005

Безопасность SQL Server 2005

Пример

1 шаг CREATE LOGIN [ULIvan] FROM WINDOWS WITH DEFAULT_DATABASE = Adventure. Works

2 шаг USE Adventureworks CREATE USER Ivan FOR LOGIN (ULIvan)

3 шаг CREATE TABLE Products (productl. D INT, product. NAME nvarchar(50)) GRANT SELECT ON Products To Ivan GO

4 шаг CREATE SCHEMA stock. Schema CREATE TABLE stock. Schema. stock (roomi. D INT, product. ID INT, quantity INT) GRANT SELECT ON Stock TO Ivan GO

5 шаг ALTER USER Ivan WITH DEFAULT_SCHEMA = stock. Schema

6 шаг INSERT INTO dbo. Products VALUES (1, 'Pentium 4 Processors') INSERT INTO stock. Schema. stock VALUES (1, 1, 11)

Проверка • Select * from Products • Select * from stock