Контент. 1.1 Клиенты SQL Server. 1.1 Клиенты SQL Server Агент SQL Server Параметры базы данных Системный каталог.
1 Содержание 1 Основная работа Клиенты SQL Server Агент SQL Server Параметры базы данных Параметры памяти Другие параметры Параметры разрешений — правило наименьшего доступа Системный каталог Файлы и файловые группы 6 4 Резервное копирование и восстановление 6 5 Обслуживание базы данных Параметры разрешений Пользователи базы данных Пользователи Windows Пользователи SQL Server Авторизация Основная работа 1.1 Клиенты SQL Server При работе с SQL Server мы будем использовать два клиента: 1
2 SQL Server Management Studio (SSMS) — графический интерфейс для настройки SQL Server. Запускается из меню «Пуск» или вводом ssms.exe в командной строке. SQLCMD — линейный процессор SQL Server. Позволяет запускать сценарии и отправлять команды на SQL Sever. Это может быть полезно, если мы хотим запустить некоторые команды из командной строки или если SQL Server перестает отвечать на запросы. SQLCMD можно использовать для запуска сохраненного сценария sql: sqlcmd -S \ -i -o Остановку экземпляра SQL Server можно выполнить несколькими способами: Остановка служб MSSQLServer и SQLServerAgent. Остановитесь в SSMS, щелкнув экземпляр правой кнопкой мыши. Команда SHUTDOWN в sqlcmd. Команда командной строки: net stop mssqlserver. Запуск экземпляра SQL Server: запуск служб MSSQLServer и SQLServerAgent. Запустите в SSMS, щелкнув экземпляр правой кнопкой мыши. Команды командной строки: net start mssqlserver, net start mssqlagent. Информацию о сервере, экземплярах, базах данных, выполненных задачах и аудитах можно найти в средстве просмотра файлов журнала. Попасть в него можно, например, в окне «Обозреватель объектов» из вкладки «Журнал управления SQL-сервером» после двойного щелчка по файлу журнала. Здесь у нас отображаются важные события insatnce (базы данных). 1.2 Агент SQL Server Это служба,который выполняет задачи, созданные в SSMS. Агент SQL Server также может отслеживать базу данных и уведомлять администратора о различных событиях в базе данных. Для того, чтобы иметь возможность пользоваться услугой 2
3 необходимо, чтобы он работал (неявно эта служба остановлена). Помимо прочего, его можно запустить с помощью SSMS (если авторизованный пользователь имеет на это право) в проводнике объектов. Для обработки задач агента SQL Server пользователь должен иметь одну из предопределенных ролей: SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole или должен быть непосредственно в роли sysamin. Роли здесь отсортированы от наименее привилегированных. 2 Настройки базы данных Подробную информацию можно найти на страницах руководства для SQL Server 1. SQL Server позволяет вам установить основные параметры экземпляра сервера в окне свойств SQL Server, доступ к которому можно получить в студии управления MS SQL Server (SSMS) . В этом окне мы можем задать основные параметры экземпляра. Две категории параметров SQL Server: Параметры устанавливаются только сразу после настройки и запуска RE-CONFIGURE (RECONFIGURE WITH OVERRIDE) Параметры устанавливаются только после перезапуска экземпляра. Параметры, относящиеся к расширенным настройкам (расширенная страница в свойствах сервера), могут быть установлены только в том случае, если для параметра отображения дополнительных параметров установлено значение 1. Мы должны вернуть этому параметру его исходное значение после установки соответствующих дополнительных параметров. Настройки параметров SQL Server можно просмотреть в представлении sys.configurations. Для каждого параметра есть два значения: атрибут value и используемое значение. Первый указывает текущее установленное значение параметра, а второй указывает значение, используемое в данный момент в экземпляре (или базе данных)
.
4 2.1 Настройки памяти При определенных условиях мы можем адресовать больше памяти, чем 2 ГБ в случае 32-разрядных компьютеров.Если мы включим /3GB в boot.ini, мы ограничим размер окон до 1 ГБ и позволим SQL Server адресовать до 3 ГБ. Если мы также включим /PAE, Windows позволит нам адресовать до 16 ГБ. Ну и последний вариант — включить только /PAE, в этом случае мы можем адресовать память от 64 Гб. Чтобы SQL Server мог использовать этот вариант адресации памяти выше 4 ГБ, для параметра awe enable должно быть установлено значение 1. Кроме того, пользователю, запускающему службу SQL Server, необходимо предоставить право на блокировку страниц в памяти с помощью локальной группы редактор политики. Это сделано для того, чтобы операционная система автоматически не сохраняла на диске память выше 4Гб. В 64-битных системах этот параметр необязателен, но в большинстве случаев рекомендуется. Это сделано специально для того, чтобы страницы не менялись местами операционной системой в случае нехватки памяти (например, при копировании больших файлов). Если мы установили пользователя Windows таким образом, то также необходимо установить максимальное значение памяти SQL Server. Это связано с тем, что не выделяется вся доступная память, что также может привести к нестабильности ОС. 2.2 Другие настройки Описание некоторых других параметров можно увидеть в таблице Настройки разрешений — правило наименьшего доступа Здесь мы представим несколько основных процедур настройки доступа отдельных частей SQL Server к ОС, чтобы действовать по правилу наименьшего доступа. Используя инструмент диспетчера конфигурации SQL Server, можно установить пользователя, от имени которого запускаются отдельные службы. Назначив пользователя на службу с помощью этого инструмента, вы избежите проблем с разрешениями, так как менеджер конфигурации неявно выставит необходимые права. Для большей детализации управления доступом рекомендуется создавать отдельных пользователей для каждой службы. 4
5 Название параметра Повышение приоритета Максимальное количество рабочих потоков Интервал восстановления Коэффициент заполненияОжидание запроса Пользовательские соединения Описание Может повысить приоритет SQL Server по сравнению с другими процессами, работающими в Windows. На хорошо настроенном сервере это минимальное ускорение. Обычно не рекомендуется устанавливать. При ненулевом значении создается пул потоков, в котором может происходить оптимизация при работе с потоками. (рекомендуемое значение выше 256) Определяет максимальное время в минутах, которое требуется базе данных для восстановления. Это значение рекомендуется установить, если мы обнаруживаем проблемы с частыми контрольными точками. Указывает на заполнение индекса в случае его создания или перестроения (rebuild). Время в секундах, в течение которого SQL Server должен ожидать выполнения оператора SQL. Максимальное количество параллельных подключений к базе данных. Это может помочь при перегрузке базы данных. Таблица 1: Параметры экземпляра SQL Server Задачи, созданные для агента SQL Server, часто требуют установки разрешений для доступа к ресурсам за пределами SQL Server. 2.4 Системный каталог Некоторые представления системного каталога перечислены в таблице 2. Подробную информацию можно найти на страницах руководства по SQL Server 2. Системный каталог также можно просматривать с помощью хранимых процедур. SQL Server содержит целый набор таких процедур, которые возвращают информацию о базе данных и экземпляре и с помощью которых можно манипулировать настройками (см. таблицу 3). Подробную информацию можно найти на страницах руководства по SQL Server
.
6 Имя представления sys.configurations sys.traces sys.sysfilegroups Описание Содержит информацию о настройке отдельных параметров экземпляра. Он содержит трассировки, запущенные на данном экземпляре файловых групп в базе данных. Таблица 2: Представления экземпляра SQL Server Имя процедуры sp configure sp monitor sp who sp spaceused Описание Настройка отдельных параметров экземпляра и базы данных. Возвращает статистику SQL Server Возвращает список пользователей, подключенных к базе данных Оценкаразмер полной резервной копии Таблица 3: Хранимые процедуры экземпляра SQL Server 3 Файлы и файловые группы Подробную информацию о файловых группах можно найти на страницах руководства для SQL Server 4. Файловые группы данной базы данных можно создать в свойствах базы данных. Кроме того, мы можем создавать файлы данных, в которых мы указываем их местоположение, сеанс в файловой группе и степень детализации роста файла. 4 Резервное копирование и восстановление Резервное копирование и восстановление можно выполнять как с помощью команд T-SQL 5, так и с помощью SSMS 6. Сначала попробуем отдельные команды T-SQL: Полное резервное копирование базы данных выполняется с помощью команды: BACKUP DATABASE TO DISK = N путь к целевому файлу С ИНИЦИАЦИЕЙ
7 Выполняется восстановление базы данных из резервной копии: RESTORE DATABASE FROM DISK = N путь к файлу резервной копии WITH REPLACE Можно запустить дифференциальное резервное копирование базы данных, которое выполняется относительно последнего полного резервного копирования: BACKUP DATABASE TO DISK = N путь к целевому файлу WITH DIFFERENTIAL , INIT Восстановление базы данных из одной разностной резервной копии выполняется в два этапа. Сначала копируется полная резервная копия: RESTORE DATABASE FROM DISK = N путь к файлу полной резервной копии WITH NORECOVERY, REPLACE GO Затем продолжается создание дифференциальной резервной копии: RESTORE DATABASE FROM DISK = N путь к файлу дифференциальной резервной копии GO Архивируем log с помощью команды: BACKUP LOG TO DISK = N путь к целевому файлу WITH INIT При восстановлении из резервной копии с использованием архивных журналов мы всегда должны сначала сделать резервную копию текущего журнала и только потом выполнять восстановление. Восстановление с использованием архивного журнала выполняется следующей командой: RESTORE LOG FROM DISK = N путь к архивному файлу журнала. При использовании SSMS можно задать три основные модели восстановления: 7
8 Модель полного восстановления означает, что будет выполняться как резервное копирование файлов данных, так и резервное копирование журнала повторов. Файл журнала повторов хранится до тех пор, пока он не будетпроведено его архивирование. Простая модель восстановления означает, что резервное копирование журнала повторов не выполняется, а старые записи постепенно перезаписываются. Немассовая модель полного восстановления, при которой не ведется журнал массовых операций. В случае ошибки носителя эта информация может быть потеряна. Модели определяют способ обработки файла журнала повторного выполнения, а также методы восстановления, поддерживаемые в базе данных. Таким образом, этот параметр сам по себе не обеспечивает никакой безопасности базы данных. Дополнительные настройки резервного копирования можно выполнить с помощью планов обслуживания, описанных в главе 5. 5 Обслуживание базы данных Мастер плана обслуживания можно использовать для создания отдельных задач обслуживания базы данных, которые можно запустить, щелкнув правой кнопкой мыши Планы обслуживания в SSMS. Другой вариант — создать новый план (Новый план обслуживания). В плане мы можем определить подпланы, которые представляют собой набор задач, которые должны быть выполнены в определенное время. Создав новое подрасписание, мы указываем время и частоту его выполнения, после чего можем вставлять в него задачи. Мы вставляем задачу в подплан, просто перетаскивая ее из панели инструментов Задачи плана обслуживания. После создания задачи дважды щелкните по ней и укажите ее детали. Все созданные таким образом задачи запускаются службой агента SQL Server, которую также необходимо запустить. Задания, созданные таким образом, можно найти на вкладке заданий агента SQL Server. Кроме всего прочего, здесь также можно создать задачу. Средство просмотра файла журнала, которое отображает информацию о запущенных задачах и планах, можно открыть, щелкнув правой кнопкой мыши Планы обслуживания в SSMS и выбрав просмотр истории. 8
9 5.1 Настройки разрешений Каждая созданная задача запускается с правами создавшего ее пользователя. SQL Server содержит несколько подсистем, требующихрасширенные разрешения. К таким подсистемам относятся, например, службы интеграции или PowerShell. Чтобы не назначать роль системного администратора пользователям, которым необходимо использовать эти подсистемы, необходимо создать учетную запись-посредник. Чтобы настроить доступ к подсистеме через прокси: Сначала создайте пользователя в ОС. Для этого используйте средство управления компьютером в средствах администрирования. Вы создаете учетные данные для этого пользователя на вкладке «Учетные данные безопасности». На вкладке Прокси агента SQL Server щелкните правой кнопкой мыши, чтобы создать новый прокси, который вы назначаете учетным данным, созданным на предыдущем шаге. Вы выбираете подсистемы, к которым этот прокси разрешит доступ, и, наконец, вы можете назначить пользователей (принципалов) для этого прокси, которые смогут использовать этот прокси для доступа к подсистеме. 6. Пользователи базы данных По сути, существует два типа учетных записей пользователей, которые мы можем использовать при входе в SQL Server: системные пользователи Windows и пользователи SQL Server. Затем мы можем назначить отдельным пользователям роли, что облегчит нам управление разрешениями пользователей. Базовая роль — sysadmin, которая представляет роль администратора SQL Server. Как и в Oracle, здесь мы также находим публичную роль, которая неявно назначается каждому пользователю. 6.1 Пользователи системы Windows Можно войти в SQL Server как пользователь ОС Windows, если имя пользователя создано в SQL Server. Имя пользователя можно создать с помощью команды CREATE LOGIN FROM WINDOWS 9
10 WITH DEFAULT DATABASE = или с помощью SSMS на вкладке Security Logins. 6.2 Пользователи SQL Server Этим пользователям не нужно входить в ОС для входа в SQL Server. Их можно создать с помощью команды CREATE LOGIN WITH PASSWORD = , WITH DEFAULT DATABASE = . 6.3 Авторизация Авторизация для доступа к различным ресурсам происходит с помощьюкоманды GRANT ON [::] TO. Вместо пользователя мы также можем указать роль, на которую мы назначаем право. Как правило, проще управлять разрешениями на уровне роли, чем на уровне пользователя. 10