SQL Server Database Capacity Planning

 SQL Server database capacity planning involves estimating the resources and storage requirements necessary to support the expected growth of your database over time. Here are some key considerations and steps to help you with SQL Server database capacity planning:

Data Analysis and Profiling:

Analyze the existing data and understand its characteristics, including the size, growth rate, and usage patterns.

Identify the tables, indexes, and other database objects that contribute to the overall database size.

Profile the workload by capturing and analyzing relevant queries and transactions to determine resource usage patterns.
 
Storage Requirements:

Estimate the size of the database files (data files, transaction log files) based on the current data size and expected growth rate.

Consider factors such as data compression, partitioning, and archiving strategies to optimize storage utilization.

Choose appropriate storage technology (e.g., direct-attached storage, SAN, or cloud storage) based on performance, scalability, and cost considerations.
 
Memory Considerations:

Determine the memory requirements for SQL Server based on the database size, workload, and query complexity.

Allocate sufficient memory for SQL Server's buffer cache, query execution plans, and other caching mechanisms to optimize performance.

Configure the appropriate maximum memory setting to ensure SQL Server does not consume excessive resources on the server.
 
CPU and Processing Power:
Analyze the workload and identify the CPU-intensive queries or processes.
Determine the CPU requirements based on the workload's complexity, concurrency, and expected growth.
Consider factors such as parallelism settings, query optimization, and server hardware capabilities to ensure adequate CPU resources.
 
Network Considerations:

Assess the network bandwidth requirements based on data transfer rates between clients and the SQL Server.

Consider factors such as client connections, data replication, and backup/restore operations that may utilize network resources.
 
Optimize the network configuration and infrastructure to minimize latency and maximize performance.

Scalability and Growth:

Consider the expected growth rate of the database and plan for future scalability.
Implement strategies such as database partitioning, table partitioning, or sharding to distribute data across multiple servers if required.

Regularly monitor database growth and performance to ensure that the capacity planning remains aligned with the actual usage.
 
Monitoring and Maintenance:

Implement a robust monitoring and alerting system to track resource utilization, performance metrics, and storage growth.

Regularly review and analyze monitoring data to identify trends, bottlenecks, and areas for optimization.
Perform routine database maintenance tasks such as index maintenance, statistics updates, and backup integrity checks to optimize performance and storage utilization.

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;

SQL Jobs Failed report via email from SQL Server

Using below mentioned script to get failed jobs report in SQL Server.

create table Failed_Jobs (

[Status] [varchar](6) NOT NULL,

[Job Name] [varchar](100) NULL,

[Step ID] [varchar](5) NULL,

[Step Name] [varchar](30) NULL,

[Start Date Time] [varchar](30) NULL,

[Message] [nvarchar](4000) NULL)

insert into Failed_Jobs

select 'FAILED' as Status, cast(sj.name as varchar(100)) as "Job Name",

       cast(sjs.step_id as varchar(5)) as "Step ID",

       cast(sjs.step_name as varchar(30)) as "Step Name",

       cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',

       sjh.message as "Message"

from sysjobs sj

join sysjobsteps sjs 

 on sj.job_id = sjs.job_id

join sysjobhistory sjh 

 on sj.job_id = sjh.job_id and sjs.step_id = sjh.step_id

where sjh.run_status <> 1

  and cast(sjh.run_date as float)*1000000+sjh.run_time > 

      cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at 7am

union

select 'FAILED',cast(sj.name as varchar(100)) as "Job Name",

       'MAIN' as "Step ID",

       'MAIN' as "Step Name",

       cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',

       sjh.message as "Message"

from sysjobs sj

join sysjobhistory sjh 

 on sj.job_id = sjh.job_id

where sjh.run_status <> 1 and sjh.step_id=0

  and cast(sjh.run_date as float)*1000000+sjh.run_time >

      cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at

declare @cnt int  

select @cnt=COUNT(1) from Failed_Jobs    

if (@cnt > 0)

begin

declare @strsubject varchar(100)

select @strsubject='Check the following failed jobs on ' + @@SERVERNAME

declare @tableHTML  nvarchar(max);

set @tableHTML =

N'<H1>Failed Jobs Listing - ' + @@SERVERNAME +'</H1>' +

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

N'<tr><th>Status</th><th>Job Name</th>' +

N'<th>Step ID</th><th>Step Name</th><th>Start Date</th>' +

N'<th>Message</th></tr>' +

CAST ( ( SELECT td = [Status], '',

                    td = [Job Name], '',

                    td = [Step ID], '',

                    td = [Step Name], '',

                    td = [Start Date Time], '',

                    td = [Message]

  FROM Failed_Jobs

  FOR XML PATH('tr'), TYPE 

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@from_address='BANDARUAJEYUDU@OUTLOOK.com',

@recipients='BANDARUAJEYUDU@OUTLOOK.com',

@subject = @strsubject,

@body = @tableHTML,

@body_format = 'HTML' ,

@profile_name='SQLMail'

end

drop table Failed_Jobs