Анотація: Огляд всіх тонкощів роботи при створенні баз даних: від теорії до практичних дій. Первинні і вторинні файли даних, файли журналів транзакцій, групи файлів - все це необхідні знання, які допомагають більш ефективно організувати роботу бази даних, здатну до масштабованості, стійкості і швидкій роботі. Всілякі правила і рекомендації обмежують адміністратора від неправильних дій. Огляд чотирьох системних баз даних (master, tempdb, model, msdb). Приклади використання Create Database Wizard, Enterprise Manager, T-SQL дозволяє вибрати більш ефективний для вас спосіб створення бази даних. Особливу увагу приділено T-SQL, тому що його використання в наступних лекціях стане набагато ширше.
Структура бази даних
Кожна база даних SQL Server складається з набору файлів операційної системи. Ці файли можуть групуватися в групи файлів. що полегшує їх адміністрування, допомагає в розміщенні даних і підвищує продуктивність. В даному розділі ви познайомитеся з файлами і групами файлів SQL Server і дізнаєтеся про їхнє значення для створення баз даних.
Як ми вже сказали, база даних SQL Server складається з набору файлів операційної системи. Файл бази даних може бути або файлом даних. або файлом журналу. Файли даних служать для зберігання даних і об'єктів, таких як таблиці, індекси, уявлення, тригери і процедури. Є два типи файлів даних: первинні і вторинні. Файли журналів служать тільки для зберігання інформації з журналів транзакцій. Місце на диску, який надається на файлів журналів завжди має адмініструватися окремо від місця, відведеного для даних, і ніколи не повинно бути частиною файлу даних.
Кожна база даних повинна створюватися хоча б з одним файлом даних і з одним файлом журналу; файли не можуть бути використані більш ніж в одній базі даних - тобто бази даних не можуть розділяти файли (використовувати файли спільно). У наведеному нижче переліку вказані три типи файлів, які можуть бути використані в базах даних:
- Первинні файли даних. Первинні файли даних містять всю інформацію для запуску бази даних і її системних таблиць і об'єктів. Вони вказують на інші файли, створені в базі даних. Вони можуть також містити таблиці і об'єкти, що задаються користувачем, хоча це і не обов'язково. Кожна база даних може мати рівно один первинний файл. Для цих файлів рекомендується застосовувати розширення .mdf.
- Вторинні файли даних. Вторинні файли даних не є обов'язковими. Вони можуть зберігати дані і об'єкти, які відсутні в первинному файлі. База даних може взагалі не мати жодного втраченого файлу (якщо все її дані зберігаються в первинному файлі). Можна мати нуль, один або кілька вторинних файлів. Для деяких баз даних потрібно мати кілька вторинних файлів, щоб розміщувати дані по декільком окремим дискам. (Це не RAID-масиви дисків, як ви побачите з наступного розділу). Для цих файлів рекомендується застосовувати розширення .ndf.
- Файли журналів транзакцій. Файли журналів транзакцій зберігають всю інформацію з журналу транзакцій, що служить для відновлення бази даних. Кожна база даних повинна мати хоча б один файл журналу, а може мати і кілька файлів журналів. Для цих файлів рекомендується застосовувати розширення .ldf.
Примітка. Максимальний розмір файлів бази даних SQL Server становить 32 терабайта для файлів даних і 4 терабайта для файлів журналів.
Проста база даних може мати один первинний файл даних, досить великий, щоб у нього могли поміститися всі дані і об'єкти і один файл - журнал транзакцій. Складніша база даних може мати один первинний файл даних, п'ять вторинних файлів даних і два файли - журналу транзакцій.
Але як же дані зможуть розміщуватися по багатьом файлам даних? А ось для цього і застосовуються групи файлів.
Групи файлів
За допомогою груп файлів можна групувати файли, це потрібно для адміністрування і розміщення даних. (Групи файлів схожі на сегменти в Microsoft SQL Server 6.5 і в більш ранніх версіях.) Застосування груп файлів дозволяє підвищити продуктивність бази даних, тому що стає можливим створення бази даних, розміщеної на багатьох дисках, на багатьох контролерах і на RAID-масивах. (Про RAID-масиви см. "Конфігурація і планування підсистеми введення-виведення".) За допомогою груп файлів можна створювати таблиці і індекси, що розміщуються на заданих фізичних дисках, контролерах і масивах дисків. У даній лекції ми розглянемо деякі приклади такої роботи.
Є три типи груп файлів з наступними основними властивостями:
- Первинні групи файлів. Містять первинний файл даних і всі інші файли, які не вміщені в інші групи файлів. До первинної групі файлів бази даних віднесені системні таблиці, що задають користувачів, об'єкти і повноваження для цієї бази даних. SQL Server автоматично створює ці системні таблиці щоразу, коли ви створюєте базу даних.
- Призначені для користувача групи файлів. Всі групи файлів, задані користувачем в процесі створення (або подальшої зміни) бази даних. Створюючи таблицю або індекс, ви можете задати, щоб вони містилися в задану спеціальну групу файлів.
- Стандартна група файлів. Містить всі сторінки для таблиць і індексів, у яких при створенні була задана конкретна група файлів. За замовчуванням, стандартної групою файлів є первинна група файлів. Члени ролі db_owner можуть змінювати стандартну групу файлів, роблячи стандартної ту чи іншу групу файлів. У кожен момент часу стандартної може бути лише якась одна група файлів, і, повторимо, якщо стандартна група файлів не була задана явно, то первинна група файлів автоматично буде стандартною. Щоб змінити стандартну групу файлів, скористайтеся наступною командою Transact SQL (T-SQL):
(Застосовувати T-SQL ви навчитеся в останніх лекціях даного курсу.) Ви можете побажати змінити стандартну групу файлів так, щоб нею стала одна з ваших груп користувачів файлів, тоді всі об'єкти, створювані в вашій базі даних, будуть автоматично створюватися у зазначеній вами групі файлів, і вам не доведеться щоразу ставити це.
Щоб підвищити продуктивність, ви можете управляти розміщенням даних, створюючи таблиці і індекси в різних групах файлів. Так, ви можете побажати помістити таблицю, доступ до якої буває часто, в групу файлів на великому масиві дисків (наприклад, складеним з 10 дисків), а іншу таблицю, доступ до якої буває рідше, помістити в іншу групу файлів, розташовану на окремому, меншому масиві дисків (наприклад, з 4 дисків). Таким чином, можна розміщувати таблиці, доступ до яких відбувається частіше, по більшій кількості дисків, дозволяючи цим дискам здійснювати паралельний вхід-видобуток. Якщо ви не застосовуєте масиви RAID і у вас є кілька дискових накопичувачів, то у вас залишається можливість застосування груп файлів. Наприклад, ви можете створити по окремого файлу для кожного дискового накопичувача, розмістивши кожен файл в окрему спеціальну групу файлів. Тоді ви зможете помістити кожну таблицю або індекс в окремий файл (і на окремий диск), призначивши групу файлів при створенні цієї таблиці або індексу. Приклад розміщення файлів показаний на рис. 9.1. один первинний файл даних розміщений в первинній групі файлів на диску C, по одному вторинному файлу даних розміщено у кожній з груп користувачів файлів (FG1 і FG2) на дисках E і F і один файл-журнал розміщений на диску G. Після цього ви можете створювати таблиці і індекси в кожній з груп користувачів файлів - FG1 або FG2.
Мал. 9.1. Застосування груп файлів для управління розміщенням даних
А може бути, ви будете застосовувати спеціальну групу файлів для розподілу даних по декількох дисках. На рис. 9.2 показана для користувача група файлів FG1, що складається з двох вторинних файлів даних, один з яких знаходиться на диску E, а інший - на диску F (диску G розміщений файл-журнал, а на C - первинний файл). У цьому прикладі ми знову припускаємо, що кожен файл бази даних створено на окремому фізичному дисковому накопичувачі, і у нас немає апаратної реалізації RAID. Таблиці і індекси, створені в цій користувальницької групи файлів, будуть розміщені відразу на двох дисках, тому що SQL Server застосовує стратегію пропорційного витрачання ресурсів.
Мал. 9.2. Застосування однієї групи файлів для розподілу даних по декількох дисках
Якщо ви застосовуєте RAID-систему, то вам може знадобитися розподілити дані з великої таблиці по декількох логічних дисків-масивів, сконфігурованим на двох або на декількох RAID-контролерів. Для цього вам треба буде створити спеціальну групу файлів, з файлами, що відповідають кожному з цих контролерів. Припустимо, ви створили два вторинних файлу даних, кожен - на своєму масиві дисків, а кожен логічний масив складається з восьми фізичних дисків і налаштований як RAID 5. Ці два масиви обслуговуються двома окремими RAID-контролерами. Щоб створити таблицю або індекс, що розташовується на обох цих контролерах (тобто на всіх 16 дискових накопичувачах), створіть одну спеціальну групу файлів, в яку помістіть обидва файли, а потім створіть таблицю або індекс в цій групі файлів. Призначена для користувача група файлів FG1 розподілена по 16 фізичним дискам (двом логічним дискам - RAID-масивів) (див. Рис. 9.3). Там також показані первинний файл даних на іншому контролері (з RAID 1) і файл журналу ще на одному контролері (з RAID 10).
SQL Server дозволяє оптимізувати розподіл ваших даних по дискових накопичувачів, за рахунок автоматичного пропорційного розшарування (розподілу) даних по всіх файлів групи файлів. "Розшарування" (striping) - це термін, який застосовується для опису розподілу даних по декількох файлах бази даних. Розшарування файлів SQL Server працює незалежно від розшарування дисків RAID-масивів і, як ви бачили з наших прикладів, може застосовуватися як в поєднанні з RAID, так і самостійно.
Щоб забезпечити розшарування даних, SQL Server записує дані в файли в обсягах, пропорційних вільного місця, що залишається в файлах (щодо вільного місця в інших файлах). Місце для таблиць і індексів розподіляється у вигляді екстентів (extents). Екстент - це одиниця для вимірювання місця на диску, один екстент складається з 8 сторінок, а одна сторінка складається з 8 КБ, так що один екстент складається з 64 Кб. Припустимо, потрібно розподілити 5 екстентів на файл F1, в якому вільно 400 Мб, і на файл F2, в якому вільно 100 Мб; тоді 4 екстента будуть розподілені на файл F1 і один екстент розподілений на файл F2. Обидва файли заповняться до кінця приблизно одночасно, завдяки чому операції введення-виведення будуть розподілятися по дискам більш рівномірно. Пропорційне заповнення буде застосовуватися і для призначених для користувача, і для первинних груп файлів. Якщо задати всі файли в групі файлів мають однаковий початковий розмір, то дані, у міру їх завантаження, будуть розподілятися по файлах рівномірно. Цей метод, коли в групах створюються файли однакового початкового розміру, можна порекомендувати для рівномірного розподілу даних по дискових накопичувачів і, одночасно з цим, для рівномірного розподілу операцій введення-виведення.
Мал. 9.3. Розподіл користувальницької групи файлів по декількох RAID-контролерів