-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathConfigureDbaAlerts.sql
More file actions
278 lines (232 loc) · 11.3 KB
/
ConfigureDbaAlerts.sql
File metadata and controls
278 lines (232 loc) · 11.3 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
/*
Script: ConfigureDbaAlerts.sql
Description: Configures Database Mail on a SQL Server instance for DBA alerting.
Enables Database Mail XPs, creates a mail account and profile,
sets it as the default public profile, creates a DBA operator,
and configures SQL Agent alerts for severity 17-25 and I/O errors
823/824/825. All operations are guarded with IF NOT EXISTS /
IF EXISTS checks for idempotency.
Prerequisites: - SQL Server 2012+
- sysadmin role membership (required for sp_configure, Database Mail,
operator creation, and alert configuration)
- SQL Server Agent must be running for alerts to fire
- A valid SMTP relay or mail server accessible from the SQL Server host
- Run in the context of the msdb database
Configuration: Set the variables in the "Configuration" section below:
- @ProfileName: Database Mail profile name
- @AccountName: Database Mail account name
- @SmtpServer: SMTP server hostname (e.g. smtp.office365.com)
- @SmtpPort: SMTP port number (default 587 for TLS)
- @EnableSsl: Whether to use SSL/TLS for SMTP (default 1)
- @SmtpUsername: SMTP login username (leave NULL for anonymous relay)
- @SmtpPassword: SMTP login password (leave NULL for anonymous relay)
- @EmailAddress: Sender email address for outgoing notifications
- @DisplayName: Display name shown on outgoing emails
- @ReplyToAddress: Reply-to email address (optional)
- @OperatorName: SQL Agent operator name
- @OperatorEmail: Email address for the DBA operator (alert recipient)
Usage Notes: - This script is safe to re-run. It skips objects that already exist.
- After running, send a test email to verify SMTP connectivity:
EXEC msdb.dbo.sp_send_dbmail @profile_name = '<YourProfile>',
@recipients = '<[email protected]>',
@subject = 'Test', @body = 'Database Mail is working.';
- If using Office 365 / Exchange Online, ensure the sending mailbox
allows SMTP AUTH or configure an SMTP relay connector.
- Alerts require SQL Server Agent to be running and configured to
use Database Mail (SQL Agent Properties > Alert System > Enable
mail profile).
License: MIT License - https://opensource.org/licenses/MIT
Source: https://github.com/mbentham/sql-server-scripts
*/
USE msdb;
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
/* ===========================================================================
Configuration - Set these variables before running
=========================================================================== */
DECLARE @ProfileName NVARCHAR(256) = N'sql-notifications',
@AccountName NVARCHAR(256) = N'sql-notifications',
@SmtpServer NVARCHAR(256) = N'smtp.yourserver.com', -- e.g. smtp.office365.com
@SmtpPort INT = 587, -- 587 for TLS, 25 for unencrypted relay
@EnableSsl BIT = 1, -- 1 = use TLS
@SmtpUsername NVARCHAR(256) = NULL, -- SMTP login username (NULL for anonymous relay)
@SmtpPassword NVARCHAR(256) = NULL, -- SMTP login password (NULL for anonymous relay)
@DisplayName NVARCHAR(256) = N'SQL Notifications',
@OperatorName NVARCHAR(256) = N'DBA',
/* ===========================================================================
Validate configuration
=========================================================================== */
IF @SmtpServer = N'smtp.yourserver.com'
BEGIN
RAISERROR('ERROR: You must set @SmtpServer to your SMTP server before running this script.', 16, 1);
RETURN;
END
BEGIN
RAISERROR('ERROR: You must set @EmailAddress and @OperatorEmail to real addresses before running this script.', 16, 1);
RETURN;
END
/* ===========================================================================
1. Enable Database Mail XPs
=========================================================================== */
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
/* ===========================================================================
2. Create Database Mail account and profile
=========================================================================== */
-- Skip if the profile already exists
IF EXISTS (SELECT 1 FROM msdb.dbo.sysmail_profile WHERE name = @ProfileName)
BEGIN
PRINT 'Database Mail profile [' + @ProfileName + '] already exists. Skipping account/profile creation.';
GOTO ConfigureOperator;
END;
-- Skip if the account already exists
IF EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE name = @AccountName)
BEGIN
PRINT 'Database Mail account [' + @AccountName + '] already exists. Skipping account/profile creation.';
GOTO ConfigureOperator;
END;
BEGIN TRANSACTION;
DECLARE @rv INT;
-- Create the mail account
EXEC @rv = msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayName,
@replyto_address = @ReplyToAddress,
@mailserver_name = @SmtpServer,
@port = @SmtpPort,
@enable_ssl = @EnableSsl,
@username = @SmtpUsername,
@password = @SmtpPassword;
IF @rv <> 0
BEGIN
RAISERROR('Failed to create Database Mail account [%s].', 16, 1, @AccountName);
ROLLBACK TRANSACTION;
GOTO ConfigureOperator;
END;
-- Create the mail profile
EXEC @rv = msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName;
IF @rv <> 0
BEGIN
RAISERROR('Failed to create Database Mail profile [%s].', 16, 1, @ProfileName);
ROLLBACK TRANSACTION;
GOTO ConfigureOperator;
END;
-- Associate the account with the profile
EXEC @rv = msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1;
IF @rv <> 0
BEGIN
RAISERROR('Failed to associate account [%s] with profile [%s].', 16, 1, @AccountName, @ProfileName);
ROLLBACK TRANSACTION;
GOTO ConfigureOperator;
END;
-- Set the profile as the default public profile so SQL Agent can use it
EXEC @rv = msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = N'public',
@profile_name = @ProfileName,
@is_default = 1;
IF @rv <> 0
BEGIN
RAISERROR('Failed to set profile [%s] as default public profile.', 16, 1, @ProfileName);
ROLLBACK TRANSACTION;
GOTO ConfigureOperator;
END;
COMMIT TRANSACTION;
PRINT 'Database Mail account [' + @AccountName + '] and profile [' + @ProfileName + '] created and set as default.';
/* ===========================================================================
3. Create DBA operator
Note: We reach this point even if mail setup failed above, so that the
operator and alerts are in place ready for when mail is fixed.
=========================================================================== */
ConfigureOperator:
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysoperators WHERE name = @OperatorName)
BEGIN
EXEC msdb.dbo.sp_add_operator
@name = @OperatorName,
@enabled = 1,
@email_address = @OperatorEmail;
PRINT 'Operator [' + @OperatorName + '] created.';
END
ELSE
BEGIN
PRINT 'Operator [' + @OperatorName + '] already exists. Skipping.';
END;
/* ===========================================================================
4. Configure SQL Agent severity alerts (17-25)
=========================================================================== */
DECLARE @Severity INT = 17,
@AlertName NVARCHAR(128);
WHILE @Severity <= 25
BEGIN
SET @AlertName = N'Severity ' + RIGHT('000' + CAST(@Severity AS NVARCHAR(3)), 3);
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysalerts WHERE name = @AlertName)
BEGIN
EXEC msdb.dbo.sp_add_alert
@name = @AlertName,
@message_id = 0,
@severity = @Severity,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification
@alert_name = @AlertName,
@operator_name = @OperatorName,
@notification_method = 1;
PRINT 'Alert [' + @AlertName + '] created with notification to [' + @OperatorName + '].';
END
ELSE
BEGIN
PRINT 'Alert [' + @AlertName + '] already exists. Skipping.';
END;
SET @Severity += 1;
END;
/* ===========================================================================
5. Configure SQL Agent I/O error alerts (823, 824, 825)
=========================================================================== */
DECLARE @ErrorNumber INT,
@ErrorCursor CURSOR;
SET @ErrorCursor = CURSOR LOCAL FAST_FORWARD FOR
SELECT ErrorNumber FROM (VALUES (823), (824), (825)) AS E(ErrorNumber);
OPEN @ErrorCursor;
FETCH NEXT FROM @ErrorCursor INTO @ErrorNumber;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AlertName = N'Error Number ' + CAST(@ErrorNumber AS NVARCHAR(10));
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysalerts WHERE name = @AlertName)
BEGIN
EXEC msdb.dbo.sp_add_alert
@name = @AlertName,
@message_id = @ErrorNumber,
@severity = 0,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification
@alert_name = @AlertName,
@operator_name = @OperatorName,
@notification_method = 1;
PRINT 'Alert [' + @AlertName + '] created with notification to [' + @OperatorName + '].';
END
ELSE
BEGIN
PRINT 'Alert [' + @AlertName + '] already exists. Skipping.';
END;
FETCH NEXT FROM @ErrorCursor INTO @ErrorNumber;
END;
CLOSE @ErrorCursor;
DEALLOCATE @ErrorCursor;
PRINT 'Database Mail configuration complete.';
GO