Сегодня решил поделиться статьей как однажды мне пришел на выручку связанный сервер при работе с MSSQL. Сначала опишу ситуацию, в которой мне пришлось с ним познакомиться.
Я работал web программистом в информационном центре одного из министерств с около сотней подведомственных учреждений. В каждом подведомственном учреждении на сервере была установлена десктоп программа, написанная на delphi, в которую ежедневно вносились данные. Раз в квартал каждому такому учреждению нужно было выгрузить dbf файл, приехать к нам в центр, по данным этой выгрузки получить отчеты и сдать их в министерство. Так было еще в досовской программе, а потом этот алгоритм просто ничего не меняя, перенесли в delphi. Выгрузка осуществлялась средствами Transact-SQL, и логика в ней была не простая.
Параллельно с этим в оффлайн режиме работала шина, которая скапливала данные со всех учреждений на единый центральный сервер. В шине были багги: она создавала дубли по первичному ключу и не все данные доходили. Конкретного алгоритма по исправлению этих ошибок не было, этим занимались разные сотрудники в разные периоды времени, не ставя друг друга в известность. Разработчик шины уволился. Через три года такой работы данные на центральном сервере значительно отличались от данных на серверах учреждений, однако все официально придерживались версии, что с шиной проблем нет.
В один момент схему с выгрузкой файла посчитали устаревшей, и были выделены деньги на доработку. Было решено сдавать отчеты на сайте в личном кабинете по нажатию кнопки. Теперь сотрудники учреждений не должны были ездить для сдачи отчета сначала к нам и потом в министерство, все общение должно было осуществляться через сайт. Данные нужно было брать с центрального сервера. Начальник отдела, не смотря на то, что знал про проблемы с шиной, велел взять процедуру, что работала на серверах учреждений, в выборке добавить условие учитывающее сегмент учреждений, и реализовать выгрузку отчетов на сайте с центрального сервера. После того как это было сделано, он назначил меня ответственным за данный процесс, а руководство официально объявило, что отчеты в министерство сдаем по новой схеме.
Все рухнуло. Из-за расхождения данных между серверами отчеты были с неверными цифрами. Так же все легло в плане производительности, мы не были готовы к такой нагрузке. От меня требовалось быстрое решение проблемы. Вариант просто прописать у себя на сайте для каждого учреждения параметры подключения к их БД и запускать процедуру у них на сервере (средствами языка программирования) не подходил, так как помимо получения данных нужно было каждый раз запускать обработку для конвертации этих данных в отчеты. Процедура обработки уже была реализована и отлажена в mssql на центральном сервере, а перенос ее в язык программирования занял бы много ресурсов и времени. Нужно было справляться средствами БД.
Погуглив я нашел информацию, что в MSSQL существуют связанные серверы. С помощью них для своего сервера я мог настроить связь с любым удаленным сервером, который в одной сети с моим и от которого у меня есть авторизационные данные. После настройки я мог на своем сервере написать запрос, указать на каком связанном сервере его нужно выполнить, и запрос выполнялся на удаленном сервере, с использованием его баз данных и его ресурсов.
Для создания связанного сервера нужно выполнить скрипт:
Параметры сервера
server – имя сервера, по которому мы будем к нему обращаться
@datasrc – ip адрес удаленного сервера
Параметры авторизации сервера
@rmtsrvname – имя, которое мы назначили серверу
@locallogin – имя учетной записи
@rmtpassword – пароль учетной записи
@rmtuser – пользователь БД
При создании связанного сервера часть параметров по доступу к данным проставляется в значение 'false' (список параметров вы можете посмотреть тут ). Если вам нужно, какие-то параметры установить в значение 'true', например 'rpc'и 'rpc out', то к скрипту создания нужно добавить следующие команды:
Обратите внимание, что в параметре server мы указали то имя, которое мы дали связанному серверу.
В итоге скрипт создания связанного сервера целиком выглядел бы так
Запрос к созданному серверу выполняется, так же как и к своему, но в начале указывается префикс с именем связанного сервера. Так же при обращении нужно указывать имя схемы (в примере ниже схема называется ‘DBO’):
В общем, техническая поддержка в течение пары дней для всех учреждений прописала связанные сервера. Я дописал код, чтобы данные получались с серверов учреждений. Цифры в отчетах стали вновь верными и вопрос производительности решился. Вот так я быстро и легко вышел из сложной ситуации.
Конечно, изначально при разработке системы не стоит закладываться на связанные сервера для реализации описанной функциональности. Лучше изначально грамотно подойти к проектирование системы, например сделав ее на web, где будет один центральный сервер а чтобы все не тормозило держать в штате специалистов разбирающихся в оптимизации баз данных. Данный пример для случаев, когда система уже спроектирована, и переделать ее вряд ли получится. Так же связанный сервер будет полезен при выверке отчетов, в случае если данные есть только на продуктивном сервере, а менять хранимую процедуру можно только на сервере разработки.
Я работал web программистом в информационном центре одного из министерств с около сотней подведомственных учреждений. В каждом подведомственном учреждении на сервере была установлена десктоп программа, написанная на delphi, в которую ежедневно вносились данные. Раз в квартал каждому такому учреждению нужно было выгрузить dbf файл, приехать к нам в центр, по данным этой выгрузки получить отчеты и сдать их в министерство. Так было еще в досовской программе, а потом этот алгоритм просто ничего не меняя, перенесли в delphi. Выгрузка осуществлялась средствами Transact-SQL, и логика в ней была не простая.
Параллельно с этим в оффлайн режиме работала шина, которая скапливала данные со всех учреждений на единый центральный сервер. В шине были багги: она создавала дубли по первичному ключу и не все данные доходили. Конкретного алгоритма по исправлению этих ошибок не было, этим занимались разные сотрудники в разные периоды времени, не ставя друг друга в известность. Разработчик шины уволился. Через три года такой работы данные на центральном сервере значительно отличались от данных на серверах учреждений, однако все официально придерживались версии, что с шиной проблем нет.
В один момент схему с выгрузкой файла посчитали устаревшей, и были выделены деньги на доработку. Было решено сдавать отчеты на сайте в личном кабинете по нажатию кнопки. Теперь сотрудники учреждений не должны были ездить для сдачи отчета сначала к нам и потом в министерство, все общение должно было осуществляться через сайт. Данные нужно было брать с центрального сервера. Начальник отдела, не смотря на то, что знал про проблемы с шиной, велел взять процедуру, что работала на серверах учреждений, в выборке добавить условие учитывающее сегмент учреждений, и реализовать выгрузку отчетов на сайте с центрального сервера. После того как это было сделано, он назначил меня ответственным за данный процесс, а руководство официально объявило, что отчеты в министерство сдаем по новой схеме.
Все рухнуло. Из-за расхождения данных между серверами отчеты были с неверными цифрами. Так же все легло в плане производительности, мы не были готовы к такой нагрузке. От меня требовалось быстрое решение проблемы. Вариант просто прописать у себя на сайте для каждого учреждения параметры подключения к их БД и запускать процедуру у них на сервере (средствами языка программирования) не подходил, так как помимо получения данных нужно было каждый раз запускать обработку для конвертации этих данных в отчеты. Процедура обработки уже была реализована и отлажена в mssql на центральном сервере, а перенос ее в язык программирования занял бы много ресурсов и времени. Нужно было справляться средствами БД.
Погуглив я нашел информацию, что в MSSQL существуют связанные серверы. С помощью них для своего сервера я мог настроить связь с любым удаленным сервером, который в одной сети с моим и от которого у меня есть авторизационные данные. После настройки я мог на своем сервере написать запрос, указать на каком связанном сервере его нужно выполнить, и запрос выполнялся на удаленном сервере, с использованием его баз данных и его ресурсов.
Для создания связанного сервера нужно выполнить скрипт:
EXEC master.dbo.sp_addlinkedserver @server = N'MY_SERV_1', @datasrc=N'192.168.1.1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MY_SERV_1',@useself=N'False',@locallogin='sa',@rmtuser='sa',@rmtpassword='1111111'
Параметры сервера
server – имя сервера, по которому мы будем к нему обращаться
@datasrc – ip адрес удаленного сервера
Параметры авторизации сервера
@rmtsrvname – имя, которое мы назначили серверу
@locallogin – имя учетной записи
@rmtpassword – пароль учетной записи
@rmtuser – пользователь БД
При создании связанного сервера часть параметров по доступу к данным проставляется в значение 'false' (список параметров вы можете посмотреть тут ). Если вам нужно, какие-то параметры установить в значение 'true', например 'rpc'и 'rpc out', то к скрипту создания нужно добавить следующие команды:
EXEC master.dbo.sp_serveroption @server=N'MY_SERV_1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server= N'MY_SERV_1', @optname=N'rpc out', @optvalue=N'true'
GO
Обратите внимание, что в параметре server мы указали то имя, которое мы дали связанному серверу.
В итоге скрипт создания связанного сервера целиком выглядел бы так
EXEC master.dbo.sp_addlinkedserver @server = N'MY_SERV_1', @datasrc=N'192.168.1.1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MY_SERV_1',@useself=N'False',@locallogin='sa',@rmtuser='sa',@rmtpassword='1111111'
GO
EXEC master.dbo.sp_serveroption @server=N'MY_SERV_1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server= N'MY_SERV_1', @optname=N'rpc out', @optvalue=N'true'
GO
Запрос к созданному серверу выполняется, так же как и к своему, но в начале указывается префикс с именем связанного сервера. Так же при обращении нужно указывать имя схемы (в примере ниже схема называется ‘DBO’):
SELECT * FROM [MY_SERV_1'].MY_BASE.DBO.MY_TABLE
В общем, техническая поддержка в течение пары дней для всех учреждений прописала связанные сервера. Я дописал код, чтобы данные получались с серверов учреждений. Цифры в отчетах стали вновь верными и вопрос производительности решился. Вот так я быстро и легко вышел из сложной ситуации.
Конечно, изначально при разработке системы не стоит закладываться на связанные сервера для реализации описанной функциональности. Лучше изначально грамотно подойти к проектирование системы, например сделав ее на web, где будет один центральный сервер а чтобы все не тормозило держать в штате специалистов разбирающихся в оптимизации баз данных. Данный пример для случаев, когда система уже спроектирована, и переделать ее вряд ли получится. Так же связанный сервер будет полезен при выверке отчетов, в случае если данные есть только на продуктивном сервере, а менять хранимую процедуру можно только на сервере разработки.