SQLServerExpressでCRONのような定期処理の仕組みを作りたい

目的

SQLServerは無料版のExpressがあって使いやすいですね。
ただ、Express版ではクライアントからの呼び出しではない、定期処理をする仕組みがありません。
それを、タスクスケジューラで代用するわけですが、タスクスケジューラにどのスクリプトを実行するなどの詳細な情報を与えるのはメンテナンス的に大変だと思ったので、LinuxのCRONの一か所で集約的に指定するように考えました。
（すみません、人間が古いので例えが古いです。）

方法

• Windows側
  Windowsでは、定期的にSQLServerの特定のストアドプロシジャを実行してもらうようにします。使用する技術は、次の通りです。
  　　

  • タスクスケジューラ
    Windows標準のタスク実行機能です。
    登録は１つだけにして、SQLServerの中で対象を切り替えて実行します。
    　　
  • SQLCMDコマンド
    SQLServer Management Studio（以下、SSMS）のインストール時にインストールされるコマンドで、コマンドラインからSQLServerにアクセスできるので便利です。
    　

• SQLServer側

  • データベースmaster
    データベースmasterは標準で作成されるシステム用のデータベースですが、使って悪いことはないはずなので、使わせてもらいます。
    　
  • Execute コマンド
    SQLクエリのExecuteコマンドは次のような書式でテキストを直接実行してくれます。

DECLARE @command nvarchar(100) = 'select @@servername';

EXECUTE (@command);
GO

これらの機能を使って、タスクスケジューラから定期的にsqlcmdコマンドを実行し、データベースmasterのテーブルに登録した文字列をexecuteで直接実行してもらうことで、CRON的に運用できるようになります。

構築手順

1. テーブルの作成

次のスクリプトを実行して、コマンドを登録するテーブルS_tasksとコマンド実行のログを保存するテーブルS_log_taskを作成します。

-- データベースmasterを使う
use [master]
GO

-- 登録用テーブル 時刻はdatetimeoffsetとして保存
CREATE TABLE [dbo].[S_tasks]
(
	  [id] INT NOT NULL IDENTITY(1,1) -- 連番
	, [title] NVARCHAR(50) COLLATE Japanese_CI_AS NOT NULL DEFAULT('') -- 見出し
	, [description] NVARCHAR(400) COLLATE Japanese_CI_AS NULL -- 説明
	, [command] NVARCHAR(2000) COLLATE Japanese_CI_AS NULL -- コマンド
	, [interval_minutes] SMALLINT NOT NULL DEFAULT((0)) -- 実行の間隔（分）実際は１時間単位
	, [limit_count] INT NULL -- 最大繰り返し回数
	, [start_time] DATETIMEOFFSET(7) NOT NULL DEFAULT(sysdatetimeoffset()) -- 開始時刻
	, [finish_time] DATETIMEOFFSET(7) NULL -- 終了時刻
	, [executed_count] INT NOT NULL DEFAULT((0)) -- 実行した回数
	, [last_executed_time] DATETIMEOFFSET(7) NULL -- 前回実行した時刻
	, [next_execute_time] AS (dateadd(minute,[interval_minutes],isnull([last_executed_time],[start_time]))) -- 次回実行予定の時刻（計算列なので自動で計算する）
	, [enable] BIT NOT NULL DEFAULT((0)) -- 有効か否かのフラグ
	, CONSTRAINT [PK_S_tasks] PRIMARY KEY ([id] ASC)
);

CREATE TABLE [dbo].[S_log_task]
(
	  [executed_time] DATETIMEOFFSET(7) NOT NULL DEFAULT(sysdatetimeoffset()) -- 実行時刻
	, [target_id] INT NOT NULL -- 実行したタスクの連番
	, [target_title] NVARCHAR(50) COLLATE Japanese_CI_AS NOT NULL -- 実行したタスクの見出し
	, [target_command] NVARCHAR(2000) COLLATE Japanese_CI_AS NOT NULL -- 実行したタスクのコマンド
	, [is_success] BIT NULL -- 成功したか否かのフラグ
	, CONSTRAINT [PK_S_log_task] PRIMARY KEY ([executed_time] ASC, [target_id] ASC)
);
GO

２．ストアドプロシジャの登録

タスクスケジューラから呼び出されるストアドプロシジャを登録します。
次のスクリプトを実行してください。

USE [master]
GO
IF OBJECT_ID('KickBatchScript') IS NOT NULL DROP PROCEDURE dbo.KickBatchScript
GO
-- =============================================
-- Author:		小島情報研究所
-- Create date: 2023/11/20
-- Description:	タイマーで定期的に実行するストアド（登録した処理を実行する）
-- =============================================
CREATE PROCEDURE [dbo].[KickBatchScript] 
AS
BEGIN TRY
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	BEGIN TRANSACTION kickbatch;

    -- Insert statements for procedure here

    -- このストアドが実行されたことをログに記録する
	INSERT INTO dbo.S_log_task
	(
		target_id
		,target_title
		,target_command
		,is_success
	) 
	SELECT 
		-1 AS target_id
		,'KickBatchScript start' AS target_title
		,'(none)' AS target_command
		,1 AS is_success
	;

    -- カーソルで順次処理をするための準備
    DECLARE @now datetimeoffset = SYSDATETIMEOFFSET();
    DECLARE @currentId int;
	DECLARE @currentTitle nvarchar(400);
	DECLARE @currentCommand nvarchar(2000);

    -- カーソルの設定
    DECLARE myCursor CURSOR FOR
	SELECT A.id, A.title, A.command FROM dbo.S_tasks A
	WHERE A.enable=1
    	AND A.executed_count <= isnull(A.limit_count,A.executed_count)
    	AND A.next_execute_time <= isnull(A.finish_time,A.next_execute_time)
    	AND A.start_time <= @now
    	AND A.next_execute_time <= @now
	ORDER BY A.id;

	OPEN myCursor;

	FETCH NEXT FROM myCursor
	INTO @currentId, @currentTitle, @currentCommand;

	WHILE @@FETCH_STATUS = 0
	BEGIN

        -- コマンドの実行
		EXEC (@currentCommand);

        -- ログに保存
		INSERT INTO dbo.S_log_task
		(
			target_id
			,target_title
			,target_command
			,is_success
		) 
		SELECT 
			@currentId AS target_id
			,@currentTitle AS target_title
			,@currentCommand AS target_command
			,1 AS is_success
		;

        -- 実行回数や最終実行時刻の更新
        UPDATE A SET
			executed_count=A.executed_count+1
			,last_executed_time=SYSDATETIMEOFFSET()
		FROM dbo.S_tasks A
		WHERE A.id=@currentId;

		FETCH NEXT FROM myCursor
		INTO @currentId, @currentTitle, @currentCommand;
	END

	CLOSE myCursor;
	DEALLOCATE myCursor;

	COMMIT TRANSACTION kickbatch;
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION kickbatch;
END CATCH
GO

３．テストスクリプトの実行

テーブルとストアドプロシジャがきちんと登録されていることを確認するためにテストスクリプトを実行します。
次のスクリプトを実行してください。

use [master]
GO
-- 確認用のタスクを登録
insert into dbo.S_tasks
(title, description, command, interval_minutes, enable)
values
('test', '特に何もしないタスク', 'select @@servername', 60, 1);

-- 動作確認のためのコマンド
exec dbo.KickBatchScript;

GO

結果：

(列名なし)
(サーバ名)\(インスタンス名)

エラーがなく結果が出れば成功です。

４．SQLCMDのテスト

SQLCMDが問題なく実行できることを確認します。
SQLCMDのインストールについては、ここでは省略します。
またログインの認証はWindows認証でログイン可能な状態であることを前提とします。
Windowsのコマンドプロンプトから次のスクリプトを実行してください。

> SQLCMD.exe -S localhost\\(インスタンス名) -d master -Q "EXECUTE dbo.KickBatchScript"

--------------------------------------------------------------------------------
------------------------------------------------
(サーバ名)\（インスタンス名）

エラーがなく結果が出れば成功です。
うまくいかない場合は、SQLServerへのログインアカウントの情報を追加してください。

５．タスクスケジューラへの登録

Windows標準のschtasksコマンドでスケジュールを登録します。
Windowsのコマンドプロンプトから次のスクリプトを実行してください。
タスクの登録にはAdministrator権限が必要なので、「管理者として実行」などの方法で行ってください。

rem タスクをシステムに登録する
schtasks /create /sc hourly /tn SQLSvTimer /tr "sqlcmd.exe -S localhost\KOILDB -d master -Q 'exec KickBatchScript' " /mo 1

６．確認

数時間放置して、S_log_taskテーブルにデータが貯まることやS_tasksテーブルのexecuted_countやlast_executed_time、next_execute_timeが変化することを確認してください。

７．運用方法

次のような形でS_tasksテーブルに追加してください。

insert into dbo.S_tasks
(title, description, command, interval_minutes, enable)
values
('test2', '他のデータベースにアクセスするタスク'
,'insert into testclr.dbo.Test_TableUsers
(
    [UserName]
    ,[Password]
    ,[AddTime]
    ,[namedecimal]
    ,[namenumeric]
    ,[namefloat]
    ,[namereal]
) values
(''name'',''pw'',getdate(),0,0,0,0)
;', 60, 1)
,('test3', '他のデータベースにアクセスするタスク2'
,'exec dbo.Macro;', 60, 1)
;
GO

注意点としては、そのデータベースにアクセスする権限が不足していると失敗します。

おわり

これだけ偉そうに記事を書いていますが、実動時間はまだ６時間程度でテスト稼働といえる段階なので何か間違いがあるかもしれません。
問題点などがありましたらご指摘いただけると幸いです。