1. Нові можливості SQL Server 2005 змінять життя адміністраторів на краще Привнесені в SQL Server 2005...
2. Знімки бази даних
3. Версії рядків і ізоляція знімків
4. Вбудована підтримка роботи з розділами
5. Моніторинг з використанням DMV і DMF
6. Безпека
7. Удосконалення T-SQL
8. Що дає SQL Server 2005 адміністраторам баз даних?

Нові можливості SQL Server 2005 змінять життя адміністраторів на краще

Привнесені в SQL Server 2005 удосконалення та додаткові функції вплинуть на всі аспекти використання продукту і дозволять створювати абсолютно нові інфраструктури та платформи. Ця стаття присвячена новим функціям і засобам, адресованим адміністраторам баз даних. Приступаючи до написання статті, я склав список функцій, які зазнали значних змін, а також нових функцій і без праці нарахував більше десятка таких, про які варто було б розповісти. І тут я зрозумів, що є ризик, як то кажуть, за деревами не помітити лісу. Тому я вирішив, що необхідно висвітлити два основних моменти. По-перше, ті переваги, які змушують задуматися про перехід на SQL Server 2005, і, по-друге, який вплив відразу нададуть нові функції на завдання повсякденного управління і супроводу баз даних. Я не буду зупинятися на поліпшення в інструментальних засобах - це досить докладно описано в статті Келен Діланом і Рона Телмеджа «Засоби управління SQL Server 2005».

Віддзеркалення бази даних

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

У редакціях SQL Server 2000 Enterprise і SQL Server Developer для підтримки резервного сервера використовувався перенесення журналу транзакцій. Ця практика набула широкого поширення, так як підхід до організації резервного сервера тут досить простий і не вимагає використання спеціалізованого обладнання. Однак слід зазначити, що перенесення журналу транзакцій є гібридом технологій, які з самого початку не були призначені для вирішення цього завдання. По суті справи, перенесення журналу транзакцій полягає в збереженні журналу транзакцій на вихідному сервері, копіювання цієї резервної копії на цільової (резервний) сервер. Для виконання таких операцій налаштовується SQL Agent. Слід зазначити, що технологія перенесення журналів характеризується деякими обмеженнями.

• Витрати часу на збереження / копіювання / завантаження журналів викликають неминучі затримки.
• Неможливо організувати роботу в синхронному режимі, якщо транзакція не підтверджена на вихідному сервері або якщо вона не була прийнята на резервному сервері.
• У разі відмови не відбувається автоматичне перемикання на резервний сервер.
• Потенційно існує кілька точок відмови, причому в разі відмови настройка заново перенесення журналу транзакцій може виявитися складним завданням.

Віддзеркалення баз даних є штатною функцією і дозволяє усунути всі перераховані вище недоліки, пов'язані з організацією резервного сервера.

При організації зеркалирования використовуються три сервера. На основному (principal) розміщується головна база даних. Сервер резерву (mirror) містить копію. Третій сервер, свідок (witness), є необов'язковим і дозволяє організувати автоматичне перемикання. Для автоматичного перемикання клієнтів в разі аварійного резервування використовується нова функція автоматичного перенаправлення клієнтів. Для організації зеркалирования бази не потрібне використання спеціалізованого апаратного забезпечення, а сам процес налаштування дивно простий. Замість копіювання файлів журналів при створення дзеркал між серверами встановлюється спеціалізований сеанс зеркалирования, який використовується для передачі між серверами окремих записів транзакцій. Дзеркальна копія бази даних знаходиться в режимі non-recovered - це означає, що вона недоступна для читання. На рис. 1 приведена схема топології для віддзеркалення серверів. Є також можливість організації доступу для читання даних з дзеркальної копії з використанням знімків (snapshot) бази даних (докладніше про знімках буде розказано нижче).

Сеанс зеркалирования може бути налаштований для роботи в синхронному або асинхронному режимі. У синхронному режимі зміни в базі основного сервера завершуються тільки в тому випадку, коли завершена запис цих змін в базу даних на резервному сервері. В цьому режимі затримки визначаються швидкістю передачі даних по мережі і обсягом змін. Асинхронний режим дозволяє домогтися збільшення швидкості роботи і скоротити затримки, оскільки завершення змін основним сервером здійснюється без підтвердження від резервної бази даних. Більш докладні відомості про створення дзеркал баз даних можна знайти в статті Рона Телмеджа «Database Mirroring in SQL Server 2005 - Віддзеркалення баз даних в SQL Server 2005», яка доступна на сайті Microsoft за адресою http://www.microsoft.com/technet/prodtechnol/ sql / 2005 / dbmirror.mspx .

