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

Send Email for Service Restarts in SQL server

 --Send Email for Service Restarts in SQL server

USE msdb

GO

-- Declare variables for necessary email content

DECLARE @ServerName VARCHAR(128),

@ComputerNamePhysicalNetBIOS VARCHAR(128),

@Datetime DATETIME,

@EmailRecipients VARCHAR(512),

@EmailSubject VARCHAR(128),

@MessageBody VARCHAR(512)

-- Set variables to proper values

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

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

@Datetime = GETDATE(),

@EmailRecipients = 'bajeyudu@sqldbanow.com', -- if more than one email address use ; between email addresses

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

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.'

EXEC sp_send_dbmail

@recipients = @EmailRecipients,

@subject = @EmailSubject,

@body = @MessageBody,

@body_format = 'TEXT'

Suspect Pages Alert in SQL Server

 --By using below mentioned script will get suspect pages report via email 

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

  exec msdb.dbo.sp_send_dbmail

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

    @body= @tableHTML, 

    @subject = @subj,

    @body_format = 'HTML',

    @profile_name ='SQLMail'

GO

delete from msdb.dbo.suspect_pages

where last_update_date < getdate()-90;

Monitoring SQL Server Jobs: Script to Report Failed Jobs

Managing and monitoring SQL Server jobs is crucial for maintaining the health and performance of your SQL Server environment. One of the key aspects is to ensure that failed jobs are promptly reported and addressed. Below is a script that creates a report of failed SQL Server jobs and sends it via email. This script can be particularly useful for DBAs to automate the monitoring process.

Script Overview

The script performs the following tasks:

1. Create a Table: A temporary table Failed_Jobs is created to store details of the failed jobs.
2. Insert Data: Job failure details are inserted into the Failed_Jobs table. This includes jobs that failed in the last 24 hours.
3. Check for Failures: The script checks if there are any failed jobs.
4. Send Email: If there are any failures, an HTML-formatted email is sent, listing the details of the failed jobs.
5. Drop Table: The temporary table is dropped at the end of the script.

Here’s the complete script:

Automatic alert when cluster fail over happened

When  fail over happen, both SQL server and Agent service will get restarted, Then we will get Notification Alert When Fail Over Happened 

DECLARE @importance AS VARCHAR(6)

DECLARE @body AS NVARCHAR(1000)

DECLARE @Subject  AS NVARCHAR(1000)

DECLARE @InstanceName AS VARCHAR(100)DECLARE @NodeName AS NVARCHAR(100)

DECLARE @recipients VARCHAR(100)

SELECT  @InstanceName =@@SERVERNAME

SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))

SELECT  @Subject  = 'Fail over Happened for Instance '+@InstanceName

SELECT  @body = 'Fail over Happened for Instance '+@InstanceName + '. This instance is currently running on the node '+@NodeName

SELECT  @importance ='High'

EXEC msdb.dbo.sp_send_dbmail

     @profile_name ='sqlmail',

         @recipients ='bajeyudu@SQLDBANOW.com;',

     --@recipients=@recipientsList,

     @subject = @subject ,

     @body = @body,

     @body_format = 'HTML' ,

     @importance=@importance