10 Самих корисних функцій sql server 2018 для адміністратора баз даних

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

За допомогою монітора активності адміністратор також може виконувати наступні завдання:

· Припиняти і відновлювати роботу монітора активності одним клацанням правої кнопки миші. Це дозволяє адміністратору «зберегти» відомості про стан на певний момент часу, вони не будуть оновлені або перезаписані. Але не забувайте, що при оновленні даних вручну, розгортанні або згортання розділу старі дані будуть оновлені і загублені.

· Клацніть правою кнопкою миші елемент рядка, щоб відобразити повний текст запиту або графічний план виконання за допомогою пункту меню «Останні ресурсомісткі запити».

· Виконувати трасування додатком Profiler або завершувати процеси в поданні «Процеси». Події додатки Profiler включають події RPC: Completed. SQL: BatchStarting і SQL: BatchCompleted. а також AuditLogin і AuditLogout.

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

Існують групи дій аудиту на рівні сервера, наприклад, такі:

· FAILED_LOGIN_GROUP відстежує невдалі спроби входу в систему.

· BACKUP_RESTORE_GROUP повідомляє, коли створювалася резервна копія бази даних або виконувалося її відновлення.

· DATABASE_CHANGE_GROUP проводить аудит часу створення, зміни або видалення бази даних.

Групи дій аудиту на рівні бази даних включають такі:

· DATABASE_OBJECT_ACCESS_GROUP викликається при кожному виконанні інструкції CREATE, ALTER або DROP для об'єкта бази даних.

· DATABASE_OBJECT_PERMISSION_CHANGE_GROUP викликається при використанні інструкцій GRANT, REVOKE або DENY для об'єктів бази даних.

Існують і інші дії аудиту, наприклад, SELECT, DELETE і EXECUTE. Додаткові відомості, в тому числі повний список усіх груп і дій аудиту, див. Розділ Групи дій і дії аудиту SQL Server.

Адміністратори БД довгий час пропонували включити цю функцію в SQL Server. Тепер це зроблено, і як раз вчасно! Останнім часом по ряду причин, наприклад, у зв'язку із збільшеною тривалістю зберігання даних і необхідністю фізичного зберігання більшого обсягу даних, розміри баз даних стали рости експоненціально. Під час резервного копіювання великої бази даних необхідно виділення значного дискового простору для файлів резервної копії, а також виділення для операції істотного часового проміжку.

Сервери централізованого управління

Часто адміністратор БД управляє відразу багатьма екземплярами SQL Server. Можливість централізації управління і адміністрування багатьма екземплярами SQL в єдиній точці дозволяє економити суттєві зусилля і час. Реалізація серверів централізованого управління, доступна в середовищі SQL Server Management Studio за допомогою компонента «Зареєстровані сервери», дозволяє адміністратору виконувати різні адміністративні операції над багатьма серверами SQL Servers з єдиної консолі управління.

Сервери централізованого управління дозволяють адміністратору зареєструвати групу серверів і виконувати над ними, як над єдиною групою, наприклад, такі операції:

· Імпорт та експорт зареєстрованих серверів: сервери, зареєстровані в серверах централізованого управління (Central Management Servers), можуть експортуватися і імпортуватися при їх передачі між адміністраторами або різними встановленими екземплярами SQL Server Management Studio. Ця можливість є альтернативою імпорту або експорту адміністратором його власних локальних груп в SQL Server Management Studio.

Складальник даних і сховище даних управління

Налаштування продуктивності і діагностика займають багато часу і можуть вимагати професійних навичок роботи з SQL Server, а також розуміння внутрішньої структури баз даних. Системний монітор Windows (Perfmon), профілювальник SQL Server Profiler і динамічні адміністративні уявлення вирішували частину цих завдань, але вони нерідко впливали на роботу сервера, були трудомісткі в застосуванні або задіяли методи збору розрізнених даних, що ускладнюють їх подальше об'єднання і інтерпретацію.

Ще одна корисна функція сховища даних управління - це можливість його установки на будь-якому сервері SQL Server з подальшим збором даних з одного або кількох примірників SQL Server. При цьому мінімізується вплив на продуктивність робочих систем, а також поліпшується масштабованість в контексті відстеження та збору даних з багатьох серверів. При лабораторних випробуваннях спостерігається втрата пропускної здатності при виконанні агентів і роботі сховища даних управління на навантаженому сервері (із застосуванням робочого навантаження OLTP) склала приблизно 4%. Втрата продуктивності може змінюватися в залежності від періодичності збору даних (згадане випробування велося при розширеній робочому навантаженні, з передачею даних в сховищі кожні 15 хвилин), вона також може різко збільшуватися під час періодів збору даних. У будь-якому випадку слід очікувати деякого зменшення доступних ресурсів, так як процессDCExec.exe використовує певний обсяг пам'яті і ресурси ЦП, а запис в сховище даних управління підвищить навантаження на підсистему введення-виведення і потребує виділення простору в місці розташування файлів даних і журнала.На діаграмі (рис. 2) показаний типовий звіт збирача даних.

Простота управління базою даних значно полегшує виконання рутинних завдань адміністрування. У міру зростання розмірів таблиць, індексів і файлів і поширення дуже великих баз даних (VLDB) управління даними і робота з громіздкими файлами стають все більш складними. Крім того, зростаючі з об'ємом запитуваних даних потреби в пам'яті і забезпеченні фізичної пропускної здатності введення-виведення також ускладнюють діяльність адміністраторів і дорого обходяться організації. Внаслідок цього в багатьох випадках адміністраторам і організаціям доводиться або розширювати пам'ять або gпропускную здатність введення-виведення серверів, або миритися зі зниженням продуктивності.

