Las Vegas SQL Server User Group

Thanks, Jason and Stacia, for allowing me to present tonight!

Here are the materials from the presentation, including bonus favorite scripts/functions.

PowerShell: The Way of the DBA Dragon – presentation scripts and slide deck.

I used Start-Demo to play back the commands to avoid typing.

From FLAC to MP3 and back again–EAC Rips with LAME

Maximum PC has a great post on Ripping archival quality MP3s from audio CDs, but one of the user comments following the article simplifies the LAME options.

User vintagegold suggests using the following:

-V0 %s %d

I just tried the above settings within LAME to rip a classical CD – Borodin – and it sounds great on my AKG K 271 Studio headphones.  The HydrogenAudio wiki does indicate one preset with even higher quality for archiving, but my ears(which are still sensitive enough to hear the frequency on certain monitors/LCDs) don’t find the higher constant 320 kbit bitrate necessary.

Some of my essential EAC Screenshots – refer to the Maximum PC article for setting up Accurate Rip and other portions of EAC:

Arturo "Buanzo" Busleiman has current Windows and Mac downloads of the LAME binaries [high quality MPEG Audio Layer III (MP3) encoder] on his Argentina-hosted site.  The Audacity for Windows download worked on windows 7 x64.

Goodbye FLAC – I’ll not miss your options.
-8 -A tukey(0.25) -A gauss(0.1875) -b 4096 -V -T "artist=%a" -T "title=%t" -T "album=%g" -T "date=%y" -T "tracknumber=%n" -T "genre=%m" %s --sector-align

Free Space on all servers for all fixed drives? Done.

A while ago I posted on querying free disk space using WMI and batch.

I wouldn't query free disk space using batch anymore.  The PowerShell script I tweaked and currently use to query free disk space is displayed below.  The script truncates for readability – the rounding methods are commented out if that’s your preference:

# spaceused.ps1
# Lars-UT http://larsrasmussen.blogspot.com
(Get-Date -format 'yyyy-MM-dd hh:mm:ss').ToString() | Out-Default
$outData = @("")
$server = $args[0]
$dataFromServer = Get-WmiObject Win32_Volume -ComputerName $server | Select-Object SystemName,Label,Name,DriveLetter,DriveType,Capacity,Freespace

foreach ($currline in $dataFromServer) {
    if ((-not $currline.name.StartsWith("\\")) -and ($currline.Drivetype -ne 5)) {
        [float]$tempfloatcapacity = [math]::truncate($currline.Capacity/ 1000000000)
    #[float]$tempfloatcapacity = [math]::round(($currline.Capacity/ 1000000000),1)
    add-member -InputObject $currline -MemberType NoteProperty -name Capacity_in_GB -value "$tempfloatcapacity GB"
    
    [float]$tempfloat = ($currline.Freespace / 1000000) / ($currline.Capacity / 1000000)
        $temppercent = [math]::round(($tempfloat * 100),2)
        add-member -InputObject $currline -MemberType NoteProperty -name FreePercent -value "$temppercent %"
        
    [float]$tempfloatfreespace = [math]::truncate($currline.Freespace/ 1000000000)
    #[float]$tempfloatfreespace = [math]::round(($currline.Freespace/ 1000000000),1)
    add-member -InputObject $currline -MemberType NoteProperty -name Freespace_in_GB -value "$tempfloatfreespace GB"
        
    $outData = $outData + $currline
    }
}

$outData | Select-Object SystemName,Label,Name,Capacity_in_GB,Freespace_in_GB,FreePercent | sort-object -property Name | format-table -autosize

To run this against muliple servers just put the server names, one per line, in a text file and save that file. You'll then assign that file to a variable in PowerShell and loop through each line for each server:

PS C:\> $serverlist = Get-Content .\ServerNames-Prod.txt
PS C:\> foreach ($server in $serverlist) { .\spaceused.ps1 $server }

Here’s the pretty output!  Yep, those are sub-second times for querying multiple servers.

Need to document this?  Use Start-Transcript.  I’ve seen auditors use it like so:

PS C:\> $strTimestamp =  [string](Get-Date -format "yyyy-MM-dd_hh-mm")
PS C:\> start-transcript .\ServerSpace-$strTimestamp.txt
PS C:\> foreach ($server in $serverlist) { .\spaceused.ps1 $server }   
PS C:\> Stop-Transcript

Enjoy.

All Your Command (CMD) Prompts Are Belong To Us

Ever typed SET to display all the useful environment variables windows has?

You can add those environment variables to your command prompt permanently. And No, Billy, this won't work by simply editing System Properties, so read on...

Four files and the PSEXEC.EXE utility are needed - save all 4 files in the same directory in a share(such as \\server\share ) to prepare to deploy your groovy command prompt on every server under your influence.

I changed the command prompt to better keep track of which server and which credentials are in use, and added a timestamp.

CMDPROMPT.BAT - contains the prompt with your preferred environment variables and gets copied to %windir%

:: c:\windows\cmdprompt.bat
@echo off
prompt $_[%ComputerName%\%Username%] $t$_$p$g

CMDPROMPT.REG - get registered on the target machine(s) to invoke CMDPROMPT.BAT when CMD.EXE is executed

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Command Processor]
"AutoRun"="%windir%\\cmdprompt.bat"

CMDPROMPT-INSTALL.CMD - the installer script - important for deploying the prompt to multiple machines

:: \\server\share\cmdprompt-install.cmd
@echo off
copy /Y \\server\share\cmdprompt.??? %windir%\
regedit /s %windir%\cmdprompt.reg

SERVERLIST.TXT - a list of target machines(one per line) that will receive the newly minted command prompt goodness.

Eriador
Gondor
GreyHavens
Minastirith
Mordor
Rivendell
Rohan
Bullwinkle
Rocky
Elaine
George
Jerry
Challenger
Cubs
Hindenburg
SpruceGoose
Titanic
TowerofPiza

Now to install! Open up a command prompt and cd to the directory containing the SERVERLIST.TXT , then run the following:

psexec @SERVERLIST.TXT -c -u DOMAIN\%username% \\server\share\cmdprompt-install.cmd

Login to some of these target servers and enjoy the utility of the improved prompt.

Create Windows Shares from the Command Line using RMTSHARE.EXE

RMTSHARE.EXE from one of the Windows NT Resource kits makes quick work of creating shares.

Here's an example of creating a hidden, read-only share accessible only by the fictional AD group 'DOMAIN\AdminsSQL':

rmtshare \\SQLSERVERNAME\BACKUPS-RO$=E:\BACKUPS /GRANT "DOMAIN\AdminsSQL":r

Want write(change) access but still with a hidden share? Use this syntax:

rmtshare \\SQLSERVERNAME\BACKUPS-RW$=E:\BACKUPS /GRANT "DOMAIN\AdminsSQL":c

Remove the dollar sign from the end of the share name if you want the share to be visible.

Running RMTSHARE.EXE against a \\SERVERNAME with no parameters shows all the shares(including the hidden shares) available, like so:

C:\>rmtshare \\SQLSERVERNAME

Share name   Resource                        Remark

-------------------------------------------------------------------------------
IPC$                                         Remote IPC
C$           C:\                             Default share
X$           X:\                             Default share
ADMIN$       C:\WINDOWS                      Remote Admin
BACKUPS-RO$  E:\BACKUPS                      Shared by remote command.
BACKUPS-RW$  E:\BACKUPS                      Shared by remote command.
D$           D:\                             Default share
L$           L:\                             Default share
E$           E:\                             Default share
The command completed successfully.

Tested in Vista, Server 2003, Server 2000. Let me know if you find a version of Windows incompatible with RMTSHARE.EXE!

Querying Disk Space on Remote Servers using Batch with WMIC

Time to check the disk free space in GB and percentage on a server volume...

WMIC(Windows Management Instrumentation Command-line) makes another appearance!

Thanks to Tipsmark for this syntax (Response number 17 in this post). I added the /node switch and some error handling / usage to have this batch file work on remote machines.

@ECHO OFF
IF "%~1"=="" goto help
IF "%~2"=="" goto help

@SETLOCAL ENABLEEXTENSIONS
@SETLOCAL ENABLEDELAYEDEXPANSION

@FOR /F "tokens=1-3" %%n IN ('"WMIC /node:"%1" LOGICALDISK GET Name,Size,FreeSpace | find /i "%2""') DO @SET FreeBytes=%%n & @SET TotalBytes=%%p

@SET /A TotalSpace=!TotalBytes:~0,-9!
@SET /A FreeSpace=!FreeBytes:~0,-10!
@SET /A TotalUsed=%TotalSpace% - %FreeSpace%
@SET /A PercentUsed=(!TotalUsed!*100)/!TotalSpace!
@SET /A PercentFree=100-!PercentUsed!

IF %TotalSpace% LSS 0 goto error

@ECHO Total space: %TotalSpace%GB
@ECHO Free space: %FreeSpace%GB
@ECHO Used space: %TotalUsed%GB
@ECHO Percent Used: %PercentUsed%%%
@ECHO Percent Free: %PercentFree%%%

@SET TotalSpace=
@SET FreeSpace=
@SET TotalUsed=
@SET PercentUsed=
@SET PercentFree=
goto end

:error
echo.
echo *** Invalid server or drive specified ***
echo.
goto help

:help
echo.
echo diskfree.cmd
echo.
echo Queries remote server for free disk space.
echo Specify a MACHINENAME and a drive letter to be queried
echo.
echo Example:   diskfree.cmd MACHINENAME c:
echo.
goto end


:end

Here's an example of the script being run with a target computer named 'LARS', checking for free space on the [F:] volume:





If parameters are not passed or passed incorrectly(wrong drive letter) the script outputs the following or similar: