Production environment me sirf jobs schedule karna hi kaafi nahi hota. Hume monitoring aur notification system bhi chahiye hota hai taaki agar koi job fail ho jaye ya system me problem aaye to DBA ko turant pata chal jaye.

SQL Server me ye kaam Alerts, Operators aur Database Mail ke through hota hai.

SQL Server Agent Alerts – Automatic Monitoring System

SQL Server Agent Alerts ek automatic response mechanism hai jo kisi specific event ke hone par trigger hota hai.

Example situations:

• Job failure
• Disk space threshold cross ho gaya
• CPU utilization high ho gaya
• SQL Server error severity high aa gayi

Alert ka kaam hai:

1. Event detect karna
2. Operator ko notify karna
3. Ya automatically koi job run karna

Alert Kaise Work Karta Hai (Internal Flow)

1. SQL Server event generate karta hai
2. Event Windows Application Log me entry karta hai
3. SQL Server Agent log ko read karta hai
4. Defined alert conditions se match karta hai
5. Match hone par alert trigger hota hai

Operators – Notification Receiver

Operator ek contact person hota hai jo alert receive karta hai.

Operator me store hota hai:

• Name
• Email address
• Pager (old systems)
• Notification schedule

Important:

⚠ Operator koi login ya database user nahi hota
⚠ Ye sirf contact information hoti hai

Example:

DBA On-Call → Email receive karega jab job fail hogi.

Database Mail – Email Sending System

SQL Server me email bhejne ke liye Database Mail use hota hai.

Database Mail allow karta hai:

• Email send karna
• Query result attach karna
• Files attach karna
• Alerts send karna

Database Mail ka architecture queued architecture par based hota hai using Service Broker.

Practical Setup – Database Mail Configure Karna

Ab dekhte hain step-by-step kaise Database Mail setup karte hain.

Database Mail setup karne ke liye SQL Server Management Studio me:

Management → Database Mail

Step 1: Open Database Mail Configuration

SSMS me jao:

Management
   → Database Mail
   → Right Click
   → Configure Database Mail

Setup wizard open hoga.

Step 2: Create New Database Mail Profile

Select option:

Set up Database Mail by performing the following tasks

Next click karo.

Step 3: Enable Service Broker

Agar Service Broker msdb database me enable nahi hai to message aayega:

Database Mail requires Service Broker.

Yes select karo.

Ye automatically enable ho jayega.

Verify karne ke liye query run kar sakte ho:

SELECT is_broker_enabled
FROM sys.databases
WHERE name = 'msdb'

Step 4: Create Mail Profile

Profile name example:

SQLServerMailProfile

Description optional hai.

Step 5: Configure SMTP Account

SMTP server details fill karo.

Example configuration:

Account Name

SQLServerMailAccount

Email Address

sqlserver@company.com

Display Name

DB Admin

SMTP Server

smtp.company.com

Authentication:

• Windows Authentication
• Basic Authentication
• Anonymous (test environment)

Production me SMTP server use hota hai.

Step 6: Set Default Profile

Next step me profile ko default public profile set karo.

Ye SQL Server Agent ko email bhejne ke liye use hoga.

Finish click karo.

Database Mail successfully configure ho jayega.

Test Database Mail

Email test karne ke liye stored procedure use karte hain:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLServerMailProfile',
    @recipients = 'dba@company.com',
    @subject = 'Test Email from SQL Server',
    @body = 'Database Mail is working successfully'

Agar SMTP properly configured hai to email send ho jayega.

Operator Create Karna (Practical)

Ab operator create karte hain jo alerts receive karega.

SSMS me:

SQL Server Agent
    → Operators
    → Right Click
    → New Operator

Fields fill karo:

Name

DBA_OnCall

Email Name

dba@company.com

Notification schedule:

Usually 24x7 enabled.

Save karo.

Alert Create Karna (Practical)

Ab alert create karte hain.

SSMS me:

SQL Server Agent
    → Alerts
    → Right Click
    → New Alert

Step 1: Alert Name

Example:

HighSeverityErrorAlert

Step 2: Alert Type

Options:

• SQL Server Event Alert
• Performance Condition Alert
• WMI Event Alert

Most common:

SQL Server Event Alert

Step 3: Define Severity

Example:

Severity Level:

17 – 25

Ye severe database errors ke liye hota hai.

Step 4: Response Configure Karna

Response tab me:

Notify Operator

Select:

DBA_OnCall

Notification method:

✔ Email

Step 5: Save Alert

OK click karo.

Alert ready hai.

Ab jab defined event trigger hoga:

SQL Server Agent → Operator ko email bhejega.

Job Failure Notification Setup

Existing job me bhi alert configure kar sakte ho.

Steps:

SQL Server Agent
    → Jobs
    → Job Properties
    → Notifications

Options:

• Email operator on success
• Email operator on failure
• Email operator on completion

Example:

Email DBA_OnCall when job fails

Job Activity Monitor – Real-Time Monitoring Tool

SQL Server Agent me ek powerful monitoring dashboard hota hai:

Job Activity Monitor.

Ye SQL Server Management Studio me available hota hai.

Job Activity Monitor Kya Show Karta Hai?

• Job Name
• Status
• Last Run Outcome
• Last Run Time
• Next Scheduled Run
• Job Category

Job Activity Monitor Se Kya Kar Sakte Ho?

✔ Job start kar sakte ho
✔ Job stop kar sakte ho
✔ Job history dekh sakte ho
✔ Running jobs monitor kar sakte ho

Example Real Production Setup

Typical enterprise setup:

Daily Jobs

• Database backup
• Index maintenance
• ETL pipeline
• Log cleanup

Monitoring setup

Database Mail → Email system
Operator → DBA contact
Alerts → Error detection
Job Activity Monitor → Real-time status

Result:

✔ Automation
✔ Monitoring
✔ Immediate notification
✔ High reliability

Final Thoughts

SQL Server Agent ka complete ecosystem automation aur monitoring dono provide karta hai.

Key components:

• Jobs → Task automation
• Schedules → Execution timing
• Alerts → Event monitoring
• Operators → Notification contact
• Database Mail → Email delivery
• Job Activity Monitor → Real-time dashboard