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

Verify whether SQL Server Service Account is locked or not

Many times, we need to ensure whether our SQL Server service account (or any other domain account) is locked or not. Here is the command which will help us to get that confirmation for us.

For example: I would like to perform this test for account named mydomin.com\sqsqlserver.

SQL Server service account name: mydomin.com\sqsqlserver.

Imp Note: We have to logged in to domain whether our account exist as this command run on primary domain controller. I always run this command from database server which belongs to same domain.

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

C:\Users\Chhavi>net user sqsqlserver /domain
The request will be processed at a domain controller for domain mydomin.com.


User name                    sqsqlserver
Full Name                    sqsqlserver
Comment                      SQL Server service account , Chhavinath Mishra
User's comment
Country/region code          000 (System Default)
Account active               Locked
Account expires              Never

Password last set            2015-05-07 5:41:32 AM
Password expires             Never
Password changeable          2015-05-08 5:41:32 AM
Password required            Yes
User may change password     Yes

Workstations allowed         All
Logon script
User profile
Home directory
Last logon                   2015-05-07 5:45:33 AM

Logon hours allowed          All

Local Group Memberships      *ADM_Test1
Global Group memberships     *Domain Users         *User_ServiceAccountNo
The command completed successfully.

Warm Regards,

Chhavinath Mishra 

Sr. Specialist Database Administrator

SQL Server : OLEDB provider 'Microsoft.ACE.OLEDB.12.0' Driver is not registered: ACE

Issue: While running an SQL agent job, if you experience following mentioned issue here is the resolution

Resolution: To resolve this issue, we need to install ' Microsoft.ACE.OLEDB.12.0 Driver'.

Post installation Verification:

If you have installed 64 bit driver then you can run odbcad32.exe from run prompt and you'll be presented with following mentioned screen

If you have installed 32bit - try running this c:\Windows\SysWOW64\odbcad32.exe 

https://social.msdn.microsoft.com/Forums/en-US/1d5c04c7-157f-4955-a14b-41d912d50a64/how-to-fix-error-the-microsoftaceoledb120-provider-is-not-registered-on-the-local-machine?forum=vstsdb

Warm Regards,

Chhavinath Mishra 

Sr. Specialist Database Administrator

SQL Server :: Moving Table to secondary data file or Different Filegroups

You may have already seen many posts on same topics as i experienced but i never got all of my answers in single post hence i thought to write this post.

Question: Is it online or offline operation?

Answer: It's online operation with few consideration. Also it's strongly recommended to do this during non-business hours if you are doing it in production and that too for huge tables.

You can follow below mentioned steps to move a table to a particular file group. A FILE-GROUP may contain one or more data files. If the FILE-GROUP contains more than one data file, SQL Server uses a proportional fill algorithm to make use of all data files in the FILE-GROUP. On the other hand, if the FILE-GROUP contains a single data file, then you can say that you have moved the table to a particular file.

To move a table to a particular file-group, you need to re-create the clustered index on the table. Recreating an index effectively moves the table to the new FILE-GROUP.


Database Name:[AdventureWorks2012]
Table Name: [Person].[Person]

Important Note:

You can not add or remove a file while a BACKUP statement is running.

Create New File-Group:

USE Master;
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP FG_Secondary
GO

Add file to Newly created File-Group:

ALTER DATABASE [AdventureWorks2012]
ADD FILE
(
NAME = FG_Secondary,
FILENAME = 'F:\mpdbs001\sqlappdb_V4RC11\AdventureWorks2012_Data2.ndf',
SIZE = 500MB,
MAXSIZE = 1000MB,
FILEGROWTH = 50MB
) TO FILEGROUP FG_Secondary;
GO

Select the table name and press Alt+ F1 and verify the data located on file-group column.
Currently it is on PRIMARY file-group.

Run Below mentioned script to move table named [Person].[Person] to filegroup named FG_Secondary.

CREATE Unique CLUSTERED INDEX [PK_Person_BusinessEntityID]
 ON [Person].[Person]
 (
  [BusinessEntityID] ASC
  )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON)
  ON FG_Secondary
GO

-----------------You may experience below mentioned error ----------

Error: Could not drop the primary key constraint 'PK_Person_BusinessEntityID' because the table has an XML or spatial index.
Solution : Script out and Drop the XML Index First and then drop the Primary Key Constraint

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

You can see in below screen nip that table is now reside in secondary file group.

Hope it will help you.

Warm Regards,

Chhavinath Mishra 

Sr. Specialist Database Administrator

Problem related to databases restore via RedGate :: Process terminated unexpectedly. Error code: -2139684860

Problem related to databases restore via RedGate :

