Alerting automatically tells a team when an error needs investigation or corrective action. The mistake is waiting for someone to inspect logs or for users to notice incorrect data: failures can be missed, while manual checks consume time and remain vulnerable to human error. Data-tier applications need the same proactive attention given to network and operating-system failures. section 7.2
Build alerts around a visible failure signal, then route that signal to someone who can act:
- Read custom logging tables or SSISDB logs at regular intervals with an observability check; alert when a logged error appears. section 7.2
- Use SQL Server Agent job history or step logs when the failure signal is held there rather than in an application log. section 7.2
- Use SQL Server’s built-in alerts and notifications when enterprise observability software is unavailable. section 7.2
- Do not rely solely on the SQL Server error log for application failures, because error severity and raising method can leave errors unlogged there. section 7.2
Alert on a job failure
For a SQL Server Agent job named Populate_Fact_Tables, retrieve full job history before designing the check. This helps identify the recorded failure information that an alerting process must inspect rather than assuming the error log contains it. section 7.2
EXEC msdb.dbo.sp_help_jobhistory
@job_name = 'Populate_Fact_Tables'
, @mode = 'FULL' ;
Retrieve the full history for Populate_Fact_Tables to inspect job execution details used by an alert check. section 7.2
Treat xp_cmdshell enablement as a P1 event
Security alerting can detect an attempted configuration change before it becomes a larger incident. An instance-level DDL trigger can inspect ALTER_INSTANCE commands, identify an xp_cmdshell-related sp_configure command, raise custom error 50001 with logging, and roll back the transaction. Monitor the event log for that error and respond quickly; each added security layer takes time and increases the chance of detection. section 14.4
Listing 14.5 Creating a trigger to prevent xp_cmdshell execution
EXEC sp_addmessage 50001, 16, 'Attempt to disable xp_cmdshell' ;
GO
CREATE TRIGGER prevent_xp_cmdshell
ON ALL SERVER
FOR ALTER_INSTANCE
AS
BEGIN
DECLARE @Statement NVARCHAR(4000) ;
SET @Statement = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(4000)')) ;
IF (CHARINDEX('sp_configure', @Statement) > 0)
AND (CHARINDEX('xp_cmdshell', @Statement) > 0)
BEGIN
RAISERROR(50001, 16, 1, 'Attempt to disable xp_cmdshell') WITH LOG ;
ROLLBACK ;
END
END ;
GO
Create an instance-level trigger that logs and blocks an xp_cmdshell configuration attempt, producing an error that monitoring can alert on. section 14.4
Rule: alert on actionable errors, choose a signal that is actually recorded, and ensure the notification reaches an operator or monitoring team able to investigate it. section 7.2 section 14.4
