Mssql server linked server: Linked Server MSSQL. Оптимизация производительности в 30 раз / Хабр

Linked Server (связанный сервер) к Mysql

  Современные  системы  часто взаимодействуют с другими системами, БД, одна из которых MySql. Попробую описать подключение через Linked Server к MySql, хотя это довольно просто.

Для настройки подключения из MS SQL Server к СУБД MySQL необходимо настроить «Linked Server» к серверу Mysql.

Для этого необходимо на сервере, где установлен MS SQL Server, настроить источник данных к Mysql.  Скачиваем  с сайта Mysql и устанавливаем  драйвера ODBC Mysql, к примеру mysql-connector-odbc-5.1.8-win32.msi, там ничего сложного нет, все по умолчанию.

После установки заходим в «Администрирование (AdministrativeTools)»-> «Источники Данных ODBC (DataSource (ODBС))»->«Системный DSN (SystemDSN)»->«Добавить (Add)»

В списке драйверов должен присутствовать установленный драйвер «MySQLODBС  5.1 Driver», если его нет, то проблема с установкой драйвера.

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

DataSourceName – имя DSN подключения, произвольное, в дальнейшем мы будеv его использовать в настройках подключения в MS SQL Server.

TCP/ IP Server – IP сервера MySql или его DNS имя

Port –порт подключения к Mysql, стандартный 3306

User и Password – имя пользователя в БД Mysql и его пароль.

Databases – имя БД по умолчанию при подключение, можно его не указывать.

После указания данных параметров, проверяем настройки «Test» и сохраняем  подключение.

После того как создали ODBC подключение в ОС, необходимо настроить Linked Server в MS SQL Server.

Создаем новый LinkServer:

Имя связанного сервера – задаем имя cвязанного сервера, будем использоваться его в дальнейшем в запросах.

Название продукта и Источник данных – имя нашего настроенного ODBCподключения.

Нажимаем «ОК», если все создали правильно, то создание связанного сервера закончится без ошибок, иначе будет ошибка, в том числе и не на неправильный пароль к MySql или отсутствие доступа к БД.

После этого можно запускать запросы к Mysql через Openquery

  select*from openquery(mysql,’select id from groups’)

Какая здесь может быть проблема в данных запросах? Это наличие одних типов данных Mysql и их отсутствие в MS SQL server-е. Текст ошибки будет таков, как например на такой запрос:

  select*from openquery(mysql,’select * from projects’)

 Для решения данной проблемы необходимо использовать преобразование типов на строне mysql, либо отказаться в использование данных столбцов в запросах

  select*from openquery(mysql,’select id from projects’)

это запрос уже сработает:

  select*from openquery(mysql,’select id,cast(viewtype as char(30)) from       projects’)

В данном запросе мы использовали функцию преобразования Mysql CAST().

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

К примеру так :

  Declare @sqlstr nvarchar(850)

  Set @sqlstr=’insert into dbo.tasks_pr select id,[user],st,priority, from      openquery(sd,»SELECT id,user,cast(state as char(15)) as st,’

  set @sqlstr=@sqlstr+’cast(priority as char(15)) as priority FROM tasks t’

  set @sqlstr=@sqlstr+’ where id>’+CAST(@idasnvarchar(10))+»’)’

  —print @sqlstr

  execute sp_executesql @sqlstr

Кстати командой «print» легко проверять, что за команда получается в итоге.

Вот и все доступ к Mysql из MS SQL Server  получили.

Общие сведения о связанных серверах SQL Server

Автор: Эрик Блинн   |
Обновлено: 07.04.2022   |
Комментарии (7)   | Связанный: Подробнее > Связанные серверы

Проблема

Я вижу, что в SQL Server Management Studio (SSMS) есть опция для связанных серверов и
Я хочу узнать больше о том, что они делают и как они работают.

Решение

Связанные серверы — это метод, с помощью которого SQL Server может взаимодействовать с другим совместимым с ODBC сервером.
базе данных, такой как другой экземпляр SQL Server или база данных Oracle, с прямым
T-SQL-запрос.

Существует несколько важных параметров, которые необходимо знать при настройке связанного сервера.
В этом совете рассматривается создание связанного сервера с другим экземпляром SQL Server с использованием
графический интерфейс SSMS вместе с соответствующими параметрами безопасности. Также будет показано, как использовать
связанный сервер в операторе T-SQL. Eсть

