📢 Automating SQL Server Alert Acknowledgment with Microsoft Forms and Power Automate

As a Junior DBA, you may be asked to monitor your SQL Server environment and respond quickly to issues. But how do you track who saw an alert, when it was resolved, or what actions were taken?

In this blog, I’ll walk you through how to use Microsoft Forms + Power Automate to build a lightweight feedback loop — without building a custom app.

---

🧩 What You’ll Set Up

You’ll create a process where:

• SQL sends an alert via Database Mail
• That alert includes a link to a Microsoft Form
• A DBA fills it out to confirm they handled it
• Power Automate catches the form response
• The response gets sent to a Teams channel or email for tracking

This is perfect for small teams that want accountability and visibility without heavy software.

---

🛠️ Prerequisites

You’ll need:

• SQL Server with Database Mail enabled
• A Microsoft 365 account (for Forms and Power Automate)
• Basic access to Microsoft Teams (optional)

---

✅ Step 1: Enable Database Mail

If it’s not already enabled, run this in SSMS:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;

Then go to SSMS → Management → Database Mail and:

• Configure a new profile (e.g., DBA_Alerts)
• Add your SMTP server (like smtp.office365.com)
• Test with a basic email

---

✅ Step 2: Create a Microsoft Form

Go to forms.microsoft.com

Set up your form with these fields:

Field	Type
Name or Initials	Text
Acknowledge this alert?	Choice (Yes / No)
Time issue was resolved	Date/Time
What did you do?	Paragraph text
Optional: Upload screenshot	File Upload

Tip: Give your form a name like SQL Alert Acknowledgment.

---

✅ Step 3: Create the Power Automate Flow

Go to Power Automate

Steps:

1. Create → Automated Cloud Flow
   • Name: Log SQL Alert Response
   • Trigger: When a new response is submitted (Microsoft Forms)
   • Choose your Form from Step 2
2. Get response details
   • Add the “Get response details” step
   • Choose same Form
3. Post to Teams (optional)
   • Add: “Post a message in a chat or channel”
   • Format message like:

🚨 SQL Alert Acknowledged
✅ Acknowledged by: [Name]
🕒 Resolved at: [Time]
🛠️ Action taken: [Response]

1. Send Email (optional alternative)
   • Add “Send an email” action if not using Teams
2. (Optional): Log to SharePoint or Excel
   • Store responses for later auditing

---

✅ Step 4: Add Form Link in SQL Alert

Now edit your SQL alert script to include the Form URL.

DECLARE @count INT;
SELECT @count = COUNT(*) FROM dbo.Orders WHERE Status = 'Pending';

IF @count > 100
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DBA_Alerts',
        @recipients = 'alerts@yourteam.teams.ms',
        @subject = '🚨 High Pending Orders Detected',
        @body = 'More than 100 pending orders detected.

Please acknowledge this alert and log your action here:
👉 https://forms.office.com/pages/responsepage.aspx?id=YourFormLinkHere

Thanks,
Your SQL Server';
END

✅ Now, when the DBA sees the alert in their email or Teams, they can click the form, fill it out, and the rest is automated.

🧪 Bonus: Log Form Data to a SQL Table (Advanced)

If you’re comfortable, you can also have Power Automate write the form data into a SQL table like this:

CREATE TABLE dbo.AlertAcknowledgments (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    AcknowledgedBy NVARCHAR(100),
    TimeResolved DATETIME,
    ActionTaken NVARCHAR(MAX),
    SubmittedAt DATETIME DEFAULT GETDATE()
);

Then in Power Automate, use the “SQL Server → Insert Row” action.

---

📊 What You Gain

• ✅ Real-time visibility into who handled which alerts
• ✅ An audit trail for SLAs and compliance
• ✅ Less “Did anyone see this?” confusion
• ✅ Works with zero-code tools (Forms + Power Automate)

---

🧪 Try This Out!

Try this full setup during your next SQL alert test and let me know:

• Did it notify the right people?
• Was the form quick to fill out?
• Did it improve your response tracking?

If it needs tweaking — like auto-reminders, SLA timers, or multi-step approvals — let me know and I’ll help you level it up.

Happy alerting! 🧠🛠️📩