Parus16.ru

Парус №16
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Настройка MSSQL для работы с 1С — мифы и реальность

Настройка MSSQL для работы с 1С — мифы и реальность

Profile picture for user Олег

Оптимизируем Microsoft SQL Server 2014 для работы с 1С. ОС — Windows Server 2012 R2. Напишу что нужно сделать, а что не нужно.

Определяю мифичность очень просто, если мне пришлось применить настройку — реальность, не пришлось — миф.

Устанавливаем последний Service Pack и Cumulative Update — реальность

На текущий момент нужно обновиться до SP3.

Обязательно нужно сделать.

Выравнивание разделов — миф

Рекомендуют выравнивать сектора дисков по границе 1024Кб. Не заморачивайтесь, эта рекомендация устарела. Операционная система теперь сама выравнивает раздел как нужно для дисков более 4 ГБ.

HKLM SYSTEM CurrentControlSet Services VDS Alignment

sql

Эту настройку не применяем.

Форматирование с размером блока 64Кб

А вот здесь всё очень неявно. Есть статья Best Practice от Microsoft:

В ней явно говорится:

Partition the Disk volumes hosting SQL databases (Data and log) with 64 KB allocation unit size:
A drive can be formatted with different sizes ranging from 512 bytes to 64K sizes with the default being 4KB (4096 bytes). This setting is also called as “Bytes Per Cluster”. The atomic unit of storage in SQL Server is a page which is 8KB in size. Extents are groups of eight 8 KB pages that are physically contiguous to each other for a total of 64 KB. SQL Server uses extents to store data. Hence, on a SQL Server machine the NTFS Allocation unit size hosting SQL database files (Including tempdb files) should be 64K.

Кстати, обратите внимание на другие разделы статьи, возможно, там тоже найдёте для себя что-то полезное.

Казалось бы всё ясно, 64 килобайта — наше всё. Но. Откуда ноги растут? Есть рекомендация от Microsoft с 2008 года:

Здесь аргументированно с указанием конкретного железа, на котором производились испытания, пишут:

Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes. The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.

Снова 64 килобайта — наше всё. Читаем дальше:

An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. In many cases, this is the same size for Analysis Services data or log files, but there are times where 32 KB provides better performance. To determine the right size, you will need to do performance testing with your workload comparing the two different block sizes.

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

А теперь добавляем переменных:

  • Появились новые типы массивов типа SSD и NVMe.
  • Всё чаще SQL сервер устанавливается виртуально или в облаке, где файловая система виртуальной машины живёт внутри файловой системы гипервизора.
  • Производители жёстких дисков и RAID контроллеров используют кеширование и дополнительные технологии ускорения работы дисков.

Ещё следует учитывать, что NTFS для разных размеров диска создаёт разные размеры блока. Стандартный размер кластера (лучше использовать это название) в файловой системе NTFS:

  • ёмкость до 16 Тб — 4 килобайта
  • ёмкость от 16 до 32 Тб — 8 килобайт
  • ёмкость от 32 до 64 Тб — 16 килобайт
  • ёмкость от 64 до 128 Тб — 32 килобайта
  • ёмкость от 128 до 256 Тб — 64 килобайта
Читайте так же:
Единая карта гражданина россии

Так что если вы не хотите заморачиваться, то ставьте размер блока (кластера) при форматировании 64 килобайта. Однако не факт, что при этом ваш SQL сервер будет работать быстрее. Правильный ответ можно получить только при тестировании производительности.

Присылайте ссылки на статьи, где проводились исследования производительности для разных размеров кластера. Истина где-то там.

Database instant file initialization — частично миф, частично реальность

Рекомендуют включить возможность Database instant file initialization для пользователя, от которого запущена служба Microsoft SQL Server.

Что это за штука?

