Уровень совместимости базы данных ms sql для 1с
Установка SQL Server для работы с 1С:Предприятие 8.3
В этой заметке мы сделаем упор лишь на тех этапах процесса развёртывания нового экземпляра SQL Server, которые могут иметь значение для работы баз данных 1С:Предприятие 8.3.
На этапе установки Feature Selection достаточным минимумом будут компоненты Database Engine Services. Если вместе с сервером SQL Server на этом же сервере планируется развёртывание серверной части 1С:Предприятие, то потребуется установить ещё и компоненту Client Tools Connectivity. Для локального подключения и возможности локального администрирования SQL Server потребуется установить компоненты Management Tools.
Имейте ввиду, что компоненты Management Tools, включающие в себя такой инструмент, как SQL Server Management Studio, являются опциональными (не нужны для работы 1С) и они отсутствует в установщике начиная с SQL Server 2016. При необходимости эти компоненты могут быть установлены отдельно из отдельно загружаемого инсталлятора SQL Server Management Studio)
Обратите внимание на то, что входящие в состав Database Engine Services компоненты SQL Server Replication, Full-Text and Semantic Extractions, Data Quality Services не требуются для работы 1С:Предприятие и включены на представленном скриншоте только по той причине, что графический инсталлятор SQL Server 2016 не позволяет выключить данные компоненты в том случае, если включена родительская компонента Database Engine Services.
На этапе установки Server Configuration на закладке Server Accounts в качестве учётных записей, от имени которых будут работать службы SQL Server, в инфраструктурах с доменом Active Directory, с точки зрения усиления уровня безопасности, более правильным будет использование учётных записей типа Group Managed Service Account (gMSA), для которых управление паролями выполняется в автоматическом режиме службой Key Distribution Service на контроллерах домена.
Здесь обратите внимание на то, что в инсталляторе SQL Server 2016 есть опция Grant Perform Volume Maintenance Task, которую здесь мы оставили нетронутой, так как фактически мы уже выдали такое право ранее, когда говорили о предоставлении привилегии SE_MANAGE_VOLUME_NAME через политику безопасности Perform volume maintenance tasks. Замечено, что при включении данной опции, инсталлятор SQL Server 2016 вместо того, чтобы добавить в политику Perform volume maintenance tasks указанную нами сервисную учётную запись, от имени которой фактически будет запускаться экземпляр SQL Server, добавляет учётную запись службы (Virtual Account) вида NT Service\MSSQL$ . Объяснить природу такого поведения инсталлятора может обсуждение Раздача прав сервисным учётным записям при установке SQL Server 2016
Важно.
В перспективе желательно придерживаться того правила, чтобы порядок сортировки создаваемых в дальнейшем баз данных для 1С не отличался от порядка сортировки самого экземпляра SQL Server, который мы укажем на этапе его развёртывания. Если пренебречь этим правилом, то в дальнейшем мы можем столкнуться с проблемами, описанными, например, в ветке обсуждения Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"
На этапе установки Database Engine Configuration на закладке Server Configuration в соответствии с 1С:ИТС - Настройки Microsoft SQL Server для работы с 1С:Предприятием необходимо выбирать смешанный режим аутентификации – Mixed Mode.
Не стоит забывать про администраторов, которым в дальнейшем может потребоваться доступ к устанавливаемому экземпляру SQL Server. То есть, как минимум, в качестве администраторов устанавливаемого экземпляра SQL Server можно назначить встроенную группу администраторов сервера.
На этапе установки Database Engine Configuration на закладке Data Directories для расположения баз 1С укажем производительный дисковый массив или отдельные дисковые массивы. По возможности желательно определить для размещения файлов БД и логов транзакций отдельные дисковые массивы, учитывая то обстоятельство, что под транзакционные логи требуется более производительный по операциям записи массив.
На этапе установки Database Engine Configuration на закладке TempDB фалы системной базы tempdb также очень желательно размещать на отдельном быстром дисковом томе. В качестве достойного претендента на эту роль будет отдельный массив SSD-дисков или RAM-диск. Один из вариантов настройки RAM-диска, который можно будет использовать в кластерных развёртываниях SQL Server, мы рассмотрели ранее в статье Организуем RAM-диск для кластера Windows Server с помощью Linux-IO FC Target.
В нашем примере помимо основного размещения файлов tempdb на том же дисковом массиве, где предполагается размещение пользовательских БД, добавлен ещё один каталог, расположенный на быстром RAM-диске. Этот же каталог указан в качестве расположения для транзакционного лога tempdb.
Дополнительную реконфигурацию размещения файлов базы tempdb можно будет выполнить и после установки SQL Server. Этому вопросу будет посвящена последующая заметка Файлы системной базы данных tempdb.
Важно.
1С:Предприятие 8.3 в своей работе может очень активно использовать ресурсы системной базы данных tempdb, а недостаточная производительность дисковых томов, на которых расположены файлы tempdb, может привести к существенной деградации общего уровня производительности в конфигурациях 1С и ощутимо ухудшить комфорт пользователей, работающих с 1С. Поэтому очень важно подойти к вопросу планирования производительных дисковых ресурсов под эту системную БД.
Неотъемлемой частью правильного процесса установки SQL Server является то, что сразу после завершения развёртывания экземпляра SQL Server желательно выполнить развёртывание последнего Service Pack (SP), а затем и последнего Cumulative Update (CU) для установленной версии SQL Server. Информацию об актуальных SP и CU для разных версий SQL Server можно найти в документе Where to find information about the latest SQL Server builds. Помимо этого, ссылки на страницы загрузки последних CU можно найти на нашей Вики-странице Microsoft SQL Server
Проверено на следующих конфигурациях:
Версия ОС | Версия SQL Server |
---|---|
Microsoft Windows Server 2012 R2 Standard EN (6.3.9600) | Microsoft SQL Server 2016 SP2 CU4 (13.0.5233.0) |
Автор первичной редакции:
Алексей Максимов
Время публикации: 12.02.2019 14:23
В этой статье описано, как просмотреть или изменить уровень совместимости базы данных в SQL Server, Базе данных SQL Azure или Управляемом экземпляре SQL Azure с помощью SQL Server Management Studio или Transact-SQL.
Перед изменением уровня совместимости базы данных проанализируйте, как это повлияет на имеющиеся приложения. Дополнительные сведения см. в разделе Уровень совместимости инструкции ALTER DATABASE (Transact-SQL).
Permissions
Необходимо разрешение ALTER на базу данных.
Использование среды SQL Server Management Studio
Чтобы просмотреть или изменить уровень совместимости базы данных SQL Server Management Studio (SSMS)
Подключитесь к соответствующему серверу или экземпляру, на котором размещена ваша база данных.
Выберите имя сервера в обозревателе объектов.
Раскройте узел Базы данных и в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.
В Базе данных SQL Azure нельзя изменить уровень совместимости системных баз данных.
Щелкните базу данных правой кнопкой мыши и выберите Свойства.
Откроется диалоговое окно Свойства базы данных .
На панели Выбор страницы выберите пункт Параметры.
Текущий уровень совместимости будет указан в списке Уровень совместимости .
Чтобы изменить уровень совместимости, выберите в списке другой параметр. Доступные параметры для разных версий Компонент Database Engine указаны на странице Уровень совместимости инструкции ALTER DATABASE (Transact-SQL).
Использование Transact-SQL
Можно использовать Transact-SQL для просмотра или изменения уровня совместимости базы данных с помощью SSMS или Azure Data Studio.
Просмотр уровня совместимости базы данных
Подключитесь к соответствующему серверу или экземпляру, на котором размещена ваша база данных.
Откройте Новый запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере возвращается уровень совместимости образца базы данных AdventureWorks2019.
Изменение уровня совместимости базы данных
Подключитесь к соответствующему серверу или экземпляру, на котором размещена ваша база данных.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере изменяется уровень совместимости базы данных AdventureWorks2019 на 150 , т. е. на значение уровня совместимости для SQL Server 2019 (15.x).
Значение уровня совместимости базы данных в SQL Server
До SQL Server 2014 уровень совместимости базы данных ваших пользовательских баз, как правило, не являлся требующим внимания аспектом, по крайней мере, с точки зрения производительности. В отличии от уровня файлов базы данных (который автоматически апгрейдится, когда вы восстанавливаете или присоединяете базу данных более низкого уровня к экземпляру, запущенному на более новой версии SQL Server, и который никогда не опускается к более низкому уровню), уровень совместимости базы данных может быть изменен до любого поддерживаемого уровня с помощью простой команды
Вы не привязаны к какому-то конкретному поддерживаемому уровню совместимости и можете изменить его по вашему желанию на любой другой из поддерживаемых. Во многих случаях большинство пользовательских баз данных никогда не изменяют свой уровень совместимости после перехода к новой версии SQL Server. Это обычно не вызывает никаких проблем до тех пор, пока вам не понадобятся новые возможности, доступные на последнем доступном уровне совместимости базы данных.
Для SQL Server 2012 и ниже уровень совместимости базы данных, главным образом, использовался для контроля над тем, доступны или нет новые возможности, введенные в конкретной версии SQL Server, и доступны или нет не поддерживаемые уже старые возможности. Уровень совместимости базы данных также использовался как метод поддержания обратной совместимости приложений со старыми версиями SQL Server. Если у вас не было времени, чтобы выполнить полное тестирование регрессии на новейшем уровне совместимости, вы могли просто использовать прежний уровень совместимости до тех пор, пока выполните тестирование и, при необходимости, модификацию ваших приложений.
В таблице 1 показаны основные версии SQL Server и их уровни совместимости, поддерживаемые и принимаемые по умолчанию.
Версия SQL Server | Версия движка БД | Уровень совместимости по умолчанию | Поддерживаемые уровни |
---|---|---|---|
SQL Server 2019 | 15 | 150 | 150, 140, 130, 120, 110, 100 |
SQL Server 2017 | 14 | 140 | 140, 130, 120, 110, 100 |
SQL Server 2016 | 13 | 130 | 130, 120, 110, 100 |
SQL Server 2014 | 12 | 120 | 120, 110, 100 |
SQL Server 2012 | 11 | 110 | 110, 100, 90 |
SQL Server 2008 R2 | 10.5 | 100 | 100, 90, 80 |
SQL Server 2008 | 10 | 100 | 100, 90, 80 |
SQL Server 2005 | 9 | 90 | 90, 80 |
SQL Server 2000 | 8 | 80 | 80 |
Таблица 1: Версии SQL Server и поддерживаемые ими уровни совместимости
Создание новой базы данных
Когда вы создаете новую пользовательскую базу данных в SQL Server, уровень совместимости базы данных будет установлен в уровень совместимости по умолчанию для этой версии SQL Server. Так, например, новая пользовательская база данных, которая создается в SQL Server 2017 получит уровень совместимости базы данных 140. Исключение возникает, если вы измените уровень совместимости системной базы данных model на другой поддерживаемый уровень, тогда новая пользовательская база данных будет наследовать этот уровень совместимости от базы данных model.
Восстановление или присоединение базы данных
Если вы восстанавливаете полный бэкап базы данных, который был сделан на более старой версии SQL Server, на экземпляре, который запущен на более новой версии SQL Server, то уровень совместимости базы данных останется прежним, каким он был на старой версии SQL Server, если уровень совместимости старой базы данных не ниже, чем минимальный поддерживаемый уровень совместимости новой версии SQL Server.
Например, если вы восстанавливаете бэкап базы данных с SQL Server 2005 на экземпляре SQL Server 2017, уровень совместимости для восстановленной базы данных будет установлен в 100. Вы получите то же самое поведение, если отсоедините базу данных с более старой версии SQL Server, а затем присоедините её к более новой версии SQL Server.
Такое поведение не ново, но кое-что новое и важное может еще произойти, когда вы переводите пользовательскую базу данных на уровень совместимости 120 или новее. Эти дополнительные изменения, которые могут оказать существенное влияние на производительность, видимо, недостаточно известны и поняты широкому сообществу пользователей SQL Server. Я по-прежнему встречаю многих профессионалов в области баз данных и организации, выполняющих то, что я называю "апгрейд вслепую". Это когда они переходят с SQL Server 2012 или более ранних версий на SQL Server 2014 или новее (особенно SQL Server 2016 и SQL Server 2017), не выполняя сколь-нибудь серьезного тестирования падения производительности, чтобы выяснить, как их рабочая нагрузка будет вести себя на новом естественном уровне совместимости, и доступны ли дополнительные конфигурационные параметры, способные оказать положительный эффект.
Уровень совместимости 120
Здесь был введен новый оценщик кардинального числа (CE), т.е. изменилась оценка числа строк. Во многих случаях большинство ваших запросов стало выполняться быстрей при использовании нового оценщика, но зачастую имелось небольшое число запросов, которые демонстрировали существенное падение производительности с новым оценщиком кардинального числа. Использование уровня совместимости базы данных 120 означает, что вы будете использовать "новый" CE до тех пор, пока не установите флажок трассировки уровня экземпляра или не будете использовать хинт в запросе, чтобы отменить его действие.
Joe Sack еще в апреле 2014 года написал классическую работу “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” (Оптимизация ваших планов запросов с оценщиком кардинального числа SQL Server 2014), которая объясняет основание и поведение сделанных изменений. Если вы наблюдаете падение производительности некоторых запросов с новым СЕ, SQL Server имел немного вариантов, чтобы устранить проблемы с производительностью, вызванные новым СЕ. Joe подробно описал эти возможности, которые, по существу, сводились к флажкам трассировки или хинтам в запросах для контроля над тем, какой оценщик кардинального числа применяется оптимизатором запросов. Или же вы решали вернуться обратно к уровню совместимости базы данных 110 и ниже.
Я использую "новый" СЕ в кавычках потому, что теперь нет единственного "нового" СЕ. Каждая следующая версия SQL Server, начиная с SQL Server 2014, имеет свой СЕ, и изменения оптимизатора запросов привязаны к уровню совместимости базы данных. Новая более точная терминология, соответствующая SQL Server 2016 и далее, использует СЕ120 для уровня совместимости 120, СЕ130 - для уровня совместимости 130, СЕ140 - для уровня совместимости 140, и СЕ150 - для уровня совместимости 150.
Уровень совместимости базы данных 130
Если вы имеете SQL Server 2016 или новее, использование уровня совместимости базы данных 130 будет применять СЕ130 по умолчанию, и будут доступны и все другие изменения, касающиеся производительности. Эффекты глобальных флажков трассировки 1117, 1118 и 2371 будут действовать при уровне совместимости базы данных 130. Вы также получите эффект глобального флажка трассировки 4199 для всех заплаток (hotfix), которые были поставлены до SQL Server 2016 RTM.
SQL Server 2016 также ввел конфигурационные опции уровня базы данных, которые дают вам возможность контролировать некоторое поведение и которые ранее конфигурировались на уровне экземпляра сервера, с помощью команды ALTER DATABASE SCOPED CONFIGURATION. Двумя наиболее уместными для темы настоящей статьи конфигурационными опциями уровня базы данных являются ESTIMATION и QUERY_OPTIMIZER_HOTFIXES.
LEGACY_CARDINALITY ESTIMATION включает унаследованный СЕ (СЕ70) вне зависимости от установки уровня совместимости базы данных. Это эквивалентно флажку трассировки 9481, но влияет только на соответствующую базу данных, а не на весь экземпляр. Это позволяет вам установить уровень совместимости базы данных в 130 для того, чтобы получить другие функциональные возможности и выгоды с точки зрения производительности, и при этом использовать унаследованный СЕ уровня базы данных (если он не переписывается хинтом в запросе).
Опция QUERY_OPTIMIZER_HOTFIXES эквивалентна флажку трассировки 4199 на уровне базы данных. SQL Server 2016 делает доступными все заплатки оптимизатора запросов, поставленные до SQL Server 2016 RTM, когда вы используете уровень совместимости базы данных 130 (без установки флажка трассировки 4199). Если вы устанавливаете флажок 4199 или включаете QUERY_OPTIMIZER_HOTFIXES, вы также получите все заплатки оптимизатора запросов, которые были выпущены после SQL Server 2016 RTM.
SQL Server 2016 SP1 также ввел хинты запроса USE HINT, которые легче использовать и понять, чем устаревшие хинты запроса QUERYTRACEON, которые могли использоваться в SQL Server 2014 и ранее. Это дает вам даже более тонкое управление поведением оптимизатора, чем связанное с используемыми уровнем совместимости и версией оценщика кардинального числа. Вы можете выполнить запрос к sys.dm_exec_valid_use_hints, чтобы получить список доступных названий в USE HINT.
Уровень совместимости базы данных 140
Если у вас установлен SQL Server 2017 или новее, использование уровня совместимости базы данных 140 будет применять СЕ140 по умолчанию. Вы также получаете все другие связанные с производительностью изменения от 130 плюс новые. SQL Server 2017 ввел новые возможности адаптивной обработки запросов, и они доступны по умолчанию, когда уровень совместимости базы данных установлен в 140. Они включают обратную связь по выделению памяти в пакетном режиме (batch mode memory grant feedback), адаптивные соединения в пакетном режиме (batch mode adaptive joins) и чередующееся выполнение (interleaved execution).
Уровень совместимости базы данных 150
Если вы имеете SQL Server 2019 или новее, использование уровня совместимости базы данных 150 будет применять СЕ150 по умолчанию. Вы также получаете все остальные изменения, связанные с производительностью, от 130 и 140 плюс новые, введенные в этой версии. SQL Server 2019 добавляет даже больше улучшений производительности и изменений поведения, чем доступно по умолчанию, когда база данных использует уровень совместимости 150. Главным примером является встраивание скалярных функций пользователя (scalar UDF inlining), который автоматически встраивает много скалярных UDF в ваших пользовательских базах данных. Это может быть одним из наиболее важных улучшений производительности при некоторых рабочих нагрузках.
Другим примером является интеллектуальная обработка запросов (intelligent query processing), которая является подмножеством адаптивной обработки запросов в SQL Server 2017. Новые возможности включают отложенную компиляцию табличных переменных (table variable deferred compilation), неточную обработку запросов (approximate query processing) и пакетный режим для построчного хранения (batch mode on rowstore).
Имеется также 16 новых конфигурационных параметров уровня базы данных (в CTP 2.2), которые дают вам больше возможностей управления базами данных, чем это предоставлялось флажками трассировки или уровнем совместимости базы данных. Это позволяет осуществлять более тонкий контроль высокоуровневых изменений, чем поведение по умолчанию при уровне совместимости базы данных 150.
Заключение
Переход на современную версию SQL Server (т.е. SQL Server 2016 или старше) существенно более сложный процесс, чем это было со старыми версиями SQL Server. Поскольку изменения связаны с различными уровнями совместимости базы данных и различными версиями оценщика кардинального числа, действительно очень важно обдумать, спланировать и протестировать тот уровень совместимости, который вы хотите использовать на новой версии SQL Server, на которую вы переносите ваши базы данных.
Майкрософт рекомендует процесс апгрейда на новейшую версию SQL Server с сохранением уровня совместимости переносимых баз данных. Затем включить Query Store на каждой базе данных и собрать показательные данные при рабочей нагрузке. Потом установить уровень совместимости последней версии и использовать Query Store, чтобы зафиксировать падение производительности, принудительно используя последний заведомо хороший план.
Если вы хотите избежать бессистемной "слепой" миграции, когда вы находитесь в блаженном неведении того, как ваша рабочая нагрузка будет реагировать на эти изменения. Изменение уровня совместимости базы данных на подходящую версию и использование подходящих конфигурационных параметров уровня базы данных, наряду с абсолютно необходимыми соответствующими хинтами запросов, исключительно важно для современных версий SQL Server.
Другим вопросом, заслуживающим внимания, является то, что Майкрософт начинает продвигать идею, что вы должны думать о тестировании ваших баз данных и приложений на конкретном уровне совместимости базы данных, а не на конкретной версии SQL Server. Майкрософт обеспечивает защиту формы плана запроса, когда новая версия SQL Server запускается на железе, которое совместимо с железом, на котором была запущена предыдущая версия (исходная) SQL Server, и один и тот же поддерживаемый уровень совместимости базы данных используется на обеих версиях.
Основная идея заключается в том, что если вы протестировали ваши приложения на конкретном уровне совместимости, например, 130, вы получите то же самое поведение и производительность, если переместите базу данных на новую версию SQL Server (например, SQL Server 2017 или SQL Server 2019), пока вы используете тот же уровень совместимости базы данных и эквивалентное железо.
This article describes how to view or change the compatibility level of a database in SQL Server, Azure SQL Database, or Azure SQL Managed Instance by using SQL Server Management Studio or Transact-SQL.
Before you change the compatibility level of a database, you should understand the impact of the change on your applications. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).
Permissions
Requires ALTER permission on the database.
Use SQL Server Management Studio
To view or change the compatibility level of a database using SQL Server Management Studio (SSMS)
Connect to the appropriate server or instance hosting your database.
Select the server name in Object Explorer.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
You cannot modify the compatibility level of system databases in Azure SQL Database.
Right-click the database, and then select Properties.
The Database Properties dialog box opens.
In the Select a page pane, select Options.
The current compatibility level is displayed in the Compatibility level list box.
To change the compatibility level, select a different option from the list. The available options for different Database Engine versions are listed in the ALTER DATABASE Compatibility Level (Transact-SQL) page.
Use Transact-SQL
You can use Transact-SQL to view or change the compatibility level of a database using SSMS or Azure Data Studio.
View the compatibility level of a database
Connect to the appropriate server or instance hosting your database.
Open a New Query.
Copy and paste the following example into the query window and select Execute. This example returns the compatibility level of the AdventureWorks2019 sample database.
Change the compatibility level of a database
Connect to the appropriate server or instance hosting your database.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example changes the compatibility level of the AdventureWorks2019 database to 150 , which is the compatibility level for SQL Server 2019 (15.x).
Есть некоторые древние приложения, которые в процессе установки делают обращение к экземпляру SQL Server, создают там базу данных и выполняют её первоначальное наполнение. В случае, если такие приложения хотят, чтобы их база данных имела определённый уровень совместимости (compatibility level), но сами при этом не выполняют настройку этого уровня в процессе создания и инициализации БД, может возникнуть необходимость изменения уровня совместимости, используемого по умолчанию в экземпляре SQL Server. Рассмотрим пример того, как это можно "провернуть".
Предположим, у нас есть экземпляр SQL Server 2012, на котором должна быть развернута БД некоторого старого приложения, которое хочет чтобы эта БД имела уровень совместимости с SQL Server 2005. Однако при создании для новых баз в нашем случае будет назначаться уровень совместимости SQL Server 2012 (110). Попробуем сделать так, чтобы по умолчанию уровень совместимости выставлялся в SQL Server 2005 (90).
Для начала на нашем экземпляре SQL Server выполним запрос, который покажет установленный уровень совместимости для всех баз:
Как видим, уровень совместимости по умолчанию для новых баз в нашем экземпляре установлен в 110, так как он определяется уровнем базы model. Информацию о возможных уровнях совместимости на разных версиях SQL Server можно найти в онлайн-документе:
MSDN Library - Transact-SQL Reference (Database Engine) - ALTER DATABASE Compatibility Level
Соответственно, чтобы изменить уровень совместимости по умолчанию для вновь создаваемых баз до уровня SQL Server 2005, выполним запрос:
Обратите внимание на то, что такой запрос изменит на указанный уровень совместимости не только базу model, но и базу tempdb
После этого снова запустим наше приложение создающее новую базу и убедимся в том, что теперь уровень базы установлен в SQL Server 2005 (90).
Чтобы снова вернуть изначально установленный уровень совместимости выполним запрос:
Чем такие манипуляции могут обернуться на продуктивных экземплярах SQL Server с некоторым количеством разных БД предсказывать не возьмусь
Читайте также: