Unable to connect SSIS from remote

Access Denied while connecting SSIS from remote.

Error:
------------------------------------------------
Cannot connect to TESTSERVER1.
------------------------------------------------
Connecting to the Integration Services service on the computer "TESTSERVER1" failed with the following error: "Access is denied".
This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does not have permission to access the SQL Server Integration Services service through DCOM.
-----------------------------------------------
To fix this error and connect the SSIS from remote, try below steps.

Add the user account to the Distributed COM Users group. To do this, follow these steps:

1. Click Start, click Run, type lusrmgr.msc, and then click OK.
2. In the Local Users and Groups dialog box, click Groups, and then double-click Distributed COM Users.
3. In the Distributed COM Users Properties dialog box, click Add.
4. In the Select Users dialog box, type the user name under Enter the object names to select, and then click OK two times. 

  Grant the appropriate permissions for the MsDtsServer DCOM application. To do this, follow these steps:

1. Click Start, click Run, type dcomcnfg, and then click OK.
2. In the Component Services dialog box, expand Component Services, expand Computers, and then expand My Computer.
3. Expand DCOM Config, right-click the MsDtsServer object, and then click Properties.
4. In the MsDtsServer (SQL Server 2005) or MsDtsServer100 (SQL server 2008) Properties dialog box, click the Security tab.
5. Under Launch and Activation Permissions, click Customize, and then click Edit.
6. In the Launch Permission dialog box, click Add.
7. In the Select Users or Groups dialog box, type the user name under Enter the object names to select, and then click OK.
8. In the Launch Permission dialog box, click the user name under Group or user names.
9. Under Permissions for UserName, click to select the Allow check box for the following permissions:
   • Local Launch
   • Remote Launch
   • Local Activation
   • Remote Activation
   Note UserName is a placeholder for the user name that you clicked in step h.
10. Click OK.
11. In the MsDtsServer (SQL Server 2005) or MsDtsServer100 (SQL server 2008) Properties dialog box, click Customize under Access Permissions, and then click Edit.
12. In the Access Permission dialog box, click Add.
13. In the Select Users or Groups dialog box, type the user name under Enter the object names to select, and then click OK.
14. In the Access Permission dialog box, click the user name under Group or user names.
15. Under Permissions for UserName, click to select the Allow check box for the following permissions:
    • Local Access
    • Remote Access
16. Click OK two times.

 Restart the SQL Server Integration Services service.

SQL Server trace for capturing connection info

-- variables
declare @rc int
declare @TraceID int
declare @maxFileSize bigint
declare @fileName nvarchar(128)
declare @on bit
set @maxFileSize = 200
set @fileName = N'C:\Trace'
set @on = 1

-- create trace
exec @rc = sp_trace_create @TraceID output, 2, @fileName, @maxFileSize, NULL
-- if error end process
if (@rc != 0) goto error

-- set the events
-- logins
exec sp_trace_setevent @TraceID, 14, 3, @on --DatabaseID
exec sp_trace_setevent @TraceID, 14, 6, @on -- NTUsername
exec sp_trace_setevent @TraceID, 14, 7, @on -- NTDomainname
exec sp_trace_setevent @TraceID, 14, 8, @on -- Hostname
exec sp_trace_setevent @TraceID, 14, 10, @on -- Applicationname
exec sp_trace_setevent @TraceID, 14, 11, @on -- Loginname
exec sp_trace_setevent @TraceID, 14, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 14, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 14, 14, @on -- Starttime
exec sp_trace_setevent @TraceID, 14, 15, @on -- Endtime
exec sp_trace_setevent @TraceID, 14, 21, @on -- Eventsubclass
exec sp_trace_setevent @TraceID, 14, 26, @on -- Servername
exec sp_trace_setevent @TraceID, 14, 35, @on -- Databasename
exec sp_trace_setevent @TraceID, 14, 40, @on -- DBusername
exec sp_trace_setevent @TraceID, 14, 51, @on -- Eventsequence
exec sp_trace_setevent @TraceID, 14, 64, @on -- Sessionloginname

-- set Filters
exec sp_trace_setfilter @TraceID, 11, 0, 6, 'testuser'

-- start the trace
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
-- error
error:
select ErrorCode=@rc
-- exit
finish:
go

--******************************************************--

-- to view the trace information
select * from :: fn_trace_getinfo(default)
-- to stop and delete the trace
exec sp_trace_setstatus @traceid, @status = 0 -- 0 stop
exec sp_trace_setstatus @traceid, @status = 2 -- 2 delete

-- load the trace into temp table
use testdb
go
select * into temp_trace
from fn_trace_gettable('C:\Trace.trc', default);
go

Specified credentials for the SQL Server/SQL Server Agent service are not valid

SQL 2008 R2 Cluster installation failed while updating service account details
Microsoft.SqlServer.Configuration.SqlEngine.ValidationException: The specified credentials for the SQL Server service are not valid. To continue, provide a valid account and password for the SQL Server service.

