SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |
   

#043<<#044>>#045

DBA И БЕЗОПАСНОСТЬ

Microsoft Security Bulletin (MS01-022)
18 апреля 2001г. Микрософт опубликовал бюллетень об уязвимости защиты Windows «WebDAV Service Provider Can Allow Scripts to Levy Requests as User». Microsoft Data Access Component Internet Publishing Provider, обеспечивающий доступ к WebDAV ресурсам Internet и обрабатывающий все запросы в контексте защиты пользователя, оказался подвержен уязвимости. Эта уязвимость позволяла злоумышленнику написать специальный сценарий, исполнение которого пользователем на web-странице или при открытии сообщения электронной почты HTML формата, могло дать злоумышленнику возможность обращения к доступным пользователю сетевым ресурсам, под видом этого пользователя.

Затронутые продукты:

Microsoft Windows 95
Microsoft Windows 98
Microsoft Windows 98 Second Edition
Microsoft Windows Me
Microsoft Windows NT 4.0
Microsoft Windows 2000

Заплаты доступны тут:
http://www.microsoft.com/Downloads/Release.asp?ReleaseID=29129

СОВЕТ

Причины заполнения журнала транзакций SQL серверов 4.2x, 6.0, 6.5, 7.0
По материалам статьи Микрософт: Q110139 - INF Causes of SQL Transaction Log Filling Up

Журнал транзакций может полностью заполнится, что сделает невозможным операции UPDATE, DELETE или INSERT, включая CHECKPOINT. Обычно это проявляется, как ошибка 1105, имеющая следующее содержание:

Can't allocate space for object syslogs in database dbname because the logsegment 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.

От этого может пострадать любая база данных, включая master и tempdb. Эта статья рассматривает возможные причины и решение проблем, приводящих к ошибке 1105. Если Ваш журнал транзакций заполнен полностью, и Вы получили сообщение об ошибке 1105, Вы должны очистить журнал, используя команду DUMP TRANSACTION. Для получения дополнительной информации об использовании DUMP TRANSACTION, см. документацию SQL сервера.

Фундаментальной характеристикой реляционных баз данных, таких как Microsoft SQL Server, является поддержка целостности. Любая транзакция должна быть абсолютно неделима и все вносимые ей изменения должны быть применены полностью или не применены вообще, даже в случае отказа системы. В определяемой пользователем транзакции, транзакционным блоком считаются все инструкции между BEGIN TRANSACTION и COMMIT TRANSACTION. В неявной транзакции, каждая отдельная инструкция SQL рассматривается неделимым модулем. Это дает возможность серверу баз данных выдерживать сбои питания, аварийные отказы операционной системы, и т.д., когда, после перезапуска, происходит автоматическое (без участия обслуживающего персонала) восстановление баз данных к непротиворечивому состоянию. Эту возможность обеспечивает механизм журнала транзакций. Так как целостность данных является фундаментальным свойством SQL сервера, регистрация транзакций не может быть заблокирована. Некоторые утилиты или операции, типа BCP или SELECT INTO, имеют минимальную регистрацию в журнале, но даже этого достаточно, чтобы обратный откат таких операций был возможен. Требования к количеству дискового пространства для поддержки регистрации транзакций могут быть весьма высокими. Например, в большинстве случаев, модификация каждой строки данных должна быть зарегистрирована, а также регистрируются и все модификации затронутых индексов. Так как запись в журнале может содержать фиксированную часть данных изменяемой строки, количество занимаемого журналом места зависит от ширины строки. Для коротких строк, количество занимаемого в журнале места, при операциях UPDATE, DELETE или INSERT, может в десятки раз увеличивать занимаемое базой место на диске. При использовании достаточно широких строк, размер базы данных будет пропорционально больше величины журнала транзакций. Поэтому, старайтесь тщательно отслеживать потребление журналом транзакций дискового пространства, что поможет Вам гарантировать целостность данных. Обеспечение нормальной работы механизма регистрации транзакций является одной из первейших обязанностей DBA.

