サーバ名を変えた後、SQL Server のメンテナンスプランが実行エラーになる時の対応

7092 ワード
server SQL Windows 2016 Windows テキストリンク

はじめに

クラウド環境でSQL ServerをインストールしたWindowsサーバをコピーして別サーバとして起動した際に、SQL Serverのサーバ名変更をちゃんと実施したにもかかわらずメンテナンスプランが実行エラーになる事象に出くわしてハマってしまったのでその時の作業メモ。
実施環境はSQL Server 2016。

１．前提作業

SQL Serverの ServerName の変更

SQL Serverで保持しているServerName を新しいサーバ名に変更

確認SQL

SELECT @@SERVERNAME AS 'Server Name';
select * from sys.servers;

設定変更SQL

sp_dropserver '旧サーバー名';
GO
sp_addserver '新サーバー名', local;
GO

リモートログインの削除（使っている場合）

//既定のインスタンスの場合は、次のプロシージャを実行します。
sp_dropremotelogin old_name;
GO  

//名前付きインスタンスの場合は、次のプロシージャを実行します。
sp_dropremotelogin old_name\instancename;
GO

Reporting Servicesの設定変更（使っている場合）

こちらを参考に実施
https://dev.classmethod.jp/articles/sql-server/

２．この状態でメンテナンスプランを実行するとどうなるか

SQL Management Studio からメンテナンスプランを実行したら、下記のエラーにより失敗した。

「The Execute method on the task returned error code 0x80131904 (SQL Server への接続を確立しているときにネットワーク関連またはインスタンス固有のエラーが発生しました。サーバーが見つからないかアクセスできません。インスタンス名が正しいこと、および SQL Server がリモート接続を許可するように構成されていることを確認してください。」


エラーメッセージはジョブの履歴から参照可能。

３．メンテナンスプランの実行が失敗する理由

メンテナンスプランの接続定義を見ると古いサーバー名のままになっており、これが原因で実行に失敗している事がわかる。

４．対処法

方法は２つある。
　　方法１　SQLを使ってメンテナンスプラン定義のサーバ名を置換
　　方法２　メンテナンスプラン定義をExport→修正→Import

方法１　SQLを使ってメンテナンスプラン定義のサーバ名を置換

(1) メンテナンスプランのIDを確認

use msdb
go

SELECT  x.*,
        LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
    SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM dbo.sysssispackages
    WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)
go

(2) 定義のアップデートの実行

use msdb
go

UPDATE dbo.sysssispackages
SET packagedata = CAST(
    CAST(
        REPLACE(
            CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)),'旧サーバー名','新サーバー名'
        ) AS XML
    ) AS VARBINARY(MAX)
)
WHERE id = '手順１で確認したID';
go

方法２　メンテナンスプラン定義をExport→修正→Import

(1) SQL Server Management Studioで Integration Services に接続

(2) 対象のメンテナンスプランを右クリックしてExport


(3) Exportされたファイルをテキストエディタで開きサーバー名を修正

  ||
  DTS:CreatorComputerName="旧サーバー名"
  ||
  <DTS:ConnectionManagers>
        <DTS:ConnectionManager
          DTS:ConnectionString="Data Source=旧サーバー名;Integrated Security=True ・・・・・

(4) Import

５．確認

３に記載の手順でメンテナンスプランの実行先サーバ名が変わっている事を確認した上でメンテナンスプランを実行してみる。正常終了すればOK。


参考URL