SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server でレプリケーションが有効な環境を異なるサーバーにリストアできるかを検証してみる

leave a comment

SQL Server のバックアップをシステムデータベースを含めて、バックアップの取得元と異なるサーバーにリストアする際には、次の情報を参考にする機会が多いのではないでしょうか。

異なるサーバーにバックアップをリストアする際にはこれらの情報を参考にしてデータベースのリストアとリストア後の設定変更を行っていきます。

バックアップの取得元でレプリケーションが有効となっていた場合も、この作業だけでリストアを完了することができるのかを調査してみました。

概念検証として実施したため、T-SQL ベースで設定を強制的に変更している箇所がいくつかありますが、実運用環境で実施する際にはレプリケーションの再設定で検討をしたほうが良いかと思います。

リストアに必要な作業

リストアに必要となる作業を列挙していきたいと思います。

今回の作業環境は次のようになっています。

  • SQL Server のバージョン: SQL Server 2019
  • バックアップ取得元のサーバー名: REPL-2019-01
    • レプリケーションのパブリッシャー / ディストリビューターとして構成されている
  • リストア先のサーバー名: REPL-2019-03
  • コマンドラインの実行: PowerShell
  • ユーザーデータベース: tpch
    • このデータベースをパブリケーションデータベースとして使用している
  • SQL Server のサービスアカウントはドメインユーザーを使用している
    • サービスマスターキーの移行の考慮は不要な状態

 

シングルユーザーモードで起動

master データベースをリストアする際には、シングルユーザーモードで起動をしておく必要があります。また、リストアを実行するセッション以外がシングルユーザーモードで起動している SQL Server に接続をすると、リストアができなくなるため、接続可能なプログラムを sqlcmd に限定しておきます。

これを実現するためには PowerShell で次のコマンドを実行します。

# SQL Server のサービス停止
Stop-Service MSSQLSERVER -Force

# シングルユーザーモードで起動
Set-Location "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn"
.\sqlservr.exe --% -sMSSQLSERVER -mSQLCMD

 

これにより接続可能なプログラムが制限された状態のシングルユーザーモードでリストアすることができます。

 

master データベースのリストア

シングルユーザーモードで起動が完了したら新しい PowerShell のウィンドウを開き、次のコマンドを実行します。

# sqlcmd で接続し、master のバックアップをリストア
sqlcmd.exe --% -Q "RESTORE DATABASE master FROM DISK='<master のバックアップファイル>' WITH REPLACE"

 

これで master のリストアが完了します。

 

SQL Server のサービスの起動

シングルユーザーモードで起動していたインスタンスに対して master データベースのリストアを実施すると、SQL Server のインスタンスが停止した状態となりますので、次のコマンドでサービスを起動します。

# SQL Server のサービス開始
Start-Service MSSQLSERVER
Start-Service SQLSERVERAGENT

 

システムデータベースのリストア

SQL Server のサービスが起動したら、SSMS で接続し、次のクエリでシステムデータベースをリストアします。

-- システムデータベースのリストア
USE [master]
GO
ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [msdb] FROM  DISK = N'<msdb のバックアップファイル>' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [msdb] SET MULTI_USER
GO

RESTORE DATABASE [model] FROM  DISK = N'<model のバックアップファイル>' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
GO

RESTORE DATABASE [distribution] FROM  DISK = N'<distribution のバックアップファイル>' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5, KEEP_REPLICATION
GO

 

ポイントとしては「distribution」のリストアをする際に、「KEEP_REPLICATION」を指定していることでしょうか。

master をリストアすると、distribution のエントリもリストアされます。

後続のレプリケーション設定の初期化を実施するためには、distribution データベースもリストアしておく必要があるのですが、KEEP_REPLICATION を指定しないで distribution データベースをリストアするとシステムデータベースではなく、ユーザーデータベースとしてリストアされてしまうため、オプションを指定してリストアを実施しています。

 

ディストリビューターの強制解除

