Если ваш запрос выполняется подозрительно долго или выглядит зависшим, возможно, он попал в блокировку. Можно просто подождать в надежде, что тот, кто нас заблокировал, вскоре закончит работу и отпустит блокировку… Но обычно хочется узнать, кто же нас заблокировал.

Разумеется, SQL Server отслеживает кто кого и почему блокирует. И у нас даже есть несколько инструментов, чтобы это увидеть. Самый простой способ — системное представление (DMV) sys.dm_Exec_Requests.

Сначала нужно узнать номер нашего процесса (сеанса). Этот номер можно узнать, заглянув в системную переменную @@SPID:

SELECT @@SPID

Правда, если запрос уже выполняется, то опросить эту переменную вы не сможете. Но обычно этот номер видно в клиентском приложении:

Теперь, зная номер нашего процесса, заглянем в sys.dm_Exec_Requests. В этом представлении уже содержится ответ на интересующий нас вопрос — столбец Blocking_Session_ID указывает на того, кто нас блокирует.

Теперь можем посмотреть на атрибуты процесса, который нам мешает. Наиболее информативно в этом плане представление sys.dm_Exec_Sessions. Из него вы узнаете кто, с какого клиентского компьютера и из какого приложения запустил блокирующий процесс.

Работая в режиме командной строки, мы ожидаем от интерфейса быстрого доступа к истории выполненных команд. Кнопки «­­­⬆️» и «⬇️» мы считаем настолько естественными, что вспоминаем про них только, когда они не срабатывают. Но есть штука поудобнее этих кнопок.

Это клавиша «F7».

Теперь, когда вы знаете про эту клавишу, вам будет остро не хватать её, к примеру, в пауэр-шелле.

Средствами PowerShell что-то подобное можно смастерить так:

Invoke-Expression (Get-History | Out-GridView -PassThru).CommandLine

Тренд Slow Living — это не только про жизнь, но и про ИТ.

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

Эта винтажная команда называется Tree.

• https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/tree

Бывает необходимо перенести файлы на новое место, сохранив все выданные на эти файлы разрешения. Как это сделать простым способом? Ведь при копировании в Проводнике все разрешения теряются. Да и при перемещении файлов информация о разрешениях переносится вместе с файлами только в пределах тома.

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

Взгляните на пример использования XCopy:

XCopy, конечно, выглядит более профессиональным инструментом, чем простые команды копирования. Но у Микрософта есть кое-что покруче.

RoboCopy — вот то, что вам на самом деле нужно. Эта утилита умеет всё то же самое, а также то, что обычно требуется в сценариях файловой репликации:

• восстанавливает копирование после разрыва связи,
• регулирует полосу пропускания

Описание RoboCopy на сайте Микрософта:

Когда Илон Маск заходит в бар, все его посетители становятся в среднем миллиардерами! :)

Эта старая шутка иллюстрирует проблему вычисления среднего значения. Когда нам требуется среднее значение по столбцу, обычно мы рефлекторно используем агрегатную функцию AVG. Но, как ни странно, в большинстве случаев такое решение будет неправильным.

Вот классический пример — посмотрите на список сотрудников:

Какова средняя зарплата в компании? Если использовать популярную функцию AVG, то получим среднее арифметическое — чуть больше миллиона рублей.

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

А вот профсоюз работников скорее всего с такой статистикой не согласится. Ведь если оценивать «по честному», то зарплата в среднем чуть больше ста рублей!

У каждого, кто работает с многомерными запросами, рано или поздно обязательно возникает желание задействовать переменные. Но, вот беда, переменных-то в языке MDX нет (или есть, но менеджмент-студия про них не знает)!

Что же делать, когда очень хочется написать вот такую конструкцию:

Есть два универсальных способа использования переменных в MDX-запросах. По крайней мере один из них вам обязательно подойдёт.

Во-первых, вы можете формировать MDX-запрос динамически, причём средствами Transact-SQL. А для выполнения передавать его аналитическому серверу через механизм связанных серверов (Linked Server). Вот так:

Здесь MDX_AW — это связанный SSAS-сервер, ссылающийся на вашу многомерную базу данных. Результат, разумеется, вы получите в табличном виде, как и положено в языке SQL. Кстати, обратите внимание на названия столбцов.

Такое решение, конечно, нельзя назвать изящным — это классический костыль. Зато, он выручит вас в любой ситуации, это универсальный способ.

Опытные разработчики и администраторы прекрасно знают, что никогда и ни при каких обстоятельствах нельзя назначать разрешения отдельным пользователям. Только группам или ролям!

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

В SQL-сервере есть простые функции и представления для таких проверок.

Для демонстрации этих механизмов я создам несколько ролей и групп разных типов. Разумеется, проверим, учитывает ли проверка вложенность. В моих примерах проверяемый пользователь входит только в самую глубоко вложенную группу/роль — № 3 (помимо членства в одной-двух стандартных).

Роли базы данных

Анализируя системные представления Sys.Database_Principals и Sys.Database_Role_Members, вы получите полную картину о членстве в группах.

Не всякую базу данных можно перенести с Enterprise-редакции на SQL Server Standard Edition. Имеется специальный механизм, показывающий список задействованных enterprise-механизмов, мешающих миграции на стандартную редакцию.

Приходилось ли вам при восстановлении базы данных из резервной копии или при присоединении файлов базы к серверу наблюдать сообщения об ошибке, при том, что на исходном сервере база восстанавливается/присоединяется нормально?

Часто это происходит при попытке переноса базы с корпоративной (Enterprise Edition) на стандартную (Standatd Edition) редакцию SQL-сервера.

Прежде чем осуществлять такой перенос, полезно проверить, а не используются ли в вашей базе механизмы, работающие только в корпоративной редакции SQL-сервера. Если задействована хоть одна enterprise-функция, то перенести базу на standard-редакцию не получится.

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

Проверить использование корпоративных механизмов, которые помешают такой миграции можно при помощи специального системного представления http://Sys.DM_DB_Persisted_SKU_Features.

Вот так:

В редакторе запросов SQL Server Management Studio можно исполнять не только SQL-код!

Обратите внимание на пункт меню студии, который выделен на картинке:

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

Во-первых, ничего не запуская и никуда не выходя, вы можете исполнять команды операционной системы (на клиентском компьютере). Для этого начните строку с двух восклицательных знаков:

Только не выполняйте таким образом команды, требующие ввода или реакции пользователя! Например, тот же NetSh, запущенный без параметров, будет ожидать дальнейших указаний.

Во-вторых, в одном сценарии теперь вы можете переключаться между разными серверами:

Современная методология проектирования многомерных баз данных подталкивает нас к объединению в одном кубе как можно большего количества разных групп мер и измерений. Одним из побочных эффектов такой консолидации является всё более и более разреженная матрица связей между размерностями и группами мер.

Работая с многомерными базами данных, вы наверняка наблюдали такой эффект, когда мера не делится по некоторым измерениям.

Вот я пытаюсь разложить показатели розничных продаж по реселлерам:

Но ни количество проданных единиц товара, ни выручка не распадаются на слагаемые.

Такой же результат я наблюдаю, подключившись к базе из экселя:

В этом нет ничего странного, потому что я пытаюсь проделать некорректную операцию. Если заглянуть в структуру куба, то мы увидим, что размерность реселлеров вообще никак не связана с розничными продажами. Срезы по реселлерам и их атрибутам имеют смысл при анализе оптовых продаж. А прикладывать их к рознице нет никакого смысла. Поэтому сервер и не может разложить розничные меры по значениям чисто оптового атрибута Reseller.[Business Type].

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

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

Теперь при попытке установить соединение с SQL-сервером, получаем сообщение об ошибке, случившейся во время выполнения триггера входа:

Если вы попали в такую ситуацию, придётся воспользоваться специальной лазейкой, которая позволяет администратору подключиться к SQL-серверу в обход LOGON-триггеров. Такое «сервисное» подключение доступно только членам серверной роли SysAdmin. Обратите внимание, что возможность удалённого сервисного подключения может быть отключена в настройках, тогда придётся работать прямо на сервере.

В утилите SQLCmd воспользоваться сервисным подключением можно, указав параметр -A:

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

Решение очень простое и гибкое — у вас не будет с ним никаких проблем. Готовый пакет прикреплён (см. ниже).

В пакете демонстрируется два роунамбера:

1. Простая расстановка номеров
2. Расстановка номеров с секционированием

Ниже пошаговое решение.

Сначала сортируем строки в том порядке, в каком надо генерировать номера:

Отсортированный поток заводим в Script Transformation и создаём в нём дополнительную колонку для роунамбера:

SQL Server умеет работать c временными таблицами. Названия у таких таблиц начинаются с одной или с двух решёток (#). Временные таблицы, очевидно, придуманы для того, чтобы хранить в них данные временно. Например, для сохранения промежуточных результатов каких-либо вычислений. Или для передачи каких-то данных от одного процесса другому.

Иногда требуется проверить, была ли уже создана определённая временная таблица или нет. Как это правильно сделать?

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

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

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

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

Если вы озабочены защитой данных, то в вашем распоряжении большой набор механизмов, предоставляемых СУБД. Однако, большинство из них работают только тогда, когда данные находятся в работающей базе. А что происходит, когда данные покидают SQL-сервер, например, после резервного копирования? Если злоумышленник украл резервную копию вашей базы данных, то, восстановив её на своём сервере, он увидит все ваши данные!

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

SQL-сервер шифрует резервную копию ключом, который сохраняется вместе с самой резервной копией. Поэтому этот ключ тоже нужно зашифровать. Это делается при помощи сертификата, который хранится отдельно от базы и не попадает в резервную копию.

Сам этот сертификат защищается мастер-ключом. Если вы раньше не использовали криптографию в SQL-сервере, то, возможно, мастер-ключа у вас тоже нет и его придётся создать. Вот так:

Обратите внимание, что создав сертификат, я сразу сохранил его в файл. Этот файл понадобится, если вы захотите восстановить базу данных из зашифрованной резервной копии на другом сервере. Не потеряйте его!

Всё, теперь можно создавать зашифрованные резервные копии. Если вы делаете это командой BACKUP DATABASE, то добавьте указание WITH ENCRYPTION:

Начиная с 2008-й версии, администраторы Windows уже не являются по умолчанию администраторами SQL-сервера. Это означает, что может возникнуть ситуация, при которой нет ни одной учётной записи, способной управлять SQL-сервером.

Например, вы назначили какую-то учётную запись Windows администратором SQL-сервера, а потом эту учётную запись удалили. Или забыли пароли ко всем учётным записям, которые исполняли роль sysadmin.

Это не просто теоретическая возможность! На практике я периодически сталкиваюсь с SQL-серверами, над которыми потерян контроль. То есть сам по себе SQL-сервер работает нормально, все базы данных функционируют, но никто не может этим сервером управлять.

Для выхода из такой ситуации Микрософт оставил специальную лазейку.

Найдите в списке windows-служб SQL Server и откройте окно со свойствами:

Остановите службу:

Администраторам часто бывает необходимо перебрать все таблицы базы данных, чтобы над каждой таблицей произвести какое-то действие. Например, перестроить индексы.

Традиционно для такого перебора можно использовать курсор. Но есть способ проще - процедура sp_MSForEachTable. Ей можно передать текст команды или запроса, который будет выполнен для каждой таблицы в базе. Команда, разумеется, параметризуется - вместо вопросительного знака будет подставлено название таблицы.

Посмотрите на сигнатуру этой процедуры:

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

Каждая из трёх команд может содержать больше одного SQL-запроса. При написании фильтра @WhereAnd учтите, что ваша строка будет встроена внутри процедуры в более сложный запрос к системным таблицам, поэтому используйте для фильтрации столбцы из SysObjects. Например:

При работе с данными часто приходится использовать «строки соединения с источником данных» (connection string). Синтаксис этих строк и набор параметров, которые можно в них использовать, зависит от типа хранилища и версии драйвера. Держать всё это в уме проблематично.

К счастью, есть способ быстро и без ошибок сконструировать такую строку. Редактор для работы со строками соединения уже имеется в Windows.

Создайте пустой файл с расширением «UDL»:

По двойному щелчку на UDL-файле по умолчанию открывается конструктор соединений. Он-то нам и нужен! Здесь мы удобным образом настраиваем все параметры подключения, причём этот редактор использует свой вариант интерфейса для каждого драйвера.

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

UDL — это обыкновенный текстовый формат. Откройте теперь этот файл блокнотом…

Это классическая задача по созданию списка с разделителями. При работе с многомерной базой данных такой список вы вероятнее всего захотите сгенерировать из элементов множества. Собрать такую строку в MDX-запросе очень просто!

Итак, при помощи MDX-запроса мы сформировали желаемый список. Но что если приложению непременно требуется этот список в виде строки с разделителями? Например, в отчёте требуется отобразить найденные элементы через запятую. Или такой формат необходим для передачи результата в какую-то другую систему для дальнейшей обработки.

Как из элементов списка (столбца, набора) собрать длинную строку с разделителями?

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

Однако, не торопитесь задействовать StrToSet! Работая с этой функцией, вам каждый раз придётся решать четыре сопутствующих проблемы:

1. StrToSet заключает результирующую строку в фигурные скобки, которые нам скорее всего не нужны. Надо не забыть от них избавиться.
2. Вы скорее всего хотели получить список из названий или значений элементов множества. А StrToSet сконвертирует в строку не названия, а ключи. Придётся подумать, как заменить их на требуемые имена.
3. Разделитель строки, возвращаемой StrToSet — всегда запятая. Если нужно что-то другое, потребуется дополнительная обработка.
4. И, конечно, надо будет избавиться от обрамления в виде префиксов и квадратных скобок.

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

В отчётных службах SQL-сервера имеются удобные виды диаграмм, специально предназначенные для встраивания в таблицы и списки. Это спарклайны, гистограммы (датабары) и индикаторы. Они предельно компактны и хорошо подходят для сложных визуализаций с большим количеством факторов.

Назначение таких «упрощённых» компонентов — помочь читателю быстрее сориентироваться в отчёте и сфокусироваться на самом важном. Однако, имеется один подводный камень, который запросто может всё испортить.

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

Благодаря спарклайнам у меня получился очень компактный и наглядный отчёт.

А как вы думаете, где выручка была больше: в Канаде или в США?

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

Как отлаживать такой код? Можно открыть несколько соединений с сервером от имени разных пользователей и смотреть на результаты одного и того же запроса в разных окнах. Но если это приходится делать по многу раз, переключение между соединениями начинает утомлять и отвлекать.

Альтернативный вариант — создайте для пользователя свой собственный связанный сервер (Linked Server), который ссылается сам на себя. Вот так:

И в настройках этого связанного сервера укажите контекст безопасности, в котором хотите провести отладку:

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