RDS SQL Server : Enable and Configure Database mail profile

In this article will discuss about enabling and configuring Database Mail on AWS RDS SQL Server.

Before configuring Database Mail, you've to first enable it through a database parameter group.
In Amazon RDS, parameter groups act as a container for engine configuration values that are applied to one or more DB instances. Each RDS instance comes with an associated default parameter group; however, we can’t modify it.

You can either use a new parameter group or an existing created parameter group. If choosing an existing parameter group, it must support your SQL Server instance edition and version.
To enable Database Mail through a parameter group, complete the following steps:

- On the Amazon RDS console, choose Parameter groups.
- Choose the parameter group you want to use.
- In the search box, enter database mail xps.
- Choose Edit Parameters to modify the value.
- For Values, choose 1.
- Save your changes.
- On the Amazon RDS console, choose Databases.
- Choose the instance you want to use.
- Choose Modify.
- Under Database options, choose the parameter group that has database mail xps set to 1.
- Choose Continue.
- Under Scheduling of modifications, choose Immediately.
- Choose Modify DB Instance to apply the changes.

Before we can use Database Mail, we need to set up a mail configuration.
- Launch SQL Server Management Studio.
- Connect to the SQL Server engine of the RDS instance that Database Mail is enabled for.
- Open a new query.
Use the following stored procedures to create a simple Database Mail configuration.

- Create a Database Mail profile (a profile is a container used to store email accounts). See the following code:
use msdb
go

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Notifications',
@description = 'Profile used for sending outgoing notifications using SES.' ;

- Add principles to the profile; use public so any user can access the profile:
use msdb
go

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Notifications',
@principal_name = 'public',
@is_default = 1 ;

We can grant the permissions on Database Mail objects as needed, but public is fine for now.
- Create the Database Mail account (make sure to enter the correct SMTP credentials):

use msdb
go

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Acc1',
@description = 'Mail account for sending outgoing notifications.',
@email_address = 'example@example.com',
@display_name = 'Automated Mailer',
@mailserver_name = 'email-smtp.us-west-2.amazonaws.com',
@port = 587,
@enable_ssl = 1, @username = 'SMTP-username',
@password = 'SMTP-password' ;

- Add Database Mail account to the Database Mail profile:
use msdb
go

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Notifications',
@account_name = 'Acc1',
@sequence_number = 1 ;

- Sending a test email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'success@simulator.amazonses.com',
@body = 'The database mail configuration was completed successfully.',
@subject = 'Automated Success Message';
GO

Then run this stored procedure to see all email items:
SELECT * FROM msdb.dbo.sysmail_allitems

Please refer the below document for more details on the same : [+] Using Database Mail on Amazon RDS for SQL Server; https://aws.amazon.com/blogs/database/using-database-mail-on-amazon-rds-for-sql-server/

RDS SQL Server : Script to automate native backup of all the individual databases using msdb.dbo.rds_backup_database Stored procedure

You can use the below command to automate native backup of all the databases using msdb.dbo.rds_backup_database Stored procedure. You can also use the below script to schedule as a agent job to take native backup (in .bak file) of your all databases.

Script to take Full backup without encryption

DECLARE @BackupFileName varchar(50)
DECLARE @DBName sysname
DECLARE @S3ARN_Prefix nvarchar(100)
DECLARE @S3ARN nvarchar(100)
SET @S3ARN_Prefix = 'arn:aws:s3:::awsbucket_name/' -- update your bucketname

DECLARE DBBackup CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('rdsadmin', 'master', 'model', 'msdb', 'tempdb')
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND is_read_only = 0

OPEN DBBackup
FETCH NEXT FROM DBBackup INTO @DBName
WHILE (@@FETCH_STATUS = 0)

BEGIN
SET @BackupFileName = @DBName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),20),'-',''),':',''),' ','') + '.bak'
SET @S3ARN = @S3ARN_Prefix + @BackupFileName
EXEC msdb.dbo.rds_backup_database
@source_db_name = @DBName,
@S3_arn_to_backup_to = @S3ARN,
@overwrite_S3_backup_file = 0
FETCH NEXT FROM DBBackup INTO @DBName
END

Script to take Full backup with encryption

DECLARE @BackupFileName varchar(50)
DECLARE @DBName sysname
DECLARE @S3ARN_Prefix nvarchar(100)
DECLARE @S3ARN nvarchar(100)
DECLARE @KMS_master_key_ARN nvarchar(100)
SET @S3ARN_Prefix = 'arn:aws:s3:::awsbucket_name/' -- update your bucketname
SET @KMS_master_key_ARN = 'arn:aws:kms:us-east-1:XXXXXXXXXX:key/xxxxxxxx-xxxx-xxxx-xxxx-xxxx' -- update your kms key ARN

DECLARE DBBackup CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('rdsadmin', 'master', 'model', 'msdb', 'tempdb')
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND is_read_only = 0

OPEN DBBackup
FETCH NEXT FROM DBBackup INTO @DBName
WHILE (@@FETCH_STATUS = 0)

BEGIN
SET @BackupFileName = @DBName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),20),'-',''),':',''),' ','') + '.bak'
SET @S3ARN = @S3ARN_Prefix + @BackupFileName
EXEC msdb.dbo.rds_backup_database
@source_db_name = @DBName,
@S3_arn_to_backup_to = @S3ARN,
@KMS_master_key_arn = @KMS_master_key_ARN,
@overwrite_S3_backup_file = 0
FETCH NEXT FROM DBBackup INTO @DBName
END

To track the status of the job, use this SQL statement:

exec msdb..rds_task_status @task_id= 5 -- 5 as an example of your task id

RDS SQL Server : How to reduce the size on disc for RDS SQL Server database?

First of all I would like to explain you that SQL Server databases have two types of files; Data File and Log File.

1. Data files contain data and objects such as tables, indexes, stored procedures, and views.
Data files can be shrink, however it is not a good practice to shrink the data file unless necessary as it can result into blocking, dead locks and index fragmentation. The below documentation to provides more insights on why shrink data files should be avoided.

