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

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2012 Books Online.

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (MsDtsSrvr)

------------------------------

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (Microsoft SQL Server Native Client 11.0)


SOLUTION:

Location of SQL Server Integration Services Configuration File

SQL Server Integration Service (SSIS Service) relies on “MsDtsSrvr.ini.xml” configuration file for its settings. By default the configuration file is located in %ProgramFiles%\Microsoft SQL Server\xxx\DTS\Binn folder. Where “xxx” referes to the SQL Server Version.

•  STOP SSIS Services
• Replace the Server Name node with Clustered Instance Name (replace “.” or ".\OLDSERVERNAME"  With cluster instance name i.e., SERVERNAME\INSTANCENAME)

• Restart the SQL Server Integration Service (SSIS Service).

Cluster NODE B/NODE C SQL Server Cluster Resources Failed to Come Online on a NODE A after removal and adding Node back to cluster.

Problem:
On a 3 Node Cluster NODE B/NODE C SQL Server Cluster Resources Failed to Come Online on a NODE A after removal and adding Node back to cluster.

Event ID 1069: Cluster resource 'SQL Server (SQLPC)' in clustered service or application 'SQL (C)' failed.

Event ID 1230 : Cluster resource 'SQL Server (SQLPC)' (resource type '', DLL 'sqsrvres.dll') either crashed or deadlocked. The Resource Hosting Subsystem (RHS) process will now attempt to terminate, and the resource will be marked to run in a separate monitor.


In this picture we can note that except SQL Server Resources all other resources are coming online.


Issue Caused:
SQL Server Services are missing in services.msc for the rest of the nodes hence resource is not coming online.

But SQL Services of NODE A will come online as services are present for this instance.
 



 

Resolution:

S1: NODE A: Move the instance to C.
S2: NODE B: Move the instance to C.
S3: Make sure NODE A and NODE B are passive.
S4: Now on NODE A, Start add node from CMD/GUI.

E:\SQL 2012 Ent Edition with SP2 64bit\Setup.exe /SkipRules=Cluster_VerifyForErrors /Action=AddNode

S5: Now select NODE B, i.e SQLPB and click install and follow on screen guidelines (this will install services)
S6: Install SP and CU applicable on NODE A only for SQLPB (NODE B Instance) to bring instance to the same level .
S7: Verify the SQL Server Services are installed.
S8: Now move Node A-A, B-B verify
S9: Now move Node B- A.
S10: All resources will be online.

Unable to rename ' DBNAME'

ADDITIONAL INFORMATION:

Rename failed for Database 'XXXXXX'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0026+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Database&LinkId=20476





Solution :

use master
GO

ALTER DATABASE SI SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE SI MODIFY NAME = [SI2]

ALTER DATABASE SI2 SET MULTI_USER

Tempdb Corruption

For the longest time the guidance around tempdb is that if you shrink it on a live system then it could cause tempdb corruption.

http://www.sqlskills.com/blogs/paul/shrinking-tempdb-longer-prohibited/

http://support.microsoft.com/kb/307487/en-us

Remember though, shrinking should be a rare operation, whether data or log file shrinking – and never a regular operation.

http://kmp.dell.com/services/kedge/application_services/databases/f/601/t/6558.aspx

The EXECUTE permission was denied on the object 'sp_help', database 'mssqlsystemresource', schema 'sys'.

ERROR:

Msg 229, Level 14, State 5, Procedure sp_help, Line 1

The EXECUTE permission was denied on the object 'sp_help', database 'mssqlsystemresource', schema 'sys'.

Solution:
 Use Master

Go

GRANT EXECUTE ON sp_help to Public

Encryption Key for the Reporting Services Fails

ERROR:

ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. 
---> System.Runtime.InteropServices.COMException (0XXXXXX): Password doesn't meet the requirements of the filter dll's 

Solution :

Remove the custom password filter on:
         HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\LSA - Notification Packages

Installing SQL Server 2012 or 2014 on Windows 2003

SQL Server 2012 or 2014 cannot be installed on the Windows 2003 Server.

Hardware Software Requirements for SQL Server 2012 : http://msdn.microsoft.com/en-us/library/ms143506%28v=sql.110%29.aspx