はじめに
負荷分散のためにスケールアウト可能な構成としているケースや、開発が並行で走っているケースといったような、複数台のSQL Serverを運用しているケースではDBの確認や修正のために各サーバーに同じクエリを流したいことがあります。
本記事ではSSMS(SQL Server Management Studio) からどうすれば各サーバーに同じクエリを流せるのか、3種類の方法を紹介します。
- 使用した環境
- SSMS(SQL Server Management Studio) :18.8
- Database: SQL Database
- データはAdventureWorksを使用
方法1: サーバーグループを使用する方法
ローカルサーバーグループあるいは中央管理サーバーグループを作成してサーバーを登録し、クエリを実行することで登録したサーバー全てに同じクエリを実行できます。
ローカルサーバーグループと中央管理サーバーグループの使い分けは主には個人で使用するか複数人かであり、情報の保存先が異なります。
また、中央管理サーバーグループはWindows認証を使用してのみ登録が可能です。
https://docs.microsoft.com/en-us/sql/ssms/register-servers/create-a-new-registered-server-sql-server-management-studio?view=sql-server-ver15
以下詳細手順となります。
-
SSMSの登録済みサーバーを表示します
-
ローカルサーバーグループor中央管理サーバーグループにサーバーを登録します
サーバーグループを作成するとフォルダ分けのようにまとめることができます
サーバー登録画面でサーバー名や認証情報などを入力します
-
作成が完了すると次のようになります。サーバーグループ側を選択して新しいクエリを実行するとクエリエディタが開き、接続数が下のバーに表示されます。
-
このクエリエディタでクエリを書いて実行すると各サーバーに対して実行されます。
SELECTクエリを書いて実行したのが下記図であり、１列目にサーバー名が表示され、2列目以降にSELECTした情報が表示されています。
方法2: リンクサーバーを使用する方法
リンクサーバーを使用すると、SELECT * FROM [サーバー名].[データベース名].[スキーマ名].[テーブル名] のようにサーバーを指定してクエリを実行することができるようになります。これを利用してサーバー名を変えながらループさせれば各サーバーに同じクエリを実行できます。
以下詳細手順となります。
- リンクサーバーは「サーバーオブジェクト」の「リンクサーバー」から追加することが可能です。
2. 設定完了後サーバー名を指定してクエリが実行可能となります
3. リンクサーバー名の一覧を sys.servers から(あるいはsp_linkedserversから)取得して、サーバー名を変更しながらループ処理を行うことで同じクエリを各サーバーに流すことが可能となります。
DECLARE @LinkedServerName varchar(100);
-- リンクサーバーの一覧を取得してカーソル定義
DECLARE CurServer CURSOR FOR SELECT name FROM sys.servers WHERE is_linked = 1;
OPEN CurServer;
FETCH NEXT FROM CurServer INTO @LinkedServerName;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @LinkedServerName;
EXEC('SELECT * FROM [' + @LinkedServerName + '].[AdventureWorksLT].[SalesLT].[Address]');
FETCH NEXT FROM CurServer INTO @LinkedServerName;
END
CLOSE CurServer;
DEALLOCATE CurServer;
方法3: SQLCMDを使用してサーバーを切り替える方法
SSMSのメニューからクエリ-> SQLCMDモードを選択するとクエリエディタでSQLCMDの変数やコマンドが使用できるようになります。
SQLCMDには接続先サーバーを変更するコマンド :Connect があるのでそちらを使用すれば１つのSQLの中で接続先を切り替えることが可能です。
sqlcmd コマンド
実際に書いてみたのが下記のSQLです。
SQLCMD変数ExecuteSQL に実行するSQLを変数として持ち、サーバー1, サーバー2, サーバー3に対して接続してそのSQLを実行する構造となっています。
-- 各サーバーで同じUserName, Passwordを使用しているので一時変数定義
:Setvar UserName {ユーザーネーム}
:Setvar Password {パスワード}
-- 各サーバーで実行するSQL
-- Connectでは接続するDBを選択できないことと、サンプル用に用意したDBがSQL Databaseであり、
-- USE句でDBを切り替えられなかったためmasterからアクセス可能な情報のみをSELECTするサンプルとしています
:Setvar ExecuteSQL "SELECT @@ServerName as serverName, name, create_date FROM sys.databases"
:Setvar ServerName {サーバー名1}
:Connect $(ServerName) -U $(UserName) -P $(Password)
EXECUTE ('$(ExecuteSQL)')
GO
:Setvar ServerName {サーバー名2}
:Connect $(ServerName) -U $(UserName) -P $(Password)
EXECUTE ('$(ExecuteSQL)')
GO
:Setvar ServerName {サーバー名3}
:Connect $(ServerName) -U $(UserName) -P $(Password)
EXECUTE ('$(ExecuteSQL)')
GO
実際にユーザー名やサーバー名などを入力して実行してみたのが下記画像であり、各サーバーでいつDBが作成されたかという情報が取得できています。