[+] https://littlekendra.com/2016/11/08/shrinking-sql-server-data-files-best-practices-and-why-it-sucks/
[+] https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
2. Log files contain the information that is required to recover all transactions in the database.

Log Files can be shrink, based upon the log space utilization and on left a transaction open and running.
Now if you would like to know which file in the database is taking more space can use below command to identify file utilization:

USE [your_database_name]
GO

SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
GO

Then accordingly perform the shrink, Here are the steps to perform the shrink for database files:

For Log files:
Firstly, I would humbly request you to check if you have an open or active transaction. The following queries may help:

1. DBCC SQLERF(LOGSPACE)
>> Provides the log space utilization
[+] How can I troubleshoot storage consumption in my Amazon RDS DB instance that is running SQL Server? -
https://aws.amazon.com/premiumsupport/knowledge-center/rds-sql-server-storage-optimization/
2. SELECT name, log_reuse_wait_desc FROM sys.databases
>> Provides why each database's log file is not clearing out
[+] sys.databases (Transact-SQL)
- https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15
>> Includes the possible reasons[+]:
a. Log backup needs to be run
b. Active backup running - because the full backup needs the transaction log to be able to restore to a specific point in time
c. Active transaction - someone typed BEGIN TRAN and locked their workstation for the night

[+] My Favorite System Column: LOG_REUSE_WAIT_DESC
- https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/
As already mentioned above, you can perform the shrink on the transaction log using the below command.

Step 1: Please run the below command to get the used space and free space of transaction log in the database [+]
DBCC SQLPERF(LOGSPACE); --> review transaction log file utilization per database level, if it is more than 80% free then only perform step2
[+] https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-2017
Step 2: Query to shrink transaction log file
USE [userdb-name]
GO
DBCC SHRINKFILE (logfilename, sizeinmb)

If this instance is vital to production, I would also like to recommend using CloudWatch Alarms [+] to monitor your 'FreeStorageSpace' to
prevent Storage Full issues. The alarm will monitor this metric and notify you when the defined threshold is breached, so you may take
immediate actions to increase storage.
[+] How can I create CloudWatch alarms to monitor the Amazon RDS free storage space and prevent storage full issues?
- https://aws.amazon.com/premiumsupport/knowledge-center/storage-full-rds-cloudwatch-alarm/
For Data Files:

You can use same 'DBCC SHRINKFILE' command for shrinking a data file to a specified target size.

The following example shrinks the size of a data file named 'DataFile1' in the user database to 1024 MB.

USE [userdb-name];
GO
DBCC SHRINKFILE (DataFile1, 1024);
GO

OR From GUI- right click on database>click task> shrink> files > select data file from drop down.
I would also like you to know that data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.
To know fragmentation information for the data and indexes of the specified database ‘DB_name’

  SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
	avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
	FROM sys.dm_db_index_physical_stats
	(DB_ID(DB_name'), NULL, NULL, NULL , 'SAMPLED')
	ORDER BY avg_fragmentation_in_percent DESC

To rebuild all indexes in a table, here is the sample query:
	ALTER INDEX ALL ON DatabaseName.SchemaName
	REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

[+] https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15
These suggestions are based on best effort basis, to conclude I would highly recommend you to please test these thoroughly before implementing them on any production environment. This will help in proactive testing to avoid any unforeseen issues during actual implementation.

SQL Server : Capture expensive queries using Server side trace

You can follow the below steps to capture the expensive queries by creating the trace and also from SQL cache plan DMV's :

1. Create Server Side trace: by defining a trace, its the events and columns to captured, and add the filter condition.

You can use the below Script to create the trace:

-- Create Server-side trace
-- Create a Queue
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
set @maxfilesize = 2048

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

DECLARE @MyFileName nvarchar(256)
SELECT @MyFileName='D:\RDSDBDATA\Log\RDSSQLTrace_' + CONVERT(nvarchar(10),GetDate(),121) + '_' + REPLACE(CONVERT(nvarchar(10),GetDate(),108),':','-')


exec @rc = sp_trace_create @TraceID output, 0, @MyFileName, @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on bit
set @on = 1 --sp_trace_setevent @traceid ,@eventid ,@columnid,@on

-- Capturing the RPC:Completed event

EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 17, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 8, @on
EXEC sp_trace_setevent @TraceID, 10, 10, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 35, @on

--Capturing the SQL:BatchCompleted event

EXEC sp_trace_setevent @TraceID, 12, 16, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 17, @on
EXEC sp_trace_setevent @TraceID, 12, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 18, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on
EXEC sp_trace_setevent @TraceID, 12, 8, @on
EXEC sp_trace_setevent @TraceID, 12, 10, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 35, @on

-- Set the Filters
EXEC sp_trace_setfilter @TraceID,11,0,7,N'%NT AUTHORITY%' --SQL login
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'RdsAdminService'

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- display trace id and file name, Note these details for future references
SELECT @TraceID,@MyFileName

goto finish

error:
select ErrorCode=@rc

finish:
go

2. Start the Trace using below command :

-- To Start the trace (Please update the trace_id as you may recived at the time of creation)
Exec sp_trace_setstatus @traceid = 2 , @on = 1 -- start

3. Start the application test run

4. Once test run is completed, Stop the trace using below command:

-- To Stop the trace (Please update the trace_id as you may recived at the time of creation)

Exec sp_trace_setstatus @traceid = 2 , @on = 0 -- stop
-- To Delete the trace, once application test is completed.
--Exec sp_trace_setstatus @traceid = 2 , @on = 0 -- Delete definition from server

5. Insert the trace output in dummy table, this helps to get the data in easy to read format.

USE [DB_name]
GO
SELECT * INTO RDSSQLTrace_20210722_1215 FROM ::fn_trace_gettable ('D:\RDSDBDATA\Log\RDSSQLTrace_2021-07-22_12-15-19.trc', default)
6. Capture the output of below commands and update the output in the attached excel sheet template in the TraceData sheet (File Name : Capture_ExpensiveQueries_Template.xlsx)

--To collect queries based on max Duration 
SELECT TOP 10 StartTime,EndTime,Reads, Writes, 
Duration/1000000 DurationSec,CPU, TextData,RowCounts,sqlHandle,[DatabaseName],
SPID,LoginName,EventClass FROM [gptst].[dbo].[RDSSQLTrace_20210722] 
ORDER BY Duration DESC

--To collect queries based on max CPU utilization
SELECT TOP 10 StartTime,EndTime,Reads, Writes, 
Duration/1000000 DurationMs,CPU, TextData,RowCounts,sqlHandle,[DatabaseName],
SPID,LoginName,EventClass FROM [gptst].[dbo].[RDSSQLTrace_20210722] 
ORDER BY CPU DESC

--To collect queries based on max Reads,Writes
SELECT TOP 10 StartTime,EndTime,Reads, Writes, 
Duration/1000000 DurationSec,CPU, TextData,RowCounts,sqlHandle,[DatabaseName],
SPID,LoginName,EventClass FROM [gptst].[dbo].[RDSSQLTrace_20210722]
ORDER BY Reads,Writes DESC

7. Also, capture the output of the below scripts as well and update the same in the attached excel sheet template in the CacheData sheet (File Name : Capture_ExpensiveQueries_Template.xlsx)

--TOP 10 Queries by Duration (Avg Sec) from system dmv's		
			
SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
o.name AS ObjectName,
qs.total_worker_time / 1000000 / qs.execution_count As Avg_CPU_time_sec,
qs.total_worker_time / 1000000 as Total_CPU_time_sec,
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS Average_Seconds,
qs.total_elapsed_time / 1000000.0 AS Total_Seconds,
'',
qs.execution_count as Count,
qs.last_execution_time as Time,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
--,qt.text [MainQuenry]
--,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qs.execution_count>5 and DB_NAME(qt.dbid) not in ( 'master','msdb','model')
--and DB_NAME(qt.dbid) is not null
ORDER BY average_seconds DESC

--TOP 10 Queries by CPU (Avg CPU time) from system dmv's

SELECT TOP 10 DB_NAME(qt.dbid) as DBName,
	qs.total_worker_time / 1000000 / qs.execution_count AS Avg_CPU_time_sec,
	qs.total_worker_time / 1000000 As Total_CPU_time_sec,
	qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
	qs.total_elapsed_time / 1000000 As Total_Seconds,
    (total_logical_reads + total_logical_writes) / qs.execution_count as Average_IO,
	total_logical_reads + total_logical_writes as Total_IO,	
    qs.execution_count as Count,
    qs.last_execution_time as Time,
	SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
		(
			(
				CASE qs.statement_end_offset
					WHEN -1 THEN DATALENGTH(qt.[text])
					ELSE qs.statement_end_offset
				END - qs.statement_start_offset
			) / 2
		) + 1
	) as Query
   ,qt.text
	,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where 
DB_NAME(qt.dbid) not in ( 'master','msdb','model')
ORDER BY Avg_CPU_time_sec DESC

-- Top 10 query by IO (Avg IO) from system dmv's

SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
o.name AS ObjectName,
qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
qs.total_elapsed_time / 1000000 As Total_Seconds,
(total_logical_reads + total_logical_writes ) / qs.execution_count AS Average_IO,
(total_logical_reads + total_logical_writes ) AS Total_IO,
'',
qs.execution_count AS Count,
last_execution_time As Time,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
--,qt.text
--,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where last_execution_time > getdate()-1 
and DB_NAME(qt.dbid) not in ( 'master','msdb','model')
--and DB_NAME(qt.dbid) is not null
and qs.execution_count > 5 
ORDER BY average_IO DESC

Note : Please do capture the output after each run if running the test multiple times, to compare the result set.

SQL Server : Powershell Commands for Basic Health Check

Here are the few powershell commands to do basic health check of MS SQL Server Instance:

1) SQL Database Status

invoke-sqlcmd -query "SELECT SERVERPROPERTY('ServerName') as SQLInstance,name as DBName,
state_desc as State from sys.databases;" -ServerInstance  | Format-table –AutoSize;

2) SQL Database File Size

invoke-sqlcmd -query "DECLARE @command varchar(1000); SELECT @command = 
'USE ? SELECT SERVERPROPERTY(''ServerName'') as SQLInstance, name as DB_FileName,
convert(varchar(100), FileName) as FileName, cast(Size as decimal(15,2))*8/1024 as ''SizeMB'',
FILEPROPERTY (name, ''spaceused'')*8/1024 as ''UsedMB'', Convert(decimal(10,2),convert(decimal(15,2),
(FILEPROPERTY (name, ''spaceused'')))/Size*100) as PercentFull,filegroup_name(groupid) as ''FileGroup'',
FILEPROPERTY (name, ''IsLogFile'') as ''IsLogFile''  FROM dbo.sysfiles order by IsLogFile'
EXEC sp_MSforeachdb @command" -ServerInstance  | Format-table –AutoSize;

3) SQL Instance Blocking & Session details

invoke-sqlcmd -query "SELECT SERVERPROPERTY('ServerName') as SQLInstance,s.session_id,
r.blocking_session_id,db_name(r.database_id) as [Database],r.wait_time, s.login_name,
s.cpu_time/60000 as[CPU_Time(mins)],s.memory_usage, r.[status], r.percent_complete,r.nest_level, [Text]
from sys.dm_exec_sessions as s 
inner join 
sys.dm_exec_requests as r 
on s.session_id = r.session_id cross apply sys.dm_exec_sql_text (sql_handle) 
where s.status = 'running' order by s.cpu_time desc;" -ServerInstance  | Format-table –AutoSize;

Here are the few powershell commands to get the health check at Windows Level:

1) Drive Free Space

Get-WmiObject -Class win32_Volume | Select-object Name, Label, 
@{Name="Size(GB)";Expression={[decimal]("{0:N0}" -f($_.capacity/1gb))}}, 
@{Name="Free Space(GB)";Expression={[decimal]("{0:N0}" -f($_.freespace/1gb))}}, 
@{Name="Free (%)";Expression={"{0,6:P0}" -f(($_.freespace/1gb) / ($_.capacity/1gb))}} 
| Format-table –AutoSize;

2) Top 5 CPU Utilization

Get-Process | Sort-Object -Property CPUPercent -Descending | Select-Object -First 10 
| Select-Object -Property  @{ Name = "TimeStamp"; Expression = {Get-Date}}, Name, CPU, 
@{Name = "CPUPercent"; Expression = {$TotalSec = (New-TimeSpan -Start $_.StartTime).TotalSeconds; 
[Math]::Round( ($_.CPU * 100 / $TotalSec), 2)}}, Description | Format-table –AutoSize;

3) Top 5 Memory Utilization

Get-Wmiobject -Class WIN32_PROCESS | Sort-Object -Property ws -Descending | Select-Object -First 5 
| Select-Object -Property @{ Name = "TimeStamp"; Expression = {Get-Date}}, processname, 
@{Name="Mem Usage(MB)";Expression={[math]::round($_.ws / 1mb)}},
@{Name="ProcessID";Expression={[String]$_.ProcessID}},
@{Name="UserID";Expression={$_.getowner().user}} | Format-table –AutoSize;

SQL Server Reporting Services Migration

Migrate Reporting Services Database to another Instance for 2008/2012

Let suppose there are two SQL Server Instances:
SQLServerA
SQLServerB
Here we are migrating Reports from
Source Server - SQLServerA to Target Server - SQLServerB

1). Backup the encryption key and the RS Databases - ReportServer & ReportServerTempdb database from SQLServerA
2). Stop the reporting services in SQLServerB
3). Restore these databases on SQLServerB on with target reporting database name (ReportServerTempdb & ReportServer)
4). Start reporting services on SQLServerB
5). Reset the database connection to ReportingServices on the target machine using Microsoft Reporting Services Configuration Manager
6). Restore the encryption key on SQLServerB
---Restore the encryption key from the backup which you have taken in step 1

After once you open the URL of target server, you might get an error stated -
Scale-out deployment configuration error:
This is because when doing step6 the old server will be added for scale-out deployment on the target machine. If the source and target machine are using different licenses of Reporting Services you might encounter issues that some features are not supported when migrating to a less featured sql server license.

The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)
Normally that should not impose a problem since you would be able to remove the old server from scale-out deploment from the list in Microsoft Reporting Services Configuration Manager.

Solution:
7). On the SQLServerA
Run this command in Query analyser

For SQL2008/2008R2

SELECT * from ReportServer.dbo.Keys

For SQL2012

SELECT * from ReportServer2012.dbo.Keys

and make note of the InstallationId value for the non-null record
8). On ServerB server,
Run this command in Query analyser

For SQL2008/2008R2

SELECT * from ReportServer.dbo.Keys

For SQL2012

SELECT * from ReportServer2012.dbo.Keys

and you should see 3 records or more. One null record, and other records that have values in the MachineName field (these should be the old and new servers name). The InstallationId value from previous step should be in there with the old server's name
9). On the SQLServerB server, delete the record that matches the old server's InstallationId.
for example in this case:
Run this command in Query analyser

DELETE FROM [ReportServer].[dbo].[Keys]
WHERE MachineName = 'SQLServerA'

How to configure URL:
Default URL:
https://servername/Reports/Pages/Folder.aspx

Like in ServerA for default instance:
https://ServerA/Reports/Pages/Folder.aspx

Named instance like ServerA\Dev
http:// ServerB/Reports_Dev/Pages/Folder.aspx

restart the reporting services later and check your reporting services.

SQL Server Master DB log backup not allowed

SYMPTOMS

If you create a Database Maintenance Plan for all the system databases or if you select the master database and you select the Back up the transaction log as part of the maintenance plan option, the Backup transaction log step for the master database fails with this error message:
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]

Backup can not be performed on database 'master'. This sub task is ignored.


CAUSE

Only full database backups of the master database is allowed, the tlog backup is not allowed even if the Recovery modal is set to Full.

The master database should be kept as the default in the Simple Recovery model. You must create a separate maintenance plan for the master database and not backup the transaction log in the maintenance plan. Create additional maintenance plans as needed for other databases for which you want to backup the transaction log.

If you want, you can set the msdb database to the Full Recovery model and perform transaction log backups. If the recovery model for msdb is set to "simple" the backup transaction log step in the maintenance plan fails with the error message shown in the "Summary" section.

SQL Server Page Type

Types of Data Pages

In SQL Server, we have different type of Pages and its consists of numbers of 8K pages.
A Page includes 8192 bytes. First 96 bytes are used for header. The rest of the space is for data.
A variable length row offset array (or slot array) is located at the end of every page and grows backwards. Count of records is saved in the header.
The size of each element in the offset array is 2 bytes. Records in a page are not sorted even though it is an index page.
If the data needs to be sorted, the offset array will be is sorted by the key of the index.

Here is the Structure of the Data Page:

There are around 17 types of page in SQL Server data file and the arrangement of the data pages is same as shown below:

     Type 1 – Data page
       - Data records in heap
       - Clustered index leaf-level
       - Location can be random

    Type 2 – Index page
       - Non-clustered index
       - Non-leave-level clustered index
       - Location can be random

    Type 3 – Text Mixed Page
       - Small LOB value(s), multiple types and rows.
       - Location can be random

    Type 4 – Text Page
       - LOB value from a single column value
       - Location can be random

    Type 7 – Sort Page
       - Temporary page for sort operation.
       - Usually tempdb, but can be in user database for online operations.
       - Location can be random

    Type 8 – GAM Page
       - Global Allocation Map, track allocation of extents.
       - One bit for each extent, if the bit is 1 then its free, otherwise its allocated.
       - The first GAM page in each file is page 2

    Type 9 – SGAM Page
       - Shared Global Allocation Map, track allocation of shared extents
       - One bit for each extent, if the bit is 1, then it has free space, otherwise its full
       - The first SGAM page in each file is page 3

    Type 10 – IAM Page
       - Index Allocation Map. Extent allocation in a GAM interval for an index or heap table.
       - Location can be random.

    Type 11 – PFS Page
       - Page Free Space. Byte map, keeps track of free space of pages
       - The first PFS is page 1 in each file.

    Type 13 – Boot Page
       - Information about the page
       - Only page 9 in file 1.
       - To review "What's on boot page?" run below commands:

         DBCC DBINFO (‘DB_Name’);
         GO
                OR
         DBCC PAGE(DB_Name, 1, 9, 3)
         GO
       - More details click here to refer Paul's Article
    
    Type 14 – Server Configuration Page (Don't know the official name)
       - Part of information returned from sp_configure.
       - It only exists in master database, file 1, page 10

    Type 15 – File Header Page
       - Information about the file.
       - It’s always page 0 every data page.
       - To review this page, run below command:

         DBCC PAGE(DB_name, 1, 0, 3)
         DBCC PAGE(DB_name, 1, 0, 3) WITH TABLERESULTS
         GO

    Type 16 – Differential Changed map
       - Extents in GAM interval have changed since last full or differential backup
       - The first Differential Changed Page is page 6 in each file

    Type 17 – Bulk Change Map
       - Extents in GAM interval modified by bulk operations since last backup
       - The first Bulk Change Map page is page 7 in each file.

SQL Server Evolution from 2000 to 2016

The Evolution of the Microsoft SQL Server

---

SQL Server 2016

New Features/Enhancement

- Stretch Database : The idea behind this feature is certainly interesting. The upcoming stretch database feature will allow you to dynamically stretch your on-premise database to Azure. This would enable your frequently accessed or hot data to stay on-premise and your infrequently accessed cold data to be moved to the cloud.
- Always Encrypted : Always Encrypted is designed to protect data at rest or in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and the encryption key can reside with the application. Encryption and decryption of data happens transparently inside the application.
- Enhancements to AlwaysOn : SQL Server 2016 will also continue to advance high availability and disaster recovery with several enhancements to AlwaysOn. The upcoming SQL Server 2016 release will enhance AlwaysOn with the ability to have up to three synchronous replicas. Additionally, it will include DTC (Distributed Transaction Coordinator) support as well as support for round-robin load balancing of the secondary replicas. There will also be support for automatic failover based on database health.
- Enhanced In-Memory OLTP : First introduced with SQL Server 2014, In-Memory OLTP will continue to mature in SQL Server 2016. Microsoft will enhance In-Memory OLTP by extending the functionality to more applications while also enhancing concurrency. This means they will be expanding the T-SQL surface area, increasing the total amount of memory supported into the terabyte range as well as supporting a greater number of parallel CPUs.

BI Features

- Revamped SQL Server Data Tools : Another welcome change in SQL Server 2016 is the re-consolidation of SQL Server Data Tools (SSDT). As Microsoft worked to supplant the popular and useful Business Development Studio (BIDS) with SQL Server Data Tools they wound up confusing almost everyone by creating not one but two versions of SQL Server Data Tools both of which needed to be downloaded separately from installing SQL Server itself. With the SQL Server 2016 release Microsoft has indicated that they intend to re-consolidation SQL Server Data Tools.

---

SQL Server 2014

New Features/Enhancement

- In-Memory OLTP Engine : SQL Server 2014 enables memory optimization of selected tables and stored procedures. The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays. Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2012 when using this new feature.
- Clusteredr ColumnStore Index : When Microsoft introduced the columnstore index in QL Server 2012, it provided improved performance for data warehousing queries. For some queries, the columnstore indexes provided a tenfold performance improvement. However, to utilize the columnstore index, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction with the new updateable Columnstore Index. The SQL Server 2014 Columnstore Index must use all the columns in the table and can’t be combined with other indexes.
- Backup to Windows Azure : SQL Server 2014’s native backup supports Windows Azure integration. Although I’m not entirely convinced that I would want to depend on an Internet connection to restore my backups, on-premises SQL Server 2014 and Windows Azure virtual machine (VM) instances support backing up to Windows Azure storage. The Windows Azure backup integration is also fully built into SQL Server Management Studio (SSMS).
- AlwaysOn Enhancements : Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.
- Buffer Pool Extension : SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). With the new Buffer Pool Extensions feature, you can use SSD drives to expand the buffer pool in systems that have maxed out their memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads.
- Backup Encryption : One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.

BI Features

- Power View for Multidimensional Models : Power View used to be limited to tabular data. However, with SQL Server 2014, Power View can now be used with multidimensional models (OLAP cubes) and can create a variety of data visualizations including tables, matrices, bubble charts, and geographical maps. Power View multidimensional models also support queries using Data Analysis Expressions (DAX). - SQL Server Data Tools : The new SQL Server Data Tools for BI (SSDT-BI) is used to create SQL Server Analysis Services (SSAS) models, SSRS reports, and SSIS packages. The new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014.

---

SQL Server 2012

New Features/Enhancement

- AlwaysOn : SQL Server AlwaysOn provides a high-availability and Disaster-recovery solution for SQL Server 2012. Click here for more details
- Non-Clustered ColumnStore Index : non-Updateable, click here to get more details
- Contained database : This is a great feature for people who have to go through pain of SQL Server database migration again and again. One of the biggest pains in migrating databases is user accounts. SQL Server user resides either in windows ADS or at SQL Server level as SQL Server users. So when we migrate SQL Server database from one server to other server these users have to be recreated again.
- User Defined Server roles : In SQL Server 2008 R2 we had the ability to create roles at database level. So you create customized roles at the database level and then assign them to users. But at the server level or instance level we did not have options of creating server roles. So if you right click on the “Server roles” you will not find any options for adding new server roles.

BI Features

- Tabular Model (SSAS) - Data Quality Service (DQS) :
- Power View :
- Cloud :

---

SQL Server 2008/R2

New Features/Enhancement

- Master Data Services : Master Data Services might be the most underrated feature in SQL Server 2008 R2. It provides a platform that lets you create a master definition for all the disparate data sources in your organization. Almost all large businesses have a variety of databases that are used by different applications and business units. These databases have different schema and different data meanings for what’s often the same data.
- Multiserver Management : SQL Server Utility Control Point
- Backup Encryption : Executed at backup time to prevent tampering.
- Data Compression : Fact Table size reduction and improved performance.
- File Stream : New data type VarBinary(Max) FileStream for managing binary data.
- Full Text Search : Native Indexes, thesaurus as metadata, and backup ability.
- Change Data Capture : For requirements to save or monitor historical information on changed data. Using SQL 2008 we can implement a non-invasive detection of changed records.
- Resource Governor : Very cool. Throttle the resources of users based on Memory or Processor

BI Features

- SQL Server Integration Service : Improved multiprocessor support and faster lookups.
- PowerPoint :
- Sharepoint Integration :

---

SQL Server 2005

New Features/Enhancement

- T-SQL (Transaction SQL) enhancements : new features including error handling via the TRY and CATCH paradigm, Common Table Expressions (CTEs), which return a record set in a statement, and the ability to shift columns to rows and vice versa with the PIVOT and UNPIVOT commands.
- Service Broker : The Service Broker handles messaging between a sender and receiver in a loosely coupled manner. A message is sent, processed and responded to, completing the transaction.
- Data encryption : SQL Server 2000 had no documented or publicly supported functions to encrypt data in a table natively. Organizations had to rely on third-party products to address this need. SQL Server 2005 has native capabilities to support encryption of data stored in user-defined databases.
- SMTP mail : Sending mail directly from SQL Server 2000 is possible, but challenging. With SQL Server 2005, Microsoft incorporates SMTP mail to improve the native mail capabilities.
- Multiple Active Result Sets (MARS) : MARS allow a persistent database connection from a single client to have more than one active request per connection. This should be a major performance improvement, allowing developers to give users new capabilities when working with SQL Server. For example, it allows multiple searches, or a search and data entry. The bottom line is that one client connection can have multiple active processes simultaneously.
- Dedicated administrator connection (DAC) : If all else fails, stop the SQL Server service or push the power button. That mentality is finished with the dedicated administrator connection. This functionality will allow a DBA to make a single diagnostic connection to SQL Server even if the server is having an issue.
- Database Mirroring : Database mirroring is an extension of the native high-availability capabilities.

BI Features

- SQL Server Integration Services (SSIS) : SSIS has replaced DTS (Data Transformation Services) as the primary ETL (Extraction, Transformation and Loading) tool and ships with SQL Server free of charge.
- Analysis Services (SSAS) and Reporting Services (SSRS)

---

SQL Server 2000

Refer the msdn Article for more details Click here

New Features/Enhancement

- Log Shipping
- Replication
- Clustering

BI Features

- Data Transformation Services (DTS)

---

SQL Server Stretch Database

SQL Server 2016 Stretch Database - - Concept and Architecture

I have attended one interview a long back and an Interviewer asked me "What is Stretch Database?" and I was like (o_o)-???? This is something I never heard. So I said I'm not aware. so he said this is new feature in SQL Server, try to get the details about it. Lucky they have not selected me.. :). I decided to get the Notes about Stretch Database.

Stretch Database

This is the new feature in SQL Server 2016.
Stretch Database allows you to archive your historical data transparently and securely. In SQL Server 2016 Community Technology Preview 2 (CTP2), Stretch Database stores your historical data in the Microsoft Azure cloud. After you enable Stretch Database, it silently migrates your historical data to an Azure SQL Database. Few silent features as below:
- You don't have to change existing queries and client Apps. You continue to have seamless access to both local and remote data.
- Your local queries and database operations against current data typically run faster.
- You typically enjoy reduced cost and complexity.

This DB offers local server performance for hot data and cloud storage for old data without any change to the application. The basic use case is a table that contain a small amount of hot data that users normally care about and a large amount of old data that should have been moved to off-line archive but the users still able to query\access it.

Stretch Database Architecture

Stretch Database leverage's the resources in Microsoft Azure to offload archival data storage and query processing.
When you enable Stretch Database on a database, it creates a secure linked server definition in the on-premises SQL Server. This linked server definition has the remote endpoint as the target. When you enable Stretch Database on a table in the database, it provisions remote resources and begins to migrate eligible data, if migration is enabled.
Queries against tables with Stretch Database enabled automatically run against both the local database and the remote endpoint. Stretch Database leverage's processing power in Azure to run queries against remote data by rewriting the query. You can see this rewriting as a "remote query" operator in the new query plan.

What kind of databases and tables are candidates for Stretch Database?

Stretch Database targets transactional databases with large amounts of historical data, typically stored in a small number of tables. These tables may contain more than a billion rows.
In SQL Server 2016 Community Technology Preview 2 (CTP2), Stretch Database migrates entire tables. This assumes that you already move historical data into a table that's separate from current data.
Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade Advisor, to identify databases and tables for Stretch Database. For more info, For more details see Identify databases and tables for Stretch Database.

What happens when you enable Stretch Database?

After you enable Stretch Database for a local server instance, a database, and at least one table, it silently begins to migrate your historical data to an Azure SQL Database. You can pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth.
Migration: Stretch Database ensures that no data is lost if a failure occurs during migration. It also has retry logic to handle connection issues that may occur during migration. A dynamic management view provides the status of migration.
Querying: You don't have to change existing queries and client apps. You continue to have seamless access to both local and remote data, even during data migration. There is a small amount of latency for remote queries, but you only encounter this latency when you query the historical data that's archived remotely.

Refer MS article for more details Stretch Database.

SQL Server Performance Tunning Ques & Ans Part-2

SQL Server Performance Tuning Question and Answers

Q. How to read the graphical execution plan?
A: The plan should be read from right to left
- Check the Graphical execution plan of a stored procedure / Query.
- Table Scan – Index is missing.
- Index Scan – Proper indexes are not using.
- BookMark Lookup – Limit the number of columns in the select list.
- Filter – Remove any functions from where clause, May require additional indexes.
- Sort – Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?.
- DataFlow Arrow – High density: Sometimes you find few rows as outcome but the arrow line density indicates the query/proc processing huge number of rows.
- Cost – Can easily find out which table / operation taking much time
- From the execution plan we can find out the bottleneck and give the possible solution to avoid the latency.
For more details click here
Q. What will be the reason if Actual and Estimated Execution Plans are having differ?
A: - When Statistics are Stale:
The main cause of a difference between the plans is differences between the statistics and the actual data. This generally occurs over time as data is added and deleted.

- When the Estimated plan is invalid:
When the batch contains temporary tables or the T-SQL statements which refers some of the objects that are not currently existed in the database, but will be created once the batch is run. (Create table is there in batch)

Q. What are the types of SQL Server Waits?
A: In general there are three categories of waits that could affect any given request:
- Resource waits: are caused by a particular resource, perhaps a specific lock that is unavailable when the requested is submitted.
- External waits: occur when SQL Server worker thread is waiting on an external process.
- Queue waits: normally apply to internal background tasks, such as ghost cleanup, which physically removes records that have been previously deleted.
Using below DMV, we can get the wait details for each SPid's:
SELECT * From sys.dm_os_wait_stats

Q. Explain three different approaches to capture a query plan?
A: Here are the way to capture/read query plan:
i) SHOWPLAN_TEXT
ii) SHOWPLAN_ALL
iii) Graphical Query Plan

Q. Explain different types of Locks in SQL Server?
A: There are 3 types of locks in SQL Server
i) Shared locks - they are used for operations which do not allow any change or update of data. For e.g. SELECT.
ii) Update locks - they are used when SQL Server wants to modify a page. The update page lock is then promoted to an exclusive page lock before actually making the changes.
iii) Exclusive locks - they are used for the data modification operations. For e.g. UPDATE, INSERT, or DELETE.

Q. What are the basic Perfmon Counters would you consider while troubleshooting the performance issue?
A: Here are few basic counters which can help to troubleshoot the issue:
CPU bottlenecks:

- Signal waits > 25% of total waits.
(See sys.dm_os_wait_stats for Signal waits and Total waits. Signal waits measure the time spent in the runnable queue waiting for CPU. High signal waits indicate a CPU bottleneck.)
- Plan re-use < 90%.
(A query plan is used to execute a query. Plan re-use is desirable for OLTP workloads because re-creating the same plan (for similar or identical transactions) is a waste of CPU resources. Compare SQL Server SQL Statistics: batch requests/sec to SQL compilations/sec. Compute plan re-use as follows: Plan re-use = (Batch requests – SQL compilations) / Batch requests. Special exception to the plan re-use rule: Zero cost plans will not be cached (not re-used) in SQL 2005 SP2. Applications that use zero cost plans will have a lower plan re-use but this is not a performance issue.)

Memory bottleneck:

- Page Life Expectancy – Number of seconds a page will stay in the buffer pool without references
Consistently low average page life expectancy. (MSSQL$Instance: Buffer Manager\Page Life Expectancy:)

(See Average Page Life Expectancy Counter which is in the Perfmon object SQL Server Buffer Manager (this represents is the average number of seconds a page stays in cache). For OLTP, an average page life expectancy of 300 is 5 minutes. Anything less could indicate memory pressure, missing indexes, or a cache flush)
- Buffer Cache Hit Ratio – Indicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.
Consistently low SQL Cache hit ratio. (MSSQL$Instance: Plan Cache\Cache Hit Ratio:)

(OLTP applications (e.g. small transactions) should have a high cache hit ratio. Since OLTP transactions are small, there should not be (1) big drops in SQL Cache hit rates or (2) consistently low cache hit rates < 90%. Drops or low cache hit may indicate memory pressure or missing indexes.)

IO bottleneck:

- High average disk seconds per read.
(When the IO subsystem is queued, disk seconds per read increases. See Perfmon Logical or Physical disk (disk seconds/read counter). Normally it takes 4-8ms to complete a read when there is no IO pressure. When the IO subsystem is under pressure due to high IO requests, the average time to complete a read increases, showing the effect of disk queues. Periodic higher values for disk seconds/read may be acceptable for many applications. For high performance OLTP applications, sophisticated SAN subsystems provide greater IO scalability and resiliency in handling spikes of IO activity. Sustained high values for disk seconds/read (>15ms) does indicate a disk bottleneck.)
- High average disk seconds per write.
(See Perfmon Logical or Physical disk. The throughput for high volume OLTP applications is dependent on fast sequential transaction log writes. A transaction log write can be as fast as 1ms (or less) for high performance SAN environments. For many applications, a periodic spike in average disk seconds per write is acceptable considering the high cost of sophisticated SAN subsystems. However, sustained high values for average disk seconds/write is a reliable indicator of a disk bottleneck.)
- Big IOs such as table and range scans due to missing indexes.

Blocking bottleneck:

- High average row lock or latch waits.
(The average row lock or latch waits are computed by dividing lock and latch wait milliseconds (ms) by lock and latch waits. The average lock wait ms computed from sys.dm_db_index_operational_stats represents the average time for each block.)
- Top wait statistics
- High number of deadlocks.
(See Profiler “Graphical Deadlock” under Locks event to identify the statements involved in the deadlock.)

Network bottleneck:

- High network latency coupled with an application that incurs many round trips to the database.
- Network bandwidth is used up.
(See counters packets/sec and current bandwidth counters in the network interface object of Performance Monitor. For TCP/IP frames actual bandwidth is computed as packets/sec * 1500 * 8 /1000000 Mbps)

Must refer Technet Article about Perfmon Counter

SQL Server DBA Basic Ques & Ans Part-5

SQL Server Database Administrator Questions & Answers
Q. How to Rebuild System databases?
A: System databases must be rebuilt to fix corruption problems in the master, model, msdb, or resource system databases or to modify the default server-level collation.

The following procedure rebuilds the master, model, msdb, and tempdb system databases. You cannot specify the system databases to be rebuilt. For clustered instances, this procedure must be performed on the active node and the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure.
This procedure does not rebuild the resource database.

This is very critical task especially if you are performing on Production Server, so ensure you list out all the pre/post steps. Follow the below steps to rebuild system databases:
1. Run Command Prompt as Administrator
1.1. From a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Release.
2. From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges.
Syntax:
If using Windows Authentication:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=DOMAIN\user_name [ /SQLCOLLATION=CollationName]

If using SQL Authentication:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Example:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=sa /SAPWD= ******
3. When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages. Examine the Summary.txt log file to verify that the process completed successfully. This file is located at C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs.





Post Steps: After rebuilding the database you may need to perform the following additional tasks:
1. Restore your most recent full backups of the master, model, and msdb databases. For more information, see Back Up and Restore of System Databases (SQL Server).
Note: If you have changed the server collation, do not restore the system databases. Doing so will replace the new collation with the previous collation setting.
If a backup is not available or if the restored backup is not current, re-create any missing entries. For example, re-create all missing entries for your user databases, backup devices, SQL Server logins, end points, and so on. The best way to re-create entries is to run the original scripts that created them.
2. If the instance of SQL Server is configured as a replication Distributor, you must restore the distribution database. For more information, see Back Up and Restore Replicated Databases.
3. Move the system databases to the locations you recorded previously. For more information, see Move System Databases.
4. Verify the server-wide configuration values match the values you recorded previously.

Q. How to Rebuild Resource Database in SQL Server?
A: The following procedure rebuilds the resource system database. When you rebuild the resource database, all service packs and hot fixes are lost, and therefore must be reapplied.
To rebuild the resource system database:
1. Launch the SQL Server Setup program (setup.exe) from the distribution media.
2. In the left navigation area, click Maintenance, and then click Repair.
3. Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
4. On the Select Instance page, select the instance to repair, and then click Next.
5. The repair rules will run to validate the operation. To continue, click Next.
5. From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.

Q. If the msdb database is damaged and you do not have a backup of the msdb database, can you to create new msdb database in SQL Server?
A: Yes, you can create new msdb by using the instmsdb script. Follow the following steps:
Note:Rebuilding the msdb database using the instmsdb script will eliminate all the information stored in msdb such as jobs, alert, operators, maintenance plans, backup history, Policy-Based Management settings, Database Mail, Performance Data Warehouse, etc.
1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server as data store.
2. Start SQL Server from the command line using the command:

NET START MSSQLSERVER /T3608
3. In another command line window, detach the msdb database by executing the following command, replacing with the instance of SQL Server:

SQLCMD -E -S -dmaster -Q"EXEC sp_detach_db msdb"
4. Using the Windows Explorer, rename the msdb database files. By default these are in the DATA sub-folder for the SQL Server instance. 5. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally. 6. In a command line window, connect to SQL Server and execute the command:

SQLCMD -E -S -i"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o" C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.out"
Replace with the instance of the Database Engine. Use the file system path of the instance of SQL Server.
7. Using the Windows Notepad, open the instmsdb.out file and check the output for any errors.
8. Re-apply any service packs or hotfix installed on the instance.
9. Recreate the user content stored in the msdb database, such as jobs, alert, etc.
10. Backup the msdb database.

Q. What are main performance monitor counters to be monitor for SQL Server?
A:Following few perfmon counters to be monitor for SQL Server:
Processor\%Processor Time: Monitoring CPU consumption allows you to check for a bottleneck on the server (indicated by high sustained usage).
High percentage of Signal Wait: Signal wait is the time a worker spends waiting for CPU time after it has finished waiting on something else (such as a lock, a latch or some other wait). Time spent waiting on the CPU is indicative of a CPU bottleneck. Signal wait can be found by executing DBCC SQLPERF (waitstats) on SQL Server 2000 or by querying sys.dm_os_wait_stats on SQL Server 2005.
Physical Disk\Avg. Disk Queue Length: Check for disk bottlenecks: if the value exceeds 2 then it is likely that a disk bottleneck exists.
MSSQL$Instance: Buffer Manager\Page Life Expectancy: Page Life Expectancy is the number of seconds a page stays in the buffer cache. A low number indicates that pages are being evicted without spending much time in the cache, which reduces the effectiveness of the cache.
MSSQL$Instance: Plan Cache\Cache Hit Ratio: A low Plan Cache hit ratio means that plans are not being reused.
MSSQL$Instance:General Statistics\Processes Blocked: Long blocks indicate contention for resources.

Q. What is “Lock Pages in Memory” option? How it will impact\useful to SQL Server?
A:Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, but depends on various conditions this option needs to be turned on.
We must be very careful in dealing with this option. One can enable this after a detailed analysis of current environment.

Following issues may rise when “Lock Pages in Memory” is not turned on:
--SQL Server performance suddenly decreases.
--Application that connects to SQL Server may encounter timeouts.
--The hardware running SQL Server may not respond for a short time periods.

Q. Task manager is not showing the correct memory usage by SQL Server. How to identify the exact memory usage from SQL Server?
A:To know the exact memory usage relay on column “physical_memory_in_use_kb” from DMV “sys.dm_os_process_memory”.
OR
Using performance counters also we can find the usage.

Performance object: Process
Counter: Private Bytes
Instance: sqlservr
Performance object: Process
Counter: Working Set
Instance: sqlservr
The Private Bytes counter measures the memory that is currently committed. The Working Set counter measures the physical memory that is currently occupied by the process.

For 64-bit sql servers we can also check the current memory usage using the below performance counter.

Performance object: SQL Server:Memory Manager
Counter: Total Server Memory (KB)

Q.I wanted to know what are the maximum worker threads setting and active worker thread count on sql server. Can you tell me how to capture this info? What’s the default value for max thread count?
A: We can check the current settings and thread allocation using the below queries.

–Thread setting
select max_workers_count from sys.dm_os_sys_info

–Active threads
select count(*) from sys.dm_os_threads
Default value is 255.

Increasing the number of worker threads may actually decrease the performance because too many threads causes context switching which could take so much of the resources that the OS starts to degrade in overall performance.