Оценка архитектуры приложения на основе 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 к подсистемам оборудования сервера.

Рис. 2.1. Установка программного эмулятора сетевого адаптера — Microsoft Loopback Adapter