アーカイブ

【SQL Server】コンピュータ名を変更しているとクエリがエラーになる場合がある【リンクサーバー】

SQL Serverに関する小ネタ。ホストするコンピュータ名を変更する場合とリンクサーバーにまつわる話。

目次
  1. SQL Serverのメタデータにはコンピュータ名の情報があり、コンピュータ名変更時に自動で更新されない
  2. 発生する問題
  3. 理由
  4. 解決策
    1. 既定のインスタンスの場合
    2. 名前付きインスタンスの場合
    3. 実行例
  5. 参考
  6. おわりに

SQL Serverのメタデータにはコンピュータ名の情報があり、コンピュータ名変更時に自動で更新されない

あまりないケースかもしれませんが、SQL Serverをホストするコンピュータのコンピュータ名を変更する場合、みなさんなにか意識されていますでしょうか?

コンピュータ名を変更しても、いきなりDBにアクセスできなくなるといったケースはほとんどなく、普通に使えるので意識してない方も多いのではないかと思います。変更後のコンピュータ名でリモート接続なども普通にできますしね。

しかし実は、SQL Serverのメタデータ内にはコンピュータ名の情報を持っていて、コンピュータ名の変更時に自動で変更はされません。なので手動で変更が必要な場合があるみたいなんです。

発生する問題

例えば、SQL Serverインストール時のコンピュータ名が「PREVNAME」で、コンピュータ名を「AFTERNAME」に変更している場合、以下のようなクエリの発行がエラーとなります。

Ex.)データベース”testDB”のテーブル”table_a”を全行全列SELECT

SELECT * FROM [AFTERNAME\TEST].[testDB].[dbo].[table_a]

テーブル名の前に「サーバー名\インスタンス」の情報を付加したクエリですね。

あまり見慣れないかもしれませんが…リンクサーバーを使っていると目にする構文かもしれません。

「sys.servers でサーバー ‘AFTERNAME\TEST’ が見つかりませんでした。正しいサーバー名を指定したかどうかを確認してください。必要であればストアド プロシージャ sp_addlinkedserver を実行し、サーバーを sys.servers に追加してください。」

理由

SQL Serverには「リンクサーバー」という機能があります。

ざっくりいうと、あるSQL Serverにアクセスしている際にリンクサーバーとして設定している他のDBサーバー(インスタンス)に対してもアクセスが可能になる機能です。
※にわかなので、ご容赦を…。詳しくは以下をご参照ください。
リンク サーバー (データベース エンジン) – SQL Server

何がいいかというと例えば、異なるDBサーバー(インスタンス)のテーブル同士を結合したクエリ発行できたりします。
※Aサーバーに接続時に、AサーバーのBテーブルとリンクサーバーに設定したCサーバーのDテーブルを結合してSELECT、みたいな。

そんなリンクサーバー機能ですが、クエリを発行する際にどのサーバー(インスタンス)のテーブルかを識別するために、テーブル名の前に「サーバー名\インスタンス名」を付加するわけです。

↓こんな感じのクエリになります。

SELECT
 *
FROM
 [AServer\INSTANCE].[DatabaseA].[dbo].[TableA] A
LEFT OUTER JOIN
 [BServer\INSTANCE].[DatabaseB].[dbo].[TableB] B
 ON B.KeyColumn = A.BKey

この際クエリに付加する「サーバー名\インスタンス」の部分ですが、どうやらSQL Serverに保持しているメタデータをベースに動作しているようです。

メタデータは、以下のクエリで確認することができます

SELECT * FROM sys.servers

上記にはリンクサーバーを設定すると設定したリンクサーバーのレコードが追加されます。
また、既定で]ローカルサーバーの情報が存在します(server_id=0 がローカルサーバーのレコード)。[/HIGHLIGHT
※画像の例ですと、リンクサーバーの設定なしなのでローカルサーバーのレコードのみです。

サーバー名を付与したクエリを実行する際、こちらの情報と照合するようです。

そしてこちらのメタデータは、[HIGHLIGHT]コンピュータ名変更時に自動では更新されません。
そのため、クエリを変更後のコンピュータ名で記述しているのに対して、このメタデータが古いとエラーになってしまうというわけですね。

反対に、クエリの記述が実際のコンピュータ名と一致していなくても、SQL Serverのメタデータ情報に合致していればクエリは成功します。(意図的に両者が異なる場合って、あるのかな・・・?)

まあ、自サーバー(インスタンス)のテーブルへのアクセスする際は、もちろんサーバー名\インスタンス名を付与する必要はないので、そのようにシステムを組んでしまっている場合だけなのですが…。
(なんと仕事で実際に遭遇したのであった・・・)

解決策

システムやアプリ側の実装が悪いのでは?という話もありそうですが…とりあえずメタデータを更新する方法をご紹介。

以下のプロシージャを実行することで、SQLServer内のメタデータの情報を変更することができます。

反映には、実行後インスタンスの再起動が必要になります。
※サービスを再起動すればOK

既定のインスタンスの場合

EXEC sp_dropserver '<old_name>';
GO
EXEC sp_addserver '<new_name>', local;
GO

名前付きインスタンスの場合

EXEC sp_dropserver '<old_name\instancename>';
GO
EXEC sp_addserver '<new_name\instancename>', local;
GO
プロシージャの実行に失敗する場合
他コンピュータから接続中だとエラーになり実行できない場合があるようです。

その場合はsp_dropremoteloginプロシージャでリモート接続を削除します。
※もちろん、使用者に確認したあとで…

実行例

実行前

○メタデータ

○クエリ

現在のコンピュータ名「AFETRNAME」でクエリを発行しても、メタデータに存在しないのでエラーとなります。

実行後

○メタデータ

コンピュータ名に関する列の値が書き換わっていますね。
※「name」列/「data_source」列

○クエリ

エラーなく実行結果が返るようになりました(サービス再起動後)。

参考

SQL Server のスタンドアロン インスタンスをホストするコンピューターの名前を変更する – SQL Server

sys.servers (Transact-SQL) – SQL Server

リンク サーバー (データベース エンジン) – SQL Server

おわりに

環境依存で起こる問題って、やーね。

(我ながらPrevに対してAfterて…なんだ;)

プロフィール
筆者:UMAイカ

IT企業に勤務しています。
当ブログは商品レビューや生活の知恵、プログラミング、PCTipsなどについてお役立ち情報を発信します。趣味などの雑記も少し。
【マイブーム】:ウイスキー/自炊
【最近のひとこと】:転職したい・・・

- SNS -
アーカイブ