Sql Services Status Report Using PowerShell

There is a requirement from client that due to some ongoing issues on 10 servers client asked us to check sql services Status everyday if they are running or stopped as these servers are not yet added to Monitoring .

So, thought do this check using PowerShell that to retrieve the sql services status information. We can use Get- Service command to get required output but this works for individual server as I want to automate to retrieve information for all 10 servers in one go so, here we have used CIM commandlets to get our sql Services status report.

Also we need sql Services Start Time for all Servers. The output has the fields Hostname, Name, Caption, State, ProcessID, StartName(ServiceAccount) and StartTime.

Below is the PowerShell script to get the sql Services Running or Stopped State where you need to provide ComputerNames or ServerNames :

Invoke-Command -ComputerName DESKTOP-02JIB76 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName

From above you can see it gives all the instances installed on the Machine with Running or Stopped State, along with sql Services Start Time

You can pass Multiple ComputerNames in Script by Separating with Coma(,) like

Invoke-Command -ComputerName DESKTOP-02JIB76,Node1,Node2 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName

If you want to get information of only Running or only Stopped State Services

Invoke-Command -ComputerName DESKTOP-02JIB76 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'running'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName
Invoke-Command -ComputerName DESKTOP-02JIB76 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'Stopped'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName

Backups Information for Multiple Servers using PowerShell

Backup Information Using Powershell :

For getting our Backup Information here using SQLPS Module which was already installed in my Machine , I heard this SQLPS will get installed along with SQL 2014 client tools installed on the server.

To check if SQLPS Module is installed on your machine we can use Powershell to check this by using below command :

Get-Module -ListAvailable

Once you execute above command in PowerShell you will see all the list of modules installed in your machine

Below is the SQLPS module for SQL 2014

If you have not seen SQLPS module installed on your machine then please download it from Microsoft site and Import them using Import-Module command.

After Importing the Module you need to stop the warning message to be generated because some of the modules that uses unapproved verbs which results in throwing unnecessary warning messages so its better to suppress those warning alerts or messages. Use below command to not getting warning message:

Import-Module -Name SQLPS -DisableNameChecking

The below PowerShell Function gives you the Backup Information of Single or Multiple Instances:

#Requires -Version 3.0
function Get-BackupInfo {
<#
.SYNOPSIS
Returns database backup information for a Microsoft SQL Server database.

.DESCRIPTION
Get-BackupInfo is a function that returns database backup information for
one or more Microsoft SQL Server databases.

.PARAMETER ComputerName
The computer that is running Microsoft SQL Server that you’re targeting to
query database backup information for.

.PARAMETER InstanceName
The instance name of SQL Server to return database backup information for.
The default is the default SQL Server instance.

.PARAMETER DatabaseName
The database(s) to return backup information for. The default is all databases.

.EXAMPLE
Get-BackupInfo -ComputerName sql01

.EXAMPLE
Get-BackupInfo -ComputerName sql01 -DatabaseName master, msdb, model

.EXAMPLE
Get-BackupInfo -ComputerName sql01 -InstanceName SQL1 -DatabaseName master,msdb, model

.EXAMPLE
'master', 'msdb', 'model' | Get-BackupInfo -ComputerName sql01

.INPUTS
String

.OUTPUTS
PSCustomObject
#>

   [CmdletBinding()]
   param (
      [Parameter(Mandatory,
      ValueFromPipelineByPropertyName)]
      [Alias('ServerName','PSComputerName')]
      [string[]]$ComputerName,

      [Parameter(ValueFromPipelineByPropertyName)]
      [ValidateNotNullOrEmpty()]
      [string[]]$InstanceName = 'Default',
    
      [Parameter(ValueFromPipelineByPropertyName)]
      [ValidateNotNullOrEmpty()]
      [string[]]$DatabaseName = '*'

   )

   BEGIN {
      $problem = $false
      Write-Verbose -Message "Attempting to load SQL Module if it's not already loaded"
      if (-not (Get-Module -Name SQLPS)) {
          try {
              Import-Module -Name SQLPS -DisableNameChecking -ErrorAction Stop
          }
          catch {
              $problem = $true
              Write-Warning -Message "An error has occurred.&amp;nbsp; Error details: $_.Exception.Message"
          }
      }
   }

   PROCESS {
       foreach ($Computer in $ComputerName) {
            foreach ($Instance in $InstanceName) {
                Write-Verbose -Message 'Checking for default or named SQL instance'
                If (-not ($problem)) {
                    If (($Instance -eq 'Default') -or ($Instance -eq 'MSSQLSERVER')) {
                       $SQLInstance = $Computer
                    }
                    else {
                       $SQLInstance = "$Computer\$Instance"
                    }
                    $SQL = New-Object('Microsoft.SqlServer.Management.Smo.Server') -ArgumentList $SQLInstance
                }

                if (-not $problem) {
                     foreach ($db in $DatabaseName) {
                         Write-Verbose -Message "Verifying a database named: $db exists on SQL Instance $SQLInstance."
                         try {
                             if ($db -match '\*') {
                                  $databases = $SQL.Databases | Where-Object Name -like "$db"
                             }
                             else {
                                  $databases = $SQL.Databases | Where-Object Name -eq "$db"
                             }
                         }
                         catch {
                             $problem = $true
                             Write-Warning -Message "An error has occurred.&amp;nbsp; Error details: $_.Exception.Message"
                         }
                         if (-not $problem) {
                             foreach ($database in $databases) {
                                  Write-Verbose -Message "Retrieving information for database: $database."
                                  [PSCustomObject]@{
                                      ComputerName = $SQL.Information.ComputerNamePhysicalNetBIOS
                                      InstanceName = $Instance
                                      DatabaseName = $database.Name
                                      LastBackupDate = $database.LastBackupDate
                                      LastDifferentialBackupDate = $database.LastDifferentialBackupDate
                                      LastLogBackupDate = $database.LastLogBackupDate
                                      RecoveryModel = $database.RecoveryModel
                             }
                          }
                     }
                 }
             }
         }
      }
   }
}

Execute above function and call the function using parameters like below :

Getting Backup information for my Default Instance and output will be generating lie below

Get-BackupInfo -ComputerName DESKTOP-02JIB76 |Out-GridView 

We can retrieve Backup Information for multiple Named Instances by using below command just provide InstanceName without servername\

Get-BackupInfo -ComputerName DESKTOP-02JIB76 -InstanceName SQL1,SQL2 |Out-GridView

From above screenshot we can say only Full Backup is happening for the databases there is NO Differential and Logbackups running for databases.

If you want the output to get in PowerShell window itself instead of Gridview then run below command :

Get-BackupInfo -ComputerName DESKTOP-02JIB76 -InstanceName SQL1,SQL2 |Format-Table

Capture Blockings Information Using SP_WHOISACTIVE

Hope everyone knows about Adam Mechanic SP_whoisactive stored procedure which is very good for DBA’s to use for finding out more information what is going on Sql Server.

Below is the new link where it is moved to github

https://github.com/amachanic/sp_whoisactive

From the above link you can download the Stored Procedure and execute it on MASTER database or you can create it in a DBA owned USER DATABASE , I have created in MASTER Database

From the above screen shot you can see the details of SP_WHOISACTIVE stored procedure where we have 24 rows and we are going to pass parameters for fetching the required data using the Parameter_name column as it will be a Lightweight to execute the SP instead of executing without any parameters as it will put some pressure on Sql to get the requested output .

Today I used this SP_WHOISACTIVE stored procedure to capture Blocking information, to do that I followed below steps :

1 Downloaded SP_WHOISACTIVE from github site ( which already there in my local )
2 Executed SP in MASTER Database ( You can create in any Database)
3 Also I created One Table by passing the parameter @return_schema =1 ( This table is to Capture the Info)
4 After creating table we need to Load the Data by running the Stored Procedure with required Parameters
5 After completing above all steps now we are ready to Schedule the Agent job to Capture our Information .

You can get information of each parameter by executing the stored with parameter @help=1

With the help of below script you can get all information of what is going on in sql server instance , you can also remove some parameters which is not required for you and make your custom script .

EXEC sp_WhoIsActive
    @filter = '',
    @filter_type = 'session',
    @not_filter = '',
    @not_filter_type = 'session',
    @show_own_spid = 0,
    @show_system_spids = 0,
    @show_sleeping_spids = 1,
    @get_full_inner_text = 0,
    @get_plans = 0,
    @get_outer_command = 0,
    @get_transaction_info = 0,
    @get_task_info = 1,
    @get_locks = 0,
    @get_avg_time = 0,
    @get_additional_info = 0,
    @find_block_leaders = 0,
    @delta_interval = 0,
    @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
    @sort_order = '[start_time] ASC',
    @format_output = 1,
    @destination_table = '',
    @return_schema = 0,
    @schema = NULL,
    @help = 0

The script will give you output like below :

Now to capture information in a table to see whenever it is required for us we need to first create table which is a destination table to get all data loaded into it by executing the Store procedure SP_WHOISACTIVE.

Use master -- you can change Database Name
go

DECLARE @script_table VARCHAR(MAX)

EXEC sp_WhoIsActive

@get_plans = 1,@get_task_info = 2,@get_locks = 1   -- you can add more parameters here
,@return_schema = 1,@format_output = 1,

@schema = @script_table OUTPUT

SET @script_table = REPLACE(@script_table, '<table_name>', 'dbo.SPWHOI')--Change Table Name

PRINT (@script_table)

Output of above script you will get it in result pane, copy it and past in new window to execute the script .I have executed in MASTER database and the table was created :

CREATE TABLE dbo.SPWHOI ( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,
[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[tasks] varchar(30) NULL,[CPU] varchar(30) NULL,
[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,
[reads] varchar(30) NULL,[writes] varchar(30) NULL,[context_switches] varchar(30) NULL,[physical_io] varchar(30) NULL,
[physical_reads] varchar(30) NULL,[query_plan] xml NULL,[locks] xml NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,
[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,
[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,
[collection_time] datetime NOT NULL)

After creating Table (SPWHOI) now manually try loading the data and retrieve it to see by using below script :

EXEC sp_WhoIsActive
@get_plans = 1,@get_task_info = 2,@get_locks = 1
,@format_output = 1
,@destination_table = 'master.dbo.SPWHOI'
--truncate table master.dbo.SPWHOI
select * from master.dbo.SPWHOI

Now you can schedule Agent job to get instance information loaded into a table for every 5 minutes ( this can be changed based on your need ).when job runs data will be populated into the table and the size of the table will increase so, make sure you have enough space on the disk . Below is the code I used in Agent Jobto capture the information .

EXEC sp_WhoIsActive
@get_plans = 1,@get_task_info = 2,@get_locks = 1 --Add more parameters as per your need
,@format_output = 1
,@destination_table = 'master.dbo.SPWHOI'

Job ran and it loaded data into table SPWHOI

If you see above screenshot there was a blocking SPID recorded in our custom Table SPWHOI and also we can see the execution plan ,sql text, [dd hh:mm:ss.mss] which is Runtime of query, wait_info, blocking_session_id, login_name, hostname…e.t.c.,

You can see only blocking info with runtime of the SPID i.e., how much time it took to execute that script from the custom Table SPWHOI

select start_time ST,collection_time as CT,[dd hh:mm:ss.mss] as Running_time,* from master.dbo.SPWHOI

where blocking_session_id <>0

order by  Running_time desc 

SQL Server 2019 Requirements for Server Side and SQL Side

SQL Server 2019 Requirements:

The minimum requirements to install SQL Server 2019 on windows server operating system.

Requirements for Hardware:

SQL Server needs a minimum of 6GB available on the disk where SQL is being installed and the disk requirements will vary based on the Components you selects while installing

It requires a minimum of 1GB RAM but microsoft recommends to have minimum 4GB RAM and it also depends on the database size we may need to increase the RAM for better performance.

Note : SQL Server installation supports on x64 processor only.x86 is no longer supported by MS.

Requirements for Software :

SQL Server 2019 requires a minimum operating system of Windows Server 2016/ Windows Server 2019
It requires a .NET Framework 4.6.1 which will come pre-installed on Windows Server 2016/ Windows Server 2019
Also better to have .NET Framework 3.5 installed as Database Mail requires this.

Supported Operating systems for SQL Server 2019 :

SQL Server 2019 Supported Editions

EntDevStanWebExpress
Windows Server 2019 EnterpriseYESYESYESYESYES
Windows Server 2019 StandardYESYESYESYESYES
Windows Server 2019 DatacenterYESYESYESYESYES
Windows Server 2016 EnterpriseYESYESYESYESYES
Windows Server 2016 StandardYESYESYESYESYES
Windows Server 2016 DatacenterYESYESYESYESYES

Support on Server Core Operating System:

SQL Server 2019 installing on Server Core operating system is supported by below Windows Servers :

Windows Server 2016 Core Edition

Windows Server 2019 Core Edition

SQL Server 2019 Installation Media file can be downloaded from below link :

https://www.microsoft.com/en-in/evalcenter/evaluate-sql-server-2019

Page File Configuration :

To get a memory dump for future analysis , servers are configured by setting a pagefile with 1.5 times of Memory (1.5*Physical Memory ) .

Disk Volumes Details:

For high I/O behavior of SQL and some application we need to consider disk performance. Data and Log volumes needs to be in separate volumes or separate disks.

Example:

VolumeDrive Mount PointSize in GBDisk Speed
SQL DATAG:\SQLDATA250 GBFAST
SQL LOGSG:\SQLLOGS300GBVERY FAST
SQL TEMP LOGG:\SQLTEMPLOG50GB VERY FAST
SQL TEMP DATAG:\SQLTEMPDATA50GBFAST
SQL BACKG:\SQLBACK500 GBSTANDARD

Note : Mount point root Permissions does not inherit to the mount points . We need to explicitly copy those root permissions and set the same to the mount points.

Firewall Security :

TCP/UDPPORTDescription
TCP1433SQL Server/Availability Group listener ( Default Port can be changed )
TCP5022SQL Server DBM/AG endpoint ( Default port can be changed )
UDP1434SQL Server Browser
UDP2382SQL Server Analysis Services Browser
UDP2383SQL Server Analysis Services Listener
UDP49152-65535Dynamic TCP\UDP ( Defined Policy or by Company )

How to troubleshoot Slow-running queries on SQL Server

Steps to troubleshoot SQL Server queries that are taking too long to execute :

When a user approached you and said his query was taking too long to complete, As a DBA, we perform all of the basic checks, such as checking CPU usage, memory usage, any blockings, deadlocks, locks, fragmentation, statistics update, and so on. If the user still experiences slowness after performing all of these checks, we must follow the steps below to troubleshoot or fix slow-running queries on SQL Server.

1 Check for Index Analysis :That means we need to check if correct indexes are exist. When a user requests that a DBA study a slow-running query with which he is having problems, we can utilise Database Tunning Advisor to analyse the query and provide recommendations for indexes that will help us improve query performance. We may check if right indexes are placed in a database this manner.

2 We need to search for join hints: HASH,LOOP,MERGE, and REMOTE are the four forms of join hints. I believe SQL Server mostly use these operators: LOOP, HASH, and MERGE. This hint will override SQL Server query optimization, preventing the query optimizer from selecting a fast execution plan. Because the optimizer changes in the current SQL Server version or builds may have a negative impact on performance or may have no impact at all.

So, if the query we’re looking at has any hints, try to decrease or delete them before rerunning the query to see how it performs.

3 Check the Execution Plan: SQL query Analyser may be used to check the query execution plan. We may achieve this by selecting Display Estimated Execution Plan from the query option in the query window.

4 Check the ShowPlan Output: The ShowPlan Output provides information about the query execution plan used by SQL Server. The basic checks we must observe from the execution plan to establish if it is using the correct plan are listed below :

–> Index Usage
–> Join Order Usage
–> Join Types
–> Parallel Execution