2014/10/26

SQL ServerからMySQLへリンクサーバを設定する

スクリーンショット 2014 10 26 14 56 51

皆さんはデータベースとして何をお使いですか?
規模にもよりますがOracleだったり、オープン系だとMySQLやPostgreSQLが人気ですよね。

そんな中でもMicrosoftが提供しているSQL Serverもなかなか捨てがたい選択肢です。
BI周りの機能が強いだけでなく同じMicrosoftが提供するExcelとの連携(Power BI)が強いのも特徴で、
小規模からDWHまでをオールインワンで提供しているものもMicrosoftらしいところです;-p

なにより私のような根っからのエンジニアではない人が簡単にSQLを実行できる環境として非常に強力な管理ツールSQL Server Management Studioが提供されているのも大きなポイントです。
MySQL Workbenchも近いツールといえばそうなのですが、標準で日本語化されているというのはManagement Studioをおすすめできる点ですね。

 

全社的にSQL Serverを使っているのであればManagement Studioひとつで全部管理出来るのですが、管理やトランザクション保存や会員管理、開発系…案外プロダクトがバラバラであるというのも有り得る話。
これを最初から見越して設計できているといいのですが、大体が後付増築で増えてゆく…となると分析基盤を作る際にデータを一箇所に集める必要があるわけです。

そこで、SQL Serverにはリンクサーバという機能があり、別サーバで稼働しているDBに接続できます。てっきりMSのことだから接続先もSQL Serverじゃないかとダメかと思っていましたがそんなことはなく、ODBCドライバ経由でMySQLやOracleなどもSQL Server経由で扱えるようになります!

 

というわけで実際にやってみました。

シナリオ:Windows上で動いているSQL Serverから別マシン上のMySQLにリンクサーバを設定して接続する。

前提:MySQLは別マシンですでに稼働しており、外部からのアクセスの設定が完了していること。
MySQL側はアクセスできればいいので、一切環境を変更する必要がありません。

スクリーンショット 2014 10 26 13 58 45

 

まずはMySQLに接続するために、SQL Serverが稼働しているWindowsマシンにODBCドライバをインストール。
MySQL :: Download Connector/ODBC

ちなみにダウンロードするのは64bitのものになります。
注意しないといけないのはすでにExcelなどを導入しておりExcelからMySQLに接続するために32bitのODBCドライバがインストールされている場合にも、SQL Serverに合わせた64bitのものをインストールし直してください。

 

スクリーンショット 2014 10 26 14 02 44

Windowsの検索窓に「ODBC」と入力して出てくるデータソースに接続先の情報を設定します。
※ユーザーDSNでもいいらしいですが、自分の環境だとダメだったので”システムDSN”に登録しました。

 

スクリーンショット 2014 10 26 14 03 00

先ほどインストールした「MySQL ODBC 5.3 Unicode Driver」を選択。
日本語を扱うならUnicodeを選択するのが無難。

 

スクリーンショット 2014 10 26 14 09 13

MySQLの設定画面が出てくるので情報を入力してTestしてみます。
ちなみにData Source Nameは任意で構いません。ちなみにわかりやすくするためにMySQLにしてみました。後ほど使いますので忘れないでね。

 

スクリーンショット 2014 10 26 14 10 19

これからの作業はSQL Server Management Studioで行います。
新しいリンクサーバのセットアップを行います。

 

スクリーンショット 2014 10 26 14 48 23

リンクサーバ:任意(今回はMYSQL)
プロバイダー:〜ODBC Drivers 
製品名:MySQL
データソース:MySQL  ※先ほどのODBCデータソース画面で設定したData Source Nameを入力してください。
プロバイダー文字列

DRIVER={MySQL ODBC 5.3 UNICODE Driver};SERVER=133.242.xxx.xxx;PORT=3306;DATABASE=test;USER=root;PASSWORD=Password;OPTION=3;

それぞれについて。
 Driver:一番最初にインストールしたMySQLのODBCドライバ
 Server:サーバのIP/ホスト名
 Port:接続に利用するポート番号
 DataBase:デフォルトで接続するデータベース名
 User:外部アクセスを許可されているアカウント名
 Password:外部アクセスできるユーザーのパスワード

カタログ:test ※デフォルで接続するDB名

 

スクリーンショット 2014 10 26 14 13 18

 今度はセキュリティのタブで、リモートログインのアカウント名を設定します。
これは先程のUserとPasswordになります。

 

スクリーンショット 2014 10 26 14 13 37

問題がなければめでたくリンクサーバにMYSQLが追加されます!

スクリーンショット 2014 10 26 14 56 51

select * from openquery (MYSQL, 'select * from test.item limit 0,100')

 MySQLのtestという名前のDBの中のitemというテーブルに格納されているレコードの先頭100件を抽出。

これでMySQLの問い合わせ結果をSQL Server上で扱えるようになりました。
やったね!

 

MySQLのデータをSQL Serverに取り込めば、SQL Server Analysis ServicesやExcelのPower BIなどの強力な分析ツールから簡単に利用することが可能になります。
ODBCドライバを設定してあげれば同様の方法でOracleやPostgreSQLに接続できます。

※別にODBCアクセスできればSQL Serverがなくても出来るじゃないですかって…あっ、そうですね ^q^