この後の作業としてはサーバー名の変更を実行したいのですが、現在の状態でサーバー名の変更を実行しようとすると次のようなエラーが発生します。(リモートログインはレプリケーションで使用している「distributor_admin」が該当しています)

メッセージ 15190、レベル 16、状態 1、プロシージャ sp_dropserver、行 56 [バッチ開始行 0]

サーバー ‘REPL-2019-01’ のリモート ログインまたはリンク ログインがまだ存在しています。

メッセージ 20582、レベル 16、状態 1、プロシージャ sp_MSrepl_check_server、行 31 [バッチ開始行 0]

サーバー ‘REPL-2019-01’ を削除できません。レプリケーションでパブリッシャーとして使用されています。

また、レプリケーションを設定しているデータベースを削除しようとすると次のエラーが発生します。

メッセージ 3724、レベル 16、状態 3、行 10

データベース ‘tpch’ を 削除 できません。レプリケーションに使用されています。

これらのエラーを解消するため、次のクエリを実行してディストリビューターとしての登録を強制的に解除します。

-- ディストリビューターの強制解除
EXEC sp_dropdistributor @no_checks = 1,@ignore_distributor = 1
GO

-- 強制解除後にレプリケーションしていたユーザーデータベースが削除できる
DROP DATABASE [tpch]
GO

-- ユーザーデータベース削除後に再度強制解除を再実行し、ディストリビューターの設定を初期化
EXEC sp_dropdistributor @no_checks = 1,@ignore_distributor = 1
GO

 

最初の強制解除を実行すると、ディストリビューターの強制解除は完了しないのですが、ユーザーデータベースが削除できる状態となりますので、最初の強制解除後にユーザーデータベースのエントリの削除を実行しています。

ユーザーデータベースのエントリが削除された後に再度強制解除を実行することでディストリビューターの設定を削除することができます。

 

サーバー名の変更

ディストリビューターの設定の解除が実行出来たら、次のクエリを実行してサーバー名の変更を行います。

-- サーバー名の変更
SELECT @@SERVERNAME
EXEC sp_dropserver'REPL-2019-01'
GO

EXEC sp_addserver 'REPL-2019-03', local;
GO

 

クエリの実行が完了したら SQL Server のサービスを再起動します。

# SQL Server のサービス再起動
Restart-Service MSSQLSERVER -Force

これにより、SQL Server で新しいサーバー名の認識が行われます。

 

ディストリビューションの構成を実行

ここまでの作業が完了したら SSMS でディストリビューションの構成を実行し、ディストリビューターとして有効な状態に再設定します。

image

何回か検証を行うのであれば、ディストリビューションの構成をスクリプト化しておき GUI 操作を省略することも可能です。

 

ユーザーデータベースとdistribution データベースのリストア

レプリケーションの設定はユーザーデータベースと distribution データベースの両データベースに保持されています。

「KEEP＿REPLICATION」オプションを使用してデータベースのリストアを行い、レプリケーションの設定を含めてリストアを行います。

-- ユーザーデータベースとディストリビューションのリストア
USE [master]
GO
RESTORE DATABASE [tpch] FROM  DISK = N'<ユーザーデータベースのバックアップファイル>' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5, KEEP_REPLICATION
GO

ALTER DATABASE [distribution] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [distribution] FROM  DISK = N'<distribution のバックアップファイル>' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5, KEEP_REPLICATION
ALTER DATABASE [distribution] SET MULTI_USER
GO

 

これでパブリッシャー / サブスクライバー / ディストリビューターの設定がリストアされた状態となります。

 

ユーザーデータベースの所有者の設定

レプリケーションはデータベースの所有者が設定されていないと動作しない機能となります。

そのため、所有者の SID で適切なログインの解決ができていないデータベースがあった場合、所有者として「sa」を設定するように次のクエリを実行して補正を行います。

-- DB 所有者の変更
USE [master]
GO
DECLARE @sql nvarchar(max)
DECLARE @t TABLE(database_name sysname, owner varchar(100))


INSERT INTO @t 
EXEC sp_MSforeachdb 'USE [?];select ''?'' AS database_name, suser_sname((select sid from sys.database_principals where name = N''dbo''));'

