Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
1 of 34
Module and Alerts
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Notifications
Contents:
Lesson 1: Lesson 2: Lesson 3: Lab:
Module One key aspect
to make sure
you are aware
a wealth of
information
when these
issues occur,
Server
database administrators
This module
covers the configuration
Objectives After completing • Monitor SQL • Configure • Configure
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
2 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Lesson It is important particular, you return. SQL
Server. In data that they important to
know how to
Lesson After completing • Define SQL • Describe error • Configure
What Is
It might not be
itself an object,
and therefore
Property
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
3 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Property Error number Error message Severity State Procedure name
occurred.
Line number
Error numbers
error,
particularly when The following system supplied
retrieve a list of above:
Viewing System
SELECT * ORDER BY
Error messages
WHERE
clause of this
Error Severity
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
4 of 34
The severity severity values
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Server. Low severities are
grouped into Values from Values from
provide
information
query SELECT
COUNT(Color)
the
Messages tab
“Warning: Null value
is eliminated
has
occurred, but
rows.
Values from Values from
Server uses
them when it
range
include: • 11. Indicates • 13. Indicates • 14. Indicates • 15. Indicates
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
5 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Values from Values from severity 17 indicates
example, memory or disk
space). Values above Values above SQL Server
hardware or alerts
generated on
Configuring
Important messages, both the Windows shows the available
logged to messages view default.
SQL Server writes
Server\MSSQL12.
\MSSQL\LOG\ERRORLOG
ERRORLOG.n where n
is the log file
text editor or
the Log Viewer By default, SQL backup the extension .
most recent log current error
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
6 of 34
log has no extension.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
the
right-click Configure The log file cycles
you might
want to remove
system stored
procedure to
a regular
need to recycle
the system
stored procedure
error log
becoming too
Demonstration: In this demonstration,
log file.
Demonstration View the SQL 1.
Ensure and log
are running, the
password 2.
In the D:
3.
Start SQL
database engine
instance 4.
In Object SQL Server
5.
Maximize
and expand Log. entries. Note that
when you 6.
In the Select main log
Then scroll the and scroll
down to 7.
When you
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
7 of 34
8.
Minimize Files\Microsoft prompted that the current
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
\Program folder. If you are Continue. Note ERRORLOG, and the log files
contain
Cycle the Log 1.
In SQL
2.
In the query EXEC
3.
Click Execute
4.
In Object
SQL Server
Log. 5.
Note that
Lesson SQL Server needs attention, such commonly-used Mail feature
require their most Database Protocol (SMTP)
server when You can configure utilize the email have been sent
users can trace emails that retention.
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
8 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Lesson After completing • Describe Database • Configure • Configure • Configure
Overview
Database Mail
reliable, scalable,
secure, and supportable
process outside
of SQL Server
However, if the
SMTP server again. By default,
is available surface area
of SQL Server
sysadmin
server role or
can execute
them. Database
and
attachments You can use
response to
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
9 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
an alert, or on You use the Database
configure
accounts and
SQL Server
needs to send authentication name, type, and SQL Server
email server password. other SQL
Server Agent
information in
memory so it
longer
available.
Database
A Database Mail
When there is
more than one
accounts in a
predefined order,
used.
Profiles can be
only available
to members of
sysadmin
role. In contrast,
use a public
profile.
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
10 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Mail Profiles You can create
you could
create one profile
address, for
mails sent by
send
external email Each database sending an email, exist, precedence default profile
profile when public profiles not specify a profile name
you want to use The following
procedure to
send an email Sending Mail
EXEC msdb. @profile_name @recipients @body @subject
Database
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
11 of 34
It is important are configuring
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
use when you Service
account, it does connections—in
server located
on a different Database Mail To minimize
procedures for
Database Mail
Configuration
Wizard, it automatically
Database Mail
manually, you sp_configure
by using the value of 1.
Security and Not all SQL role called DatabaseMailUserRole
the database sysadmin fixed
server role can You can also
by Database
Mail. You can
Wizard or
by calling the
database.
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
12 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Database
SQL Server
configure
how much information • Normal. Only • Extended. • Verbose. Logs
internal
messages.
Note: can generate performance.
You configure
because it impact
Parameters
dialog box of dbo.sysmail_configure_sp
the logged
messages by Internal tables that Database
attachments Database Mail
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
13 of 34
updates these
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
status of an
individual message • dbo.sysmail_allitems • dbo.sysmail_sentitems • dbo.sysmail_unsentitems • dbo.sysmail_faileditems
To see details Because Database a retention policy
view. need to plan related
attachments You can periodically
organization's
document retention The following
entries that are
more than one Deleting Old
USE msdb; GO DECLARE SET @CutoffDate EXECUTE
EXECUTE
GO
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
14 of 34
You could schedule
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SQL Server
Agent job.
Demonstration: In this demonstration, • Create a Database • Send a test • Query Database
Demonstration Create a Database 1.
If you did MIA-DC
20462C20462C-MIA-SQL as
ADVENTUREWORKS\Student D:\Demofiles\Mod13 2.
If SQL
to the
MIA-SQL 3.
In Object
right-click
Database 4.
In the Welcome
.
5.
In the Select
Mail and
click Next 6.
In the New and click
Agent Profile, Profile' dialog
box, click 7.
In the New
and click
OK:
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
15 of 34
o
Account
o
E-mail
o
Display
o
Reply
o
Server
8.
In the New
9.
In the Manage
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Agent Profile
profile, 10. In the Configure
Complete the
Wizard
Send a Test 1.
In Object
2.
In the Send
Server
Agent student@adventureworks. 3.
View the
email
message 4.
Double-click
message, close
it and minimize 5.
In the Database
Server
Management
Query Database 1.
In SQL
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
16 of 34
2.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Enter the SELECT SELECT
3.
View the
and the second
shows records 4.
Keep SQL
Lesson Alerts Many SQL Server configure operators
and
enables you to determine when
to contact each SQL Server can configure space availability,
administrators. You as low disk situations.
Lesson After completing • Describe the • Describe SQL • Create alerts. • Configure • Troubleshoot
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
17 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Operator
An operator
who can receive
electronic notifications
Note: users.
database
Configuring You can define procedure. After
system stored the
dbo.sysoperators You can configure • Email. An to use group
it is desirable multiple email
addresses • Pager email
specified times
(and days) • Net send address
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
18 of 34
Note: development
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
used for new
Fail-Safe Operator You can also • The SQL
circumstances: operators and
notifications • A pager notification
receive pager
alerts are
Job Notifications You can configure
completion,
failure, or success.
success might lead
to a large volume However, for
if a job fails. the outcome
to remove any
Demonstration:
Operators
In this demonstration, • Enable a SQL • Create an • Configure
Demonstration Enable a SQL 1.
Ensure
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
19 of 34
2.
In SQL
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Server Agent
and click 3.
In the SQL Enable
page, select Server Agent
Profile 4.
In Object
prompted to
confirm,
Create an Operator 1.
In Object
click
NewOperator 2.
In the New
E-mail name
box type
Configure a 1.
In Object
2.
Right-click
3.
In the Job Notifications
existing jobs. Properties. on the completes.
Then click 4.
In Object Properties
click notifications that have
been defined 5.
Right-click
Job at Step.
Then, when 6.
Under the page, note
the History .
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
20 of 34
7.
In the C:
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
has been
created. 8.
Double-click
you have read
the message, 9.
Keep SQL
Overview
There are many interest to administrators. attention and execute a job
system that are of that requires define alerts to when a
performance SQL Server startup, SQL means that the interest occur. not need to continuously When the Application compares the
Event Log. On Application Log. This when events of Agent does interest. Server Agent finds a
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
21 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
match, it fires
Note: Application
Windows
Alerts Actions You can create severity level.
specific You can
also define the
Note: with severity
messages
System Events In addition to
conditions
that are detected
WMI Query
Language (WQL) minute, so it performance
each configure exposes.
Creating
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
22 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
You can create
stored
procedure. When
to start when
the alert occurs. The following
message
with id of 9002: Using sp_add_alert
EXEC msdb. @name=N'AdventureWorks @message_id=9002, @database_name=N'AdventureWorks'; GO
Logged Events You have seen to the Windows
messages are written to 25 are
automatically
check which
messages are
column in
the sys.messages Most events
perform one of the
following steps: • Modify the
procedure to
make it a • Raise the • Use the xp_logevent
written to the
log.
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
23 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Configuring
You can configure • Execute a You can configure start multiple
If you need to multiple jobs in
turn, and then • Notify operators You can define dbo.sp_add_notification about alerts,
the operators that they can
determine You can include
available for
working with o A-DBN. o A-SVR. o A-ERR. o A-SEV. o A-MSG.
By default, the
enable it in the
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
24 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
properties of
Troubleshooting
When troubleshooting 1.
identify the issues:
Ensure The Application running.
the agent is make at a later
date. 2.
Check For SQL
Application
Log. You
sufficient
size to 3.
Ensure If the alert
4.
Check If the alert
delay between
responses
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
25 of 34
5.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Check
Check that the notifications, correct. Test
operator configuration is manually sending
mail from the
Demonstration:
Alerts
In this demonstration, • Create an • Test an alert.
Demonstration Create an Alert 1.
In SQL
Agent,
right-click 2.
In the New
Alert. In
the Type
performance
monitor
alert, select
Error number
raised by SQL
Server 3.
In the New
operators and select
the E-mail 4.
In the New
error text in,
select E-mail
Test an Alert
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
26 of 34
1.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
In SQL D:\Demofiles\Mod13
2.
Click Execute
database. When
the log 3.
In Object Properties
click of last
response 4.
In the C:
has been
created. 5.
Double-click
you have read
the message, 6.
Close SQL
Lab: Using Scenario You are a database the AWDataWarehouse configured jobs
responsibility for have implement
notifications
Objectives After completing • Configure • Configure • Configure
Lab Setup
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
27 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Estimated Time: Virtual machine: User name: ADVENTUREWORKS Password: Pa$$w0rd
Exercise
Scenario All database communication.
notifications from
SQL Server. The main tasks 1.
Prepare
2.
Configure
3.
Test Database
Task 1: Prepare
1.
Ensure then log
running, and password
Pa$$w0rd 2.
Run Setup.
Task 2: Configure
1.
Configure
profile named
SQL Server
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
28 of 34
o
Account
o
E-mail
o
Display
o
Reply
o
Server
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
The new profile Task 3: Test
1.
Send a
2.
Verify that
[email protected]. \inetpub\mailroot\Drop
folder. 3.
Query the
msdb database
to view
Result: After named SQL
new profile
Exercise
Scenario Now that you notifications. are responsible
receive which you also need to
configure a fail-safe (dba@adventureworks. You need to HumanResources peace of mind
business, so for its
transaction log
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
29 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
The main tasks 1.
Create
2.
Configure
3.
Configure
4.
Test Job
Task 1: Create
1.
Create a student@adventureworks.
2.
Create a dba@adventureworks.
Task 2: Configure
1.
Configure
Profile Database
Mail profile. 2.
Set the
3.
Restart
Task 3: Configure
1.
Configure HumanResources
2.
Configure
InternetSales jobs to
notify the 3.
Verify the
notification properties.
Task 4: Test
1.
Run the
Back Up
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
30 of 34
Database
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
which should
succeed). 2.
View the
notification that was
sent. 3.
Verify that AWDataWarehouse
InternetSales job were
successfully
Result: After Team, configured Database Mail Up Database InternetSales
DBA Profile AWDataWarehouse, Back Up Log -
Exercise
Scenario The InternetSales
remains
operational should
configure an alert
that will notify
up the
transaction log, The main tasks 1.
Create
2.
Test the
Task 1: Create
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
31 of 34
1.
Create an
2.
Configure the error
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
that includes InternetSales
database.
Task 2: Test
1.
Use the
the
InternetSales 2.
View the
most recent
alert and 3.
Verify that Back Up
completion of the \inetpub\mailroot\Drop
folder.
Result: After Alert.
InternetSales Log Full
Review Review Question(
Module In this module, and the notification
Database Mail database servers
and databases
Best Practice: following • Use
consider the
• Configure
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
32 of 34
• Provide engine.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
database
• Implement • Create • Define
Review Question(
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
33 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:56
Module 13: Monitoring SQL Server 2014 with Notifications and Alerts
34 of 34
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:56