У деяких лабораторних випробуваннях включення стиснення даних забезпечувало економію 50-80% дискового простору. Економія простору істотно відрізнялася: якщо в даних містилося мало повторюваних значень, або значення використовували все виділяються для зазначеного типу даних байти, економія була мінімальною. При цьому продуктивність багатьох робочих навантажень не збільшувалася. Однак при роботі з даними, які містять багато числових даних і багато значень, що повторюються, відзначалися значна економія дискового простору і зростання продуктивності, що становить від декількох відсотків до 40-60% для деяких зразків робочих навантажень запитів.

І стиснення рядків, і стиснення сторінок може включатися для таблиці або індексу в оперативному режимі, анітрохи не порушуючи доступність даних для додатків. У той же час стиснути або розпакувати окрему секцію секціонірованной таблиці в оперативному режимі без її відключення неможливо. Випробування показали, що найкращим виявляється поєднаний підхід, при якому стискаються лише кілька найбільших таблиць: при цьому досягається відмінне ставлення економії дискового простору (значною) до втрати продуктивності (мінімальної). Оскільки у операції стиснення, як і у операцій створення або перестроювання індексу, також є вимоги до доступного дискового простору, проводити стиснення слід з урахуванням цих вимог. Мінімум вільного простору під час процесу стиснення буде потрібно, якщо стиск почати з об'єктів найменшого розміру.

Додаткові відомості про використання стиснення см. В документі Створення стислих таблиць і індексів.

Управління на основі політик

У багатьох бізнес-сценаріях необхідно підтримувати певні конфігурації або дотримуватися політики або на певному сервері SQLServer, або багаторазово, в групі серверів SQLServer. Адміністратор або організація можуть вимагати застосування особливої ​​схеми іменування всіх створюваних користувальницьких таблиць або збережених процедур або вимагати певних змін застосовувалися до конфігурації на багатьох серверах.

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

Політики можна експортувати і імпортувати в вигляді XML-файлів для їх визначення і застосування на багатьох примірниках серверів. Крім того, в середовищі SQLServerManagement Studio і в поданні зареєстрованих серверів політики можна визначати на багатьох серверах, зареєстрованих в локальній групі серверів або в групі сервера централізованого управління.

Прогнозована продуктивність і паралелізм

Багато адміністратори стикаються зі значними труднощами при підтримці серверів SQLServers з постійно змінюються робочими навантаженнями і забезпеченні передбачуваного рівня продуктивності (або мінімізації розбіжностей в планах запитів і продуктивності). Несподівані зміни продуктивності при виконанні запитів, зміни планів запитів і / або загальні пов'язані з продуктивністю проблеми можуть бути викликані багатьма причинами, в тому числі підвищенням навантаження від виконуються на сервері SQLServer додатків або оновленням версії самої бази даних. Передбачуваність виконуваних на сервері SQLServer запитів і операцій значно полегшує досягнення і підтримання цілей за рівнем доступності, продуктивності і / або безперервності бізнес-діяльності (виконання угод про рівень обслуговування та рівні операційної підтримки).

По-перше, структури планів (Plan Guide):

sp_create_plan_guide_from_handle
@name = N'MyQueryPlan ',
@plan_handle = @plan_handle,
@statement_start_offset = @offset;

По-друге, ескалація блокувань:

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

На діаграмі нижче показаний процес виділення ресурсів. У даному сценарії налаштовуються три пулу робочих навантажень (робочі навантаження Admin, OLTP і Report), після чого пулу робочих навантажень OLTP присвоюється вищий пріоритет. У той же час налаштовуються два пула ресурсів (пул Pool і пул Application) з заданими обмеженнями за обсягом пам'яті і часу процесора (ЦП). На останньому етапі робоче навантаження Admin призначається пулу Admin, а робочі навантаження OLTP і Report призначаються пулу Application.

Нижче наведені особливості, які слід враховувати при використанні регулятора ресурсів.

- Регулятор ресурсів використовує облікові дані входу, ім'я вузла або назва програми в якості «ідентифікатора пулу ресурсів», тому використання для додатка одного імені входу при певних кількостях клієнтів, що припадають на один сервер, може ускладнити створення пулів.

- Чи не підтримується угруповання об'єктів на рівні бази даних, при якій доступ до ресурсів регулюється на основі об'єктів бази даних, до яких здійснюється доступ.

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

- Налаштовувати можна тільки використання ресурсів процесора і пам'яті. Управління ресурсами введення-виведення не реалізоване.

- Динамічне перемикання робочих навантажень між пулами ресурсів після з'єднання неможливо.

Прозоре шифрування даних (TDE)

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

Шифрування бази даних - одноразовий процес, який можна запустити командою Transact - SQL або з середовища SQL Server Management Studio. після чого він виконується в фоновому потоці. Стан шифрування або дешифрування можна відстежувати за допомогою динамічного адміністративного подання sys.dm_database_encryption_keys. Під час проведених лабораторних випробувань шифрування бази даних розміром 100 Гб із застосуванням алгоритму шифрування AES _128 зайняло близько години. Хоча накладні витрати при використанні TDE визначаються в основному робочим навантаженням додатки, в деяких з проведених випробувань ці додаткові витрати склали менше 5%. Слід враховувати одну особливість, яка може вплинути на продуктивність: якщо TDE використовується в будь-який з баз даних на екземплярі, то також шифрується і системна база даних tempDB. Нарешті, при одночасному використанні різних функцій необхідно враховувати наступне:

• При використанні стиснення резервних копій для стиснення зашифрованої бази даних розмір стислій резервної копії буде більшим, ніж без використання шифрування, так як зашифровані дані стискаються погано.
• Шифрування бази даних не впливає на стиснення даних (рядкове або сторінкове).