While restoring database using Red Gate SQL Backup, was getting below error messages.

Error Msg:

4/20/2015 10:31:44 AM: Thread 3 error: 
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
4/20/2015 10:31:44 AM: 
4/20/2015 10:31:45 AM: SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
4/20/2015 10:31:45 AM: SQL error 3117: SQL error 3117: The log or differential backup cannot be restored because no files are ready to rollforward.

Solution:

You could also try increasing SQL Backup's timeout value to 60 seconds, by adding/modifying the VDITimeout value in the registry (HKEY_LOCAL_MACHINE\\SOFTWARE\\Red Gate\\SQL Backup\\BackupSettingsGlobal\\[InstanceName]). The VDITimeout value is a DWORD type.

It worked perfectly fine for me so thought to share with you all.

Thanks

P.S: https://forums.red-gate.com/viewtopic.php?t=16174

Warm Regards,

Chhavinath Mishra 

Sr. Specialist Database Administrator

SQL Server :: A database user is showing up as disabled (a down reddish arrow).

Strange problem : One of my friend came across a strange problem. He had a set of Users (not Logins) in the newly created database that, in SSMS, have the red down arrow in their icon. They act as if they are disabled.

I knew that starting from SQL server 2005 , one way to disable a user in a database is to run a REVOKE CONNECT FROM permission for that user . If that is the case , you can run a GRANT CONNECT TO and fix this problem hence I did same thing and issue was resolved.

Point to jotting down this here cause I had discussion on this with many DBA friends and most of them said that we need to delete the user and re-create it.

Even recreating a user can be a solution but I wish to highlight that it is not the only solution.

We can run below code and resolve the issue:-

USE <YourDatabase>
Go
GRANT CONNECT TO User1

Hope it will help.

Warm Regards,

Chhavinath Mishra 

Sr. Specialist Database Administrator

Import-Module : The specified module 'activedirectory' was not loaded because no valid module file was found in any module directory

The Active Directory module is part of the Remote Server Administration Tools (RSAT), which you have to download first.

Download URL: http://www.microsoft.com/en-us/download/confirmation.aspx?id=7887

After the installation is complete, open the Control Panel, start typing “features,” and then click Turn Windows features on or off. Scroll down to Remote Server Administration Tools and enable Active Directory Module for Windows PowerShell in Remote Server Administration Tools > Role Administration Tools > AD DS and AD LDS Tools.

The current SKU is invalid - SQL Server 2008

This is an error you might get when when you try to install SQL Server 2008 or 2008 R2 to add a node to a failover cluster. As mentioned in the Microsoft KB Article:

FIX: Error message when you try to add a second node to a SQL Server 2008 failover cluster: "The current SKU is invalid"

http://support.microsoft.com/kb/957459

This problem occurs when you install SQL Server 2008 from a deployment share that uses a custom Defaultsetup.ini file, and this custom file includes the product key for SQL Server 2008. Additionally, this problem may occur when you install a second node for SQL Server 2008 from installation media that has a custom Defaultsetup.ini file that includes the product key.

While the resolution provided in the KB article to install CU1 for SQL Server 2008 might be the best step forward, unfortunately it did not seem to work for us. So we planned a work-around and it did work for us.

Even i have another solution. I had applied Service pack 3 (SP3) prior to add node and it worked perfectly fine.
https://connect.microsoft.com/SQLServer/feedback/details/363036/add-node-to-sql-server-failover-cluster-failed-with-invalid-sku-error

Warm Regards,

Chhavinath Mishra 

Sr. Specialist Database Administrator

SQL Server 2008/2012 Reporting Services service doesn't start after the installation

The error message you are getting when you try to start SSRS:

"The request failed or the service did not respond in a timely fashion."

Modify the registry to increase the default time-out value for the service control manager. To increase this value to 60 seconds, follow these steps:
       1. Click Start, click Run, type regedit,and then click OK.
       2. Locate and then click the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control.

       3.  In the right pane, locate the ServicesPipeTimeout entry.


            Note If the ServicesPipeTimeout entry does not exist, you must créate it. To do this, follow these steps:

1. On the Edit menu, point to New, and then click DWORD Value.
2. Type ServicesPipeTimeout, and then press ENTER.
3. Right-click ServicesPipeTimeout, and then click Modify.
4. Click Decimal, type 180000, and then click OK.
   This value represents the time in milliseconds befor

            Restart the computer for the changes to take effect.
 Check that SSRS service can be started now...

http://blogs.msdn.com/b/mariae/archive/2012/11/12/reporting-services-service-doesn-t-start-after-the-installation-of-ms12-070-security-patch.aspx