SQL Server имеет интересную особенность, называемую Linked Servers. Речь идет о связывании других баз данных с SQL Server и использовании их данных как локальных. Есть много мощных систем с открытым исходным кодом, написанных на PHP, и они в основном используют MySQL в качестве базы данных. В этой записи блога показано, как связать базу данных MySQL с SQL Server и как использовать связанный сервер в запросах SQL.
Что такое связанный сервер?
Связанный сервер в MSSQL – это некоторый другой сервер баз данных, подключенный к данному, что позволяет запрашивать и манипулировать данными в других базах данных. Например, мы можем связать некоторую базу данных MySQL с MSSQL и использовать ее почти как любую другую базу данных на MSSQL.
Для более детального углубления, вы можете узнать больше о том, как создать связанный сервер (Linked Server) в Microsoft SQL Server.
Изображение взято со страницы документации MSSQL 2019 Связанные серверы (компонент Database Engine)
Хотя связь со связанными серверами осуществляется через поставщиков OLE DB, существует также поставщик OLE DB для ODBC, и мы можем использовать его, если в нашей внешней базе данных нет поставщика OLE DB.
Связанный сервер доступен для всего экземпляра SQL Server. Это означает, что все базы данных SQL Server могут использовать связанный сервер для получения данных.
Связывание MySQL с SQL Server
Добавление связанного сервера и настройка параметров соединения не всегда просты и понятны.
Чтобы связать MySQL с SQL Server, мне нужно было создать ODBC DSN для MySQL (мы назвали ее MySQLCrm). Прежде чем перейти к следующим шагам, убедитесь, что источник данных ODBC работает.
Выполните следующие шаги, чтобы связать MySQL с SQL Server:
- Запустите SQL Server Management Studio (SSMS)
- Подключитесь к вашему серверу
- Разверните узел Объекты сервера из дерева слева
- Щелкните правой кнопкой мыши на связанных серверах
- Выберите новый связанный сервер …
Вы должны увидеть следующий диалог (или немного другой, но идея остается прежней).
NB! Обратите особое внимание на то, что вы вставляете в этот диалог. С этим набором данных мы заставили работать ссылку. Мы пробовали разные значения, и если что-то не так на один миллиметр, соединение не устанавливается. Это чертовски чувствительный диалог.
Строка подключения к базе данных MySQL должна быть такой, как показано здесь:
DRIVER=(MySQL ODBC 8.0 Unicode Driver); SERVER=localhost; PORT=3306; DATABASE=crmlinked; USER=crmuser; PASSWORD=crm_user_password; OPTION=3;
Также обратите внимание на OPTION = 3 – без этого мы получали только ошибки при подключении к связанному серверу.
Попробуйте сохранить, нажав OK, и посмотрите, сможете ли вы перейти на связанный сервер. Если вы получили ошибки, щелкните правой кнопкой мыши на сервере и выберите свойства. Оставив диалог открытым, перейдите на страницу параметров сервера. Установите для параметров RPC и RPC Out значение True.
Мы все еще не совсем уверены, что делают эти опции, но некоторые из тех, у кого были проблемы со связью с MySQL, заставили его работать после установки RPC в значение true.
Чтобы запрос действительно работал, нам нужно еще одно небольшое изменение, которое затрагивает весь поставщик OLE DB и, следовательно, все соединения, использующие его. Откройте узел «Поставщики» в разделе «Связанные серверы», щелкните правой кнопкой мыши MSDASQL (это поставщик OLE DB для источников данных ODBC) и выберите свойства.
Установите флажок только перед Level Zero и нажмите OK, чтобы сохранить изменения.
Запрос данных со связанного сервера
Запрашивать связанные базы данных на самом деле просто. Вот таблица клиентов из базы данных crmlinked в MySQL. Эта база данных связана с моим SQL Server.
Синтаксис запросов к связанному серверу немного отличается от того, что мы обычно пишем на SQL Server. Нам нужно использовать имена из четырех частей: server.database.schema.table. Поскольку в MySQL нет схем, а в строке подключения указано имя базы данных, мы можем их оставить, как показано здесь.
select * from MYSQLCRM...customers
Выполнение этого запроса из SSMS дает следующий вывод. Это те же данные, что и в таблице клиентов MySQL.
Конечно, мы также можем написать более сложные запросы. Все, что ODBC может обработать, хорошо.
Смешение данных с локального и связанного сервера
Таблицы со связанного сервера не полностью изолированы от таблиц и представлений локальной базы данных. Мы также можем смешивать данные с локального и связанного сервера.
Чтобы продемонстрировать смешанный запрос к локальным и связанным таблицам, давайте напишем простой запрос, чтобы получить всех клиентов из локальной таблицы и их кредитные рейтинги из связанной таблицы.
SELECT c.FirstName, c.LastName, crm_c.credit_rating as CreditRating FROM Customers c LEFT JOIN MYSQLCRM...customers crm_c ON c.ssn = crm_c.ssn ORDER BY crm_c.credit_rating, c.LastName, c.FirstName
Выполнение этого запроса дает нам следующий вывод.
Поскольку Марк отсутствует в базе данных MySQL (предположим, он новый клиент в интернет-магазине, а отдел продаж еще не имеет его в своей системе CRM), у него нет кредитного рейтинга. В текущем случае кредитные рейтинги Джона и Мэри исходят из MySQL.
Использование OPENQUERY() для выполнения запроса на связанном сервере
В приведенных выше примерах вся обработка данных выполняется на SQL Server. Это может быть очень неоптимально, если в таблицах связанных серверов много данных. Мы можем захотеть – или обычно хотим – обработать некоторые данные на связанном сервере, прежде чем SQL Server начнет локальную обработку. Для этого у нас есть OPENQUERY().
Вот пример использования функции OPENQUERY() в смешанном запросе. Мы должны указать имя связанного сервера и запрос SQL для запуска на связанном сервере при вызове OPENQUERY(). Запрос красного цвета выполняется на сервере MySQL, а результаты считываются на SQL Server для дальнейшей обработки.
SELECT c.FirstName, c.LastName, crm_c.credit_rating as CreditRating FROM Customers c LEFT JOIN OPENQUERY(MYSQLCRM, ' SELECT c.credit_rating FROM customers p left join loyalty_points lp on c.customer_id = lp.customer_id WHERE lp.points > 2500 ') crm_c ON c.ssn = crm_c.ssn ORDER BY crm_c.credit_rating, c.LastName, c.FirstName
OPENQUERY() – отличный способ оптимизировать и ускорить смешанные запросы, выполняя более сложные запросы к данным связанного сервера на связанном сервере.
Завершение
Связанные серверы – это мощная функция SQL Server, облегчающая нам использование данных с внешних серверов. Существует два способа написания запросов с использованием данных со связанных серверов: прямые запросы, которые выполняют всю обработку на SQL Server, и OPENQUERY(), которая позволяет нам выполнять некоторую обработку на удаленном сервере. Связанный сервер является интеграцией, поэтому его использование требует особой осторожности. Планирование и измерение производительности должны быть обязательными при планировании использования связанного сервера.