Below message will be logged in details
Sco.User.LookupADEntry - Attempting to find user account ABC\xxxx
Slp: Sco: Attempting to check if container 'WinNT://ABC' of user account exists
Slp: UserSecurity.ValidateCredentials -- Exception caught and ignored, exception is Access is denied.
Slp: UserSecurity.ValidateCredentials -- user validation failed

This error is due to lack of permission in AD.
The account requires Create Computer objects and Read All Properties permissions in the container that is used for computer accounts in the AD.

1. On a domain controller, click Start, click Administrative Tools, and then click Active Directory Users and Computers. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2. On the View menu, make sure that Advanced Features is selected.
   When Advanced Features is selected, you can see the Security tab in the properties of accounts (objects) in Active Directory Users and Computers.
3. Right-click the default Computers container or the default container in which computer accounts are created in your domain, and then click Properties. Computers is located in Active Directory Users and Computers/domain node/Computers.
4. On the Security tab, click Advanced.
5. Click Add, type the name of the account and then click OK.
6. In the Permission Entry for container dialog box, locate the Create Computer objects and Read All Properties permissions, and make sure that the Allow check box is selected for each one.
   
   

SQL Server Database Services feature state rule failed while adding node to cluster - SQL 2008 R2

Error:
---------------------------
Rule Check Result
---------------------------
Rule "SQL Server Database Services feature state" failed.
The SQL Server Database Services feature failed when it was initially installed. The feature must be removed before the current scenario can proceed.

Fix:
Registry key needs to be modified on Active node.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\ConfigurationStat

Change all values from 2 to 1 for all four item and run the rule check again.

Data collector - SQL Server 2008

Data collector is a component of SQL Server 2008 used to collects different sets of data. It is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server.

The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data.

Its stores the collected data in a relational database known as the management data warehouse, A relational database that enables you to manage the data that you collect by setting different retention periods for your data.

The data collector is integrated with SQL Server Agent and Integration Services, and uses both extensively. SQL Server Agent is used to schedule and run collection jobs.Integration Services (SSIS) is used to execute packages that collect data from individual data providers.

--------------------
To Configure the management data warehouse using SQL Server Management Studio.

• Ensure that SQL Server Agent is running.
• In Object Explorer, expand the Management node.
• Right-click Data Collection, and then click Configure Management Data Warehouse.

Use the Configure Management Data Warehouse Wizard to create a management data warehouse, configure logins, enable data collection, and start the System Data Collection Sets.

Here is some sample reports.

You can export the report in pdf or excel. This inbuilt tool is very easy to configure and monitor.

Microsoft SQL Server Virtual Labs

Try Microsoft SQL Server in a virtual lab and learn more about AlwaysOn, ColumnStore Index, PowerView, and other new features. Virtual labs are simple, with no complex setup or installation required.

SQL Server 2012 Virtual Labs
SQL Server 2008 R2 Virtual Labs
SQL Server 2008 Virtual Labs

Click here to go to virtual Labs.

SQL Server Performance Objects

Following objects can be used to monitor the performance of SQL Server.

Object	Purpose
SQLServer:Databases	To provide SQL Server database information. There can be more than one instance of this object.
SQLServer: Workload Group Stats	To provide Resource Governor workload group statistics.
SQLServer: Wait Statistics	To provide wait information.
SQLServer:SQL Errors	To provide SQL Server error information.
SQLServer:SQL Statistics	To provide Transact-SQL query information.
SQLServer:Memory Manager	To provide SQL Server memory usage information.
SQLServer:Latches	To provide details of the latches on internal resources that are used by SQL Server.
SQLServer:Locks	To provide details about individual lock requests made by SQL Server. There can be more than one instance of this object.
SQLServer:General Statistics	To provide general server-wide activity information.
SQLServer:Exec Statistics	To provide execution statistics information.
SQLServer: Resource Pool Stats	To provide Resource Governor resource pool statistics.
SQLServer:User Settable	To perform custom monitoring.
SQLServer:Transactions	To provide detailed information about the active transactions in SQL Server.
SQLServer:Plan Cache	To provide SQL Server cache information for objects such as query plans, and triggers.
SQL Server:HADR Availability Replica	To provide details of SQL Server AlwaysOn Availability Groups availability replicas.
SQL Server:HADR Database Replica	To provide details of SQL ServerAlwaysOn Availability Groups database replicas.
SQL Server:Deprecated Features	To record the amount of usage of a deprecated feature.
SQLServer:Database Mirroring	To provide database mirroring information.
SQLServer:CLR	To provide common language runtime, or CLR, information.
SQL Server:Buffer Node	To display statistics for the frequency of SQL Server requests and accesses to free pages.
SQLServer:Buffer Manager	To provide information about the memory buffers used by SQL Server.
SQLServer:Backup Device	To produce information on the devices used to perform backup and restore operations.
SQLServer:Access Methods	To search through SQL Server database objects to measure the allocation of these objects.
SQLServer:Cursor Manager Total	To provide cursor information.
SQLServer:Cursor Manager by Type	To record cursor information.