SQL Server: How to use NamedPipe Protocol to establish connection

Error:-

 (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

To resolve this issue , we can create alias on client server using TCP/IP protocol.

But story was different here, There was a situation when an application was unable/couldn't establish connection using TCP/IP protocol. 

As a solution , I was bound to use NamedPipe protocol.

Steps to use NamedPipe protocol:-

1. Enable the NamedPipe protocol on Database server

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select NamedPipe

Right Click on NamedPipe>> Click on Enable

2. For clustered instance named S12345\SQL1 , here is is pipe name

\\.\pipe\$$\S12345\MSSQL$SQL1\sql\query

S12345 -- It is virtual SQL Server name 
SQL1 -- Instance name

3. You must restart SQL Server Services for all the changes to take effect.

4. Enable the NamedPipe protocol on Client server

5. Create Alias on Client server/Client Machine

Create 32 bit alias with following mentioned parameters

a. Alias name : S12345\SQL1
b. Pipe Name : \\S12345.ca.com\pipe\$$\S12345\MSSQL$SQL1\sql\query
c. Protocal : Named Pipe
d. Server : S12345.ca.com (FQDN)

Now, you are ready to use alias in in connection string. 

To validate whether named pipe is being used or not:-

First, you can establish connection from SSMS on client server/your laptop

Second, you can run below query and see the value in net_transport column

Select * from sys.dm_exec_connections order by 1

net_transport -- It must be Namedpipe

Hope it will help you.

Warm Regards,

Chhavinath Mishra
Sr. Specialist Database Administrator

SQL Server : To drain all the connections for a particular database

/***********************************************************************
Kill all the spid's attached to a particular database. Very useful when 
continually restoring backups for testing.

***********************************************************************/
Error Msg:

ALTER DATABASE failed because a lock could not be placed on database 'employee'. 

ALTER DATABASE statement failed.
Cannot drop database "employee" because it is currently in use.

Solution: 

To drain all the connections , use bellow SQL to kill all the connections.

use master
go
Sp_killall 'employee'

to verify, use following mentioned code

select * from sys.sysprocesses where dbid = <dbid for your database>

Note: Create this (sp_KillAll Stored Procedure) in the master database, and call it from anywhere; but you can't kill your own process!

*********************Script to create sp_KillAll Stored Procedure *****************

CREATE PROCEDURE sp_KillAll
@DbName VARCHAR(100)
AS


IF db_id(@DbName) = NULL 
BEGIN
    PRINT 'DataBase dose not Exist'
END
ELSE
BEGIN
    DECLARE @spId VARCHAR(30)
        
    DECLARE TmpCursor CURSOR FOR
    SELECT  'Kill ' + CONVERT(VARCHAR, spid)  AS spId
      FROM master..SysProcesses
      WHERE db_Name(dbID) = @DbName
      AND spId <> @@SpId
      AND dbID <> 0
      
    OPEN TmpCursor
        
    FETCH NEXT FROM TmpCursor INTO @spId 
        
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC (@spId)
     
        FETCH NEXT FROM TmpCursor INTO @spId 
    END

    CLOSE TmpCursor
    DEALLOCATE TmpCursor
END 
GO 

Warm Regards,
Chhavinath Mishra 
Sr. Specialist Database Administrator

SQL Server : Differences between SSIS 2008 and 2012

SSIS server: SSIS server is no longer a separate service that you connect to, it now appears as a node in Object Explorer when connecting to a SQL Server database instance

SSIS Catalog, which is a database that stores deployed projects, environments, and package execution logs.  Each SSIS server has one catalog.

SSIS Projects -  which is an encapsulation of multiple packages into a single unit that you build, deploy & execute.

SSIS Environments are fairly self-explanatory – they are a wrapper for all environment-specific information (e.g. Connection Strings) that you want to maintain outside of a package and when you execute a package you have to choose which Environment to execute it against. In short Environments are the replacement for SSIS configurations and they work hand-in-hand with Parameters that are also getting introduced in SSIS code-named Denali. 

Data tap: At any path, capture all the data coming through and output it to a text file to review later.  This is done without having to modify the package.

	SSIS 2008	SSIS 2012
Undo And Redo	No Undo And Redo feature in SSIS 2008	Undo And Redo feature available in SSIS 2012.
SSIS PARAMETERS	SSIS Parameters at package level	SSIS Parameters at the package level, task level and project level.
DQS TRANSFORMATION	No DQS in SSIS 2008.	DQS Transformation is available in SSIS 2012.
Change Data Capture (CDC)	Introduced in SSIS 2008. But there is no task to support CDC in SSIS 2008.	CDC ControlTask available to support CDC in SSIS 2012.
Data Flow Tap	No Data Tap Available in SSIS 2008.	Data Tap Available in SSIS 2012.
Deployment	Package Level Deployment	Project Level Deployment Package Level deployment
Logging	Disabled by default	Enabled by default and improved
Newly introduced	-	SSIS server SSIS Catalog SSIS Environments SSIS Parameters Shared Connection Managers Offline Connection Managers
Improved	-	Column mapper New Reports included [catalog].[execution_data_statistics]   Debug the Script component by setting break points
Execution	runs package locally and uses local components like drivers	When you use the “Run Package” command in SSMS, the package runs on the server,
Version control	-	Each Package is tracked and can be rolled back

Warm Regards,

Chhavinath Mishra 

Sr. Specialist Database Administrator