большая библиотека более сложных тем, доступных после освоения основ.

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

В SQL Server Management Studio в области обозревателя объектов разверните раздел «Сервер
«Объекты», щелкните правой кнопкой мыши «Связанные серверы» и выберите «Новый
Связанный сервер…» из меню.

Новый связанный сервер — общая страница

Появится экран, похожий на показанный ниже. Выберите сервер
введите SQL Server и введите имя другого экземпляра SQL Server. В
В этом примере соединение будет выполнено с именованным экземпляром SQL Server на
такая же машина. Это, безусловно, разрешено, но чаще соединение выполняется
на другую машину совсем. Имя связанного сервера указывается как
ИмяСервера\ИмяЭкземпляра. В приведенном ниже примере я подключаюсь к локальному
сервер и имя экземпляра «SECURITY_TEST».

Новый связанный сервер — страница безопасности

Затем перейдите со страницы «Общие» на страницу «Безопасность» с помощью меню слева.
Эта страница позволяет администратору сообщить этому SQL Server, как аутентифицироваться для
другой экземпляр. Верхняя половина экрана позволяет регистрировать отдельные логины.
предоставлен доступ к использованию связанного сервера.

Существует 2 метода аутентификации пользователей. Первый — выбрать логин для
их использовать при использовании связанного сервера. На скриншоте ниже пользователь Эрик
автоматически войдет на вторичный сервер как логин LinkedServerTest
на другом экземпляре. Эти учетные данные хранятся в SQL Server Engine и
зашифрован. Во второй строке логин LinkedServerTest (логин SQL) олицетворение
флажок установлен. Для учетной записи SQL это означает, что SQL Server попытается войти в систему, используя то же имя пользователя и пароль на связанном сервере, что и на исходном сервере. Также можно выполнить олицетворение имени входа Windows, но для этого в домене должны быть настроены правильные Kerberos, имена участников-служб и правильные правила делегирования.

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

Опция Описание
Не производиться Это выдаст ошибку любому пользователю, пытающемуся использовать связанный сервер.
которого нет в списке выше. Чтобы выбрать этот вариант,
должен быть хотя бы 1 логин в верхней половине экрана.

Это
ошибка, которую получат все остальные пользователи.

Сообщение 7416, уровень 16, состояние 1,
Строка 1
Доступ к удаленному серверу запрещен из-за отсутствия сопоставления входа в систему.
существует.

Сделать без использования контекста безопасности Это отправит анонимное подключение к связанному серверу и, вероятно,
не будет предоставлять никакого доступа к вторичному серверу.
Сделать с использованием текущего контекста безопасности входа Это то же самое, что разместить каждого пользователя в верхнем разделе с помощью
установлен флажок олицетворения.
Сделать с использованием этого контекста безопасности: Эта опция потребует ввода логина и пароля и будет работать
так же, как если бы каждый отдельный пользователь был добавлен в верхний раздел и получил
точно такой же логин и пароль. Это наименее безопасный метод установки
связанный сервер, так как любой, у кого есть доступ к первому SQL-серверу,
иметь возможность использовать связанный сервер, используя сохраненные учетные данные.
Новый связанный сервер — параметры сервера Страница

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

В большинстве случаев изменять настройки не требуется, и эта таблица будет
объясните что делают настройки.

