SQL Server has interesting feature calles 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.
[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).
![MySQL linked server settings]
**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.
#data platform #data analysis