Во-первых, эта настройка влияет только на файл данных. Когда файл автоматически вырастает, то новый кусок заполняется нулями, в этот момент 1С может тормозить. Instant File Initialization (IFI) позволяет отключить это зануление.

  1. Запускаем Local Group Policy Editor:
  2. Слева выбираем Local Computer Policy, Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assigment.
  3. Тыкаем в Perform volume maintenance tasks.
  4. Сюда добавляем юзера, от имени которого запускается SQL Server.

Однако, у меня там уже прописана группа Administrators, а пользователь, под которым работает служба SQL Server уже в этой группе.

Так что у меня ничего не пришлось настраивать. Но вы у себя проверьте.

Давайте проверим, что всё работает. Рекомендуют создать новую БД размером 5 Гб и логом 1 Мб. Вот тут тоже нужно быть внимательным, нужно создавать под тем же пользователем, от которого запускается сервис SQL. Я создам от имени другого пользователя, который тоже в группе Administrators.

sql

База создалась мгновенно. На всякий случай попробую создать базу размером 50 Гб, место есть. Да, моментально на диске пропало 50 Гб и БД создалась быстро.

Instant File Initialization (IFI) работает из коробки, если пользователь, под которым работает сервис SQL входит в группу Administrators сервера.

Эту настройку применяем только при необходимости.

Lock pages in memory — реальность

Рекомендуют установить разрешение на Lock pages in memory (блокировку страниц в памяти) для пользователя, от которого запущена служба Microsoft SQL Server. Эта политика Windows определяет, какие учетные записи могут сохранять данные в физической памяти, чтобы система не отправляла страницы данных в виртуальную память на диске.

  1. Запускаем Local Group Policy Editor:
  2. Слева выбираем Local Computer Policy, Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assigment.
  3. Тыкаем в Lock pages in memory.

Эту настройку применяем. Нужно учитывать, что потребление оперативки возрастёт.

Power Option: High performance — реальность

Да, это реальность. По умолчанию план электропитания в Windows Server 2012 R2 — Balanced.

power

В некоторых случаях, Microsoft не уточняет в каких, на сервере может из-за этого снижаться производительность. Переключаем план на High performance.

power

Эту настройку применяем.

Сжатие содержимого — миф

compress

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

Индексация файлов — миф

По умолчанию галка Allow files in this folder to have contents indexed in addition to the file properties стоит:

indexing

Казалось бы, надо снимать. Подумаем, эта галка указывает службе индексации, что содержимое нужно обработать. Зачем на сервере индексировать содержимое? А стоит ли вообще служба индексации по умолчанию? Проверяем:

search

А служба поиска не установлена! Ну и забиваем на все эти галки. Эту настройку не применяем.

Исключить файлы БД из систем резервного копирования — миф

Это классика, не нужно ничего исключать, нужно просто нормально резервирование делать.

Во-первых, не отделяйте мух от котлет. Не держите приложение 1С и SQL Server на одном сервере. MSSQL должен жить отдельно. И резервироваться он должен средствами MSSQL. И зеркалироваться.

Эту настройку не применяем.

Резервирование и обслуживание — реальность

Эту настройку применяем.

Настройка памяти — реальность

Да, память для MSSQL нужно настраивать. По умолчанию сиквел жрёт всё что может, системе начинает не хватать. Как итог — тормоза. Нужно выделить SQL серверу строго определённое количество памяти и пусть не рыпается.

Не держите приложение 1С и SQL Server на одном сервере. MSSQL должен жить отдельно.

Это позволить избежать конкуренции за ресурсы.

Эту настройку применяем.

Настройка процессора — реальность

Эту настройку применяем.

Расположение файлов данных — реальность

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

sql

Эту настройку по возможности применяем.

Разбить tempdb на несколько файлов по количеству процессоров — миф

Эту настройку не применяем.

Перенести tempdb в RAM — реальность

Сам протестировал, добился 10-кратного прироста скорости чтения-запись для tempdb.

Эту настройку по возможности применяем.

Установить параметр Max degree of parallelism = 1 — частично миф, частично реальность

В закладке Advanced есть возможность установить параметр Max degree of parallelism. Кто-то рекомендует ставить 1, чтобы запретить параллельные запросы. Смысл в том, что один толстый запрос может сожрать все процессоры и помешать другим пользователям. С другой стороны, запущенная на ночь обработка могла бы выполняться быстрее при использовании нескольких процессоров. Давайте поступим иначе — ограничим половиной процессоров. У меня их 8, ставлю 4.

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

Прирост файлов БД — реальность

Меняем дефолтные настройки прироста размера файлов данных и лога.

По умолчанию прирост 10%, это никуда не годится. Если база 500 гигабайт, то одномоментно отобранные 50 Гб места могут негативно сказаться на работе.

Ставим прирост 200 Мб.

sql

Эту настройку применяем.

Флаги трассировки — реальность

4199 — включает исправления оптимизатора из фиксов.

1118 — использовать разные страницы памяти.

Запускаем SQL Server Configuration Manager. SQL Server Services. SQL Server — правой кнопкой свойства. Вкладка Startup Parameters. Добавляем -T1118 и -T4199.

sql

Для применения потребуется перезапустить SQL сервер.

Эту настройку применяем.

Сетевые протоколы — реальность

Протокол «Named pipes» необходимо отключить.

Запускаем SQL Server Configuration Manager. SQL Server Network Connfiguration. Protocols for MSSQLSERVER (наш инстанс). Делаем Named Pipes — Disabled.

sql

Эту настройку применяем.

Отключение DFSS — миф

DFSS по умолчанию устанавливается на терминальный сервер. При чём тут SQL? Отключать нечего.

Статья: 1С 7.7 и SQL 2005

В глобальный модуль (процедура ПриНачалеРаботыСистемы()) необходимо добавить код:

Если это не поможет — смотрите другие варианты решения проблемы здесь.

Ошибки при загрузке данных 1С в базе SQL <в начало>

SQL State: 42000

Message: [Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near ‘HOLDLOCK’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required.

Недопустимое состояние транзакции.

Необходимо установить compatibility level 80 (режим совместимости) в свойствах базы данных.

Требуется MS SQL Server 6.5 + Service Pack 5a или более старшая версия <в начало>

Порядок сортировки отличается от системного при загрузке базы 1С в формате SQL на WIndows 7 <в начало>

Необходимо допатчить bkend.dll 0018A79D: 75 EB для отключения проверки порядка сортировки либо опять же использовать секретный релиз платформы v77.27.1

Примечание к описанию секретного релиза, добавляем public чтобы не было ошибки: GRANT VIEW SERVER STATE TO public

Попробуйте заменить оригинальную BkEnd.dll на пропатченную Bkend.dll

SQL сервер не существует или доступ запрещен <в начало>

Вот такая ошибка может появиться на клиентском компьютере при запуске 1С.

Расскажу что нужно проверить и настроить

Если Вы используете SQL Express, то он именуется как ИмяСервераSQLEXRESS, не забывайте об этом.

Что нужно сделать на сервере:

Зайти в SQL Server Configuration Manager.

В конфигурации сервера и в клиентских протоколах оставить включенным только протокол TCP/IP.

Убедиться в том, что включена не только служба SQL Server, но и SQL Server Browser (Обозреватель SQL Server).

Если не помогает — отключить или проверить Брандмауэр.

В протоколах убрать динамический порт и выставить TCP-порт 1433.

После этого остановить службы и запустить заново.

На клиенте:

Открыть Пуск — Настройка — Панель управления — Администрирование — Источники данных (ODBC) — Системный DSN.

Там добавить SQL Server, ввести имя (например, Client), указать сервер (SERVERSQLEXPRESS или именованный SQL Server), нажать Далее, в настройке SQL клиента снять флажок «динамически определить порт» и указать порт 1433.

Можно сразу же и проверить доступность сервера:

Все, пробуем запускать 1С еще раз.

Если это не помогло — попробуйте вместо имени сервера (допустим, Server01, если экземпляр не именованный) ввести в параметрах соединения 1С IP, например 192.168.0.2.

Если после этого начались какие-то другие ошибки, значит Вы сделали все правильно 🙂

Не удалось выделить новую страницу для базы данных <в начало>

Вот такая ошибка может появиться при выполнении обмена, либо в процессе работы:

Физически увеличить размер файла базы данных также не получится, так как Вы используете, вероятно, SQL Express.

Объем базы данных для версии Express (для 2005 и 2008 SQL Server) не может превышать 4 Гб. 64-разрядная версия 2008 R2 SQL Server позволяет работать с базой до 10 Гб.

Таким образом, необходимо либо обрезать базу, либо использовать другую версию SQL Server. Developer Edition вполне можно приручить для работы нескольких пользователей на Winows XP или другой 32-разрядной системе.

Добавление пользователей базы данных

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

Управлять пользователями баз данных можно с помощью среды Management Studio или инструкций языка Transact-SQL. Оба эти способа рассматриваются в следующих подразделах.

Управление пользователями базы данных с помощью среды Management Studio

Чтобы добавить пользователя базы данных с помощью среды Management Studio, разверните узел сервера в окне Object Explorer и в нем папку «Databases», в этой папке разверните узел требуемой базы данных, а в ней папку «Security». Щелкните правой кнопкой мыши папку «Users» и в контекстном меню выберите пункт New User. Откроется диалоговое окно Database User — New, в котором следует ввести имя пользователя User name и выбрать соответствующее регистрационное имя Login name:

Диалоговое окно Database User - New

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

Управление безопасностью базы данных посредством инструкций языка Transact-SQL

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

Параметр user_name определяет имя, по которому пользователь идентифицируется в базе данных, а в параметре login указывается регистрационное имя, для которого создается данный пользователь. В параметрах cert_name и key_name указываются соответствующий сертификат и асимметричный ключ соответственно. Наконец, в параметре WITH DEFAULT_SCHEMA указывается первая схема, с которой сервер базы данных будет начинать поиск для разрешения имен объектов для данного пользователя базы данных.

Применение инструкции CREATE USER показано в примере ниже:

Для успешного выполнения на вашем компьютере второй инструкции примера требуется сначала создать учетную запись Windows для пользователя Alexandr и вместо домена (сервера) ProfessorWeb указать имя вашего сервера.

В этом примере первая инструкция CREATE USER создает пользователя базы данных Vasya для пользователя Vasya учетной записи Windows. Схемой по умолчанию для пользователя Vasya будет dbo, поскольку для параметра DEFAULT_SCHEMA значение не указано. Вторая инструкция CREATE USER создает нового пользователя базы данных Alex. Схемой по умолчанию для этого пользователя будет схема poco. (Параметру DEFAULT_SCHEMA можно присвоить в качестве значения схему, которая в данное время не существует в базе данных.)

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

С помощью инструкции ALTER USER можно изменить имя пользователя базы данных, изменить схему пользователя по умолчанию или переопределить пользователя с другим регистрационным именем. Подобно инструкции CREATE USER, пользователю можно присвоить схему по умолчанию прежде, чем она создана.

Для удаления пользователя из текущей базы данных применяется инструкция DROP USER. Пользователя, который является владельцем защищаемых объектов (объектов базы данных), удалить нельзя.

Схемы базы данных по умолчанию

Каждая база данных в системе имеет следующие схемы по умолчанию:

Компонент Database Engine позволяет пользователям, которые не имеют учетной записи пользователя, работать с базой данных, используя схему guest. (Каждая созданная база данных имеет эту схему.) Для схемы guest можно применять разрешения таким же образом, как и для любой другой схемы. Кроме этого, схему guest можно удалить из любой базы данных, кроме системных баз данных master и tempdb.

Каждый объект базы данных принадлежит одной, и только одной схеме, которая является схемой по умолчанию для данного объекта. Схема по умолчанию может быть определена явно или неявно. Если при создании объекта его схема по умолчанию не определена явно, этот объект принадлежит к схеме dbo. Кроме этого, при использовании принадлежащей ему базы данных регистрационное имя всегда имеет специальное имя пользователя dbo.

Вся информация о схемах содержится в схеме information_schema. Схема sys, как можно догадаться, содержит системные объекты, такие как представления каталога.

Настройка соединения 1С 8.2/8.3 с базой данных MySQL

В данной статье я не буду подробно расписывать как писать обработку для 1С чтобы приконнектиться к MySQL. У меня уже была обработка и я столкнулся с проблемой именно из-за отсутствия драйвера.

Пункт 1. Проблема

При переезде на другой сервер столкнулся со следующей проблемой (Произошла исключительная ситуация Microsoft OLE DB Provider for ODBC Drivers): не работал обмен 1С с внешней базой данных MySQL. Оно то и правильно что не работал. Он не должен, ведь я не установил ODBC Connector MySQL и как следствие при попытке обмена видел ошибку (у вас наверняка что-то подобное выползает на экран, если вы сюда попали гугля аналогичную проблему 😉)

Пункт 2. Какая версия MySQL ODBC ANSI Driver нужна?

И так. У нас для решения проблемы с подключением 1С 8.2/8.3 к промежуточной базе MySQL нам нужен, как я уже писал выше, ODBC Connector MySQL! И не простой, а именно тот, что у нас прописан в модуле обмена. Идём в Конфигуратор 1С, находим там модуль в котором прописано подключение и смотрим какой же чудесный драйвер и какой версии используется для подключения к внешней базе данных! И о чудо — это MySQL ODBC 5.3 ANSI Driver! В моём случае, конечно у вас он может быть другой версии. И на всякий случай вот код (смотрите ниже), можете сравнить со своим или вообще написать заново. Как вам больше нравится 🙂! И да, я тут пару фишек сделал. Вы можете вписать свои данные в поля и они сразу же отобразятся в коде, после чего вам остаётся скопипастить его себе прямо с сайта. Удобно? Ну да ладно. Код:

Пункт 3. Установка MySQL ODBC ANSI Driver

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

Пункт 4. Настройка соединения 1С 8.2/8.3 с внешней базой данных MySQL

Дальше нам понадобится программа odbcad32. Её устанавливать не нужно, она у вас уже есть! Ищите её в Администрирование или Ctrl + R odbcad32 Enter

Для 32 битных систем программа расположена:

А для 64 битных систем программа лежит тут:

Но и ту и другую вы можете открыть из Администрирование в панели управления.

Расположение Источника данных ODBC в Windows

Переходим во вкладку Системный DSN и жмем кнопку Добавить. выбираем MySQL ODBC 5.3 ANSI Driver или какой у вас там версии коннектор. Готово . После чего переходим в настройки, выделяем системный источник данных и кликаем Настройка. . Тут совсем всё просто, даже писать не буду, подставляете свои данные

Добавление драйвера в ODBC

Создание нового источника данных

Тестирование соединения с внешней базой данных MySQL

И конечно стоит разок прожать кнопку Test дабы убедиться что вы всё вписали верно. Если вы увидите сообщение: «Connection Successful«, жмите ОК и можете спать спокойно.

Пункт 5. Пару слов в заключение

Действительно тут нет ничего сложного, однако решил записать, так как дело это нехитрое и если ты не занимаешься настройкаой соединения базы 1С с внешней MySQL базой данных каждый день, то через N месяцев ты успешно можешь забыть всю процедуру и будешь заново гуглить. Я записал для себя, но если это пригодится и тебе, то будет просто здорово! Спасибо что дочитал до конца. А то обычно пользователи только успешно копипастят куски кода и ничего не читают 😒 !

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector