Створення бази даних

База даних є фізичною контейнером для всіх схем даних, а також для серверних програм База даних SQL Server являє собою одну логічну одиницю, навіть незважаючи на те, що фізично може розміщуватися в декількох файлах

Створення баз даних є одним з тих питань, в яких SQL Server вимагає зовсім небагато адміністративної роботи У той же час ви можете прийняти рішення довести файли бази даних до ідеалу, застосувавши складнішу технологію

Команда DDL create

Операція створення бази даних з використанням параметрів, заданих за замовчуванням, вкрай проста Наприклад, наступна команда DDL взята з навчальної бази даних Сарі Hatteras Adventures:

CREATE DATABASE CHA2

Інструкція CREATE створює файл даних із заданим імям і розширенням Mdf, а також файл журналу транзакцій з розширенням ldf

Природно, команда CREATE підтримує безліч параметрів За замовчуванням прийняті наступні

■ Порядок символів за замовчуванням – прийнятий на сервері

■ Початковий розмір За допомогою інструкції створюється файл обємом близько 2 Мбайт за допомогою Object Explorer створюється файл обємом 3 Мбайт Файл журналу транзакцій

при використанні коду має вихідний обсяг 0,5 Мбайт при створенні в Object Explorer – 1 Мбайт

■ Розміщення Файли даних і журналу транзакцій за замовчуванням розміщуються в каталозі, прийнятому в SQL Server за замовчуванням

Незважаючи на те що ці значення, прийняті за замовчуванням, можуть виявитися прийнятними для навчальних і тестових баз даних, вони абсолютно не адекватні для реальних промислових баз Вам будуть запропоновані більш практичні альтернативи

Створення баз даних у вікні Object Explorer вимагає тільки введення її імені у формі, показаної на рис 171 Дане вікно відкривається після клацання правою кнопкою миші на вузлі Database і вибору в контекстному меню пункту New Database

Додаткова Місце розміщення файлів даних і журналу транзакцій визначається у вікні інформація \ параметрів сервера Більш докладно це питання ми розглянемо в розділі 34

Рис 171 Найпростіше створити нову базу даних за допомогою введення імені на сторінці властивостей бази даних

Вікно Database Properties полягає їх декількох сторінок: General, Options і Filegroups Для існуючих баз даних в це вікно додаються сторінки Files, Permissions, Extended Properties, Mirroring і Log Shipping (на рис 171 вони не показані) Всі сторінки вікна Database Properties описані в табл 171

Таблиця 171 Сторінки вікна властивостей бази даних

Сторінка

Нова база даних

Існуюча база даних

General

Створення нової бази даних, налаштування імені, власника, упорядкування, моделі відновлення, повнотекстової індексації та властивостей файлу даних

Перегляд (тільки читання) загальних властивостей: імені, дати останнього резервування, розміру та впорядкування

Сторінка

Нова база даних

Існуюча база даних

Files

Немає даних

Перегляд і зміна власника бази даних, упорядкування, моделі відновлення, повнотекстової індексації та файлів бази даних

Filegroups

Перегляд і зміна інформації про файлових групах

Перегляд інформації про файлових групах

Options

Перегляд і модифікація параметрів бази даних, таких як автосжатіе, налаштування ANSI, метод верифікації сторінок і монопольного доступу

Перегляд і модифікація параметрів бази даних, таких як автосжатіе, налаштування ANSI, метод верифікації сторінок і монопольного доступу

Permissions

Немає даних

Перегляд і модифікація серверних ролей, користувачів і дозволів Більш докладно цю тему ми торкнемося в главі 40

Extended

Properties

Немає даних

Перегляд і модифікація розширених властивостей

Mirroring

Немає даних

Перегляд і конфігурація дзеркального відображення бази даних (див главу 52)

Transaction Log Shipping

Немає даних

Налаштування доставки журналу транзакцій

Концепції файлів бази даних

База даних складається з двох файлів (точніше, з двох наборів файлів): файлу даних і журналу транзакцій Файл даних містить системні і користувальницькі таблиці, індекси, подання, збережені процедури, користувача функції, тригери і дозволу системи безпеки Журнал транзакцій з послідовною записом є центром SQL Server Всі оновлення даних спочатку записуються в журнал транзакцій і перевіряються, що гарантує запис оновлень в двох місцях

Ніколи не зберігайте журнал транзакцій в тій же дискової системі, що і файл Увага даних З міркувань цілісності транзакцій і можливості відновлення бази даних необхідно звести ймовірність одночасного пошкодження файлу даних і журналу транзакцій до мінімуму

Журнал транзакцій містить не тільки користувача записи, а й індексні записи, розбиття сторінок, реорганізації таблиць і багато іншого Після одного інтенсивного тесту оновлень я перевірив журнал за допомогою програми Log Explorer компанії Lumigent і був немало здивований, коли виявив, що обсяг записів про діяльність системи займає в ньому близько 80% Так як файл транзакцій містить не тільки поточну інформацію, а й всі оновлення файлу даних, він, як правило, весь час розростається

Додаткова Адміністрування журналу транзакцій включає в себе операції резервується-ікформацйй вання як частини загального плану відновлення (див главу 36) Те, як сервер ис-помагає журнал у транзакціях, ми обговоримо в главі 51

Автоматизація зростання розміру файлу

До виходу версії SQL Server 7 доводилося вручну управляти обємом файлів даних, щоб мати можливість додавання даних На щастя, в нових версіях SQL Server цей процес автоматизований при цьому ви можете встановити наступні параметри

■ Enable Autogrowth У міру того як база наповнюється даними, розмір файлу повинен збільшуватися Якщо автоматичне збільшення розміру не включене, адміністратор повинен змінювати розміри файлу вручну Якщо ж цей параметр встановлений, то це завдання бере на себе SQL Server, враховуючи такі параметри

• File growth in megabytes Коли файл даних вимагає розширення, він автоматично збільшується на задану кількість мегабайт Збільшувати розмір на фіксоване число мегабайт має сенс у великих базах даних Якщо збільшення обсягу файлу передбачувано, то кращий спосіб – встановити цей параметр в значення, приблизно рівне щотижневому збільшенню обсягу даних

• File grouth by percent У разі потреби розмір файлу даних автоматично збільшується на заданий відсоток Цей варіант краще використовувати в невеликих базах даних У великих базах даних установка цього параметра може призвести до занадто великого збільшення займаного файлом місця на диску за одну операцію, що може сильно позначитися на продуктивності Наприклад, додавання 10% розміру до файлу обємом в 5 Гбайт призведе до запису 500 Мбайт інформації, що може протривати досить довго

■ Maximum file size Установка цього параметра не дозволить переповнитися дискової підсистемі в іншому випадку переповнення могло б привести до збою операційної системи

На рис 172 показано вікно конфігурування файлу даних

Максимально можливий обсяг файлу даних становить 16 Тбайт, в той же час обсяг файлу журналу транзакцій обмежений 2 Тбайт Однак це анітрохи не обмежує розмір бази даних, так як вона може складатися з декількох файлів

Встановити автоматичне збільшення обсягу файлу можна і програмним шляхом, в інструкції CREATE DETABASE Цей обсяг може бути встановлений в кілобайтах (Кбайт), мегабайтах (Мбайт), гігабайтах (Гбайт) і терабайт (Тбайт) За замовчуванням прийняті мегабайти Як і у випадку використання утиліти Management Studio, цей обсяг може бути визначений в абсолютних і процентних одиницях У наступному прикладі створюється база даних NewDB з початковим обсягом файлу даних в 10 Мбайт, максимальним обємом в 2 Гбайт і приростом в 10 Мбайт Файл журналу транзакцій має вихідний розмір 5 Мбайт, максимальний –

1 Гбайт і крок збільшення – 10%

CREATE DATABASE NewDB

PRIMARY

(NAME = NewDB,

FILENAME = з: \ SQLData \ NewDBmdf,

SIZE = 10MB,

MAXSIZE = 2Gb,

FILEGROWTH = 20)

LOG ON

(NAME = NewDBLog,

FILENAME = d:\SQLLog\NewDBLogldf1,

SIZE = 5MB,

MAXSIZE = 1Gb,

FILEGROWTH = 10%)

Якщо автоматичне збільшення обєму не включено, то буде потрібно вручну коригувати обсяг файлу при необхідності додавання в них даних У цьому випадку можна також скористатися діалоговим вікном параметрів бази даних утиліти Management Studio та інструкцією ALTER DATABASE з параметром MODIFY FILE Наступна інструкція змінює обсяг файлу даних бази NewDB до 25 Мбайт:

ALTER DATABASE NewDB MODIFY FILE

(Name = NewDB,

SIZE = 25MB,

MAXSIZE = 2Gb,

FILEGROWTH = 0)

Щоб перерахувати бази даних програмним шляхом, скористайтеся поданням каталогу sysdatabases

Використання безлічі файлів

Як дані, так і журнал транзакцій можуть зберігатися в безлічі файлів Це дозволить збільшити швидкодію і відкрити нові перспективи для збільшення обсягу файлів Будь додатковий (або вторинний) файл даних має за замовчуванням розширення ndf Якщо база використовує безліч файлів даних, то системні таблиці будуть зберігатися в першому з них (тобто первинному)

Незважаючи на неможливість управління місцем розміщення таблиць та індексів, ця методика дозволяє зменшити навантаження на кожну з використовуваних дискових підсистем, задіюючи одночасно кілька файлів (Пропорційно наявному у них в наявності вільного місця) Так як СУБД SQL Server сама управляє робочої завантаженням дискових підсистем, рядки однієї таблиці можуть бути розподілені по декількох файлах Якщо в базі даних автоматизовано збільшення обсягу файлів, він буде виконуватися тільки після того, як всі існуючі обсяги будуть заповнені

з декількома файлами

Для того щоб створити базу даних з декількома файлами в Management Studio, додавайте імена файлів в таблицю Database files на сторінці Files діалогового вікна параметрів бази даних (рис 173)

При програмному створенні бази даних з декількома файлами додайте їх місце розміщення в інструкцію CREATE DATABASE

CREATE DATABASE NewDB ON

PRIMARY

(NAME = NewDB,

FILENAME = e:\SQLData\NewDBmdf1),

Результат буде наступним:

LOG ON

(NAME = NewDBLog,

FILENAME = g:\SQLLog\NewDBLogldf1), (NAME = NewDBLog2/

FILENAME = 1h:\SQLLog\NewDBLog2ldf)

Puc 173 з декількома файлами в Management Studio

(NAME = NewDB2,

FILENAME = 1f:\SQLData\NewDB2ndf)

The CREATE DATABASE process is allocating 063 MB on disk NewDB1

The CREATE DATABASE process is allocating

10&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp MB on disk 1NewDB21

The CREATE DATABASE process is allocating

10&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp MB on disk NewDBLog1

The CREATE DATABASE process is allocating

10&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp MB on disk NewDBLog2.

Для перерахування файлів, використовуваних поточною базою даних, можете виконати запит до подання каталогу sysdatabases

Зміна файлів поточної бази даних

Кількість файлів, що використовуються поточною базою даних, можна легко змінити Якщо дані заповнили пристрій, ви можете додати новий файл в таблиці Database Files (мал 173) так само, як і при створенні бази даних

У програмному коді можна виконати ту ж операцію за допомогою інструкції ALTER DATABASE з параметром ADD FILE Синтаксис цієї інструкції аналогічний використовувалася при створенні бази даних У наступному прикладі до бази даних NewDB додається третій файл:

ALTER DATABASE NewDB ADD FILE (NAME = NewDB3,

FILENAME = i:\SQLData\NewDB3ndf,

SIZE = 10MB,

MAXSIZE = 2Gb,

FILEGROWTH = 20)

Результат виконання інструкції:

Extending database by 1000 MB on disk NewDB31

Якщо деякий файл більше не потрібно або унаслідок виведення з експлуатації дискової подсісітеми, або через її використання для інших цілей, файл даних або журналу транзакцій може бути видалений Для цього його потрібно спочатку стиснути за допомогою функції DBCC ShrinkFile, а потім видалити в утиліті Management Studio (для цього потрібно виділити його і натиснути клавішу )

Додаткові файли можна також видалити і програмним шляхом за допомогою інструкції ALTER DATABASE REMOVE FILE Наступна інструкція видаляє створений раніше третього файл бази даних NewDB:

DBCC SHRINKFILE (NewDB3, EMPTYFILE)

ALTER DATABASE NewDB REMOVE FILE NewDB3

Результат виконання пакета наступний:

Dbld Fileld CurrentSize MinimumSize UsedPages EstimatedPages

12 5     1280                     1280                     0                      0

Файл NewDB 3 був видалений

Планування декількох файлових груп

Файлова група являє собою розширений механізм організації обєктів бази даних За замовчуванням база даних має всього одну файлову групу – первинну При конфігуруванні бази даних для використання декількох файлових груп конкретні обєкти (таблиці, індекси тощо) можуть створюватися в заданих групах Ця методика може підтримувати дві основні стратегії

■ Використання декількох файлових груп здатне підвищити продуктивність за рахунок розділення таблиць та індексів на різні дискові підсистеми

■ Використання декількох файлових груп може допомогти організувати план резервування і відновлення, поміщаючи статичні дані в одну файлову групу і активізовані дані – в іншу

| Найпростіше визначити склад файлів бази даних та їх розміри наступним cVc чином: виконати запит до подання каталогу sysdababase_f iles

При плануванні файлів і файлових груп можна виграти в продуктивності від приміщення кожного файлу в окрему дискову підсистему Особисто я настійно рекомендую розподіляти завдання введення-виведення по декількох дисковим приводам

Однак перед тим як призначати додаткові дискові підсистеми файлів даних, обовязково задовольните потреби журналу транзакцій і тимчасової бази даних В ідеальному випадку краще мати пять дискових підсистем (для Windows, додатків і файлу підкачки для тимчасової бази даних tempdb і для її журналу а також для файлу даних і журналу транзакцій), перш ніж створювати додаткові файли та файлові групи

з файловими групами

Щоб додати до бази даних файлову групу в Management Studio, відкрийте сторінку параметрів бази даних в Object Explorer На сторінці Filegroups створіть додаткові файлові групи Після цього на сторінці Files створюйте нові файли і вибирайте для них в комбінованому списку конкретні файлові групи

Щоб виконати ту ж операцію програмним шляхом, використовуйте параметр Filegroups У наступному прикладі створюється база даних NewDB з двома файловими групами:

CREATE DATABASE NewDB ON

PRIMARY

(NAME = NewDB,

FILENAME = d:\SQLData\NewDBmdf,

SIZE = 50MB,

MAXSIZE = 5Gb,

FILEGROWTH = 25MB),

FILEGROUP GroupTwo

(NAME = NewDBGroup 2,

FILENAME = e:\SQLData\NewDBTwondf1,

SIZE = 50MB,

MAXSIZE = 5Gb,

FILEGROWTH = 25MB)

LOG ON

(NAME = NewDBLog,

FILENAME = f:\SQLLog\NewDBLogndf,

SIZE = 100MB,

MAXSIZE = 25Gb,

FILEGROWTH = 25MB)

Модифікація файлових груп

Файлові групи можна змінювати точно так само, як і файли У Management Studio можна створювати нові файлові групи, додавати і видаляти в них файли, а також видаляти порожні групи Однак видалити файлову групу дещо складніше, ніж стиснути файл Якщо в файлової групі містяться які-небудь дані, то стиснення файлу призведе до переміщення його даних в інший файл тієї ж групи Перед видаленням файлової групи потрібно видалити з неї таблиці та індекси

За допомогою редактора запитів та коду Т-SQL можна створювати і видаляти файлові групи Для цього використовують інструкції ALTER DATABASE ADD FILEGROUP і ALTER DATABASE REMOVE FILEGROUP Вони мають практично такий же синтаксис, як інструкції додавання і видалення файлів

Видалення бази даних

База даних може бути видалена з сервера за допомогою виділення її в Object Explorer і вибору в контекстному меню команди Delete

Для програмного видалення бази даних використовуйте інструкцію DROP DATABASE: DROP DATABASE NewDB

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*