SQL Server has interesting feature called Linked Servers. It’s about linking other databases to SQL Server and using their data like it’s local. There are many powerful open-source systems written on PHP and they are mostly using MySQL as database. This blog post shows how to link MySQL database to SQL Server and how to use linked server in SQL queries.
What is linked server?
Linked server in MSSQL is some other database server connected to given one, making it possible to query and manipulate data in other databases. By example, we can link some MySQL database to MSSQL and use it almost like any other database on MSSQL.
Image is taken from MSSQL 2019 documentation page
Linked Servers (Database Engine)
Although communication with linked servers goes through OLE DB providers, there is also OLE DB provider for ODBC and we can use it if our external database doesn’t have OLE DB provider.
NB! Linked server is available for whole SQL Server instance. It means that all SQL Server databases can use linked server to retrieve data.
Linking MySQL to SQL Server
Adding linked server and configuring connection setting is not always easy and straightforward.
To get MySQL linked to SQL Server I needed to create ODBC DSN for MySQL (I named it as MySQLCrm). Before going to next steps, make sure that ODBC data source works.
Follow these steps to link MySQL to SQL Server:
- Run SQL Server Management Studio (SSMS)
- Connect to your server
- Expand Server Objects node from tree at left
- Right-click on Linked Servers
- Select New Linked Server…
You should see the following dialog (or bit different but the idea remains the same).
NB! Pay extra attention to what you insert to this dialog. With this set of data I made link work. I tried different values and if something is one millimeter wrong then connection fails. It’s damn sensitive dialog.
Connection string to MySQL database should be like shown here:
DRIVER=(MySQL ODBC 8.0 Unicode Driver); SERVER=localhost; PORT=3306; DATABASE=crmlinked; USER=crmuser; PASSWORD=crm_user_password; OPTION=3;
Also pay attention to OPTION=3 – without this I got only errors back when connecting to linked server.
Try to save by clicking OK and see if you can browse to linked server. If you get errors then right-click on server and select properties. Keeping the dialog open, move to Server Options page. Set RPC and RPC Out settings to True.
I’m still not very sure what are these options doing but some of those who had issues with link to MySQL made it work after setting RPC-s to true.
To make querying actually work, we need one more little change that affects whole OLE DB provider and therefore all connections using it. Open Providers node under Linked Servers, right-click on MSDASQL (this is OLE DB provider for ODBC data sources) and select properties.
Check the box before Level Zero only and click OK to save changes.
Querying data from linked server
Querying linked databases is actually easy. Here’s the customers table from crmlinked database in MySQL. This database is linked to my SQL Server.
Syntax for querying linked server is a little bit different from what we usually write on SQL Server. We need to use four-part names: server.database.schema.table. As there’s no schemas on MySQL and connection string specifies database name, then we can leave these out like shown here.
select * from MYSQLCRM...customers
Running this query from SSMS gives the following output. It’s the same data that is in MySQL customers table.
Of course, we can also write more complex queries. Everything that ODBC can handle is okay.
Mixing data from local and linked server
Tables from linked server are not totally isolated from local database tables and views. We can also mix data from local and linked server.
To demonstrate mixed query over local and linked tables let’s write simple query to get all customers from local table and their credit ratings from linked table.
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
Running this query gives us the following output.
As Mark is not present in MySQL database (suppose he is new customer in e-shop and sales department doesn’t have him yet in their CRM system) then he doesn’t have credit rating available. Credit ratings for John and Mary are coming from MySQL in current case.
Using OPENQUERY() to execute query in linked server
The examples above make all data processing on SQL Server. It can be very unoptimal if there’s a lot of data in linked server’s tables. We may want – or usually want – to process some data in linked server before SQL Server starts local processing. For this we have OPENQUERY().
Here’s the example of using OPENQUERY() function in mixed query. We have to specify linked server name and SQL query to run in linked server when calling OPENQUERY(). The query in red is executed in MySQL server and results are read to SQL Server for further processing.
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 > 1000
') crm_c ON
c.ssn = crm_c.ssn
ORDER BY
crm_c.credit_rating,
c.LastName,
c.FirstName
OPENQUERY() is great way to optimize and speed up mixed queries by running more complex queries over linked server data in linked server.
Wrapping up
Linked servers is powerful feature of SQL Server making it easy for us to use data from external servers. There are two ways to write queries using data from linked servers – direct queries that do all processing on SQL Server and OPENQUERY() that lets us do some processing in remote server. Linked server is integration and therefore using it needs extra care. Planning and performance measuring are must-be activities when planning to use linked server.
The post Querying MySQL from SQL Server using linked server appeared first on Gunnar Peipman - Programming Blog.
Top comments (0)