概要
Postgresを主DBとして使用している環境で、
都合によってSQLServer経由でPostgresに接続したくなった。
以下のような接続である
Client -> SQL Server -> Postgres
これを SQLServerの Linked Server を使って実現した
直接関連しないものもあるが、構築した環境のその他情報
- PostgresはRDS Aurora を利用
- 外部からPostgresに接続するには踏み台(Linux)ServerへのSSHトンネルが必要
- SQLServerはexpress edition
- SQLServerは任意の場所にあって、クライアントから接続できる
設定手順
事前準備
事前に SQLServerを配置するインスタンスに SQLServer と SSMS をインストールして使えるようにしておく。
Postgres ODBC Driverのインストール
流れにそってSQLServerを配置するインスタンスにインストールしておく
SQLServerにLinked Server設定
SSMS でクエリエディタを開き、以下の例のように記述し、実行
※中でpostgresへの接続設定があるので書き換えてください
※今回はsshトンネル経由の例なので、postgres serverがlocalhostやらport12345やらになってます
DECLARE @name NVARCHAR(4000);
DECLARE @provider NVARCHAR(4000);
DECLARE @provstr NVARCHAR(4000);
DECLARE @db_name NVARCHAR(4000);
-- destination postgres database
SET @name = N'test'; -- リンクServerの名前。任意につける。後にselectとかする時に必要
SET @provider = N'MSDASQL';
SET @provstr = 'Driver={PostgreSQL UNICODE};Server=localhost;Port=12345;Database=test_db;UID=test_user;Password=test_user_password;Network=dbmssocn;'
SET @db_name = N'test_db'; -- postgres db name
-- create linked server
EXEC MASTER.dbo.sp_addlinkedserver @server = @name
,@srvproduct = N'PostgreSQL'
,@provider = @provider
,@provstr = @provstr
,@catalog = @db_name
-- set up Extended properties of the Linked Server
EXEC MASTER.dbo.sp_serveroption @server = @name
,@optname = 'data access'
,@optvalue = 'true'
EXEC MASTER.dbo.sp_serveroption @server = @name
,@optname = 'use remote collation'
,@optvalue = 'true'
EXEC MASTER.dbo.sp_serveroption @server = @name
,@optname = 'rpc'
,@optvalue = 'true'
EXEC MASTER.dbo.sp_serveroption @server = @name
,@optname = 'rpc out'
,@optvalue = 'true'
GO
うまく設定できれば以下のように SSMS からテーブル情報等を見ることができる
SQLServerからPostgres Tableへクエリの発行
SQLServerから普通にselectを発行すると、SQLServerだけに参照ロックがかかる。
これは嫌。
T-SQL の OPENQUERY を使ってSQLを発行すると参照ロックがかからないらしい。
select * from OPENQUERY(
hoge, -- link server name
'select * from xxxxxxxxx'
)