Оценка архитектуры приложения на основе SQL Server 2008
Академия специальных курсов
По информационным технологиям
Санкт-Петербург, В.О. 20 линия, д. 7, офис 101
тел.: (812) 922-47-60, e-mail: info@askit.ru
Www.askit.ru

Администрирование Microsoft SQL Server 2008
Методические материалы к учебному курсу
Санкт-Петербург
2010 г.
1. Планирование и установка SQL Server 2008. 8
1.1 Планирование установки SQL Server 2008. 8
1.1.1 Оценка архитектуры приложения на основе SQL Server 2008. 8
1.1.2 Выбор оборудования. 8
1.1.3 Выбор редакции SQL Server 2008. 9
1.2 Установка SQL Server 2008. 9
1.2.1 Начало установки. Выбор набора компонентов. 9
1.2.2 Работа с именованными экземплярами. 10
1.2.3 Выбор учетной записи для служб SQL Server 10
1.2.4 Выбор режима аутентификации SQL Server 2008. 10
1.2.5 Выбор кодировки и порядка сортировки. 11
1.2.6 Остальные параметры установки. 11
1.3 Автоматизированная и удаленная установка. 11
1.4 Проверка установки и выполнение послеустановочных задач. 11
1.4.1 Проверка результатов установки. 11
1.4.2 Настройка серверных сетевых библиотек. 12
1.4.3 Другие послеустановочные задачи. 12
2. Средства администрирования SQL Server 2008. 13
2.1 SQL Server Management Studio. 13
2.1.1 Окно Registered Servers («Ctrl»+«Alt»+«G») 13
2.1.2 Окно Object Explorer («F8») 13
2.1.3 Окно Summary («F7») 13
2.1.4 Окно Solution Explorer («Ctrl»+«Alt»+«L») 14
2.1.5 Другие окна SQL Server Management Studio. 14
2.1.6 Способы создания скриптов в Management Studio. 15
2.1.7 Дополнительные возможности Management Studio. 16
2.2 Business Intelligence Development Studio. 16
2.3 SQL Server Configuration Manager 17
2.3.1 SQL Server Configuration Manager 17
2.3.2 Службы SQL Server 2008. 17
2.3.3 Настройка клиентских сетевых библиотек средствами SQL Server Configuration Manager. SQL Native Client 17
2.4 SQLCmd. 18
2.4.1 Применение SQLCmd. 18
2.4.2 Специальный режим подключения Dedicated Administrator Connection (DAC) 18
2.6 SQL Server Profiler 19
2.7 Database Engine Tuning Advisor 19
2.8 Другие графические утилиты SQL Server 2008. 20
2.9 Другие консольные утилиты SQL Server 2008. 20
3. Создание баз данных и настройка параметров. 21
3.1 Служебные и учебные базы данных SQL Server 2008. 21
3.2 Физическая структура базы данных. 22
3.2.1 Файлы баз данных и журналов транзакций. 22
3.2.2 Определение размера файлов данных. 22
3.2.3 Определение размера файлов журналов транзакций. 22
3.2.4 Экстенты и страницы.. 22
3.2.5 Применение файловых групп. 23
3.3 Логическая структура базы данных. 23
3.4 Создание пользовательских баз данных. 23
3.4.1 Создание базы данных из SQL Server Management Studio. 23
3.4.2 Создание скрипта, описывающего базу данных. 24
3.4.3 Перенос базы данных. 24
3.5 Основные параметры базы данных. 25
3.5.1 Режим восстановления базы данных (Recovery Model) 25
3.5.2 Режимы работы базы данных. 25
3.6 Сопровождение баз данных. 26
3.6.1 Увеличение размера базы данных. 26
3.6.2 Уменьшение размера базы данных. 26
3.6.3 Перенос файлов базы данных. 27
3.6.4 Переименование базы данных. 27
3.6.5 Изменение владельца базы данных. 27
3.6.6 Удаление базы данных. 27
3.6.7 Проверка целостности базы данных. 28
4. Безопасность SQL Server 2008. 28
4.1 Терминология и основы системы безопасности SQL Server 2008. 28
4.2 Учетные записи SQL Server 2008. 28
4.2.1 Выбор типа учетной записи. 28
4.2.2 Создание учетной записи и настройка ее параметров. 29
4.2.3 Режимы аутентификации SQL Server 2008. Аудит попыток входа. 30
4.2.4 Учетные записи, создаваемые по умолчанию.. 30
4.2.5 Серверные роли. Разрешения на уровне сервера. 31
4.3 Пользователи баз данных и схемы.. 32
4.3.1 Создание, изменение и удаление пользователей базы данных. 32
4.3.2 Встроенные пользователи базы данных. 33
4.3.3 Встроенные роли баз данных. 33
4.3.4 Создание пользовательской роли. 33
4.3.5 Роли приложений. 34
4.3.6 Управление схемами базы данных. 35
4.3.5 Разрешения SQL Server 2008. 35
4.3.6 Предоставление прав на объекты в базе данных. 36
4.4 Изменение контекста выполнения. Выражение Execute As. 36
4.4.1 Предоставление права IMPERSONATE.. 36
4.4.2 Изменение контекста выполнения. 36
5. Резервное копирование и восстановление баз данных SQL Server 2008. 37
5.1 Планирование резервного копирования. 37
5.1.1 Выбор оборудования для хранения резервных копий. 37
5.1.2 Устройства резервного копирования. 38
5.1.3 Типы резервного копирования. 38
5.1.4 Расписание резервного копирования. 38
5.2 Проведение резервного копирования. 39
5.2.1 Средства для выполнения резервного копирования. 39
5.2.2 Параметры резервного копирования. 39
5.2.3 Получение информации о резервном копировании и создание отчетов. 41
5.3 Основы восстановления баз данных. 42
5.3.1 Терминалогия восстановления БД из резервной копии. 42
5.3.2 Общий план восстановления из резервной копии. 42
5.3.3 Подготовка к восстановлению.. 42
5.4 Проведение восстановления. 43
5.5 Специальные ситуации восстановления. 45
5.5.1 Восстановление базы данных в оперативном режиме (online) 45
5.5.2 Восстановление отдельных страниц базы данных. 45
5.5.3 Восстановление системных баз данных. 46
Глава 6. Автоматизация администрирования SQL Server 2008. 46
6.1. Автоматизация административных операций средствами SQL Server Agent 46
6.1.1. SQL Server Agent 46
6.1.2. Параметры настройки SQL Server Agent 47
6.2. Работа с заданиями SQL Server Agent 49
6.2.1. Параметры заданий. 49
6.2.2. Параметры этапов заданий. 50
6.2.3. Запуск заданий. 51
6.2.4. Настройка расписания заданий. 51
6.2.5. Настройка параметров отчета о завершении задания. 52
6.2.6. Безопасность при выполнении заданий. Настройка учетных записей прокси. 52
6.2.7. Просмотр истории выполнения заданий. 53
6.3. Мультисерверные задания. 54
6.3.1 Предварительная подготовка: 54
6.3.2 Создание и настройка мультисерверных заданий. 54
6.4. Работа с предупреждениями. 55
6.4.1 Создание предупреждения. 55
6.4.2 Предупреждения типа SQL Server event alert 56
6.5. Работа с операторами. 57
6.6. Настройка электронной почты в SQL Server 2008. 58
6.6.1. Обзор возможностей SQL Server 2008 для работы с электронной почтой. 58
6.6.2. Работа с Database Mail (SQLiMail) 58
6.6.3 Настройка Database Mail 59
6.6.4 Хранимые процедуры Database Mail 60
6.6.5 Настройка SQL Server Agent для использования электронной почты.. 60
6.6.6 Получение информации о работе Database Mail 60
6.6.7. Работа с SQLMail 61
6.6.8 Хранимые процедуры для работы с SQLMail 61
6.6.9. Применение объекта CDO.Message для работы с электронной почтой SQL Server и SQL Server Agent 62
6.6.10. Использование утилит для отправки сообщений из командной строки. 63
6.7. Планы обслуживания баз данных (Database Maintenance Plans) 63
Глава 7. Выполнение административных операций при помощи объектных моделей SMO, SQL-DMO и WMI. 65
7.1. Применение скриптов для выполнения административных операций. 65
7.2. Объектная модель SQL-DMO.. 65
7.2.1. Обзор объектной модели SQL-DMO.. 65
7.2.2. Объект SQLDMO.Application. 66
7.2.3. Объект SQLDMO.SQLServer2. 66
7.2.4. Объект SQLDMO.Database2. 68
7.3. Объектная модель SMO.. 68
7.3.1. Обзор объектной модели SMO.. 68
7.3.2. Свойства и методы объектов SMO.. 70
7.3.3. Объект SMO.Server 71
7.3.4. Объект SMO.Database. 73
7.4. WMI и SQL Server 2008. 74
7.4.1. Характеристики WMI. 74
7.4.2. WMI-поставщики для работы с SQL Server 75
7.4.3. Программные средства для работы с WMI. 75
7.4.4. Подключение к службе WMI. 77
7.4.5. Язык WQL: подключаемся к объектам WMI. 77
7.4.6. Работа с событиями в WMI. 79
7.4.7. Объекты WMI Provider for Configuration Management 80
7.4.8. Работа с WMI Provider for Server Events. 81
Глава 8. Мониторинг и оптимизация производительности SQL Server 2008. 82
8.1. Мониторинг активности пользователей. 82
8.1.1. Применение Activity Monitor 82
8.1.2. Использование хранимых процедур и динамических представлений. 82
8.1.3. Использование SQL Profiler (профилировщика) 83
8.1.4. Использование хранимых процедур трассировки. 86
8.1.5. Применение триггеров DDL.. 86
8.1.6. Другие средства мониторинга активности пользователей и уведомления о событиях. 86
8.2. Журналы SQL Server 2008. 87
8.3. Мониторинг производительности SQL Server 2008. 87
8.3.1. Терминология мониторинга производительности. 87
8.3.2. Средства для мониторинга и анализа производительности. 88
8.3.3. Нагрузочное тестирование. 88
8.3.4. Приемы работы с Системным монитором.. 89
8.3.5. Основы работы с объектами и счетчиками. 90
8.3.6. Счетчики для анализа загрузки процессора. 91
8.3.7. Счетчики для анализа загрузки оперативной памяти. 92
8.3.8. Счетчики для анализа производительности дисковой подсистемы.. 93
8.3.9. Счетчики для анализа производительности сетевой подсистемы.. 93
8.3.10. Объекты System Monitor для мониторинга работы SQL Server 2008. 94
8.4. Оптимизация работы SQL Server 95
8.4.1. Оптимизация операционной системы для работы с SQL Server 2008. 95
8.4.3. Оптимизация подключений к SQL Server 2008. 96
8.4.4. Оптимизация системы индексов. Использование Database Tuning Advisor 98
8.4.5. Оценка уровня фрагментации индексов и таблиц. 99
8.4.6. Устранение фрагментации индексов и таблиц. 100
8.4.7. Работа с блокировками. 101
8.4.8. Оптимизация запросов. 103
Глава 9. Применение SQL Server Integration Services. 105
9.1. Средства для работы с SSIS. 105
9.2. Преобразование пакетов DTS SQL Server 2000 в формат пакетов SSIS. 106
9.3. Использование мастера импорта/экспорта данных. 106
9.4. Использование SSIS Designer для создания пакетов. 107
9.5. Менеджеры подключений (Connection Managers) 109
9.6. Работа с Data Flow Task. 110
9.6.1. Что такое Data Flow Task. 110
9.6.2. Элементы Data Flow Task. 111
9.6.3. Источники и назначения Data Flow Task. 111
9.6.4. Преобразования Data Flow Task. 112
9.6.5. Пути и логика выполнение Data Flow Task. 114
9.7. Script Task и ActiveX Script Task. 115
9.8. Bulk Insert Task. 115
9.9. Execute SQL Task. 116
9.8. XML Task. 116
9.11. Message Queue Task. 117
9.12. Execute Package Task и Execute DTS 2000 Package Task. 118
9.13. Transfer Database Task. 118
9.14. Другие задачи копирования объектов SQL Server 119
9.15. File System Task и FTP Task. 119
9.16. Send Mail Task. 119
9.17. Execute Process Task. 119
9.18. Web Service Task. 120
9.19. WMI Data Reader Task и WMI Event Watcher Task. 120
9.19.1 Пример использования задачи WMI Event Watcher Task. 121
9.21. Контейнеры SSIS. 122
8.21.1 Контейнер For Loop. 122
8.21.2 Контейнер Foreach Loop. 122
8.21.3 Контейнер Sequence. 123
9.22. Задачи планов обслуживания (Maintenance Plans) 123
9.23. Ограничения предшественников (precedence constraints) 123
9.24. Протоколирование выполнения пакетов. 125
9.25. Работа с конфигурациями. 125
9.26. Хранение пакетов. 126
9.27. Безопасность пакетов SSIS. 127
9.27.1 Настройка уровня защиты пакета. 127
9.27.2 Использование ролей базы данных MSDB. 128
9.27.3 Применение цифровых подписей для пакетов. 128
9.28. Запуск пакетов SSIS на выполнение. 128
9.28.1 Использование утилиты dtexecui 129
Глава 10. Средства обеспечения отказоустойчивости SQL Server 2008. 130
10.1. Работа SQL Server 2008 в кластере. 130
10.1.1. Терминология и варианты конфигурации кластера. 130
10.1.2. Установка SQL Server 2008 в кластер. 130
10.2. Автоматическая доставка журналов (log shipping) 131
10.2.1. Терминология доставки журналов. 131
10.2.2. Настройка доставки журналов. 131
10.2.3. Мониторинг доставки журналов. 133
10.2.4. Действия в случае сбоя основного сервера. 134
10.2.5. Отмена доставки журналов. 135
10.3. Зеркальное отображение баз данных. 136
10.3.1. Зеркальное отображение баз данных. 136
10.3.2. Терминология зеркального отображения баз данных. 136
10.3.3. Настройка зеркального отображения. 137
10.3.4. Мониторинг зеркального отображения. 137
10.3.5. Смена ролей серверов. 138
10.3.6. Приостановка и отмена зеркального отображения. 139
Глава 11. Репликация в SQL Server 2008. 139
11.1. Новые возможности репликации SQL Server 2008. 139
11.2. Терминология системы репликации. 139
11.3. Типы репликации. 140
11.4. Подготовка к настройке репликации. 141
11.5. Настройка репликации. 141
11.6. Средства администрирования и мониторинга репликации. 143
11.6.1. Средства администрирования репликации. 143
11.6.2. Применение Replication Monitor 143
11.6.3. Другие средства мониторинга репликации. 144
Задание для самостоятельной работы 1.1 Установка Microsoft SQL Server 2008. 147
Задание: 147
Решение: 147
Задание для самостоятельной работы 1.2 Просмотр журнала ошибок SQL Server и системного журнала Windows. 148
Задание: 148
Решение: 148
Задание для самостоятельной работы 1.3 Просмотр созданных файлов и папок. 148
Задание: 148
Решение: 148
Задание для самостоятельной работы 1.4 Просмотр разрешений доступа, назначенных разделам реестра. 149
Задание: 149
Решение: 149
Задание для самостоятельной работы 2.1 Работа со скриптами в SQL Server Management Studio и SQLCMD.. 149
Ситуация: 149
Задание: 149
Решение: 150
К пункту 1 — генерация скрипта на создание таблицы: 150
К пункту 2 — создание пакетного файла: 150
Задание для самостоятельной работы 2.2 Работа с серверными сетевыми библиотеками и псевдонимами. 150
Задание: 150
Решение: 150
К пунктам 1 и 2 — включение сетевой библиотеки и просмотр информации об используемых портах: 150
К пункту 3 — настройка псевдонима для обращения к серверу: 150
Задание для самостоятельной работы 3.1 Перенос баз данных с SQL Server 2000 на SQL Server 2008. 151
Ситуация: 151
Задание: 151
Решение: 151
К пункту 1 — присоединение баз данных SQL Server 2000: 151
К пункту 2 — применение Copy Database Wizard. 151
Задание для самостоятельной работы 4.1 Назначение прав на объекты SQL Server 2008 и изменение контекста выполнения 152
Задание: 152
Решение: 152
К пункту 1 — создание логинов: 152
К пункту 2 — предоставление разрешений на схему и проверка прав: 152
К пункту 3 - предоставление права на выполнение команды EXECUTE AS: 153
Задание для самостоятельной работы 4.2 Шифрование информации в таблицах баз данных. 153
Ситуация: 153
Задание: 153
Решение: 154
К пункту 1 — создание симметричного ключа: 154
К пункту 2 — создание зашифрованной копии таблицы: 154
К пункту 3 — запрос к зашифрованным данным: 154
Задание для самостоятельной работы 5.1 Резервное копирование и восстановление базы данных. 155
Задание: 155
Решение: 155
К пункту 1 — перевод базы данных AdventureWorks в режим восстановления Full: 155
К пункту 2 — проведение полного резервного копирования базы данных AdventureWorks: 155
К пункту 3 — проведение разностного резервного копирования: 155
К пункту 4 — проведение резервного копирования журнала транзакций: 155
К пункту 5 — восстановление резервных копий в другую базу данных: 155
Задание для самостоятельной работы 6.1 Применение заданий, предупреждений и операторов. 156
Задание: 156
Решение: 156
К пункту 1 — создание пользовательской ошибки: 156
К пункту 2 — создание оператора: 156
К пункту 3 — создание предупреждения: 156
К пункту 4 — создание задания: 157
Задание для самостоятельной работы 7.1 Применение объектной модели SMO.. 158
Задание: 158
Решение: 158
Задание для самостоятельной работы 7.2 Применение объектной модели SQL-DMO.. 158
Задание: 158
Решение: 159
Задание для самостоятельной работы 7.3 Работа с WMI Provider for Configuration Management 159
Задание: 159
Решение: 159
Задание для самостоятельной работы 8.1 Сбор информации о запросах, выполняемых приложением.. 160
Ситуация: 160
Задание: 160
Решение: 160
Задание для самостоятельной работы 8.2 Приемы работы с Системным монитором.. 160
Задание: 160
Решение: 161
К пункту 1 — сохранение набора счетчиков Системного монитора в файл HTML: 161
К пункту 2 — использование файла HTML для создания журнала: 161
К пункту 3 — запуск журнала из командной строки: 162
К пункту 4 — анализ собранных данных в Excel: 162
Задание для самостоятельной работы 8.3. Оптимизация системы индексов. 162
Задание: 162
Решение: 162
Задание для самостоятельной работы 8.4 Дефрагментация таблиц и индексов. 163
Задание: 163
Решение: 163
К пункту 1 — анализ степени фрагментации: 163
К пункту 2 — перестроение индексов в базе данных: 163
Задание для самостоятельной работы 8.5 Управление уровнем блокировок. 163
Задание: 163
Решение: 164
К пункту 6: 164
К пункту 9: 164
К пункту 12: 164
Задание для самостоятельной работы 10.1 Создание пакетов SSIS для переноса данных. 165
Задание: 165
Решение: 165
К пункту 1 — создание пустой базы данных Microsoft Access: 165
К пункту 2 — создание пакета SSIS: 165
К пункту 3 — применение преобразований Data Flow Task: 165
К пункту 4 — создание пакетного файла с зашифрованной командной строкой: 167
Задание для самостоятельной работы 11.1 Настройка доставки журналов. 167
Задание: 167
Решение: 168
К пункту 1 — установка именованного экземпляра SQL Server 2008: 168
К пункту 2 — создание базы данных DB1 на первом экземпляре сервера SQL Server 2008: 168
К пункту 3 — создание таблицы: см. задание. 168
К пункту 4 — настройка автоматической доставки журналов: 168
К пункту 5 — просмотр информации о поставке журналов: 169
К пункту 6 — отмена доставки журналов: 169
Задание для самостоятельной работы 12.1 Настройка одноранговой репликации. 170
Задание: 170
Решение: 170
К пункту 1 — копирование базы данных: 170
К пункту 2 — настройка одноранговой репликации: 170
Часть 1 — настройка распределителя: 170
Часть 2 — назначение распределителя второму серверу: 171
Часть 3 — создание публикации: 171
Часть 4 — настройка свойств публикации: 171
Часть 5 — настройка топологии одноранговой репликации: 171
Часть 6 — проверка репликации: 172
Планирование и установка SQL Server 2008
Планирование установки SQL Server 2008
Оценка архитектуры приложения на основе SQL Server 2008
Для разработки структуры приложения необходимо учесть следующие моменты:
1. Обеспечение хранения оперативных и архивных данных. Рекомендуется БД разделить на две:
· Рабочая (оперативная) БД, называемая базой данных OLTP (online transaction processing).
· БД архивной информации – OLAP (online analytical processing). Информация в этой БД, как правило, доступна только на чтение и используется для формирования отчетов и получения аналитической информации.
2. Обеспечение доступа пользователей к БД. Обычно используются следующие варианты:
· применение терминальных технологий (Microsoft Terminal Server или Citrix).
· применение Web-интерфейса (можно использовать и для пользователей из локальной сети).
3. Определение компонентной структуры приложения.Большинство приложений, на основе SQL Server, можно разделить на три уровня:
· уровень данных;
· уровень бизнес-логики;
· уровень представления данных.
Во власти разработчика разнести эти компоненты по отдельным компьютерам или, наоборот, объединить на одном компьютере, при этом следует принимать во внимание:
· соображения производительности;
· соображения отказоустойчивости;
· соображения безопасности.
Выбор оборудования
Минимальные требования SQL Server 2008 к подсистемам оборудования сервера.
Подсистема
| Минимальные требования
| Рекомендованные требования
| Оценка производительности подсистемы (значения счетчиков системного монитора)
| подсистема центрального процессора
| 1 ГГц
| 2 ГГц и выше
| Processor: % Processor Time – должно превышать 80%
| подсистема оперативной памяти
| SQL Server 2008 Express Edition — 192 Мбайт;
Остальные редакции — 512 Мбайт
| SQL Server 2008 Express Edition не более 1 Гбайт;
SQL Server 2008 Workgroup Edition — не более 3 Гбайт;
Остальные редакции — лимитируется только ограничениями ОС
| Memory: Pages/Sec (число обращений к файлу подкачки в секунду) - среднее значение не должно превышать 10 для продолж. промежутка времени;
SQL Server Buffer Manager: Buffer cache hit ratio (количество запросов, обслуживаемых из кэша) - должно быть не меньше 90%
| сетевая подсистема.
| Адаптер Microsoft замыкания на себя (Microsoft Loopback Adapter). Он имеется в дистрибутиве Windows NT 4.0, Windows 2000, XP и 2003 (см. рис. 2.1).
| сетевой адаптер
| | дисковая подсистема
| около 700 Мбайт в зависимости от выбранной версии SQL Server 2008
| Чем больше, тем лучше
| Logical Disk: % Disk Time (сколько процентов от общего времени дисковой подсистеме приходится работать) - значение этого счетчика не должно приближаться к 100% на протяжении продолжительного промежутка времени.
| 
Рис. 2.1. Установка программного эмулятора сетевого адаптера — Microsoft Loopback Adapter
|