Interview Questions on AOAG in SQL server

1.What are the prerequisites for setting up AlwaysOn?

2.How do you set up replication in AlwaysOn environment?

3.How do you manage replication during Windows patching or failover if replication has been set up in AlwaysOn?

4.How do you sync logins in AlwaysOn?

5.How do you sync users in AlwaysOn secondary?

6.How do you add database files in AlwaysOn?

7.How do you perform an in-place upgrade of SQL Server in a AlwaysOn environment?

8.What is the procedure for SQL Server patching in AlwaysOn?

9.How do you failover a mirror server if replication has been set up?

10.What is the SPN concept in AlwaysOn?

11.What is file share in AlwaysOn?

12.How do you create multiple AlwaysOn listeners?

13.How do you check latency in AlwaysOn?

14.What is the command used to check latency in replication without using GUI?

15.What are DNS issues in AlwaysOn?

16.If a user is connecting to the primary and not able to connect to the secondary, and the secondary is in read-only mode, how do you fix the issue in AlwaysOn?

17.How do you fix if secondary is not in sync?

18.How do you apply TDE in AlwaysOn?

19.How do you add the databases to availability group when encryption is enabled?

20.How to check the health of AlwaysOn?

21.How to resolve if blockings occur in secondary replica due to reporting server?

22.What are the DMVs used to troubleshoot AlwaysOn?

23.How do you set backup priority in AlwaysOn?

24.How do you restore the database which is part of AG ?

Source: Ali Shaik's Database Diary

How to Send an Email Notification When SQL Server Service Restarts

Monitoring SQL Server service restarts is essential for maintaining database availability and troubleshooting unexpected issues. In this post, I’ll show you how to automate email notifications whenever SQL Server services start.

Why Monitor Service Restarts?

Unexpected SQL Server service restarts could indicate issues like:

• Server reboots
• Maintenance activities
• Resource constraints
• Crashes or failures

Getting an instant email notification helps database administrators (DBAs) quickly investigate the reason for the restart.

SQL Script to Send Email on Service Restart

Below is a simple T-SQL script that sends an email when SQL Server services restart.

USE msdb

GO

-- Declare variables for email content

DECLARE @ServerName VARCHAR(128),

        @ComputerNamePhysicalNetBIOS VARCHAR(128),

        @Datetime DATETIME,

        @EmailRecipients VARCHAR(512),

        @EmailSubject VARCHAR(128),

        @MessageBody VARCHAR(512)

-- Assign values to variables

SELECT @ComputerNamePhysicalNetBIOS = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(128)),

       @ServerName = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(128)),

       @Datetime = GETDATE(),

       @EmailRecipients = 'bajeyudu@sqldbanow.com', -- Add multiple emails using a semicolon (;)

       @EmailSubject = SQL Server Services Have Been Started!!!'

-- Construct the email body

SELECT @MessageBody = 'SQL Server services have been started on a SQL Server Instance named ' + @ServerName + CHAR(13) +

                      'running on Windows server ' + @ComputerNamePhysicalNetBIOS + '.' + CHAR(13) + CHAR(13) +

                      'Investigate the service restart if it has not been communicated.'

-- Send email using Database Mail

EXEC sp_send_dbmail

    @recipients = @EmailRecipients,

    @subject = @EmailSubject,

    @body = @MessageBody,

    @body_format = 'TEXT'

How to Automate This?

1. Enable Database Mail:
   Ensure Database Mail is configured in SQL Server. 

2. Create a SQL Server Agent Job:
• Open SQL Server Agent → Jobs → New Job.
• Set the job name (e.g., "Monitor SQL Service Restarts").
• Add a new step and paste the above script.
• Schedule it to run at server startup (under Schedules, choose Start automatically when SQL Server Agent starts)

Automating Suspect Pages Monitoring in SQL Server with Email Alerts

Introduction

Database corruption is one of the most critical issues in SQL Server, and detecting suspect pages early can help prevent data loss and downtime. SQL Server logs suspect pages in msdb.dbo.suspect_pages whenever it encounters corruption issues like torn pages, checksum failures, or I/O errors. In this article, I’ll share a simple yet effective SQL script that automates the detection of suspect pages and sends an HTML-formatted email report to database administrators.

SQL Script for Suspect Pages Alert

The following SQL script checks for suspect pages in SQL Server and sends an email alert with the details when any are found. Additionally, it deletes suspect page records older than 90 days to maintain a clean log.

DECLARE @count INTEGER;

DECLARE @tableHTML NVARCHAR(MAX);

DECLARE @subj NVARCHAR(100);

SELECT @count = COUNT(1) FROM msdb.dbo.suspect_pages;

SET @subj = 'Suspect Pages Found in ' + @@SERVERNAME;

SET @tableHTML =

    N'<H1>Suspect Pages Found in ' + @@SERVERNAME + ', details are below.</H1>' +

    N'<table border="1">' +

    N'<tr><th>Database ID</th><th>Database</th>' +

    N'<th>File ID</th><th>File</th><th>Page ID</th>' +

    N'<th>Event Desc</th><th>Error Count</th><th>Last Updated</th></tr>' +

    CAST((SELECT td = sp.database_id, '',

                 td = d.name, '',

                 td = sp.file_id, '',

                 td = mf.physical_name, '',

                 td = sp.page_id, '',

                 td = CASE

                         WHEN sp.event_type = 1 THEN '823 or 824 error other than a bad checksum or a torn page'

                         WHEN sp.event_type = 2 THEN 'Bad checksum'

                         WHEN sp.event_type = 3 THEN 'Torn Page'

                         WHEN sp.event_type = 4 THEN 'Restored (The page was restored after it was marked bad)'

                         WHEN sp.event_type = 5 THEN 'Repaired (DBCC repaired the page)'

                         WHEN sp.event_type = 7 THEN 'Deallocated by DBCC'

                     END, '',

                 td = sp.error_count, '',

                 td = sp.last_update_date

          FROM msdb.dbo.suspect_pages sp

          INNER JOIN sys.databases d ON d.database_id = sp.database_id

          INNER JOIN sys.master_files mf ON mf.database_id = sp.database_id AND mf.file_id = sp.file_id

          FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +

    N'</table>';

IF @count > 0

BEGIN

    EXEC msdb.dbo.sp_send_dbmail

        @recipients = N'bandaruajeyudu@outlook.com',

        @body = @tableHTML,

        @subject = @subj,

        @body_format = 'HTML',

        @profile_name = 'SQLMail';

END

DELETE FROM msdb.dbo.suspect_pages WHERE last_update_date < GETDATE() - 90;

--Note: To ensure this script works correctly, you need to configure Database Mail in SQL Server.