如何将Windows故障转移集群日志插入到SQLServer的表中

1. 在相关的数据库中增加存储过程：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_DBA_InsertEventLog]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].Proc_DBA_InsertEventLog
GO
-- =============================================
-- Author:		yenange
-- Create date: 2016-03-30
-- Description:	插入记录到windows日志监控表
-- =============================================
CREATE proc [dbo].Proc_DBA_InsertEventLog
	@index INT,
	@entryType NVARCHAR(50),
	@timeGenerated DATETIME,
	@machineName NVARCHAR(50),
	@source NVARCHAR(100),
	@eventID INT,
	@instanceId BIGINT,
	@userName NVARCHAR(50),
	@message NVARCHAR(MAX)
as 
BEGIN
	SET NOCOUNT ON
	--1. 如果 windows 日志监控表不存在则增加
	IF OBJECT_ID('dbo.DBA_EventLog','U') IS NULL
	BEGIN
		CREATE TABLE dbo.DBA_EventLog(
			id INT IDENTITY(1,1) NOT NULL,
			[Index] INT NOT NULL,
			[EntryType] NVARCHAR(50),
			[TimeGenerated] DATETIME NOT NULL,
			[MachineName] NVARCHAR(50) NOT NULL,
			[Source] NVARCHAR(100),
			[EventID] INT NOT NULL,
			[InstanceId] BIGINT NOT NULL,
			[UserName] NVARCHAR(50),
			[Message] NVARCHAR(MAX),
			[checked] BIT NOT NULL DEFAULT(0)
			CONSTRAINT PK_DBA_EventLog PRIMARY KEY(
				id	ASC
			)
		)
	END
	--2. 插入记录
	IF NOT EXISTS (SELECT * FROM dbo.DBA_EventLog WHERE [Index]=@index AND [TimeGenerated] =@timeGenerated AND [EventID]=@eventID AND [InstanceId]=@instanceId )
	BEGIN
		INSERT INTO [dbo].[DBA_EventLog]
			   ([Index]
			   ,[EntryType]
			   ,[TimeGenerated]
			   ,[MachineName]
			   ,[Source]
			   ,[EventID]
			   ,[InstanceId]
			   ,[UserName]
			   ,[Message])
		 VALUES
			   (@index
			   ,@entryType
			   ,@timeGenerated
			   ,@machineName
			   ,@source
			   ,@eventID
			   ,@instanceId
			   ,@userName
			   ,@message)
			   
	END
	
	--8. 如果是凌晨，删除早期记录, 并重建索引
	IF DATEPART(hour, GETDATE())=0
	BEGIN
		DELETE FROM dbo.[DBA_EventLog] WHERE [TimeGenerated]>dateadd(day,-5,getdate())
		ALTER INDEX PK_DBA_EventLog ON dbo.[DBA_EventLog] REBUILD
	END
END
GO
EXEC sys.sp_addextendedproperty
	  @name=N'Version', @value=N'1.0'
	, @level0type=N'SCHEMA',@level0name=N'dbo'
	, @level1type=N'PROCEDURE',@level1name=N'Proc_DBA_InsertEventLog'

2. 新建作业， 注意作业脚本的类型为 PowerShell. 脚本如下：

#定义db相关变量, 这个是在本地Windows验证, 注意替换dbName
$dbLink="(local)"
$dbSql=""
$dbName="monitor_dbName"

Get-EventLog -LogName system -EntryType error -Source 'Microsoft-Windows-FailoverClustering','Service Control Manager' -newest 20|foreach-object{
	$dbSql="EXEC dbo.Proc_DBA_InsertEventLog '"+$_.Index+"','"+$_.EntryType+"','"+$_.TimeGenerated+"','"+$_.MachineName+"','"+$_.Source+"','"+$_.EventID+"','"+$_.InstanceId+"','"+$_.UserName+"','"+$_.Message+"'"
	sqlcmd -S $dblink -d $dbname -Q $dbsql
}

做好这些， 就可以定时将WSFC的日志插入到表中了。 至于预警， 你可以定期访问这个表或者做一个insert触发器都可以发预警邮件的了。