Category Archives: powershell

Which Secure Protocols are Allowed on Your Servers

A script to have handy to tell you which schannel protocols are enabled / disabled for PCI or certificate compliance.

This one works well for local servers. If you want to query a remote server, look below.

$path = "HKLM://SYSTEM/CurrentControlSet/Control/SecurityProviders/Schannel/Protocols"
Set-Location -Path $path
Get-ChildItem . |
  Select-Object @{
                  Expression={Split-Path $_.PSPath -leaf}
                  Name="Client Enabled"
                  Expression={(Get-ItemProperty -Path (Join-Path $_.PSPath 'Client') -ErrorAction silentlycontinue).Enabled}
                  Name="Client DisabledByDefault"
                  Expression={(Get-ItemProperty -Path (Join-Path $_.PSPath 'Client') -ErrorAction silentlycontinue).DisabledByDefault}
                  Name="Server Enabled"
                  Expression={(Get-ItemProperty -Path (Join-Path $_.PSPath 'Server') -ErrorAction silentlycontinue).Enabled}
                  Name="Server DisabledByDefault"
                  Expression={(Get-ItemProperty -Path (Join-Path $_.PSPath 'Server') -ErrorAction silentlycontinue).DisabledByDefault}
              } | ft -autosize

This version works against remote servers.

$path = "SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\Schannel\\Protocols\"
$Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $ComputerName)
$Reg.OpenSubKey($path).GetSubKeyNames() | ForEach-Object {
    $protocol = $_
    try {
        $cEnabled = $Reg.OpenSubKey("$($path)\\$($_)\\Client").GetValue('Enabled')
        $cDisabledByDefault = $Reg.OpenSubKey("$($path)\\$($_)\\Client").GetValue('DisabledByDefault')
    } catch {}
    try {
        $sEnabled = $Reg.OpenSubKey("$($path)\\$($_)\\Server").GetValue('Enabled')
        $sDisabledByDefault = $Reg.OpenSubKey("$($path)\\$($_)\\Server").GetValue('DisabledByDefault')
    } catch {}
  New-Object –TypeName PSObject –Prop @{"Protocol"=$protocol; "Client Enabled"=$cEnabled; "Client DisabledByDefault"=$cDisabledByDefault; "Server Enabled"=$sEnabled; "Server DisabledByDefault"=$sDisabledByDefault}
  $cEnabled, $cDisabledByDefault = $null;
  $sEnabled, $sDisabledByDefault = $null;
} | Format-Table -Property "Protocol", "Client Enabled", "Client DisabledByDefault", "Server Enabled", "Server DisabledByDefault" -AutoSize
Remove-Variable Reg

Display All SQL DateTime Conversions

If you want a quick way to determine which format, or style, to specify when converting a datetime value in SQL to a character data type, you can use this simple PowerShell script. Or you can look at the MSDN page.

0..200 | %{
    Invoke-Sqlcmd -ServerInstance . -Query ("SELECT num = {0}, CONVERT(varchar(100), GETDATE(), {0})" -f $_)
} | ft -AutoSize

Extract CLR Assemblies from SQL Server

I’ve run into a few situations that required examining the existing CLR assemblies on a server. Whether I needed to do a comparison between two versions to make sure they are the same or confirm something in the assembly itself, this script has come in handy. Point it at a database, give it an output path and it will save all the assemblies in that database to dlls in the given folder. You can then use a .NET disassembler to confirm suspicions or a binary comparison to make sure the dll matches what it should.

        Extracts CLR Assemblies from a SQL 2005+ database.
        Extracts CLR Assemblies from a SQL 2005+ database.
    .PARAMETER  ServerInstance
        The Server\Instance to connect to
    .PARAMETER  Database
        The Database to extract assemblies from
    .PARAMETER  OutPath
        The path to output the assemblies
        PS C:\> .\Get-SqlAssemblies.ps1 -ServerInstance 'MyServer\MyInstance' -Database 'MyDatabase'
        This example shows how to call Get-SqlAssemblies with named parameters.
        For more information about advanced functions, call Get-Help with any
        of the topics in the links listed below.
    [string]$ServerInstance = 'LOCALHOST',
    [string]$OutPath = '.'
