Основы администрирования sql server (шпаргалка начинающего администратора)

Что нужно знать начинающим администраторам БД:

Модели восстановления (обязательная тема для любого кто планирует заниматься работой администратора баз данных. Эту тему надо понимать в полном объёме (FULL,LOG) (https://msdn.microsoft.com/ru-ru/library/ms189275.aspx)
— FULL https://technet.microsoft.com/ru-ru/library/ms190217(v=sql.105).aspx
— LOG https://technet.microsoft.com/ru-ru/library/ms191164.aspx
После изучения моделей восстановления, обязательно проведите самостоятельное тестирование с полным и частичным восстановлением (Restore)
Безопасность уровня БД и сервера
Изучение лога ошибок
Конфигурация и установка
Простые запросы
Базовые понятия производительности сервера (плохо ему или хорошо)
Индексы и статистика
CHECKDB
Варианты отказоустойчивости (Log Shipping, Mirroring, Failover Cluser, AlwaysOn). Отказоустойчивость ни в коем случае не отменяет необходимость делать резервные копии

11. Как упаковать (зашринковать) базу на MS SQL Server

Упаковать базу
данных можно или
с помощью графического интерфейса приложения SQL Server Enterorise
Manager, или с помощью выполнения кода (например с QA).
Первый случай выполняется посредством вызова контекстного меню на
необходимой базе и выбора All Tasks — Shrink Database… .
Второй случай опишем несколько подробнее. В приведенном коде
выполняется 4 строки кода. 1 строка — усечение всей базы. 2 строка —
усечение только DATA файла до размера 101 мб. 3 строка — усечение
только файла транзакций до размера 0 мб (несколько килобайт). 4 —
строка — очистка журнала транзакций (иногда файл транзакций усечь
невозможно, по причине заисших открітіх транзакций, выполнение этой
команды должно привести до полной очистки журнала транзакций).
Код:

DBCC shrinkdatabase(N’base_1c’,
TRUNCATEONLY )use
DBCC
SHRINKFILE (N’base_1c_Data’,
101)use DBCC SHRINKFILE
(N’base_1c_Log’,
0)DUMP
TRANSACTION base_1c WITH
TRUNCATE_ONLY

base_1c
Если приведенный
више код не привел к требуемым результатам, можно попробовать выполнить
следующий код:
Код:

use BACKUP LOG base_1c
WITH
TRUNCATE_ONLYGODUMP
TRANSACTION base_1c WITH no_log
GOCHECKPOINT
GODBCC
SHRINKFILE(base_1c_log,2)
GODBCC
SQLPERF (logspace)

base_1c

Виды нереляционных баз данных

Базы NoSQL делятся на четыре основные категории (в зависимости от решаемых с их помощью задач).

Ключ-значение

Такую базу можно представить как огромную таблицу. В каждой её ячейке хранятся данные произвольного типа, а каждому значению присвоен уникальный ключ, по которому это значение можно найти.

Такая СУБД не поддерживает связи между объектами, выполняет лишь операции поиска значений по ключу, добавления и удаления записи.

Например:

key	value
user1	{Кузнецов В., отдел маркетинга}
user2	{name:Лена, position:секретарь}
user3	{ООО «Вектор»}
user4	{Трофимова Таня, отд.2, дизайнер}
user5	{Галина Николаевна, гл. бух.}
user6	{65,84,236}

Базы «ключ-значение» часто используют для кэширования данных и организации очередей.

Их достоинства — быстрый поиск и простое масштабирование.

Их недостаток — нельзя производить операции со значениями. Например — сортировать их или анализировать.

Одна из самых популярных — Redis. Её используют Uber, Slack, Stack Overflow, сайты гостиниц и туристические, социальная сеть Twitter.

Документоориентированные СУБД

В таких данные хранятся в виде иерархических структур (документов) с произвольным набором полей и их значений. Документы объединяются в коллекции.

Если провести аналогию с реляционными СУБД, то коллекциям соответствуют таблицы, а документам — строки в них.

Например, фрагмент документа с информацией о фильмах:

Документоориентированные базы используют в системах управления содержимым (CMS) — для хранения каталогов и пользовательских профилей.

Одна из самых популярных — MongoDB (там можно создавать процедуры на JavaScript).

Колоночные

Эти базы отличаются от реляционных лишь способом хранения данных на накопителе.

Если реляционная база создаёт для каждой таблицы по файлу, то в колоночной отдельный файл создаётся для каждого столбца таблицы.

Например, если реляционная таблица выглядит так:

name	color	property
волк	серый	зубастый
коза	белая	рогатая
капуста	зелёная

То те же записи колоночной базы будут выглядеть примерно так:


name	волк	коза	капуста


color	серый	белая	зелёная


property	зубастый	рогатая

Что это даёт? Представьте, что вам нужны только названия объектов, а их свойства вас не интересуют.

При выполнении запроса в реляционной таблице просматривается каждая запись и из неё выбираются нужные данные. В колоночной базе с диска будет считана только одна колонка с названиями. Это сокращает время выполнения запроса, причём намного.

Колоночные базы применяются в различных каталогах и архивах данных, работа с которыми основана на подобных выборках.

Одна из самых популярных СУБД такого типа — Apache Cassandra.

Графовые

В некоторых предметных областях данные удобно представлять в виде графов. Для их хранения лучше всего подходят графовые базы.

Вершины (или узлы графа) — это объекты (сущности), а рёбра графа — взаимосвязи между ними.

Шаг 4. Настройка Планов обслуживания (Maintenance Plans, Регламентных заданий)

Для работы регламентных заданий необходимо создать план обслуживания:

Итак, приведу свой пример настроенного Maintenance Plans с комментариями. Мой план состоит из 5 подпланов:

Первый подплан (ежедневное еженочное обслуживание сервера и резервных копий):

Данный подплан состоит из нескольких шагов. Связи зеленого цвета задают переход к следующему заданию при удачном завершении (т.е. без ошибок), связи синего цвета задают переход к следующему заданию при любом результате выполнения текущего. Параметры шагов видны на размещенных в редакторе заданиях. Параметры некоторых заданий нужно описать отдельно. Первым шагом выполняется «Проверка целостности базы данных» (Check Database Integrity Task), которая выполняется для всех баз системы и следующие задания выполняются только при отсутствии ошибок при проверке баз. Следующим шагом выполняется «Перестроение индексов баз данных» (Rebuild Index Task) для всех баз данных сервера. Данная процедура довольно ресурсоемкая, но в последствии ускоряет работу базы, т.к. если фрагментированость индексов > 25%, это резко снижает производительность сервера. Если размер баз не позволяет выполнять данную задачу, т.к. она занимает много времени, то рекомендуется делать данное действие хотябы раз в неделю, при этом, на ночные задания заменить задачу Перестроение индексов баз данных (Rebuild Index Task) на Дефрагментацию индекса (Reorganize Index Task), которая менее ресурсоемка. Далее происходит «Обновление статистики базы данных» (Update Statistics Task) для всех баз данных, опять же для оптимизации. После этого задания рекомендуется выполнить «Очистку процедурного кэша»:

При этом, запускается процедура

DBCC FREEPROCCACHE

После оптимизации работы желательно сделать резервную копию журналов транзакций. Этот шаг делать не обязательно, но желательно. Во время выполнения предыдущих шагов (Перестроение индексов баз данных (Rebuild Index Task) и Обновление статистики базы данных (Update Statistics Task)) файлы журналов вырастают примерно до размера базы данных, а то и более. В результате, в следующих подпланах при выполнении первого резервного копирования журнала транзакций, размер копии имеет довольно большой объем. Это может сильно увеличить вермя восстановления базы. Таким образом, делая копию логов до полной копии, мы избавляемся от данной проблемы. (спасибо за идею комментатору Kyoshiro)

Далее можно выполнить полный бэкап заданием Создание резервной копии базы данных (Back Up Database Task):

При выполнении данного задания копии складываются в сетевую папку на файловом сервере с расширением bak, при этом, для каждой базы создается своя папка:

SAMBA ~ # ls -1 /backup/full/
database1
database2
...
SAMBA ~ # ls -1 /backup/full/satabase1/
database1_backup_201111210250.bak
database1_backup_201111220251.bak
...

После заверешения создания резервной копии параллельно запускается 3 задания: очистка резервных копий журналов транзакция (о создании таких копий — ниже) старее 5 дней, очистка полных бэкапов старее 1 недели и очистка истории старше 1 месяца (сюда входит в основном — очистка служебной информации MS SQL, такой как журналы):

Данный подплан у меня запускается каждую ночь в нерабочее время по будням:

Второй, третий, четвертый подплан (обновление статистики 3 раза в день):

Следующие 3 подплана одинаковы по содержимому и различаются лишь временем выполнения. Выполняются 3 раза в день — в 6, 13 и 19 часов:

Обновление статистики довольно ресурсоемкая задача, поэтому спланируйте ее выполнение в то время, когда база данных не сильно загружена.

Пятый подплан (резервное копирование журнала транзакций):

Данный план выполняет инкрементальное копирование транзакционного лога Microsoft SQL Server:

Копирование выполняется каждые пол часа в рабочее время и сохраняется в сеть с расширением trn:

После настройки данного плана мы имеем регулярное резервное копирование с необходимым регламентным обслуживанием, с хранением копий базы данных за последние 7 дней с возможностью восстановления базы интервалом до 30 минут.

Более подробно о выборе и планировании плана обслуживания можно посмотреть данный подкаст(временно убран по причине заражения сайта s.rpod.ru):

Лучшее для краткого справочника: руководство по SQL Pocket

Независимо от того, являетесь ли вы разработчиком начального уровня или администратором базы данных, или много лет работали с SQL, запоминание деталей каждой возможной команды и аргумента было бы сверхчеловеческим подвигом. Вот тут-то и появляется компактное руководство по Pocket SQL от Джонатана Дженника .

Этот удобный справочник, охватывающий ряд серверов баз данных, в том числе Microsoft SQL Server, Oracle, DB2 и другие, объясняет различия в реализации между платформами и служит отличным обновлением для редко используемых команд.

Разработанный в качестве справочного, а не практического руководства, нет необходимости читать книгу от корки до корки — она предназначена для того, чтобы сидеть на столе и консультироваться при необходимости. Несмотря на то, что вы можете найти большую часть информации внутри с помощью нескольких хороших поисков в Google, возможность быстро пролистать SQL Pocket Guide для точных деталей, которые вам нужны, часто быстрее, более конкретна и имеет гораздо меньше шансов отвлечься.

Лучше всего учиться на практике: проблемы практики SQL

Для тех, кто хочет расширить свои знания по SQL за пределы того, что обычно доступно в учебных руководствах и онлайн-учебниках, SQL Practice Задачи использует совершенно новый подход к изучению языка.

Книга содержит 57 задач различной сложности, от начинающих до продвинутых, и предназначена для имитации тех проблем, с которыми сталкиваются пользователи SQL в реальном мире. Цель автора — научить читателей «мыслить в SQL», анализировать проблемы с данными и предлагать качественные решения.

Для тех, кто не имеет доступа к существующему серверу базы данных, прилагаются инструкции по установке для бесплатной Microsoft SQL Server Express Edition и студии управления, а также видео-пошаговое руководство для примера базы данных.

Задачи SQL Practice в основном ориентированы на тех, кто ищет данные (с помощью операторов SELECT), а не на обновление существующей информации, и кому необходимо изучить наиболее эффективные способы для этого. Он доступен как в версии Kindle, так и в мягкой обложке, а его автор может обратиться по электронной почте за помощью в решении проблем и запросов.

Что такое MS SQL Server

Чтобы упростить работу с такими хранилищами данных и повысить эффективность их применения, создаются специализированные системы управления. Одной из наиболее популярных является разработка от Microsoft – SQL Server. Первый релиз платформы опубликован еще в 1989 году, а последняя версия выпущена в 2019 году (проект продолжает развиваться).

Преимущества решения:

Тесная интеграция с операционной системой Windows.
Высокая производительность, отказоустойчивость.
Поддержка многопользовательской среды.
Расширенные функции резервирования данных.
Работа с удаленным подключением.

Каждый выпуск включает в себя несколько специализированных редакций. Это снижает сложность внедрения и затраты на процесс разработки собственных решений, адаптированных для «узких» задач. При написании программного кода активно используется интеграция с продуктами Microsoft, например, с платформой Visual Studio.

Прямые конкуренты на рынке – Oracle Database, PostgreSQL. Первый проект коммерческий, он создан для поддержки крупных компаний, поэтому сопоставим по возможностям с MS SQL Server. Второй же распространяется на бесплатной основе и не «блещет» функциональностью, хотя весьма популярен среди многих разработчиков (аналог от Oracle MySQL).

Что такое СУБД

Появление таких продуктов позволило объединить разное понимание БД (баз данных) со стороны пользователей и системных администраторов. Неискушенные в технических деталях люди «видят» таблицы как некий перечень данных с колонками и строками. Системный подход включает файлы с табличными данными, связанными друг с другом согласно определенному алгоритму.

Функции базы данных:

Постоянное хранение информации.
Поиск по ключевым критериям.
Чтение и редактирование по запросу.

Клиентами БД являются прикладные программы, их интерфейс, различные интерактивные модули сайтов вроде калькуляторов и онлайн-редакторов. Но есть еще один компонент системы – СУБД. Он предназначен для ручного доступа к информации и позволяет извлекать данные на диск, работать с ними в памяти сервера, в том числе с применением структурированного языка SQL.

Всего различают три типа БД – клиент-серверные, файл-серверные и встраиваемые. MS SQL Server относится к первой категории. Плюс система является реляционной, т.е. адаптированной для хранения данных без избыточности, с минимальными рисками появления аномалий и нарушения целостности внутренних таблиц.

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Microsoft SQL Server

Microsoft SQL Server – коммерческая система, с помощью которой производится управление базами данных. Распространением данной системе занимается компания Microsoft. Систему SQL Server можно использовать как в маленьких, так для средних и больших базах данных. Она является одной из самых лидирующих систем в своей области и является достаточно сильным конкурентом среди других систем. Взять в аренду Microsoft SQL Server можно на сайте http://www.smoff.ru/products/arenda-ms-sql, при аренде не требуется покупать и настраивать лицензии и серверное оборудование.

Основные функции Microsoft SQL Server

обеспечения поддержки большому количеству пользователей;
присутствие платформ;
поддерживает 64 разряда архитектуры;
функция масштабирования;
динамическая блокировка;
распределения запросов;
распределения транзакции;
репликация информации;
параллельное сбережение;
стандарт SQL92;
интегрирование с IIS и InterDev.

Установить программу SQL Server достаточно легко и просто. Необходимо провести установку загрузочного файла и проследовать указаниям. Скачать SQL Server можно на официальном сайте компании производителя.

Благодаря системе Microsoft SQL Server управления распределенными серверами происходит просто. Это происходит с помощью новейшего пользовательского интерфейса. Такие функции производятся на все задачи управления, инструменты администратора, а также внутри основного сервера.

Предоставляется масштабирующая среда управления, которая отлично адаптируется как с большим, так и маленьким заказчикам. За чет встроенных OLE объектов и визуальных средств разработки может произойти расширения инструментов стандартного вида.

Данная программа способствует самостоятельной работе удаленных серверов. Это происходит на уровне управления средствами, которые взаимодействуют с процессором информации сервера. Программа может запрограммировать действия, без участи человека.

Если возникают определенного вида проблемы или условия когда переполненный журнал, администратор без инструментов может перейти к активной защите администрирования, используя при этом развитую модель обработки предупреждений для того чтоб назначит корректирующие действия.

На данный период проходят тестирования новой версии программы Microsoft SQL Server. К новой версии планируется добавления новых функций, которые будут способствовать ещё более активной работе программы.

Всем привет! Сегодня я максимально просто, специально для начинающих, попытаюсь рассказать Вам о том, что такое SQL, и для чего он нужен. Из данного материала Вы также узнаете, что такое база данных и система управления базами данных, а также что такое диалект языка SQL, ведь вся статья будет построена на том, чтобы плавно подвести Вас к пониманию того, что же такое SQL.

Я думаю, Вы уже представляете себе, что SQL — это некий язык, связанный с какими-то там базами данных, однако для того, чтобы лучше понимать, что же такое SQL, необходимо понять, для чего нужен SQL, для чего нужен этот язык, т.е. его назначение.

Поэтому сначала я дам Вам немного вводной информации, из которой будет ясно назначение языка SQL, и для чего он вообще нужен.

Лучшее для начинающих: начало работы с SQL

Предоставлено Amazon

За эти годы издатель ветеранов технологий О’Рейли выпустил множество различных руководств по SQL, но для тех, кто просто окунается в воду, « Начало работы с SQL» — идеальное место для начала.

На 130 страницах книга является относительно короткой, чтобы помочь читателям освоить основы и быстро научиться выполнять полезные задачи. Наполненный практическими примерами и полезными объяснениями, он написан в простом, доступном стиле, который не требует много или каких-либо предварительных знаний. Полезно для тех, кто только начинает, книга не требует доступа к существующему серверу базы данных. Вместо этого в нем объясняется, как настроить учебную среду дома, используя SQLite для снижения затрат и сложности.

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

Решение

Чтобы устранить проблему доступа, рекомендуется запустить экземпляр SQL Server в однопользовательском режиме. Этот режим не позволит устанавливать другие подключений при попытке восстановить доступ. Вы можете подключиться к экземпляру SQL Server и добавить имя входа в роль сервера sysadmin.

Можно запустить экземпляр SQL Server в однопользовательском режиме с параметрами или из командной строки. Затем любой член локальной группы администраторов на компьютере может подключиться к экземпляру SQL Server в качестве члена предопределенной роли сервера sysadmin.

При запуске экземпляра в однопользовательском режиме сначала нужно остановить службу «Агент SQL Server». В противном случае агент SQL Server может установить соединение первым, заняв единственное доступное подключение к серверу и заблокировав возможность входа.

Кроме того, неизвестное клиентское приложение может воспользоваться единственным доступным подключением, прежде чем вы сможете выполнить вход. Чтобы исключить возникновение этой ситуации, используйте параметр , за которым следует имя приложения, что позволит ограничить подключения одним подключением из определенного приложения. Например, запуск SQL Server с помощью разрешает только одно соединение, которое должно идентифицироваться как клиентская программа sqlcmd. Для подключения через редактор запросов в Среда Management Studio используйте .

Важно! Не используйте с именем приложения в качестве средства безопасности. Клиентские приложения предоставляют имя приложения в параметрах строки подключения и могут легко указать ложное имя

В следующей таблице приведены различные способы запуска экземпляра в однопользовательском режиме в командной строке.

ТАБЛИЦА 1
Параметр	Описание	Назначение
	Ограничение подключений одним подключением	Если другие пользователи не пытаются подключиться к экземпляру или вы не знаете имя приложения, которое используется для подключения к экземпляру.
	Разрешает только одно соединение, которое должно идентифицироваться как клиентская программа sqlcmd.	Если вы планируете подключиться к экземпляру с помощью sqlcmd и хотите запретить другим приложениям использовать единственное доступное подключение.
	Разрешает только одно соединение, которое должно идентифицироваться как приложение Microsoft SQL Server приложении Management Studio — Query.	Если вы планируете подключиться к экземпляру с помощью редактора запросов в Среда Management Studio и хотите запретить другим приложениям использовать единственное доступное подключение.
	Разрешает только одно соединение и запускает экземпляр в минимальной конфигурации.	Когда запуску препятствует какая-либо другая конфигурация.

Что нужно знать начинающим администраторам БД:

Модели восстановления (обязательная тема для любого кто планирует заниматься работой администратора баз данных. Эту тему надо понимать в полном объёме (FULL,LOG) (https://msdn.microsoft.com/ru-ru/library/ms189275.aspx) — FULL https://technet.microsoft.com/ru-ru/library/ms190217(v=sql.105).aspx — LOG https://technet.microsoft.com/ru-ru/library/ms191164.aspx
После изучения моделей восстановления, обязательно проведите самостоятельное тестирование с полным и частичным восстановлением (Restore)
Безопасность уровня БД и сервера
Изучение лога ошибок
Конфигурация и установка
Простые запросы
Базовые понятия производительности сервера (плохо ему или хорошо)
Индексы и статистика
CHECKDB
Варианты отказоустойчивости (Log Shipping, Mirroring, Failover Cluser, AlwaysOn). Отказоустойчивость ни в коем случае не отменяет необходимость делать резервные копии

Как выбрать место работы для Junior DBA

В начале вашей карьеры следует обращать больше внимание не на зарплату, а на коллектив. Вам необходимо найти такое место работы, где вы сможете перенять опыт у ваших коллег

Вам очень повезёт, если вы сможете найти достойного наставника, тогда ваш карьерный рост будет стремительным. Если вы устраиваетесь на работу где нет других ДБА, то вам придётся самостоятельно проходить все сложности обучения и очень вероятно что это обучение будет сопровождаться авариями и другими сложностями, в таком случае будет полезно иметь знакомых, опытных администраторов БД, которым можно задать вопросы по телефону.

Чтобы стать Senior DBA вам необходимо постоянно развиваться. Вот несколько вариантов как вы можете это делать:

Посещать курсы
Посещать мероприятия
Читать сайты и форумы
Задавать вопросы на форумах и сайтах
Смотреть обучающее видео
Старайтесь делать на работе больше, чем вас просят (изучать каждую тему глубже)

Пора переходить к нашей теме.

«Изучение программирования на SQL» от Петра Радько

Язык: русский.

Длительность: 18 видеоуроков длительностью от 5 до 55 минут, общая длительность — 7 часов 10 минут.

Формат обучения: видеоуроки.

Уровень сложности: для начинающих.

Обратная связь: нет.

Программа обучения:

Введение.
О SQL.
Установка MySQL.
Базовые команды.
Дружим с SELECT.
Работа с записями.
Нормализация.
Изменяем поля — ALTER.
Изменяем поля — Строковые функции.
CASE и ORDER BY.
Числа и еще чутка о SELECT.
Пара полезных фишек.
Внешний ключ и виды связей.
Соединения.
Еще фишки и подзапросы.
Просто о нормальных формах.
Представления.
Верстак.

Шаг 3. Настройка рабочих баз данных Ms SQL

Если база еще не развернута из .dt файла, и вы знаете примерный ее размер, то первичному файлу размер инициализации лучше сразу указать больший или равный размеру базы, но это дело вкуса, он все равно вырастет при развертке до нужных размеров. А вот Автоувеличение (Autogrowth) размера надо обязательно указать примерно по 200 МБ на базу и по 50 МБ на лог (можно увеличить/уменьшить, в зависимости от размера конечной базы и наличия места на диске), т.к. значения по умолчанию – рост по 1МБ и по 10% очень сильно тормозят работу сервера, когда ему при каждой 3й транзакции надо файл увеличивать. В этом же параметре можно ограничить размер файла лога, чтоб сильно не разрастался, хотя это очень спорный параметр…

Остальные параметры можно оставить по умолчанию, за исключением некоторых:

Например, параметр AutoShrink советуют отключить, ибо он приводит к постоянным скачкам размера лога. Лучше его держать в узде с помощью Планов обслуживания (они же Регламентные задания, они же Maintenance Plans).