Количество занимаемого журналом места может зависеть от многих факторов и его очень трудно предсказать заранее. Стандартное руководство администратора предлагает, в качестве отправной точки, использовать величины порядка 15 - 30 процентов от размера базы данных. В действительности этого может оказаться недостаточно или наоборот, такие значения будут не оправдано избыточны. Как правило, более точные результаты даёт эмпирическое испытание, дающие грубую оценку размера журнала для реальных данных и прикладных программ. Попытки вычислить размер журнала транзакций, без подобного моделирования реальных условий, достаточно трудны и дают очень не точеные результаты.

Несколько факторов могут влиять на размер журнала транзакций. Первый из таких факторов - оптимизатор запросов (query optimizer). Для одного и того же SQL запроса, модифицирующего данные, через какое-то время план выполнения может измениться в зависимости от статистики распределения данных. Разные планы выполнения могут по разному использовать место в журнале. Следующий фактор - неизбежная внутренняя фрагментация базы данных, которая может привести к разному количеству имеющихся разбитых страницы. Не существует стандартных методов отслеживания подобных процессов, поскольку SQL сервер управляет пользовательскими данными автоматически. Самым простым методом такой оценки является выполнение команды DBCC CHECKTABLE (syslogs), которая возвращает число 2048-байтовых страниц данных в журнале, до и после выполнения тестовых или реальных, модифицирующих данные запросов. Это может дать приблизительную оценку требующегося места в журнале для таких запросов. Обычно, лучше допустить ошибку в сторону избыточности размера журнала, при определении его размера или дискового пространства для него.

У SQL Server 7.0, журнал транзакций имеет возможность расшириться автоматически. Дискретность прироста может задаваться пользователем или может быть разрешено использование всего доступного дискового пространства. Журнал состоит из виртуальных журналов (Virtual Log files, далее VLF). Количество и размер этих виртуальных журналов определяет SQL сервер и это не может быть изменено конфигурационными параметрами. После создания новой базы данных, каждый её физический журнал имеет не менее двух VLF. Иногда администраторы базы данных включают опцию базы данных «truncate log on checkpoint», чтобы избежать переполнения журнала. Назначение этой опции состоит в том, чтобы обеспечить автоматическое усечения журнала транзакций, главным образом для разрабатываемых или тестовых баз данных, которым не обязательна регистрация транзакций для последующего их резервирования. Эта опция не отключает регистрацию транзакций или поддержку целостности, а только заставляет обработчик контрольной точки делать попытку усечения журнала приблизительно каждые 60 секунд. Обратите внимание, что журнал транзакций не будет усекаться при ручной установке команды «truncate log on checkpoint», которая автоматически запускает контрольную точку в базе данных. Эта опция всегда включена для базы данных tempdb, хотя Вы не увидите этого с помощью хранимой процедуры sp_help.
Но даже при включённой опцией «truncate log on checkpoint», не исключены случаи, когда журнал транзакций может переполниться. Ниже эти случаи рассмотрены подробно:

1. При исполнении большой, неделимой транзакции, особенно при массовых операциях UPDATE, DELETE или INSERT: Каждый отдельная SQL инструкция будет рассматриваться как самостоятельный, неделимый модуль, который должен применяться или не применяться целиком. По этой причине, все изменения строк должны быть зарегистрированы, и транзакция не может быть усечена на всём протяжении её исполнения. Например, если происходит массовая операция с INSERT, которая продолжается пять минут, записи журнала транзакций, относящиеся к этой транзакции, не могут быть усечены в течении этого времени. Администратор базы данных должен обеспечить достаточно места в журнале для самой большой, массовой операции или должен разбить массовую операцию на меньшие блоки.

2. При наличии в журнале незавершённых транзакций: журнал транзакций может быть усечен только до самой старой незавершённой транзакции. Есть несколько возможных причин появления незавершённых транзакций, большинство из которых относятся к ошибкам прикладного программного обеспечения. Они включают:
- Массовые транзакции. Как показано выше, для больших и продолжительных, массовых транзакций соответствующие регистрационные записи в журнале не могут быть усечены в течение всего периода исполнения. Однако, такая транзакция будет препятствовать усечению и других более коротких транзакций, которые исполняются в то же самое время.
- Не продуманная разработка прикладного программного обеспечения, когда в пределах определяемой пользователем транзакции возможен ввод или корректирование данных пользователем или другие, продолжительные действия пользователя. Например, после BEGIN TRANSACTION, приложение может запросить у пользователя ввод данных, который может занять долгое время, в зависимости от поведения пользователя. Пока пользователь соответствующим образом не отреагирует, приложение не сможет исполнить COMMIT, а усечение журнала не будет возможным.
- Ошибки прикладного программного обеспечения, вследствие которых транзакция остаётся не завершённой: обычная причина этого - неправильная обработка вызова dbcancel() DB-Library в пределах определяемой пользователем транзакции. Это наблюдается, когда запрос отменен с помощью dbcancel(), причём, исполняющаяся в это время SQL инструкция прерывается и откатывается назад, а внешняя транзакция не отменяется. Приложение должно гарантировать выполнение ROLLBACK TRANSACTION или инструкции COMMIT TRANSACTION, чтобы полностью отработать и закрыть транзакцию. Не выполнение этого требования, часто приводит к появлению ошибки 3902: The commit transaction has no corresponding BEGIN TRANSACTION. Это можно использовать в приложении, чтобы в ответ выполнить SELECT @@TRANCOUNT, и определить, какая вложенная транзакция существует на этом уровне. Однако, приложение не должно делать это вслепую, что бы всё-таки выполнить COMMIT/ROLLBACK и получить @@TRANCOUNT=0. Это не желательно потому, что @@TRANCOUNT может указывать не на ту транзакцию, которую ожидалось определить. Т.е. приложение может не увидеть запись о транзакции вложенного уровня, относительно той транзакции, которая стала причиной ошибки приложения. И тогда, инициация COMMIT/ROLLBACK может привести к завершению или откату не завершённой транзакций, так как приложение не может знать, какие транзакции завершены на вложенном уровне. Для разрешения таких проблем, программист должен так отладить приложение и возможные хранимые процедуры, что бы полностью исключить возможность  непреднамеренного прерывания вложенных транзакций.
- Сетевые ошибки, из-за которых SQL сервер не получает уведомление о потере сетевого подключения. Если клиентская рабочая станция зависает, перезагружается, или будет выключена во время исполнения определяемой пользователем транзакции, средства сетевого уровня должны сообщить об этом SQL серверу. Если сеть не отреагирует на это должным образом, SQL сервер будет считать, что клиент продолжает работу, и выполняющаяся клиентская транзакция будет по-прежнему активна. Эта сетевая проблема должна контролироваться администратором, который может с помощью sp_who, sp_lock, или сетевых утилит определить такие оборванные сетевые соединения и уничтожать их вручную.
- Транзакции, не завершенные из-за блокировок. В многопользовательской среде исполняющаяся транзакция может обратиться к блокированным другим процессом данным. В этом случае, транзакция останется открытой и не позволит выполнить усечение журнала. Чтобы это обнаружить, программист или администратор базы данных должен использовать sp_who, sp_lock, или другие инструментальные средства, для соответствующего анализа. В большинстве случаев, проблемы блокировок могут быть снижены или устранены через изменение запроса, индекса, и изменение дизайна базы данных.
- Неудачные попытки отмены запросов на модификацию данных. Если приложение исполняет dbcancel(), но запрос не отменен из-за проблем в сети или SQL сервера, запрос продолжает выполняться, и транзакция останется открытой. Если Вы подозреваете наличие такой проблемы, используете sp_who, чтобы увидеть, отменён ли запрос. При попытке такой отмены для клиента, работающего через TCP/IP сокет, пробуйте проверить подключение клиента по named pipes, или запустите клиентское приложение на сервере, используя local pipes. Это поможет определить, вызвана ли проблема сетью или SQL сервером.

3. Чрезмерная утилизация ресурсов сервера при усечении журнала во время отработки Checkpoint: Хотя журнал транзакций усекается каждые 60 секунд, количество усекаемых за это время транзакций ограничено. Вероятность того, что контрольная точка не успеет пробежать за отведённое ей время весь журнал, чрезвычайно мала, и Вы должны предварительно рассмотреть все другие возможные причины возникновения проблемы. Однако, превысить максимальную норму усечения всё-таки возможно, если большое количество  клиентов будет одновременно выполнять модификацию большого количества данных. Подобные проблемы решаются путём пересмотра структуры приложения, с целью уменьшения числа модифицируемых клиентом строк, что всегда должно быть одной из первостепенных задач дизайна для любой базы данных.
Если это не выполнимо, система может быть масштабирована в целях увеличения пропускной способности дисковой подсистемы (I/O), например striping, дополнительные диски или дисковые контроллеры, и т.д. Обычно, это видно потому, что процесс отработки контрольной точки потребляет больше времени, чем положено при исполнении команды DUMP TRANSACTION, поскольку он пытается не отставать от усечения журнала. Как только порог усечения превышен (см. ниже) Вы увидите, что обработчик контрольной точки перестал делать попытки усечения журнала базы данных, пока не произойдёт очистка журнал транзакций.

4. Превышение порога усечения. Обработчик контрольной точки по существу делает DUMP TRANSACTION WITH TRUNCATE_ONLY. Так же, как это делается вручную. Но контрольная точка не всегда будет успевать появится до того, как журнал будет заполнен до некоторого критического значения. Например, чрезмерный объём модификаций данных, выполненный за очень короткое время, может заполнить журнал транзакций на 95% между двумя очередными контрольными точками. Когда обработчик контрольной точки сделает попытку усечения почти заполненного журнала транзакций, это может привести к невозможности самого усечения. Такое случается потому, что усечение журнала само является регистрируемой операцией. Единственное решение в этом случае состоит в том, чтобы использовать DUMP TRANSACTION WITH NO_LOG, т.е. вручную запустить усечение журнала транзакций. Не рекомендуется использование опции NO_LOG кроме случаев, когда это абсолютно необходимо, поскольку операция не будет регистрироваться в журнале и произошедший во время выполнения такой операции  отказ системы, может породить ошибки в базе данных.

5. Комбинации представленных выше четырёх причин. Например, при нормальных условиях в интенсивно модифицируемой среде, норма усечения обработчика контрольной точки может препятствовать заполнению журнала. Если временно открытая транзакция, вызванная любой из вышеупомянутых причин (например, блокировки) приведёт к заполнению журнала, к примеру, до 50 %, останется гораздо меньшее возможностей для обработки других возможных модификаций данных, что делает более вероятным достижение порога усечения, после которого автоматическое усечение журнала будет невозможно. Транзакции в tempdb регистрируются так же, как и в любой другой базе данных. Опция TRUNCATE LOG ON CHECKPOINT, в большинстве случаев,  остаётся включённой для tempdb. Из-за этого, журнал транзакций постоянно усекается и не переполняется. Однако, любая из вышеупомянутых причин может заставить журнал базы tempdb переполниться. Конфигурация Tempdb обычно подразумевает размещение базы и журнала в одном файле (sysusages.segmap=7), вследствие чего данные и регистрационные операции будут конкурировать за одно и то же доступное дисковое пространство. Некоторые конструкции Transact-SQL, такие, как GROUP BY, ORDER BY DESC и т.д., будут автоматически требовать место в tempdb для своей работы. Это порождает неявные записи BEGIN TRANSACTION в журнале tempdb, отвлекая на это дополнительное место. Такая транзакция по базе tempdb будет продолжаться до завершения породившей её транзакции в пользовательской базе, что может задержать усечение журнала tempdb в течение этого времени. Если транзакция в пользовательской базе приостановлена по какой-либо причине, (включая блокировки или приложение, не обрабатывающее dbnextrow() для завершения), транзакция в tempdb, как и в предыдущем случае, останется открытой, мешая усечению журнала tempdb. Для решения такой проблемы, программист должен соответствующим образом отладить приложение и/или разрешать проблемы параллельного исполнения транзакций, которые порождают эту ситуацию.

Усечение журнала транзакций SQL Server 7.0 происходит путём усечения виртуальных журналов Virtual Log Files (VLF) из которых, как из кирпичиков, состоит журнал транзакций. Если в журнале существует активная транзакция, расположенная резидентно в одном из VLF, этот виртуальный журнал не может быть усечен. Если активные транзакции есть во всех виртуальных журналах, журнал транзакций не может быть усечен. Если включена опция автоматического роста журнала и есть достаточно места на диске, где находится журнал, и максимальный размер файла журнала ещё не достигнут, журнал транзакций будет увеличен на величину, указанную в свойствах журнала.

Нижеследующие замечания рассматривают тот случай, когда происходит усечение журнала при исполнении SQL запроса, в зависимости от того, включена ли опция TRUNCATE LOG ON CHECKPOINT:
- Если опция TRUNCATE LOG ON CHECKPOINT включена, и будет установлено во время запуска сервера, что журнал транзакций переполнен - его содержимое будет автоматически уничтожено опцией no_log.
- Опция TRUNCATE LOG ON CHECKPOINT - установлена по умолчанию в базе master, потому что его журнал транзакций не может быть вынесен на отдельное устройство, так что Вы будете весьма ограничены в возможностях при переполнении его журнала. Единственная возможность побороть переполнение журнала базы master, это отключить его.
- Если опция TRUNCATE LOG ON CHECKPOINT не установлена, и будет установлено во время запуска, что журнал транзакций переполнен - восстановление завершается, но конечная контрольная точка не отрабатывается. Администратор может войти в базу данных и резервировать журнал транзакций с помощью опции no_truncate, чтобы сохранить данные, а затем очистить его с помощью опции no_log.

ГОТОВИМСЯ К ТЕСТУ ПО 70-028

ШПАРГАЛКА #9 Продолжение (обзор официального курса Microsoft)
Архив шпаргалок Вы найдёте на следующих сайтах:
http://www.sql.ru/subscribe/
http://subscribe.ru/archive/comp.soft.winsoft.sqlhelpyouself

Мониторинг SQL сервера

Определить оптимальность работы сервера баз данных и выявить возможные неполадки или узкие места, можно с помощью средств мониторинга, предоставляемых SQL сервером. Грамотное использование этих средств позволит Вам вовремя определить и устранить причины низкой производительности сервера, которая определяется объёмом операций ввода-вывода, необходимых для исполнения транзакции, утилизацией процессоров и временем отклика системы. Для каждой конкретной реализации, уровень доступной производительности определяется составом аппаратно-программных средств и способов их использования. Наиболее наглядным фактором оценки производительности системы является её время отклика, которое представляет собой интервал времени, в течении которого сервер возвращает первую строку результата исполнения запроса, т.е. пользователь получает визуальное подтверждение того, что его запрос исполняется. Пропускная способность обслуживаемых сервером процессов и пользователей определяет сколько запросов возможно исполнить в фиксированный интервал времени, и сколько строк и какого размера возвращается клиенту. При увеличении числа активных процессов и/или пользователей, возрастает и их конкуренция за системные ресурсы. Результатом такой чрезмерной нагрузки может стать увеличение времени отклика и снижение общей пропускной способности. Большое влияние на производительности базы данных оказывает также и состояние/согласованность самих данных. Страницы данных и индексы могут оказаться повреждёнными, что приводит потере логической и физической согласованности. Для выявления таких повреждений используют специальное средство Database Consistency Checker (DBCC).

В числе факторов, которые способны повлиять на производительности системы, можно выделить, в первую очередь, аппаратную конфигурацию сервера. Количество процессоров и их быстродействие, количество и быстродействие дисков, объём оперативной памяти; всё это оказывает очень существенное влияние на уровень производительности. Не малую роль в общую производительность вносит и операционная система (ОС). Очевидно, что параллельно с процессами сервера баз данных будут выполняться процессы ОС и других, одновременно функционирующих на этом сервере программ. Также, на производительность могут повлиять файлы страничного обмена, их количество и местоположение. Использование RAID технологии также может в ту или иную сторону сказаться на общей производительности. Разумеется, нельзя исключать из систем, участвующих в оценке общей производительности, и сетевую среду, быстродействие сетевых соединений которой, а также её утилизация или уровень коллизий влияют на полосу пропускания сети, а следовательно, и на скорость передачи данных и запросов между клиентом и сервером. Сам SQL сервер, естественно, тоже оказывает влияние на производительность системы. Его конфигурация подразумевает динамическое распределение многих ресурсов и параметров, таких, как память, дисковое пространство подключения пользователей. Если у Вас нет веских на то оснований, лучше не вмешивайтесь в это динамическое распределение. Отрицательно сказываются на производительности сервера блокировки и большие объёмы журналируемых операций. Параллельное выполнение резервирования/восстановления, запуск DBCC или переиндексация могут также существенно сказаться на времени отклика сервера. Качество проектирования баз данных тоже имеет весомое значение. Производительность запросов может зависеть от уровня нормализации данных, их логической и физической структуры. Уровень контроля выполнения транзакций, как правило, влияет на количество и длительность блокировок. Часто повторяющиеся конфликты также замедляют работу. Зато оформление запросов в виде хранимых процедур может существенно поднять их «скорострельность», относительно незапланированных запросов, реализованных в логике клиента.  От логики работы клиентского приложения также зависит очень многое. Само число одновременно обращающихся к серверу баз данных и выбранной для этого схемы сетевых соединений, оказывает существенное влияние на распределение памяти сервера. Уменьшение числа конфликтов при обслуживании транзакций в состоянии существенно повысить общую производительность. Реакция клиентского приложение на блокировки и его способность повторно выставлять запрос или операторы модификации данных, может существенно разгрузить и оптимизировать работу сети и SQL сервера. Время отклика можно значительно сократить за счёт использования правильных видов курсора, сокращения объёмов извлекаемых данных и оптимизации использования кэша.

Продолжение следует.

ПОЛЕЗНОСТИ

Новые технические статьи Microsoft

Support WebCast: Analyzing Optimal Compiled Plan Caching
Q199872 - PRB: Some Database Tables Are Not Available for OLAP Cubes
Q252660 - INF: SQL Server 7.0 Clients Can Send Encrypted Password Strings
Q274797 - INF: SQL Server 7.0 Service Pack 3 Fixlist
Q286286 - FIX: LOCK_TIMEOUT Causes Transaction to Roll Back and @@error Does Not Capture Error 1222
Q290562 - BUG: HTTP Connection Resets Session ID of an ASP Session Object
Q290918 - BUG: Incorrect Behavior of LIKE [-] When Using an Index
Q293320 - PRB: DTS Transfer From SQL Server to Oracle Through OLE DB Provider Shows Ongoing Private Bytes Consumption
Q292808 - BUG: Backup Filegroup Error Message 3029 Subject to Logical Recovery after Creating a Clustered Index on Two or More Filegroups
Q281203 - BUG: Unable to Change Password in SEM when Server is Registered with Non-SA SQL Server Login
Q294209 - INF: Rebuilding or Moving MSDTC Used with a Failover Clustered SQL Server
Q279099 - PRB: Application Center 2000 Installation Fails with Wait Operation Timed Out When You Set MSDE Security

ФОРУМ SQL.RU: ДЮЖИНА САМЫХ ПОПУЛЯРНЫХ ТОПИКОВ НЕДЕЛИ

Как узнать права пользователя на операции над объектом?
SELECT с ограничением количества возвращаемых значений
Проблема с кодировкой при использовании DTS
BACKUP базы master не проходит
MS SQL ADO
Галлюцинации
Размер DB растет как на дожах. Помогите советом как это избежать
Размещение файлов данных на сетевом устройстве
Access MSSQL
Error details
СРОЧНО!! Формат даты в BCP
Где взять ломаный SQL SERVER 7.0

ФОРУМ SQL.RU: ВОПРОСЫ ОСТАЛИСЬ БЕЗ ОТВЕТА

Jobs...
поиск по множеству таблиц
Об ADO
Реплик. транзакций, помогите правильно настроить.....
Ошибка MergeReplication (to DmitryGolubev)
sql 70 - 6.5 Codepage 866-1251
Как убить задекламированную переменную ???
SQL-7 & dbf
MS SQL Server 7.0 MS Visual Foxpro
..could not configure 'NOTES_SQL' as the Distibutor for 'NOTES_SQL' ...- MS SQL7
Проблема с параметрами в запросе к связанному серверу
LookupCombo на GUID-поля в Delphi5

#043<<#044>>#045

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013