#Correct for variations of incoming ServerInstance names
if(-not $ServerInstance.Contains('\')) {$ServerInstance += '\DEFAULT'}
if($ServerInstance.Contains(',') -and -not $ServerInstance.Contains('`,')) {$ServerInstance = $ServerInstance.Replace(',', '`,')}
dir SQLSERVER:\SQL\$ServerInstance\Databases\$Database\Assemblies | %{
    $_.SqlAssemblyFiles | %{
        $str = $
        $path = Join-Path $OutPath ($str.Substring($str.LastIndexOf('\')+1))
        Set-Content -Path $path -Value $_.GetFileBytes() -Encoding byte;

Split Typeperf Output in More Managable Chunks

I really like using typeperf for perfmon counter collection. It allows me to save a collection of counters to monitor and store those readings in a csv file for later analysis. Sometimes I end up running the output through the PAL tool. Unfortunately, the PAL tool generates graphs that are fairly narrow. Monitoring sessions of long duration causes these graphs to be really cramped. I wanted a way to split the typeperf output to get a reasonable amount of data points in these graphs. A side benefit is the processing per file is a lot quicker.

The script takes a filepath as its only argument. It splits by the hour and copies the header row to each new file.

param (
	[string]$filepath #incoming file
#Does the file exist
if (Test-Path $filepath) {
	$infile = Get-Item $filepath
	$data = Get-Content $infile
#if not, exit the script
else {
	Write-Warning "Failed to find $filepath"
#Get the header to be able to repeat it in each file
$header = $data | Select-Object -First 1
$lastHour = $null
$outFile = $null
#Loop through the data, skipping the header line.
$data | Select-Object -Skip 1 | ForEach-Object {
	$date = [DateTime]::Parse([string]$_.Substring(1, [string]$_.IndexOf('"',1)-1))
	if($lastHour -eq $null -or $date.Hour -ne $lastHour.Hour -or $outFile -eq $null) {
		$lastHour = $date.AddMinutes(-$date.Minute).AddSeconds(-$date.Second)
		$outFile = Join-Path $infile.Directory ("{0}_{1}{2}" -f $infile.BaseName, $lastHour.ToString('yyyyMMdd_HHmmss'), $infile.extension)
		$header | Out-File $outFile -Encoding UTF8
	$_ | Out-File $outFile -Encoding UTF8 -Append

Update: I cleaned up the script a little based on Jeff’s comments below.

Automate CPU-Z Capture to Check for Throttled Processors

Several professionals have posted about checking whether or not your processors are running at full speed by using CPU-Z. Some recommendations are to check your servers every couple of months or, if virtual, every time your guest moves. Me being lazy efficient, I’d rather automate having these servers send me their info on a scheduled basis.

First things, first. Be sure to copy CPU-Z out to all your servers, preferably in a consistent folder. Edit the cpuz.ini file to turn off extra scanning (we just need the CPU info).


Save the following as a script and schedule it to run on a monthly or so basis using your favorite scheduler. The email portion was taken from this post.

& '.\cpuz64.exe' "-txt=$env:COMPUTERNAME"
Start-Sleep -Seconds 15 #Give CPUZ enough time to generate output
$smtpServer = ""
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg.From = ""
$msg.Subject = "[CPU-Z] $env:COMPUTERNAME"
$msg.Body = gc "$env:COMPUTERNAME.txt" | ?{$_ -match "(Specification|Core Speed|Stock frequency)"}

Disable SQL Agent Jobs with PowerShell

I had a need today to disable all of our admin jobs while I moved our admin database to another file location. We didn’t want to get a lot of alerts or job failures during the move, even though it took less than 5 minutes. This script runs best in sqlps or by adding the appropriate snap-ins to your regular console.

cd SQLSERVER:\SQL\[server]\[instance]\JobServer\Jobs
dir | ?{$_.Name.StartsWith('!Admin')} | %{$_.IsEnabled = $false; $_.Alter()} #rerun with $true to enable

SQL Saturday #63 – Dallas: Wrap-up

SQL Saturday #63 – Dallas was a great event. The venue was great, although parking was a little unclear (I had to walk all the way around the building to get in.) Breakfast was good, but I’m always disappointed when there are no breakfast juices, just coffee, cola or water.

I Attended Rob Sullivan’s (blog | twitter) session on ORMs. I always like having a different perspective of topics I talk about. He did a good job of showing what to watch out for, but not demonizing ORMs as is often done. David Stein’s (blog | twitter) talk on Zombie Databases was excellent. David was very entertaining to listen to. If you ever get a chance to hear him present, do so.

After lunch it was time for my session, “So What can I do with Powershell ?” (Resources) I got off to a good start: [qtweet 54249436800483328] I was trying to make a clean joke and this came out instead. Oops. Other than that misstep, my session went really well. I was getting plenty of questions and interaction. I think this session was better since I used feedback given by Paul Randal (blog | twitter) when I gave a short presentation in front of him. Stay in front of the audience, not your computer and don’t block your mouth with your hands. I found I’m a much better presenter when I’m standing as it corrects both of those issues.

The gelato breaks were awesome!

I spent an hour talking with Idera about their interaction with the community and it sounds like they’re starting to get it. Quest, Redgate, Confio, and SQL Sentry have been doing community for a while and it’s nice to see the other big vendor get in the game as well.

During the closing ceremonies and raffle, I won a freaking iPad 1 being given away by Fusion-io. Before I had sat down from receiving my prize, I had won the Telerik Ultimate Collection. Talk about lucky, since “I never win anything!!”. I do have intentions to give away the Telerik collection to someone who’ll really use them, after all, what’s a DBA going to do with RAD controls. If you or someone you know does UI development, or forced to do UI development without any help, let me know.

Thanks to NTSSUG (blog | twitter) for putting on a great event.

Change SQL Startup Parameters with PowerShell

Backups Abound

The Annoyance

Sometimes when we’re trying to track down a problem and looking through SQL’s Logs we have to dig through hundreds of backup successful events just to find what we’re looking for. Unfortunately, SSMS doesn’t have an exclusion filter when viewing the logs, so I can’t filter them out that way. We also don’t care about having successful backups in these logs as the info is available elsewhere, including our management database.

The Solution

Trace flag 3226 will turn off logging of successful backups to the SQL Server error log, in addition to the Windows system event log. There are two ways to turn this on. Using the T-SQL command DBCC TRACEON(3226,-1), you immediately enable it, but only until SQL restarts as flags set this way do not survive a service restart. The other way to set it is by using startup parameters for the SQL service.

Don't Forget the Semicolons

In order to change SQL’s startup parameters, you need to:

  1. Load SQL Server Configuration Manager
  2. Right-click and bring up the Properties for the service you want to edit
  3. Click the Advanced tab
  4. Edit the Startup Parameters property by appending -T3226, remembering to separate with semicolons
  5. Click OK

The Better Solution

I wanted to do this quick and easy without all the clicks. I knew I wouldn’t just do this on one server, but maybe tens if not a hundred. So I opened up PowerShell ISE and started cranking out a script to this for me. My first hurdle was how are these startup parameters stored and how can I actually get to them to edit them. It turns out, they are just stored as registry keys. PowerShell works with the registry real well. After finding the key locations, doing some logic to make sure I change all instances on that server, I had my script. I made it generic enough so you could add any startup parameter to SQL, not just trace flags.

Calling the script to update all instances on the current computer is as easy as

PS> .\Add-SqlServerStartupParameter.ps1 '-T3226'


The current version does not support SQL 2000, though I plan to release an update that will. I also plan to release a version that supports remote servers. If you are changing a clustered instance, make sure to change it on node the instance is running on as clustered instances overwrite registry settings with whatever their last values were when the instance was running. It’s best to change all nodes in a cluster at once, to be on the safe side.

Download the script: Add-SqlServerStartupParameter

PowerShell Script for Paul’s Survey: how is your tempdb configured?

Paul Randal (b | t) posted a survey how is your tempdb configured? Since I just happen to be at a SQLskills event this week I went ahead and created a PowerShell script to get the info across all your servers.

You’ll need a list of servers in a text file ($serverfile), one per line. It will output the list to ‘sqlskills – tempdb proc, file count results.txt’ in the directory from which it ran. Send that file, or the results to Paul.

$serverfile = 'SQL_Servers_PROD.txt'
$query = "SELECT os.Cores, df.Files
   (SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
   (SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df;"
$dt = $null
#Get list of servers from SQL_Servers_PROD.txt
#Exclude any lines that start with # or are blank
gc $serverfile | ?{-not $_.StartsWith("#") -and $_ -ne "" -and $x -lt 2 } | %{
    $tempdbstats = Invoke-Sqlcmd -Query $query -ServerInstance $_ -SuppressProviderContextWarning
    if ($dt -eq $null) {
        $dt = , $tempdbstats
    } else {
        $dt += $tempdbstats
$dt | ft -AutoSize | Out-File 'sqlskills - tempdb proc, file count results.txt' -Encoding UTF8

The output looks like this:

Cores Files
----- -----
   16     1
   16     8