В рамках данной статьи рассмотрим системные базы данных MS SQL Server 2012. Ниже приводится их подробное описание, расположение, а также разбираются вопросы о необходимости резервного копирования системных баз данных.
0. Оглавление
- Краткое описание системных баз данных
- Подробное описание системных баз данных
- Просмотр и редактирование свойств системных баз данных
1. Краткое описание системных баз данных
В процессе установки компоненты Database Engine MS SQL Server генерируются следующие системные базы данных:
| Системная база данных | Описание |
|---|---|
| master | В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server. |
| tempdb | Рабочее пространство для временных объектов или взаимодействия результирующих наборов. |
| msdb | Используется агентом SQL Server для планирования предупреждений и задач. |
| model | Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения. |
| resource | Системные объекты физически хранятся в базе данных resource, но логически отображаются в схеме sys любой базы данных. |
2. Подробное описание системных баз данных
Рассмотрим каждую из системных баз данных более подробно:
- База данных master — наиболее важная системная база данных Databse Engine. Она содержит в себе всю системную информацию, в том числе общие для всего экземпляра метаданные, такие как сведения об учетных записях входа, конечных точках и связанных серверах, а также параметры конфигурации системы. Кроме того, база данных master содержит информацию обо всех других базах данных. Здесь же SQL Server хранит сведения об инициализации. Таким образом, если база данных master станет недоступна, запустить SQL Server будет невозможно.
Резервное копирование: По причине важности этой системной базы данных необходимо всякий раз осуществлять ее резервное копирование, после выполнения любых системных операций, таких как добавление новых баз данных, добавления пользователей в SQL Server, изменений параметров экземпляра SQL Server и т. д.
- База данных tempdb — служит рабочим пространством для временных объектов или взаимодействия результирующих наборов. Она доступна всех пользователям подключенных к данному экземпляру SQL Server и используется всеми базами данных, принадлежащих экземпляру SQL Server. База данных tempdb пересоздается при каждом запуске SQL Server. Поэтому в базе данных tempdb ничего не сохраняется от одного сеанса SQL Server до следующего. В данной базе данных содержатся следующие объекты:
- временные объекты, созданные явно, такие как глобальные или локальные временные таблицы, временные хранимые процедуры, табличные переменные и курсоры;
- внутренние объекты, создаваемые компонентой Database Engine, например рабочие таблицы, хранящие промежуточные результаты буферов или сортировки, промежуточные результаты вычислений сложных выражений;
- изменения данных в базе данных, в которой используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков;
- изменения данных для таких функциональных возможностей, как операции с индексами в сети, множественные активные результирующие наборы (режим MARS) и триггеры AFTER.
Резервное копирование: Операции резервного копирования и восстановления базы данных tempdb запрещены.
Замечание: База данных tempdb используется системой довольно активно. По этой причине необходимо убедиться, что объем памяти для этой базы данных достаточно большой. Также можно получить значительный прирост производительности SQL Server путем выноса базы tempdb на отдельный дисковый накопитель, или более быстрый SSD-диск.
- База данных model — используется в качестве шаблона для всех баз данных, которые будут создаваться в данном экземпляре SQL Server. Поскольку база данных tempdb создается каждый раз при запуске SQL Server, база данных model всегда должна существовать в системе SQL Server. Все содержимое базы данных model, включая параметры базы данных, копируется в создаваемую базу данных. Соответственно, возможно изменять данную базу данных, добавляя в нее объекты или полномочия. После этого все новые базы данных будут наследовать и эти новые свойства.
Резервное копирование: Следует делать резервную копию базы данных model, после каждого изменения этой базы данных.
- База данных msdb — используется агентом SQL Server для создания расписания предупреждений и заданий, а также другими компонентами, такими как среда «Среда SQL Server Management Studio», компонента «Service Broker«» и «Database Mail» и др. Например, SQL Server хранит полный журнал резервного копирования и восстановления в базе данных msdb. В эти сведения включено имя стороны, выполнившей резервное копирование, время резервного копирования и устройства или файлы, в которых храниться резервная копия. Среда «SQL Server Management Studio» использует эти сведения для создания плана восстановления базы данных и применения существующих резервных копий журнала транзакций. События резервного копирования для всех баз данных записываются, даже если они создаются средствами пользовательских приложений или сторонних разработчиков.
Резервное копирование: Следует делать резервную копию базы данных msdb, после каждого изменения параметров агента SQL Server.
- База данных resource — это доступная только для чтения база данных, которая содержит все системные объекты, включенные в SQL Server. Такие системные объекты SQL Server, как sys.objects, физически хранятся в базе данных resource, но логически присутствуют в схеме sys каждой базы данных. База данных resource не содержит пользовательских данных или метаданных. Содержимое этой базы данных обычно используется для системных обновлений.
Резервное копирование: Резервное копирование базы данных resource не предусмотрено.
3. Просмотр и редактирование свойств системных баз данных
Для того, чтобы просмотреть или изменить свойства системных баз данных, а также узнать расположение файлов баз данных необходимо подключиться к данному экземпляру SQL Server с помощью утилиты «SQL Server Management Studio». В окне программы, раскрыв в дереве обозревателя объектов (Object Explorer) вкладки «Базы данных» (Databases) — «Системные базы данных» (System databases) можно найти все системные базы данных данных за исключением базы resource, которая скрыта от пользователя. Для того чтобы просмотреть параметры конкретной базы данных, необходимо кликнуть по ней правой кнопкой мыши и в контекстном меню выбрать «Свойства» (Properties).
В открывшемся окне можно просмотреть или изменить различные свойства базы данных, располагающиеся на соответствующих вкладках. Например, на вкладке «Файлы» (Files) можно узнать владельца базы данных или путь расположения файлов базы.
О том как изменить каталог расположения файлов системной базы данных tempdb я писал здесь.
Файлы базы данных resource имеют имена mssqlsystemresource.mdf и mssqlsystemresource.ldf и располагаются в каталоге с установленным экземпляром SQL Server. По умолчанию это
C:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\.