Поддержка Microsoft SQL Server 2012, работа в режиме AlwaysOn

15 1

В системе DIRECTUM 5.0 реализована долгожданная поддержка Microsoft SQL Server 2012, а также возможность работы в режиме AlwaysOn! C помощью новых возможностей можно распределять запросы между несколькими серверами, таким образом снижая нагрузку на главный сервер.

  Важно!

С 2014 года Microsoft Corporation прекращает поддержку Microsoft SQL Server 2008, распространение которого было завершено еще в 2012 году. В связи с этим, 1 марта 2014 года в составе своих решений (в том числе и DIRECTUM) можно распространять только Microsoft SQL 2012 Runtime.

Компаниям, приобретающим предыдущую версию DIRECTUM 4.9 после 1 марта 2014 года, при необходимости лицензирования ПО Microsoft, рекомендуется покупать Microsoft SQL 2012 в OLP поставке с возможностью «downgrade» до SQL 2008.

Источник: directum.ru

SQL 2012

Итак, во время разработки версии DIRECTUM 5.0 перед нами стояла задача реализовать поддержку SQL 2012 в IS-Builder 7.

Для этого, сперва было проведено тестирование работы на SQL 2012, которое показало, что необходим отказ от старого стандарта  ANSI SQL-89, в первую очередь от условий «*=» и «=*». В связи с этим, внешние соединения таблиц в SQL-запросах были переведены с использования условий «*=» на синтаксис JOIN, что позволило перейти к поддержке новых уровней совместимости базы данных: 90 (SQL 2005), 100 (SQL2008), 110 (SQL2012).

Список несовместимостей SQL Server 2012 по отношению к другим версиям SQL Server можно посмотреть здесь Discontinued Database Engine Functionality in SQL Server 2012.

Теперь при конвертации ISBL-текстов проверяется наличие условий типа «*=» и некоторые другие несовместимости, которые необходимо устранить для работы на SQL Server 2012. К примеру:

Так же при конвертации системы теперь можно самим устанавливать уровень совместимости базы данных, если этому не противоречит прикладная разработка.

Объектная модель IS-Builder

В связи с поддержкой SQL 2012, в объектную модель к уже существующим методам AddSelect, AddWhere, AddFrom, AddOrderBy и Del* добавлены новые: AddJoin и DelJoin.

 

function AddJoin(

 const TableName: WideString;

 JoinType: TJoinType;

 const Condition: WideString;

 Position: TAddPosition): Integer;

function DelJoin(

 ID: Integer);

 

Метод AddJoin добавляет к итоговому запросу условие. При вызове двух и более AddJoin с одинаковыми параметрами TableName и JointType, они сливаются в один JOIN с двумя и более условиями при помощи операции and. Если параметры TableName одинаковые, а JointType разные, то генерируется исключение.

Если необходимо несколько раз сделать соединение с одной и той же таблицей, например, MBAnalit с фильтром по разным типам справочника, то в качестве параметра TableName нужно указывать имя таблицы и алиас, например, dbo.MBAnalit Analit.

Метод DellJoin удаляет условие из запроса. DelJoin работает аналогично другим Del* методам: из списка условий удаляется условие с указанным ИД. Если в списке было несколько JOIN к одной и той же таблице, то из результирующего запроса удаляется только условие, относящееся к переданному ИД.

Примеры использования методов можно посмотреть в справке здесь и здесь.

В объектную модель были также добавлены перечислимые типы: TJoinType – тип соединения и TAddPosition – позиция соединения.

Пример изменения текста SQL-запроса при добавлении соединений можно посмотреть в TAddPosition.

Рекомендации по доработке SQL-запросов

Существенным отличием операторов «*=» и «=*» от Left Join и Right Join является задание ограничений на связываемые таблицы. Важно понимать, что при использовании оператора Left Join, условия, записанные в секции on, проверяются только для правой таблицы и не влияют на включение строк левой таблицы в итоговую выборку. А вот условия в секции where применяются ко всей итоговой выборке. Поэтому, если условия для правой таблицы наложены в секции where и не выполняются, то они исключат и строки левой таблицы из итоговой выборки. При использовании Left Join все условия на правую таблицу должны быть записаны в секции on. Аналогично будет и для Right Join.

Рассмотрим это правило на примере. Допустим, нужно получить все действующие записи справочника с Vid = 3118, и утвержденные записи табличной части, относящиеся к записям справочника, если они есть.

Вариант 1. Условие находится в правой таблице секции where всего запроса:

select
  *
from
  MBAnalit mbanalit
  left join MBAnValR mbanvalr on
    mbanalit.Analit = mbanvalr.Analit    
where
  mbanalit.Vid = 3118
  and mbanalit.Sost = 'Д'
  and mbanvalr.XRecStat = '+' 

Результат: если выполнить этот запрос, то он вернет 14 записей. При этом в итоговой выборке будут только записи справочника, у которых есть записи в табличной части. Запрос возвращает не все необходимые данные.

Вариант 2. Условие находится в секции on:

select
  *
from
  MBAnalit mbanalit
  left join MBAnValR mbanvalr on
    mbanalit.Analit = mbanvalr.Analit    
    and mbanvalr.XRecStat = '+' 
where
  mbanalit.Vid = 3118
  and mbanalit.Sost = 'Д'

Результат: если выполнить этот запрос, то он вернет 22 записи, в том числе записи справочника, у которых нет записей в табличной части. Запрос возвращает все данные.

Исключение из описанных выше правил составляет запрос получения строк левой таблицы, к которым не относится ни одна строка правой таблицы вида:

select
  *
from
  MBAnalit mbanalit
  left join MBAnValR mbanvalr
    on mbanalit.Analit = mbanvalr.Analit
where
  mbanvalr.Analit is null

В этом случае условие на правую таблицу должно быть в секции where.

Эти правила действуют при отборе данных для всех типов запросов: Select, Update, Insert, Select, Delete.

Статические SQL-запросы

Большую часть запросов можно изменить простым переносом условия связывания из секции where в секцию on.

Как было:

select 
  Groups.NameAn as GroupName,
  RefUsers.NameAn as UserName, 
  Users.UserLogin
from
  MBAnalit Groups,
  MBUser Users,
  MBAnValR ValR,
  MBVidAn VidAn,
  MBAnalit RefUsers
where 
  Groups.Vid = VidAn.Vid
  and VidAn.Kod = 'ГПЛ'
  and Groups.Analit = ValR.Analit
  and Groups.NameAn like 'М%'
  and ValR.PolzovatelT = RefUsers.Analit
  and RefUsers.IDSpr *= Users.UserID
  order by  GroupName, UserName

Как стало:

select 
  Groups.NameAn as GroupName,
  RefUsers.NameAn as UserName, 
  Users.UserLogin
from
  MBAnalit Groups,
  MBAnValR ValR,
  MBVidAn VidAn,
  MBAnalit RefUsers
  left join MBUser Users on
    RefUsers.IDSpr = Users.UserID
where 
  Groups.Vid = VidAn.Vid
  and VidAn.Kod = 'ГПЛ'
  and Groups.Analit = ValR.Analit
  and Groups.NameAn like 'М%'
  and ValR.PolzovatelT = RefUsers.Analit
 order by GroupName, UserName

При этом сложность исправления запроса не всегда зависит от его размера.

Параметры типа AddWhere

Если значение, передаваемое в качестве параметра AddWhere какой-либо функции (например, ChangeReferenceDataset, СпрИзмНабДан), содержит «*=», то нужно:

  1. Найти правую таблицу в параметре AddFrom и перенести её в параметр AddJoin с типом соединения jtLeft.
  2. У параметра AddJoin в секции on написать условие из параметра AddWhere.
  3. Удалить из параметра AddWhere условие с «*=».

Для «=*» будут аналогичные действия, только замена выполняется на параметр AddJoin с типом соединения jtRight.

Как было:

!AddFrom = "tmpTable t"
!AddWhere = "MBAnalit.Analit *= t.Analit and t.YesNoT = 'Д'" 
Справочник(!Код;;"t.AccT as Счет";!AddFrom;!AddWhere)

Как стало:

Справочник(!Код;;"t.AccT as Счет";;;;;;;;;;; ;;True; "tmpTable t|jtLeft|MBAnalit.Analit = t.Analit and t.YesNoT = 'Д'))

Важным моментом является то, что использование параметров AddFrom и AddWhere для присоединяемых таблиц не рекомендуется, так как нельзя определить позицию присоединяемой таблицы в запросе.

При присоединении таблицы через параметр AddJoin нужно обращать внимание на последовательность добавления, так как при добавлении из условия выше нельзя обратиться к таблицам объявленным ниже.

Параметр AddJoin всегда присоединяет таблицу к первой таблице в блоке From.

К примеру, исходный запрос выглядит так:

select 
  Analit.Kod, 
  Analit.NameAn,
  ValR.ISBRole
from
  MBAnalit Analit,
  MBAnValR ValR
where
  Analit.Analit = ValR.Analit
  and Analit.Vid = 3140
  and ValR.ISBRole is not null

Если нам нужно добавить новую присоединяемую таблицу, не к первой таблице, а ко второй, то нужно объявление MBAnValR переделать на выражение с JOIN и только потом вносить наши изменения.

AlwaysOn

Со временем нагрузка на SQL-сервер возрастает. Это может быть связано с увеличением объема данных, либо с увеличением числа пользователей, которые работают с сервером. Инвестиции в новый сервер с более высокими аппаратными характеристиками не всегда оправданы. AlwaysOn позволяет задействовать в работе «спящие» ресурсы (старые серверы) и оптимально распределить нагрузку между этими серверами.

Режим Microsoft SQL Server 2012 AlwaysOn представляет собой объединение нескольких серверов для работы одной базы данных. На одном из серверов база находится в режиме для чтения и записи, на других серверах находятся ее копии в режиме только для чтения.

В системе DIRECTUM поддерживается работа AlwaysOn в режиме синхронной фиксации, которая гарантирует, что все транзакции, зафиксированные в базе данных-источнике, также будут зафиксированы в соответствующей базе данных-получателе. В синхронном режиме можно использовать максимум 3 сервера: основной для чтения и записи и два сервера только для чтения.

Все серверы должны входить в отказоустойчивый кластер WSFC, т.к. AlwaysOn использует для работы механизмы кластера (своих механизмов у AlwaysOn нет). В свою очередь, на всех серверах должны быть расположены экземпляры SQL Server Server 2012 Enterprise Edition. Подробнее системные требования можно посмотреть в статье «Предварительные требования, ограничения и рекомендации».

Для распределения нагрузки в IS-Builder 7 между «основным» сервером и серверами «только для чтения» в системе DIRECTUM появились новые установки:

  • PercentageOfPossibleConnectionsToReadOnlyServers – процент возможных подключений к серверам для чтения с поддержкой AlwaysOn. Предназначена для распределения нагрузки между основным сервером и серверами для чтения. После конвертации системы автоматически устанавливается значение 0 все запросы на чтение отправляются только на основной сервер. Рекомендуется установить значение 100.

Примечание. Важно, что при значении 100 % не все запросы на чтение будут отправляться на вторичный сервер. Часть запросов (~40%) все же будет уходить на главный сервер. Это связано с тем, что не все запросы на чтение можно выполнять на вторичных серверах. Например, запросы на изменение данных будут все же отправляться на главный сервер.

  • GetDataFromReadOnlyServerTimeLag – интервал задержки в получении данных с сервера для чтения. Указывается в миллисекундах. После конвертации системы автоматически устанавливается значение 2000 – после обновления объекта системы данные будут считываться с сервера для чтения спустя две секунды. До указанного значения данные будут считываться с главного сервера.

Порядок настройки для работы в режиме AlwaysOn подробно описан в соответствующей инструкции. Посмотреть ее можно на сайте поддержки support.directum.ru.

 

Михаил Тарасов

По поводу перевода запросов на новый синтаксис:

При написании подзапросов, они должны быть в том же стиле, что и основной запрос. если join в запросе, то join и в подзапросе. Если *= в запросе, то *= и в подзапросе.

В старых версиях запрос к MBAnalit строится по синтаксису *=. В новых будет через join. 

В прикладной разработке, где использовалось, например addWhere в котором был подзапрос, потребуется так же переделать его на новый синтаксис.

Авторизуйтесь, чтобы написать комментарий