Важливе зауваження. Оскільки, за даними Microsoft, в ході початкового бета-тестування технологія зеркалирования баз даних була випробувана меншим числом користувачів, ніж очікувалося, і, відповідно, отримані відомості не забезпечують повної впевненості в абсолютній надійності цього засобу забезпечення високої відмовостійкості баз даних, було прийнято рішення про необхідності продовження тестування, перш ніж цю технологію можна буде зробити загальнодоступною. Технологія успішно витримала розширене внутрішнє тестування, і в Microsoft було прийнято рішення зробити її широко доступною в першій половині 2006 року. В остаточній версії для виробництва SQL Server 2005 технологія зеркалирования баз даних буде за замовчуванням відключена, але буде доступна для випробування в режимі трасування. Microsoft продовжить «польові випробування» із зацікавленими замовниками і зробить технологію зеркалирования широко доступною, як тільки це стане можливим.

Знімки бази даних

Знімки бази даних являють собою нову функціональність SQL Server 2005, яка дозволить підвищити надійність і відмовостійкість бази даних. Знімок - це спеціальний об'єкт, який представляє собою доступну тільки для читання копію бази даних в певний момент часу. Можна створити знімок-копію стану бази даних в певний момент часу, привласнити цьому знімку ім'я і призначити власні імена файлів даних для кожного з файлів даних вихідної бази даних. Оскільки знімки доступні тільки для читання і не можуть бути змінені за визначенням, файли журналів для них створені не будуть. Знімок бази даних гранично ефективно використовує дисковий простір. Створювані файли даних спочатку порожні і не містять ніяких даних. Копіювання сторінок даних в файли знімка відбувається тільки в момент зміни сторінок в вихідній базі даних. При повторному зміні тих же сторінок в вихідній базі додаткове копіювання в файл знімка не відбувається - зрозуміло чому.

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

SQL Server 2005 дозволяє створювати множинні знімки бази даних в різні моменти часу. Можна налаштувати періодичне створення знімків - кожен день або кожну годину. У разі ненавмисного видалення даних їх можна відновити з найбільш свіжого знімка. Можна створювати знімки бази даних перед виконанням складних завдань і запитів, в результаті яких дані можуть бути зіпсовані. Наприклад, якийсь процес може виявитися занадто складним, щоб укладати його в одну транзакцію. В цьому випадку можна зробити знімок, і в разі збою при виконанні процесу можна буде повернутися до вихідного стану - за умови, що в проміжку були зроблені інші знімки. Використання знімків незамінне, якщо вам необхідно формувати звіти за всіма даними в базі як вони є в певні моменти часу.

Нарешті, знімки бази даних можна створювати на резервної дзеркальної базі даних (в такому випадку дзеркальна база стає доступною для читання), це дозволяє значно знизити навантаження на основну базу даних.

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

Версії рядків і ізоляція знімків

У SQL Server 2000 існує тільки одна версія рядки даних - поточна, т. Е. Актуальна, найбільш свіжа. У SQL Server 2005 представлена ​​нова технологія, що дозволяє зберігати різні версії рядка у вигляді списку значень, що зберігаються в базі tempdb. Завдяки цьому досягається відразу кілька цілей: забезпечення різних рівнів ізоляції знімків, побудова вставлених і віддалених таблиць в тригерах і онлайн-операції з індексами, а також підтримка технології Multiple Active Result Sets (MARS).

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

У SQL Server 2005 також представлений новий рівень ізоляції виконання, заснований на версійності рядків даних. Цей рівень ізоляції, званий Read Committed with Snapshot (підтвердження читання з знімка), являє собою вдосконалений варіант існуючого рівня ізоляції. В цьому режимі ізоляції виконання транзакції послідовні запити SELECT повертатимуть дійсні підтверджені значення рядка даних. Іншими словами, послідовні операції читання, що виконуються в рамках однієї транзакції, можуть повертати різні версії значень однієї і тієї ж рядки даних, причому всі вони будуть підтвердженими. Що Їх операції читання можуть не чекати завершення операцій зміни даних.

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

Версійність рядків даних використовується в SQL Server 2005 для побудови в тригерах вставлених і віддалених таблиць. У SQL Server 2000 вставлені і віддалені таблиці є уявленнями розділу журналу транзакцій, що містять зміни даних, що викликали виконання тригерів. Виконуючи запити про вставлених і віддалених даних, можна отримати версії даних станом на момент до і після виконання змін. Таким чином, обробка цих запитів в дійсності є скануванням окремих ділянок журналу транзакцій. Навіть без урахування тригерів таке використання журналу транзакцій може виявитися вузьким місцем, особливо для додатків оперативної обробки транзакцій OLTP (online transaction processing). Запис в журнал транзакцій SQL Server може виконуватися тільки послідовно. Тому втручання в роботу з журналом транзакцій (а саме це відбувається при запиті вставлених і віддалених в тригерах і при реплікації журналу транзакцій) в кінцевому рахунку викликає затримки в збереженні змін даних. При проектуванні і оптимізації бази даних завжди потрібно враховувати особливості і механізми роботи журналу транзакцій.

SQL Server 2005 використовує версійність рядків для відстеження змін, які викликають виконання тригерів і виконуються в самих триггерах. SQL Server будує таблиці вставлених і віддалених з пов'язаних списків версій рядків даних (ці пов'язані списки зберігаються в tempdb). На відміну від журналу транзакцій, читання і запис tempdb можуть здійснюватися в паралельному режимі, якщо база tempdb розподілена між кількома дисковими накопичувачами. Так, властиві журналу транзакцій обмеження вдалося знизити, але натомість потрібно оптимізувати базу tempdb.

Ще одне важливе нововведення, яке використовує технологію версійності рядків даних, - це онлайнові операції з індексами. У SQL Server 2000 всі операції з індексами (в тому числі створення, перебудова і видалення) виробляються в автономному режимі. Необхідно періодично виконувати перебудову індексів для запобігання їх фрагментації (фрагментація значно уповільнює виконання упорядкованого сканування). Для виконання перебудови кластерного індексу SQL Server здійснює ексклюзивну блокування таблиці, так що таблиця виявляється недоступна для читання і запису. При перебудові звичайного індексу виконується блокування таблиці без монополізації, т. Е. Таблиця для запису недоступна, а сам перебудовується індекс виявляється недоступний на час виконання операції. У SQL Server 2000 є утиліта для оперативної дефрагментації індексів (DBCC INDEXDEFRAG), що дозволяє обійти деякі проблеми, пов'язані з перебудовою індексів. Однак ця утиліта іноді вимагає для виконання більше часу, ніж звичайна перебудова індексу, витрачає більше ресурсів журналу транзакцій, а результат її роботи за якістю відстає від повної перебудови індексу.

У SQL Server 2005 введені оперативні операції з індексами (створення, перебудова, видалення). Я вважаю, що це важливий крок вперед, оскільки управління індексами має велике значення для організації регулярного супроводу баз даних. SQL Server використовує версійність рядків даних для забезпечення можливості виконання змін, які відбуваються під час перебудови індексів. Це можна уявити як побудова другої копії індексу, яка заміщає перший індекс після завершення побудови. При цьому під час створення копії індексу дані і вихідний індекс залишаються доступними для використання. Звичайно, ця операція вимагає наявності достатнього дискового простору. Поява подібних операцій з індексами буде особливо затребувана додатками, які вимагають безперервної відмовостійкості даних в режимі роботи 7х24, які не можуть допускати простою в операціях.

Вбудована підтримка роботи з розділами

Робота з розділами не є новою концепцією в SQL Server 2005. Масштабування і супровід величезних таблиць даних може доставити масу клопоту адміністраторам баз даних, оскільки при цьому потрібно перебудовувати індекси, боротися з фрагментацією, архівувати і видаляти застарілі дані - все це вельми трудомісткі операції, що віднімають багато часу і вимагають уваги і акуратності. Зі збільшенням обсягу таблиць виконання завантаження даних починає вимагати все більше часу через необхідність підтримки різних індексів. В якості одного із способів обходу описаних труднощів адміністратори баз даних використовують розділи - це дозволяє фізично розділити занадто великі таблиці даних на групу менших за розміром одиниць управління, якими простіше управляти. Таким чином, використання розділів дозволяє забезпечити масштабування даних.

Робота з розділами в SQL Server 2000 реалізується як гібрид кількох елементів, які не призначалися для цієї мети, а саме таблиць, перевірочних обмежень, уявлень і деяких додаткових прийомів. Для роботи з розділами в SQL Server 2000 потрібно виконати наступні дії.

• Створення груп таблиць менше розміру вместо однієї Великої.
• Обмеження діапазону, підтрімуваного кожної з таблиць с помощью механізму перевірочніх обмежень.
• Створення уявлень для об'єднання окремий таблиць в єдине ціле, подібне віхідної Великій табліці, для Додатків и Користувачів.

Той факт, що в SQL Server 2000 відсутня вбудована підтримка секціонування, накладає суттєві обмеження на управління розбитими на розділи даними і істотно знижує продуктивність. Адміністраторам баз даних доводиться мати справу з великою кількістю об'єктів управління замість одного. Кожен розділ представляє собою окрему таблицю, так що можуть виникнути розбіжності в схемах даних та індексування розділів. Оптимізатор SQL Server окремо обробляє розділи при побудові плану виконання - це призводить до великих витрат часу на побудову плану, а сам він виявляється більш складним. Ефективно використовувати час, що використовують уявлення для об'єднання безлічі таблиць (див. Рис. 2), вимагає виконання певних умов і накладає суттєві обмеження.

У SQL Server 2005 реалізована вбудована підтримка секціонування на розділи для таблиць і індексів, що дозволяє усунути багато обмежень, властиві роботі з розділами в SQL Server 2000. Тепер можна створити один об'єкт, таблицю або індекс, що мають внутрішнє розбиття на розділи, як показано на рис . 3. Робота здійснюється як зі звичайним об'єктом, що дозволяє значно знизити накладні витрати на управління та супровід. Створення, видалення і перемикання розділів здійснюється безпосередньо новими вбудованими командами SQL Server 2005. Усунуто більшість обмежень, пов'язаних з читанням і зміною даних. Крім того, було досягнуто значного спрощення завдання оптимізатора SQL Server; оскільки всі розділи таблиці мають ідентичну схему даних і індекси, плани виконання виявляються, як правило, значно простіше.

Моніторинг з використанням DMV і DMF

У SQL Server 2005 представлено 70 нових динамічних уявлень для управління (Dynamic Management Views, DMV) і динамічних функцій управління (Dynamic Management Functions, DMF), які забезпечують доступ до інформації про поточний стан сервера SQL Server, його продуктивності, дозволяють виконувати діагностику проблем і налаштовувати продуктивність примірників сервера і баз даних.

Вирішення проблем тепер легко доступна, повністю документована і представляється у вигляді звичайних таблиць. Деяка частина цієї інформації була взагалі недоступна в SQL Server 2000, частина була документована, але не була представлена ​​в табличному вигляді (наприклад, відомості про фрагментацію DBCC SHOWCONTIG), а частина - доступна тільки через недокументовані кошти (інформація про очікування DBCC SQLPERF (WAITSTATS) ).

