OCIの異機種DBLINK検証環境構築メモ ～その２ [OCI]
先日の異機種DBのDBLINK性能検証を行ったOCI上の検証環境の構築方法について簡単にメモしておく。前回はDBCSでOracleを構成したので、今回はOCI上にSQL Serverを構成し、そこからOracleへLinkedServerで接続する。
１．VCNの設定
２．DBCSの構築
３．DB設定（初期化パラメータ設定、PDB構築）
４．SQL Serverの構築 ★
５．LinkedServerの構成 ★
６．DB2の構築
７．Federationの構成
８．OracleGWの構築
９．SQL ServerへのDBLINKの構成
１０．DB2へのDBLINKの構成
まずはWindows2019を構築する。構築といっても、基本的にはOCIのメニューのComputeのInstanceからCreate Instanceを選択し、Image and shapeでWindows(Server 2019 Standard)を選択していくだけである。シェイプはVM.Standard2.1(OCPU 1, memory 15GB, Boot volume 256GB)となった。作成は比較的すぐ終わる。作成されたら、Instance Detail画面のInstant AccessでパブリックIPアドレスと、初期パスワードを確認しておく。
次にRDPで作業できるようにポートを開ける。ハンバーガーメニューからNetworking、Virtual Cloud Networksを選択し、このサーバを配置したVCNを選択。左側のSecurity Listを選択し、デフォルトセキュリティリストを選択する。ここのIngress RulesにRDP用のアクセス許可を追加する。
以上で、基本的な構成は終了である。手元のPCからリモートデスクトップでパブリックIPで接続する。アカウントはopc/初期パスワードで接続できる。
接続できたら、基本的な設定として、OSの言語（日本語の言語パックのダウンロード必要）、リージョンを変更しておく。時刻は変更しようとしたが成功しなかった（UTCのまま）。気にはなるが、検証なので一旦無視して先に進むことにする。
SQL Server 2019のdeveloper版をダウンロードする。以下のSQL Server on-premisesのFree trialを選択すると180日間の評価版がダウンロードできる（氏名、会社、メールアドレス等の入力は必要）。
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
インストーラを起動し、ウィザードに従ってインストールする。はじめにNew SQL Server stand-alone installation ... を選択する。インストールルールチェックで、Firewallが引っかかる。これはWindows FirewallによりSQL Serverへのアクセスのためのポートが開いていないことに起因する。インストールするためだけなら無視しても良いが、後々外からSQL Serverにアクセスする必要があるので、ここでWindows Firewallのポート(デフォルトポートは1433)を開けておくことが望ましい。
WindowsFirewallの設定は以下の通り少し操作が面倒だが、やりたいことはサブネット内のIPからこのマシンへのポート1433へのアクセスを許可するだけである。
上記が完了すると以下のように1433を許可するルールが追加される。
※蛇足ならが、WindowsFirewallの設定を間違えてRDPをブロックしてしまうと、本当にアクセスできなくなってしまうから注意が必要である。実は今回、安易にWindowsファイアウォールを無効化し、通信が通ることを確認後、有効化（設定の復旧）を行ったところ、RDPが通らなくなり、compute instanceから再構築する羽目になった。画面から簡単に操作ができる反面、RDPが絶たれると手も足もでなくなるWindowsの怖さを身をもって経験した。MOSを調べた限り、この状態に陥った場合の復旧方法はあるにはあるらしいが、具体的な手順として誰でも実行できるようなものではない印象だったので、くれぐれもFirewallの設定は注意が必要。
さて、SQL Server 2019のインストーラに戻る。基本的にはデフォルトでどんどんウィザードを進めていけば良い。Engineとクライアントのみインストール、パッチはあてない。変更点だけ以下に記載しておく。
上記でSQL Serverのインストールは完了。続いてinstall SQL Server Management toolsで管理ツールも導入しておく。検証用のデータベースも作成しておく。このあたりは画面操作で知っていれば難しくはないが、私は初めてだったので参考[1]を見ながら作業を進めた。ちなみにこの本はSQL Server 2016について記載されているが、2019インストール上は特に違和感なく作業を進められた。
続いてOracle Clientを導入する。SQL ServerからLinkedServerでOracle Databaseへ接続するには、OracleのOLE DB providerが必要なのだが、これがOracle Clientに含まれているからである。インスタントクライアントとどちらにするか悩んだが、確実な通常のクライアントにすることとした。OracleクライアントはOTNから19cのWindows64bit用を以下からダウンロードする。2つバイナリがあるが通常のインストールが必要なclient.zipの方を使う（homeが付いている方は配置するのみでインストール不要のようだが、パス等の設定を手動でやる必要がある。台数が少なければこちらが無難だろう）。
Oracle Database 19c Client (19.3) for Microsoft Windows x64 (64-bit),WINDOWS.X64_193000_client.zip
https://www.oracle.com/database/technologies/oracle19c-windows-downloads.html
Oracleクライアントはsetup.exeを起動、デフォルトでインストール。OSユーザはoracleユーザを作成する。インストール後、tnsnames.oraに以下を記載し、C:\app\oracle\product\19.0.0\client_1\network\admin配下に配置する。ここではk5というPDBへの接続エントリを追加している。
コマンドプロンプトからtnsping k5で応答あること、sqlplusで接続できることを確認しておく。
上記まででSQL ServerからOracle Databaseへ接続する準備ができた。ここから、SQL ServerでLinkedServerを構成してOracleへ接続してみよう。
SQL Server Management Studioを起動し、saでログインする。Server ObjectsからLinked Serversを右クリックして、New Linked Serverを選択する。
Generalには以下の通り設定する。
Securityはbe made using this security contextを選択し、Oracleの接続ユーザ名/パスワードを指定する。
次にOracle OLE DBのプロパティを変更する。これをしないとLinkedServerでOracleへ接続しようとしたときに以下MSG7399というエラーが発生するため、そのワークアラウンド設定である。Server ObjectsからLinked Servers、Providers、OraOLEDB.Oracleを右クリックでプロパティを開く。Allow inprocessにチェックを入れOKを押下する。
なお、この設定変更についての必要性はよくわかっていない。Googleで調べている際に参考[2]のスレッドを発見して試してみたところうまくいっただけなので、この設定の正しさについて保証できるものではない。
また、直接LinkedServerとは関係ないが、他サーバからSQL Serverに接続するためには、上記Windows Firewallの設定の他、SQL ServerのプロトコルでTCP/IPを有効にする必要がある。後にOracle Gatewayで接続するために必要となるので、ここで変更しておく。スタートメニューからSQL Server 2019構成マネージャを起動し、SQL Serverネットワークの構成からMSSQLSERVERのプロトコルを選択し、TCP/IPを右クリックで有効化する。
Linked Serverの動作確認をしてみよう。以下のSQLを実行し、OracleDatabaseのemp表が表示されることを確認する。もちろん、事前にOracle側にemp表は作成しておく必要があるのはいうまでもない。
select * from openquery([k5],'select * from emp');
なお、下記のようにopenqueryを使わずとも、以下のようにクエリを実行することもできるようだ。この場合、Oracleのスキーマ名、オブジェクト名は大文字で記述しないとエラーとなる。Oracleはsqlplus上は大文字小文字は通常意識する必要はないので違和感があるかもしれない。
select * from K5..F.EMP;
OCI上でSQL Serverを構成し、DBCSで構成されたOracle DatabaseへLinked Serverで接続する構成方法について記載した。Linked Serverの構成方法は難しいものではなく、Oracle Netで接続が確立されていれば、作成自体は簡単にできる。ただ、OLEDBを使う上で一部プロパティを変更しなければならなかった点が注意点である。OCI上で構成する上では必要な通信ができるようにファイアウォールの設定に注意が必要である。特にサーバ上にデフォルトで構成されているポリシーはセキュリティ確保のため厳しく設定してあるため、必要なサーバ間通信を正確に把握して明示的に通す設定を入れる点は構成上のポイントといえるだろう。
今回は以上であるが、次回はDB2で同様のことをやってみよう。
[1] SQL Server 2016の教科書, Chapter 1 SQL Serverの概要とインストール
[2] LinkedServerでMsg 7399の対処方法
https://www.sqlservercentral.com/forums/topic/linked-server-for-oracle-1
#836885: Just to follow up, a call to Microsoft support revealed that editing the Provider Properties (right-click the provider in the providers folder) and ticking the "Allow inprocess" option corrects my problem.
１．VCNの設定
２．DBCSの構築
３．DB設定（初期化パラメータ設定、PDB構築）
４．SQL Serverの構築 ★
５．LinkedServerの構成 ★
６．DB2の構築
７．Federationの構成
８．OracleGWの構築
９．SQL ServerへのDBLINKの構成
１０．DB2へのDBLINKの構成
４．SQL Serverの構成
(1)Windows2019の構築
まずはWindows2019を構築する。構築といっても、基本的にはOCIのメニューのComputeのInstanceからCreate Instanceを選択し、Image and shapeでWindows(Server 2019 Standard)を選択していくだけである。シェイプはVM.Standard2.1(OCPU 1, memory 15GB, Boot volume 256GB)となった。作成は比較的すぐ終わる。作成されたら、Instance Detail画面のInstant AccessでパブリックIPアドレスと、初期パスワードを確認しておく。
次にRDPで作業できるようにポートを開ける。ハンバーガーメニューからNetworking、Virtual Cloud Networksを選択し、このサーバを配置したVCNを選択。左側のSecurity Listを選択し、デフォルトセキュリティリストを選択する。ここのIngress RulesにRDP用のアクセス許可を追加する。
- Source CIDR:0.0.0.0/0
- IP Protocol：TCP
- Source Port range: All
- Destination Port range: 3389
以上で、基本的な構成は終了である。手元のPCからリモートデスクトップでパブリックIPで接続する。アカウントはopc/初期パスワードで接続できる。
接続できたら、基本的な設定として、OSの言語（日本語の言語パックのダウンロード必要）、リージョンを変更しておく。時刻は変更しようとしたが成功しなかった（UTCのまま）。気にはなるが、検証なので一旦無視して先に進むことにする。
(2)SQL Serverの導入
SQL Server 2019のdeveloper版をダウンロードする。以下のSQL Server on-premisesのFree trialを選択すると180日間の評価版がダウンロードできる（氏名、会社、メールアドレス等の入力は必要）。
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
インストーラを起動し、ウィザードに従ってインストールする。はじめにNew SQL Server stand-alone installation ... を選択する。インストールルールチェックで、Firewallが引っかかる。これはWindows FirewallによりSQL Serverへのアクセスのためのポートが開いていないことに起因する。インストールするためだけなら無視しても良いが、後々外からSQL Serverにアクセスする必要があるので、ここでWindows Firewallのポート(デフォルトポートは1433)を開けておくことが望ましい。
WindowsFirewallの設定は以下の通り少し操作が面倒だが、やりたいことはサブネット内のIPからこのマシンへのポート1433へのアクセスを許可するだけである。
- 設定→ネットワークとインターネット→Windowsファイアウォールを選択すると、ファイアウォールとネットワーク保護の画面が出るので、詳細設定を選択する。
- セキュリティが強化されたWindows Defenderファイアウォールの画面の左ペインの「受信の規則」を選択する。
- ここで右側の「新しい規則」を選択し、ポート、TCP、特定のポート(1433)、接続を許可する、この規則はいつ適用しますかはデフォルトのまま（全てチェック）、名前は任意（SQLPort等）とする。
- 作成された規則をダブルクリックし、「スコープ」タブのリモートIPアドレスの部分で「これらのIPアドレス」を選択し、追加ボタンを押下、事前定義されたコンピューターセットでローカルサブネットを選択しOKする。
上記が完了すると以下のように1433を許可するルールが追加される。
※蛇足ならが、WindowsFirewallの設定を間違えてRDPをブロックしてしまうと、本当にアクセスできなくなってしまうから注意が必要である。実は今回、安易にWindowsファイアウォールを無効化し、通信が通ることを確認後、有効化（設定の復旧）を行ったところ、RDPが通らなくなり、compute instanceから再構築する羽目になった。画面から簡単に操作ができる反面、RDPが絶たれると手も足もでなくなるWindowsの怖さを身をもって経験した。MOSを調べた限り、この状態に陥った場合の復旧方法はあるにはあるらしいが、具体的な手順として誰でも実行できるようなものではない印象だったので、くれぐれもFirewallの設定は注意が必要。
さて、SQL Server 2019のインストーラに戻る。基本的にはデフォルトでどんどんウィザードを進めていけば良い。Engineとクライアントのみインストール、パッチはあてない。変更点だけ以下に記載しておく。
- Database Engine Configuration: Server configurationで認証方法を選択する。Windows authentication modeかMixed Mode(SQL server authentication and Windows authentication)を選択できるが、後者を選択し、saのパスワードを入れる。SQL server administratorにAdd Current Userでopcを追加しておく。
- Database Engine Configuration: Memoryでメモリを指定できる。Defaultだと明らかに大きすぎる印象があったので、Recommendedを選択
上記でSQL Serverのインストールは完了。続いてinstall SQL Server Management toolsで管理ツールも導入しておく。検証用のデータベースも作成しておく。このあたりは画面操作で知っていれば難しくはないが、私は初めてだったので参考[1]を見ながら作業を進めた。ちなみにこの本はSQL Server 2016について記載されているが、2019インストール上は特に違和感なく作業を進められた。
(3)Oracle Clientの導入
続いてOracle Clientを導入する。SQL ServerからLinkedServerでOracle Databaseへ接続するには、OracleのOLE DB providerが必要なのだが、これがOracle Clientに含まれているからである。インスタントクライアントとどちらにするか悩んだが、確実な通常のクライアントにすることとした。OracleクライアントはOTNから19cのWindows64bit用を以下からダウンロードする。2つバイナリがあるが通常のインストールが必要なclient.zipの方を使う（homeが付いている方は配置するのみでインストール不要のようだが、パス等の設定を手動でやる必要がある。台数が少なければこちらが無難だろう）。
Oracle Database 19c Client (19.3) for Microsoft Windows x64 (64-bit),WINDOWS.X64_193000_client.zip
https://www.oracle.com/database/technologies/oracle19c-windows-downloads.html
Oracleクライアントはsetup.exeを起動、デフォルトでインストール。OSユーザはoracleユーザを作成する。インストール後、tnsnames.oraに以下を記載し、C:\app\oracle\product\19.0.0\client_1\network\admin配下に配置する。ここではk5というPDBへの接続エントリを追加している。
k5 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.X)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = k5.subnet.vcn.oraclevcn.com)
)
)
コマンドプロンプトからtnsping k5で応答あること、sqlplusで接続できることを確認しておく。
５．LinkedServerの構成
上記まででSQL ServerからOracle Databaseへ接続する準備ができた。ここから、SQL ServerでLinkedServerを構成してOracleへ接続してみよう。
(1)Linked Serverの構成
SQL Server Management Studioを起動し、saでログインする。Server ObjectsからLinked Serversを右クリックして、New Linked Serverを選択する。
Generalには以下の通り設定する。
- Linked Server: Linked Serverの名前（ここではK5を指定）
- Server Type: Other data sourceを選択。Oracle Provider for OLE DBを指定
- Product Name: Oracle
- Data source: tnsnames.oraの接続識別子（ここではk5を指定）
Securityはbe made using this security contextを選択し、Oracleの接続ユーザ名/パスワードを指定する。
(2)Oracle OLE DBのプロパティを変更
次にOracle OLE DBのプロパティを変更する。これをしないとLinkedServerでOracleへ接続しようとしたときに以下MSG7399というエラーが発生するため、そのワークアラウンド設定である。Server ObjectsからLinked Servers、Providers、OraOLEDB.Oracleを右クリックでプロパティを開く。Allow inprocessにチェックを入れOKを押下する。
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "OraOLEDB.Oracle" for linked server "K5" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "K5".Completion time: 2021-09-12T23:53:05.7344726+00:00
なお、この設定変更についての必要性はよくわかっていない。Googleで調べている際に参考[2]のスレッドを発見して試してみたところうまくいっただけなので、この設定の正しさについて保証できるものではない。
(3)SQL ServerのTCP/IPの有効化
また、直接LinkedServerとは関係ないが、他サーバからSQL Serverに接続するためには、上記Windows Firewallの設定の他、SQL ServerのプロトコルでTCP/IPを有効にする必要がある。後にOracle Gatewayで接続するために必要となるので、ここで変更しておく。スタートメニューからSQL Server 2019構成マネージャを起動し、SQL Serverネットワークの構成からMSSQLSERVERのプロトコルを選択し、TCP/IPを右クリックで有効化する。
(4)Linked Serverの動作確認
Linked Serverの動作確認をしてみよう。以下のSQLを実行し、OracleDatabaseのemp表が表示されることを確認する。もちろん、事前にOracle側にemp表は作成しておく必要があるのはいうまでもない。
select * from openquery([k5],'select * from emp');
なお、下記のようにopenqueryを使わずとも、以下のようにクエリを実行することもできるようだ。この場合、Oracleのスキーマ名、オブジェクト名は大文字で記述しないとエラーとなる。Oracleはsqlplus上は大文字小文字は通常意識する必要はないので違和感があるかもしれない。
select * from K5..F.EMP;
まとめ
OCI上でSQL Serverを構成し、DBCSで構成されたOracle DatabaseへLinked Serverで接続する構成方法について記載した。Linked Serverの構成方法は難しいものではなく、Oracle Netで接続が確立されていれば、作成自体は簡単にできる。ただ、OLEDBを使う上で一部プロパティを変更しなければならなかった点が注意点である。OCI上で構成する上では必要な通信ができるようにファイアウォールの設定に注意が必要である。特にサーバ上にデフォルトで構成されているポリシーはセキュリティ確保のため厳しく設定してあるため、必要なサーバ間通信を正確に把握して明示的に通す設定を入れる点は構成上のポイントといえるだろう。
今回は以上であるが、次回はDB2で同様のことをやってみよう。
◇参考
[1] SQL Server 2016の教科書, Chapter 1 SQL Serverの概要とインストール
- 出版社/メーカー: ソシム
- 発売日: 2016/07/26
- メディア: 単行本
[2] LinkedServerでMsg 7399の対処方法
https://www.sqlservercentral.com/forums/topic/linked-server-for-oracle-1
#836885: Just to follow up, a call to Microsoft support revealed that editing the Provider Properties (right-click the provider in the providers folder) and ticking the "Allow inprocess" option corrects my problem.
2021-10-04 09:02
