Путевые заметки про SQL Server разных версий

[apanko]

Режимы восстановления базы данных (recovery models) баз данных SQL Server 2005, полное протоколирование (full), неполное протоколирование (bulk-logged), простая модель восстановления (simple)

Одно из важных решений, которые нужно принять при создании базы данных — в каком режиме восстановления будет работать база. Этот параметр выбирается на вкладке Options свойств базы данных в строке Recovery Model (Режим восстановления) (над списком остальных параметров). Изменить режим восстановления базы данных можно также при помощи команды ALTER DATABASE.

Всего предусмотрено три режима восстановления базы данных.
q Full (режим полного протоколирования) — в этом режиме максимальное количество операций записывается в журнал транзакций. Журнал транзакций автоматически не обрезается. Этот режим обеспечивает максимальные возможности восстановления (за счет снижения производительности). Только в этом режиме вы можете использовать зеркальное отображение баз данных и автоматическую доставку журналов (log shipping). Именно этот режим выбирается по умолчанию для пользовательских баз данных, поскольку он настроен для базы данных model. Если изменить режим восстановления для базы данных model, то для создаваемых баз данных по умолчанию будет выбираться новый режим.

q Bulk-logged (режим неполного протоколирования) — это компромисс между требованиями производительности и возможностями восстановления. При использовании этого режима запись в журнал практически отключается (в терминологии Microsoft — проводится минимальное протоколирование) для операций следующих типов:

• массовой вставки (команды BULK INSERT, SELECT INTO, загрузка средствами bcp и т. п.);
• вставка/изменение больших двоичных данных (text, ntext, image);
• операции по созданию, перестроению и удалению индексов.

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

q Simple (простая модель восстановления) — максимальный выигрыш в производительности и удобстве работы за счет возможностей восстановления. Минимально протоколируются те же операции, что и в режиме восстановления Bulk-logged, а кроме этого, журнал транзакций автоматически очищается (блоками, размер которых изначально равен 256 Кбайт, но при необходимости он может быть автоматически увеличен). В результате вы получаете максимальную производительность и возможность не думать о потенциальной нехватке места в журнале транзакций. Но в этом режиме использовать журнал транзакций для восстановления уже удасться. Вы не сможем даже выполнить резервное копирование журнала транзакций: команда BACKUP LOG в этом режиме сразу вернет ошибку.

Какой же режим восстановления выбрать? Microsoft (в своих учебных курсах) рекомендует для рабочих баз данных выбирать только режим Full. Однако из опыта проведения автором этих самых учебных курсов и общения со слушателями можно сказать, что очень многие опытные администраторы сознательно настраивают для своих баз данных режим восстановления Simple. Значительное повышение производительности при операциях массовой вставки и при работе с большими двоичными данными вполне оправдывает некоторое снижение возможностей резервного копирования и восстановления. Что важнее для вашей задачи — дополнительные возможности восстановления или максимальная производительность, решать вам.

http://www.askit.ru/custom/sql2005_a...very_model.htm

(пригодится)

[apanko]

Обычно после создания логина и пользователя базы данных следующее, что нужно сделать, — предоставить пользователю разрешения в базе данных. Один из способов сделать это — воспользоваться ролями базы данных.
Роли базы данных — это специальные объекты, которые используются для упрощения предоставления разрешений в базах данных. В отличие от серверных ролей, которые могут быть только встроенными, роли баз данных могут быть как встроенными, так и пользовательскими. Встроенные роли баз данных обладают предопределенным набором разрешений, а пользовательские роли можно использовать для группировки пользователей при предоставлении разрешений. Обычно пользовательские роли используются только для логинов SQL Server (хотя им можно назначать любых пользователей баз данных, в том числе созданных на основе логинов Windows). Для группировки логинов Windows обычно удобнее и проще использовать группы Windows.
Еще одна специальная разновидность ролей баз данных — роли приложений (application roles). Речь о них пойдет в разд. 5.4.
Вначале перечислим встроенные роли баз данных, которые "готовы к использованию" для предоставления разрешений:

q public — эта специальная роль предназначена для предоставления разрешений сразу всем пользователям базы данных. Обратите внимание на то, что она выполняет другие функции по сравнению со специальным пользователем guest. Пользователь guest используется для предоставления разрешений логинам, для которых не создано пользователей в базе данных, а public — только существующим пользователям.
Специально сделать пользователя членом этой роли или лишить его членства невозможно. Все пользователи базы данных получают права этой роли автоматически. Интересно, что при добавлении пользователя этой роли никакой ошибки не возникнет, но при следующем открытии ее свойств пользователь исчезнет из списка;

q db_owner — этой роли автоматически предоставляются полные права на базу данных. Изначально права этой роли предоставляются только специальному пользователю dbo, а через него — логину, который создал эту базу данных;

q dbo_accessadmin — роль для сотрудника, ответственного за пользователей базы данных. Этот сотрудник получит возможность создавать, изменять и удалять объекты пользователей баз данных, а также создавать схемы. Других прав в базе данных у него нет;

q dbo_securityadmin — эта роль дополняет роль dbo_accessadmin. Сотрудник с правами этой роли получает возможность назначать разрешения на объекты базы данных и изменять членство во встроенных и пользовательских ролях. Прав на создание и изменение объектов пользователей у этой роли нет;

q db_backupoperator — эта роль дает право, как понятно из ее названия, выполнять резервное копирование базы данных;

q db_ddladmin — эта роль применяется в редких ситуациях, когда пользователю необходимо дать право создавать, изменять и удалять любые объекты в базе данных, не предоставляя прав на информацию, которая содержится в существующих объектах;

q db_datareader и db_datawriter — эти встроенные роли дают право просматривать и изменять соответственно (в том числе добавлять и удалять) любую информацию в базе данных. Очень часто пользователю необходимо дать права на чтение и запись информации во всех таблицах базы данных, не предоставляя ему лишних административных разрешений (на создание и удаление объектов, изменение прав и т. п.). Самый простой вариант в этой ситуации (о котором забывают некоторые администраторы) — воспользоваться этими двумя ролями.

q db_denydatareader и db_denydatawriter — эти роли, как понятно из названий, противоположны ролям db_datareader и db_datawriter. Роль db_denydatareader явно запрещает просматривать какие-либо данные, а db_denydatawriter запрещает внесение изменений. Явный запрет всегда имеет приоритет перед явно предоставленными разрешениями. Обычно эти роли используются в ситуации, когда "разрешаем всем, а потом некоторым запрещаем".

Как уже говорилось ранее, в отличие от серверных ролей роли баз данных вы можете создавать самостоятельно. Это можно сделать из контекстного меню для контейнера Имя_базы_данных | Security | Roles | Database Roles или при помощи команды CREATE ROLE, например:
CREATE ROLE DBRole1;
Кроме имени роли, при создании можно также указать ее владельца (если это не указано, владельцем роли автоматически станет создавший ее пользователь базы данных). Если вы создаете роль при помощи графического интерфейса, вы можете сразу назначить роль владельцем схемы в базе данных и назначить пользователей, которые будут обладать правами этой роли.
Встроенным ролям назначены предопределенные права, изменить которые невозможно. Предоставление прав пользовательским ролям производится точно так же, как и обычным пользователям базы данных.

http://www.askit.ru/custom/sql2005_a...5_db_roles.htm

[apanko]

Архитектура управления памятью

В этом разделе описывается архитектура управления памятью компонента SQL Server Database Engine.
В этом разделе:
Архитектура оперативной памяти Описывает динамическое управление памятью в SQL Server.

Адресное пространство процесса Описывает количество физической и виртуальной памяти, доступной приложениям.
Динамическое управление памятью Описывает, как SQL Server управляет памятью для буферного пула.
Параметры настройки min server memory и max server memory Описывает влияние параметров min server memory и max server memory.
Требования к объему памяти для хранения объектов SQL Server Приводит примерные сведения об объеме памяти, занимаемой различными объектами SQL Server.
Управление буферами Содержит сведения о способах доступа и обновления страниц данных диспетчером буферов.
Управление памятью для больших баз данных Содержит сведения о таких специальных настройках памяти, как расширения AWE, которые позволяют использовать преимущества наиболее современного оборудования.
MSDN ссылки про SQL Server 2008

-------------------------------------------

Tips for DBA: выравнивание кластеров NTFS и блоков RAID-массивов


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


-------------------------------------------

Поддержка памяти большого размера в Windows Server 2003 и Windows 2000
В этой статье рассмотрены технологии РАЕ (Physical Address Extension) и AWE (Address Windowing Extensions), а также взаимодействие этих технологий и ограничения, связанные с использованием памяти за пределами используемого 32-разрядными операционными системами диапазона 4 ГБ. ....

[apanko]

Freddy сообщил о том, что MS сделал доступным для скачивания Microsoft SQL Server Management Studio Express (ссылка для скачивания)

А вот за чем я про это говорю:
http://forum.mazzy.ru/index.php?showtopic=12531

[apanko]

Ставил SQL Server на Windows Vista. Reporting Services ставиться не хотел (соотвествующий check-box был недоступен, не смотря на установленный IIS).
Нашел в интернете рекомендации от MVP по SQL Server и с ее помощью победил http://msmvps.com/blogs/gladchenko/a...0/1184163.aspx

[apanko]

Хотел поставить SQL 2008 Developer Visual Studio 2008, ну и чтобы это все работало с Навижн. В частности с НАВ5 (а затем и 2009).

Убил пол дня. Видел следующие ошибки:
* SQL не хотел ставиться пока не будет установлен SP1 для VS
* Нав ни как не мог видеть флаг трассировки 4616, хотя я его установил и сервер перезапустил
* Режим Repair при установке SQL работать не хотел. Точнее были ошибки, хотя часть из них удалось победить с помощью статьи поддержки с сайта MS, полностью нормально устанавливаться SQL не хотел.

TITLE: Microsoft SQL Server 2008 Setup
The following error has occurred:
Error 1316.A network error occurred while attempting to read from the file ...\en_sql_server_2008_developer_x86_x64_ia64_dvd_ x14-88839\x86\setup\SSCERuntime-enu.msi

* На криво установленный SQL не хотел устанавливаться SP1.

Программа установки SQL Server обнаружила следующую ошибку:
A failure was detected for a previous installation, patch, or repair for instance 'MSSQLSERVER' during configuration for features [MPT_AGENT_CORE_CNI,]. In order to apply this patch package (KB968369), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed.
Код ошибки 0x84B20001.

Мораль:
Вначале ставим SQL, затем ему SP1, а уже потом Visual Studio 2008 и его SP1.

[apanko]

Опять мигрировал.
Теперь у меня SQL 2008 R2

Базы перенес легко и приятно (с помощью задачи по копированию баз данных Task -> Copy Database)

С флагом трассировки опять были проблемы.
Запишу команды которыми пользовался, в процессе диагностики:
DBCC TRACESTATUS (4616,-1)
DBCC TRACEON (4616, -1)

[apanko]

Открыл SQL Server Configuration Manager, щелкнул по серверу правой кнопкой мыши и выбрал пункт свойства. Перешел на закладку Advanced и немного удивился не увидев в списке пункт Startup parameters.

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

[apanko]

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

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

Хотя это не универсальный импорт ни разу.

И не с первого раза конечно все получилось. Тем не менее, новый инструмент освоил.

[apanko]

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

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

Был вариант перезалить мастера, но мне этот вариант не улыбался ни разу (8 часов). Решил использовать SQL.
Удалил неправильные мастер-данные командой:

 
DELETE FROM [IMS_MD_MigrationTest].[dbo].[Item Master Price]
GO

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

INSERT INTO [IMS_MD_MigrationTest].[dbo].[Item Master Price]
   ([Item No_]
   ,[Make Code]
   ,[все поля])
SELECT 
    [Item No_]
   ,[Make Code]
   ,[все теже поля]
FROM [IMS_MD_Release].[dbo].[Item Master Price]
GO

Со звездочкой в SELECTе не получилось из-за поля timestamp.

Управился за 15 минут. 3 на удаление, 2 на вставку и 10 на BING

[apanko]

Вышел SQL Server 2012 Release Candidate - RC0.
http://www.microsoft.com/download/en....aspx?id=28145

Вот сижу и думаю - пробовать или обождать.

[apanko]

В Dynamics NAV, работающем на SQL Server нужно синхронизировать логины, периодически получаем ошибки (особенно когда базы между серверами носим)

Системы безопасности Microsoft Dynamics NAV и SQL Server не были синхронизированы.

Вход SQL Server ADMIN не существует на сервере APANKO-ACER.

Значит, что на SQL Server не существует пользователя ADMIN.
Следует создать пользователя на SQL Server.


А вот недавно поймали интересное:

Произошла следующая ошибка(и) SQL Server:
15138,"42000",[Microsoft][ODBCSQL Server Driver][SQL Server]The database principal owns a schema in the database, and
cannot be dropped.
SQL:
{CALL[sp_revokedbaccess](?)}

Причина – попытка удалить Database User, который является Owner для схем, которыеиспользуются как Default Schema в других Database User(см. скриншоты, там видно, что пользователь User является Owner для схемы Admin, которая является схемой поумолчанию для пользователя Admin).
Как править, думаю понятно: или сменить Owner'a в схеме, или изменить схему по умолчанию.

Написал скрипт умеренной полезности, который показывает проблемые SQL логины, проблемные Windows логины и проблемные схемы.
Сильно не тестировал, кусок по преобразованию SID взял с прекрасного ресурса sql.ru.

[apanko]

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

http://support.microsoft.com/kb/918992

[apanko]

Криво сделали детач базы.
Обратному аттачу не поддавался, говорил примерно следующее:


TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'MYSQLSRV'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An error occurred while processing the log for database 'MYDATABASE'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Could not open new database 'MYDATABASE'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 9004)
------------------------------


Помог аттач с пересозданием лога (именно он поломался как видно в сообщении).

CREATE DATABASE database_name FOR_ATTACH_REBUILD_LOG


А вот и ссылочка в тему: http://blog.sqlauthority.com/2008/07...build-the-log/

[apanko]

Проблема (НАВ5):

You cannot start Microsoft Dynamics NAV because you donot have the VIEW SERVER STATE PERMISSION SQL Server".


Решение:

GRANT VIEW SERVER STATE TO Public

[apanko]

Кто подключен к какой базе:

SELECT     DB_NAME(dbid) as 
DBName,     COUNT(dbid) as 
NumberOfConnections,     
loginame as LoginName 
FROM     sys.sysprocesses WHERE     dbid > 0 GROUP 
BY     dbid, loginame ;