Регистрация и работа со связанным сервером MSSQL 2012

Введение

В этой статье мы пошагово рассмотрим все этапы регистрации связанного сервера MSSQL 2012 к базе данных Firebird/InterBase и работу с ним.

Тестовое окружение

• MSSQL 2012 Express R2 x64 (English). Windows Server 2008 R2 x64 (Russian) c English UI.
• Firebird 2.5.3. 26709 x64 SuperClassic.
  • Тестовая база данных: employee.fdb (3 диалект) из поставки FB2.5

Подготовка к настройке связанного сервера

1. Установите IBProvider на сервер с MSSQL

Используйте инсталляторы правильной разрядности:

• Для 64-битного MSSQL нужно установить 64-битный IBProvider
• Для 32-битного MSSQL нужно установить 32-битный IBProvider

Рекомендации:

• В случае 64-битной операционной системы, настоятельно рекомендуется установить оба провайдера – 32 и 64 бита.
• Для работы с MSSQL не рекомендуется использовать «IBProvider Lite».
• Установите так же «Samples» и «Binaries of examples» — они понадобятся при проверке вашей FB/IB базы данных. Потом их можно будет деинсталлировать.

2. Установка клиента InterBase/Firebird на сервер с MSSQL

Модули с серверными клиентами:

Вы можете найти эти DLL в дистрибутивах вашего InterBase/Firebird сервера.

Обратите внимание, что серверные клиенты используют дополнительные DLL «Visual C++». Вы можете скачать установочные файлы «Microsoft Visual C++ Redistributable Package» с сайта IBProvider-a.

Варианты установки клиента

1. Скопировать DLL в системный каталог Windows.

2. Скопировать DLL в отдельный каталог.

Пример для 64-битного клиента InterBase:

• На любом не системном диске создайте каталог (например) d:\InterBaseClient\64bit
• Скопируйте в этот каталог ibclient64.dll
• Проверьте права доступа (на уровне файловой системы) к ibclient64.dll

3. Проверьте корректность установки провайдера и серверного клиента

Если у вас нет опыта установки и настройки IBProvider-а и серверного клиента для работы с InterBase/Firebird, то имеет смысл проверить этап (1) и (2) без участия MSSQL.

Пример проверки для «Windows 2008 R2 64bit».

На компьютере с MSSQL создайте файл «test_connection.vbs» со следующим VBS-скриптом:

option explicit

dim cn

set cn=createobject("ADODB.Connection")

cn.Provider="LCPI.IBProvider.3"

'fbclient.dll from system directories of Windows (System32/SysWOW64)
cn.ConnectionString _
 ="location=HOME2:e:\database\employee.fdb;" & _
  "user id=sysdba;" & _
  "password=masterkey;" & _
  "dbclient_library=fbclient.dll;" & _
  "dbclient_library_64=fbclient.dll"

on error resume next

call cn.Open()

if(err.number<>0)then
 errorFlag=true

 wscript.echo "ERROR: ["&err.Source&"] "&err.Description

 dim e,n

 n=0

 for each e in cn.Errors
  n=n+1
  wscript.echo "["&n&"] "&e.Source&" - "&e.Description
 next 'e

 call wscript.quit(1) 
end if

on error goto 0

wscript.echo "Provider: "&cn.Properties("Provider Name").Value&" "&cn.Properties("Provider Version").Value

wscript.echo "OK!"

call wscript.quit(0) 

NOTE: в строке подключения должны быть ваши значения свойств «location», «user id», «password» и «dbclient_library». Описание всех свойств инициализации можно посмотреть здесь.

Запустите ваш файл «test_connection.vbs» из консоли (cmd.exe).

Тестирование 64-битного доступа к базе данных:

Тестирование 32-битного доступа к базе данных:

Добейтесь того, чтобы этот скрипт начал отрабатывать без ошибок.

4. Проверка подключаемой базы данных

Перед тем как подключать вашу FB/IB базу данных к MSSQL, нужно проверить отсутствие ошибок в её системных таблицах.

В противном случае – IBProvider или MSSQL могут отказаться работать с вашей базой данных.

В поставке дистрибутива IBProvider-а есть VBS-скрипт «prog_check_system_tables.wsf», который находит часть известных ошибок.

Скрипт запускается из консоли (cmd.exe) через «cscript.exe».

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

В тестовой базе employee.fdb, которую мы будем подключать к MSSQL, этот скрипт нашел одну ошибку:

Основным результатом работы скрипта является пара файлов – «check_system_table.log» и «check_system_table.sql».

Находим в «check_system_table.log» описание ошибки:

FULL_NAME – это вычисляемая текстовая колонка. По непонятной причине, в описании этой колонки не определено количество символов.

В сгенерированном файле «check_system_table.sql» предлагается скрипт для исправления ошибки:

Внимательно изучаем сгенерированные запросы для исправления базы данных и накатываем этот скрипт на нашу базу данных (например, через IBExpert).

Снова запускаем «prog_check_system_tables.wsf». На этот раз скрипт не нашел ошибок. Но это не значит, что их там нет. В процессе работы связанного сервера, возможно будут выявлены другие проблемы.

5. Сконфигурируйте координатор распределенных транзакций (MSDTC)

Для выполнения запросов через связанный сервер нужна постоянно запущенная служба MSDTC.

1. Откройте консоль для управления службами на сервере с MSSQL
2. Найдите службу координатора
3. Установите ей автоматический запуск и запустите её.

Настройка связанного сервера

1. Запустите «SQL Server Management Studio»

2. Подключитесь к MSSQL, на котором будет настраиваться связанный сервер

3. Настройте использование OLE DB провайдера

Найдите IBProvider в списке доступных провайдеров. Если его там нет, то попробуйте обновить список (Refresh). Если не помогло — значит на первом этапе вы не допустили ошибки.

Откройте диалог настройки свойств:

Поставьте галочки напротив следующих настроек:

• Dynamic Parameters
• Nested queries
• Allow In Process (обязательно!)
• Supports ‘Like’ operations

Закройте диалог (OK).

4. Создание связанного сервера

Перейдите на элемент «Linked Servers» и выберите в меню пункт «New Linked Server…»:

Откроется диалог для настройки связанного сервера.

Заполните поля на странице основных настроек.

«Linked Server»

Имя связанного сервера, которое будет использоваться в запросах. В нашем случае это будет FB_EMPL.

Provider

Выберите «LCPI OLE DB Provider for InterBase [v3]».

«Product Name», «Data Source»

Укажите в этих полях любой текст. В нашем случае, здесь будет продублировано имя связанного сервера – «FB_EMPL».

«Provider String»

Здесь определяются свойства для настройки работы провайдера. Рекомендуемая настройка:

В нашем случае, строка подключения выглядит так:

Если вы хотите подключаться к Firebird без использования fbclient.dll, используйте следующую строку подключения:

Вы можете проверить вашу строку подключения в тестовом скрипте из пункта «Проверка корректности установки провайдера и серверного клиента».

«Location»

Путь к подключаемой базе данных. В нашем случае база данных находится на сервере HOME2 в файле «e:\database\employee.fdb»:

При подключении к Firebird без использования fbclient.dll (в строке подключения указано «dbclient_type=fb.direct») имеет смысл точно указать тип протокола подключения и версию TCP/IP:

Это ускорит процесс подключения в сетях с одновременной поддержкой TCP/IP v4 и v6.

Переключитесь на страницу «Security» и укажите логии и пароль подключения к базе данных:

Переключитесь на страницу «Server Options»:

Рекомендуется установить свойства:

• «Collation Compatible»=true
• «RPC»=true
• «RPC Out»=true

На этом настройка связанного сервера завершена – нажимаем «OK».

Если все было настроено правильно, то связанный сервер будет создан и появится в дереве объектов:

Общие правила работы с Linked Server

1. В SQL запросах, выполняемых через связанный сервер, следует указывать имена таблиц и колонок в верхнем регистре. Соответственно, в вашей базе данных Firebird/InterBase названия всех объектов, с которыми вы будете работать через связанный сервер, должны быть «upper-case».

2. Для упрощения кода, будет использоваться уровень изоляции транзакций «по-умолчанию»: read_committed.

Работа со связанным сервером из «Microsoft SQL Server Management Studio»

Нажмите «New Query» для перехода в окно редактирования и запуска SQL скриптов.

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

begin distributed transaction;

select * from FB_EMPL...EMPLOYEE;

commit;

Результат работы:

Если из строки подключения убрать «schema_ldr_cfg__check_constraints=0», то запрос к таблице EMPLOYEE будет генерировать следующее сообщение:

Это связанно с тем, что MSSQL, на стадии подготовки запроса, запрашивает у IBProvider-а различную информацию о таблице EMPLOYEE. В том числе, он получает информацию о CHECK-ограничении, которое и провоцирует данное сообщение:

salary >= (SELECT min_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country) AND
            salary <= (SELECT max_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country)

Указав в строке подключения «schema_ldr_cfg__check_constraints=0», мы полностью исключаем из схем метаданных сведения о CHECK-ограничениях. Как следствие – устраняем причины для такого рода сообщений.

Выборка данных с использованием автоматической транзакции

Поскольку в нашей строке подключения было указано «auto_commit=true», то мы можем опустить явный запуск и коммит транзакции.

select * from FB_EMPL...EMPLOYEE;

Результат работы запроса будет аналогичным – 42 строки.

Уровень изоляции «автоматической транзакции» определяется в свойстве инициализации «auto_commit_level».

Выборка данных с использованием параметров

begin distributed transaction;

declare @first_name as varchar(32);

set @first_name='Scott';

select * from FB_EMPL...EMPLOYEE empl where empl.FIRST_NAME=@first_name;

commit;

Результат работы:

Модификация данных

При выполнении SQL-запросов INSERT, UPDATE и DELETE, MSSQL пытается стартовать вложенную транзакцию. IBProvider реализует поддержку вложенных транзакций через точки сохранения Firebird/InterBase. По-умолчанию эта поддержка отключена. Для её включения нужно указать в строке подключения «nested_trans=true».

Если вы работает со старой версией FB/IB, в которой отсутствует поддержка точек сохранения, то надо указать в начале скрипта:

SET XACT_ABORT ON;

Или вообще отказаться от явного управления транзакциями. В последнем случае, в строке подключения должно быть указано «auto_commit=true».

Вставка нового ряда

begin distributed transaction;

insert into FB_EMPL...COUNTRY (COUNTRY,CURRENCY) values ('Mars','Snickers');

commit transaction;

Результат работы:

Начиная с версии 3.50, IBProvider поддерживает DEFAULT-значения. Это позволяет правильно выполнять запросы вида «INSERT INTO … DEFAULT VALUES».

Создадим в тестовой базе данных таблицу:

CREATE TABLE TABLE6_5_DEF
(
 COL_SMALLINT SMALLINT DEFAULT 2,
 COL_INTEGER INTEGER DEFAULT 4
);

Теперь выполним следующие запросы через наш связанный сервер:

begin distributed transaction;

insert into FB_EMPL...TABLE6_5_DEF default values;

select * from FB_EMPL...TABLE6_5_DEF;

rollback;

Результат работы:

Так же, начиная с версии 3.50, корректно обрабатывается ключевое слово «DEFAULT» в списке значений колонок:

begin distributed transaction;

insert into FB_EMPL...TABLE6_5_DEF values(22,DEFAULT);

select * from FB_EMPL...TABLE6_5_DEF;

rollback;

Результат работы:

Последним примером, с демонстрацией поддержки DEFAULT-значений, будет частичное перечисление колонок таблицы:

begin distributed transaction;

insert into FB_EMPL...TABLE6_5_DEF (COL_SMALLINT) values(222);

select * from FB_EMPL...TABLE6_5_DEF;

rollback;

Результат работы:

Обновление ряда

begin distributed transaction;

declare @country varchar(32);

set @country='Mars';

update FB_EMPL...COUNTRY set CURRENCY='Beer' where COUNTRY=@country;

commit;

Результат работы:

Удаление ряда

begin distributed transaction;

declare @country varchar(32);

set @country='Mars';

delete from FB_EMPL...COUNTRY where COUNTRY=@country;

commit;

Результат работы:

Выполнение скрипта

begin distributed transaction;

declare @country varchar(32);

set @country='Mars';

insert into FB_EMPL...COUNTRY (COUNTRY, CURRENCY) values (@country,'Mars');

select * from FB_EMPL...COUNTRY where COUNTRY=@country;

update FB_EMPL...COUNTRY set CURRENCY='Beer' where COUNTRY=@country;

select * from FB_EMPL...COUNTRY where COUNTRY=@country;

delete from FB_EMPL...COUNTRY where COUNTRY=@country;

select * from FB_EMPL...COUNTRY where COUNTRY=@country;

commit;

Результат работы:

Создание VIEW на основе связанного сервера

Создадим в тестовой базе MSSQL (с названием TEST) представление (VIEW) для таблицы COUNTRY:

use TEST;
go

create view V_COUNTRY (COUNTRY,CURRENCY)
as
select COUNTRY, CURRENCY
from FB_EMPL...COUNTRY;
go

Теперь выполним выборку из этого представления:

use TEST;
go

select * from V_COUNTRY;
go

Результат работы:

Давайте убедимся, что V_COUNTRY будет работать в рамках явной транзакции:

use TEST;
go

begin distributed transaction;

insert into FB_EMPL...COUNTRY (COUNTRY,CURRENCY) values('Russia', 'Ruble');

select * from V_COUNTRY where COUNTRY='Russia';

delete from FB_EMPL...COUNTRY where COUNTRY='Russia';

select * from V_COUNTRY where COUNTRY='Russia';

commit;

Результат работы:

Выборки из V_COUNTRY «видят» изменения в FB_EMPL…COUNTRY.

Вызов хранимой процедуры

У FB/IB есть два типа хранимых процедур:

• Возвращающие результат в виде множества.
• Возвращающие результат через OUT-параметры.

В обоих случаях, вы используете унифицированный синтаксис вызова:

EXEC name [param [,param …]];

Все остальное за вас сделает IBProvider.

Выполнение хранимой процедуры возвращающей множество

В employee.fdb есть хранимая процедура, которая возвращает идентификаторы проектов сотрудника:

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID CHAR(5))
AS
BEGIN
    FOR SELECT PROJ_ID
             FROM EMPLOYEE_PROJECT
             WHERE EMP_NO = :EMP_NO
             INTO :PROJ_ID
    DO
        SUSPEND;
END

Получим список проектов для сотрудника «Bruce Young»:

begin distributed transaction;

declare @emp_no integer;

set @emp_no=(select EMP_NO from FB_EMPL...EMPLOYEE
             where FIRST_NAME='Bruce' and LAST_NAME='Young');

print 'emp_no='+convert(varchar(32),@emp_no);

exec FB_EMPL...GET_EMP_PROJ @emp_no;

commit;

Результат работы:

Выполнение хранимой процедуры возвращающей OUT-параметры

В employee.fdb отсутствуют хранимые процедуры, возвращающие OUT-параметры. Поэтому создадим собственную процедуру, которая будет вычислять разность двух INTEGER-чисел:

CREATE PROCEDURE SP_SUB(A INTEGER, B INTEGER)
RETURNS (RESULT INTEGER)
AS
BEGIN
 RESULT=A-B;
END

Вызываем эту хранимую процедуру через Linked Server:

begin distributed transaction;

declare @A integer;
declare @R integer;

set @A=4;

exec FB_EMPL...SP_SUB @A, 1, @R output;

print 'R='+convert(varchar(32),@R);

commit;

Результат работы:

Выполнение запросов через инструкцию «EXEC AT»

Для запроса к связанному серверу, который не поддерживается MSSQL, можно воспользоваться инструкцией EXEC:

{ EXEC | EXECUTE }
    ( { @string_variable | [ N ] 'command_string [ ? ]' } [ + …n ]
        [ { , { value | @variable [ OUTPUT ] } } [ …n ] ]
    ) 
    [ AS { LOGIN | USER } = ' name ' ]
    [ AT linked_server_name ]
[;]

Через «exec(…) at» можно выполнить любые запросы, которые поддерживаются IB/FB, включая DDL и DML запросы с параметрами. Например, запрос «UPDATE RETURNING»:

begin distributed transaction;

declare @emp_no bigint;

set @emp_no=(select EMP_NO from FB_EMPL...EMPLOYEE
             where FIRST_NAME='Robert' and
                   LAST_NAME='Nelson');

print 'emp_no='+convert(varchar(32),@emp_no);

declare @fn varchar(32);

exec('update EMPLOYEE
      set FIRST_NAME=upper(FIRST_NAME)
      where EMP_NO=?
      returning NEW.FIRST_NAME',
     @emp_no,
     @fn OUTPUT) at FB_EMPL;

print 'first_name='+@fn;

rollback;

Результат работы:

Уровни изоляций транзакций

По умолчанию, распределенная транзакция будет иметь уровень изоляции «READ COMMITTED». Вы можете указать другой уровень изоляции, используя команду:

SET TRANSACTION ISOLATION LEVEL <isolation_level_name>;

В случае распределенных транзакций, поддерживаются следующие уровни изоляции:

• READ COMMITTED
• REPEATABLE READ

Работа в транзакции с уровнем изоляции «READ COMMITTED»

set transaction isolation level READ COMMITTED;

begin distributed transaction;

select * from FB_EMPL...EMPLOYEE;

commit;

Работа в транзакции с уровнем изоляции «REPEATABLE READ»

set transaction isolation level REPEATABLE READ;

begin distributed transaction;

select * from FB_EMPL...EMPLOYEE;

commit;