2009-03-19

Dedicated Administrator Connection (DAC)

Usually the sqlcmd tool is used for automation, but I also think of it as my last access to a running database instance when the dirt hits the fan. sqlcmd is a part of a database instance installation - you don't have to install management tools.
But when the resources of a Windows Server host or a database instance is consumed by a wild running process or session, then the Dedicated Administrator Connection (DAC) might be your last option - before shutting down the box.

SQLCMD usage
Usually it is possible to use the host name, but once I've experienced a situation where the DNS server(-s?) was down. In that case you have to enter the IP address of the host. This also gives the fastest connection to the host.

Syntax
SQLCMD.EXE -S "" -A
SQLCMD.EXE -S "Admin:" -E

Example
SQLCMD.EXE -S "SANDBOX.sqladmin\SSDB42" -A
SQLCMD.EXE -S "Admin:SANDBOX.sqladmin\SSDB42" -E

Please notice that the Browser Service must be running.

Management Studio (SSMS) usage
Use the value like the SQLCMD -S parameter.

TCP port number
The TCP port number of the DAC is shown in the Registry and the SQL Server Errorlog.

Registry
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SSDB\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp\TcpDynamicPorts [REG_SZ]
ERRORLOG
2009-03-18 21:35:15.26 Server Dedicated admin connection support was established for listening locally on port 1434.

If a host has several database instances installed, each instance DAC has it own TCP port assigned. Then you have to look into several Registry branches to get the TCP port number.

The DAC TCP port number is unfortunately not available through SMO or the SQL Server WMI provider :-(

Remote DAC
By default the DAC is available only locally on the host, but it is possible to enable remote DAC.
At SQL Server 2005 it was possible to enable remote DAC with the Surface Area Configuration point-and-click tool. But this tool is not part of SQL Server 2008 where you have to enable Remote DAC by
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

Reference

MSDN library: Using a Dedicated Administrator Connection

Kalen Delaney et al: "Microsoft SQL Server 2008 Internals", pp 27.

2009-03-16

Executing a T-SQL script file

I would like to execute a developers T-SQL script file automated, so that I can deploy without having to press the button every single time.
To do this I'm working on a PowerShell function, that uses both the Windows Shell (cmd.exe) end the SQL Server sqlcmd (SQLCMD.EXE) utility.

function Invoke-SqlCmd_cmd {
param(
 [string]$WindowsName = $( throw "Windows host name required as parameter." ),

 [string]$SqlInstanceName = $( throw "SQL Server database instance name required as parameter." ),

 [string]$DatabaseName = $( throw "Database name required as parameter." ),

 [string]$SqlScriptFileName = $( throw "T-SQL script file name required as parameter." ) )


 if( $SqlInstanceName -eq '.' )

 { $DbInstanceName = $WindowsName }

 else

 { $DbInstanceName = $WindowsName + '\' + $SqlInstanceName }


 & cmd.exe /c "SQLCMD.EXE -E -S `"$DbInstanceName`" -d `"$DatabaseName`" -i `"$SqlScriptFileName`" -u -b -m-1"

}


The function is used like this:
Invoke-SqlCmd_cmd 'Sandbox' '.' 'master' 'C:\SQLAdmin\SqlCmd.sql'

I use '.' (dot) to identify a default database instance in my repository, but any other identification can be motivated.

I haven't found a way round the Windows Shell, which I think is a indicator that PowerShell is still some way from complete. Maybe the PowerShell V2 CmdLet Invoke-Command does a better job...

The documentation is at MSDN Library on sqlcmd and at TechNet Library on Windows Shell (cmd.exe).

2009-03-10

SQL Server Native Client (SNAC)

SNAC is not documentet in Books Online (BOL), but in the learning section of MSDN:
Data Platform Developer Center > Learn > Microsoft SQL Server Native Client

Also there is a blog about SNAC. The reference to the blog is at the page referenced above.

The installed version can be found in the Registry:
HKLM\SOFTWARE\Microsoft SQL Native Client\CurrentVersion\Version : [REG_SZ]
or
HKLM\SOFTWARE\Microsoft\SNAC\InstalledVersion : [REG_SZ]
Using PowerShell the value can be accessed like this
PS > $SnacVersion = get-itemproperty 'hklm:\software\microsoft\microsoft sql native client\currentversion' 'Version'
PS > $SnacVersion.Version
and the result could be
9.00.4035.00
The SNAC version for SQL Server 2008 can be found in the Registry at
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server Native Client 10.0\CurrentVersion\Version : [REG_SZ]

To get the SNAC version on a remote host, I have used WMI and the StdReg provider:
function Get-SnacVersion {
Param( [string]$ServerName = '.' ) # Default local host
 $Reg = [WMIClass]"\\$ServerName\root\default:StdRegProv" # Remote host
 $HKLM = 2147483650
 $RegBranch = 'software\microsoft\microsoft sql native client\currentversion'
 $RegItem = 'Version'
 $Reg.GetStringValue($HKLM, $RegBranch, $RegItem).sValue
}

The function is called like this for the local host:
Get-SnacVersion
Getting the SNAC version on a remote host the function is called like this:
Get-SnacVersion 'Sandbox.sqladmin.dk'
The result is equal the result above from the Registry.

The existance of SNAC can be determined by either the existance of the Registry braches above or if the file "sqlncli.dll" exists in the folder "%SYSTEM%" (e.i. "C:\WINDOWS\system32\").
The SQL Server 2008 SNAC is implemented in the file "sqlncli10.dll".

Installation of SNAC itself can be done with the Feature Pack for Microsoft SQL Server.
A reference to this can be found at the page referenced in the beginning of this entry.

Why Microsoft (again) has a seperate installation and path for SQL Server 2008 I don't know. I'm not impressed as is will only make automated maintenance much more complex.

The book "Windows PowerSehll: TFM" (2nd Edition) har a whole chapter on managing the Registry with PowerShell (chapter 30).