I have four production database servers to manage. They are all SQL 2005 Server. Sometimes I need to know the name, owner, size, available space of all the DBs on each server. I create this little PowerShell scripts to do that instead of opening up all the servers in SQL Server Management Studio.

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$computers = get-content “DBServers.txt”
$OutFile = “DBServerInfo.txt”
$Line = “Production Databases information”
$Line | out-file $OutFile
foreach ($computer in $computers)
{
$Line = “rn———————————–”
$Line | out-file $OutFile -append
$Line = “Working on “ + $computer
write-host $Line
$Server = new-object (“Microsoft.SqlServer.Management.Smo.Server”) “$computer”
$Line = “Server :” + $computer + “ (Version: “ + $Server.Information.VersionString + “)rn———————————–”
$Line | Out-File $OutFile -append

$Server.databases | select name,owner,@{Name=”Size(MB)”;Expression={“{0,8:0.00}” -f $.size}},`
@{Name=”Available Space(MB)”;Expression={“{0,8:0.00}” -f ($.SpaceAvailable/1024)}}`
| Format-Table -auto | Out-File $OutFile -append
}

write-host “Output File Created: .” $OutFile

This is not easy to dig it out from MSDN webs.

http://msdn.microsoft.com/en-us/library/aa394554.aspx

Here is the script I got it from Windows PowerShell Blog for improving the startup speed.

Set-Alias ngen @(<br></br>dir (join-path ${env:windir} "Microsoft.NETFramework") ngen.exe -recurse |<br></br>sort -descending lastwritetime<br></br>)[0].fullName<br></br>[appdomain]::currentdomain.getassemblies() | %{ngen $_.location}<br></br><br></br><br></br>

Windows default shares may present a possible security hole. I create a script to disable it.

# disableDefaultShares.ps1
$MachineName = ‘.’
$reg = [Microsoft.win32.RegistryKey]::OpenRemoteBaseKey(‘LocalMachine’, $MachineName)
$regKey = $reg.OpenSubKey(“systemCurrentControlSetServiceslanmanserverparameters”, $true)
# for workstations
$regkey.SetValue(“AutoShareWks”, 0)
# for servers
# $regkey.SetValue(“AutoShareServer”, 0)

Powershelllive.com has a very good serial of articles for learning Windows PowerShell.

SQL 2005 has a new feature called Common Table Expression (CTE). You don’t need to use table variable any more. It is more powerful. You can use it for recursive query, aggregation query etc. Ex.

_WITH tmpa (col1, col2, col3)
AS
(
SELECT col1, col2, col3 FROM a WHERE a.flag = 1
)

_SELECT * FROM tmp_a
WHERE tmpa.col1 like ‘aa%’

After I read the article about comparison of Bash vs PowerShell, I conclude the most import point that PowerShell does better than Bash is the Object model. I really like the way of Object-Oriented instead of string in Bash.

SQL 2005 has a new feature called Common Table Expression (CTE). You don’t need to use table variable any more. It is more powerful. You can use it for recursive query, aggregation query etc.

Ex.

WITH tmp_a (col1, col2, col3)
AS
(
SELECT col1, col2, col3 FROM a WHERE a.flag = 1
)

SELECT * FROM tmp_a
WHERE tmp_a.col1 like ‘aa%’

Today I saw an article talking about how to extend windows power shell. It is very helpful.

http://msdn2.microsoft.com/en-us/magazine/cc163293.aspx

ABA Routing Number has 9 digits. The first 8 digits represent a specific bank or organization. And the last digit is Check Digit.

The first two digits of the routing number must fall within the range “01” through “12”, or “21” through “32”. These digits indicate the Federal Reserve District (there are twelve), with the latter set indicating that the bank is a “thrift institution”. See FRB Regulation CC, Appendix A, which available online here: http://www.bankersonline.com/regs/229/a229a.html

The Check Digit is computed using Modulus 10 as follows:

Step 1: Multiply each digit in the Transit Routing Number by a weighting factor. The weighting factors for each digit are:

<font size="4"> 
Position: 1 2 3 4 5 6 7 8 
Weights: 3 7 1 3 7 1 3 7 </font>

Step 2: Add the results of the eight multiplications.

Step 3: Subtract the sum from the next highest multiple of 10. The result is the Check Digit.

Example:

<font size="3"> 
Transit/ABA    0 7 6 4 0 1 2 5 
Multiply by    <u>3 7 1 3 7 1 3 7</u> 
Add:        0 49 6 12 0 1 6 35 

Sum = 109 
Check Digit = 1 (110 minus 109)
</font>