Нові DMV і DMF подають відомості про двох контекстах - контексті бази даних і контексті сервера. Нові динамічні об'єкти управління представляють наступні категорії діагностики: Common Language Runtime (загальна середовище виконання), Database Mirroring (віддзеркалення баз даних), Databases (бази даних), Execution (виконання), Full-Text Search (повнотекстовий пошук), Indexing (індексація) , I / O (введення / виведення), Query Notifications (повідомлення про запити), Replication (реплікація), Service Broker (брокер служб), SQL Operating System (операційна система SQL) і Transactions (транзакції). Повна інформація про ці об'єкти доступна в оперативній документації SQL Server 2005 Books Online в розділі Dynamic Management Object (динамічні об'єкти управління). Не варто шкодувати часу на вивчення цих об'єктів - надається ними інформація має величезну цінність.

Безпека

У SQL Server 2005 значно удосконалені засоби безпеки. Всі дозволи тепер надаються за допомогою пропозиції GRANT. У SQL Server 2000 деякі дозволу надавалися безпосередньо, а деякі можна було призначити тільки непрямим чином, наприклад через призначення ролі. Управління безпекою в SQL Server 2005 побудовано за ієрархічним принципом і зажадає звикання до нового словника. Наприклад, терміном securables (об'єкти захисту) позначаються об'єкти (entity), доступ до яких може бути надано або обмежений за допомогою дозволів (це реєстрація в базі даних на рівні сервера, складання або служби на рівні бази даних або суті низького рівня, такі як тип на рівні схеми або таблиця на рівні об'єктів). Дозволи над об'єктами захисту securables надаються принципалам (principals) т. Е. Групам / облікових записів Windows, ролям / облікових записів SQL Server, ролям / користувачам баз даних.

SQL Server 2005 використовує коректне поняття схеми, як воно визначено комітетом ANSI, і повністю відокремлює користувачів бази даних від схем. У SQL Server 2000 схема, до якої належать об'єкт і користувач бази даних, який створив об'єкт, не можуть бути відокремлені, т. Е. Якщо користувач user1 створив таблицю Table1, таблиця має ім'я user1.Table1. Якщо потрібно видалити користувача user1 (наприклад, через звільнення), необхідно буде спочатку змінити власника для всіх об'єктів, створених користувачем user1, і призначити їх іншим користувачам. SQL Server 2005 дозволяє створювати об'єкти в схемах, при цьому схема не містить відомостей про користувача бази даних. Можна надавати, забороняти і відкликати дозволи доступу до схеми (securable в термінології SQL Server 2005) у принципалів. Ніяких проблем з видаленням користувачів не виникає, адже користувачі не є власниками об'єктів. Крім того, досягається значно більша гнучкість в управлінні захистом колекцій об'єктів (т. Е. Схемами).

Ще одне вдосконалення, якого давно чекали адміністратори баз даних, - поява в базі вбудованих засобів шифрування даних. SQL Server 2005 являє нові засоби, що дозволяють управляти контекстом безпеки для команд і процедур з використанням пропозиції EXECUTE AS. EXECUTE AS замінює старе пропозицію SETUSER і забезпечує більшу гнучкість для зміни контексту безпеки. Більш докладні відомості про зміни в системі безпеки SQL Server 2005 містяться в бібліотеці SQL Server 2005 Books Online. Також можна прочитати статтю «Introduction to SQL Server 2005 Relational Engine Security Features» ( http://www.microsoft.com/ technet / prodtechnol / sql / 2005 / sqlsysec.mspx ).

Удосконалення T-SQL

Якщо в сферу ваших обов'язків входить розробка, аналіз і супровід коду T-SQL, ви знайдете для себе в SQL Server 2005 масу нового і цікавого. Досить докладно зміни T-SQL описані в статті «T-SQL 2005», яка доступна на сайті SQL Server Magazine за посиланням http://www.windowsitpro.com/departments/ DepartmentID / 946 / 946.html . Тут я згадаю тільки найбільш важливі і цікаві зміни в мові опису даних Data Definition Language (DDL) і мовою маніпулювання даними Data Manipulation Language (DML).

У SQL Server 2005 були введені тригери DDL, які дозволяють відкидати або видавати іншу реакцію на події DDL. Тригери дозволяють реагувати на події рівня сервера (наприклад, ALTER LOGIN) або рівня бази даних (такі, як CREATE TABLE). Результат використання таких тригерів важко переоцінити - наприклад, у такий спосіб можна встановити тверде дотримання політик іменування об'єктів, аудиту, змін версії схеми і забезпечити виконання безлічі інших операцій.

У SQL Server 2005 введений новий тип даних XML, що дозволяє зберігати дані XML в базі даних і використовувати XML в локальних змінних і вхідних / вихідних аргументах процедур. Дані XML можуть бути проіндексовані з допомогою спеціальних індексів XML, можна задіяти спеціальні обмеження XML і виконувати запити і зміна даних з використанням мови XQuery.

Зміни торкнулися і типів даних змінного розміру (VARCHAR, NVARCHAR, VARBINARY) - тепер можна задіяти описатель MAX для визначення максимального розміру елемента замість точної вказівки розміру. За допомогою описателя MAX SQL Server може самостійно визначати, чи використовувати такі дані як звичайне значення або великий об'єкт. Особливо зручно те, що для нових типів уніфіковано звернення в програмуванні для регулярних типів і великих об'єктів. Можна використовувати нові типи як локальних змінних, параметрів процедур і виконувати більшість операцій, які можна застосувати до звичайних типів даних.

Також не можна не згадати два розширення мови запитів - Analytical Ranking Functions і Recursive Queries. Аналітичні функції дозволяють отримувати номери рядків і інші значення порівняння для рядків в результуючому наборі. Використання цих функцій дозволило мені провести настроювання і налагодження багатьох рішень. Рекурсивні запити дуже зручні при обробці і маніпулюванні ієрархічними даними.

Іцик Бен-Ган - Cтарший викладач на курсах по SQL Server в коледжі Hi-Tech в Ізраїлі. Має сертифікати MCDBA, MCSE + I, MCSD, MCT і SQL Server MVP. Глава ізраїльської групи користувачів SQL Server. [email protected]