Опция Описание
Совместимость с подборкой Значение по умолчанию — ложь. Для этого можно установить значение true, если целевой сервер
использует ту же сортировку, что и основной сервер. Изменение
может повысить производительность некоторых запросов, разрешив удаленное сравнение столбцов
в столбцах текстового типа.
Доступ к данным Значение по умолчанию: Истина. При значении false все отдельные удаленные выполнения запросов
не удастся. Этот параметр не зависит от выхода RPC. В сценарии
если необходимо выполнять ТОЛЬКО удаленные вызовы процедур, этот параметр может быть
изменяется на false, в то время как RPC Out остается истинным.
RPC/выход RPC Обозначает удаленный вызов процедур и позволяет запускать сохраненные
процедуры на связанном сервере. RPC включает удаленную процедуру
Вызовы с указанного сервера и RPC OUT включают удаленную процедуру
Звонки на указанный сервер.
Использовать удаленную сортировку/имя сортировки По умолчанию — Ложь. При значении false сопоставление локального сервера будет
используется во всех удаленных запросах. При истинном сопоставлении удаленного
сервер будет использоваться для запросов. Если true и удаленный сервер НЕ является SQL
Сервер, то локальный сервер не может автоматически искать правильную сортировку
ценить. В этом случае используйте параметр Collation Name, чтобы ввести
сопоставление удаленного сервера.
Время ожидания соединения Значение по умолчанию: 0. Если не равно нулю, это количество секунд,
подождите до истечения времени ожидания при подключении к связанному серверу. Когда 0, то
значение на самом деле считывается с сервера по умолчанию, которое можно найти, запустив
эта команда.

 exec sp_configure 'время ожидания удаленного входа в систему (с)'
Время ожидания запроса Значение по умолчанию: 0. Если не равно нулю, это количество секунд,
подождите, прежде чем истечет время выполнения запроса к связанному серверу. Когда 0 тогда
значение фактически считывается с сервера по умолчанию, которое можно найти, запустив
эта команда.

 exec sp_configure 'время ожидания удаленного запроса (с)'
Дистрибьютор/издатель/подписчик Эти параметры используются только тогда, когда связанный сервер используется для
поддерживать репликацию.
Ленивая проверка схемы По умолчанию — Ложь. При значении false проверяется схема удаленного запроса.
перед каждым выполнением запроса. Любые изменения в удаленной схеме
обнаружены заранее, и правильный план запроса может быть воссоздан для учета
для изменения. Когда это правда, схема не проверяется до момента
исполнение. Если изменение в удаленной схеме делает план запроса недействительным
это вызовет ошибку времени выполнения.
Включить продвижение распределенных транзакций Значение по умолчанию: Истина. При включении любого удаленного запроса, изменяющего данные
вызовет DTC (координатор распределенных транзакций). Этот процесс
защищает такие запросы, позволяя обоим серверам иметь общую транзакцию
которая либо завершается на 100 %, либо на 0 %, как и обычная транзакция внутри
локальный запрос. Когда отключено, код DTC не вызывается, и неисправный удаленный
запрос может завершиться ошибкой и не может быть полностью откатан.

Запрос данных через связанный сервер SQL Server

Для чтения данных со связанного сервера на любые таблицы или представления необходимо ссылаться с помощью
идентификатор из 4 частей, состоящий из имени связанного сервера, имени базы данных, схемы
имя и имя объекта – именно в таком порядке.

Например:

 ВЫБЕРИТЕ * ИЗ [.\SECURITY_TEST].master.sys.databases

ВЫБЕРИТЕ * ИЗ [.\SECURITY_TEST].WideWorldImporters.Sales.Orders
  • Сначала указывается имя связанного сервера: [.\SECURITY_TEST] для
    наш пример. В этом примере он должен быть заключен в квадратные скобки из-за его формата. Не все связаны
    Имена серверов должны быть заключены в квадратные скобки.
  • Далее следует имя базы данных, master и WideWorldImporters соответственно.
  • В-третьих, имя схемы, sys и Sales соответственно.
  • Наконец, отображается имя объекта. В этих примерах объектами являются базы данных
    и Заказы.

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

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

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

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

Для целей этой демонстрации эта небольшая процедура была создана в главном
базу данных на целевом связанном сервере.

 мастер ЕГЭ
ИДТИ

СОЗДАТЬ ПРОЦЕДУРУ dbo.SQLTips1
КАК
SELECT 'Мне удалось позвонить';

Затем процедура выполняется на первичном экземпляре с помощью этой команды, которая
следует той же схеме идентификатора из 4 частей, что и операторы в предыдущем разделе.

 EXEC [.\Security_Test].master.dbo.SQLTips1

Однако вместо ожидаемого результата возникает ошибка.

RPC означает удаленный вызов процедур. По умолчанию связанные серверы не
возможность совершать вызовы хранимых процедур. Это легко исправить, включив
единая настройка на связанном сервере. Перейдите к свойствам связанного сервера,
выберите Параметры сервера и 4 -й
вариант в списке будет RPC Out. Просто измените это значение на True и нажмите
ХОРОШО.

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

Повторное выполнение процедуры дает гораздо лучший результат.

Ограничения связанного сервера SQL Server

Связанные серверы

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

Варианты использования связанного сервера SQL Server

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

Иногда требование ETL для меньшего набора данных может быть выполнено с помощью связанного
Сервер, а не инструмент ETL, такой как SSIS. Когда число строк исчисляется тысячами
или менее 10 тысяч, которые ETL на основе связанного сервера может выполнить за
меньше времени, чем требуется для запуска механизма SSIS.

Следующие шаги

  • На MSSQLTips.com есть целое семейство расширенных советов по связанным серверам.

  • Облегчение чтения имен связанных серверов

Об авторе

Эрик Блинн — старший архитектор данных в компании Squire Patton Boggs. Он также является автором SQL и руководителем локальной группы PASS.

Посмотреть все мои советы

Последнее обновление статьи: 07.04.2022

О связанных серверах | Облачный SQL для SQL Server

MySQL
| PostgreSQL
| SQL-сервер

Связанные серверы дают SQL Server возможность считывать данные из удаленных источников данных
и выполнять команды в удаленных базах данных. На этой странице представлена ​​информация о
Разрешения экземпляра Cloud SQL, что следует учитывать при реализации связанных
серверов, а также ограничения использования связанных серверов.

Чтобы настроить связанные серверы, вам потребуются следующие разрешения экземпляра и пользователя:

  • ИЗМЕНИТЬ ЛЮБОЙ СВЯЗАННЫЙ СЕРВЕР : Cloud SQL предоставляет это разрешение при добавлении
    cloudsql включает флаг связанных серверов для вашего экземпляра.
  • ИЗМЕНИТЬ ЛЮБОЙ ВХОД : Cloud SQL автоматически предоставляет это разрешение
    пользователь с правами администратора для экземпляра Cloud SQL. Это разрешение позволяет
    вам создать сопоставление между логином в Cloud SQL и безопасностью
    учетная запись на связанном сервере.

Список разрешений по умолчанию, доступных для экземпляров Cloud SQL, см.
Пользователи SQL Server по умолчанию.

Для получения информации о том, как предоставить ИЗМЕНИТЬ ЛЮБОЙ СВЯЗАННЫЙ СЕРВЕР или ИЗМЕНИТЬ ЛЮБОЙ
LOGIN разрешение на дополнительные учетные записи, см. пример на Предоставление сервера
разрешения.

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

Безопасность

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

  • Реализовать соответствующие механизмы аутентификации и авторизации, такие как
    SSL-шифрование.
  • Внедрить строгие политики паролей.
  • Убедитесь, что только авторизованные пользователи имеют доступ к связанному серверу.
  • Убедитесь, что конфиденциальные данные правильно зашифрованы и защищены.
  • Убедитесь, что логины имеют доступ только к тем ресурсам, которые вы используете во время
    конфигурация.

Производительность

Существует несколько факторов, которые могут повлиять на производительность связанных серверов:

  • Связанные серверы могут вызывать задержки и снижать производительность при выполнении запросов.
    сложны, в зависимости от объема данных, которые передает каждый сервер. Это может
    вызвать проблемы для приложений, требующих синхронизации данных в реальном времени.
  • При использовании связанных серверов для выполнения запроса ядро ​​базы данных создает
    новое соединение и извлекает данные для каждого запускаемого вами запроса. Данные не кэшируются
    и сервер может
    скопировать всю таблицу по сети
    каждый раз, когда вы используете связанный сервер.
  • Cloud SQL не может создать оптимизированный план выполнения
    для удаленного сервера, потому что он не знает, как удаленный сервер обрабатывает
    индексы и статистика.

Для оптимизации производительности выполните следующие действия:

  • Тщательно разработайте свои запросы и убедитесь, что только необходимые данные
    передаются между серверами.
  • Использовать распределенные запросы SQL Server
    особенность.
  • Распределите запросы по нескольким серверам.
  • Регулярно отслеживайте и оптимизируйте конфигурацию связанного сервера, чтобы помочь
    убедитесь, что он работает гладко.

Надежность

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

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

  • Реализовать резервный связанный сервер для отработки отказа.