MS SQL Server - дело тонкое... Мы снова вместе!

ВЫПУСК #12

СОВЕТЫ

Восстановление связи между пользователем БД и его login

(По материалам статьи Neil Boyle на swynk.com "Fixing Broken Logins")
 http://www.swynk.com/friends/boyle/default.asp

При переносе базы данных на новый сервер копированием-восстановлением
или при переприкреплении файла базы данных, связь между login и
пользователями базы данных разрывается. На новый сервер перемещается
только половина информации, определяющей доступ клиента сервера к
данным, которая содержится непосредственно в базе данных. Вторая
половина необходимой для доступа к информации остаe:тся в таблице
syslogins первоначального сервера и содержит информацию, относящуюся
к logins. Из предыдущих выпусков рассылки Вы помните, что login - это не
тот же самое, что пользователь базы данных.
При вводе новой учe:тной записи подключения к SQL серверу (login), она
автоматически становится пользователем одной или более баз данных. Для
иллюстрации связей, которые при этом создаются, можно выполнить
представленный низе запрос:

select master..syslogins.name as login_name, sysusers.name as user_name
from master..syslogins inner join sysusers
on master..syslogins.suid = sysusers.suid

(Поле "Suid" - идентификатор (ID) учe:тной записи подключения к серверу,
полученный из таблицы syslogins. Suid = 1 - администратор системы,
Suid = -1 - учетная запись гостя).

Результат, который Вы получите, может различаться у разных баз и
серверов, но связь logins и пользователей базы данных будет видна.
Обратите внимание, что имена пользователей и logins не обязательно
совпадают, а учe:тная запись sa всегда соответствует пользователю базы
dbo.
В рассматриваемом нами случае, пользователи перенесe:нной на другой
сервер базы данных потеряют связь с соответствующими им учe:тными
записями подключения. Попытка простого добавления на новом сервере
недостающих logins успеха не возымеет ввиду того, что поля Suid у logins
и пользователей БД всe: же будут различны. Для правильного установления
связи между пользователем БД и login, воспользуйтесь хранимой процедурой
sp_change_users_login. Если клиент сервера баз данных не желает разглашать
свой пароль доступа, Вам поможет процедура sp_addlogin.

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

(По материалам статьи Neil Boyle на swynk.com "SQL Server two minute memory
tune-up")
 http://www.swynk.com/friends/boyle/default.asp



В своей статье, Нейл предлагает нам удобный способ оценки оптимальности
настроек SQL сервера для обеспечения максимального КПД использования
памяти. Ниже представлен используемый автором для этих целей скрипт,
который запускается, как на SQL 6.5, так и на 7 (с учe:том некоторых
оговорок, следующих по тексту статьи).

dbcc traceon(3604)
go
dbcc sqlperf(lrustats)
go
dbcc bufcount(1)
go
-- dbcc memusage - Перед использованием прочтите комментарий для  SQL Server 7
go
dbcc proccache
go
dbcc traceoff(3604)

Dbcc traceon - разрешает использование некоторых команд из представленного
скрипта (иначе они не выполняются). Traceoff - в конец выключает эту возможность.
Dbcc sqlperf(lrustats) - выдаe:т подробности использования кэша. Наиболее
интересные для Вашего анализа значения:
"cache hit ratio" - которое у Вас должно стремиться к 100 %;
"cache flushes" - которое выдаe:т число сброса данных из кэша на диск,
для высвобождения места другим данным. В идеальном случае, значение
"cache flushes" должно равняться нулю (если только размер Вашей базы
данных не значительно превышает размер ОЗУ).
Если выданные Вам значения не выглядят удовлетворительными, используйте
команду "Sp_configure memory", чтобы понять, имеет ли SQL сервер достаточно
памяти. Это особенно важно для 6.5, у которого по умолчанию установлено
очень маленькое значения отводимой серверу баз данных памяти. В SQL 7
память выделяется автоматически. См. "Причины не эффективного использования
кэша" в конце этой статьи, если с памятью у Вас всe: в порядке.
Размер памяти, которую Вы отдаe:те SQL 6.5, зависит от того, какие еще
задачи обслуживает ваш сервер. Для получения более подробной информации,
посмотрите статью Microsoft:
http://support.microsoft.com/support/kb/articles/Q110/9/83.asp
которая содержит примеры, рекомендации и параметры настройки памяти.

Dbcc bufcount(1) пользователи SQL 7 могут пропустить эту команду, поскольку
конфигурирование у них автоматическое. Для пользователей SQL 6.5 особый
интерес представляет выводимая в отчe:те строка, подобная "The Average Chain
Size is: 2.922601".
Это означает, что  эффективность структуры индексации кэша - оценивается
между 2 и 4.
Величина близкая к 3, является оптимумом в SQL 6.5. Помочь в регулировке
этого значения может - "sp_configure hash buckets", которая плохо
документирована в документации по SQL 6.5. Как правило, у многих DBA сохраняется
настройка по умолчанию. Дополнительную информацию относительно
этой установки смотрите на узле поддержки Microsoft:
http://support.microsoft.com/support/kb/articles/Q151/2/56.asp
Dbcc memusage детализирует информацию о Ваших больших таблицах, которые
находятся в настоящее время в кэше. Идеально они должны находиться в кэше
как можно дольше, так что желательно, при каждом запуске скрипта, помечать
себе какие объекты находятся в кэше, и какое пространство они там занимают.
SQL 6.5 детализирует также информацию о самых больших хранимых процедурах,
находящихся в Вашем кэше. Более подробно о процедурах - ниже по тексту.
Предупреждение для пользователей SQL 7! Статья Microsoft:
http://support.microsoft.com/support/kb/articles/Q196/6/29.ASP
не рекомендует Вам выполнять команду dbcc memusage на SQL 7. Нейл пишет,
что никогда не имел проблем с этой командой, но советует Вам не
использовать еe: на промышленных серверах.
Dbcc proccache выводит краткую информацию об использования кэша процедур.
В SQL 7 достаточно дескрипторов, характеризующих это, но в SQL 6.5
дополнительная информация, выдаваемая этой командой, будет Вам полезна. Это
происходит потому, что SQL 6.5 по умолчанию распределяет 30 % памяти SQL
сервера для кэша хранимых процедур, в то время как остальная память
отводится данным. В конфигурациях с большой емкостью памяти это может
привести к тому, что для процедур выделяется не оправдано большая часть
памяти, которая фактически не используется, а могла бы быть выделена для
кэширования данных. Для регулировки этого значения можно использовать
- "sp_configure procedure cache".
Помните, что существует опасность для нормальной работы сервера баз данных,
если установлено слишком маленькое значение кэша процедур. Изменяйте это
значение поэтапно и на небольшую величину и никогда его не обнуляйте. Этой
теме посвящена статья Microsoft:
http://support.microsoft.com/support/kb/articles/Q192/9/62.ASP

Причины не эффективного использования кэша

Наиболее очевидной причиной неэффективного использования кэша является
недостаток доступной оперативной памяти или выделение SQL серверу
недостаточного еe: количества. Нейл рекомендует, прежде чем Вы отправитесь
к поставщикам за дополнительными DIMM модулями, проанализируйте другие
возможные причины не эффективного еe: использования:
1. Отсутствие индексов может вызывать частое сканирования таблиц, которые
вытесняют другие данные из кэша. В SQL 7 можно наблюдать эти процессы с
помощью SQL Profiler и Index Wizard. Ознакомьтесь с материалами на
http://www.swynk.com/friends/boyle/indextuningwizard.asp,
чтобы обнаружить
вредные сканирования таблицы и улучшать эффективность использования
индексов. К сожалению, пользователи SQL 6.5 должны делает это более сложным
путe:м.
2. Плохо организованный проект базы данных и не достаточно продуманные
проекты запросов могут приводить к сканированию таблицы без использования
существующих индексов, что становится причиной чрезмерной утилизации
дисковой подсистемы и неэффективному использованию памяти.
3. Смешивание баз данных с OLTP (короткие транзакции) и OLAP (большие
отчe:ты) на одном сервере баз данных, также можно стать причиной
неэффективного использования кэша и появления других проблем.
Дополнительную информацию на эту тему можно почерпнуть из  статьи Microsoft: http://support.microsoft.com/support/kb/articles/Q110/3/52.asp

Другие возможности распределения памяти

Кэш это один из наиболее важных показателей ОПЕРАТИВНОЙ ПАМЯТИ, но ни в
коем случае ни единственно важный. SQL сервер выделяет память для кэширования только
после того, как выполнены многие другие требования. Из
этого следует, что Вы можете увеличивать или уменьшать количество памяти,
доступной для кэша,  регулируя распределение памяти между другими аспектами
сервера, особенно это существенно для версии 6.5, которая не умеет
динамически распределять память, как более поздние версии.
Для примера, посмотрите некоторые параметры настройки, которые Вы можете
изменять:
Sort Pages (только 6.5);
Index Create Memory (только 7);
Locks;
Open Objects;
Tempdb In RAM (только 6.5, и обычно не рекомендуемые Microsoft http://support.microsoft.com/support/kb/articles/Q115/0/50.asp);

Существуют и другие важные параметры, о которых Вы можете узнать на
http://support.microoft.com/
Предупреждение! Неправильная установка для любого из перечисленных
параметров может привести к "краху" Вашего сервера! Поэтому, тщательно
изучите рекомендации Microsoft по установке и изменению конфигурационных
значений и меняйте их постепенно на небольшую величину, анализируя
поведение сервера с помощью специализированных утилит или по результатам
работы соответствующих процедур.
КОМЕНТАРИЙ АВТОРА РАССЫЛКИ:
От себя к этому добавлю, что нельзя забывать также о том, что сервер баз
данных находится в плотном взаимодействии с операционной системой и другим
программным и аппаратным обеспечением. Поэтому проблемы кэширования и распределения
памяти нужно анализировать комплексно, особенно тщательно
следя за разделяемыми между сервером, ОС и другими программами ресурсами.

Хранимые процедуры XP_ENUM

(По материалам статьи Brian Knight на swynk.com "XP_ENUM Extended Stored Procedures")

Изучая возможности расширенного набора хранимых процедур, Брайен предлагает
нам обратить внимание на очень интересную их группу xp_enum. Эти процедуры
не требуют никаких параметров, но обеспечивают удобный мостик к информации
об операционной системе SQL сервера, и чрезвычайно полезны для
разработчиков.
Xp_enumgroups - отобразит все группы NT, которые созданы в настоящее время на вашем
SQL СЕРВЕРЕ. Она не требует никаких параметров и выгружает
результат в varchar (255) формате, удобном для загрузки во временную
таблицу.
Xp_enumdsn - выдаe:т список используемых SQL сервером коммутационных сетей.
Xp_enum_activescriptengines - также хорошая хранимая процедура, которая
отобразит все scripting engines, установленные на вашем сервере в настоящее
время.
Представленные выше процедуры выполняются от имени учe:тной записи сервера
Windows NT, под которой стартуют сервисы SQL сервера.

ГОТОВИМСЯ К ТЕСТУ ПО 1139A

ШПАРГАЛКА #3  продолжение (обзор официального курса Microsoft)

Вспомогательные средства обеспечения безопасности SQLS7

Использование представлений и хранимых процедур, при условии назначения
разрешений к ним, а не к используемым ими объектам, позволяет организовать
дополнительный уровень безопасности на уровне приложения. Такой подход,
при использовании представлений, скрывает от пользователя изменения,
вносимые в данные используемые представлениями. Определив представление, в котором
включены только те столбцы, которые должны быть доступны
пользователю, мы можем ограничить ему доступ к информации в столбцах
таблицы, которая должна быть ему недоступна.
Кроме того, такое применение представлений и хранимых процедур может
оказаться эффективней по производительности и с точки зрения удобства
администрирования, чем обеспечение безопасности на уровне столбцов.
С помощью предоставления пользователям разрешений на выполнение
хранимых процедур, можно добиться того, что пользователям не будет
разрешено работать непосредственно с таблицами, но те операции, которые
формализованы в самой процедуре.
Использование механизма ролей может помочь разграничить доступ к данным
из разных клиентских приложений. Если обеспечить доступ пользователей к
данным из конкретного приложения, которое работает через определe:нную
специально для него роль приложения с уникальными правами на доступ к
данным, то пользователи смогут выполнять только разрешe:нные этой роли
действия, а другие приложения, не имеющие разрешения на использование
этой роли, работать с этими данными не смогут. Однако у такого типа ролей
есть определe:нные особенности. Роли приложений могут быть задействованы
пользователем только при запуске приложения и не имеют членов. Т.е.
разрешения действительны для пользователя только в момент работы
конкретного приложения, которому сопоставлена роль, определяющая набор этих
разрешений.
Использование роли приложений потребует ввода пароля. После запуска
приложения, с момента вступления в силу разрешений определяемых ролью
приложения, пользователь теряет все другие разрешения в текущей базе, за
исключением относящихся к роли public. Фактически происходит наследование
разрешения роли приложения в текущей базе. Кроме всего прочего,
пользователю становится недоступно выполнение оператора USE в текущей базе.
Правда, через учe:тную запись guest можно получить доступ к информации
других баз через ввод полных имe:н объектов, если таковой предоставлены
необходимые разрешения.
Участники ролей db_owner, db_securityadmin и sysadmin с помощью стандартных
возможностей SQL SEM или с помощью специальной системной процедуры
(sp_addapprole 'роль', 'пароль, который будут вводить пользователи для
использования роли') могут создавать роли приложений, запись о которой
добавляется в sysusers текущей базы. Удаляет роль sp_dropapprole (если под
этой ролью нет активных пользователей), а изменить пароль можно с помощью
sp_apprplepassword. Управление разрешений  ролей приложений осуществляется
из SQL SEM или с помощью операторов GRANT, REVOKE и DENY. Например,
DENY SELECT ON 'имя таблицы' TO 'имя роли приложения'.
Для активизации роли приложения подключe:нного к серверу баз данных клиента
через сопоставленное этой роли приложение, необходимо, что бы в этом
приложении был выполнен оператор T-SQL sp_setapprole. Выполнить этот
оператор из хранимой процедуры или  в определe:нной пользователем транзакции
не получится.
Синтаксис оператора следующий:

sp_setapprole  [@rolename=] 'роль',
  [@password=] {Encrypt N 'пароль'} | 'пароль'
  [,[@encrypt=] 'стиль_шифрования']

Приложение должно уметь подставлять пароль, даже если он зашифрован,
например, с использованием функции ODBC ENCRYPT. При шифрации пароля он
должен быть преобразован в строку типа Unicode, посредством размещения
непосредственно перед ним символа "N".
Роль приложения ограничивает права пользователя только в пределах текущей
базы данных. В других базах пользователь сохраняет разрешения, которые ему
там установлены.

Окончание ШПАРГАЛКИ #3

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