DECLARE alter_cursor CURSOR FOR 
SELECT 'ALTER AUTHORIZATION ON DATABASE::' + database_name + ' TO sa' AS alter_stmt  FROM @t WHERE owner IS NULL

OPEN alter_cursor

FETCH NEXT FROM alter_cursor INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN
	EXECUTE (@sql)
	FETCH NEXT FROM alter_cursor INTO @sql
END
CLOSE alter_cursor
DEALLOCATE alter_cursor

 

distribution のサーバー情報の変更

distribution データベース内のレプリケーション設定に関するテーブルには、サーバー名が含まれている情報があります。

今回は「REPL-2019-01 → REPL-2019-03」にサーバーが変更されていますので、関連する情報のサーバー名も変更します。

-- DB 内のサーバー情報の変更
USE [distribution]
GO
UPDATE MSsubscriber_info SET publisher= 'REPL-2019-03' WHERE  publisher = 'REPL-2019-01'
UPDATE MSpublication_access SET sid = (SELECT sid FROM sys.server_principals WHERE name = 'distributor_admin') WHERE login = 'distributor_admin' 
UPDATE MSreplication_monitordata SET publisher = 'REPL-2019-03' WHERE  publisher = 'REPL-2019-01'
UPDATE MSsubscriber_schedule SET publisher = 'REPL-2019-03' WHERE publisher = 'REPL-2019-01'
UPDATE MSreplservers SET srvname = 'REPL-2019-03' WHERE srvname = 'REPL-2019-01'
GO

 

レプリケーションのジョブのサーバー情報の変更

レプリケーションのプロセスの起動は SQL Server エージェントのジョブで制御が行われています。

ジョブの情報内には、サーバ名の情報も含まれていますので、distribution と同様に関連する情報を書き換えます。データベースと同様に、ジョブも所有者の設定がありますので、解決できない SID が設定されているジョブについては sa を所有者として設定します。

-- レプリケーションのジョブのサーバー情報の変更
USE [msdb]
GO
UPDATE sysjobsteps SET server = 'REPL-2019-03' WHERE server = 'REPL-2019-01'
GO

-- レプリケーション系のジョブのステップ内のサーバー名の変更
UPDATE dbo.sysjobsteps SET command =  REPLACE(command, 'REPL-2019-01', 'REPL-2019-03') WHERE command LIKE '%REPL-2019-01%'
GO

-- ジョブの所有者の変更
USE [msdb]
GO
UPDATE dbo.sysjobs SET owner_sid = 0x01 WHERE SUSER_NAME(owner_sid) IS NULL
GO

 

SQL Server エージェントの再起動

最後に、レプリケーションに関してのジョブを再起動するため、SQL Server エージェントのサービスを再起動します。

# エージェントの再起動
Restart-Service SQLSERVERAGENT -Force

 

 

以上で作業は完了となります。

「DISTRIB.exe」「logread.exe」が起動しており、レプリケーションモニターでエラーが発生していなければ設定が期待通り移行できているはずです。

image

 

まとめ

本投稿では、レプリケーションが有効な環境のデータベースバックアップを異なるサーバーにリストアできるかを検証してみました。

技術的には既存のレプリケーションの設定を活かしながらリストアを進めていくことができましたが、本投稿の手順は、UI として提供がされていない「distribution」「msdb」内のサーバー情報を強制的に書き換えています。

この手順は運用環境での実行が推奨されるものではなく、何か問題が発生した場合の保証ができるものではありません。

レプリケーションの設定はスクリプト化することができ、レプリケーションの再設定にはスクリプトを再実行することで設定を復元することが望ましいと思います。

そのため、実運用環境ではレプリケーション設定のスクリプト化を行い、コード管理の中でレプリケーションの設定のバックアップをしておくことを検討するのが良いのではないでしょうか。

Share

Written by Masayuki.Ozawa

2月 4th, 2025 at 9:59 pm

Posted in SQL Server

Tagged with

«
»

Leave a Reply