Login timeout expired. The SQL Server instance specified in SSIS service configuration is not present or is not available

We all know that SQL Server Integration services is not cluster aware. This means that we cannot set the SQL Server Integration Services to failover automatically. But still in many situations we are required to have SSIS installed on a cluster and make it work when the database engine failover or failback.
To achieve this, we have to install SSIS on all the nodes of the cluster.
Okay, I have a 2 node cluster and I have installed SSIS on both the nodes.
What next?
When we install the SSIS, the configuration file by default refers to "." which means local default instance. But in the clustered environment, we never install the database engine with the node name and hence when we try to open the Integration services it fails with the below error message.

To fix this error, we have to modify the SSIS configuration file "MsDtsSrvr.ini.xml" to refer to the correct SQL Server Instance Name.
This file will be usually located at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" and for SQL Server 2005 at "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"

Before editing the file

After editing the file, restart the SQL Server Integration services to refer to the new SQL Server instance name

Fix - The SSIS subsystem failed to load

The subsystem failure is a common error which occurs when an SQL Server instance is migrated.
When we do the migration, we usually miss to install the SQL Server binaries on the same location as it was in source. Because of this the SQL Server jobs which uses SSIS, powershell, etc... subsystems will fail.

T-SQL Query to find last run status of scheduled Jobs

Here is a T-SQL query to find the last run status of all the scheduled jobs in SQL Server.
This query will be handy when you are not able to access the "Job Activity Monitor"

USE msdb
GO
SELECT DISTINCT SJ.Name AS JobName, SJ.description AS JobDescription,
SJH.run_date AS LastRunDate, 
CASE SJH.run_status 
WHEN 0 THEN 'Failed' 
WHEN 1 THEN 'Successful' 
WHEN 3 THEN 'Cancelled' 
WHEN 4 THEN 'In Progress' 
END AS LastRunStatus
FROM sysjobhistory SJH, sysjobs SJ
WHERE SJH.job_id = SJ.job_id and SJH.run_date = 
(SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id)
ORDER BY SJH.run_date desc

T-SQL Query to find currently running jobs

Here is a T-SQL query to find the currently executing jobs.
The output of this query will be the list of jobs that are currently running along with the number of seconds it is been running.

SELECT  J.name as Running_Jobs,  
  JA.Start_execution_date As Starting_time,
        datediff(ss, JA.Start_execution_date,getdate()) as [Has_been_running(in Sec)]
FROM msdb.dbo.sysjobactivity JA

Merry-Go-Round Scans in SQL Server

There might be many reasons for choosing an enterprise edition, but the basic one will be because of the availability of the advanced features in enterprise edition which is not available in standard edition.
Because of these advanced features, you can even see some difference of performance on enterprise edition when compared to standard edition.
Merry-Go-Round Scans also known as Advanced scanning in SQL server is a feature available in SQL Server Enterprise Edition.